In Shopify, you can use reports in the Shopify admin to check a wide range of data, including sales trends, performance by product, performance by sales channel, and customer purchase trends. Standard reports alone let you check a lot of information, but if you want to “look only at specific products,” “compare sales by sales channel,” or “check customer purchase trends in detail,” you need to adjust the report’s aggregation conditions.
That is where ShopifyQL comes in. ShopifyQL is a query language for aggregating and analyzing Shopify store data, and it is used to create reports and customer segments.
This article explains ShopifyQL and the ShopifyQL query editor used in reports in the Shopify admin. ShopifyQL Notebooks, which are available to Plus stores, are not covered here.
If you want to understand Shopify store analytics as a whole, see the related article below.
What is ShopifyQL?
Even if ShopifyQL itself is unfamiliar, it is used in features that you may use casually in your day-to-day work. First, let’s look at which features use ShopifyQL.
ShopifyQL stands for Shopify Query Language
ShopifyQL (Shopify Query Language) is a query language used to retrieve the information you need from Shopify store data.
A query is a rule that specifies aggregation conditions for data. For example, you can use ShopifyQL to specify conditions such as “I want to see total sales,” “I want to break down sales by product,” or “I want to see only data from the past 30 days.”
However, ecommerce operators do not need to memorize every part of the syntax. In Shopify reports, when you use the configuration panel or filters, the ShopifyQL also changes accordingly. A practical way to start is to review the ShopifyQL in existing reports and make small changes little by little.
ShopifyQL is used in store analytics and customer segments
ShopifyQL is used as the language for collecting and aggregating data on Shopify, such as in Analytics reports and customer segments.
When you open a report from “Analytics” in the Shopify admin, you can view the ShopifyQL that makes up the report.
Shopify report: report query for items purchased together
Shopify reports let you check a wide range of data, including sales, orders, products, sales channels, regions, and customers. Viewing a report query in the ShopifyQL query editor makes it easier to understand what conditions an existing report is based on.
ShopifyQL is also used to create and modify customer segments. A customer segment is a feature that groups customers who match certain conditions, such as purchase count, amount spent, region, or email subscription status. For example, you can use it to extract customers who have purchased a certain number of times or more, or customers who live in a specific region.
On the customer segment editing screen, the conditions are represented as a ShopifyQL query.
Customer segment: segment conditions for users with many past orders
For more details about customer segments, see the article below.
Differences between ShopifyQL and SQL
Because the names are similar, some people may think of ShopifyQL as a database operation language like SQL. However, ShopifyQL is a language built for Shopify reports and analytics.
SQL is a language used to freely retrieve data from general databases. ShopifyQL, on the other hand, is designed to handle Shopify store analytics and customer data, and the available metrics, dimensions, and conditions are determined according to Shopify’s features.
For that reason, it may be easier to think of ShopifyQL not as something that directly queries external databases, but as a mechanism for checking and adjusting reports and customer segments inside Shopify.
What is the ShopifyQL query editor?
The ShopifyQL query editor is an editing screen used to adjust Shopify reports to match the conditions you want to view.
Standard reports let you check overall sales, but in actual operations, there are times when you want to “view sales by product,” “compare performance by sales channel,” or “check data for only a specific period.” With the ShopifyQL query editor, you can specify custom conditions and adjust how you view reports.
Shopify Analytics: report “New exploration” (new report creation screen)
From here, we will introduce what you can do with the ShopifyQL query editor in more detail.
You can check the contents of existing reports
By looking at the ShopifyQL query editor, you can check what conditions an existing report is based on.
For example, when you open a sales report, you can check whether you are looking at total sales, net sales, a product-by-product breakdown, or aggregation by time period. Because you can understand not only the tables and graphs in the report, but also the aggregation conditions, it becomes easier to understand what the numbers mean.
You can change metrics and dimensions
In the ShopifyQL query editor, you can change the metrics and dimensions displayed in a report.
Metrics are items that you check as numbers, such as sales, number of orders, and average order value. Dimensions are items used to break down data, such as product name, sales channel, region, and time period.
For example, you can view sales not only by month, but also by product or sales channel. By combining metrics and dimensions, the same sales data becomes easier to analyze from different angles.
You can narrow conditions to check the data you need
With ShopifyQL, you can filter for sales that include a specific product, orders from a specific country, data from a specific period, and more.
In ecommerce operations, looking only at overall sales can sometimes make it difficult to understand the issues. By narrowing conditions by sales channel, product, region, time period, and other factors, it becomes easier to review which initiatives led to results.
You can adjust reports to make them easier to read with tables and graphs
In Shopify reports, you can check data using tables and graphs. Changing the display based on what you want to see—such as a line graph for sales trends or a bar graph for sales comparisons by product—makes it easier to understand changes in the numbers.
However, depending on the combination of data you select, some graph formats might not be available. If a report can’t be displayed, review the combination of metrics and dimensions.
You can save conditions you check often
When you adjust a standard Shopify report, you can save the changes as a custom data exploration.
For example, if you save a sales report you check every month or a report for reviewing the performance of a specific channel, it becomes easier to check it next time. Because you don’t need to set the same conditions again every time, it is also easier to use for monthly checks and reviewing initiatives.
One convenient aspect of the ShopifyQL query editor is that you can create reports tailored to the conditions your company wants to check, rather than overwriting standard reports.
Sidekick can help you create ShopifyQL queries
With Sidekick, Shopify’s AI assistant, you can get help creating ShopifyQL queries in everyday language, such as “I want to see sales by product for the past 30 days” or “I want to compare sales by sales channel.”
Shopify Analytics: giving instructions to Sidekick and creating a report query
Sidekick is an AI assistant that can be used in the Shopify admin. By asking Sidekick in everyday language, you can get help creating a query that matches the contents of the report you currently have open.
However, always review the generated content. It is important to check whether the metrics, time period, and conditions in the displayed report match your intent, and then make edits as needed.
Sidekick is also introduced in another article.
Considerations when using ShopifyQL
The scope of ShopifyQL is limited to data on Shopify, and there are also limits to the conditions you can aggregate. Here are some points to keep in mind when using it.
Not all data can be combined freely
ShopifyQL is useful, but not all data can be combined freely. The data that can be aggregated is limited to the scope defined by Shopify’s reporting features and customer segments.
For example, aggregating a description that joins different data sources—such as “sales by product, return rate, and average order value only for customers who purchased three or more times in the past year”—might not be possible.
You don’t need to understand all of the syntax, but you should know that there are combinations ShopifyQL can’t handle.
If you want to learn more about the syntax, refer to the official documentation, “Overview of ShopifyQL syntax.”
How numbers appear changes depending on aggregation conditions
When viewing reports with ShopifyQL, you need to be aware that how the numbers appear changes depending on the aggregation conditions.
For example, the numbers appear differently in cases such as the following:
- Whether you look at gross sales before discounts, or net sales after discounts and returns
- Which date you use as the basis for aggregation, such as order date, shipping date, return date, or payment date
- Which sales channels you include, such as Online Store, POS, or social media sales
- Which dimension you use for classification, such as product, channel, region, or customer segment
- Whether you include or exclude canceled orders, test orders, and returned orders
When checking reports, review the metrics, time period, filters, and dimensions together. Rather than looking only at the numbers, it is important to understand what conditions were used to aggregate those numbers.
Integrating with external data may require APIs or BI tools
The ShopifyQL query editor is a feature for checking store data in the Shopify admin. If you want to combine and analyze data from advertising platforms, core systems, accounting data, and other sources, you may need Shopify APIs, CSV exports, BI tools, or similar options.
ShopifyQL is first and foremost a feature for organizing data inside Shopify so it is easier to view. If you want to perform more advanced analysis, consider integrations with external tools as well.
Summary
When you start using ShopifyQL, you do not need to memorize detailed syntax from the beginning. A good first step is to open an existing report and check the ShopifyQL that is displayed. You can gradually change metrics, dimensions, time periods, and conditions as needed, and save reports you use often as custom data explorations.
By checking customer data along with sales and product data, it becomes easier to use reports for daily sales checks and reviewing initiatives.
