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 the platform 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 "Main List" who have the var "vip" set to true.
  
  Copy

  {
     "list" : "VIP Users",
     "type" : "smart",
  
     "query": {
        "source_list": "Main List",
        "criteria": ["match"],
        "field": ["vip"],
        "value": [true]
     }
  }
  

• Using the List API, create a Smart List of all users from the natural list "Main List" who opened an email at least 3 times in the last 30 days.
  
  Copy

  {
     "list" : "Opened three times in the last 30 days",
     "type" : "smart",
  
     "query": {
        "source_list" : "Main 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.
  
  Copy

  {
     "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.

Criteria-Specific Parameters

All parameters listed below are optional.

Source List Values

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

Criteria

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

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",
}

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"]
   }
}

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][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 click their emails in New York, NY at least 25% of the time. Possible geo_frequency values are any integer 1-100. Note: Clicks are used to track geo_frequency over opens since opens are tracked by an image loading. For Gmail, Google acts as a proxy for these images. If opens were tracked for geo_frequency, then the Google server location would be tracked, not the user's location.

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 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 Other. Possible 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:

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", ")"]. >

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.