User Queries for Job and List

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

  • In the List API you can build a Smart List of users based on a query of user profile attributes while specifying a Natural List source.
  • In the Job API, you can query a particular list, all users, or those who received a particular campaign, while including additional query parameters to identify a further subset of users. For example:
    • Perform a campaign-specific query (blast_query, blast_save_list, blast_snapshot) and include additional query parameters for users, beyond their inclusion in the campaign.
    • Perform a query of all users – or all users on a specific natural list (recommended) – based on other criteria, for example, for a snapshot or update job.

These queries of user profile attributes are identical to the queries that can be built in MySailthru using Audience Builder.

For all queries, you must provide parameters. Most of these are expressed as arrays.

Examples

  • 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 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_days"]
          "value": [30],
        }
     }
  • Using the Job API, create a blast_snapshot report containing all users who were included in the campaign 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.
query_mode and (match all criteria) or or (match at least one criterion)
criteria Array of criteria (see below)
value Array of values (see below)
engagement Array of engagement values (see below)
1 = Dormant
2 = Disengaged
3 = New
4 = Passive
5 = Active
6 = Engaged
geo_frequency Array of geo frequency values (see below)
threshold Array of interest thresholds (see below)
timerange Array of timeranges (see below)
field Array of fields to be updates (see below)

Source List Values

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

Note that you cannot use a Smart List as a source list because a Smart List exists as a query in the system and you cannot query a query. Instead include the criteria for that Smart List in your new query.
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 and determines what other query parts need to be provided.

Audience Builder Name Criteria in API Description Also Requires
is match exact match against variable value (“is”) field, value
is not not exact not-match against variable value (“is not”) 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
exists exists var is present field
does not exist nexists var is not present 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
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 a level of Horizon interest in valueexceeding threshold value, threshold
engagement level is engagement engagement level is engagement engagement
engagement level is at least engagement_min engagement level is at least engagement engagement
engagement level is at most engagement_max engagement level is at most engagement engagement
first purchased first_purchase_time first purchase is within specified timerange timerange, value
last purchased purchase_time has purchased within the specified timerange timerange, value
last purchased had_purchase_incomplete had an incomplete purchase logged within the specified timerange timerange, 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
geo radius geo_radius match a radius of distance in miles around geolocated zip code or city ( use_geo=1) OR match a radius of distance in miles around a custom user variable ( use_geo=0field ). See examples below. use_geo=1radius, value / use_geo=0 , field radius , value
signed up before signup_before has signed up prior to a particular date value
signed up since signup_since has signed up after a particular date value
has opted out of template optout_template has opted out of the template named value value
has opted out of mass mail only optout_blast_only has opted out of campaigns only
has opted out of mass mail optout_blast has opted out of campaigns or all mail
has opted out of all email optout_all has opted out of all mail
has purchased at least purchase_count_min has purchased at most valuetimes value
has purchased at most purchase_count_max has purchased at most valuetimes value
total purchase value is at least purchase_price_min has purchased at least value(currency) value
total purchase value is at most purchase_price_max has purchased at most value(currency) value
largest purchased item price is at least purchase_largest_item_price_min the largest item ever purchased is at least value value
largest purchased item price is at most purchase_largest_item_price_max the largest item ever purchased is at most value 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
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

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 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 parameter values apply to the criteria:

  • first purchased (first_purchase_time)
  • last purchased (purchase_time)
  • had incomplete purchase (had_purchase_incomplete)
  • last clicked (click_time)
  • last opened (open_time)
  • last bounced (bounce_time)
  • last viewed (last_viewed)

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" : ["purchase_time"],
       "timerange" : ["between_days"],
       "value" : ["30|10"]
   }
}

timerange Parameter Value

Description

Required value Parameter Value

ever

event occurred any time in the history of the user account

none

never

event has never occurred for the user

none

since_date

event occurred between the specified date and today (inclusive)

M/D/YYYY

not_since_date

event did not occur between the specified date and today (inclusive)

M/D/YYYY

since_days

event has occurred since ‘the current time of day, X days ago’

Integer representing the number of days

not_since_days

event has not occurred ‘since the current time of day, X days ago’

Integer representing the number of days

between_days

event occurred between X and Y days ago, based on the current time this query is submitted. For example, ‘between 2 to 4 days ago’ is translated to ‘between 48 and 96 hours ago’, that is, “the current time of day 4 days ago through that time 2 days ago”.

Range of days in the format
[“X|Y”]

Where X and Y are integers and X < Y.

Example: [“2|4″]

between_dates

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

Date range in the format
[“M/D/YYYY|M/D/YYYY”] Where the first date is the start date and the second is the end date.

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 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"],
      "use_geo":[0],
      "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. Setting use_geo to 1 and 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_days"],
      "value":[15]
   }
}

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.

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”. Note that the value array requires an empty option in the second position, otherwise the match of var3 would not receive the correct value.

Top