Shopify Query Language
The query language of the Analytics API is called ShopifyQL. A valid ShopifyQL query consists of parts similar to a valid SQL request. Let's look at an example:
SHOW sum(pageview_count) FROM visits SINCE -7d UNTIL -1d
This query will fetch the total pageviews for the last 7 days. The result would look something like:
Fetching data (SHOW)
There are a number of ways to select, filter and aggregate data using ShopifyQL.
Each schema is comprised of many columns.
SHOW utm_campaign_name, utm_campaign_term, utm_campaign_source
A number of functions exist to aid with gathering or aggregating data.
|sum||returns the summation of rows scanned|
|min||returns the minimum value of rows scanned|
|max||returns the maximum value of rows scanned|
|count||returns the count of objects|
|DISTINCT||returns the unique count of rows|
|avg||returns the average value of rows scanned|
Commonly used aggregation expressions are provided as named aliases.
SHOW total_pageviews vs
Named aliases on the
Result columns can be explicitly labeled with expression AS label, otherwise a label derived from the expression will be assigned which can often be a bit unwieldy.
SHOW sum(pageview_count) AS pageviews
ShopifyQL provides support for conditions within the
SHOW statement. These can be used to filter rows from inclusion into aggregation functions. Aggregate conditions make it possible to get results back in a single query that would usually require two or more.
Selecting schemas (FROM)
Schemas are selected by the key word
FROM. This works in a similar fashion to SQL. The following query is using the
SHOW sum(pageview_count) FROM visits
Shopify provides several schemas that you can fetch data from:
WHERE clause indicates the condition or conditions that rows must satisfy to be selected.
Unlike aggregate conditions, which only filter for one column of the result.
WHERE conditions filter for all columns that will be returned.
WHERE clauses can simplify queries, such as below:
Using aggregate conditions
Valid operators are:
Grouping by results (BY)
BY statement can be used to group the result-set by one or more columns. In the following example, we use the BY clause to group data based on day. Note that there is no row for 2016-02-01. This is because there is no data for that day. This behaviour differs from that of the OVER clause explained below.
Total pageviews by day (last 7 days)
SHOW sum(pageview_count) BY day(timestamp) FROM visits SINCE -7d UNTIL -1d
Grouping with backfill (OVER)
OVER clause can also be used to group result sets. The difference is that missing rows will be backfilled. In the following example, we use the OVER clause to group data based on day. Note that there is a backfilled row for 2016-02-01.
Total pageviews per day (last 7 days)
SHOW sum(pageview_count) OVER day(timestamp) FROM visits SINCE -7d UNTIL -1d
It is also possible to combine
Total pageviews by browser per day (last 7 days)
SHOW sum(pageview_count) OVER day(timestamp) BY ua_browser FROM visits SINCE -7d UNTIL -1d
A number of time functions exist:
year. These can be used in conjuction with
OVER to bucket data into a specific period.
SHOW sum(pageview_count) OVER month(timestamp) AS month
This will bucket pageview counts per month. Note that only some types of properties (generally only time related ones) can be backfilled.
Time Range (SINCE/UNTIL)
When writing ShopifyQL queries, the use of time/date constraints is strongly recommended. Not including the
UNTIL clauses will result in a query that scans a shops entire dataset, which can include years of data and take quite a long time.
Explicit dates are supported, such as
2016-01-28. There is also support for relative dates.
|d||day eg: SINCE -7d|
|w||week eg: SINCE -3w|
|m||month eg: SINCE -2m|
|y||year eg: SINCE -1y|
SINCE will be taken from the beginning of the day, while
UNTIL will be to the end of the day.
For example. Assume today is
SINCE -7d UNTIL -1d would be equivalent to
SINCE 2016-01-28 UNTIL 2016-02-03