Target Expression Reference

Last Updated: 6/10/2019     Tags: target expressions, target, expression, reference, purchased
  • Switch Version
  • V5
  • V4

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.
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'

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
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'
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.

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

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

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)

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

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

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