Skip to main content

ShopifyQL reference

Note

The ShopifyQL API is being sunset as of version 2024-07. Please consider using alternative sources of data in the Admin GraphQL API for your use-cases, such as the Orders API, and the Web Pixels API. You can continue using ShopifyQL API on GraphQL API version 2024-04.

This page describes ShopifyQL syntax, keywords, and keyword parameters.

The examples on this page use the orders and products datasets in Shopify stores. For information about available metrics, dimensions, and aggregates, refer to the orders dataset, products dataset, or payment attempts dataset.

Anchor to SyntaxSyntax

ShopifyQL follows the syntax below. You can place the entire query on one line or on separate lines.

ShopifyQL

FROM { table_name }

SHOW { column1 AS { alias } , column2 AS { alias } , ... }

VISUALIZE { column1 AS { alias } , column2 AS { alias } , ... }

TYPE { visualization_type }

GROUP BY { dimension | date }

ALL { date }

WHERE { condition }

SINCE { date_offset }

UNTIL { date_offset }

DURING { named_date_range }

COMPARE TO { named_date_range | relative_date_range }

ORDER BY { column } ASC | DESC

LIMIT { number }

-- Single line comment

/*
Multi line comment
*/

Anchor to KeywordsKeywords

Keywords need to follow the syntax order, otherwise, errors occur.

This section describes the required keywords in ShopifyQL queries.

Anchor to Required keywordsRequired keywords

A ShopifyQL query must contain at least the FROM and SHOW keywords.

Using FROM and SHOW to return a numerical value of net sales

FROM orders
SHOW sum(net_sales)

Anchor to Keyword orderKeyword order

Keywords need to be in the following order:

  • FROM
  • SHOW | VISUALIZE
  • GROUP BY
  • WHERE
  • (SINCE & UNTIL) | DURING
  • COMPARE TO
  • ORDER BY
  • LIMIT

Anchor to FROMFROM

  • FROM { table_name }
  • FROM accepts one parameter, table_name, where table_name is a table.

Anchor to SHOWSHOW

  • SHOW { column1 AS { alias } , column2 AS { alias } , ... }
  • SHOW accepts any number of parameters, where each parameter is a column in a table or an expression.
    • Each parameter optionally accepts an alias using the AS keyword.

Using FROM to return the sum of net sales

FROM orders
SHOW sum(net_sales)

Anchor to ASAS

  • AS { alias }
  • AS accepts one parameter, which is an alias for a column name in a table, or an alias for the return value of an aggregate function. If an alias has a space in the name, then surround the alias with double quotes.
  • AS can used with both the SHOW and VISUALIZE keywords.

Using AS to alias the sum of net_sales as `Net Sales`

FROM orders
SHOW sum(net_sales) AS "Net Sales"
Note

The double quotes are necessary when the alias contains a space.

Anchor to VISUALIZEVISUALIZE

  • VISUALIZE { column1 AS { alias } , column2 AS { alias } , ... }
  • VISUALIZE accepts any number of parameters, where each parameter is a column in a table or an expression.
    • Each parameter optionally accepts an alias using the AS keyword.
  • VISUALIZE returns axis labels and data formatting information for creating data visualizations.
  • Optionally accepts a visualization_type using the TYPE keyword.

Return axis labels and data formatting for the sum of the net sales grouped by month for the last year

FROM orders
VISUALIZE sum(net_sales)
TYPE line
GROUP BY month ALL
SINCE -1y
UNTIL today

The sales are depicted as a single line, with the x-axis labeled as month, and the y-axis as sum_net_sales.

Anchor to TYPETYPE

  • TYPE { visualization_type }
  • TYPE accepts one parameter, visualization_type, where visualization_type is line or bar.
    • line returns a line graph.
    • bar returns a bar graph.
  • TYPE is an optional keyword. If TYPE isn't used, then ShopifyQL returns a visualization that's appropriate for the submitted query.

Return axis labels and data formatting for a line chart

FROM orders
VISUALIZE sum(net_sales)
TYPE line
GROUP BY month ALL
SINCE -1y
UNTIL today

Anchor to GROUP BYGROUP BY

  • GROUP BY { dimension | date }
  • GROUP BY accepts any number of parameters, where each is a dimension or time dimension. A dimension is a field in a table.
    • Each parameter optionally accepts an alias_name using the AS keyword.
  • If there isn't a return value for the specified dimension, expression, or date, then the dimension, expression, or date isn't returned. If you want to return the dimension, expression, or date when there's no data present, then you can use the ALL modifier.
Note

GROUP BY is similar to the SQL GROUP BY statement.

Using GROUP BY to sort by shipping country

FROM orders
SHOW sum(net_sales)
GROUP BY shipping_country

Using GROUP BY to sort by month

FROM orders
SHOW sum(net_sales)
GROUP BY month

Anchor to ALLALL

  • GROUP BY { dimension | date } ALL
  • ALL is an optional GROUP BY modifier which can only be used with a date.
  • The ALL modifier fills in zeros for any date where data isn't present.
  • The ALL modifier enables you to retrieve continuous date periods. This allows you to get continuous date periods without having to perform joins to date lookup tables.
  • When using the ALL modifier SINCE or DURING must also be specified.

Using GROUP BY with ALL modifier to retrieve the net sales for each hour in the last 24 hours

FROM orders
SHOW sum(net_sales)
GROUP BY hour ALL
SINCE -1d
UNTIL today
ORDER BY hour DESC

Using GROUP BY with and without an ALL modifier to retrieve all months for each shipping country

FROM orders
SHOW sum(net_sales)
GROUP BY shipping_country, month ALL
SINCE -1y
UNTIL yesterday
ORDER BY shipping_country ASC, month ASC

Anchor to WHEREWHERE

  • WHERE { condition }
  • WHERE accepts one parameter, condition, where condition is an expression that consists of one or more comparison operators and logical operators.
  • WHERE filters the results of an entire query based on the specified condition.

Using WHERE and the = operator to filter the gross sales for all orders that were shipped to the United States

FROM orders
SHOW sum(gross_sales)
WHERE shipping_country = 'United States'

Anchor to SINCE and UNTILSINCE and UNTIL

  • SINCE { date_offset }

    • SINCE accepts one parameter, date_offset, where date_offset is a date range operator. The date range operator that's used for start_date_offset sets a starting date in a date range. This date is included in the range.

  • UNTIL { date_offset }

    • UNTIL accepts one parameter, date_offset, where date_offset is a date range operator. The date range operator that's used for end_date_offset sets an ending date in a date range. This date is included in the range.
    • If UNTIL isn't used in a query, then today is used as the ending date.

Using SINCE and UNTIL to filter the net sales since and including March 1

FROM orders
SHOW sum(net_sales)
SINCE 2020-03-01
UNTIL 2020-08-01

Using SINCE and UNTIL to filter the net sales since and including 30 days ago until and including yesterday

FROM orders
SHOW sum(net_sales)
SINCE -30d
UNTIL yesterday

Anchor to DURINGDURING

  • DURING { named_date_range }
  • DURING accepts one parameter, named_date_range, where named_date_range is a named date range operator.
  • DURING is an optional keyword that replaces SINCE and UNTIL statements.
  • This keyword helps to filter the query results for known time periods such as a calendar year or a specific month, or to filter the query results for date ranges that have different dates every year, such as Black Friday Cyber Monday.

Using DURING to filter the net sales during Black Friday Cyber Monday 2021

FROM orders
SHOW sum(net_sales)
GROUP BY day
DURING bfcm2021

Anchor to COMPARE TOCOMPARE TO

Using DURING and COMPARE TO to compare the net sales during Black Friday Cyber Monday 2022 and Black Friday Cyber Monday 2021

FROM orders
SHOW sum(net_sales)
GROUP BY day
DURING bfcm2022
COMPARE TO bfcm2021

Using SINCE and COMPARE TO to compare the net sales previous month to the same month in the previous year

FROM orders
SHOW sum(net_sales)
GROUP BY day
SINCE -1m
UNTIL -0m
COMPARE TO previous_year

Anchor to ORDER BYORDER BY

  • ORDER BY { column } ASC | DESC
  • ORDER BY can accept a list of one or more parameters. Each parameter consists of one or two elements, column, and an optional ASC or DESC. column is a column in a table, and ASC or DESC change the behavior of the returned results.
    • The ASC parameter is used after the column parameter, and indicates that the returned query results are sorted in an ascending order.
    • The DESC parameter is used after the column parameter, and indicates that the returned query results are sorted in a descending order.
    • If the ASC or DESC parameters aren't used, then the default sorting order is ascending.
  • If ORDER BY has multiple columns, then the results are first sorted by the first column, and then sorted by the second column, and so on.
  • The columns used in the ORDER BY parameters must be present in either the SHOW, BY or OVER parameter list.

Using ORDER BY to retrieve the net sales for each product

FROM products
SHOW sum(net_sales) as sales
GROUP BY product_title
ORDER BY sales DESC

Using ORDER BY with multiple fields to sort the net sales for all products and variants over the last year. The results are first sorted in alphabetical order by product title

FROM products
SHOW sum(net_sales)
GROUP BY product_title, product_type
SINCE -1y
UNTIL today
ORDER BY product_title, product_type DESC

Anchor to LIMITLIMIT

  • LIMIT { number }
  • LIMIT accepts one parameter, number, where number is a number that represents how many rows that you want the query to return.
  • LIMIT enables you to understand the data in each column without returning all of the data in the table. This is useful for larger tables where queries can take longer to return values. You can also use LIMIT with ORDER BY to create lists of the top or bottom-most X. For example, the top five products.

Using LIMIT to return a limited number of rows in a table to understand the relationship between the fields that you've selected

FROM products
SHOW net_sales, gross_sales, returns, taxes, discounts
GROUP BY product_title, product_type, product_id
SINCE -1y
UNTIL today
LIMIT 5

Using LIMIT with ORDER BY to create a list of the top-10 selling products over the last 3 months

FROM products
SHOW sum(gross_sales) as total_gross_sales
GROUP BY product_title
SINCE -3m
UNTIL today
ORDER BY total_gross_sales DESC
LIMIT 10

Anchor to Time dimensionsTime dimensions

The time functions are abstracted so you don't have to keep track of which date field corresponds to the grain of the available datasets. The following date fields are available as time dimensions in ShopifyQL:

Date fieldDescription
hourGroups by hour of calendar day
dayGroups by calendar day
weekGroups by calendar week
monthGroups by calendar month
quarterGroups by calendar quarter
yearGroups by calendar year
hour_of_dayGroups by 24 hours (1, 2, ..., 24)
day_of_weekGroups by day of week (M, T, W, ..., S)
week_of_yearGroups by week of year (1, 2, ..., 52)

Using the day time dimension to group by day over the last 30 days

FROM orders
SHOW sum(gross_sales)
GROUP BY day ALL
SINCE -30d
UNTIL today

Using the day_of_week time dimension to group by day_of_week over the last 30 days

FROM orders
SHOW sum(gross_sales)
GROUP BY day_of_week ALL
SINCE -30d
UNTIL today

Anchor to Date range operatorsDate range operators

You can use the following date range operators as well as a named date range operator in SINCE and UNTIL statements.

Date range operatorDescription
{-} {#} dThe number of calendar days ago from the day that the query was run
{-} {#} wThe number of calendar weeks ago from the day that the query was run
{-} {#} mThe number of calendar months ago from the day that the query was run
{-} {#} qThe number of calendar quarters ago from the day that the query was run
{-} {#} yThe number of calendar years ago from the day that the query was run
yyyy-mm-ddA specific date
Note

Date range operators truncate to the start of the time grain (day, week, month, quarter, and year) when used with SINCE and to the end of the time grain when used with UNTIL.

Using date range operators to get the gross sales from 2020-01-01 through 2022-10-30 (Assuming current date of 2022-11-08)

FROM orders
SHOW sum(gross_sales)
SINCE -2y
UNTIL -2w

Anchor to Named date range operatorsNamed date range operators

SINCE and UNTIL, DURING, and COMPARE TO accept any of the following named date range date operators below:

Date Range OperatorDescription
todayThe day that the query was run
yesterdayThe previous 24-hour period from the time that the query was run
this_weekThe current calendar week
this_monthThe current calendar month
this_quarterThe current calendar quarter
this_yearThe current calendar year
last_weekThe previous calendar week
last_monthThe previous calendar month
last_quarterThe previous calendar quarter
last_yearThe previous calendar year
bfcm2022November 25 to November 28 2022
bfcm2021November 26 to November 29 2021
bfcm2020November 27 to November 30 2020
bfcm2019November 29 to December 2 2019
bfcm2018November 23 to November 26 2018
bfcm2017November 24 to November 27 2017
bfcm2016November 25 to November 28 2016

Anchor to Relative date range operatorsRelative date range operators

Relative operators return the same length of time as the base date range, shifted back by the specified period. COMPARE TO accepts the following relative date range operators:

Date Range OperatorDescription
previous_periodOne period before the base date range
previous_yearOne year before the base date range

The following table demonstrates how previous_period and previous_year work with base periods specified by DURING:

DURINGCOMPARE TO previous_periodCOMPARE TO previous_year
todayyesterdaysame date, in the previous year (Feb 29 = Mar 1, Feb 28 = Feb 28)
yesterdayday before yesterdayyesterday, in the previous year (Feb 29 = Mar 1, Feb 28 = Feb 28)
this_weeklast_weeksame week of year as this_week but the in previous year
last_weekweek before last_weeksame week of year as last_week but the in previous year
this_monthlast_monthsame month as this_month but in the previous year
last_monthmonth before last_monthsame month as last_month but in the previous year
this_quarterlast_quartersame quarter as this_quarter but in the previous year
last_quarterquarter before last_quartersame quarter as last_quarter but in the previous year
this_yearlast_yearlast_year
last_yearyear before last_yearyear before last_year
bfcm2022bfcm2021bfcm2021
bfcm2021bfcm2020bfcm2020

The following table demonstrates how previous_period and previous_year work with base periods specified by SINCE and UNTIL:

SINCEUNTILCOMPARE TO previous_periodCOMPARE TO previous_year
todaytodayyesterdaysame date, in the previous year (Feb 29 = Mar 1, Feb 28 = Feb 28)
yesterdayyesterdayday before yesterdayyesterday, in the previous year (Feb 29 = Mar 1, Feb 28 = Feb 28)
this_weekthis_weeklast_weeksame week of year as this_week but the in previous year
bfcm2022bfcm2022bfcm2021bfcm2021
yesterdaytodaythe same length of time as the base period but before the base periodthe same two days but in the previous year
-2m1mthe same length of time as the base period but before the base periodthe start of the previous two months to the end of last month but in the previous year
-1mthis_weekthe same length of time as the base period but before the base periodthe start of last month to the end of this week but in the previous year
2021-11-032022-11-15the same length of time as the base period but before the base periodinvalid, the period of 377 days is greater than a single year
Note

When using SINCE and UNTIL with COMPARE TO previous_period the previous period will be precisely the same length of time as defined in the SINCE and UNTIL base period. This is true even in special cases such as leap years, daylight savings, and months with varying number of days.

Using SINCE and UNTIL with COMPARE TO previous_period. The base period is 27 days long therefore the previous_period will be 2022-01-05 to 2022-02-01

FROM orders
SHOW sum(net_sales)
SINCE 2022-02-01
UNTIL 2022-02-28
COMPARE TO previous_period

Anchor to Comparison operatorsComparison operators

You can use the following comparison operators in WHERE statements.

Comparison operatorDescription
=Equal to
!=Not equal to
<Less than
>Greater than
<=Less than or equal to
>=Greater than or equal to

Anchor to Logical operatorsLogical operators

You can use one or more of the following logical operators in WHERE statements.

Logical operatorDescription
ANDReturn all rows where the conditions that are separated by an AND are satisfied
ORReturn all rows where either of the conditions that are separated by an OR are satisfied
NOTReturn all rows where the conditions aren't satisfied

Using WHERE and the = operator to filter the gross sales for all orders that were shipped to the United States and sold on the Online Store or POS sales channels

FROM orders
SHOW sum(gross_sales)
WHERE shipping_country = 'United States' AND (sales_channel = 'Online Store' OR sales_channel = 'Point of Sale')

Anchor to Mathematical operatorsMathematical operators

You can use the following mathematical operators on numerical data.

Mathematical operatorDescription
+Add two numbers
-Subtract two numbers
*Multiple two numbers
/Divide two numbers

Using the addition and division operators to calculate order values for each billing region over the last year

FROM orders
SHOW sum(net_sales) + sum(returns) AS total_sales , (sum(net_sales) + sum(returns))/sum(orders) AS sales_per_order
GROUP BY billing_region
SINCE -1y
UNTIL today

Anchor to Aggregate functionsAggregate functions

You can use the following functions to aggregate columns, and group columns by dimensions.

Aggregate functionDescription
count()Return the number of instances in a result set
sum()Return the sum of values in a result set
min()Return the lowest value in a result set
max()Return the highest value in a result set
avg()Return the average value in a result set

The sum(), min(), max(), and avg() functions can only be used with numerical values, while count() can be used to count different instances of dimensional attributes. You can't use aggregated fields as arguments in the functions.

Using the sum() function on an aggregated field result in an error since that field has already been aggregated

FROM orders
SHOW sum (average_order_value)

The following query returns the aggregated sum of net sales and ordered quantity as a result of the sum() function, and the count of cities as a result of the count() function. These metrics are broken down by region for all regions in the United States in 2021.

Valid way to mix aggregated fields with aggregate functions

FROM orders
SHOW sum(net_sales), sum(ordered_product_quantity), count(billing_city) AS number_of_cities, average_order_value
GROUP BY billing_region
WHERE billing_country = 'United States'
SINCE 2021-01-01
UNTIL 2021-12-31

Anchor to CommentsComments

Single line comments start with -- and end at the end of the line.

Multi-line comments start with /* and end with */.

You can use comments to explain sections of ShopifyQL statements, or to prevent the execution of a ShopifyQL statement. Any text within a comment will be ignored during execution time.

Using comments to prevent execution of statements or add explanations

FROM orders
SHOW sum(net_sales), sum(ordered_product_quantity), count(billing_city) AS number_of_cities, average_order_value
-- the line below has been commented out and will not run
-- GROUP BY billing_region
WHERE billing_country = 'United States'
/*
this line and the two lines below it have been commented out and will not run
SINCE 2021-01-01
UNTIL 2021-12-31
*/
Was this page helpful?