Segment query language reference

Segmentation queries in API requests are precise definitions of segments. The queries indicate the condition or conditions that data must satisfy to be selected. The syntax performs a similar function to WHERE statements in SQL. Query syntax acts as a filter to determine the data returned.

For example, a segment query can retrieve segment members that have left a checkout without completing their purchase in the last 30 days.

You can query a maximum of 250 segments. The maximum value that first and last arguments accept is 250.

Segment queries are composed of a clause, or multiple clauses, specifying facts about the segment. The clauses specify a segment by applying a condition to an attribute using an operator.

An individual clause consists of the following components:

• <attribute>: The properties that define all items in a specific category.
• <operator>: A reserved character pattern that performs tasks such as comparisons and arithmetic operations.
• <condition>: The state required for data to be either selected or filtered out.

The following is the query clause format:

For example, in the following clause, email_subscription_status is the attribute, = is the operator, and SUBSCRIBED is the condition:

You can also group clauses.

An attribute's data type determines the valid operators and conditions for defining segment queries.

The following are supported attributes and their data types:

Boolean attributes support specific operators and conditions.

Date attributes support specific operators and conditions.

Date operators act on complete 24-hour days and default to a shop's time zone.

For example, a New York shop (GMT-4) with the following query would include customers who last ordered between midnight (T00:00:00) on May 30th, 2020 to midnight on May 31st, 2020 in the shop's timezone.

In SQL, with UTC-based times, this would look like the following:

The following segment query would include customers who last ordered from midnight on May 31st:

The following are supported operators for date-type attributes.

Date-type attributes accept specific conditions.

Language-sensitive formatted dates aren't accepted.

Syntactic sugar for an absolute date without time. For example, if the date is May 15th 2021, then -7d is syntactic sugar for 2021-05-07.

The following are examples of {dateOffset}:

Syntactic sugar for an absolute date without time.

{namedDate} supports the following operators:

When using the BETWEEN operator, condition types can be mixed.

In some cases, users might want finer segment granularity, such as the segment of customers that participated in a timed flash sale. The segmentation query syntax supports an absolute datetime in place of an absolute date.

Times default to a shop's timezone. For example, a New York shop (GMT-4) with the following query would include customers means last_order_date > 2020-05-30T20:00:00 in UTC.

• Only 24h format is supported. For example, HH needs to be be <= 23 (24h = 00:00:00).

• Leading zeros are required, for example, 23:00:00.

• Time-only syntax is not supported. For example, the following syntax is invalid:

• You can't extend {namedDate} or {dateOffset} with a time-specifier.

Enum attributes support specific operators and conditions.

• Conditions accept UTF-8 characters and must be wrapped with single quotes ', similar to the ENUM type in MySQL.

• Conditions only accept a value chosen from a list of permitted values.

• Conditions are case-sensitive and whitespaces are syntactically significant.

• Single quotes within a condition must be escaped (ie. '\'VIP\'').

• For Segmentation, we're using multiple clauses with connectors to specify multiple values.

Float attributes support specific operators and conditions. The underlying data type is a float64 (double-precision floating-point format).

• Conditions accept float (float64) and integer numbers.

• Integers automatically get converted to floating point (ie. 99 => 99.0).

• Operators and functions returning a Float, for example COUNT, SUM, MAX, and MEDIAN, aren't supported for querying segments.

• A decimal point (.) is used as a decimal separator, by default.

• Thousand separators, such as commas and spaces, aren't supported.

• Language-sensitive formatted numbers aren't supported.

A Function returns a typed value that indicates the <operator> and <condition> that you can use.

For segmentation queries, functions are typically used to abstract subqueries on evented data and create multi-value pseudo-attributes as their return type. An example is products_purchased().

• Function names accept the same characters as regular filters, using lowercase alphanumerical and underscore (snake_case()).

• Parameters are optional. However, the parentheses of a parameters list is required.

• Parameters can either be named or not, but you can't have a mix of named and unnamed parameters.

The segmentation query syntax has limited support for function-type attributes.

This function returns a Boolean. Supported <operator> values are = and !=.

For example,

Where ID is a single value and List<ID> is a set of comma-separated values wrapped in parenthesis. An implicit OR between values is applied for lists.

Each ID is a product ID.

• A single value in a list is valid.

• Multiple values must be provided as a list. For example (1012132033639, 2012162031638, 32421429314657).

• A list can contain up to 500 IDs.

For example,

Omitting the id parameter returns results for all products.

Where tag is a product tag.

• A single tag can be provided.

For example,

Where

This parameter is the start date of when the product was purchased.

For example,

Where

This parameter is the end date of when the product was purchased.

For example,

Omitting the since and until parameters returns results from all time.

Shopify Email functions enable segmenting on Shopify Email events. They are namespaced under the shopify_email. prefix. The following events are supported:

• bounced
• clicked
• delivered
• marked_as_spam
• opened
• unsubscribed

All of these functions accept the same named parameters and return a Boolean. Supported <operator> values are = and !=.

For example,

Where ID is a single value and List<ID> is a set of comma-separated values wrapped in parenthesis. An implicit OR between values is applied for lists.

Each ID is a marketing activity ID to filter on.

• A single value in a list is valid.

• Multiple values must be provided as a list. For example (5240029206, 1932881090, 3250045832).

• A list can contain up to 500 IDs.

For example,

Omitting the activity_id parameter returns results for all Shopify Email activities.

Where

This parameter is the start date for the Shopify Email event.

For example,

Where

This parameter is the end date for the Shopify Email event.

For example,

Omitting the since and until parameters returns results from all time.

Integer attributes are int64 types that support specific operators and conditions.

• Conditions accept only integer numbers.

• Floats aren't valid.

• Minimum value of -9,223,372,036,854,775,808.

• Maximum value of 9,223,372,036,854,775,807, inclusive.

• Operators and functions returning a Float, for example COUNT, SUM, MAX, and MEDIAN, aren't supported for querying segments.

• Thousand separators, such as commas and spaces, aren't supported.

• Language-sensitive formatted numbers aren't supported.

List attributes represent multi-value attributes. The values in the list have a type.

The arguments the operator admits will depend on the type of the values within the list. An example is customer_tags, which is a multi-value list of strings. Its full type is therefore List<String>.

Lists support the following operators and conditions.

• The semantics of CONTAINS as applied to a LIST is that the arguments must be a subset of the preceding list attribute.

• Segmentation queries currently use multiple clauses with connectors to specify multiple values, rather than a multi-argument form.

• String conditions accept UTF-8 characters and must be wrapped with single quotes ', similar to the ENUM type in MySQL.

• Conditions are NOT case-sensitive and whitespaces are syntactically significant.

• Single quotes within a condition must be escaped (ie. '\'VIP\'').

• ID is an alias for an unsigned integer. The underlying data type is a uint64.

• Conditions don't have to be wrapped in single quotes '.

String attributes support specific operators and conditions.

• Conditions accept UTF-8 characters and must be wrapped around single quotes '.

• Conditions are case-sensitive and whitespaces are syntactically significant.

• Single quotes within a condition must be escaped. For example, '\'VIP\'').

• Conditions can be empty strings. The API verifies that the attribute exists and is an empty string.

• All operators/functions returning a String, such as CONCAT, SUBSTR, LOWER, UPPER, and TRUNCATE, are not supported for querying segments.

The following are syntax examples of grouped clauses.

Two clauses linked with AND

Two clauses linked with OR

Invalid query: Too many clauses (>10)

By default AND takes precedence over OR. Parenthesis are optional. For example, the following queries are equivalent.

Change implicit order of precedence: OR now takes precedence over AND