Introducing ShopifyQL: Our New Commerce Data Querying Language - Shopify

Commerce Data, Without the Query-Language Barrier

Data-informed decisions drive business growth, but for most merchants, the path from raw data to insight is blocked by SQL’s complexity and the need to understand an entire data lifecycle. That’s why Shopify built ShopifyQL, a querying language designed specifically for commerce exploration, now available to Shopify Plus merchants. It aims to lower the barrier to entry by combining simple syntax, direct visualization, and commerce-specific features like built-in period comparisons and named date ranges.

Standard dashboards and reports can tell you what happened, but they can’t explain why. Deep dives into underlying datasets often require support from a centralized data team, leaving marketing, merchandising, and operations teams in competition for resources — or worse, making decisions without data. ShopifyQL addresses this by putting data exploration power into the hands of merchants, letting them query their online store data directly.

Simplifying the Querying Fundamentals

ShopifyQL retains the core concepts of a querying language but simplifies both the syntax and the structure. The general syntax follows a familiar pattern:

FROM {table_name}
SHOW|VISUALIZE {column1, column2,...}
TYPE {visualization_type}
AS {alias1,alias2,...}
BY {dimension|date}
WHERE {condition}
SINCE {date_offset}
UNTIL {date_offset}
ORDER BY {column} ASC|DESC
COMPARE TO {date_offset}
LIMIT {number}

The language keeps building blocks users expect — WHERE for filtering, ORDER BY for sorting, LIMIT for row counts, and GROUP BY, shortened to BY, for aggregation — while making other parts more intuitive. SELECT is renamed SHOW, emphasizing that data needs to be seen to be understood. Critically, FROM now comes before SHOW, letting users pick the dataset they care about first, then choose their fields.

Turning Queries Into Charts

Traditional querying interfaces often return plain tables, forcing users to copy data into other tools to create visualizations. ShopifyQL’s VISUALIZE keyword removes that manual step altogether. Merchants can display their data in a chart format directly from the query:

Smart defaults can even shorten that query, keeping the logic explicit but cutting down on lines of code:

FROM sales
VISUALIZE total_sales, gross_sales
BY month
WHERE product_category = ‘Shoes’
SINCE -13m


Built for Commerce Comparisons

Ecommerce analysis is deeply rooted in comparing periods — year-over-year, month-over-month, or against a custom range. In SQL, that often means complicated self joins or window functions. ShopifyQL reduces this to a single COMPARE TO statement, letting users compare two periods without extra modeling:

Alongside comparisons, ShopifyQL recognizes that commerce has its own calendar. Instead of manually looking up dates for Black Friday Cyber Monday or holiday sales, a DURING statement, combined with Shopify-provided date ranges, handles the filtering for you:

Streamlined Data Models

ShopifyQL works with purpose-built exploration data models designed to answer questions across products, orders, and customers. Because SQL is inflexible, efforts to make data accessible usually focus on flat tables, which can answer simple questions but fall apart on deeper dives. The data models behind ShopifyQL are built around several principles:

• Single flat tables. All data is aggregated to a lowest domain dimension grain and time attribute, eliminating the need for joins, common table expressions, or window functions. A product’s pageviews and inventory, for instance, sit together in one table.
• Fully additive metrics. All metrics can be aggregated across every dimension without worrying about conformed dimensions. This makes schemas easy to understand, even for those with no data modeling background.
• Support for overlapping use cases. Total sales can be calculated across product, order, or customer datasets, letting users reconcile their work and build confidence in the queries they write.

Without the leverage of a custom querying language, these design characteristics would demand complex queries that still limit exploration. With ShopifyQL, more of the merchant’s questions — and explorations — are within reach.

Where ShopifyQL Fits in the Platform

ShopifyQL was built to serve merchant-facing commerce analytics for three audiences: Shopify Plus merchants, third-party developer partners, and Shopify’s own internal product teams. Each group interacts with the language through a different entry point.

A Notebooks app for merchants

Using the ShopifyQL APIs, Shopify built a first-party app called ShopifyQL Notebooks. It gives Shopify Plus merchants a traditional notebook experience for writing ShopifyQL queries — useful for exploring data, digging into specific questions, and assembling data-driven narratives. The app was introduced as part of the Summer 2022 Editions release.

GraphQL endpoints for developers

For third-party developers, Shopify exposes ShopifyQL and its underlying datasets through GraphQL endpoints. Partners can submit ShopifyQL queries via these APIs and receive the resulting data directly in the API response. The advantage is that developers don’t need to model raw data themselves — they can query modeled data and save engineering time building analytics apps. Shopify’s API documentation has more detail on the GraphQL interface.

The backbone for Shopify’s own analytics

Shopify’s internal teams plan to use ShopifyQL to power all analytical experiences in Shopify Admin, the backend where merchants manage their stores. The goal is to standardize merchant-facing analytics across the business. Because Shopify’s own engineers are also users of the language, they can spot gaps and iterate on the language more quickly than they could with a purely external product.

Planned Language Features

The ShopifyQL roadmap includes additions aimed at making queries simpler and more expressive:

• More visualizations. Line and bar charts are the baseline, but the team wants more options for uncovering insights. Planned chart types include dual axis charts, funnels, annotations, scatter plots, and donut charts.
• Pivoting. Breaking down a metric by dimensional attributes in a columnar fashion is currently complicated in traditional SQL. ShopifyQL aims to simplify this so users can chart trends of dimensional attributes across time for a specific metric with a single query.
• Aggregate conditions. Similar to SQL’s HAVING clause, this feature will let users filter queries on an aggregate condition. Unlike SQL, ShopifyQL will support this pattern directly in the WHERE clause, eliminating the need for extra syntax or keyword ordering complexity.

As the language evolves, Shopify’s focus stays on making commerce analytics more accessible to data-driven decision makers — whether they are merchants exploring their own store data, partners building analytics apps, or internal teams shipping merchant-facing features.