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:

sum_pageview_count 282778

Fetching data (SHOW)

There are a number of ways to select, filter and aggregate data using ShopifyQL.

Columns

Each schema is comprised of many columns.

SHOW utm_campaign_name, utm_campaign_term, utm_campaign_source

Aggregation Functions

A number of functions exist to aid with gathering or aggregating data.

function description
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
median median value of results
any returns one value from the results

Aliases

Commonly used aggregation expressions are provided as named aliases.

SHOW total_pageviews vs SHOW sum(pageview_count)

Named aliases on the visits schema:

Name Type Definition
avg_duration number avg(duration)
total_pageviews number sum(pageview_count)
total_purchases number sum(purchase_count)
total_sessions number count(pageview_count)
total_visitors number count(DISTINCT user_token)

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

Aggregate Conditions

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