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 useShopifyQLwith 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 theOrders schemaand theProducts 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,...}GROUPBY{dimension|date_group}WHERE{condition}SINCE{date_offset}UNTIL{date_offset}ORDERBY{column}DESCLIMIT{count}

Glossary of terms

Keyword reference table

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

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:

FROMproductsSHOWproduct_id,product_title

Grouping data: GROUP BY

To segment ametricby adimension, 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:

FROMordersSHOWshippingGROUPBYbilling_country,billing_region

Here is another example query that uses atime dimensionto show net sales by month:

FROMordersSHOWnet_salesGROUPBYmonth

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:

FROMordersSHOWnet_salesGROUPBYmonthALLSINCElast_yearUNTILtoday

When you use the ALL modifier, you need to also specifySINCEandUNTIL. Note the ALL modifier only works withtime dimension.

Time Dimensions

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

Filtering Data: WHERE

在关键字允许您应用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:

FROMordersSHOWnet_salesGROUPBYmonthALLWHEREbilling_region='ohio'SINCElast_yearUNTILtoday

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:

Logical operators

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

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:

FROMordersSHOWnet_salesGROUPBYmonthALLWHEREbilling_region='ohio'ANDdiscounts>0SINCElast_yearUNTILtoday

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:

FROMordersSHOWnet_salesGROUPBYmonthALLWHEREbilling_country='Canada'SINCE-12mUNTILtoday

Offset operators

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

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:

FROMordersSHOWsum(net_sales)GROUPBYdayALLDURINGbfcm2021

DURING accepts any of the following named date range operators:

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 theDURINGsection. 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_weekis a valid combination butDURING this_week COMPARE TO last_monthis not.

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

FROMordersSHOWsum(net_sales)GROUPBYdayALLDURINGbfcm2022COMPARETObfcm2021

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.

FROMordersSHOWnet_salesGROUPBYbilling_country,billing_regionSINCE-1yUNTILtodayORDERBYbilling_country,billing_regionDESC

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:

FROMproductsSHOWnet_product_quantityGROUPBYproduct_titleSINCE-3mUNTILtodayORDERBYnet_product_quantityDESCLIMIT10

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:

FROMordersVISUALIZEgross_salesTYPElineGROUPBYmonthALLSINCE-1yUNTILtoday

Mathematical Operators

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

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.

FROMordersSHOW(net_sales+returns)ASorder_value,orders,(net_sales+returns)/ordersASsales_per_orderGROUPBYbilling_regionSINCE-1yUNTILtoday

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:

Thesum,min,max, andavgfunctions can only be used with numerical values, whilecount可以用来计算不同实例啊f 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:

FROMordersSHOWsum(total_sales)

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

FROMordersSHOWaverage_order_value,sum(gross_sales)GROUPBYbilling_regionSINCE2021-01-01UNTIL2021-12-31

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

Comments

您可以使用注释来解释Shopif的部分yQL 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*/.

FROMordersSHOWaverage_order_value,sum(gross_sales)-- the line below has been commented out and will not run-- GROUP BY billing_regionWHEREbilling_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 */