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 and total sales - as SQL models that run 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.
How Shopify calculates its sales metrics
Almost every mismatch between a hand-built report and the Shopify admin comes from one of two things: using a slightly different formula, or putting the right number on the wrong day. Shopify's sales report documentation pins down the formulas, so start there.
| Metric | Formula | Excludes |
|---|---|---|
| Gross sales | price × quantity | tax, shipping, discounts, reversals |
| Discounts | code, automatic and manual discounts, applied before tax | stored negative |
| Sales reversals | refunds, cancellations, order edits, exchanges | stored negative |
| Net sales | gross sales − discounts − reversals | tax, shipping |
| Total sales | net sales + shipping + duties + fees + taxes | — |
| AOV | (gross sales − discounts) ÷ orders | reversals, shipping, tax |
Three of those trip people up in practice:
- Gross sales is pre-everything. Not "revenue" in any accounting sense — no tax, no shipping, and crucially not net of discounts. If your gross looks high, check you haven't left tax in.
- Discounts and reversals are stored negative. So
net = gross + discounts + reversalsis plain addition. Subtract them again and you double-count. - AOV is measured before post-order adjustments. It uses gross-less-discounts, not total sales. Dividing
total_salesby orders is the single most common way to get a number that looks plausible and disagrees with Shopify.
"Sales reversal" is just Shopify's newer word for a return. The underlying data doesn't change — it's still a refund or an order edit, which is exactly what the order_agreement event stream your connector already syncs. Where the 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. Once the data is in your warehouse, each of those becomes SQL you own.
What you'll need
- Shopify synced to your warehouse via Weld. The sales report reads
order,order_agreement,order_agreement_sale,order_line,order_refund,order_line_refund,shopandlocation. The product model addsinventory_itemand its history table for COGS; the quick ranking template usesproduct. Connect them all with the Shopify connector. - History tables on
inventory_item, if you want point-in-time COGS. Enable them under Data Source → the stream → History tables. Without it, costs fall back to the current value and margin on old orders will be wrong. - Nothing to configure for timezones. Shopify reports in store-local time while the raw timestamps are UTC. The model reads your store's timezone from the
shoptable, so there's no literal to swap in. - 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:
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 salesThe 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.
The same order, two ways. On the event grain it produces three rows on three days and 1 March is settled forever. Summed off order_line it produces one row of 225.00 on 1 March — the edit invisible, the refund back-dated — so last month's report quietly changes whenever someone processes an old refund.
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 model comes in four layers, each with one job:
raw.shopify.* ELT output, untouched
↓
staging/*.sql thin wrappers: cast, rename, drop test orders
↓
core/*.sql the report - all the business logic lives here
↓
analytics/*.sql a SELECT * your BI tool binds toThat's worth doing even for a single store. Staging means the casting and filtering
happen once instead of in every report - and it's where multi-store lives: each
staging model labels its rows with a shopify_store, so adding a second storefront
is a UNION ALL there and nothing else. shopify_store is already part of every
join key and of the grain in core, because order IDs are only unique within a
store. And the analytics layer exists for
indirection rather than transformation: dashboards bind to
analytics.shopify.sales_over_time, so you can rename a column or change the grain
in core without breaking anything downstream.
The complete model is core/sales_over_time.sql —
long, because reconciling with Shopify means handling order edits, gift cards,
store locations and presentment currencies. Below are the three parts
that carry the actual logic, taken straight from that file - so there is no
simplified-for-the-blog version to drift out of date. The complete model is
expandable at the end of this step if you'd rather read it all at once, and it's
also in the Shopify SQL template library
alongside every staging model it depends on.
First, the staging model for the agreement lines
(staging/order_agreement_sale.sql).
Note amount_ex_tax: Shopify's total_amount is tax-inclusive while every sales
component except taxes is not, so the subtraction happens once here rather than
in every consumer.
1-- staging.shopify.order_agreement_sale
2-- Line-level money per event, in both shop money (the store's base currency) and
3-- presentment money (what the customer actually paid in).
4--
5-- amount_ex_tax is derived here because total_amount is tax-inclusive while every
6-- sales component except taxes is not - computing it once avoids repeating the
7-- subtraction in every consumer.
8--
9-- Single store. To add another, UNION ALL a second block below pointing at that
10-- store's connector with a different shopify_store label. Keep it in staging so
11-- the core models never have to know how many stores there are.
12
13SELECT
14 'store_1' AS shopify_store,
15 CAST(order_agreement_id AS STRING) AS order_agreement_id,
16 CAST(order_id AS INT64) AS order_id,
17 UPPER(CAST(line_type AS STRING)) AS line_type,
18 UPPER(CAST(action_type AS STRING)) AS action_type,
19 COALESCE(CAST(quantity AS INT64), 0) AS quantity,
20
21 COALESCE(CAST(total_amount_shop_money_amount AS NUMERIC), 0) AS total_amount,
22 COALESCE(CAST(total_tax_amount_shop_money_amount AS NUMERIC), 0) AS total_tax,
23 COALESCE(CAST(total_discount_amount_before_taxes_shop_money_amount AS NUMERIC), 0)
24 AS discount_before_tax,
25 COALESCE(CAST(total_amount_shop_money_amount AS NUMERIC), 0)
26 - COALESCE(CAST(total_tax_amount_shop_money_amount AS NUMERIC), 0) AS amount_ex_tax,
27
28 COALESCE(CAST(total_amount_presentment_money_amount AS NUMERIC), 0) AS presentment_total_amount,
29 COALESCE(CAST(total_tax_amount_presentment_money_amount AS NUMERIC), 0) AS presentment_total_tax,
30 COALESCE(CAST(total_discount_amount_before_taxes_presentment_money_amount AS NUMERIC), 0)
31 AS presentment_discount_before_tax,
32 COALESCE(CAST(total_amount_presentment_money_amount AS NUMERIC), 0)
33 - COALESCE(CAST(total_tax_amount_presentment_money_amount AS NUMERIC), 0) AS presentment_amount_ex_tax,
34 UPPER(CAST(total_amount_presentment_money_currency_code AS STRING)) AS presentment_currency
35FROM {{raw.shopify.order_agreement_sale}}Then the event join. This is where the report becomes event-based: agreements
carry when, sale lines carry how much, and the order header carries the
dimensions. Two details matter — the timestamp is localised before being truncated
to a date, and an ORDER_EDIT within 90 seconds of checkout is treated as part of
the original purchase rather than a later restatement.
1joined AS (
2 SELECT
3 -- Localise BEFORE truncating to a date. happened_at is UTC while Shopify
4 -- reports in store-local time.
5 DATE(DATETIME(a.happened_at, s.report_timezone)) AS date,
6 a.shopify_store,
7 a.order_id,
8 a.reason,
9 a.app_handle,
10 o.location_id,
11 o.source_name,
12 o.shop_currency,
13 sa.line_type,
14 sa.action_type,
15 sa.quantity,
16 sa.total_tax,
17 sa.discount_before_tax,
18 sa.amount_ex_tax,
19 sa.presentment_total_tax,
20 sa.presentment_discount_before_tax,
21 sa.presentment_amount_ex_tax,
22
23 -- An ORDER_EDIT within 90 seconds of creation is part of the original
24 -- purchase (a post-purchase upsell), not a later restatement.
25 CASE
26 WHEN a.reason = 'ORDER_EDIT'
27 AND TIMESTAMP_DIFF(a.happened_at, oft.order_created_at, SECOND) <= 90
28 THEN TRUE ELSE FALSE
29 END AS is_checkout_edit
30 FROM agreements a
31 JOIN shop s USING (shopify_store)
32 JOIN sales sa USING (shopify_store, order_agreement_id, order_id)
33 JOIN orders o USING (shopify_store, order_id)
34 JOIN order_line_flags olf USING (shopify_store, order_id)
35 LEFT JOIN order_first_time oft USING (shopify_store, order_id)
36 WHERE olf.has_product_title = 1
37)
38
39typed AS (
40 SELECT
41 *,
42 CASE
43 WHEN action_type IN ('ORDER', 'UPDATE') THEN 'SALE'
44 WHEN action_type = 'RETURN' THEN 'RETURN'
45 END AS report_row_type
46 FROM joined
47 WHERE action_type IN ('ORDER', 'UPDATE', 'RETURN')
48)Then the sales equation itself, as aggregates over those events. This is the bridge from Step 1 expressed in SQL:
1 -- RETURN rows carry negative amounts, so they reduce net sales on their
2 -- own. ADJUSTMENT lines are refund value Shopify cannot tie to a product
3 -- line (partial refunds, goodwill credits) - including them is what makes
4 -- returns tie out.
5 SUM(CASE
6 WHEN report_row_type = 'SALE' AND line_type = 'PRODUCT' THEN amount_ex_tax
7 WHEN report_row_type = 'RETURN' AND line_type IN ('PRODUCT', 'ADJUSTMENT') THEN amount_ex_tax
8 ELSE 0
9 END) AS net_sales,
10
11 -- Same, excluding POST-ORDER edits: reconciles to the admin UI.
12 SUM(CASE
13 WHEN report_row_type = 'SALE' AND line_type = 'PRODUCT'
14 AND (reason != 'ORDER_EDIT' OR is_checkout_edit) THEN amount_ex_tax
15 WHEN report_row_type = 'RETURN' AND line_type IN ('PRODUCT', 'ADJUSTMENT') THEN amount_ex_tax
16 ELSE 0
17 END) AS net_sales_shopify_parity,
18
19 -- GREATEST guards negative discounts, which appear on some edit and
20 -- return events and would otherwise inflate the total.
21 (-1) * SUM(CASE
22 WHEN report_row_type = 'SALE' AND line_type = 'PRODUCT'
23 THEN GREATEST(discount_before_tax, 0) ELSE 0
24 END) AS discounts,
25
26 SUM(CASE WHEN report_row_type = 'RETURN' AND line_type IN ('PRODUCT', 'ADJUSTMENT')
27 THEN amount_ex_tax ELSE 0 END) AS returns,
28 SUM(CASE WHEN report_row_type = 'RETURN' AND line_type = 'ADJUSTMENT'
29 THEN amount_ex_tax ELSE 0 END) AS return_adjustments,
30
31 -- No row_type filter: shipping and fees occur on sales AND refunds, and a
32 -- refunded shipping charge should reduce the total.
33 SUM(CASE WHEN line_type = 'SHIPPING' THEN amount_ex_tax ELSE 0 END) AS shipping_charges,
34 SUM(CASE WHEN line_type = 'DUTY' THEN amount_ex_tax ELSE 0 END) AS duties,
35 SUM(CASE WHEN line_type = 'FEE' THEN amount_ex_tax ELSE 0 END) AS return_fees,
36 CAST(0 AS NUMERIC) AS additional_fees,
37 SUM(total_tax) AS taxesEverything else in the model is either a dimension (location, sales channel, country, fulfilment status) or a variant of these same sums — see what the full model adds below.
Show the complete model — all 470 lines, exactly as it runs
1-- shopify_sales_over_time - recreates Shopify's "Total sales over time"
2--
3-- Grain: day x store x order x location x channel x row type. Keeping row type
4-- in the grain means a day where an order both sells and refunds produces two
5-- rows, so the SALE and RETURN sides stay independently auditable.
6--
7-- Multi-store safe: order IDs are only unique WITHIN a store, so shopify_store is
8-- part of every join key and of the grain. Drop it from one join and rows fan out
9-- across storefronts silently.
10--
11-- Event-based: reads Shopify's order agreement log, so every financial change
12-- lands on the day it happened - returns on the refund date, order edits on the
13-- edit date. History does not restate itself when an old order is refunded.
14--
15-- Currency: shop money, plus the presentment amounts the customer actually paid.
16-- Timezone: read from the shop record, so nothing needs hardcoding.
17--
18-- Depends on: staging.shopify.order, .order_agreement, .order_agreement_sale,
19-- .order_line, .order_refund, .order_line_refund, .shop, .location
20
21WITH shop AS (
22 -- Per store: currency and timezone both vary between storefronts.
23 SELECT
24 shopify_store,
25 ANY_VALUE(currency) AS shop_currency,
26 COALESCE(ANY_VALUE(iana_timezone), 'UTC') AS report_timezone
27 FROM {{staging.shopify.shop}}
28 GROUP BY shopify_store
29),
30
31locations AS (
32 SELECT shopify_store, location_id, location_name, location_country_code
33 FROM {{staging.shopify.location}}
34),
35
36orders AS (
37 SELECT
38 o.shopify_store,
39 o.order_id,
40 o.order_name,
41 o.location_id,
42 o.source_name,
43 o.financial_status,
44 o.fulfillment_status,
45 o.cancelled_at,
46
47 -- The order's own currency wins over the shop's CURRENT currency, which
48 -- may have changed since. Deliberately NOT falling back to presentment
49 -- currency: that is what the customer was charged in, so using it here
50 -- would label shop-currency money with the buyer's currency. If none of
51 -- these is set, NULL is the honest answer - a guessed currency code
52 -- silently mislabels every amount on the row.
53 COALESCE(
54 o.order_shop_currency,
55 o.currency,
56 s.shop_currency
57 ) AS shop_currency,
58
59 -- Shipping country is the right geography, but it is NULL for digital
60 -- goods and POS. Cascade to billing, then the location's country.
61 COALESCE(
62 o.shipping_country_code,
63 o.billing_country_code,
64 l.location_country_code
65 ) AS country
66 FROM {{staging.shopify.order}} o
67 JOIN shop s USING (shopify_store)
68 LEFT JOIN locations l USING (shopify_store, location_id)
69 -- Only voided orders are dropped here: the money never existed. Everything
70 -- else stays, including 'pending' - an authorised-but-uncaptured order
71 -- (bank transfer, cash on delivery, manual payment) is a sale Shopify counts,
72 -- so excluding it here would understate every report downstream. Cancelled
73 -- and refunded orders also stay: their reversals arrive as their own RETURN
74 -- events, so removing the order would lose the sale AND the reversal.
75 -- financial_status and cancelled_at are passed through, so the analytics
76 -- layer can narrow this without core having destroyed the rows.
77 WHERE o.financial_status != 'voided'
78),
79
80-- Orders carrying only untitled lines are dropped by Shopify's reports: deleted
81-- products, draft-order custom lines, API artifacts. Flag at order level so a
82-- partial order does not half-appear. Gift-card lines count as titled.
83order_line_flags AS (
84 SELECT
85 shopify_store,
86 order_id,
87 MAX(CASE WHEN is_gift_card OR product_title IS NOT NULL THEN 1 ELSE 0 END)
88 AS has_product_title,
89 LOGICAL_OR(requires_shipping) AS any_line_requires_shipping
90 FROM {{staging.shopify.order_line}}
91 GROUP BY shopify_store, order_id
92),
93
94agreements AS (
95 SELECT shopify_store, order_agreement_id, order_id, happened_at, app_handle, reason
96 FROM {{staging.shopify.order_agreement}}
97),
98
99sales AS (
100 SELECT
101 shopify_store, order_agreement_id, order_id, line_type, action_type, quantity,
102 total_amount, total_tax, discount_before_tax, amount_ex_tax,
103 presentment_total_amount, presentment_total_tax,
104 presentment_discount_before_tax, presentment_amount_ex_tax,
105 presentment_currency
106 FROM {{staging.shopify.order_agreement_sale}}
107),
108
109-- Earliest ORDER agreement per order, used to spot checkout-flow edits.
110order_first_time AS (
111 SELECT shopify_store, order_id, MIN(happened_at) AS order_created_at
112 FROM agreements
113 WHERE reason = 'ORDER'
114 GROUP BY shopify_store, order_id
115),
116
117-- Shopify's own implied FX rate per order: presentment per 1 unit of shop money.
118-- Using this beats converting shop money at your own daily rate, because Shopify
119-- used its rate at checkout and yours will not match what the customer paid.
120order_presentment AS (
121 SELECT
122 shopify_store,
123 order_id,
124 ANY_VALUE(presentment_currency) AS presentment_currency,
125 SAFE_DIVIDE(SUM(presentment_total_amount), NULLIF(SUM(total_amount), 0))
126 AS shopify_fx_rate
127 FROM sales
128 GROUP BY shopify_store, order_id
129),
130
131joined AS (
132 SELECT
133 -- Localise BEFORE truncating to a date. happened_at is UTC while Shopify
134 -- reports in store-local time.
135 DATE(DATETIME(a.happened_at, s.report_timezone)) AS date,
136 a.shopify_store,
137 a.order_id,
138 a.reason,
139 a.app_handle,
140 o.location_id,
141 o.source_name,
142 o.shop_currency,
143 sa.line_type,
144 sa.action_type,
145 sa.quantity,
146 sa.total_tax,
147 sa.discount_before_tax,
148 sa.amount_ex_tax,
149 sa.presentment_total_tax,
150 sa.presentment_discount_before_tax,
151 sa.presentment_amount_ex_tax,
152
153 -- An ORDER_EDIT within 90 seconds of creation is part of the original
154 -- purchase (a post-purchase upsell), not a later restatement.
155 CASE
156 WHEN a.reason = 'ORDER_EDIT'
157 AND TIMESTAMP_DIFF(a.happened_at, oft.order_created_at, SECOND) <= 90
158 THEN TRUE ELSE FALSE
159 END AS is_checkout_edit
160 FROM agreements a
161 JOIN shop s USING (shopify_store)
162 JOIN sales sa USING (shopify_store, order_agreement_id, order_id)
163 JOIN orders o USING (shopify_store, order_id)
164 JOIN order_line_flags olf USING (shopify_store, order_id)
165 LEFT JOIN order_first_time oft USING (shopify_store, order_id)
166 WHERE olf.has_product_title = 1
167),
168
169typed AS (
170 SELECT
171 *,
172 CASE
173 WHEN action_type IN ('ORDER', 'UPDATE') THEN 'SALE'
174 WHEN action_type = 'RETURN' THEN 'RETURN'
175 END AS report_row_type
176 FROM joined
177 WHERE action_type IN ('ORDER', 'UPDATE', 'RETURN')
178),
179
180-- Shopify lumps cancellations, returns and refunds together; your business does
181-- not. restock_type on the refunded lines tells them apart. Return rate measured
182-- on RETURN alone is a product-quality signal; on all three it is noise.
183refunds AS (
184 SELECT
185 r.shopify_store,
186 r.refund_id,
187 r.order_id,
188 DATE(DATETIME(r.refund_created_at, s.report_timezone)) AS date
189 FROM {{staging.shopify.order_refund}} r
190 JOIN shop s USING (shopify_store)
191),
192
193refund_detail AS (
194 SELECT
195 r.shopify_store,
196 r.date,
197 r.order_id,
198 olr.restock_type,
199 olr.location_id,
200 SUM(olr.quantity) AS qty
201 FROM {{staging.shopify.order_line_refund}} olr
202 JOIN refunds r USING (shopify_store, refund_id)
203 GROUP BY 1, 2, 3, 4, 5
204),
205
206reversal_type AS (
207 SELECT
208 shopify_store,
209 date,
210 order_id,
211 CASE
212 WHEN restock_type = 'cancel' THEN 'CANCELLATION' -- never shipped
213 WHEN restock_type = 'return' THEN 'RETURN' -- came back, restocked
214 ELSE 'REFUND' -- money back, no restock
215 END AS reversal_type
216 FROM refund_detail
217 -- The dominant restock type wins when one refund mixes them.
218 QUALIFY ROW_NUMBER() OVER (
219 PARTITION BY shopify_store, date, order_id
220 ORDER BY qty DESC, restock_type
221 ) = 1
222),
223
224-- Carry the last known refund location forward so a multi-day return sequence
225-- does not fragment across locations.
226refund_location AS (
227 SELECT
228 shopify_store,
229 date,
230 order_id,
231 LAST_VALUE(location_id IGNORE NULLS) OVER (
232 PARTITION BY shopify_store, order_id ORDER BY date
233 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
234 ) AS refund_location_id
235 FROM (
236 SELECT shopify_store, date, order_id, MAX(location_id) AS location_id
237 FROM refund_detail
238 GROUP BY 1, 2, 3
239 )
240),
241
242agg AS (
243 SELECT
244 date,
245 shopify_store,
246 order_id,
247 location_id,
248 source_name,
249 shop_currency,
250 report_row_type,
251
252 -- MAX not SUM: one order is one order however many lines it has.
253 -- Excluding ORDER_EDIT stops an edited order counting again on the edit date.
254 MAX(CASE
255 WHEN report_row_type = 'SALE' AND line_type = 'PRODUCT'
256 AND action_type = 'ORDER' AND reason NOT IN ('RETURN', 'ORDER_EDIT')
257 THEN 1 ELSE 0
258 END) AS orders,
259
260 -- 1. Net unit movement: sales minus returns.
261 SUM(CASE
262 WHEN report_row_type = 'SALE' AND line_type IN ('PRODUCT', 'GIFT_CARD') THEN quantity
263 WHEN report_row_type = 'RETURN' AND line_type IN ('PRODUCT', 'ADJUSTMENT') THEN quantity
264 ELSE 0
265 END) AS quantity,
266
267 -- 2. Strict units ordered: no returns, no edits. Matches the admin UI.
268 SUM(CASE
269 WHEN report_row_type = 'SALE' AND line_type = 'PRODUCT'
270 AND action_type = 'ORDER' AND reason NOT IN ('RETURN', 'ORDER_EDIT')
271 THEN quantity ELSE 0
272 END) AS quantity_ordered_shopify_compat,
273
274 -- 3. Keeps checkout-flow edits, drops post-order edits. Matches Shopify's
275 -- CSV export, which differs from the UI.
276 SUM(CASE
277 WHEN report_row_type = 'SALE' AND line_type IN ('PRODUCT', 'GIFT_CARD')
278 AND action_type = 'ORDER' AND reason != 'RETURN'
279 AND (reason != 'ORDER_EDIT' OR is_checkout_edit)
280 THEN quantity ELSE 0
281 END) AS quantity_ordered_shopify_export_parity,
282
283 -- RETURN rows carry negative amounts, so they reduce net sales on their
284 -- own. ADJUSTMENT lines are refund value Shopify cannot tie to a product
285 -- line (partial refunds, goodwill credits) - including them is what makes
286 -- returns tie out.
287 SUM(CASE
288 WHEN report_row_type = 'SALE' AND line_type = 'PRODUCT' THEN amount_ex_tax
289 WHEN report_row_type = 'RETURN' AND line_type IN ('PRODUCT', 'ADJUSTMENT') THEN amount_ex_tax
290 ELSE 0
291 END) AS net_sales,
292
293 -- Same, excluding POST-ORDER edits: reconciles to the admin UI.
294 SUM(CASE
295 WHEN report_row_type = 'SALE' AND line_type = 'PRODUCT'
296 AND (reason != 'ORDER_EDIT' OR is_checkout_edit) THEN amount_ex_tax
297 WHEN report_row_type = 'RETURN' AND line_type IN ('PRODUCT', 'ADJUSTMENT') THEN amount_ex_tax
298 ELSE 0
299 END) AS net_sales_shopify_parity,
300
301 -- GREATEST guards negative discounts, which appear on some edit and
302 -- return events and would otherwise inflate the total.
303 (-1) * SUM(CASE
304 WHEN report_row_type = 'SALE' AND line_type = 'PRODUCT'
305 THEN GREATEST(discount_before_tax, 0) ELSE 0
306 END) AS discounts,
307
308 SUM(CASE WHEN report_row_type = 'RETURN' AND line_type IN ('PRODUCT', 'ADJUSTMENT')
309 THEN amount_ex_tax ELSE 0 END) AS returns,
310 SUM(CASE WHEN report_row_type = 'RETURN' AND line_type = 'ADJUSTMENT'
311 THEN amount_ex_tax ELSE 0 END) AS return_adjustments,
312
313 -- No row_type filter: shipping and fees occur on sales AND refunds, and a
314 -- refunded shipping charge should reduce the total.
315 SUM(CASE WHEN line_type = 'SHIPPING' THEN amount_ex_tax ELSE 0 END) AS shipping_charges,
316 SUM(CASE WHEN line_type = 'DUTY' THEN amount_ex_tax ELSE 0 END) AS duties,
317 SUM(CASE WHEN line_type = 'FEE' THEN amount_ex_tax ELSE 0 END) AS return_fees,
318 CAST(0 AS NUMERIC) AS additional_fees,
319 SUM(total_tax) AS taxes,
320
321 -- Returns detail block.
322 SUM(CASE WHEN report_row_type = 'RETURN' AND line_type IN ('PRODUCT', 'ADJUSTMENT')
323 THEN amount_ex_tax ELSE 0 END) AS sales_reversals,
324 (-1) * SUM(CASE WHEN report_row_type = 'RETURN' AND line_type = 'PRODUCT'
325 THEN discount_before_tax ELSE 0 END) AS discount_reversals,
326 SUM(CASE WHEN report_row_type = 'RETURN' THEN total_tax ELSE 0 END) AS tax_reversals,
327 SUM(CASE WHEN report_row_type = 'RETURN' AND line_type = 'SHIPPING'
328 THEN amount_ex_tax ELSE 0 END) AS shipping_reversals,
329 SUM(CASE WHEN report_row_type = 'RETURN' AND line_type IN ('PRODUCT', 'ADJUSTMENT')
330 THEN quantity ELSE 0 END) AS reversed_quantity,
331
332 -- Gift cards are deferred revenue, kept out of the sales equation.
333 SUM(CASE WHEN line_type = 'GIFT_CARD'
334 THEN amount_ex_tax + GREATEST(discount_before_tax, 0) ELSE 0 END) AS gift_card_gross_sales,
335 SUM(CASE WHEN line_type = 'GIFT_CARD' THEN amount_ex_tax ELSE 0 END) AS gift_card_net_sales,
336 (-1) * SUM(CASE WHEN line_type = 'GIFT_CARD'
337 THEN GREATEST(discount_before_tax, 0) ELSE 0 END) AS gift_card_discounts,
338 SUM(CASE WHEN line_type = 'GIFT_CARD' THEN total_tax ELSE 0 END) AS gift_card_taxes,
339
340 -- Presentment money: what the customer actually paid, before any
341 -- conversion of ours.
342 SUM(CASE
343 WHEN report_row_type = 'SALE' AND line_type = 'PRODUCT' THEN presentment_amount_ex_tax
344 WHEN report_row_type = 'RETURN' AND line_type IN ('PRODUCT', 'ADJUSTMENT') THEN presentment_amount_ex_tax
345 ELSE 0
346 END) AS presentment_net_sales,
347 SUM(presentment_total_tax) AS presentment_taxes
348 FROM typed
349 GROUP BY 1, 2, 3, 4, 5, 6, 7
350)
351
352SELECT
353 a.date,
354 a.shopify_store,
355 a.order_id,
356 o.order_name,
357 a.report_row_type,
358 a.shop_currency,
359 o.country,
360 a.source_name,
361
362 -- The order's own location, straight from Shopify. NULL means Shopify
363 -- recorded no location - usually an online-store order - and is left NULL
364 -- rather than labelled, because "no physical location" is not the same claim
365 -- as "online". Identical expression in product_sales_over_time, so the two
366 -- models reconcile on this dimension.
367 own_loc.location_name AS location_name,
368
369 -- Where a refund was processed, which is often NOT where the order was
370 -- placed: a POS sale returned through the web admin has a store
371 -- location_name and no refund location. Kept as its own column rather than
372 -- coalesced into location_name - they are two different facts, and merging
373 -- them makes it impossible to ask either question. NULL on SALE rows.
374 rf_loc.location_name AS refund_location_name,
375
376 -- Shopify's own channel value, not an interpretation of it: 'web' for the
377 -- online store, 'pos' for retail, 'shopify_draft_order' for manual orders,
378 -- otherwise the handle of the app or marketplace that created the order.
379 -- Split retail from online with this, or with location_name IS NOT NULL.
380 LOWER(a.source_name) AS sales_channel,
381
382 -- An order of only digital goods never gets a fulfillment status, which reads
383 -- as "unfulfilled" in every dashboard and panics ops. Distinguish it.
384 CASE
385 WHEN o.fulfillment_status IS NULL AND olf.any_line_requires_shipping = FALSE
386 THEN 'fulfillment_not_required'
387 ELSE o.fulfillment_status
388 END AS fulfillment_status,
389
390 o.financial_status,
391 rt.reversal_type,
392 o.cancelled_at IS NOT NULL AS is_cancelled,
393
394 a.orders,
395
396 -- Exactly ONE row per order, on its first SALE day. Summing over any window
397 -- gives "orders placed", the AOV denominator Shopify uses. Counts edit-only
398 -- orders with no ORDER row, ignores return-only orders, and never
399 -- double-counts across locations or days.
400 CASE
401 WHEN a.report_row_type = 'SALE'
402 AND ROW_NUMBER() OVER (
403 PARTITION BY a.shopify_store, a.order_id
404 ORDER BY
405 CASE WHEN a.report_row_type = 'SALE' THEN 0 ELSE 1 END,
406 a.date ASC, a.location_id ASC, a.source_name ASC
407 ) = 1
408 THEN 1 ELSE 0
409 END AS is_order_placed,
410
411 a.quantity,
412 a.quantity_ordered_shopify_compat,
413 a.quantity_ordered_shopify_export_parity,
414
415 -- discounts and returns are stored negative, so subtracting adds them back.
416 ROUND(a.net_sales - a.discounts - a.returns, 2) AS gross_sales,
417 ROUND(a.discounts, 2) AS discounts,
418 ROUND(a.returns, 2) AS returns,
419 ROUND(a.return_adjustments, 2) AS return_adjustments,
420 ROUND(a.net_sales, 2) AS net_sales,
421 ROUND(a.net_sales_shopify_parity, 2) AS net_sales_shopify_parity,
422
423 ROUND(a.shipping_charges, 2) AS shipping_charges,
424 ROUND(a.duties, 2) AS duties,
425 ROUND(a.return_fees, 2) AS return_fees,
426 ROUND(a.additional_fees, 2) AS additional_fees,
427 ROUND(a.taxes, 2) AS taxes,
428
429 -- Matches the Shopify admin UI.
430 ROUND(a.net_sales + a.shipping_charges + a.duties
431 + a.return_fees + a.additional_fees + a.taxes, 2) AS total_shopify_sales,
432 -- Excludes tax and duties (pass-through money); what finance usually wants.
433 ROUND(a.net_sales + a.shipping_charges
434 + a.return_fees + a.additional_fees, 2) AS total_sales,
435
436 ROUND(a.sales_reversals, 2) AS net_sales_reversals,
437 ROUND(a.sales_reversals - a.discount_reversals, 2) AS gross_sales_reversals,
438 ROUND(a.sales_reversals + a.tax_reversals
439 + a.shipping_reversals + a.return_fees, 2) AS total_sales_reversals,
440 ROUND(a.discount_reversals, 2) AS discount_reversals,
441 ROUND(a.tax_reversals, 2) AS tax_reversals,
442 ROUND(a.shipping_reversals, 2) AS shipping_reversals,
443 a.reversed_quantity,
444
445 ROUND(a.gift_card_gross_sales, 2) AS gift_card_gross_sales,
446 ROUND(a.gift_card_net_sales, 2) AS gift_card_net_sales,
447 ROUND(a.gift_card_discounts, 2) AS gift_card_discounts,
448 ROUND(a.gift_card_taxes, 2) AS gift_card_taxes,
449 ROUND(a.taxes - a.gift_card_taxes, 2) AS taxes_excluding_gift_cards,
450
451 -- What the customer actually paid, and the rate Shopify used at checkout.
452 op.presentment_currency,
453 ROUND(a.presentment_net_sales, 2) AS presentment_net_sales,
454 ROUND(a.presentment_taxes, 2) AS presentment_taxes,
455 op.shopify_fx_rate
456FROM agg a
457JOIN orders o USING (shopify_store, order_id)
458JOIN order_line_flags olf USING (shopify_store, order_id)
459LEFT JOIN order_presentment op USING (shopify_store, order_id)
460LEFT JOIN reversal_type rt ON rt.shopify_store = a.shopify_store
461 AND rt.order_id = a.order_id
462 AND rt.date = a.date
463LEFT JOIN refund_location rf ON rf.shopify_store = a.shopify_store
464 AND rf.order_id = a.order_id
465 AND rf.date = a.date
466LEFT JOIN locations rf_loc ON rf_loc.shopify_store = a.shopify_store
467 AND rf_loc.location_id = rf.refund_location_id
468LEFT JOIN locations own_loc ON own_loc.shopify_store = a.shopify_store
469 AND own_loc.location_id = a.location_id
470ORDER BY a.date, a.shopify_store, a.order_id, a.report_row_typeWhy the agreement log and not
order_line?order_lineis a current-state table: it tells you what an order looks like right now, and cannot express when its value changed. Sum it and returns get netted back to the original order date, order edits vanish, and yesterday's chart quietly rewrites itself every time someone refunds an old order. It's the most common reason a hand-built sales report drifts from the admin. Useorder_linefor product identity - SKU, title, variant - and the agreement log for anything time-based.On AOV: there's no
average_order_valuecolumn. At order grain it would be meaningless on a single row, so the model gives youis_order_placedinstead - exactly one row per order on its first SALE day. Divide in your BI tool:SUM(gross_sales + discounts) / SUM(is_order_placed), which is Shopify's own definition (gross less discounts, over orders) rather than dividing total sales by orders.
The Shopify connector lands the raw tables; a staging model wraps each one; shopify_sales_over_time reads all nine. Nine models sounds like a lot until you push the folder once and let Weld work out the dependency order.
What the full model adds
The excerpts above are the sales equation. The rest of the model exists because reconciling with Shopify's admin means handling the awkward cases:
| Why it's there | |
|---|---|
Parity variants — net_sales_shopify_parity, three quantity columns | Shopify's admin UI and its CSV export don't agree on units or on how order edits count. Rather than pick a winner, the model ships both and documents which is which. |
Returns detail — the reversal block, plus reversal_type | restock_type separates a cancellation (never shipped) from a genuine return (came back, restocked) from a refund with no restock. Return rate on all three together is noise. |
| Gift cards — four separate columns | Selling a gift card is deferred revenue, not product revenue. Shopify keeps it out of the sales equation, so the model tracks it alongside instead of inside. |
location_name and refund_location_name | Two different facts, so two columns. A POS sale carries a location; a refund of it processed through the web admin carries a different one, or none. Merging them into one column makes it impossible to ask either question. location_name is NULL when Shopify recorded no location — the model reports the absence rather than labelling it "Online", because "no physical location" is not the same claim. |
sales_channel — Shopify's value, not an interpretation | source_name verbatim: web for the online store, pos for retail, shopify_draft_order for manual orders, otherwise the handle of the app or marketplace that created the order. Split retail from online with this, or with location_name IS NOT NULL. |
country — a cascade, not a column | Shipping country is NULL for digital goods and POS, so it falls back to billing, then the location's country. |
fulfillment_status — with a digital-goods case | An order of only digital products never gets a status, which reads as "unfulfilled" in every dashboard and panics ops. |
Presentment money — plus shopify_fx_rate | Converting shop money at your own daily rate gives a number that doesn't match what the customer paid, because Shopify used its rate at checkout. The model derives Shopify's own implied rate per order. |
is_order_placed | Flags exactly one row per order on its first SALE day — the AOV denominator Shopify uses. At order grain you divide in your BI tool rather than hardcoding AOV into the model. |
Two things worth knowing before you run it. The timezone is read from
shop.iana_timezone, so there's nothing to hardcode. And the grain is
day × store × order × location × channel × row type — a day where an order both sells
and refunds produces two rows, which is what keeps the SALE and RETURN sides
independently auditable. Aggregate to day in your BI tool.
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. Because the model is layered, GitHub Sync is the easier path here: push the folder and Weld works out the dependency order between the staging models and the report. 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
The all-time sales bridge: gross sales, less discounts and returns, gives net sales; shipping and taxes are then added to reach total sales. On this store the deductions are a thin sliver of gross - most of it survives all the way 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. Something is truncating
happened_atto a date before converting it to store-local time. The model localises first, usingshop.iana_timezone- check that column is actually populated for your store. - Your numbers are consistently higher than Shopify's. Test orders aren't excluded — they're filtered in the
orderstaging model, ontest = TRUE. Resist the urge to also droppending: an authorised-but-uncaptured order (bank transfer, cash on delivery, manual payment) is a sale Shopify counts, so excluding it understates instead. Onlyvoidedis dropped in core, andcancelled_at IS NULLis applied in the analytics layer, where you can change it without core having already destroyed the rows. - Total sales looks too low. Don't exclude
refundedorpartially_refundedorders - 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_amounton an agreement sale line is tax-inclusive; every component excepttaxesis tax-exclusive. That's whatamount_ex_taxis 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 totals don't match the payment processor. You're converting shop money at your own daily rate, but Shopify used its rate at checkout. The model already carries
presentment_net_sales- what the customer actually paid - plusshopify_fx_rate, Shopify's own implied rate per order. Use those rather than converting yourself, and cross-currency totals land much closer to settlement. - 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, sumline_type = 'GIFT_CARD'into separate columns rather than folding them into gross sales. - Digital-only orders look permanently unfulfilled. An order of only digital products never gets a fulfilment status, so every dashboard shows it as outstanding and ops chase it. The model reports those as
fulfillment_not_requiredinstead. - Your product report doesn't tie to your sales report. Almost always the non-product rows. Shipping, fees and gift cards belong to the order, not to any line, so a product-grain model has to carry them as rows with a NULL sku. Drop them and the product report sits below the sales report by exactly the value of shipping and fees.
- Every number inflates after adding a second storefront. Order IDs are only unique within a store, so a join missing
shopify_storefans rows out across brands. It fails silently — nothing errors, the totals just grow. - Margins show as 100%. A missing cost is being treated as zero. The model leaves
standard_costNULL where it doesn't know, so the gap is visible rather than flattering.
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 are two more, plus the cost model that turns revenue into margin.
Product sales over time — the one that reconciles
What it is: the same sales equation as the report above, at line grain — day × store × order × line. This is the model merchandising lives in, and the one that makes SKU-level margin possible.
Replicates: Native - Shopify's "Total sales by product", but on the event grain so it actually ties to your sales report.
Getting a product report to reconcile is harder than it looks, and three things do the work:
| Why it matters | |
|---|---|
| Rows with a NULL sku | Shipping, duties, fees and gift cards belong to the order, not to any line. They have to be carried as sku-less rows — drop them and this model sits below the sales report by exactly the value of shipping and fees. |
| Two separate weights | Not every agreement says which line it belongs to, so order-level money is spread across lines. Units follow quantity share, money follows revenue share. One weight for both distorts mixed-price baskets. |
| Residual de-duplication | Returns arrive from both agreement RETURN events and order_line_refund rows — the same money, twice. The refund rows attribute per line; only the unexplained remainder gets spread. Sum both and returns double. |
It also carries COGS. Costs come from Shopify itself — inventory_item for the current cost and inventory_item__history for every change — so a January order is valued at January's cost rather than today's:
Show sku_cost_per_day — point-in-time cost per SKU
1-- shopify_sku_cost_per_day
2-- Point-in-time standard cost per SKU, for every day that SKU actually sold.
3--
4-- Costs change. Valuing a January order at today's cost overstates or understates
5-- margin for the whole of history, so this picks the cost that was in effect on
6-- the day of the sale: the most recent cost change at or before that day, falling
7-- back to the earliest known cost for orders that predate any recorded change.
8--
9-- Only (day, SKU) pairs that sold are produced, so this stays small rather than
10-- materialising a full SKU x calendar grid.
11--
12-- Depends on: staging.shopify.order, .order_line, .inventory_item,
13-- .inventory_item__history, .shop
14
15WITH shop AS (
16 SELECT
17 shopify_store,
18 COALESCE(ANY_VALUE(iana_timezone), 'UTC') AS report_timezone
19 FROM {{staging.shopify.shop}}
20 GROUP BY shopify_store
21),
22
23-- Every cost that has ever applied: the history table plus the current value.
24cost_grid AS (
25 SELECT shopify_store, sku, cost, updated_at AS effective_ts
26 FROM {{staging.shopify.inventory_item__history}}
27 WHERE cost IS NOT NULL
28
29 UNION ALL
30
31 SELECT shopify_store, sku, cost, COALESCE(updated_at, created_at) AS effective_ts
32 FROM {{staging.shopify.inventory_item}}
33 WHERE cost IS NOT NULL
34),
35
36-- Only the days each SKU actually sold.
37day_skus AS (
38 SELECT DISTINCT
39 DATE(DATETIME(o.processed_at, s.report_timezone)) AS date,
40 ol.shopify_store,
41 ol.sku
42 FROM {{staging.shopify.order_line}} ol
43 JOIN {{staging.shopify.order}} o USING (shopify_store, order_id)
44 JOIN shop s USING (shopify_store)
45 WHERE NOT ol.is_gift_card
46 AND ol.sku IS NOT NULL
47 AND TRIM(ol.sku) != ''
48)
49
50SELECT
51 ds.date,
52 ds.shopify_store,
53 ds.sku,
54 cg.cost AS standard_cost,
55 cg.effective_ts AS cost_effective_at
56FROM day_skus ds
57LEFT JOIN cost_grid cg USING (shopify_store, sku)
58-- Prefer the newest cost effective on or before the sale day. The ELSE branch
59-- keeps the earliest known cost for orders predating any recorded change, so old
60-- orders get a cost rather than NULL.
61QUALIFY ROW_NUMBER() OVER (
62 PARTITION BY ds.date, ds.shopify_store, ds.sku
63 ORDER BY
64 CASE WHEN cg.effective_ts < TIMESTAMP(DATE_ADD(ds.date, INTERVAL 1 DAY)) THEN 0 ELSE 1 END,
65 cg.effective_ts DESC
66) = 1Note it leaves standard_cost NULL where it doesn't know, rather than zero. A zero cost reads as 100% margin and ends up in a board deck; a NULL shows up as the coverage gap it is.
Show the complete product model — all 491 lines (view in the repo)
1-- shopify_product_sales_over_time
2--
3-- The sales report at line grain: day x store x order x line. This is the model
4-- merchandising lives in, and the one that makes SKU-level margin possible.
5--
6-- It reconciles to sales_over_time. That is the whole point, and it is why the
7-- non-product rows are here: shipping, fees, gift cards and unattributable refund
8-- adjustments are emitted as rows with a NULL sku. Drop them and this model sits
9-- below the sales report by exactly the value of shipping and fees, and someone
10-- spends a week finding out why.
11--
12-- The hard part is attribution. An agreement records that money moved on an order,
13-- not always which line it belongs to, so product money is distributed across the
14-- order's lines by weight - units follow quantity share, money follows revenue
15-- share. Using one weight for both distorts mixed-price baskets.
16--
17-- Returns arrive from two places: agreement RETURN events and order_line_refund
18-- rows. Same money, two sources. Naively unioning both doubles returns, so the
19-- refund rows attribute per line and only the unexplained residual is spread.
20--
21-- Depends on: staging.shopify.{order, order_line, order_agreement,
22-- order_agreement_sale, order_refund, order_line_refund, location,
23-- shop} and core.shopify.sku_cost_per_day
24
25WITH shop AS (
26 SELECT
27 shopify_store,
28 ANY_VALUE(currency) AS shop_currency,
29 COALESCE(ANY_VALUE(iana_timezone), 'UTC') AS report_timezone
30 FROM {{staging.shopify.shop}}
31 GROUP BY shopify_store
32),
33
34locations AS (
35 SELECT shopify_store, location_id, location_name, location_country_code
36 FROM {{staging.shopify.location}}
37),
38
39orders AS (
40 SELECT
41 o.shopify_store,
42 o.order_id,
43 o.order_name,
44 o.location_id,
45 o.source_name,
46 o.financial_status,
47 o.cancelled_at,
48 DATE(DATETIME(o.processed_at, s.report_timezone)) AS order_created_day,
49 -- No presentment-currency fallback and no default code: presentment is
50 -- the buyer's currency, and a guessed code mislabels every amount.
51 COALESCE(o.order_shop_currency, o.currency, s.shop_currency) AS shop_currency,
52 -- Shipping country is the right geography but is NULL for digital goods
53 -- and POS, so cascade to billing, then the location's country.
54 COALESCE(o.shipping_country_code, o.billing_country_code, l.location_country_code) AS country,
55 -- The order's own location, straight from Shopify, NULL when there is
56 -- none. Identical expression in sales_over_time so the two models
57 -- reconcile on this dimension - they previously disagreed, which meant
58 -- a per-location comparison between them could never tie.
59 l.location_name AS location_name
60 FROM {{staging.shopify.order}} o
61 JOIN shop s USING (shopify_store)
62 LEFT JOIN locations l USING (shopify_store, location_id)
63 -- Voided only - see sales_over_time. Keeping 'pending' matters for parity:
64 -- if the two models filter orders differently they cannot reconcile.
65 WHERE o.financial_status != 'voided'
66),
67
68-- Untitled lines are dropped by Shopify's product reports: deleted products,
69-- draft-order custom lines, API artifacts.
70order_lines AS (
71 SELECT
72 shopify_store, order_id, line_id, product_id, variant_id,
73 product_title, variant_title, sku, vendor,
74 CAST(quantity AS NUMERIC) AS ordered_quantity,
75 unit_price,
76 discounts AS line_discount,
77 gross_sales AS line_gross_sales,
78 gross_sales - discounts AS line_net_sales
79 FROM {{staging.shopify.order_line}}
80 WHERE NOT is_gift_card
81 AND product_title IS NOT NULL
82),
83
84-- Two weights, deliberately: money follows revenue share, units follow quantity
85-- share. The ELSE branch splits evenly for fully discounted orders, where every
86-- line is zero and a share would be undefined.
87line_weights AS (
88 SELECT
89 ol.*,
90 CASE
91 WHEN SUM(GREATEST(ol.ordered_quantity, 0))
92 OVER (PARTITION BY ol.shopify_store, ol.order_id) > 0
93 THEN SAFE_DIVIDE(GREATEST(ol.ordered_quantity, 0),
94 SUM(GREATEST(ol.ordered_quantity, 0))
95 OVER (PARTITION BY ol.shopify_store, ol.order_id))
96 ELSE SAFE_DIVIDE(1, COUNT(*) OVER (PARTITION BY ol.shopify_store, ol.order_id))
97 END AS quantity_weight,
98 CASE
99 WHEN SUM(GREATEST(ol.line_net_sales, 0))
100 OVER (PARTITION BY ol.shopify_store, ol.order_id) > 0
101 THEN SAFE_DIVIDE(GREATEST(ol.line_net_sales, 0),
102 SUM(GREATEST(ol.line_net_sales, 0))
103 OVER (PARTITION BY ol.shopify_store, ol.order_id))
104 ELSE SAFE_DIVIDE(1, COUNT(*) OVER (PARTITION BY ol.shopify_store, ol.order_id))
105 END AS revenue_weight
106 FROM order_lines ol
107),
108
109agreements AS (
110 SELECT shopify_store, order_agreement_id, order_id, happened_at, reason
111 FROM {{staging.shopify.order_agreement}}
112),
113
114sales AS (
115 SELECT
116 shopify_store, order_agreement_id, order_id, line_type, action_type,
117 CAST(quantity AS NUMERIC) AS quantity,
118 total_tax, discount_before_tax, amount_ex_tax
119 FROM {{staging.shopify.order_agreement_sale}}
120),
121
122events AS (
123 SELECT
124 DATE(DATETIME(a.happened_at, s.report_timezone)) AS date,
125 a.shopify_store, a.order_id, a.reason,
126 sa.line_type, sa.action_type, sa.quantity,
127 sa.total_tax, sa.discount_before_tax, sa.amount_ex_tax
128 FROM agreements a
129 JOIN shop s USING (shopify_store)
130 JOIN sales sa USING (shopify_store, order_agreement_id, order_id)
131),
132
133-- ---------------------------------------------------------------- product sales
134sale_order_day AS (
135 SELECT
136 date, shopify_store, order_id,
137 SUM(quantity) AS sale_quantity,
138 SUM(CASE WHEN action_type = 'ORDER'
139 AND reason NOT IN ('RETURN', 'ORDER_EDIT') THEN quantity ELSE 0 END)
140 AS quantity_ordered_shopify_compat,
141 SUM(CASE WHEN action_type = 'ORDER'
142 AND reason != 'RETURN' THEN quantity ELSE 0 END)
143 AS quantity_ordered_shopify_export_parity,
144 SUM(amount_ex_tax) AS net_sales,
145 (-1) * SUM(GREATEST(discount_before_tax, 0)) AS discounts,
146 SUM(total_tax) AS taxes
147 FROM events
148 WHERE line_type = 'PRODUCT' AND action_type IN ('ORDER', 'UPDATE')
149 GROUP BY 1, 2, 3
150),
151
152sale_line_events AS (
153 SELECT
154 d.date, o.shopify_store, o.order_id, o.order_name, o.financial_status,
155 o.cancelled_at, o.location_id, o.source_name, o.shop_currency, o.country,
156 o.location_name, 'SALE' AS report_row_type,
157 lw.line_id, lw.product_id, lw.variant_id, lw.product_title, lw.variant_title,
158 lw.sku, lw.vendor, lw.unit_price,
159 d.sale_quantity * lw.quantity_weight AS quantity,
160 d.quantity_ordered_shopify_compat * lw.quantity_weight AS quantity_ordered_shopify_compat,
161 d.quantity_ordered_shopify_export_parity * lw.quantity_weight AS quantity_ordered_shopify_export_parity,
162 -- Fractional on purpose: each line carries its share of one order, so the
163 -- lines still sum to exactly 1.0 per order-day and the two reports tie.
164 CASE WHEN d.date = o.order_created_day THEN lw.revenue_weight ELSE 0 END AS orders,
165 (d.net_sales - d.discounts) * lw.revenue_weight AS gross_sales,
166 d.discounts * lw.revenue_weight AS discounts,
167 CAST(0 AS NUMERIC) AS returns,
168 d.net_sales * lw.revenue_weight AS net_sales,
169 CAST(0 AS NUMERIC) AS shipping_charges,
170 CAST(0 AS NUMERIC) AS duties,
171 CAST(0 AS NUMERIC) AS additional_fees,
172 CAST(0 AS NUMERIC) AS return_fees,
173 d.taxes * lw.revenue_weight AS taxes,
174 CAST(0 AS NUMERIC) AS sales_reversals,
175 CAST(0 AS NUMERIC) AS discount_reversals,
176 CAST(0 AS NUMERIC) AS tax_reversals,
177 CAST(0 AS NUMERIC) AS shipping_reversals,
178 CAST(0 AS NUMERIC) AS reversed_quantity,
179 CAST(0 AS NUMERIC) AS gift_card_gross_sales,
180 CAST(0 AS NUMERIC) AS gift_card_net_sales,
181 CAST(0 AS NUMERIC) AS gift_card_discounts,
182 CAST(0 AS NUMERIC) AS gift_card_taxes
183 FROM line_weights lw
184 JOIN sale_order_day d USING (shopify_store, order_id)
185 JOIN orders o USING (shopify_store, order_id)
186),
187
188-- ---------------------------------------------------------------------- returns
189refunds AS (
190 SELECT
191 r.shopify_store, r.refund_id, r.order_id,
192 DATE(DATETIME(r.refund_created_at, s.report_timezone)) AS date
193 FROM {{staging.shopify.order_refund}} r
194 JOIN shop s USING (shopify_store)
195),
196
197-- What the agreements say the return was worth, per order-day.
198return_order_day AS (
199 SELECT
200 date, shopify_store, order_id,
201 SUM(quantity) AS return_quantity,
202 SUM(amount_ex_tax) AS return_net_sales,
203 (-1) * SUM(discount_before_tax) AS return_discount_reversals,
204 SUM(total_tax) AS return_taxes
205 FROM events
206 WHERE action_type = 'RETURN' AND line_type IN ('PRODUCT', 'ADJUSTMENT')
207 GROUP BY 1, 2, 3
208),
209
210-- What the per-line refund rows already account for.
211refund_line_totals AS (
212 SELECT
213 r.date, r.shopify_store, r.order_id, olr.order_line_id AS line_id,
214 SUM(CAST(olr.quantity AS NUMERIC)) AS refund_quantity
215 FROM {{staging.shopify.order_line_refund}} olr
216 JOIN refunds r USING (shopify_store, refund_id)
217 GROUP BY 1, 2, 3, 4
218),
219
220refund_order_day AS (
221 SELECT date, shopify_store, order_id, SUM(refund_quantity) AS refund_quantity
222 FROM refund_line_totals
223 GROUP BY 1, 2, 3
224),
225
226-- Returns land per line where the refund rows say so.
227return_line_events AS (
228 SELECT
229 rlt.date, o.shopify_store, o.order_id, o.order_name, o.financial_status,
230 o.cancelled_at, o.location_id, o.source_name, o.shop_currency, o.country,
231 o.location_name, 'RETURN' AS report_row_type,
232 lw.line_id, lw.product_id, lw.variant_id, lw.product_title, lw.variant_title,
233 lw.sku, lw.vendor, lw.unit_price,
234 (-1) * rlt.refund_quantity AS quantity,
235 CAST(0 AS NUMERIC) AS quantity_ordered_shopify_compat,
236 CAST(0 AS NUMERIC) AS quantity_ordered_shopify_export_parity,
237 CAST(0 AS NUMERIC) AS orders,
238 CAST(0 AS NUMERIC) AS gross_sales,
239 CAST(0 AS NUMERIC) AS discounts,
240 (-1) * rlt.refund_quantity * lw.unit_price AS returns,
241 (-1) * rlt.refund_quantity * lw.unit_price AS net_sales,
242 CAST(0 AS NUMERIC) AS shipping_charges,
243 CAST(0 AS NUMERIC) AS duties,
244 CAST(0 AS NUMERIC) AS additional_fees,
245 CAST(0 AS NUMERIC) AS return_fees,
246 CAST(0 AS NUMERIC) AS taxes,
247 (-1) * rlt.refund_quantity * lw.unit_price AS sales_reversals,
248 CAST(0 AS NUMERIC) AS discount_reversals,
249 CAST(0 AS NUMERIC) AS tax_reversals,
250 CAST(0 AS NUMERIC) AS shipping_reversals,
251 (-1) * rlt.refund_quantity AS reversed_quantity,
252 CAST(0 AS NUMERIC) AS gift_card_gross_sales,
253 CAST(0 AS NUMERIC) AS gift_card_net_sales,
254 CAST(0 AS NUMERIC) AS gift_card_discounts,
255 CAST(0 AS NUMERIC) AS gift_card_taxes
256 FROM refund_line_totals rlt
257 JOIN line_weights lw USING (shopify_store, order_id, line_id)
258 JOIN orders o USING (shopify_store, order_id)
259),
260
261-- Whatever the agreements say was returned but the refund rows did not explain -
262-- partial refunds, goodwill credits - spread across the order's lines. Without
263-- this the two reports disagree; with it counted twice, returns double.
264return_residual AS (
265 SELECT
266 rod.date, rod.shopify_store, rod.order_id,
267 rod.return_quantity + COALESCE(rfd.refund_quantity, 0) AS residual_quantity,
268 rod.return_net_sales,
269 rod.return_discount_reversals,
270 rod.return_taxes
271 FROM return_order_day rod
272 LEFT JOIN refund_order_day rfd USING (date, shopify_store, order_id)
273),
274
275return_adjustment_events AS (
276 SELECT
277 rr.date, o.shopify_store, o.order_id, o.order_name, o.financial_status,
278 o.cancelled_at, o.location_id, o.source_name, o.shop_currency, o.country,
279 o.location_name, 'RETURN' AS report_row_type,
280 CAST(NULL AS INT64) AS line_id,
281 CAST(NULL AS INT64) AS product_id,
282 CAST(NULL AS INT64) AS variant_id,
283 CAST(NULL AS STRING) AS product_title,
284 CAST(NULL AS STRING) AS variant_title,
285 CAST(NULL AS STRING) AS sku,
286 CAST(NULL AS STRING) AS vendor,
287 CAST(NULL AS NUMERIC) AS unit_price,
288 rr.residual_quantity AS quantity,
289 CAST(0 AS NUMERIC) AS quantity_ordered_shopify_compat,
290 CAST(0 AS NUMERIC) AS quantity_ordered_shopify_export_parity,
291 CAST(0 AS NUMERIC) AS orders,
292 CAST(0 AS NUMERIC) AS gross_sales,
293 CAST(0 AS NUMERIC) AS discounts,
294 rr.return_net_sales AS returns,
295 rr.return_net_sales AS net_sales,
296 CAST(0 AS NUMERIC) AS shipping_charges,
297 CAST(0 AS NUMERIC) AS duties,
298 CAST(0 AS NUMERIC) AS additional_fees,
299 CAST(0 AS NUMERIC) AS return_fees,
300 rr.return_taxes AS taxes,
301 rr.return_net_sales AS sales_reversals,
302 rr.return_discount_reversals AS discount_reversals,
303 rr.return_taxes AS tax_reversals,
304 CAST(0 AS NUMERIC) AS shipping_reversals,
305 rr.residual_quantity AS reversed_quantity,
306 CAST(0 AS NUMERIC) AS gift_card_gross_sales,
307 CAST(0 AS NUMERIC) AS gift_card_net_sales,
308 CAST(0 AS NUMERIC) AS gift_card_discounts,
309 CAST(0 AS NUMERIC) AS gift_card_taxes
310 FROM return_residual rr
311 JOIN orders o USING (shopify_store, order_id)
312 -- Floating-point residue would otherwise produce thousands of near-zero rows.
313 WHERE ABS(rr.return_net_sales) > 0.0001
314 OR ABS(rr.return_discount_reversals) > 0.0001
315 OR ABS(rr.residual_quantity) > 0.0001
316),
317
318-- ------------------------------------------------- non-product money, NULL sku
319-- These belong to the order, not to any line. Shopify's own product exports show
320-- them with an empty product column, and they are what makes this model tie out
321-- against sales_over_time.
322non_product_day AS (
323 SELECT
324 date, shopify_store, order_id,
325 SUM(CASE WHEN line_type = 'SHIPPING' THEN amount_ex_tax ELSE 0 END) AS shipping_charges,
326 SUM(CASE WHEN line_type = 'SHIPPING' AND action_type = 'RETURN'
327 THEN amount_ex_tax ELSE 0 END) AS shipping_reversals,
328 SUM(CASE WHEN line_type = 'DUTY' THEN amount_ex_tax ELSE 0 END) AS duties,
329 SUM(CASE WHEN line_type = 'FEE' THEN amount_ex_tax ELSE 0 END) AS return_fees,
330 SUM(CASE WHEN line_type IN ('SHIPPING', 'DUTY', 'FEE')
331 THEN total_tax ELSE 0 END) AS taxes,
332 SUM(CASE WHEN line_type = 'GIFT_CARD'
333 THEN amount_ex_tax + GREATEST(discount_before_tax, 0) ELSE 0 END) AS gift_card_gross_sales,
334 SUM(CASE WHEN line_type = 'GIFT_CARD' THEN amount_ex_tax ELSE 0 END) AS gift_card_net_sales,
335 (-1) * SUM(CASE WHEN line_type = 'GIFT_CARD'
336 THEN GREATEST(discount_before_tax, 0) ELSE 0 END) AS gift_card_discounts,
337 SUM(CASE WHEN line_type = 'GIFT_CARD' THEN total_tax ELSE 0 END) AS gift_card_taxes
338 FROM events
339 WHERE line_type IN ('SHIPPING', 'DUTY', 'FEE', 'GIFT_CARD')
340 GROUP BY 1, 2, 3
341),
342
343non_product_line_events AS (
344 SELECT
345 d.date, o.shopify_store, o.order_id, o.order_name, o.financial_status,
346 o.cancelled_at, o.location_id, o.source_name, o.shop_currency, o.country,
347 o.location_name, 'SALE' AS report_row_type,
348 CAST(NULL AS INT64) AS line_id,
349 CAST(NULL AS INT64) AS product_id,
350 CAST(NULL AS INT64) AS variant_id,
351 CAST(NULL AS STRING) AS product_title,
352 CAST(NULL AS STRING) AS variant_title,
353 CAST(NULL AS STRING) AS sku,
354 CAST(NULL AS STRING) AS vendor,
355 CAST(NULL AS NUMERIC) AS unit_price,
356 CAST(0 AS NUMERIC) AS quantity,
357 CAST(0 AS NUMERIC) AS quantity_ordered_shopify_compat,
358 CAST(0 AS NUMERIC) AS quantity_ordered_shopify_export_parity,
359 CAST(0 AS NUMERIC) AS orders,
360 CAST(0 AS NUMERIC) AS gross_sales,
361 CAST(0 AS NUMERIC) AS discounts,
362 CAST(0 AS NUMERIC) AS returns,
363 CAST(0 AS NUMERIC) AS net_sales,
364 d.shipping_charges AS shipping_charges,
365 d.duties AS duties,
366 CAST(0 AS NUMERIC) AS additional_fees,
367 d.return_fees AS return_fees,
368 d.taxes AS taxes,
369 CAST(0 AS NUMERIC) AS sales_reversals,
370 CAST(0 AS NUMERIC) AS discount_reversals,
371 CAST(0 AS NUMERIC) AS tax_reversals,
372 d.shipping_reversals AS shipping_reversals,
373 CAST(0 AS NUMERIC) AS reversed_quantity,
374 d.gift_card_gross_sales,
375 d.gift_card_net_sales,
376 d.gift_card_discounts,
377 d.gift_card_taxes
378 FROM non_product_day d
379 JOIN orders o USING (shopify_store, order_id)
380),
381
382-- UNION ALL matches positionally, not by name: every branch must emit the same
383-- columns in the same order. Verbose, but adding an event type is copy-paste
384-- plus one union line.
385line_events AS (
386 SELECT * FROM sale_line_events
387 UNION ALL SELECT * FROM return_line_events
388 UNION ALL SELECT * FROM return_adjustment_events
389 UNION ALL SELECT * FROM non_product_line_events
390),
391
392final_base AS (
393 SELECT
394 date, shopify_store, order_id, order_name, financial_status, cancelled_at,
395 location_id, source_name, shop_currency, country, location_name,
396 report_row_type, line_id, product_id, variant_id, product_title,
397 variant_title, sku, vendor,
398 MAX(unit_price) AS unit_price,
399 SUM(quantity) AS quantity,
400 SUM(quantity_ordered_shopify_compat) AS quantity_ordered_shopify_compat,
401 SUM(quantity_ordered_shopify_export_parity) AS quantity_ordered_shopify_export_parity,
402 SUM(orders) AS orders,
403 SUM(gross_sales) AS gross_sales,
404 SUM(discounts) AS discounts,
405 SUM(returns) AS returns,
406 SUM(net_sales) AS net_sales,
407 SUM(shipping_charges) AS shipping_charges,
408 SUM(duties) AS duties,
409 SUM(additional_fees) AS additional_fees,
410 SUM(return_fees) AS return_fees,
411 SUM(taxes) AS taxes,
412 SUM(sales_reversals) AS sales_reversals,
413 SUM(discount_reversals) AS discount_reversals,
414 SUM(tax_reversals) AS tax_reversals,
415 SUM(shipping_reversals) AS shipping_reversals,
416 SUM(reversed_quantity) AS reversed_quantity,
417 SUM(gift_card_gross_sales) AS gift_card_gross_sales,
418 SUM(gift_card_net_sales) AS gift_card_net_sales,
419 SUM(gift_card_discounts) AS gift_card_discounts,
420 SUM(gift_card_taxes) AS gift_card_taxes
421 FROM line_events
422 GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19
423)
424
425SELECT
426 fb.date,
427 fb.shopify_store,
428 fb.order_id,
429 fb.order_name,
430 fb.report_row_type,
431 fb.financial_status,
432 fb.cancelled_at IS NOT NULL AS is_cancelled,
433 fb.location_name,
434 fb.country,
435 fb.source_name,
436 LOWER(fb.source_name) AS sales_channel,
437 fb.shop_currency,
438
439 fb.line_id,
440 fb.product_id,
441 fb.variant_id,
442 fb.product_title,
443 fb.variant_title,
444 fb.sku,
445 fb.vendor,
446 fb.unit_price,
447
448 fb.orders,
449 fb.quantity,
450 fb.quantity_ordered_shopify_compat,
451 fb.quantity_ordered_shopify_export_parity,
452
453 ROUND(fb.gross_sales, 2) AS gross_sales,
454 ROUND(fb.discounts, 2) AS discounts,
455 ROUND(fb.returns, 2) AS returns,
456 ROUND(fb.net_sales, 2) AS net_sales,
457 ROUND(fb.shipping_charges, 2) AS shipping_charges,
458 ROUND(fb.duties, 2) AS duties,
459 ROUND(fb.additional_fees, 2) AS additional_fees,
460 ROUND(fb.return_fees, 2) AS return_fees,
461 ROUND(fb.taxes, 2) AS taxes,
462 ROUND(fb.net_sales + fb.shipping_charges + fb.duties
463 + fb.return_fees + fb.additional_fees + fb.taxes, 2) AS total_shopify_sales,
464 ROUND(fb.net_sales + fb.shipping_charges
465 + fb.return_fees + fb.additional_fees, 2) AS total_sales,
466
467 ROUND(fb.sales_reversals, 2) AS net_sales_reversals,
468 ROUND(fb.sales_reversals - fb.discount_reversals, 2) AS gross_sales_reversals,
469 ROUND(fb.sales_reversals + fb.tax_reversals
470 + fb.shipping_reversals + fb.return_fees, 2) AS total_sales_reversals,
471 ROUND(fb.discount_reversals, 2) AS discount_reversals,
472 ROUND(fb.tax_reversals, 2) AS tax_reversals,
473 ROUND(fb.shipping_reversals, 2) AS shipping_reversals,
474 fb.reversed_quantity,
475
476 ROUND(fb.gift_card_gross_sales, 2) AS gift_card_gross_sales,
477 ROUND(fb.gift_card_net_sales, 2) AS gift_card_net_sales,
478 ROUND(fb.gift_card_discounts, 2) AS gift_card_discounts,
479 ROUND(fb.gift_card_taxes, 2) AS gift_card_taxes,
480 ROUND(fb.taxes - fb.gift_card_taxes, 2) AS taxes_excluding_gift_cards,
481
482 -- Point-in-time cost, so a January order is valued at January's cost. NULL
483 -- rather than 0 where cost is unknown: a zero cost reads as 100% margin and
484 -- ends up in a board deck, a NULL shows up as the coverage gap it is.
485 scd.standard_cost,
486 ROUND(fb.quantity * scd.standard_cost, 2) AS cogs,
487 ROUND(fb.net_sales - (fb.quantity * scd.standard_cost), 2) AS gross_profit
488FROM final_base fb
489LEFT JOIN {{core.shopify.sku_cost_per_day}} scd
490 USING (date, shopify_store, sku)
491ORDER BY fb.date, fb.shopify_store, fb.order_id, fb.line_idIf you take one thing from this model, take the reconciliation test that ships with it: sum the product level by day and diff it against the order level. If it returns rows, start with the NULL-sku rows.
Sales by product — the quick ranking one
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_lineon the order date. That's the right trade-off for ranking top sellers -order_lineis 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, use product sales over time above instead - that's exactly what it's for.
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-- Depends on: staging.shopify.order, .order_line, .product
4--
5-- SCOPE: net of discounts, NOT net of returns. Does not reconcile to
6-- shopify_sales_over_time - returns stay on the original order date. Right for
7-- ranking top sellers, wrong for SKU revenue that has to tie out.
8
9WITH orders AS (
10 SELECT shopify_store, order_id, DATE(processed_at) AS date
11 FROM {{staging.shopify.order}}
12)
13
14SELECT
15 o.date,
16 l.shopify_store,
17 l.product_id,
18 -- Canonical product attributes beat the order_line snapshot, which was taken
19 -- at purchase time and goes stale if a product is renamed.
20 COALESCE(p.product_title, l.product_title) AS product_title,
21 p.product_type,
22 COALESCE(p.vendor, l.vendor) AS vendor,
23 l.variant_id,
24 l.variant_title,
25 l.sku,
26 SUM(l.quantity) AS units,
27 COUNT(DISTINCT o.order_id) AS orders,
28 ROUND(SUM(l.gross_sales), 2) AS gross_sales,
29 ROUND(SUM(l.discounts), 2) AS discounts,
30 ROUND(SUM(l.gross_sales - l.discounts), 2) AS net_sales
31FROM {{staging.shopify.order_line}} l
32-- shopify_store in every join key: order IDs are only unique within a store.
33JOIN orders o USING (shopify_store, order_id)
34LEFT JOIN {{staging.shopify.product}} p USING (shopify_store, product_id)
35-- Gift cards are deferred revenue, not product revenue. staging.shopify.order_line
36-- keeps them, so exclude them here.
37WHERE NOT l.is_gift_card
38-- Ordinals, not names: product_title and vendor exist on both joined tables, so
39-- a bare column name here would be ambiguous.
40GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9
41ORDER BY o.date, net_sales DESCsales_by_product reads the same staging models as the sales report, joining orders to lines and product to roll 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 Shopify template library, where all three models and their staging dependencies are ready to copy, or from the public repo that is the source of truth for both.
- 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
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.
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.
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.
At minimum: order, order_agreement and order_agreement_sale. The full sales report also uses order_line, order_refund, order_line_refund, shop and location. Product-level margin adds inventory_item plus its history table, and the quick ranking template uses product. Sync them all in one go with the Shopify connector.
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).
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.
The models are already multi-store safe. Order IDs are only unique within a store, so shopify_store is carried through every join key, window partition and GROUP BY - without it, rows silently fan out across brands. Each staging model labels its rows with the store it came from, so adding a storefront means adding a UNION ALL block in staging and nothing else; core and analytics need no changes. Where it gets harder is schema drift between stores that were set up years apart, which is what the dbt version of this guide covers.
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.
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.
Related reading
- Build the same report as a production dbt model →
- Why Shopify's reports don't match your data warehouse →
- Shopify's official sales report documentation →
- BigQuery vs. Snowflake →
- Best ETL Tools in 2026 →
- How to build a modern data stack →
- BigQuery partitioning and clustering →
- How to calculate MRR with Stripe using SQL →



