Skip to main content

About ShopifyQL

ShopifyQL is Shopify's commerce-focused query language that powers analytics and customer segmentation across the platform. Use ShopifyQL to build custom reports, analyze store performance, and gain insights from your merchant data.

Unlike general-purpose SQL, ShopifyQL abstracts away database complexity and provides built-in support for common commerce patterns like time-series analysis, currency handling, and multi-store reporting.

ShopifyQL enables you to:

  • Query store data across sales, orders, products, customers, and sessions.
  • Analyze trends with built-in time-series support and date range comparisons.
  • Build visualizations directly within queries for charts and tables.
  • Create customer segments using sophisticated filtering and matching patterns.

Anchor to How ShopifyQL worksHow ShopifyQL works

ShopifyQL queries follow a declarative structure where you specify what data you want rather than how to retrieve it. The language handles joins, aggregations, and data formatting automatically.

Anchor to Basic query structureBasic query structure

Every ShopifyQL query requires two keywords: FROM to specify the data source, and SHOW to select the metrics and dimensions to display:

ShopifyQL query

FROM sales
SHOW total_sales

Anchor to Adding dimensions and filtersAdding dimensions and filters

Break down metrics by dimensions using GROUP BY, and filter results using WHERE:

ShopifyQL query

FROM sales
SHOW total_sales, product_title
WHERE billing_country = 'United States'
GROUP BY product_title
ORDER BY total_sales DESC
LIMIT 10

Anchor to Time-based analysisTime-based analysis

ShopifyQL provides powerful time-based filtering with SINCE, UNTIL, and DURING keywords:

ShopifyQL query

FROM sales
SHOW total_sales
TIMESERIES day
DURING last_month

Compare periods using COMPARE TO:

ShopifyQL query

FROM sales
SHOW net_sales
TIMESERIES month WITH PERCENT_CHANGE
DURING this_quarter
COMPARE TO previous_year

Anchor to Where you can use ShopifyQLWhere you can use ShopifyQL

ShopifyQL is available through multiple surfaces across Shopify:

Shopify admin

Access ShopifyQL through the code editor in the Shopify admin with syntax highlighting, autocompletion, and real-time visualization.

GraphQL Admin API

Build apps that query analytics data programmatically using the shopifyqlQuery endpoint.

Python SDK and CLI

Access ShopifyQL data through a dedicated Python SDK with pandas integration designed for analytics workflows.

Anchor to Key capabilitiesKey capabilities

Anchor to Data sourcesData sources

ShopifyQL supports querying from multiple data models:

TableDescription
salesTransaction and revenue data.
ordersOrder information and fulfillment.
productsProduct catalog and inventory.
customersCustomer profiles and segments.
sessionsStorefront visitor sessions.

Query multiple tables in a single query with implicit joins:

ShopifyQL query

FROM sales, sessions
SHOW day, total_sales, sessions
GROUP BY day
SINCE last_month

Anchor to Time dimensionsTime dimensions

ShopifyQL abstracts date handling with built-in time dimensions:

DimensionDescription
second, minute, hourSub-day granularity.
day, week, month, quarter, yearCalendar periods.
hour_of_day, day_of_weekCyclical time patterns.
week_of_year, month_of_yearAnnual patterns.

Anchor to Named date rangesNamed date ranges

Use intuitive named ranges instead of calculating dates:

ShopifyQL query

FROM sales
SHOW total_sales
DURING bfcm2024
COMPARE TO bfcm2023

Available named ranges include: today, yesterday, this_week, last_week, this_month, last_month, this_quarter, last_quarter, this_year, last_year, this_weekend, last_weekend, and bfcm{year}.

Anchor to VisualizationsVisualizations

Embed visualization instructions directly in queries:

ShopifyQL query

FROM sales
SHOW total_sales
GROUP BY product_title
VISUALIZE total_sales TYPE bar MAX 10

Supported visualization types:

  • Charts: bar, line, stacked_bar, stacked_area, donut, histogram
  • Tables: table, list, list_with_dimension_values
  • Specialized: funnel, heatmap

Anchor to Multi-store reportingMulti-store reporting

Organizations with multiple stores can query across their entire portfolio using FROM ORGANIZATION:

ShopifyQL query

FROM ORGANIZATION sales
SHOW total_sales

Break down results by store using the shop_name dimension:

ShopifyQL query

FROM ORGANIZATION sales
SHOW total_sales
GROUP BY shop_name

Filter to specific stores using shop_id:

ShopifyQL query

FROM ORGANIZATION sales
SHOW total_sales
WHERE shop_id IN (10002, 20023, 24211)
GROUP BY shop_name

Multi-store queries use the currency and timezone of the current store by default. Override these with WITH CURRENCY and WITH TIMEZONE:

ShopifyQL query

FROM ORGANIZATION sales
SHOW total_sales
WITH CURRENCY 'USD', TIMEZONE 'America/New_York'

Anchor to TOP N analysisTOP N analysis

Control result cardinality with per-dimension limits:

ShopifyQL query

FROM sales
SHOW total_sales
GROUP BY month, TOP 5 product_title WITH TOTALS

Use OVERALL for cross-dimension top items, and ONLY to exclude the "Other" bucket:

ShopifyQL query

FROM sales
SHOW total_sales
GROUP BY ONLY TOP 3 product_title OVERALL, TOP 3 shipping_country

Anchor to Common query patternsCommon query patterns

Anchor to Sales performance by periodSales performance by period

ShopifyQL query

FROM sales
SHOW gross_sales, net_sales, total_sales, orders
TIMESERIES month
SINCE startOfYear(-1y) UNTIL today
ORDER BY month

Anchor to Top products with comparisonTop products with comparison

ShopifyQL query

FROM sales
SHOW total_sales, product_title
GROUP BY product_title WITH PERCENT_CHANGE
DURING this_month
COMPARE TO previous_month
ORDER BY total_sales DESC
LIMIT 10

Anchor to Regional breakdownRegional breakdown

ShopifyQL query

FROM sales
SHOW total_sales
WHERE billing_country IN ('United States', 'Canada')
GROUP BY billing_country, billing_region WITH TOTALS, GROUP_TOTALS
ORDER BY total_sales DESC

Anchor to Customer acquisition trendsCustomer acquisition trends

ShopifyQL query

FROM customers
SHOW new_customers, returning_customers
TIMESERIES week
SINCE -12w UNTIL today
VISUALIZE new_customers, returning_customers TYPE stacked_area

Anchor to Product performance funnelProduct performance funnel

ShopifyQL query

FROM sessions
SHOW sessions, product_views, add_to_carts, checkouts, orders
GROUP BY product_title
HAVING orders > 0
ORDER BY sessions DESC
LIMIT 20
VISUALIZE sessions, product_views, add_to_carts, checkouts, orders TYPE funnel

Anchor to Best practicesBest practices

Anchor to Query optimizationQuery optimization

  • Use appropriate date ranges: Narrow your SINCE/UNTIL range to only the data you need.
  • Limit dimensions: Each GROUP BY dimension increases query complexity.
  • Filter early: Apply WHERE conditions to reduce the data set before aggregation.
  • Use LIMIT: Restrict result sets, especially for exploration queries.

Anchor to Readable queriesReadable queries

  • Use aliases: Give metrics descriptive names with AS.
  • Format consistently: Place each keyword on its own line for complex queries.
  • Add comments: Document complex logic with -- single-line or /* */ block comments.

ShopifyQL query

-- Top selling products by category
FROM sales
SHOW total_sales AS "Revenue", orders AS "Order Count", total_sales / orders
AS "Average Order Value"
WHERE product_type IS NOT NULL
GROUP BY product_type, product_title
HAVING total_sales > 1000
ORDER BY total_sales DESC
LIMIT 25

Anchor to Next stepsNext steps

Was this page helpful?