Why build your own Shopify sales report?

Summary: Shopify's built-in "Total sales over time" report is a great starting point, but it can't be redefined, extended, or joined with data from outside Shopify. This guide shows you how to build a Shopify sales report with SQL, rebuilding that same report - gross sales, discounts, sales reversals, shipping, taxes, total sales, and average order value - as a single query that runs directly against your own warehouse. It's built on Shopify's own event grain - the order_agreement log - which is what makes the numbers actually match the admin instead of drifting a few percent. It's written for BigQuery but ports cleanly to Snowflake or Redshift, and the same four-step pattern (understand the logic → write the SQL → deploy → visualize) works for the bonus sales-by-product template further down.

Shopify's own sales report documentation defines these metrics precisely, and we follow the same definitions so the numbers you get match what you'd see in Shopify's admin:

Gross sales - product price × quantity, before discounts, taxes, shipping, or reversals.
Discounts - the dollar value taken off a sale through discount codes, applied before tax.
Sales reversals - Shopify's current term (previously "returns") for any adjustment that results in a negative value: refunds, cancellations, order edits, and exchanges.
Total sales - gross sales minus discounts minus sales reversals, plus shipping, taxes, duties, and fees.
Average order value (AOV) - gross sales minus discounts, divided by orders, calculated before post-order adjustments like reversals or shipping.

The underlying data doesn't change with Shopify's terminology - a "sales reversal" is still recorded as a refund or an order edit, which is exactly what the order_agreement event stream your Shopify connector syncs already captures. Where Shopify's dashboard falls short is everything after that: you can't redefine a metric, extend it with your own logic, or blend in data from outside Shopify - your ad platforms, your accounting system, your support tools. Once the same data is in your warehouse, every one of those reports becomes SQL you own and can extend.

What you'll need

Shopify synced to your warehouse via Weld. The sales report reads order, order_agreement and order_agreement_sale. The bonus sales-by-product template also uses order_line and product. Connect them all with the Shopify connector.
Your store's timezone. Shopify reports in store-local time while the raw timestamps are UTC, so you need to know which timezone to convert to. It's in your Shopify admin under Settings → General.
A warehouse. Weld connects seamlessly to BigQuery, Snowflake and Redshift (all destinations are listed here). The examples are written in BigQuery SQL; the logic ports cleanly to the other two.
Optional: a store to test against. You won't be able to reproduce our exact demo data, but you don't need to - point the templates at your own Shopify development or test store to see them working before you run them against production.

Here's how those tables sit inside Shopify's full data model - the same schema explorer from the Shopify connector page:

Open full-page schema explorer

Click through to the full-page schema explorer to pan, zoom and search across every table and relationship.

How to build your Shopify sales report

This is the flagship template: Shopify's own "Total sales over time" report, rebuilt in SQL and deployed in your warehouse. The bonus template further down follows this same pattern.

Step 1: Understand the sales bridge logic

A Shopify sales report is really a bridge from gross sales down to total sales: start with gross sales, subtract discounts, subtract sales reversals to reach net sales, then add shipping, duties, fees and taxes back on top to reach total sales. Alongside that bridge you want orders and average order value (AOV) for the same period - which is exactly what Shopify's own "Total sales over time" report shows, and the report most merchants check first every morning.

  Gross sales        product price x quantity, before anything else
- Discounts          order- and line-level discounts (stored negative)
- Sales reversals    refunds, cancellations, edits (stored negative)
-----------------
= Net sales
+ Shipping charges
+ Duties
+ Additional fees
+ Taxes
-----------------
= Total sales

The part that trips almost everyone up is when each of those lands. Shopify's report is event-based: a refund belongs to the day the refund was processed, not the day the order was placed. That's what stops yesterday's chart from silently rewriting itself every time support refunds an old order.

Shopify exposes that event log through order agreements. Every change to an order's financial state creates an order_agreement, and each agreement carries the order_agreement_sale lines that changed. Two columns on those lines drive the entire report:

`line_type`	Feeds
`PRODUCT`	Gross sales, discounts, net sales, units
`SHIPPING`	Shipping charges
`DUTY`	Duties
`FEE`	Return and restocking fees
`GIFT_CARD`	Gift card sales - deferred revenue, kept out of the sales equation
`ADJUSTMENT`	Refund value Shopify can't tie to a specific product line

`action_type`	Meaning	Counts as
`ORDER`	The original sale	SALE
`UPDATE`	An order edit that changed value	SALE
`RETURN`	A refund or return	RETURN

Every metric in the query below is just a cell from that grid, summed over a day.

Two sign conventions make the arithmetic work, and they're Shopify's own: discounts are negative and reversals are negative. So net_sales = gross_sales + discounts + returns is plain addition, and every BI tool aggregates it correctly with no special-casing.

Step 2: Write the SQL for the sales report

The SQL (view in the repo):

1-- shopify_sales_over_time - recreates Shopify's "Total sales over time"
2-- Grain: day (store timezone). Currency: shop money.
3-- Event-based: every financial change lands on the day it happened, so
4-- returns hit the refund date and order edits hit the edit date.
5with
6    orders as (
7        select
8            id as order_id
9        from
10            {{raw.shopify.order}}
11        where
12            coalesce(test, false) = false
13            -- These never became revenue. Note that refunded and partially_refunded
14            -- orders DO stay in - their refunds arrive later as RETURN events.
15            and lower(coalesce(financial_status, '')) not in ('pending', 'voided', 'expired')
16    )
17  , -- One row per financial event on an order: the original sale, any later
18    -- edit, and any refund. A 'voided' agreement was cancelled before it ever
19    -- represented money.
20    agreements as (
21        select
22            id as order_agreement_id
23          , order_id
24          , happened_at
25          , upper(coalesce(reason, '')) as reason
26        from
27            {{raw.shopify.order_agreement}}
28        where
29            lower(coalesce(reason, '')) != 'voided'
30    )
31  , -- The line-level money that moved in each event.
32    sales as (
33        select
34            order_agreement_id
35          , order_id
36          , upper(line_type) as line_type
37          , upper(action_type) as action_type
38          , coalesce(quantity, 0) as quantity
39          , coalesce(total_amount_shop_money_amount, 0) as total_amount
40          , coalesce(total_tax_amount_shop_money_amount, 0) as total_tax
41          , coalesce(
42                total_discount_amount_before_taxes_shop_money_amount
43              , 0
44            ) as discount_before_tax
45        from
46            {{raw.shopify.order_agreement_sale}}
47    )
48  , events as (
49        select
50            -- Localise to the store's timezone BEFORE truncating to a date.
51            -- Swap in your own store timezone here.
52            date(datetime(a.happened_at, 'America/New_York')) as date
53          , a.order_id
54          , a.reason
55          , s.line_type
56          , s.action_type
57          , s.quantity
58          , s.discount_before_tax
59          , s.total_tax
60          , case
61                when s.action_type in ('ORDER', 'UPDATE') then 'SALE'
62                when s.action_type = 'RETURN' then 'RETURN'
63            end as row_type
64          , -- total_amount is tax-inclusive; every component except taxes is not.
65            s.total_amount - s.total_tax as amount_ex_tax
66        from
67            agreements a
68            join sales s using (order_agreement_id, order_id)
69            join orders o using (order_id)
70        where
71            s.action_type in ('ORDER', 'UPDATE', 'RETURN')
72    )
73  , daily as (
74        select
75            date
76          , -- One order counts once, on the day it was placed - not again when edited.
77            count(
78                distinct case
79                    when row_type = 'SALE'
80                    and line_type = 'PRODUCT'
81                    and action_type = 'ORDER'
82                    and reason not in ('RETURN', 'ORDER_EDIT') then order_id
83                end
84            ) as orders
85          , sum(
86                case
87                    when row_type = 'SALE'
88                    and line_type = 'PRODUCT' then quantity
89                    when row_type = 'RETURN'
90                    and line_type in ('PRODUCT', 'ADJUSTMENT') then quantity
91                    else 0
92                end
93            ) as units
94          , -- RETURN rows carry negative amounts, so they reduce net sales on their own.
95            sum(
96                case
97                    when row_type = 'SALE'
98                    and line_type = 'PRODUCT' then amount_ex_tax
99                    when row_type = 'RETURN'
100                    and line_type in ('PRODUCT', 'ADJUSTMENT') then amount_ex_tax
101                    else 0
102                end
103            ) as net_sales
104          , -1 * sum(
105                case
106                    when row_type = 'SALE'
107                    and line_type = 'PRODUCT' then greatest(discount_before_tax, 0)
108                    else 0
109                end
110            ) as discounts
111          , sum(
112                case
113                    when row_type = 'RETURN'
114                    and line_type in ('PRODUCT', 'ADJUSTMENT') then amount_ex_tax
115                    else 0
116                end
117            ) as returns
118          , -- No row_type filter: shipping and fees occur on sales AND refunds
119          , -- and a refunded shipping charge should reduce the total.
120            sum(
121                case
122                    when line_type = 'SHIPPING' then amount_ex_tax
123                    else 0
124                end
125            ) as shipping
126          , sum(
127                case
128                    when line_type = 'DUTY' then amount_ex_tax
129                    else 0
130                end
131            ) as duties
132          , sum(
133                case
134                    when line_type = 'FEE' then amount_ex_tax
135                    else 0
136                end
137            ) as fees
138          , sum(total_tax) as taxes
139        from
140            events
141        group by
142            date
143    )
144select
145    date
146  , orders
147  , units
148  , -- Discounts and returns are stored negative, so subtracting adds them back.
149    round(net_sales - discounts - returns, 2) as gross_sales
150  , round(discounts, 2) as discounts
151  , round(returns, 2) as returns
152  , round(net_sales, 2) as net_sales
153  , round(shipping, 2) as shipping
154  , round(duties, 2) as duties
155  , round(fees, 2) as fees
156  , round(taxes, 2) as taxes
157  , round(net_sales + shipping + duties + fees + taxes, 2) as total_sales
158  , -- Shopify's AOV: gross sales less discounts, over orders. Algebraically
159    -- that is (net_sales - returns), since gross = net - discounts - returns.
160    round(
161        safe_divide(net_sales - returns, nullif(orders, 0))
162      , 2
163    ) as average_order_value
164from
165    daily
166order by
167    date

Why order_agreement_sale and not order_line? order_line is a current-state table - it tells you what an order looks like right now. Shopify's sales reports are event-based: they attribute value to the day it changed. An order placed on the 1st, edited on the 3rd and partly refunded on the 20th produces value on three separate days, and a current-state table can only ever show you the end state. Summing order_line is the single most common reason a hand-built sales report drifts from the admin.

Localise before you truncate. happened_at is UTC; Shopify reports in the store's timezone. DATE(happened_at) puts every late-evening order on the wrong day - a drift that averages out over a month but makes daily and day-of-week comparison useless.

On AOV: Shopify defines average order value as gross sales minus discounts, divided by orders - calculated before returns, shipping and taxes. Dividing total_sales by orders instead lets refunds and shipping swing the metric in ways Shopify's own report doesn't.

Lineage graph for shopify_sales_over_time

The Shopify connector lands the raw order, order_agreement and order_agreement_sale tables in your warehouse; shopify_sales_over_time joins the event log to the order header to build the daily gross-to-total sales bridge.

Step 3: Deploy the sales report model in Weld

Once the SQL is written, you have two options to get it running: paste it straight into a new Weld transform and publish, or (our recommended path) push the .sql file to a GitHub repo connected via Weld's GitHub Sync - every push deploys automatically, and only the models that changed re-materialise. If you're already running dbt, Weld's dbt Cloud integration orchestrates dbt jobs directly after each Shopify sync, so the model is just as at home in a dbt project as it is as a standalone Weld transform. Either way, the model reads directly from your synced Shopify streams, so once it's published the sales report keeps itself up to date on whatever schedule your Shopify sync runs on.

Step 4: Visualize your Shopify sales report

BI dashboard: total sales over time - the all-time sales bridge with KPIs

The all-time sales bridge: gross sales, less discounts and returns, gives net sales; adding shipping reaches $2,030,650 in total sales across 12,983 orders. The deductions are a thin sliver of gross - most of it survives to total.

Point any BI tool (Looker Studio, Metabase, Power BI) at the published shopify_sales_over_time model, or hand the model to an AI agent and let it build the chart for you - the columns are already named and typed for a clean drag-and-drop.

Troubleshooting common issues when building a Shopify sales report

Work down this list in order - it's roughly the order of frequency.

A small, consistent daily offset that cancels out over a month. Timezone. You're truncating happened_at to a date before converting it to store-local time. Fix the DATETIME(a.happened_at, 'America/New_York') call to your own timezone and the drift disappears.
Your numbers are consistently higher than Shopify's. Either test orders aren't excluded (test = TRUE), or you've left pending / voided / expired orders in. All four are filtered in the orders CTE above.
Total sales looks too low. Don't exclude refunded or partially_refunded orders - they're real sales, and their refunds already arrive separately as RETURN events on the refund date. Filtering the order out removes the original sale from your totals while the refund was never double-counted in the first place, so you end up understating revenue.
Gross sales too high, net sales correct. Tax isn't being subtracted. total_amount_shop_money_amount on an agreement sale line is tax-inclusive; every component except taxes is tax-exclusive. That's what amount_ex_tax is for.
Returns land on the wrong days. You're attributing them to the order date instead of the refund date. On the event grain this is automatic - if it's happening, something upstream is joining back to the order's created_at.
Units are off but revenue is right. Order-edit handling. Shopify's admin UI and its CSV export don't always agree on units, so pick which one you're reconciling against before you start debugging.
Multi-currency stores. The query uses _shop_money_amount fields, normalised to your shop's base currency. If you need to report in a currency your customers actually paid in, don't convert shop money with your own FX rate - see the dbt guide for why that double-converts and what to do instead.
Inflated revenue on stores that sell gift cards. Gift card sales are deferred revenue, not product revenue. The query excludes them by filtering line_type = 'PRODUCT'; if you want to report on them, sum line_type = 'GIFT_CARD' into separate columns rather than folding them into gross sales.

More Shopify report templates

The same four-step pattern - understand the logic, write the SQL, deploy in Weld, visualize - works for any other Shopify metric. Here is one more ready-made template.

Sales by product

What it is: units and revenue for every product, variant and SKU, per day - so you can rank top sellers and break the numbers down by product type or vendor.

Replicates: Native - Shopify's "Total sales by product".

Scope, stated honestly: unlike the sales report above, this one reads order_line on the order date. That's the right trade-off for ranking top sellers - order_line is where product identity lives, and relative ranking barely moves whether or not you net out returns. It is not the right basis for SKU-level revenue that has to tie to your sales report, because returns stay attached to the original order date and never reduce the SKU that was actually sent back. If you need product-level numbers that reconcile to the day, you need the event grain at line level and the shipping/fee rows that come with it - that's covered in the dbt guide.

The SQL (view in the repo):

1-- shopify_sales_by_product - recreates Shopify's "Total sales by product"
2-- Grain: product x variant (SKU) x day, on the ORDER date. Currency: shop money.
3-- Ranking-oriented: gross and net of discounts, but NOT net of returns.
4with
5    orders as (
6        select
7            id as order_id
8          , date(processed_at) as date
9        from
10            {{raw.shopify.order}}
11        where
12            coalesce(test, false) = false
13    )
14  , lines as (
15        select
16            order_id
17          , product_id
18          , variant_id
19          , sku
20          , title as line_product_title
21          , variant_title
22          , vendor as line_vendor
23          , quantity as units
24          , coalesce(price_set_shop_money_amount, price) * quantity as gross_sales
25          , coalesce(
26                total_discount_set_shop_money_amount
27              , total_discount
28              , 0
29            ) as discounts
30        from
31            {{raw.shopify.order_line}}
32        where
33            coalesce(gift_card, false) = false
34    )
35select
36    o.date
37  , l.product_id
38  , coalesce(p.title, l.line_product_title) as product_title
39  , p.product_type
40  , coalesce(p.vendor, l.line_vendor) as vendor
41  , l.variant_id
42  , l.variant_title
43  , l.sku
44  , sum(l.units) as units
45  , count(distinct o.order_id) as orders
46  , round(sum(l.gross_sales), 2) as gross_sales
47  , round(sum(l.discounts), 2) as discounts
48  , round(sum(l.gross_sales - l.discounts), 2) as net_sales
49from
50    lines l
51    join orders o using (order_id)
52    left join {{raw.shopify.product}} p on p.id = l.product_id
53group by
54    o.date
55  , l.product_id
56  , product_title
57  , p.product_type
58  , vendor
59  , l.variant_id
60  , l.variant_title
61  , l.sku
62order by
63    o.date
64  , net_sales desc

Lineage graph for shopify_sales_by_product

shopify_sales_by_product joins the raw order and order_line tables to product, rolling revenue up to product, variant and SKU per day.

Net sales for the top product variants, with units alongside each. Note the top two are near-identical on revenue but 93 units apart - the same model lets you re-rank by units, product type or vendor to see that.

How we built this (AI-native Weld: MCP + GitHub Sync workflow)

The interesting part isn't just the SQL - it's how fast and low-friction it was to build and ship. This is the workflow Weld is built for, and it's one you can copy today.

A local repo, push to deploy. These models are just .sql files in a local Git repo. You write them in your own IDE, git push, and GitHub Sync deploys them to Weld. No copy-pasting into a web editor, full version control and review on every change, and only the models that actually changed re-materialise. Idea to live model in minutes.

AI removes the SQL friction. You don't have to be a SQL expert. An AI agent, working through the Weld MCP, inspects your real synced data, writes each model, and validates it against the live warehouse before anything ships. It catches the gotchas a beginner would miss - localising timestamps before truncating them to a date, keeping refunded orders as real sales, subtracting tax out of agreement amounts - so the agent does the heavy lifting and you just review the diff and push.

Put together, local repo plus GitHub plus AI is the whole point: the AI writes the SQL, Git ships it, Weld runs it, and a beginner moves at an expert's pace. For the deeper walkthroughs:

Make it yours

Here's the short path from this post to your own live dashboards:

Grab the templates from the public repo - the source of truth for both models.
Get them into Weld. Connect the repo with GitHub Sync (push to deploy), or paste each model into a new Weld transform.
Point them at your data. The models already read your raw Shopify tables (raw.shopify.*), so once your Shopify streams are synced they run exactly as they are - no rewriting required. Each model's header lists the tables it reads, so you can check the fit at a glance.
Visualise. Point your BI tool at the published models, or hand a model to an AI agent and let it build the dashboard for you.

FAQ: building a Shopify sales report with SQL

How do I calculate total sales in Shopify using SQL?

Total sales is gross sales (price × quantity) minus discounts minus sales reversals, plus shipping, duties, fees and taxes. The Step 2 query above builds that bridge day by day from the raw order, order_agreement and order_agreement_sale tables, attributing every component to the day it actually happened.

Should I use `order_line` or `order_agreement_sale` for a Shopify sales report?

order_agreement_sale, for anything time-based. order_line is a current-state table: it describes what an order looks like right now and cannot express when its value changed, so returns, edits and cancellations can't be attributed to the day they happened. Use order_line only for product identity - SKU, title, variant, vendor. Summing order_line is the most common reason a hand-built sales report drifts from Shopify's admin.

Does Shopify's average order value include refunds and shipping?

No. Shopify calculates AOV as gross sales minus discounts, divided by orders - before any post-order adjustments like returns, shipping or taxes. Dividing total_sales by orders instead is a common mistake that lets refunds and shipping distort the number.

What Shopify data do I need synced to build a sales report?

At minimum: order, order_agreement and order_agreement_sale. The bonus sales-by-product template also uses order_line and product. Sync all of them in one go with the Shopify connector.

Can I build this Shopify sales report in Snowflake or Redshift instead of BigQuery?

Yes. The query is written in BigQuery SQL, but the logic - CTEs, joins, and aggregations - is standard ANSI SQL and ports to Snowflake or Redshift with only minor syntax changes (e.g. SAFE_DIVIDE → NULLIF-based division).

Will this sales report match the numbers in my Shopify dashboard?

It's built to reproduce Shopify's own "Total sales over time" logic on the same event grain Shopify uses, so a single-store setup should land within a fraction of a percent. Exact-to-the-cent parity across all history isn't a realistic goal - Shopify restates some historical figures and rounds in places you can't observe - so agree a tolerance with finance rather than chasing zero. If you're further out than that, work through the troubleshooting section above, starting with timezone.

How do I build this across multiple Shopify stores?

Not with this query - it assumes one store. Order IDs are only unique within a store, so the moment you union two storefronts you need a shopify_store key in every join or rows silently fan out across brands. That, plus schema drift between stores and multi-currency reporting, is what the dbt version of this guide covers.

How often does the sales report update?

As often as your Shopify sync runs. Since the model reads directly from your synced raw tables, republishing isn't needed - it recalculates from whatever data is currently in your warehouse each time it runs.

Do I need dbt to build this Shopify sales report?

No - the SQL above runs standalone as a Weld transform. dbt is worth adding once you want version-controlled tests, documentation and lineage across many models rather than just this one; Weld's dbt Cloud integration orchestrates dbt jobs directly after each Shopify sync if you go that route. If you're already in dbt, the dbt version of this build covers the staging layer, multi-store unions, multi-currency and the test suite.

Conclusion

You now own your Shopify sales report instead of renting it from Shopify's dashboard - and that's just the start. Because the model lives in your warehouse, you can join it to everything else Weld connects to (your ad platforms, your accounting and ERP data, your subscription and support tools), and use the same four-step pattern to rebuild any other Shopify report you rely on - turning today's report into the foundation your whole analytics stack builds on.

All Connectors

Custom Connectors

Data Sources

Files

Destinations

Reverse ETL

Functions

Platform

Build

Extend

Product Tour

Resources

Company

Compare

From the Blog

How to Build a Shopify Sales Report with SQL