Querying Commerce Data Without SQL: Inside ShopifyQL Notebooks
ShopifyQL Notebooks is a self-serve tool that lets merchants explore business metrics, understand what’s driving changes, and share findings as data stories. It runs on ShopifyQL, a query language purpose-built for commerce, and on data models designed around merchant business processes. Partners can use the same language and models to build apps for niche needs.
The core design goal was to let business-savvy users query data intuitively, without SQL expertise. That meant the models needed to encapsulate key metrics across different business processes. A natural question like “What were the sales and view-to-purchase conversion metrics for product X last month?” would normally require an analyst to join sales and marketing data. With the commerce data models, that question is directly answerable.
Getting there was not straightforward. The team had to decide which business questions merchants would ask, which columns would answer those questions, and what each row in the model should represent. Reconciling different granularities—sales data versus marketing data, for instance—was a particular challenge. The models also had to return results quickly, even for very large merchants.
What the Models Are Designed to Do
The data models given to merchants are meant to be purpose-driven, explorable, and extensible. Each of those qualities shaped different design decisions.
Purpose-driven means the models are opinionated about what they include. Every column should have a clear role, supporting commerce questions across domains like products, orders, and customers. Users should have a clear plan of action—know what question they’re answering and what data supports it.
Explorable means the models are built at the right granularity with useful dimensions and easily aggregated metrics. Choosing the right grain is a balance. Second-by-second data is rarely needed for decisions, but monthly may be too coarse. Shopify took a position on which dimensions matter for commerce decisions—things like “sales channel” and “product title”—so merchants can drill down where it counts.
Fully additive metrics make exploration simpler. For example, “Gross sales” can be summed across both time and product dimensions, whereas “customers who place an order” is only semi-additive—summing the column across days would double count customers who order multiple times. “Discount %” is non-additive: it can’t be summed at all without producing meaningless values.
Extensible models let users build their own metrics on top. If the provided “Net Sales” is calculated as gross sales minus returns and discounts, a user might want to also subtract shipping. The model should let them construct that variant and aggregate it by the same dimensions as the built-in metric.
The products, orders, and benchmarks datasets are examples of these commerce-focused models, designed with users of varying technical backgrounds in mind.
How the Models Were Built
The process that worked for Shopify’s data engineering team had six steps.
1. Start with Business Questions
The first step is to determine which metrics and dimensions matter most to users. Shopify gathered input from Product Managers, UX Researchers, and domain experts. The questions should be actionable, like:
- What is the conversion rate of my top selling products? Even a small improvement here can have a large revenue impact.
- Which of my products are not selling well? List products viewed frequently but not actually top sellers.
The goal is to collect as many questions as possible, then break them down into a list of required data columns.
2. Create Mock Data
Mock data confirms the model is at the correct grain and has the right columns. Shopify’s team would manually create data with several unique values per column—often starting in Google Sheets—then load it into a query engine like BigQuery or Trino.
Once the data is queryable, each business question should have a corresponding query. In ShopifyQL Notebooks these are called “templates,” which demonstrate what the data can answer. Writing actual ShopifyQL queries against mock data also verified that the language’s syntax and aggregate functions could handle the questions.
3. Find the Data
With the model designed, the next step is to inventory all upstream tables needed to build it. If any required data doesn’t exist, a plan for creating it is necessary.
4. Assess Data Quality and Consistency
Before finalizing the model, Shopify analyzed column distributions to identify outliers, missing data, and invalid values. The team recommends exploratory data analysis for this purpose.
Consistency matters when the same data appears in multiple places. A merchant seeing total views for a product in one part of the Shopify admin should see the same number elsewhere. When discrepancies exist, it’s essential to communicate why.
5. Assess Model Freshness
The data must be fresh enough to be useful. A model is only as fresh as its least fresh upstream component. Shopify retrieved the SLOs of each input dataset and computed an overall freshness target for the product model. Stakeholders should agree that the freshness level does not degrade the user experience.
6. Assess Model Performance
Performance comes in two forms: model build time and query performance.
Build time refers to how long the data pipeline takes to assemble the final model. Shopify improved this for the products model by switching from full-drop to incremental builds. Incremental models only load new data, processing far less at a time and building much faster. They are harder to maintain—new columns in incoming data may require a full historical rebuild, and incremental pipelines have additional dependencies to monitor—but in this case the performance gains justified the cost.
Query performance is how fast the query engine returns results. High-cardinality columns can slow down GROUP BY operations. Cold data—sales from a month ago, for example—is unlikely to change, and the finest grain needed is daily by product title. Shopify rolled those raw rows up into a single row per product per day in a separate table. Scanning fewer rows makes queries faster and improves the user experience considerably.
Querying the Products Model
The products data model is designed to answer merchant questions about how products are selling and converting. Here is a sample query checking product conversion rates:
That query enables quick answers to questions such as “what are my most popular products by traffic?”:
Merchants can also identify products that need attention by finding items abandoned at checkout:
These queries give merchants visibility into how customers engage with product pages and move through checkout. Spotting where visitors drop off before converting is a direct path to improving revenue.
Key Takeaways
The walkthrough demonstrates several principles for building commerce data models:
- Data models should be purpose-driven, explorable, and extensible.
- Start with business questions. Speak with stakeholders and domain experts to understand what the model should unlock.
- Mock data matters. Write real ShopifyQL queries against fake data to verify the model answers your questions.
- Discover available upstream data. Identify existing source models required to build your own.
- Assess model quality, freshness, and performance. These qualities drive the user experience.
