Token Definitions

Token	Description
field	A subscriber field (e.g. firstname, email), a list option value, or a custom field key. The data type defaults to text, but can be cast to date, real or integer via field::date, field::real, or field::integer. Click here for more information on casting.
integer	An integer/number (e.g. 1).
percentage	A percentage (e.g. 80%).
string	A quoted string in single or double quotes (e.g. 'xxx').
null	The database null value, (e.g. null).
range	One of the options described in Date Ranges below.
count	One of the options described in Counts below.
channel	'email' or 'sms'
event	'open', 'view', 'abandon', 'submit', 'update', 'enter'

---

Field Predicate

Predicate	Examples
[field] [operator] [integer|string|null] [range]	state = 'VIC' would return subscribers whose state is VIC postcode one of '2000, 2001, 2002, 2003' would return subscribers in postcodes 2000, 2001, 2002 and 2003 predicted_lifetime_value > 50 would return subscribers with a predicted lifetime value of more than $50 total_conversion_value < 100 would return subscribers with a total (past) conversion value of less than $100

List/Lists Predicate

Predicate	Examples
subscribed list [integer] [range] subscribed lists [integer] [range]	subscribed list 18 would return all subscribers who subscribed to list ID 18 subscribed lists 18, 22, 49 would return all subscribers who subscribed to list ID's 18, 22, 49
unsubscribed list [integer] [range] unsubscribed lists [integer] [range]	unsubscribed lists 18, 22, 49 would return all subscribers who are unsubscribed from list ID's 18, 22, 49 unsubscribed lists 10, 20 this week would return subscribers who unsubscribed from list ID's 10 or 20 this week

Link/Links Predicate

Predicate	Examples
clicked link [string] [range] clicked links [string] [range]	clicked link 'http://example.org/' would return all subscribers who clicked link 'http://example.org/' clicked links 'http://example.org/1', 'http://example.org/2' would return subscribers who clicked link 'http://example.org/1' or link 'http://example.org/2'

Activity Link/Links Predicate

Predicate	Examples
clicked link [string] activity [integer] [range] [count] clicked links [string[, ...]] activity [integer] [range] [count]	clicked link 'http://example.com' activity 1 would return all subscribers who clicked link 'http://example.org/' within activity 1 clicked links 'http://example.com/1', 'http://example.com/2' activity 1 would return all subscribers who clicked links 'http://example.org/2' or 'http://example.org/2' within activity 1
clicked link [string] activities [integer] [range] [count] clicked links [string[, ...]] activities [integer] [range] [count]	clicked link 'http://example.com' activities 1, 2 would return all subscribers who clicked link 'http://example.org/' within activities 1 or 2 clicked links 'http://example.com/1', 'http://example.com/2' activities 1, 2 would return all subscribers who clicked links 'http://example.org/1' or 'http://example.org/2' within activities 1 or 2

Activity/Activities Predicate

Predicate	Examples
sent activity [integer] [range] [count] sent activities [integer[, ...]] [range] [count]	sent activity 10 would return all subscribers who were sent activity ID 10 sent activities 10, 20 would return all subscribers who were sent activity ID 10 or activity ID 20
clicked activity [integer] [range] [count] clicked activities [integer[, ...]] [range] [count]	clicked activity 20 would return all subscribers who clicked a link within activity ID 20 clicked activities 10, 20 would return all subscribers who clicked a link within activity ID 10 or activity ID 20
opened activity [integer] [range] [count] opened activities [integer[, ...]] [range] [count]	opened activity 10 would return all subscribers who opened activity ID 10 opened activities 10, 20 would return all subscribers who opened activity ID 10 or activity ID 20
subscribed activity [integer] [range] [count] subscribed activities [integer[, ...]] [range] [count]	subscribed activity 10 would return all subscribers who subscribed through activity ID 10 (e.g. a webform) subscribed activities 10, 20 would return all subscribers who subscribed through activity ID 10 or activity ID 20 (e.g. a webform)
unsubscribed activity [integer] [range] [count] unsubscribed activities [integer[, ...]] [range] [count]	unsubscribed activity 20 would return all subscribers who unsubscribed through activity ID 20 (e.g. a webform) unsubscribed activities 10, 20 would return all subscribers who unsubscribed through activity ID 10 or activity ID 20 (e.g. a webform)
viewed activity [integer] [range] [count] viewed activities [integer[, ...]] [range] [count]	viewed activity 10 would return all subscribers that have viewed activity ID 10 (useful for webforms) viewed activities 10, 20 would return all subscribers that have viewed activity ID 10 or activity ID 20 (e.g. a webform)
bounced activity [integer] [range] [count] bounced activities [integer[, ...]] [range] [count]	bounced activity 10 would return all subscribers who bounced from activity ID 10 bounced activities 10, 20 would return all subscribers who bounced from activity ID 10 or activity ID 20
converted activity [integer] [range] [count] converted activities [integer[, ...]] [range] [count]	converted activity 10 would return all subscribers who converted/purchased through activity ID 10 converted activities 10, 20 would return all subscribers who converted/purchased through activity ID 10 or activity ID 20
liked activity [integer] [range] [count] liked activities [integer[, ...]] [range] [count]	liked activity 10 would return all subscribers who liked (using a social media button) in activity ID 10 liked activities 10, 20 would return all subscribers who liked (using a social media button) in activity ID 10 or activity ID 20

Campaign/Campaigns Predicate

Predicate	Examples
sent campaign [integer] [range] [count] sent campaigns [integer[, ...]] [range] [count]	sent campaign 10 would return all subscribers who were sent activities within campaign ID 10 sent campaign 10 would return all subscribers who were sent activities within campaign ID 10 or campaign 20
clicked campaign [integer] [range] [count] clicked campaigns [integer[, ...]] [range] [count]	clicked campaign 10 would return all subscribers who clicked links in activities within campaign ID 10 clicked campaign 10 would return all subscribers who who clicked links in activities within campaign ID 10 or campaign ID 20
opened campaign [integer] [range] [count] opened campaigns [integer[, ...]] [range] [count]	opened campaign 10 would return all subscribers who opened campaign ID 10 opened campaigns 10, 20 would return all subscribers who opened campaign ID 10 or campaign ID 20
subscribed campaign [integer] [range] [count] subscribed campaigns [integer[, ...]] [range] [count]	subscribed campaign 10 would return all subscribers who subscribed using an activity within campaign ID 10 (e.g. a webform) subscribed campaigns 10, 20 would return all subscribers who subscribed using an activity within campaigns ID 10 or ID 20 (e.g. a webform)
unsubscribed campaign [integer] [range] [count] unsubscribed campaigns [integer[, ...]] [range] [count]	unsubscribed campaign 10 would return all subscribers who unsubscribed using an activity within campaign ID 10 (e.g. a webform) unsubscribed campaigns 10, 20 would return all subscribers who unsubscribed using an activity within campaigns ID 10 or ID 20 (e.g. a webform)
viewed campaign [integer] [range] [count] viewed campaigns [integer[, ...]] [range] [count]	viewed campaign 10 would return all subscribers who viewed an activity within campaign ID 10 (e.g. a webform) viewed campaigns 10, 20 would return all subscribers who viewed an activity within campaigns ID 10 or ID 20 (e.g. a webform)
bounced campaign [integer] [range] [count] bounced campaigns [integer[, ...]] [range] [count]	bounced campaign 10 would return all subscribers who bounced from an activity within campaign ID 10 bounced campaigns 10, 20 would return all subscribers who bounced from an activity within campaigns ID 10 or ID 20
converted campaign [integer] [range] [count] converted campaigns [integer[, ...]] [range] [count]	converted campaign 10 would return all subscribers who converted/purchased through an activity within campaign ID 10 converted campaigns 10, 20 would return all subscribers who converted/purchased through an activity within campaigns ID 10 or ID 20
liked campaign [integer] [range] [count] liked campaigns [integer[, ...]] [range] [count]	liked campaign 10 last 30 days would return all subscribers who liked (using a social media button) within campaign 10 in the last 30 days liked campaigns 10, 20 last 30 days would return all subscribers who liked (using a social media button) within campaigns 10 or 20 in the last 30 days

Any Predicate

Predicate	Examples
sent any [integer] [range] [count]	sent any last 30 days would return all subscribers who were sent any activities in the last 30 days
clicked any [integer] [range] [count]	clicked any last 30 days would return all subscribers who clicked a link within any activity in the last 30 days
opened any [integer] [range] [count]	opened any last 30 days would return all subscribers who opened any activity in the last 30 days
subscribed any integer] [range] [count]	subscribed any last 30 days would return all subscribers who subscribed through any activity (e.g. a webform) in the last 30 days
unsubscribed any [integer] [range] [count]	unsubscribed any last 30 days would return all subscribers who unsubscribed through any activity (e.g. a webform) in the last 30 days
viewed any [integer] [range] [count]	viewed any last 30 days would return all subscribers who viewed any activity (e.g. a webform) in the last 30 days
bounced any [integer] [range] [count]	bounced any last 30 days would return all subscribers who bounced from any activity in the last 30 days
converted any [integer] [range] [count]	converted any last 30 days would return all subscribers who converted through any activity in the last 30 days
liked any [integer] [range] [count]	liked any last 30 days would return all subscribers who liked using the social media share buttons in any activity in the last 30 days

Cluster Keyword Predicate

Predicate	Examples
cluster_id [operator] [integer]	cluster_id = 20 would return all subscribers that are assigned to cluster ID 20 cluster_id one of '20, 30, 40' would return all subscribers that are assigned to cluster ID's 20, 30 and 40

The cluster ID is a numerical value assigned to clusters. Do not use the cluster name. The cluster ID can be found under Settings > Cluster Keywords in v4, or Settings > Clusters in v5.

Configuration Predicate

Predicate	Examples
sent configuration [integer] [range]	sent configuration 10869 would return all subscribers that have been sent configuration (multivariate test) ID 10869 sent configuration 10869 last 3 days would return all subscribers that have been sent configuration (multivariate test) ID 10869 in the last 3 days

The configuration ID can be found under the 'results' tab of the broadcasted activity. Each time a new change to the broadcasted activity is deployed, a new configuration ID will be generated, ensure you update the configuration ID after each deployed change.

Revision Predicate

Predicate	Examples
sent revision [integer] [range]	sent revision 484857 would return all subscribers that have been sent revision ID 484857 sent revision 484857 last 3 days would return all subscribers that have been sent revision ID 484857 in the last 3 days

The revision ID can be found under the 'Revision' dropdown in the activity preview. Each time a new change to the broadcasted activity is saved, a new revision ID will be generated, ensure you update the revision ID after each change is saved.

Views Predicate

Predicate	Examples
match views [integer[, ...]] [string]	match views 5, 10 would return all subscribers who match either of view IDs 5 or 10 match views 1 with "example" would return all subscribers who match view ID 1 when called with the parameter "example"

Purchase History Predicate

Predicate

Examples

purchased [fields] [range] [count]

purchased last 12 months would return all subscribers who have purchased in the last 12 months purchased count>=5 would return all subscribers who have purchased at least 5 times purchased id = "50" would return all subscribers who purchased product ID 50 purchased total::real > 100.0 last 12 months would return all subscribers who made a purchase with a total exceeding 100 in the last 12 months purchased category like "Appliances/%" would return all subscribers who purchased a product with a "category" custom field value starting with "Applicances/" purchased category like "Toys/%", price::real > 15.0 last 7 days would return all subscribers who purchased a product with a "category" custom field value starting with "Toys/" and a price above 15 any time in the last 7 days purchased 2024-04-13T12:00:00 to 2024-04-16T12:00:00 would return all subscribers who made a purchase between April 13, 2024, at 12:00 PM and April 16, 2024, at 12:00 PM.

Visit Predicate

Predicate

Examples

visited [fields] [range] [count]

visited last 12 months would return all subscribers who have visited a tracked web page in the last 12 months visited count>=2 would return all subscribers who have visited a tracked web page at least twice visited url = "http://example.com" would return all subscribers who have visited http://example.com visited url like "http://example.com/sales/%" last 12 months would return all subscribers who visited a page in the /sales/ directory in the last 12 months

Custom Tracking Predicate

Predicate

Examples

tracked [event] [fields] [range] [count]

tracked view last 12 months would return all subscribers who have had a "view" event logged in the last 12 months tracked open count>=2 would return all subscribers who have had an open event logged at least twice tracked update UserType = "premium" would return all subscribers who have had an update event logged with a "UserType" data field value of "premium" tracked open AppPage like "products/%" last 12 months would return all subscribers who had an open event logged in the last 12 months containing an "AppPage" data field value starting with "products/"

Birthday Predicate

Predicate	Examples
birthday [range]	birthday this month would return all subscribers with a birthday in the current month birthday next 3 days would return all subscribers with a birthday in the next 3 days

Contactable Predicate

Predicate	Examples
contactable via [channel]	contactable via email would return all subscribers with a valid email address who are not globally unsubscribed contactable via sms would return all subscribers with a valid phone number who are not globally unsubscribed

Value Predicate

Predicate	Examples
highest value [integer|percentage]	highest value 10% selects the top 10% of subscribers as measured by highest total conversion value
lowest value [integer|percentage]	lowest value 10% selects the bottom 10% of subscribers as measured by highest total conversion value
highest future value [integer|percentage]	highest future value 50 selects the 50 subscribers with the highest predicted future value
lowest future value [integer|percentage]	lowest future value 50 selects the 50 subscribers with the lowest predicted future value
most engaged [integer|percentage]	most engaged 50% selects the top 50% of subscribers as measured by engagement (clicks, opens, web activity)
least engaged [integer|percentage]	least engaged 50% selects the bottom 50% of subscribers as measured by engagement (clicks, opens, web activity)

---

Subset Selection Definitions

Selection	Definition
All Subscribers	Selects all subscribers that match a set of conditions.
Random N Subscribers	Selects a random N amount of subscribers who match a set of conditions. Where N is defined as a number or percentage of the total.
Highest-value N subscribers	Returns N amount of subscribers who are in the top 10% of subscribers as measured by the highest total conversion value. Where N is defined as a number or percentage of the total.
Lowest-value N subscribers	Returns N amount of subscribers who are in the bottom 10% of subscribers as measured by the highest total conversion value. Where N is defined as a number or percentage of the total.
Highest predicted value N subscribers	Returns N amount of subscribers with the highest predicted value. Where N is defined as a number or percentage of the total.
Lowest predicted value N subscribers	Returns N amount of subscribers with the lowest predicted value. Where N is defined as a number or percentage of the total.
Most-engaged N subscribers	Returns N amount of subscribers who have interacted the most, as measured by engagement clicks (clicks, opens and web activity) based on all past activities. Where N is defined as a number or percentage of the total.
Least-engaged N subscribers	Returns N amount of subscribers who have interacted the least, as measured by engagement clicks (clicks, opens and web activity) based on all past activities. Where N is defined as a number or percentage of the total.
N subscribers who most recently engaged	Returns N amount of subscribers who have interacted most recently, as measured by engagement clicks (clicks, opens and web activity). Where N is defined as a number or percentage of the total.
N subscribers who most recently purchased	Returns N amount of subscribers who have purchased most recently. Where N is defined as a number or percentage of the total.

Date Ranges

Date Predicate	Description
yesterday	00:00:00 yesterday to 00:00:00 today
today	00:00:00 today to 00:00:00 tomorrow
tomorrow	00:00:00 tomorrow to 00:00:00 the following day
this week	00:00:00 last Sunday to 00:00:00 next Sunday
this month	00:00:00 on the 1st of the current month to 00:00:00 on the 1st of the next month
this year	00:00:00 Jan 1st this year to 00:00:00 Jan 1st next year
last hour	(current hour - 1), rounded down to the hour
last day	same as yesterday
last week	00:00:00 preceding Sunday to 00:00:00 last Sunday
last month	00:00:00 on the 1st of the last month to 00:00:00 on the 1st of the current month
last year	00:00:00 on Jan 1st last year to 00:00:00 on Jan 1st this year
last N hours	(current datetime - N hours) to the current datetime
last N days	(current datetime - N days) to the current datetime
last N weeks	(current datetime - N weeks) to the current datetime
last N months	(current datetime - N months) to the current datetime
last N years	(current datetime - N years) to the current datetime
N hour[s] ago	timestamp rounded down to the hour between (current datetime - N hours) and (current datetime - (N - 1) hours)
N day[s] ago	timestamp rounded down to the day between (current datetime - N days) and (current datetime - (N - 1) days)
N week[s] ago	timestamp rounded down to the day between (current datetime - N weeks) and (current datetime - N weeks + 1 day)
N month[s] ago	timestamp rounded down to the day between (current datetime - N months) and (current datetime - N months + 1 day)
N year[s] ago	timestamp rounded down to the day between (current datetime - N years) and (current datetime - N years + 1 day)
all time	no time constraints (from a time to the current datetime)
YYYY-MM-DD to YYYY-MM-DD	00:00:00 on the first date to 00:00:00 on the second date
YYYY-MM-DDTHH:MM:SS to YYYY-MM-DDTHH:MM:SS	00:00:00T00:00:00 on the first date to the same specific time 00:00:00T00:00:00 on the second date. (Note that datetime comparisons are in UTC)

For queries on known future events, e.g. birthdays, next can be used in place of last in any of the above expressions.

Counts

Predicate	Examples
count [operator] [integer]	purchased any count>5 would return subscribers with more than 5 past purchases. opened any count<=2 would return subscribers who have opened less than or equal to two times.

Limit/Random Models

Predicate	Examples
limit [integer|percentage]	limit 10 selects 10 random subscribers matched by the previous expression
random [integer|percentage]	random 50% selects a random 50% of the subscribers matched by the previous expression

---

Comparison Operators

Assume variable a holds a value of 7 and variable b holds a value of 3.

Operator	Description	Example
=	Equal to. Checks if the values either side of the operator are equal or not, if yes then condition becomes true.	(a = b) is false, (a = a) is true.
!=	Not Equal to. Checks if the values either side of the operator are not equal, if yes then condition becomes true.	(a != b) is true, (b != b) is false.
>	Greater than. Checks if the value to the left of the operator is larger than the right. If yes, the condition becomes true.	(a > b) is true, (b > a) is false.
<	Less than. Checks if the value to the left of the operator is smaller than the right. If yes, the condition becomes true.	(a < b) is false, (b < a) is true.
>=	Greater than or equal to. Checks if the value to the left of the operator is larger or equal to the right. If yes, the condition becomes true.	(a >= b) is true, (a >= 4) is false.
<=	Less than or equal to. Checks if the value to the left of the operator is smaller or equal to the right. If yes, the condition becomes true.	(a <= b) is false, (b <= a) is true.

Binary Operators

Operator	Description	Example
and or &&	The operator executes both predicates if all the conditions separated by AND or && are TRUE.	subscribed list 10 and opened campaign 20 would return all subscribers who subscribed to list 10 and opened campaign 20
or or ‖	The operator executes one of the predicates if one of the conditions separated by OR or ‖ are TRUE.	subscribed list 10 or opened campaign 20 would return all subscribers who subscribed to list 10 or opened campaign 20

Unary Operators

Operator	Description	Example
not or ! or ~	Negates an operator	~ subscribed list 10 would return subscribers who have not subscribed to list 10

Logical Operators

Assume variable state equals Victoria

Operator	Description	Example
like	This is used to compare a value to similar values using wildcard operators.	state LIKE 'Vic%' is true
not like	This reverses the meaning of the like operator.	state NOT LIKE 'Vic%' is false
ilike	Case insensitive value comparison, similar to the like operator.	state ILIKE 'vic%' is true
not ilike	Case insensitive value comparison, similar to the not like operator.	state NOT ILIKE 'vic%' is false
is	Evaluates to true if the variables on either side of the operator point to the same object and false otherwise.	state IS 'VIC' is false
is not	Evaluates to false if the variables on either side of the operator point to the same object and true otherwise.	state IS NOT 'VIC' is true
one of	Returns true if variable on the left of operator is within the comma separated variable to the right of the operator.	state ONE OF 'Victoria, Queensland' is true
not one of	Returns false if variable on the left of operator is within the comma separated variable to the right of the operator.	state NOT ONE OF 'Victoria, Queensland' is false

Casting

Casting a data type to a specific type.

Operator	Description
::real	This casting type handles any kind of number, including both whole numbers (integers) and decimal numbers (floats). It allows for a wide range of numerical values, both positive and negative, with or without fractional parts. The format to cast is field::real.
::integer	This casting type specifically handles only whole numbers, without any fractional parts or decimals. It is used when you need to work exclusively with integers that cannot be divided into parts. The format to cast is field::integer.
::date	This casting type converts a value into a date type. It is used when you need to work with dates, such as representing calendar dates, scheduling events, or calculating durations between different points in time. The format to cast is field::date.

POSIX Regular Expression Operators

More information on POSIX Regular Expressions.

Operator	Description
~	Matches regular expression, case sensitive
!~	Does not match regular expression, case sensitive
~*	Matches regular expression, case insensitive
!~*	Does not match regular expression, case insensitive

---