Does anyone know of a method to create a Segments from a SQL Query?
I have been swearing at my PC for the last 2 days with building accurate segments with custom objects (thought it was a misconfiguration issue but it is not).
For whatever reason - date and datetime values for custom objects just will not behave for me with relative dates. ie find all custom objects => yesterday (some back with the last week).
Same query against the contact custom field - works fine.
I know we have sqlconditions and I could do something with campaigns but thats ugly and inefficient for a very large db.
Is there a logic piece for sql segments?
Makes sense that someone would have done this before for complex queries
I use custom objects for enquiries, where a prospect may make multiple enquiries on different products/projects/properties etc.
Each enquiry has a different workflow etc etc - loving the concept of what is possible as I sync Salesforce for our clients.
I watched these custom object queries go through the database monitor from a simple custom object segment - they are a mess.
Manually building the query below, I want to create a segment from it
-- Find all leads whom have a recent enquiry in the last 1 day (since yesterday) against a related custom object
-- where field id 28 is the (date of the enquiry) against custom object (enquiries)
SELECT
l.id
FROM
leads AS l
INNER JOIN
custom_item_xref_contact AS contact_to_custom_object
ON
l.id = contact_to_custom_object.contact_id
INNER JOIN
custom_item AS custom_object
ON
contact_to_custom_object.custom_item_id = custom_object.id
INNER JOIN
custom_field_value_date
ON
custom_object.id = custom_field_value_date.custom_item_id
WHERE
custom_field_value_date.custom_field_id = 28 AND
custom_field_value_date.`value` >= date( now()- 1 )
I’ve had similar issues segmenting from custom objects data - it just doesn’t seem to work!
Have you considered just inserting the ids directly into the lead_lists_leads table? You could just run an INSERT INTO query on a regular basis with a cronjob.
It’s a bit hacky, I haven’t tested whether it would trigger campaign actions in the normal way.
INSERT INTO lead_lists_leads (leadlist_id,lead_id,date_added,manually_removed,manually_added)
VALUES (
SELECT
123, -- REPLACE WITH YOUR SEGMENT ID
l.id,
CURRENT_TIMESTAMP, -- this is the function for MySQL, you may need to use something else
0,
1
FROM
leads AS l
INNER JOIN
custom_item_xref_contact AS contact_to_custom_object
ON
l.id = contact_to_custom_object.contact_id
INNER JOIN
custom_item AS custom_object
ON
contact_to_custom_object.custom_item_id = custom_object.id
INNER JOIN
custom_field_value_date
ON
custom_object.id = custom_field_value_date.custom_item_id
WHERE
custom_field_value_date.custom_field_id = 28 AND
custom_field_value_date.`value` >= date( now()- 1 ) AND
l.id NOT IN (SELECT lead_id FROM lead_lists_leads WHERE leadlist_id = 123 AND manually_removed = 0 AND manually_added = 1 ) -- REPLACE 123 with your segment ID
);
You’d also want to write a DELETE FROM query to remove contacts from the segment who no longer fit the criteria.
haha what a hardcoded solution but yeah should work
Might be something I run from n8n
Highlevel
Static lists
Run the removal first before the insert
Need to nut out a means of storing these query statements somewhere nice
(thinking even a custom object … segment name, segment id, custom query statement)
Let me nut something out of the period - might share a n8n scenario for ppl
Little rusty in cleaning up my query, trying to nut out how to bulk delete these records whom are no longer within the criteria but in the segment.
We don’t have a unique id for an elegant match in the list table so need to do something like
DELETE FROM lead_lists_leads
Where
leadlist_id = 27 – Segment ID
AND
lead_id = (SELECT list below)
I can do them individually via n8n but trying to clean it up so I could potentially do this as a database function later on
SELECT
lead_id
FROM
`lead_lists_leads`
Where
leadlist_id = 27
AND
lead_id NOT IN (
SELECT
l.id
FROM
leads AS l
INNER JOIN
custom_item_xref_contact AS contact_to_custom_object
ON
l.id = contact_to_custom_object.contact_id
INNER JOIN
custom_item AS custom_object
ON
contact_to_custom_object.custom_item_id = custom_object.id
INNER JOIN
custom_field_value_date
ON
custom_object.id = custom_field_value_date.custom_item_id
WHERE
custom_field_value_date.custom_field_id = 28 AND
custom_field_value_date.`value` >= DATE_SUB(NOW(), INTERVAL 2 DAY)
)
Field 28 is my date field against my custom object
Segment 27 is my blank segment with no filters
n8n workflow screenshot above
Scheduled for now every 15, will prob have this run on call aswell in due course
Steps
Query 1 (see below)
Checks for results
If query empty ( no contacts to remove) proceed to insert (query 3) otherwise query 1 again
Split removal contacts into batches of 1
run removal query 2 per contact ID (lead_id) for the segment (leadlist_id ) 27
Once complete run query 3 (add all new contacts that match criteria and not yet in segment 27
Query 1
MySQL Current Removal Criteria
SELECT
lead_id
FROM
`lead_lists_leads`
Where
leadlist_id = 27
AND
lead_id NOT IN (
SELECT
l.id
FROM
leads AS l
INNER JOIN
custom_item_xref_contact AS contact_to_custom_object
ON
l.id = contact_to_custom_object.contact_id
INNER JOIN
custom_item AS custom_object
ON
contact_to_custom_object.custom_item_id = custom_object.id
INNER JOIN
custom_field_value_date
ON
custom_object.id = custom_field_value_date.custom_item_id
WHERE
custom_field_value_date.custom_field_id = 28 AND
custom_field_value_date.`value` >= DATE_SUB(NOW(), INTERVAL 1 DAY)
)
Query 2 - delete individual mismatch contacts
DELETE FROM `lead_lists_leads`
WHERE
leadlist_id = 27
AND lead_id = {{ $json.lead_id }}
Query 3 after above loop (note - SELECT DISTINCT on insert)
INSERT INTO lead_lists_leads (leadlist_id,lead_id,date_added,manually_removed,manually_added)
-- use VALUES ( ) for below statement for mysql5, remove for mysql8
SELECT DISTINCT
27 AS leadlist_id, -- REPLACE WITH YOUR SEGMENT ID
l.id AS lead_id,
CURRENT_TIMESTAMP AS date_added, -- this is the function for MySQL, you may need to use something else
0 AS manually_removed,
1 AS manually_added
FROM
leads AS l
INNER JOIN
custom_item_xref_contact AS contact_to_custom_object
ON
l.id = contact_to_custom_object.contact_id
INNER JOIN
custom_item AS custom_object
ON
contact_to_custom_object.custom_item_id = custom_object.id
INNER JOIN
custom_field_value_date
ON
custom_object.id = custom_field_value_date.custom_item_id
WHERE
custom_field_value_date.custom_field_id = 28 AND
custom_field_value_date.`value` >= DATE_SUB(NOW(), INTERVAL 2 DAY) AND
l.id
NOT IN (SELECT lead_id FROM lead_lists_leads WHERE leadlist_id = 27 AND manually_removed = 0 AND manually_added = 1 ) -- REPLACE 27 with your segment ID
