Using ShopifyQL in Notebooks

ShopifyQL is Shopify’s query language built for commerce. Query languages are used to request and retrieve data from databases. You can use ShopifyQL with Shopify’s Notebooks app to explore your own business's database and retrieve the data that gives you a more in-depth understanding of your business.

For a full list of available values and dimensions that can be used in ShopifyQL Notebook queries, refer to the Orders schema and the Products schema.

ShopifyQL overview

Your store’s data is stored in database tables, structured in defined columns and rows. Columns define the type of information they contain, such as sales, and rows specify the actual value of the data type, such as 2,450 USD in sales.

To retrieve your data in a meaningful format, a query must be submitted to the database. A query is a question that’s asking for specific data as an answer. A query language, like ShopifyQL, is a standardized way of constructing that question. A query is made of keywords and their corresponding parameters. The combination of several keywords with specific parameters builds your query. After you’ve built your query, you can run it and receive an answer.

This is an example of ShopifyQL syntax, written out as a query. The keywords are bolded, their corresponding parameters are in brackets. The parameters shown here are placeholders:

  FROM { table_name }
  SHOW  { column1, column2,  ... }
  GROUP BY { dimension | date_group }
  WHERE { condition }
  SINCE { date_offset }
  UNTIL { date_offset }
  ORDER BY { column } DESC
  LIMIT { count }

Glossary of terms

Definitions of ShopifyQL terms
TermDefinition
DimensionAn attribute that segments data so that it can be sorted and presented more clearly. Common examples of dimensions include time, products, and places. Dimensions are used as parameters in ShopifyQL.
KeywordShopifyQL syntax that acts as a command to direct your query.
MetricA quantitative measurement of data. Common examples of metrics include total sales, number of orders, and gross profit.
ParameterShopifyQL syntax that identifies the database elements or details to be included in your query.

Keyword reference table

These are the keywords that you can use to write your query:

List of ShopifyQL keywords
KeywordFunctional use
FROMSpecifies which dataset table to select data from.
SHOWSelects the columns you want to extract from the dataset table.
VISUALIZEDisplays your data in a line or bar visualization.
GROUP BYGroups the extracted data by a dimension or time dimensions.
WHEREDefines the condition or conditions that rows must satisfy to be selected.
SINCEShows data since a specified time in the past.
UNTILShows data until a specified time in the past.
DURINGShows data since a specified time in the past.
COMPARE TOShows data until a specified time in the past.
ORDER BYSpecifies which column to order the data by.
LIMITLimits the number of rows of data that are shown.
ASRenames a column to a name of your choosing.

Writing ShopifyQL queries

ShopifyQL queries can range from basic, for high-level data insights, to comprehensive, for detailed insights. Each keyword has a specific function that builds out your query.

ShopifyQL basics: FROM and SHOW

Creating the simplest ShopifyQL query only requires two keywords: FROM and SHOW, written in that order. FROM, followed by a table name parameter specifies which table you want to query. SHOW, followed by a column name parameter specifies the columns you want to select.

For example, you can list all product IDs and their corresponding titles from the sales table by writing this query:

  FROM products
  SHOW product_id, product_title


Grouping data: GROUP BY

To segment a metric by a dimension, such as group sales by region, use the GROUP BY keyword. The GROUP BY keyword can be paired with any dimension parameter.

For example, a query that groups total shipping prices by billing country and region is written as:

  FROM orders
  SHOW shipping
  GROUP BY billing_country, billing_region

Here is another example query that uses a time dimension to show net sales by month:

  FROM orders
  SHOW net_sales
  GROUP BY month

The above query doesn't return any months where you have no sales. If you want a query that returns a complete, uninterrupted time period, then use the ALL modifier:

  FROM orders
  SHOW net_sales
  GROUP BY month ALL
  SINCE last_year
  UNTIL today

When you use the ALL modifier, you need to also specify SINCE and UNTIL. Note the ALL modifier only works with time dimension.

Time Dimensions

These are the time dimensions that you can use to group your data:

ShopifyQL time dimensions
OperatorFunctional use
hourGrouping by hour of calendar day.
dayGrouping by calendar day.
weekGrouping by calendar week.
monthGrouping by calendar month.
quarterGrouping by calendar quarter.
yearGrouping by calendar year.
hour_of_dayGrouping by 24 hours (1,2,...,24).
day_of_weekGrouping by day of week (M,T,W,...,S).
week_of_yearGrouping by week of year (1,2,...,52).


Filtering Data: WHERE

The WHERE keyword allows you to apply a dimensional filter to an entire ShopifyQL query.

For example, if you want to return net sales, grouped by month, but only for a specific region, then your query is:

  FROM orders
  SHOW net_sales
  GROUP BY month ALL
  WHERE billing_region = 'ohio'
  SINCE last_year
  UNTIL today

As you can see in the example above, you can filter the result set with the WHERE parameter even if that parameter isn't included in the SHOW or GROUP BY keywords. In this case, net sales is filtered over all months for orders with a billing address in Ohio only, even though billing_region isn't included in the result set.

Comparison operators

The WHERE keyword uses comparison operators to filter data. In the example above ‘=’ was used to specify that the query filters on a specific value, however, there are other operators available to you:

ShopifyQL comparison operators
Comparison operatorFunctional use
=equal to
!=not equal to
<less than
>greater than
<=less than or equal to
>=greater than or equal to

Logical operators

To further filter your data, you can add logical operators to your query. The ShopifyQL logical operators are:

ShopifyQL logical operators
Logical operatorFunctional use
ANDFilter to show all rows where the conditions separated by AND are satisfied.
ORFilter to show all rows where either of the conditions separated by OR are satisfied.
NOTFilter to only show rows where the conditions aren't satisfied, such as rows that don’t contain the specified value.

You can use multiple filters with the WHERE keyword by adding logical operators.

Adding to the example dataset query, to get a monthly grouping of net sales for all orders where the billing address was in Ohio and there was a discount applied , the query is:

  FROM orders
  SHOW net_sales
  GROUP BY month ALL
  WHERE billing_region = 'ohio' AND discounts > 0
  SINCE last_year
  UNTIL today


Date ranges: SINCE and UNTIL

If you want to filter your query by a date or some time period, then you can use the SINCE and UNTIL keywords and their associated parameters. These keywords are unique because they only filter time periods.

For example, this is a query to find net sales over the last 12 months, in Canada, starting with today:

  FROM orders
  SHOW net_sales
  GROUP BY month ALL
  WHERE billing_country = 'Canada'
  SINCE -12m
  UNTIL today

Offset operators

You can filter by specific dates, or by date offsets. The ShopifyQL offset operators are:

ShopifyQL offset operators
Offset operatorFunctional use
-{#}dNumber of days ago from the day query is run.
-{#}wNumber of weeks ago from the day query is run.
-{#}mNumber of months ago from the day query is run.
-{#}qNumber of quarters ago from the day query is run.
-{#}yNumber of years ago from the day query is run.
yyyy-mm-ddA specific date.
todayDate when the query is run.
yesterdayThe previous 24h period from the time the query is run.


Filtering on Date Ranges: DURING

The DURING keyword simplifies date filtering for date ranges. You can use the DURING keyword to filter the query results for a known time period such as a calendar year or specific month, or for date ranges that have different dates every year, such as Black Friday Cyber Monday. For example:

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

DURING accepts any of the following named date range operators:

ShopifyQL named date range operators
Date range operatorFunctional use
todayThe date when the query is run.
yesterdayThe previous 24h period from the time query is 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.


Comparing across date ranges: COMPARE TO

The COMPARE TO keyword allows you to compare data across the date range in DURING, and the one in COMPARE TO. It accepts any of the named date range operators listed in the DURING section. The operator used by COMPARE TO must be the same length in time as the one used by DURING. For example, DURING this_week COMPARE TO last_week is a valid combination but DURING this_week COMPARE TO last_month is not.

The following example compares net sales during Black Friday Cyber Monday 2022 with Black Friday Cyber Monday 2021.

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


Sorting data: ORDER BY

You can specify how you want to sort the data returned by your query using the ORDER BY keyword, and its parameters: ASC, for ascending order, and DESC, for descending order.

You can specify any metric or dimension you’re including in your query in the ORDER BY keyword, including multiple fields.

For example, this query returns net sales for each billing country and region, sorted in reverse alphabetical order by billing country, and then by billing region within each country.

  FROM orders
  SHOW net_sales
  GROUP BY billing_country, billing_region
  SINCE -1y
  UNTIL today
  ORDER BY billing_country, billing_region DESC

The order in which you write your metrics or dimension matters. If you specify multiple values for ORDER BY, then the sorting is applied to each metric or dimension in the order given.


Limit

The LIMIT keyword allows you to specify how many rows the query returns. This is useful when you simply want to understand the look of the data in each column. Or, you can combine it with ORDER BY to create top and bottom lists.

This example uses LIMIT and ORDER BY to create a list of top 10 selling products by quantity over the last 3 months:

  FROM products
  SHOW net_product_quantity
  GROUP BY product_title
  SINCE -3m
  UNTIL today
  ORDER BY net_product_quantity DESC
  LIMIT 10


Charting your queries: VISUALIZE and TYPE

The VISUALIZE keyword allows you to write a ShopifyQL query that displays data in a line or bar visualization.

The TYPE keyword is optional and must be accompanied by ‘line’ or ‘bar’ so that your query returns a visualization in either a line chart or bar chart, respectively. If TYPE isn’t included in your query, then ShopifyQL automatically decides the best visualization that suits your query. If your query can’t be visualized as written, then ShopifyQL returns tabular data.

For example, you can visualize your sales trend over the last year by month with a trend line. This query returns a time series chart displaying the gross sales by month over the last year. The gross sales are depicted by a single line, with the x-axis labeled as month and the y-axis labeled as gross sales:

  FROM orders
  VISUALIZE gross_sales
  TYPE line
  GROUP BY month ALL
  SINCE -1y
  UNTIL today


Mathematical Operators

ShopifyQL allows arithmetic operations with the metrics in your data. The following mathematical operators are available:

ShopifyQL offset operators
Mathematical operatorFunctional use
+Addition of two numbers.
-Subtraction of two numbers.
*Multiplication of two numbers.
/Division of two numbers.

For example, this query calculates order value for each region over the last year. When using mathematical operators with metrics, you can use the AS keyword to assign a new name to the new metric.

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


Functions

ShopifyQL functions allow you to aggregate columns similar to pivot tables in Microsoft Excel. Aggregating columns means combining them to create a new value. The following function operators are available in the current version of ShopifyQL:

ShopifyQL offset operators
Function operatorFunctional use
count()Count of instances in result set.
sum()Summation of values in result set.
min()Minimum value in result set.
max()Maximum value in result set.
avg()Average value in 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. Aggregated fields end in _sum, _count, or _percent.

For example, this query returns an error since total_sales has already been aggregated:

  FROM orders
  SHOW sum(total_sales)

Here is a valid query that mixes aggregated fields with aggregate functions:

  FROM orders
  SHOW average_order_value, sum(gross_sales)
  GROUP BY billing_region
  SINCE 2021-01-01
  UNTIL 2021-12-31

This query returns the aggregated sum of average order value, gross sales sum as a result of the sum function. These metrics are broken down by billing region for all orders placed in 2021.


Comments

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.

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

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

  FROM orders
  SHOW average_order_value, sum(gross_sales)
  -- 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
*/
Ready to start selling with Shopify?Try it free