Marketing Campaigns V2 Segmentation Query Reference

In the previous example, the contact_id and updated_at columns are selected in the query. These two columns must be selected, and aggregate function MAX and its alias can be selected part of Last Event queries. Other than those columns, no additional columns can be selected. When joining two tables, they must be joined on the contact_id column only because it is the foreign key used to connect the contact_data and event_data tables.

• SELECT
• FROM
• JOIN (defaults to INNER JOIN )
• INNER JOIN
• ON
• WHERE

• [NOT] IN
• IS [NOT]
• AND
• OR
• NOT
• =
• >
• >=
• <
• <=
• !=
• +
• -
• *
• /
• %

Event data that can be optionally be used to enhanced segments

• click - Whenever a recipient clicks one of the Click Tracked links in your email. In the Email History, SendGrid displays the date, time, and the URL for the link that was clicked.
• blocked - When your IP address has been blocked by an ISP or messaging organization. Blocks are less severe than bounces and do not result in permanent suppressions: subsequent sends to blocked email addresses are not automatically suppressed.
• bounce - The receiving server could not or would not accept the message. If a recipient has previously unsubscribed from your emails, your attempt to send to them is bounced.
• deferred - The recipient mail server asked SendGrid to stop sending emails so fast.
• delivered - The accepted response generated by the recipients' mail server.
• dropped - Twilio SendGrid will drop an email when the contact on that email is in one of your suppression groups, the recipient email previously bounced, or that recipient has marked your email as spam.
• group_resubscribe - When a recipient resubscribes themselves to a suppression group.
• group_unsubscribe - Whenever a recipient unsubscribes from a suppression group.
• open - The response generated by a recipient opening an email.
• processed - Requests from your website, application, or mail client via SMTP Relay or the API that SendGrid processed.
• spamreport - Whenever a recipient marks your email as spam and their mail server tells us about it.
• unsubscribe - Whenever a recipient unsubscribes from your emails.

Example query

current_timestamp

The following query will return all contacts that were created before this run of the segment:

timestampadd allows you to specify a time interval, a number of those intervals as a count, and a timestamp that you can use to compare Date fields against.

Parameters

Example Query

timestampadd(interval, count, timestamp)

The following query will return all contacts that were created before one year prior to 2020-10-15 6PM GMT:

array_contains checks for at least one of the values provided in an array. It does not hard check for the presence of all the values or an exact match of the values given. It also doesn't support pattern-matches like '%gmail.com%' as array values. array_contains will instead check for the exact string, including pattern match characters. For example, when searching '%gmail.com%' the % characters are considered part of the string literal.

Parameters

Example Query

array_contains(field_name, array)

The following query will return all contacts with email domains equal to gmail.com or yahoo.com:

contains_word is a custom function provided by the Marketing Campaigns V2 Segments API. contains_word accepts two arguments: a word_value that will be searched for in the field_name values stored for each contact.

The field_name you specify must be of type Text, and the value stored in field_name must be a comma-separated string. For example, if the function evaluates the word_value alice against a first_name field where the value is bob,alice,sue, it will find alice and return the contact. However, if the first_name value is stored as bobalicesue, bob.alice.sue, or any other non comma separated string, alice will not be found, and the contact will not be returned. This is important when thinking about how to enter the string values associated with your contacts. The check is case sensitive.

• All special characters and spaces within the comma separated string will be included when looking for a match. For Example, a field with a value of bob,(alice),sue and a match value of (alice) will return a contact
• Single quotes in a field value must be escaped in the match value using double single quotes. For Example, a field value of bo'b,alice will require a match value of bo''b .

Parameters

Example Query

The following query will return all contacts where first_name contains the word bob :

All contacts:

All contacts with first_name 'Dave':

All contacts where state_province_region is 'Colorado':

All contacts with primary email with the substring 'gmail.com':

All contacts with a text type custom field my_text_custom_field value 'abc':

All contacts with primary email with 'gmail.com' as domain name, and a text type custom field my_custom_field value 'abc':

All contacts with a number type custom field my_number_custom_field value 12:

All contacts with a date type custom field my_date_custom_field value `2021-01-01T12:46:24Z':

All contacts where alternate email is equal to 'alternate@gmail.com':

All contacts where alternate email is equal to 'alternate@gmail.com' or 'alternate2@gmail.com':

All contacts present in a specific list "list_id":

All contacts present in either of the list_ids:

All contacts with specific email domain(s) gmail.com:

All contacts where created_at is after 2021-01-01 12 PM GMT:

All contacts where created_at is equal to 2021-01-01 12 PM GMT:

All contacts with external id that starts with '123':

When a contact's reserved fields are not set during creation, they default to a NULL value. Not setting a value for custom fields when creating a contact will save the contact without those custom fields. A segment query using these fields does not return contacts having NULL as a value. Additional conditions may be used to specify the field is null so that contacts having a null value will be included in the segment.

All contacts where first_name does not equal Dave, also including contacts without a value for first_name:

All contacts where my_custom_field does not contain abc, also including contacts without a value for my_custom_field:

Only JOIN and INNER JOIN are allowed and INNER JOIN will be internally converted to JOIN. LEFT JOIN and RIGHT JOIN are restricted for performance, and the same functionality can be achieved using JOIN. The examples below show how LEFT JOIN / RIGHT JOIN can be replaced with JOIN and UNION.

The following queries return all contacts that have an event "delivered" for a particular Single Send or whose state is Colorado. While both queries return the same set of contacts, using JOIN and UNION is more performant than using LEFT JOIN.

LEFT JOIN (not supported)

JOIN and UNION

UNION can be used instead of "OR" to return contacts based on distinct criteria that requires use of a JOIN.

For example, a query for contacts that were 'delivered' a Single Send OR the contacts whose state_province_region is in "CO" can be written as follows:

All contacts that have at least one event ( i.e. an attempt was made to send them an email):

All contacts that have opened emails from two different single sends:

All contacts that have opened ANY single sends within 3 days:

All contacts that have both of the two events - click and open:

All contacts that have both of the two events - bounce and deferred for a particular automation:

All contacts that have no event data (i.e. all contacts with where there has been no attempt to send an email to):

All contacts that have not opened any mail in the last two months worth of seconds from the time when the segment is run:

All contacts that have been 'delivered' the ANY single sends but have not 'open' ANY single sends within 1 month:

All contacts that have been 'delivered' the second email from automation but have not 'open' the email (mc_auto_id = 'fa3e0f6e-d397-11eb-87ce-22ffc6ed50f5' mc_auto_step_id = 'fc9ead9f-d397-11eb-92fe-02aa17a6534e'):

There are three email activities that support last events: "Last Clicked", "Last Opened", and "Last Emailed". The last event queries are helpful to see the contacts whose last mail activity is in a certain period. The following queries are used to segment contacts based on their last mail activity.

All contacts that have not opened contacts since January of 2021 (last opened activity is before 2021):

All contacts that last clicked an email within 7 days (last clicked activity is within 7 days):

All contacts that were last emailed in the past three months and last opened in the previous month (last emailed is within 3 months and last opened is within 1 month):

Multiple data sets are allowed to be joined together if done in a specific manner even though SQL supports both JOINs expressed linearly and JOINs expressed using subqueries. Each data set's criteria must be listed alongside the corresponding table reference as it makes for better readability by having the queries nested explicitly by using subqueries. Following is an example of a segment query for both types. Here, the query defines a segment of all contacts that have the event of type 'processed' from the list of contacts which have the event of type 'delivered'.

This logic, however, can easily be represented using JOINs with subqueries in a more readable way.

When creating or changing a segment query using curl command, escape any single quotes present in any parameter: