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:
Glossary of 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:
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. |
DURING | Shows data since a specified time in the past. |
COMPARE TO | 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:
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:
Here is another example query that uses a time dimension to show net sales 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:
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:
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:
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:
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:
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:
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:
Offset operators
You can filter by specific dates, or by date offsets. The ShopifyQL offset operators are:
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. |
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:
DURING accepts any of the following named date range operators:
Date range operator | Functional use |
---|---|
today | The date when the query is run. |
yesterday | The previous 24h period from the time query is run. |
this_week | The current calendar week. |
this_month | The current calendar month. |
this_quarter | The current calendar quarter. |
this_year | The current calendar year. |
last_week | The previous calendar week. |
last_month | The previous calendar month. |
last_quarter | The previous calendar quarter. |
last_year | The previous calendar year. |
bfcm2022 | November 25 to November 28 2022. |
bfcm2021 | November 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.
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.
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:
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:
Mathematical Operators
ShopifyQL allows arithmetic operations with the metrics in your data. The following mathematical operators are available:
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.
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:
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:
Here is a valid query that mixes aggregated fields with aggregate functions:
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 */
.