About this guide
Quick answer:
Shopify’s built-in reports and ShopifyQL cover sales, orders and customers inside Shopify. They’re the place to start before adding a warehouse or writing your own SQL.
For questions that blend Shopify with ad spend, support, accounting or other sources, the usual setup is a connector that syncs Shopify into a data warehouse, where SQL builds the reports you need. This guide rebuilds the Sales Over Time report both ways and shows how to make the warehouse numbers match Shopify’s.
This tutorial features SQL queries written in Postgres-style SQL, which also runs on Amazon Redshift, Snowflake and DuckDB. If you’re on a Google BigQuery data warehouse, you’ll need to adjust the date functions, for example TIMESTAMP_TRUNC(created_at, MONTH).
You’ve perfected your Shopify store, you’ve put together a strategic marketing mix, and you’ve got a steady stream of sales coming in. You’re on the road to retail genius, and for good reason.
But if all that success also means that you find yourself:
- spending hours on your regular reporting each week…
- cobbling together data from all over the company via a brittle chain of spreadsheets…
- or just plain frustrated by a lack of visibility around your customers, orders, or inventory…
You are not alone. For many Shopify merchants, growth isn’t just a sign of success, it’s a problem to manage. But it shouldn’t be. By harnessing your Shopify data, you can make better business decisions more easily, and you don’t need an entire analytics team or an expensive consulting firm to do it.
In this guide, you’ll learn:
- Why canned Shopify reports just don’t cut it
- What a Shopify analytics stack looks like today
- How to create the Shopify reports you really need
- How to check your numbers against Shopify
Why aren’t built-in Shopify reports good enough?
Shopify dashboards are great when you’re starting out, but they can only take you so far. For example, Shopify’s built-in reporting can label customers by how recently, how often and how much they buy, but it can’t answer a question like, “Which of my customers are the most valuable once ad spend, support costs and returns are counted?”
If you’ve ever taken advantage of a $5-off coupon for a $6 item only to never return to that store again, you can see the importance of this kind of information. By identifying your most valuable customers, you can target your marketing and promotional efforts in a way that will encourage them to spend even more with you without subsidizing deal seekers who’ll only buy when your margins are at their thinnest.
ShopifyQL is Shopify’s SQL-like language for sales, orders, customers, marketing, inventory and payments. You can run it in the ShopifyQL editor in the Shopify admin, through shopifyqlQuery in the GraphQL Admin API (which requires read_reports), or with Shopify’s Python SDK and CLI. Its sales metrics include gross_sales, net_sales, sales_reversals and total_sales; rfm_group labels include Champions, Loyal, Needs attention and At risk.
But these queries only see Shopify data; cross-source questions need ad spend, support tickets and accounting brought together elsewhere. Report editing and custom report features vary by plan, so check Analytics > Reports in your admin before planning a custom report.
Plus, Shopify’s standard reporting doesn’t give you a competitive edge. Those prepackaged dashboards are available to thousands of other Shopify merchants, which means they’re all basing their strategy around the same stock insights.
Can’t I just hook Shopify to a BI tool like Tableau?
Just because you can doesn’t mean you should. The problem with connecting Shopify directly to your business intelligence tool is that while it might give you prettier visuals than the native Shopify reporting, it doesn’t actually offer more insight.
That’s because you’d still be looking at Shopify data in isolation rather than taking advantage of multiple data sources to really understand your customers, sales, and inventory.
By syncing all your data into a data warehouse, including sources like Google Analytics, Facebook Ads, Zendesk, and QuickBooks, you get a much more cohesive and meaningful view of your business. Plus, you’ll finally be working from a single source of truth (SSOT) that your entire company can trust.
What a Shopify Analytics Stack Looks Like Today
A warehouse setup for Shopify has four layers, and you can swap each without replacing the others. The pattern is ELT: load raw Shopify data first, then model it inside the warehouse.
- Connector: Syncs Shopify’s API into the warehouse on a schedule. Options include Fivetran, Airbyte, Hevo Data and the open-source Python library dlt.
- Warehouse: Stores the history and runs your SQL. Options include BigQuery, Snowflake and Amazon Redshift, with DuckDB or MotherDuck for smaller stores.
- Modeling: Turns raw tables into clean orders, refunds and daily sales. dbt is a common choice; Fivetran’s open-source
dbt_shopifypackage includes models such asshopify__ordersandshopify__daily_shop. - BI and activation: Dashboards use Looker Studio, Metabase, Microsoft Power BI or Tableau. Reverse ETL tools such as Hightouch send your modeled customer segments back to ad and email platforms.
Shopify’s REST Admin API has been legacy since October 1, 2024; new public apps must use GraphQL since April 1, 2025. Fivetran defaults to its GraphQL schema for Shopify connections created on or after August 1, 2025, so table names can differ between older and newer connections.
Apps can read only orders created in the last 60 days by default. Older history needs read_all_orders, the first scope to check if your backfill stops there.
How to Create a Shopify Sales Over Time Report
Once your Shopify data is in a warehouse, a whole new world of insights is at your fingertips. Whether you’re already familiar with or have been longing for Shopify’s Sales Over Time report, it’s incredibly helpful for gauging the health of your sales as well as identifying seasonal trends.
Start with ShopifyQL in the Shopify admin
The quickest version of this report needs no warehouse: the ShopifyQL editor returns it from the sales schema. The query groups the last 12 months by month: orders, gross_sales, discounts, sales_reversals, net_sales, shipping_charges, taxes, total_sales, net_items_sold and average_order_value. Shopify deprecated returns; sales_reversals covers value removed through refunds, returns, cancellations and edits.
FROM sales
SHOW orders, gross_sales, discounts, sales_reversals, net_sales,
shipping_charges, taxes, total_sales, net_items_sold, average_order_value
TIMESERIES month WITH TOTALS
SINCE -12m UNTIL today
ORDER BY month ASC
VISUALIZE net_sales TYPE line
These ShopifyQL numbers are the baseline the warehouse version below should match. The reconciliation checklist explains which dates, filters and line items to compare when totals differ.
Rebuild the report in your warehouse with SQL
To get a taste of this report in your warehouse, open your SQL editor and paste in this query:
/* BASIC SHOPIFY GROSS SALES BY MONTH */
SELECT date_trunc('month', o.created_at) AS "month",
count(o.id) AS total_orders,
sum(o.total_line_items_price) AS gross_sales,
sum(-o.total_discounts) AS discounts,
sum(o.total_tax) AS total_tax
FROM orders o
WHERE NOT o.test
GROUP BY 1
ORDER BY 1;
This example query will return four key metrics:
total_orders= the count of ordersgross_sales= sum of gross salesdiscounts= sum of discountstotal_tax= sum of taxes charged
The output uses a sample store’s data, not your own Shopify orders.
| month | total_orders | gross_sales | discounts | total_tax |
|---|---|---|---|---|
| 2026-07-01 | 46 | 9673.00 | -607.85 | 634.55 |
| 2026-08-01 | 41 | 6580.00 | -379.85 | 434.01 |
| 2026-09-01 | 29 | 3981.00 | -198.45 | 264.80 |
In this example, the GROUP BY function lets us see a rollup by month, but you can adjust that to show daily, weekly, quarterly, or even yearly data.
The queries use generic names: orders, order_lines, refunds, order_line_refunds and order_shipping_lines. Fivetran uses tables such as ORDER, ORDER_LINE, REFUND and TRANSACTION; Airbyte exposes streams such as Orders and Order Refunds. Match the query names to your connector’s warehouse tables. The WHERE NOT o.test filter removes test orders, which Shopify’s reports also exclude.
Most connectors make syncing Shopify data look simple, but there’s a lot going on behind the scenes. To really understand what’s happening in these queries, get a deep dive into how Shopify’s data is structured.
Now that you have a sense of how these queries work, let’s replicate the complete Sales Over Time report. To calculate the metrics the way Shopify does, you’ll need to join a few more tables into the query.
To get started, copy this SQL query and paste it into your SQL editor:
/* SHOPIFY SALES REPORT BY MONTH */
WITH sales AS (
/* ORDERS: dated by order date */
SELECT date_trunc('month', o.created_at) AS "month",
count(o.id) AS total_orders,
sum(o.total_line_items_price) AS gross_sales,
sum(-o.total_discounts) AS discounts,
sum(o.total_tax) AS order_tax,
sum(o.total_price) AS order_value
FROM orders o
WHERE NOT o.test
GROUP BY 1
),
units AS (
/* LINE ITEMS: units sold */
SELECT date_trunc('month', o.created_at) AS "month",
sum(li.quantity) AS units_sold
FROM order_lines li
JOIN orders o ON o.id = li.order_id
WHERE NOT o.test
GROUP BY 1
),
shipping AS (
/* SHIPPING LINES: charges after shipping discounts */
SELECT date_trunc('month', o.created_at) AS "month",
sum(sl.discounted_price) AS shipping_charges
FROM order_shipping_lines sl
JOIN orders o ON o.id = sl.order_id
WHERE NOT o.test
GROUP BY 1
),
reversals AS (
/* REFUNDS: dated by refund date, the way Shopify reports date them */
SELECT date_trunc('month', r.created_at) AS "month",
sum(-rli.subtotal) AS returns,
sum(-rli.total_tax) AS tax_returned,
sum(rli.quantity) AS units_returned
FROM refunds r
JOIN order_line_refunds rli ON rli.refund_id = r.id
JOIN orders o ON o.id = r.order_id
WHERE NOT o.test
GROUP BY 1
)
SELECT s."month",
s.total_orders,
s.gross_sales,
s.discounts,
coalesce(rv.returns, 0) AS "returns",
s.gross_sales + s.discounts + coalesce(rv.returns, 0) AS net_sales,
s.order_tax + coalesce(rv.tax_returned, 0) AS taxes,
coalesce(sh.shipping_charges, 0) AS shipping_charges,
s.gross_sales + s.discounts + coalesce(rv.returns, 0)
+ s.order_tax + coalesce(rv.tax_returned, 0)
+ coalesce(sh.shipping_charges, 0) AS total_sales,
coalesce(u.units_sold, 0) - coalesce(rv.units_returned, 0) AS net_quantity,
round(coalesce(u.units_sold, 0)::numeric / s.total_orders, 2) AS units_per_order,
round(s.order_value / s.total_orders, 2) AS average_order_value
FROM sales s
LEFT JOIN units u ON u."month" = s."month"
LEFT JOIN shipping sh ON sh."month" = s."month"
LEFT JOIN reversals rv ON rv."month" = s."month"
ORDER BY 1;
This query pulls information from several tables to return a whole lot more data, including details like returns, net sales, and shipping. To keep it simple, let’s take a look at one row from that table to get a sense of what you’d see:
| month | total_orders | gross_sales | discounts | returns | net_sales | taxes | shipping_charges | total_sales | net_quantity | units_per_order | average_order_value |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 2026-09-01 | 29 | 3981.00 | -198.45 | -112.10 | 3670.45 | 256.95 | 114.35 | 4041.75 | 60 | 2.14 | 143.51 |
The reversals step uses each refund’s created_at, not the order date. Shopify’s sales reports show a return as a negative value on the day it was processed: a March refund on a February order lowers March’s net_sales.
Units come from line item quantity, not a count of lines; shipping uses the charge after shipping discounts. The query omits refunded shipping and order adjustments for readability. The data model chapter linked above covers them.
Exploring this report can give you key insights into your business, from your average order size to how many returns you receive each month.
How to Check Your Numbers Against Shopify
Warehouse totals rarely match Shopify’s reports on the first run. The gap usually comes from a handful of known causes, checked in this order.
- Match the report and date basis: Compare
net_salesby order date, for example, without mixing definitions from different reports. - Use the store’s time zone: Convert timestamps before truncating to a day or month so orders near midnight match Shopify’s reporting period.
- Check order exclusions: Remove test orders but keep canceled ones. Shopify’s
gross_salesincludes pending, canceled and unpaid orders. - Use the refund date: Date refunds by their processing date, not the original order date, so a later return lowers the period in which it was processed.
- Compare a mismatched day: Check order IDs and line items, then compare discounts, refunds, shipping and taxes separately.
Fivetran’s dbt_shopify daily models truncate created_timestamp, while Shopify’s in-app reports use processed_timestamp; the package’s decision log notes possible discrepancies. The data model chapter explains these date fields.
How This Guide Is Organized
The chapters that follow build on the warehouse setup above, from customer segmentation to the data definitions behind your reports.
- RFM segments groups customers by recency, frequency and monetary value in SQL.
- Customer lifetime value covers LTV formulas and worked examples.
- Customer analytics covers the customer metrics worth tracking.
- The Shopify data model maps entities, keys and the revenue gotchas behind these queries.
Common questions
Are Shopify’s built-in reports enough for a small store?
Yes, they’re enough to start. Shopify’s built-in reports and ShopifyQL cover sales, orders and customers without a separate warehouse. A warehouse becomes worthwhile when you need to join Shopify with other sources, such as ad spend or accounting, or keep modeled history for your own reporting.
What is ShopifyQL?
ShopifyQL is Shopify’s SQL-like query language for commerce data. You can use its editor in the Shopify admin, Shopify’s Python SDK and CLI, or shopifyqlQuery in the GraphQL Admin API. API access requires the read_reports scope and Level 2 protected customer data access.
Why don’t my warehouse numbers match Shopify’s reports?
Differences usually come from the date basis, time zone or order filters. Remove test orders but keep canceled orders when comparing gross_sales, and date refunds by the refund date. Compare a mismatched day at line-item level. See How to Check Your Numbers Against Shopify above for the full checklist.
How far back can a connector pull Shopify orders?
By default, an app can read orders created in the last 60 days. Access to older orders needs the read_all_orders scope, declared alongside read_orders. If your connector’s backfill stops at that boundary, check its access scopes before treating the missing history as a reporting problem.
Where to next
Continue with the RFM segments chapter to group customers using the warehouse data behind these reports. The data warehouse guide covers storage options if you’re still choosing where that data should live. For the ingestion layer, ETL tools compares connectors that bring Shopify and your other sources into the warehouse.
Connectors, warehouses and BI tools that speak Shopify, with pricing and free tiers.
Browse the tools