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. Once 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
Term Definition
Dimension An 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.
Keyword ShopifyQL syntax that acts as a command to direct your query.
Metric A quantitative measurement of data. Common examples of metrics include total sales, number of orders, and gross profit.
Parameter ShopifyQL 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
Keyword Functional use
FROM Specifies which dataset table to select data from.
SHOW Selects the columns you want to extract from the dataset table.
VISUALIZE Displays your data in a line or bar visualization.
GROUP BY Groups the extracted data by a dimension or time dimensions.
WHERE Defines the condition or conditions that rows must satisfy to be selected.
SINCE Shows data since a specified time in the past.
UNTIL Shows data until a specified time in the past.
ORDER BY Specifies which column to order the data by.
LIMIT Limits the number of rows of data that are shown.
AS Renames 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
Operator Functional use
hour Grouping by hour of calendar day.
day Grouping by calendar day.
week Grouping by calendar week.
month Grouping by calendar month.
quarter Grouping by calendar quarter.
year Grouping by calendar year.
hour_of_day Grouping by 24 hours (1,2,...,24).
day_of_week Grouping by day of week (M,T,W,...,S).
week_of_year Grouping 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 operator Functional 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 operator Functional use
AND Filter to show all rows where the conditions separated by AND are satisfied.
OR Filter to show all rows where either of the conditions separated by OR are satisfied.
NOT Filter 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 operator Functional use
-{#}d Number of days ago from the day query is run.
-{#}w Number of weeks ago from the day query is run.
-{#}m Number of months ago from the day query is run.
-{#}q Number of quarters ago from the day query is run.
-{#}y Number of years ago from the day query is run.
yyyy-mm-dd A specific date.
today Date when the query is run.
yesterday The previous 24h period from the time the query is run.


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 the sum of 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 operator Functional 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 operator Functional 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.

Ready to start selling with Shopify?

Try it free