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.

MetricFormulaExcludes
Gross salesprice × quantitytax, shipping, discounts, reversals
Discountscode, automatic and manual discounts, applied before taxstored negative
Sales reversalsrefunds, cancellations, order edits, exchangesstored negative
Net salesgross sales − discounts − reversalstax, shipping
Total salesnet sales + shipping + duties + fees + taxes—
AOV(gross sales − discounts) ÷ ordersreversals, 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 + reversals is 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_sales by 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, shop and location. The product model adds inventory_item and its history table for COGS; the quick ranking template uses product. 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 shop table, 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:

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.

One order produces three rows on three different days: the sale on 1 March, an order edit on 3 March and a refund on 20 March. Summing order_line collapses all of it onto 1 March.

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_typeFeeds
PRODUCTGross sales, discounts, net sales, units
SHIPPINGShipping charges
DUTYDuties
FEEReturn and restocking fees
GIFT_CARDGift card sales - deferred revenue, kept out of the sales equation
ADJUSTMENTRefund value Shopify can't tie to a specific product line
action_typeMeaningCounts as
ORDERThe original saleSALE
UPDATEAn order edit that changed valueSALE
RETURNA refund or returnRETURN

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 to

That'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.

SQL
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.

SQL
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:

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 taxes

Everything 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
SQL
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_type

Why the agreement log and not order_line? order_line is 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. Use order_line for product identity - SKU, title, variant - and the agreement log for anything time-based.

On AOV: there's no average_order_value column. At order grain it would be meaningless on a single row, so the model gives you is_order_placed instead - 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.

Lineage of the Shopify sales report: nine raw Shopify tables feed nine staging models, which feed the core sales_over_time model, which feeds a thin analytics model

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 columnsShopify'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_typerestock_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 columnsSelling 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_nameTwo 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 interpretationsource_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 columnShipping 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 caseAn order of only digital products never gets a status, which reads as "unfulfilled" in every dashboard and panics ops.
Presentment money — plus shopify_fx_rateConverting 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_placedFlags 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

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; 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_at to a date before converting it to store-local time. The model localises first, using shop.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 order staging model, on test = TRUE. Resist the urge to also drop pending: an authorised-but-uncaptured order (bank transfer, cash on delivery, manual payment) is a sale Shopify counts, so excluding it understates instead. Only voided is dropped in core, and cancelled_at IS NULL is 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 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 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 - plus shopify_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, sum line_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_required instead.
  • 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_store fans 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_cost NULL 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 skuShipping, 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 weightsNot 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-duplicationReturns 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
SQL
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) = 1

Note 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)
SQL
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_id

If 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_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, use product sales over time above instead - that's exactly what it's for.

The SQL (view in the repo):

SQL
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 DESC
Lineage: three raw Shopify tables feed three staging models, which feed the core sales_by_product model, which feeds a thin analytics model

sales_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.

BI dashboard: top products by net sales

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:

  1. 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.
  2. Get them into Weld. Connect the repo with GitHub Sync (push to deploy), or paste each model into a new Weld transform.
  3. 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.
  4. 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

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.