User Queries for Job and List

Some API calls allow for a specific query of your user base:

  • List API – build a Smart List of users based on a query of user profile attributes.
  • Job API – select users for profile or campaign queries, such as snapshot, blast snapshot, or update. Query a particular list, all users, or those who received a particular campaign (blast).

These are identical to the queries that can be built in MySailthru using the Audience Builder. For these queries, you must provide parameters. Most of these are expressed as arrays.

For example:

  • Using the List API, create a Smart List of all users from the natural list “Master List” who have the var “vip” set to true.
    {
       "list" : "VIP Users",
       "type" : "smart",
    
       "query": {
          "source_list": "Master List",
          "criteria": ["match"],
          "field": ["vip"],
          "value": [true]
       }
    }
    
  • Using the List API, create a Smart List of all users from the natural list “Master List” who opened an email at least 3 times in the last 30 days.
    {
       "list" : "Opened three times in the last 30 days",
       "type" : "smart",
    
       "query": {
          "source_list" : "Master List",
          "criteria" : ["daily_open"],
          "compare" : ["min"],
          "compare_value" : [3],
          "timerange" : ["since_date"]
          "value": ["-30 days"],
        }
     }
    
  • Using the Job API, create a blast_snapshot report containing all users who were included in the campaign 1234 who also have a specific email address domain.
    {
       "job" : "blast_snapshot",
       "blast_id" : 1234,
       "report_email" : "example@example.com",
    
       "query" : {
          "criteria" : ["domain"],
          "value" : ["example.com"]
       }
    }
    

You’ll find additional examples throughout this page, including in the last section, Example Queries.

Basic Parameters

All parameters listed below are optional.

Parameter Description
source_list List to use as the source for the query. See below to query multiple lists. If you leave out this parameter, all users in the database are queried.
query_mode and (match all criteria) or or (match at least one criterion). Nested queries are supported. See below for more information.
criteria Array of criteria (see below)

Criteria-Specific Parameters

All parameters listed below are optional.

Parameter Description
value Array of values to match against. Note that the values which make sense here differ based on the criteria being used. For any criteria involving date timeranges, this value field will include information about what dates to search (see Timerange section below).
engagement Array of engagement values (see below)0 = optout/hardbounce1 = Dormant

2 = Disengaged

3 = New

4 = Passive

5 = Active

6 = Engaged

geo_frequency Only used for geo_* criteria. Array of geo frequency values, which specify how frequently the user was in a given location (e.g. 0.5 means they were in a location 50% of the time).
threshold Only used for horizon_interest criteria. Array of interest thresholds that specify how interested a user is in a particular horizon tag, relative to all other users in an account.
timerange Array of timeranges (see below)
field Array of fields to be updates. This usually corresponds to a var name.
radius Only used for geo_radius criteria. Array of radius values, which specify how many miles radius from the given location should be searched.
compare Array of values, which when combined with the compare_value field, control what kind of searching will be done. For some criteria, this specifies the operator (e.g. min vs. max for purchase_count criteria). For other criteria, it specifies the search domain (e.g. tag or product for last_purchase criteria).
compare_value Array of values, which when combined with the compare field, control what kind of searching will be done. For some criteria, this will be a number (e.g. for purchase_count, compare=max and compare_value=3 will find users who have made at most 3 purchases). For other criteria, this will be a string (e.g. for last_purchase criteria, compare=tag and compare_value=abc|def will find users whose last purchase is for items tagged as abc or def.)

Source List Values

You can query single or multiple lists under the source_list parameter.

Value Description Also Requires Example
.primary includes all primary lists “source_list”:”.primary”
null (no value) includes all users in database source_list not passed, or source_list:””
.multiple choose multiple lists array of list names within multiple_source_list[] parameter {“source_list”:”.multiple”,”multiple_source_list”:[“Main”,”Another List”],
.multiple-all check multiple lists but return only members in common (who appear on all lists) array of list names within multiple_source_list[]parameter {“source_list”:”.multiple-all”,”multiple_source_list”:[“Main”,”Another List”],

Criteria

The criteria array contains the type of matching to do and determines what other query parameters need to be provided.

Audience Builder Name Criteria in API Description Also Requires
is match exact match against variable value (“is”) field, value
is at least min greater-than-or-equal test against variable value (“is at least”) field, value
is at most max less-than-or-equal test against variable value (“is no more than”) field, value
is greater than gt greater-than test against variable value (“is greater than”) field, value
is less than lt less-than test against variable value (“is less than”) field, value
contains contains variable value contains the given text field, value
exists exists var is present field
custom profile field matches date var_date checks if the date value stored in the given variable is within the specified timerange field, timerange, value
has key key has a particular profile key (e.g. twitter, Facebook, email, sms) field
is valid (not optout/hardbounce) valid is a valid email (not an opt-out or hardbounce)
email domain is domain match email domain name value
email address matches email_matches match email address value
geolocated city geo_city match geolocated city value, geo_frequency
geolocated state geo_state match geolocated state value, geo_frequency
geolocated country geo_country match geolocated country value, geo_frequency
geolocated zip geo_zip match geolocated zipcode value,geo_frequency
is interested in horizon_interest has no Horizon interest in value exceeding threshold value, threshold
is not interested in horizon_interest_less_than has a level of Horizon interest in value less than threshold value, threshold
engagement level is engagement engagement level is engagement engagement
purchase purchase has purchased within the specified the specified timerange timerange, value, compare, compare_value
first purchased first_purchase first purchase is within specified timerange timerange, value, compare, compare_value
last purchased last_purchase last purchased within the specified timerange timerange, value, compare, compare_value
last purchased abandoned_cart had an abandoned cart logged within the specified timerange timerange, value, compare, compare_value
last clicked click_time last clicked within the specified timerange timerange, value
last opened open_time last opened within the specified timerange timerange, value
last bounced bounce_time has bounced within the specified timerange timerange, value
last viewed last_viewed last viewed within the specified timerange timerange, value
clicked, opened, viewed, or messaged N times in a time range daily_open / daily_click / daily_pv / daily_message performed the given action N number of times within the specified timerange compare, compare_value, timerange, value
geo radius geo_radius match a radius of distance in miles around geolocated zip code or city OR match a radius of distance in miles around a custom user variable (must specify which one using field ). See examples below. radiusvaluefield 
Subscription date (signup date) signup_date has signed up within the particular timerange value, timerange
optout date optout_date has opted out from email within the particular timerange value, timerange
has opted out of all email optout_all has opted out of all mail
amount of purchases purchase_count has purchased at most or at least valuetimes in the specified timerange value, compare, compare_value, timerange
total purchase revenue value net_revenue_price has purchased at least or at most value(currency) in the specified timerange, minus any returns compare, compare_value, timerange, value
largest purchased item price purchase_largest_item_price the largest item purchased in the specified timerange is at least or at most value compare, compare_value, timerange, value
made a purchase including a purchase var purchase_var made a purchase in the specified timerange including a particular item or order var compare, compare_value, field, timerange, value
is a member of list list_member is a member of the list named value value
is not a member of list list_member_not is not a member of the list named value value
status is softbounce status_softbounce status is softbounce
status is hardbounce status_hardbounce status is hardbounce
user marked as spam spam user marked as spam
user is valid for mailing valid Status is valid, i.e. not opted out or hardbounce
reads email using browser_email has read email using the device/browser value value
browses site using browser_site has browsed site using the device/browser value value
has at least one app has_any_app has at least one app
app was uninstalled app_uninstalled uninstalled from all devices value,
field,
timerange
opened app N times in a time range daily_open_app performed the given action N number of times within the specified timerange compare,
compare_value,
value,
field,
timerange

compare/compare_value Details

Purchase compare/compare_value options
purchase / first_purchase / last_purchase / abandoned_cart

  • The valid values for compare are:
    • tag – searches tags
    • product – searches titles and SKUs
    • <null> – searches both tags and titles and SKUs
  • compare_value

    A pipe delimited string of tags or titles/SKUs to search

Numeric compare/compare_value options
daily_open / daily_click / daily_pv / daily_message / purchase_count / net_revenue_price / purchase_largest_item_price

  • The valid values for compare are:
    • min – “at least”
    • max – “at most”
  • The valid values for compare_value are:
    • compare_value is an integer for daily_open, daily_click, daily_py, daily_message, and purchase_count
    • compare_value is a decimal for net_revenue_price and purchase_largest_item_price.

Purchase var compare/compare_value options
purchase_var

  • The valid values for compare are:
    • item
    • order

    These values control what type of var is searched for.

  • compare_value is a string or a decimal that is compared against.

Blast-Specific Job Criteria

For example, to save all users who opened a particular campaign to a new list called “Campaign Openers”, you can issue the following API call to the /job POST:

{
   "job" : "blast_save_list",
   "blast_id" : "12345",
   "query: {
      "criteria" : ["message_open"]
   },
   "list" : "Campaign Openers",
}
Audience Builder Name Criteria in API Description Also Requires
opened message message_open all users who opened that specific campaign
did not open message message_open_not all users who did not open that specific campaign
clicked message message_click all users who clicked on a link in that specific campaign
did not click message message_click_not all users who did not click on a link in that specific campaign
purchased from this message message_purchase all users who made a purchase within X hours after opening that campaign (see Message Cookie Duration in Settings page)
did not purchase from this message message_purchase_not all users who did not make a purchase after receiving that campaign
opted out from this message message_purchase_not opted out by clicking through this campaign
clicked on URL message_click_url clicked on a specific URL in this campaign (specify the complete URL in the query ie https//www.mydomain.com/product/1234) value
was sent URL message_sent_url campaign contained a specific URL value
was sent subject message_sent_subject campaign contained a specific subject (for dynamic/Zephyr subject lines) value
message softbounced message_softbounce campaign softbounced
message hardbounced message_hardbounce campaign hardbounced

Time-Range Parameters

The following timerange values apply to any criteria involving date ranges. Wherever the timerange parameter is required, it uses the value field to include more information about what time range to query for.

For example, to create a new smart list with users who have last purchased some time between 30 and 10 days ago, the following API call can be made to the /list POST:

{
   "list" : "Purchased between 30 and 10 days ago",
   "type" : "smart",
   "query" : {
       "source_list" : "Some Natural List",
       "criteria" : ["last_purchase"],
       "timerange" : ["between_dates"],
       "value" : ["-30 days|-10 days"]
   }
}

timerange Parameter Value

Description

Required value Parameter Value

ever

event occurred any time in the history of the user account

none

since_date

event occurred on or after the specified date and today (inclusive)

YYYY-MM-DD or relative date (see below)

before_date

event occurred before the specified date (does not include the specified date)

YYYY-MM-DD or relative date (see below)

between_dates

event occurred between the specified dates (inclusive of the dates specified)

Two pipe-delimited dates in the format YYYY-MM-DD or relative date format (see below)

E.g. [“2017-01-01|2017-02-01”]

Where the first date is the start date and the second is the end date.

on_date

event occurred on the specified date (any time from midnight to 11:59:59pm on that day)

YYYYY-MM-DD or relative date (see below)

anniversary_date (only supported by the var_date criteria)

event occurred on the anniversary of a given month and day combination. This is useful to query against birthdays or anniversaries.

MMM D format, e.g. “Mar 1” or “Dec 18”. Or relative date (see below)

anniversary_month (only supported by var_date criteria)

event occurred on any day within the given month.

MMM format, e.g. “Feb” or “Sep”. Relative dates are NOT supported.

Relative date format:

Any of the timeranges support a relative date instead of an exact date. This means that the actual date range being queried will change over time, to be relative to the current date.

Relative dates should be specified in the following format:

“[operator][number][time unit]”

  • where operator is + for future dates and – for past dates
  • where number is any number
  • where time unit is “days” or “weeks” or “months” or “years”

For example, “-30 days” means 30 days ago.

For example, “+5 weeks” means 5 weeks from now.

To query for “today”, you can use “-0 days”. To query for yesterday, you can use “-1 days”.

Note: Future dates only make sense when querying against custom profile field dates (var_date criteria). All other event types (e.g. purchases, clicks, opens) are expected to be in the past.

Example Queries

Users Who Match a Variable Value

{
   "job":"snapshot",
   "query":{
      "source_list":".primary",
      "criteria":["match"],
      "field":["myvar"],
      "value":[1]
   }
}

This snapshot captures all primary list users whose value for “myvar” is the integer 1.

Users who have a variable containing a specified date range

{
    "job": "snapshot",
    "query": {
        source_list: "my list",
        criteria: ["var_date"],
        field: ["custom_date"],
        timerange: ["since_date"],
        value: ["-7 days"]
    }
}

This snapshot captures all “my list” users who have a profile var named “custom_date” containing any date value after 7 days ago. For example, if this was executed on September 10, 2017, this would match a user var that has the value 20170904 since it is after 7 days ago (September 3). It would not match a user var that has the value 20170901 since that is before 7 days ago (September 3). It would match a user var that has the value 20170903 because that is on the same day as 7 days ago. It would also match any future dates, for example 20341125.

Users by Geolocation

{
   "job":"snapshot",
   "query":{
      "source_list":"Main",
      "criteria":["geo_city"],
      "value":["New York, NY US"],
      "geo_frequency":[25]
   }
}

This snapshot captures all Main list users who open their emails in New York, NY at least 25% of the time. Possible geo_frequency values are any integer 1-100.

Users by Geolocation Variable

{
   "job":"snapshot",
   "query":{
      "source_list":".primary",
      "criteria":["geo_radius"],
      "radius":[5],
      "field":["zip"],
      "value":["10025"]
   }
}

This snapshot captures all primary list users whose value for the “zip” user variable is within 5 miles of the zip code 10025. Removing the field parameter would allow you to use Sailthru geolocation data.

Users by Clicks within Time Range

{
   "job":"snapshot",
   "query":{
      "criteria":["click_time"],
      "timerange":["since_date"],
      "value":["-15 days"]
   }
}

This snapshot captures all users in the database who have clicked in the past 15 days.

Users by Device

{
   "job":"snapshot",
   "query":{
      "source_list":".primary",
      "criteria":["browser_site"],
      "value":["iPad|iPhone"]
   }
}

This snapshot captures all primary list users who browse your website with either an iPad or iPhone. Possible desktop values are Firefox, Chrome, Safari, Opera, Internet Explorer, and OtherPossible mobile values are iPad, iPhone, Android, Opera Mini, BlackBerry, Palm, Windows Smartphone, and Other Mobile. All these values may be used in combination.

Purchase Queries

{
    "job": "snapshot",
    "query": {
  source_list: "smart list",
        criteria: ["purchase"],
        timerange: ["on_date"],
        value: ["2017-02-01"]
    }
}

This snapshot captures all users on the Smart List “smart list” who have made a purchase on February 1, 2017. Users will match if they have made a purchase on that day, regardless of what they bought.

{
    "job": "snapshot",
    "query": {
        source_list: "smart list",
        criteria: ["purchase"],
        timerange: ["ever"],
        value: [""],
        compare: ["tag"],
        compare_value: ["foo"]
    }
}

This snapshot captures all users on the Smart List “smart list” who have ever purchased an item tagged with the tag “foo” (case insensitive). Using the “ever” timerange means that the timing of the user’s purchase will not be considered in the query evaluation.

{
    "job": "snapshot",
    "query": {
        source_list: "smart list",
        criteria: ["purchase"],
        timerange: ["between_dates"],
        value: ["2017-01-31|2017-02-02"],
        compare: ["tag"],
        compare_value: ["foo"]
    }
}

The purchase queries also support combining the “what” and “when” aspects covered above. This query finds users who have purchased items tagged with the tag “foo” between January 1, 2017 and February 2, 2017. If a user has purchased a “foo” tagged item outside of that date range, they will not match. Similarly, a user who has purchased items within the date range that are not tagged with “foo” will not match.

Purchase Var Query

{
    "job": "snapshot",
    "query": {
        source_list: "natural list",
        criteria: ["purchase_var"],
        compare: ["order"],
        field: ["foo"],
        compare_value: ["bar"],
        timerange: ["on_date"],
        value: ["2017-09-25"]
    }
}

This snapshot will capture users on the natural list “natural list” who have purchased on September 25, 2017, and where the purchase includes an order var “foo” equal to “bar”. Note that a similar type of query could be used for item vars, by changing the compare_value to “item”. Additionally, the timerange could be set to “ever” if you do not care when the purchase including the var was made.

Daily Clicks, Opens, etc. Query

{
    "job": "snapshot",
    "query": {
        source_list: "natural list",
        criteria: ["daily_click"],
        compare: ["min"],
        compare_value: [3],
        timerange: ["since_date"],
        value: ["-7 days"]
    }
}

This snapshot will find all users on the natural list “natural list” who have clicked at least 3 times in the last 7 days (i.e. since 7 days ago). The same can be done with opens (daily_open criteria), site page views (daily_pv criteria), or message sends (daily_message criteria). A “compare” value of “max” can be used to do an “at most” search, e.g. finding users who have viewed the site at most 5 times in the last 2 weeks.

Query with Multiple Criteria

{
   "job":"snapshot",
   "query":{
      "source_list":".primary",
      "query_mode":"and",
      "criteria":[
         "match",
         "geo_city"
         ],
      "field":["source",""],
      "value":["sidebar","New York, NY US"],
      "geo_frequency":["",50]
   }
}

This snapshot captures all primary list users who have a “source” value of “sidebar” and open their email in New York, NY more than 50% of the time. Note that the order of the “criteria” array determines the order of subsequent arrays (“value”,”field”, etc.).

{
   "job" : "snapshot",
   "query" : {
      "source_list": "Natural List",
      "query_mode": "and",
      "criteria": ["match", "exists", "match"],
      "field": ["var1", "var2", "var3"],
      "value": ["one", "", "three"],
   }
}

This Snapshot will find any users on the “Natural List” who have var1 = “one” AND var2 exists AND var3 = “three”. The criteria, field, and value relationship is described in the following table:

Criteria Corresponding Field Corresponding Value
match var 1 one
exists var 2 “”
match var 3 three

Advanced Query Logic

Queries also support nested ANDs and ORs. The query_mode field controls the relationship between the top-level criteria, but you may also want to have a nested logical relationship.

{
   "job" : "snapshot",
   "query" : {
      "source_list": "Natural List",
      "query_mode": "and",
      "criteria": ["match", "(", "match", "match", ")"],
      "field": ["var1", "", "var2", "var3", ""],
      "value": ["one", "or", "two", "three", ""],
   }
}

This query will match users who have “var1 = one AND (var2 = two OR var3 = three)”. This is accomplished by providing the opening parenthesis — ( — in the criteria array. In order to tell the system when the grouping ends, a closing parenthesis — ) — is required. You can control whether the nested grouping is combined with AND or OR by providing “and” or “or” in the value array, at the position corresponding with the open parenthesis.

Queries also support a NOT operator. This will reverse the match behavior of the criteria which immediately follows it.

{
   "job" : "snapshot",
   "query" : {
      "source_list": "Natural List",
      "criteria": ["!", "match"],
      "field": ["", "var1"],
      "value": ["", 1],
   }
}

This query will match users who do NOT have the “var1” var set to 1. Note that this will match users who have var1 set to any other value (e.g., 2 or “string” or false), as well as users who do not have var1 set at all. The NOT operator is specified with an exclamation point — ! — within the criteria array, and it will negate the criteria immediately following it. You can combine the nested groups with the NOT operator, by placing the NOT exclamation point directly before the opening parenthesis of the nested group. For example, the criteria array may look like “criteria”: [“!”, “(“, “match”, “match”, “)”]. >

Note: The logic for negating a group is complex. Use a Snapshot before sending to the list for mass mail campaigns to confirm that the query matches your intended audience.

Top