Why rebuild Shopify's sales report in dbt?

Summary: Shopify's sales reports are the numbers your finance team trusts and your board deck repeats, but they live behind an admin UI with a date picker and a CSV button. This guide is the production dbt blueprint for reproducing them in your warehouse: the sales equation component by component, why order_line is the wrong source table, the raw → staging → core → analytics lineage, annotated SQL for every metric, the parity gotchas that cost a week, and multi-store plus multi-currency handling. Every example is dbt on BigQuery; the logic ports to Snowflake, Databricks, Redshift or Postgres with minor dialect changes.

You need your own model once you have to do any of these:

  • Join sales to ad spend, COGS or shipping cost to get real profit per SKU
  • Report across multiple Shopify stores as one business
  • Report in a single reporting currency across stores that sell in many
  • Keep history at daily grain beyond Shopify's retention
  • Put sales next to marketing, support and supply-chain data in one BI layer

And this is where nearly everyone gets it wrong the first time: they SUM(price * quantity) off order_line, compare it to the admin, and find they're off by anywhere from 2% to 20%.

Just want a working query? If you're not on dbt and you have a single store, the standalone SQL version of this report is one query you can paste in and run today. This guide is what you graduate to when you have several storefronts, several currencies, and a finance team asking why the numbers moved.

Part 1 — The Shopify sales equation

Before writing a line of SQL, know exactly what you're reproducing. Shopify's sales reports are built on one equation, applied at whatever grain you slice by (day, product, channel, location, staff):

  Gross sales        product price x quantity, before anything else
- Discounts          order- and line-level discounts (shown negative)
- Returns            value of returned items (shown negative)
-----------------
= Net sales
+ Shipping charges
+ Duties
+ Additional fees
+ Taxes
-----------------
= Total sales

The definitions that trip people up

ComponentWhat it actually isCommon mistake
Gross salesLine price × quantity, excluding tax, shipping, discounts and returnsIncluding tax, or netting discounts out of it
DiscountsAll discounts applied before tax - code, automatic and manual. Stored negativeOnly counting discount codes, missing automatic and manual discounts
ReturnsValue of items returned or refunded, attributed to the refund date, stored negativeAttributing returns back to the original order date
Net salesGross sales – Discounts – Returns (with negative signs, this is arithmetic addition)Subtracting positive numbers twice and double-counting
Shipping chargesWhat the customer paid for shipping, excluding taxConfusing it with what shipping cost you - a different metric entirely
DutiesImport duties charged to the customerRolling them into shipping
Additional feesLocal or regulatory fees (bottle deposits, e-waste levies)Ignoring; usually 0 for most stores
TaxesVAT or sales tax collectedIncluding in gross sales
Total salesNet sales + shipping + duties + fees + taxesComparing against payouts, which are net of processing fees

Sign conventions: pick one and enforce it

The single biggest source of "why is my number wrong" is inconsistent signs. Adopt Shopify's own convention and never deviate:

  • Discounts are negative. A 100 discount is stored as -100.
  • Returns are negative. A 500 return is stored as -500.
  • Therefore net_sales = gross_sales + discounts + returns arithmetically, which is why in SQL you'll see gross_sales derived as net_sales - discounts - returns - subtracting a negative adds it back.

Once the convention is fixed, every downstream formula is a plain sum and every BI tool aggregates correctly without special-casing.

Total sales vs "total Shopify sales"

Worth building both:

SQL
1-- Shopify's Total sales (matches the UI)
2net_sales + shipping_charges + duties + return_fees + additional_fees + taxes
3  AS total_shopify_sales,
4
5-- Revenue-recognition friendly: excludes tax and duties (pass-through money)
6net_sales + shipping_charges + return_fees + additional_fees
7  AS total_sales

Finance almost always wants the second. The UI shows the first. Ship both columns and let each consumer pick - arguing about which is "correct" wastes a quarter.

Part 2 — The source tables that actually matter

Why order_line is the wrong table

The intuitive model is: an order has lines, lines have price and quantity, sum them up. It fails for a specific structural reason.

order_line is a current-state table. It tells you what the order looks like right now. Shopify's sales reports are event-based - they attribute value to the day the value changed. An order placed on the 1st, edited on the 3rd and partially refunded on the 20th produces value on three separate days. A current-state table can only tell you the end state.

Sum order_line and you get returns silently netted back to the original order date, invisible order edits, no way to reproduce a sales-over-time chart that matches the admin, and restated history - yesterday's report changes when someone refunds an old order.

The tables you actually need

Shopify exposes an event log through order agreements. Every change to an order's financial state creates an agreement, and each agreement carries the sale lines that changed.

TableRole in the sales reportRequired?
orderOrder header: status, currency, location, channel, addresses, test flagYes
order_agreementThe event log: one row per financial event, with happened_at + reasonYes
order_agreement_saleLine-level financial deltas per event - the heart of the reportYes
order_lineProduct identity (SKU, title, variant), gift-card flag, requires_shippingYes
order_refund / order_line_refundRefund dates, restock type, per-line refund amountsYes
shopStore's base currency (fallback only)Yes
locationPhysical and virtual location names, used to resolve location_nameRecommended
product / product_variantCanonical SKU, product statusRecommended
order_shipping_lineShipping method chosen - used to classify delivery typeOptional
transaction / transaction_feesPayment processor fees - needed for profit, not for salesOptional
FX rates (external)Daily exchange rates for multi-currency reportingIf multi-currency

The two columns that decide everything

Inside order_agreement_sale, two enum columns drive the entire report.

line_type — what kind of money this is:

line_typeMeaningFeeds
PRODUCTA product lineGross sales, discounts, net sales, quantity
SHIPPINGShipping charged to the customerShipping charges
DUTYImport dutiesDuties
FEEFees, typically return or restocking feesReturn fees
GIFT_CARDGift card purchaseGift card sales (excluded from product sales)
ADJUSTMENTReturn adjustments not tied to a product lineReturns
TIPTipsUsually excluded from sales

action_type — which direction the money moved:

action_typeMeaningRow type
ORDEROriginal saleSALE
UPDATEOrder edit adding or changing valueSALE
RETURNRefund or returnRETURN

Combine them and you get a matrix every metric selects a cell from:

PRODUCTSHIPPINGDUTYFEEGIFT_CARDADJUSTMENT
SALE (ORDER, UPDATE)gross sales, discounts, net sales, qtyshipping chargesdutiesfeesgift card sales—
RETURN (RETURN)returns, reversals, returned qtyshipping reversals—return fees—returns (no SKU)

None of this exists in isolation - order_agreement_sale sits inside Shopify's full relational model alongside order, order_line, customer, product and everything else Weld syncs. Here's the live schema, the same explorer you'd get from Shopify's connector page:

Open full-page schema explorer

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

Part 3 — The model architecture

Four layers, each with one job.

dbt architecture: three raw Shopify store schemas and an FX feed flow into staging models, then into two core sales models, then into two BI-facing analytics models

Each Shopify storefront lands in its own raw schema. Staging unions them behind a shopify_store key, so the two core models - and everything downstream - never know how many stores there are.

Raw — untouched ELT output. One schema per store. Never queried except by staging.

Staging — one model per source table. Union all stores, add a shopify_store key, cast types, nothing else. No business logic, no filters, no joins. This is what makes adding a fourth storefront a ten-minute job.

Core — where the reports live. core_shopify__sales_over_time is the Sales report at order × day × row-type grain. core_shopify__product_sales_over_time is the Sales-by-product report at order × line × day grain.

Analytics — thin and stable. Usually a SELECT * plus a join or two. The point is that dashboards bind to a contract you control, so you can refactor core freely.

SQL
1-- analytics__shopify_sales_over_time.sql
2SELECT * FROM {{ ref('core_shopify__sales_over_time') }}

Part 4 — Staging: unioning multiple stores

If you run one Shopify store, skip to Part 5. If you run regional storefronts - and most scaling DTC brands do - solve this first, because it affects every model above it.

Whatever you do, do it here. Staging is the only layer that should know how many stores exist; core just carries a shopify_store key through every join.

The simple version: UNION ALL

Each store lands in its own schema, so a staging model becomes one block per store, each labelling its rows:

SQL
1-- stg_shopify__order_agreement_sale.sql
2{{ config(materialized='table') }}
3
4SELECT 'store_1' AS shopify_store, /* ...columns... */
5FROM {{ source('shopify_store_1', 'order_agreement_sale') }}
6
7UNION ALL
8
9SELECT 'store_2' AS shopify_store, /* ...same columns... */
10FROM {{ source('shopify_store_2', 'order_agreement_sale') }}

That is what the ready-made templates do. It is plain SQL, there is nothing to configure, and adding a storefront touches staging only.

When UNION ALL isn't enough

It breaks the moment the stores' columns diverge - and they do. A store connected last month has fields one connected two years ago doesn't, and UNION ALL fails on the column count rather than degrading gracefully.

If you're maintaining more than two or three storefronts, that stops being hypothetical and the fix is a macro that introspects each source's columns, takes the union of all column names, and fills missing ones with NULL:

SQL
1{% macro shopify_union(table_name, store_sources, store_column='shopify_store') %}
2  {# store_sources = [('raw_schema_a', 'store_a'), ('raw_schema_b', 'store_b'), ...] #}
3
4  {% set refs = [] %}
5  {% for source_name, _ in store_sources %}
6    {% do refs.append(source(source_name, table_name)) %}
7  {% endfor %}
8
9  {% if not execute %}
10    SELECT * FROM {{ refs[0] }} WHERE 1 = 0
11  {% else %}
12
13    {# Collect every column name across every store #}
14    {% set relation_columns = {} %}
15    {% for source_name, _ in store_sources %}
16      {% do relation_columns.update({
17           source_name: adapter.get_columns_in_relation(refs[loop.index0])
18         }) %}
19    {% endfor %}
20
21    {% set all_columns = [] %}
22    {% set seen = [] %}
23    {% for source_name, _ in store_sources %}
24      {% for column in relation_columns[source_name] %}
25        {% if column.name | lower not in seen %}
26          {% do all_columns.append(column.name) %}
27          {% do seen.append(column.name | lower) %}
28        {% endif %}
29      {% endfor %}
30    {% endfor %}
31
32    {# Emit one SELECT per store, NULL-filling anything it lacks #}
33    {% for source_name, store_label in store_sources %}
34      {% set present = relation_columns[source_name]
35                       | map(attribute='name') | map('lower') | list %}
36      SELECT
37        {% for col in all_columns %}
38          {% if col | lower in present %}
39            {{ adapter.quote(col) }},
40          {% else %}
41            NULL AS {{ adapter.quote(col) }},
42          {% endif %}
43        {% endfor %}
44        '{{ store_label }}' AS {{ store_column }}
45      FROM {{ refs[loop.index0] }}
46      {% if not loop.last %}UNION ALL{% endif %}
47    {% endfor %}
48
49  {% endif %}
50{% endmacro %}

Each staging model then becomes three lines:

SQL
1-- stg_shopify__order_agreement_sale.sql
2{{ config(materialized='table') }}
3
4{% set store_sources = [
5    ('shopify_raw_store_1', 'store_1'),
6    ('shopify_raw_store_2', 'store_2'),
7    ('shopify_raw_store_3', 'store_3')
8] %}
9
10{{ shopify_union('order_agreement_sale', store_sources) }}

Either way, two rules will save you pain:

  1. shopify_store joins everywhere. Order IDs are only unique within a store. Every join, window partition and GROUP BY in every downstream model must include shopify_store. Forget it once and rows silently fan out across storefronts - every number inflates and nothing errors. Two related traps: your shop CTE must group by store rather than collapsing with ANY_VALUE, because currency and timezone vary between storefronts; and location IDs are per store, so those joins need the key too.
  2. Materialize the big ones as tables. order_agreement_sale and product_variant get hit repeatedly by the core models; leaving them as views makes the warehouse re-scan raw on every reference.

Part 5 — Building the sales-over-time model

The model, CTE by CTE:

orders      filter test + pending/voided/expired; resolve currency, country, location
agreements  exclude reason = 'voided'; carry happened_at + reason
sales       agreement sale lines; shop money + presentment money
    |
joined      agreements . sales . orders
            convert happened_at -> local date; flag checkout-flow edits
    |
typed       classify SALE vs RETURN
    |
agg         GROUP BY day, store, order, location, channel, row type
            -> every sales component
    |
final       components + parity variants + reporting currencies
            (joined with: dimensions, nearest-day FX)

Step 1 — Filter orders correctly

Your first CTE decides which orders exist at all. Get this wrong and nothing downstream can save you.

SQL
1WITH shop AS (
2  SELECT
3    CAST(shopify_shop AS STRING) AS shopify_shop,
4    ANY_VALUE(UPPER(NULLIF(TRIM(CAST(currency AS STRING)), ''))) AS shop_currency
5  FROM {{ ref('stg_shopify__shop') }}
6  GROUP BY 1
7),
8
9orders AS (
10  SELECT
11    CAST(o.shopify_store AS STRING) AS shopify_store,
12    CAST(o.id AS INT64)             AS order_id,
13    CAST(o.name AS STRING)          AS order_name,
14    CAST(o.location_id AS INT64)    AS location_id,
15    LOWER(CAST(o.source_name AS STRING)) AS source_name,
16
17    -- Currency resolution: the ORDER's own recorded currency wins over the
18    -- shop's CURRENT currency. Stores that changed base currency, or imported
19    -- historical orders from another platform, have orders genuinely
20    -- denominated in a different currency than the shop is today. Falling back
21    -- to the shop's current currency silently mislabels those amounts and can
22    -- inflate converted figures by the entire FX factor.
23    UPPER(NULLIF(TRIM(COALESCE(
24      CAST(o.current_total_price_set_shop_money_currency_code AS STRING),
25      CAST(o.currency AS STRING),
26      sh.shop_currency,
27      CAST(o.current_total_price_set_presentment_money_currency_code AS STRING),
28      CAST(o.presentment_currency AS STRING),
29      '{{ var("default_currency", "USD") }}'
30    )), '')) AS shop_currency,
31
32    o.created_at,
33    LOWER(CAST(o.financial_status AS STRING))   AS financial_status,
34    LOWER(CAST(o.fulfillment_status AS STRING)) AS fulfillment_status,
35    UPPER(NULLIF(TRIM(CAST(o.shipping_address_country_code AS STRING)), '')) AS shipping_country_code,
36    UPPER(NULLIF(TRIM(CAST(o.billing_address_country_code AS STRING)), ''))  AS billing_country_code,
37    CAST(o.cancelled_at AS TIMESTAMP) AS cancelled_at
38  FROM {{ ref('stg_shopify__order') }} o
39  LEFT JOIN shop sh
40    ON sh.shopify_shop = CAST(o.shopify_store AS STRING)
41  WHERE LOWER(CAST(o.financial_status AS STRING))
42          NOT IN ('pending', 'voided', 'expired')
43    AND o.test = FALSE
44)

Three decisions embedded there, all of which matter:

  • test = FALSE — test orders from theme development and gateway testing are real rows in raw data, and Shopify's reports exclude them. Miss this and your early history is junk.
  • Excluding pending, voided, expired — these never became revenue. Note that refunded and partially_refunded orders stay in: their refunds show up as RETURN events on the refund date, which is exactly what you want.
  • Currency precedence — the order's own currency beats the shop's current currency. This is the nastiest multi-currency bug: if your store ever changed base currency, or you migrated orders in from another platform, those historical orders carry a different currency, and defaulting to today's shop currency mislabels them by the full FX factor.

Step 2 — The event log

SQL
1agreements AS (
2  SELECT
3    CAST(shopify_store AS STRING)   AS shopify_store,
4    CAST(id AS STRING)              AS order_agreement_id,
5    CAST(order_id AS INT64)         AS order_id,
6    CAST(happened_at AS TIMESTAMP)  AS happened_at,
7    LOWER(CAST(app_handle AS STRING)) AS app_handle,
8    CAST(reason AS STRING)          AS reason
9  FROM {{ ref('stg_shopify__order_agreement') }}
10  WHERE LOWER(reason) != 'voided'
11),
12
13sales AS (
14  SELECT
15    CAST(shopify_store AS STRING)       AS shopify_store,
16    CAST(order_id AS INT64)             AS order_id,
17    CAST(order_agreement_id AS STRING)  AS order_agreement_id,
18    UPPER(CAST(line_type AS STRING))    AS line_type,
19    UPPER(CAST(action_type AS STRING))  AS action_type,
20    CAST(quantity AS INT64)             AS quantity,
21
22    -- Shop money = the store's own base currency
23    CAST(total_amount_shop_money_amount AS NUMERIC)     AS total_amount,
24    CAST(total_tax_amount_shop_money_amount AS NUMERIC) AS total_tax,
25    CAST(total_discount_amount_before_taxes_shop_money_amount AS NUMERIC)
26                                                        AS discount_before_tax,
27
28    -- Presentment money = what the customer actually saw and paid
29    CAST(total_amount_presentment_money_amount AS NUMERIC)     AS presentment_total_amount,
30    CAST(total_tax_amount_presentment_money_amount AS NUMERIC) AS presentment_total_tax,
31    CAST(total_discount_amount_before_taxes_presentment_money_amount AS NUMERIC)
32                                                               AS presentment_discount_before_tax,
33    UPPER(CAST(total_amount_presentment_money_currency_code AS STRING))
34                                                               AS presentment_currency
35  FROM {{ ref('stg_shopify__order_agreement_sale') }}
36)

reason = 'voided' agreements are cancellations of the agreement itself - they never represented money. Excluding them at the source is cleaner than netting them out later.

Step 3 — Join and localise the date

SQL
1-- Earliest ORDER agreement per order - used to detect checkout-flow edits
2order_first_time AS (
3  SELECT shopify_store, order_id, MIN(happened_at) AS order_created_at
4  FROM agreements
5  WHERE LOWER(reason) = 'order'
6  GROUP BY 1, 2
7),
8
9joined AS (
10  SELECT
11    -- Localise BEFORE truncating to a date. This is not optional.
12    DATE(DATETIME(a.happened_at, '{{ var("report_timezone", "UTC") }}')) AS event_day,
13    a.shopify_store,
14    a.order_id,
15    a.order_agreement_id,
16    o.location_id,
17    o.source_name,
18    s.line_type,
19    s.action_type,
20    a.reason,
21    COALESCE(s.quantity, 0)            AS quantity,
22    COALESCE(s.total_amount, 0)        AS total_amount,
23    COALESCE(s.total_tax, 0)           AS total_tax,
24    COALESCE(s.discount_before_tax, 0) AS discount_before_tax,
25    (COALESCE(s.total_amount, 0) - COALESCE(s.total_tax, 0)) AS amount_ex_tax,
26
27    -- Presentment equivalents, carried in parallel
28    COALESCE(s.presentment_total_amount, 0)        AS presentment_total_amount,
29    COALESCE(s.presentment_total_tax, 0)           AS presentment_total_tax,
30    COALESCE(s.presentment_discount_before_tax, 0) AS presentment_discount_before_tax,
31    s.presentment_currency,
32
33    -- Checkout-flow edit: an ORDER_EDIT within 90 seconds of order creation is
34    -- part of the original purchase (upsell widget, post-purchase offer), not a
35    -- later restatement of the order.
36    CASE
37      WHEN a.reason = 'ORDER_EDIT'
38       AND TIMESTAMP_DIFF(a.happened_at, oft.order_created_at, SECOND) <= 90
39      THEN TRUE ELSE FALSE
40    END AS is_checkout_edit
41
42  FROM agreements a
43  JOIN sales s
44    ON  s.order_agreement_id = a.order_agreement_id
45   AND  s.order_id           = a.order_id
46   AND  s.shopify_store      = a.shopify_store
47  JOIN orders o
48    ON  o.order_id      = a.order_id
49   AND  o.shopify_store = a.shopify_store
50  LEFT JOIN order_first_time oft
51    ON  oft.order_id      = a.order_id
52   AND  oft.shopify_store = a.shopify_store
53),
54
55typed AS (
56  SELECT
57    *,
58    CASE
59      WHEN action_type IN ('ORDER', 'UPDATE') THEN 'SALE'
60      WHEN action_type = 'RETURN'             THEN 'RETURN'
61      ELSE 'OTHER'
62    END AS report_row_type
63  FROM joined
64  WHERE action_type IN ('ORDER', 'UPDATE', 'RETURN')
65)

amount_ex_tax = total_amount - total_tax is the workhorse expression. Shopify's total_amount on an agreement sale line is tax-inclusive; every sales component except taxes is tax-exclusive. Subtract once, here, and reuse.

The 90-second window is the kind of rule you only discover by diffing against the admin UI. Post-purchase upsells and checkout-flow adjustments arrive as ORDER_EDIT agreements seconds after the order, and Shopify's UI treats them as part of the order. A genuine order edit three days later is a restatement. Ninety seconds separates the two cleanly in practice - tune it if your checkout has slower post-purchase flows.

Part 6 — Every sales component, in SQL

The core aggregation. Grain: day × store × order × location × channel × row type.

Keeping report_row_type in the grain (rather than collapsing to one row per order-day) means a day where an order both sells and refunds produces two rows, which keeps the SALE and RETURN sides independently auditable.

Orders count

SQL
1MAX(
2  CASE
3    WHEN report_row_type = 'SALE'
4     AND line_type   = 'PRODUCT'
5     AND action_type = 'ORDER'
6     AND reason NOT IN ('RETURN', 'ORDER_EDIT')
7    THEN 1 ELSE 0
8  END
9) AS orders

MAX not SUM - one order is one order regardless of how many product lines it has. Excluding ORDER_EDIT prevents an edited order being counted a second time on the edit date.

For a clean AOV denominator that counts every order exactly once across its whole lifetime, add a window-function flag in the final SELECT:

SQL
1-- Flags exactly ONE row per order, on its first SALE day. Summing this over any
2-- window gives "orders placed" - the denominator Shopify uses for AOV. Counts
3-- edit-only orders that have no ORDER row, ignores return-only orders, and
4-- never double-counts across locations or days.
5CASE
6  WHEN report_row_type = 'SALE'
7   AND ROW_NUMBER() OVER (
8         PARTITION BY shopify_store, order_id
9         ORDER BY
10           CASE WHEN report_row_type = 'SALE' THEN 0 ELSE 1 END,
11           day_date ASC, location_id ASC, source_name ASC, reason ASC
12       ) = 1
13  THEN 1 ELSE 0
14END AS is_order_placed

Quantity — three variants, and why you need all three

SQL
1-- 1. Net quantity: sales minus returns. The "real" unit movement.
2SUM(
3  CASE
4    WHEN report_row_type = 'SALE'   AND line_type IN ('PRODUCT', 'GIFT_CARD')  THEN quantity
5    WHEN report_row_type = 'RETURN' AND line_type IN ('PRODUCT', 'ADJUSTMENT') THEN quantity
6    ELSE 0
7  END
8) AS quantity,
9
10-- 2. Strict "units ordered": excludes returns AND all order edits.
11--    Matches the Shopify admin UI's units-ordered figure.
12SUM(
13  CASE
14    WHEN report_row_type = 'SALE'
15     AND line_type   = 'PRODUCT'
16     AND action_type = 'ORDER'
17     AND COALESCE(reason, '') NOT IN ('RETURN', 'ORDER_EDIT')
18    THEN quantity ELSE 0
19  END
20) AS quantity_ordered_shopify_compat,
21
22-- 3. Export parity: keeps checkout-flow edits, drops post-order edits.
23--    Matches Shopify's CSV export, which differs from the UI. Yes, really.
24SUM(
25  CASE
26    WHEN report_row_type = 'SALE'
27     AND line_type   IN ('PRODUCT', 'GIFT_CARD')
28     AND action_type = 'ORDER'
29     AND COALESCE(reason, '') != 'RETURN'
30     AND (COALESCE(reason, '') != 'ORDER_EDIT' OR is_checkout_edit)
31    THEN quantity ELSE 0
32  END
33) AS quantity_ordered_shopify_export_parity

Shopify's UI and its CSV export do not always agree on units. Rather than picking a winner, materialise both and document which is which. When finance asks why this doesn't match the export, you have an answer and a column.

Net sales, returns, discounts

SQL
1SUM(
2  CASE
3    WHEN report_row_type = 'SALE'   AND line_type = 'PRODUCT'                  THEN amount_ex_tax
4    WHEN report_row_type = 'RETURN' AND line_type IN ('PRODUCT', 'ADJUSTMENT') THEN amount_ex_tax
5    ELSE 0
6  END
7) AS net_sales,
8
9SUM(
10  CASE
11    WHEN report_row_type = 'RETURN' AND line_type IN ('PRODUCT', 'ADJUSTMENT')
12    THEN amount_ex_tax ELSE 0
13  END
14) AS returns,
15
16-- Split out separately: useful for diagnosing "unattributed" refund value
17SUM(
18  CASE
19    WHEN report_row_type = 'RETURN' AND line_type = 'ADJUSTMENT'
20    THEN amount_ex_tax ELSE 0
21  END
22) AS return_adjustments,
23
24(-1) * SUM(
25  CASE
26    WHEN report_row_type = 'SALE' AND line_type = 'PRODUCT'
27    THEN GREATEST(discount_before_tax, 0)
28    ELSE 0
29  END
30) AS discounts

RETURN rows carry negative total_amount, so they reduce net sales automatically. ADJUSTMENT lines are return-side amounts Shopify can't attribute to a specific product line (partial refunds, goodwill credits) - including them is what makes returns tie out. Returns land on the refund date, not the order date; that's the whole point of the event grain and what makes your daily chart match the admin.

On discounts, two details: GREATEST(x, 0) guards against negative discount values, which appear on some edit and return events and would otherwise inflate the total; (-1) * enforces the sign convention.

Gross sales, shipping, duties, fees, taxes, totals

SQL
1-- In the final SELECT, derived from the aggregates. Since discounts and
2-- returns are both stored negative, this ADDS them back.
3(net_sales - discounts - returns) AS gross_sales,
4
5-- No report_row_type filter on these four: shipping and fees can occur on both
6-- SALE and RETURN events, and both belong in the totals. A refunded shipping
7-- charge arrives as a SHIPPING line on a RETURN action and correctly reduces
8-- the shipping total.
9SUM(CASE WHEN line_type = 'SHIPPING' THEN amount_ex_tax ELSE 0 END) AS shipping_charges,
10SUM(CASE WHEN line_type = 'DUTY'     THEN amount_ex_tax ELSE 0 END) AS duties,
11SUM(CASE WHEN line_type = 'FEE'      THEN amount_ex_tax ELSE 0 END) AS return_fees,
12CAST(0 AS NUMERIC)                                                  AS additional_fees,
13SUM(total_tax)                                                      AS taxes,
14
15(net_sales + shipping_charges + duties + return_fees + additional_fees + taxes)
16  AS total_shopify_sales,
17(net_sales + shipping_charges + return_fees + additional_fees)
18  AS total_sales

additional_fees is hardcoded to zero for most stores. Keep the column so the schema stays stable and the sales equation stays complete; populate it if your market has regulatory fees.

Reversal columns (the returns detail block)

Shopify's reports include a returns breakdown. These columns let you build it without a second model:

SQL
1SUM(CASE WHEN report_row_type = 'RETURN' AND line_type IN ('PRODUCT','ADJUSTMENT')
2         THEN amount_ex_tax ELSE 0 END) AS sales_reversals,
3
4(-1) * SUM(CASE WHEN report_row_type = 'RETURN' AND line_type = 'PRODUCT'
5                THEN discount_before_tax ELSE 0 END) AS discount_reversals,
6
7SUM(CASE WHEN report_row_type = 'RETURN' THEN total_tax ELSE 0 END) AS tax_reversals,
8
9SUM(CASE WHEN report_row_type = 'RETURN' AND line_type = 'SHIPPING'
10         THEN amount_ex_tax ELSE 0 END) AS shipping_reversals,
11
12SUM(CASE WHEN report_row_type = 'RETURN' AND line_type IN ('PRODUCT','ADJUSTMENT')
13         THEN quantity ELSE 0 END) AS reversed_quantity

And in the final SELECT:

SQL
1sales_reversals                                   AS net_sales_reversals,
2sales_reversals - discount_reversals              AS gross_sales_reversals,
3sales_reversals + tax_reversals
4  + shipping_reversals + return_fees              AS total_sales_reversals

Gift cards — keep them separate

Gift card sales are not product sales. Selling a gift card is deferred revenue; redeeming it is a discount on a future order. Shopify keeps them out of the sales equation, and so should you:

SQL
1SUM(CASE WHEN line_type = 'GIFT_CARD'
2         THEN (amount_ex_tax - (-1) * GREATEST(discount_before_tax, 0))
3         ELSE 0 END) AS gift_card_gross_sales,
4SUM(CASE WHEN line_type = 'GIFT_CARD' THEN amount_ex_tax ELSE 0 END) AS gift_card_net_sales,
5(-1) * SUM(CASE WHEN line_type = 'GIFT_CARD'
6                THEN GREATEST(discount_before_tax, 0) ELSE 0 END) AS gift_card_discounts,
7SUM(CASE WHEN line_type = 'GIFT_CARD' THEN total_tax ELSE 0 END) AS gift_card_taxes,
8
9-- Convenience column: tax on gift cards behaves differently by jurisdiction
10taxes - gift_card_taxes AS taxes_excluding_gift_cards

The complete component reference

Output columnSource rowsSign
ordersMAX over SALE + PRODUCT + ORDER, excluding editspositive
is_order_placedwindow flag, first SALE day per order0/1
quantitySALE (PRODUCT, GIFT_CARD) + RETURN (PRODUCT, ADJUSTMENT)net
quantity_ordered_shopify_compatSALE + PRODUCT + ORDER, no edits or returnspositive
quantity_ordered_shopify_export_parityas above, keeping checkout editspositive
gross_salesnet_sales − discounts − returnspositive
discountsSALE + PRODUCT, GREATEST(discount, 0)negative
returnsRETURN + (PRODUCT, ADJUSTMENT)negative
return_adjustmentsRETURN + ADJUSTMENT onlynegative
net_salesSALE + PRODUCT, plus RETURN + (PRODUCT, ADJUSTMENT)net
shipping_chargesany SHIPPING linenet
dutiesany DUTY linenet
return_feesany FEE linepositive
additional_feesconstant 0 unless your market needs itpositive
taxesSUM(total_tax) across all linesnet
total_shopify_salesnet + shipping + duties + fees + taxesnet
total_salesnet + shipping + fees (no tax, no duties)net
sales_reversals … reversed_quantityRETURN rows by line typenegative
gift_card_*GIFT_CARD lines, excluded from product salespositive

Part 7 — The implementation gotchas that cost you a week

These separate a model that's "roughly right" from one finance signs off on. For the conceptual version of why Shopify and your warehouse disagree in the first place - attribution windows, event aggregation, metric definitions - see Shopify analytics discrepancies. What follows is the SQL-level fix for each.

1. Timezone: localise before you truncate

happened_at is UTC. Shopify's reports use the store's timezone. If you DATE(happened_at) directly, every order placed after your local midnight offset lands on the wrong day - a systematic daily drift that averages out over a month but makes daily comparison impossible and breaks day-of-week analysis.

SQL
1-- WRONG
2DATE(happened_at)
3
4-- RIGHT
5DATE(DATETIME(happened_at, '{{ var("report_timezone") }}'))

Set the timezone once as a dbt var and reference it everywhere. Never inline it.

2. Order edits: three different behaviours

ScenarioShopify UIWhat to do
Post-purchase upsell, seconds after checkoutPart of the original orderInclude via is_checkout_edit
Support agent adds an item 3 days laterNew value on the edit dateInclude as a SALE on the edit date, exclude from "orders placed"
Edit that removes an itemNegative value on the edit dateInclude; it correctly reduces that day's sales

That's the reason for the parity columns:

SQL
1-- Shopify-parity net sales: exclude POST-ORDER edits, keep checkout edits
2SUM(
3  CASE
4    WHEN report_row_type = 'SALE' AND line_type = 'PRODUCT'
5     AND (COALESCE(reason, '') != 'ORDER_EDIT' OR is_checkout_edit)
6    THEN amount_ex_tax
7    WHEN report_row_type = 'RETURN' AND line_type IN ('PRODUCT','ADJUSTMENT')
8    THEN amount_ex_tax
9    ELSE 0
10  END
11) AS net_sales_shopify_parity

Ship both net_sales (everything, correct for finance) and net_sales_shopify_parity (matches the UI, correct for reconciliation).

3. Refund vs return vs cancellation

Shopify's data lumps these together; your business doesn't. order_line_refund.restock_type distinguishes them:

SQL
1refund_reversal_type_ranked AS (
2  SELECT
3    event_day, shopify_store, order_id,
4    CASE
5      WHEN restock_type = 'cancel' THEN 'CANCELLATION'  -- never shipped
6      WHEN restock_type = 'return' THEN 'RETURN'        -- came back, restocked
7      ELSE 'REFUND'                                     -- money back, no restock
8    END AS reversal_type
9  FROM refund_reversal_type
10  -- Dominant restock type wins when a refund mixes them
11  QUALIFY ROW_NUMBER() OVER (
12    PARTITION BY event_day, shopify_store, order_id
13    ORDER BY qty DESC, restock_type
14  ) = 1
15)

Return rate calculated on RETURN alone is a product-quality signal. Calculated on all three it's noise - cancellations say something about fulfillment speed, not product fit.

4. Blank product titles

Some orders carry lines with no title - deleted products, custom line items from draft orders, API artifacts. Shopify's product reports drop them. Filter at the order level so partial orders don't half-appear:

SQL
1order_product_title_flag AS (
2  SELECT
3    CAST(shopify_store AS STRING) AS shopify_store,
4    CAST(order_id AS INT64)       AS order_id,
5    MAX(
6      CASE
7        WHEN COALESCE(CAST(gift_card AS BOOL), FALSE) THEN 1
8        WHEN TRIM(COALESCE(CAST(title AS STRING), CAST(name AS STRING), '')) != ''
9        THEN 1 ELSE 0
10      END
11    ) AS has_product_title
12  FROM {{ ref('stg_shopify__order_line') }}
13  GROUP BY 1, 2
14)
15-- ... then in the final query:
16WHERE COALESCE(opt.has_product_title, 0) = 1

5. Fulfillment status is NULL for digital goods

An order of only digital products never gets a fulfillment status, which reads as "unfulfilled" in every dashboard and panics ops. Distinguish it:

SQL
1CASE
2  WHEN fulfillment_status IS NULL
3   AND COALESCE(order_any_line_requires_shipping, TRUE) = FALSE
4  THEN 'fulfillment_not_required'
5  ELSE fulfillment_status
6END AS fulfillment_status

fed by a LOGICAL_OR(COALESCE(requires_shipping, TRUE)) rollup of order_line per order.

6. Cancelled orders

cancelled_at doesn't remove sales value - a cancelled order that was refunded produces RETURN events that net it out. Expose the flag and let consumers filter:

SQL
1DATETIME(cancelled_at, '{{ var("report_timezone") }}') AS cancelled_at,
2cancelled_at IS NOT NULL                               AS is_cancelled

Don't filter cancelled orders out of the model. If you do, your sales-over-time chart stops matching Shopify and you can't explain the gap.

Part 8 — Multi-currency: shop money vs presentment money

Shopify records every amount twice: shop money (the store's base currency) and presentment money (the currency the customer actually saw and paid in).

The double-conversion trap

Say your store's base currency is GBP and a customer checks out in EUR. Shopify records shop money in GBP and presentment money in EUR. If you want to report in EUR and you convert GBP → EUR at your own daily rate, you get a number that doesn't match what the customer paid, because Shopify used its own rate at checkout.

The fix: when the presentment currency already equals your target currency, use the presentment amount directly. Only convert when it doesn't.

An alternative that scales better across many currencies is to derive Shopify's own implied FX rate per order and reuse it:

SQL
1order_presentment AS (
2  SELECT
3    shopify_store,
4    order_id,
5    ANY_VALUE(presentment_currency) AS presentment_currency,
6    SAFE_DIVIDE(
7      SUM(presentment_total_amount),
8      NULLIF(SUM(total_amount), 0)
9    ) AS shopify_fx_rate       -- presentment per 1 unit of shop money
10  FROM agreement_sales
11  GROUP BY 1, 2
12)

Then every conversion becomes:

SQL
1COALESCE(
2  CASE WHEN presentment_currency = 'EUR' THEN net_sales * shopify_fx_rate END,
3  SAFE_DIVIDE(net_sales * shop_to_base, eur_rate)
4) AS net_sales_eur

This uses Shopify's actual checkout rate when the target currency matches what the customer paid in, and falls back to your daily reference rate otherwise. Cross-currency totals then tie to the payment processor far more closely.

Nearest-day FX with no gaps

FX feeds skip weekends and holidays. A naive equi-join on date silently NULLs those rows and quietly deletes a large slice of your converted revenue.

SQL
1fx_shop_to_base_ranked AS (
2  SELECT
3    dc.rate_day,
4    dc.shop_currency,
5    CASE
6      WHEN p.source_currency = dc.shop_currency AND p.target_currency = 'BASE'
7        THEN p.exchange_rate
8      WHEN p.source_currency = 'BASE' AND p.target_currency = dc.shop_currency
9        THEN SAFE_DIVIDE(1, p.exchange_rate)     -- invert if only the reverse pair exists
10    END AS shop_to_base,
11    ROW_NUMBER() OVER (
12      PARTITION BY dc.rate_day, dc.shop_currency
13      ORDER BY ABS(DATE_DIFF(p.rate_day, dc.rate_day, DAY)), p.rate_day DESC
14    ) AS rn
15  FROM fx_day_currency dc
16  JOIN fx_pairs_by_day p
17    ON dc.shop_currency != 'BASE'
18   AND (
19     (p.source_currency = dc.shop_currency AND p.target_currency = 'BASE')
20     OR (p.source_currency = 'BASE' AND p.target_currency = dc.shop_currency)
21   )
22),
23
24fx_shop_to_base AS (
25  SELECT rate_day, shop_currency, CAST(1 AS NUMERIC) AS shop_to_base
26  FROM fx_day_currency WHERE shop_currency = 'BASE'
27  UNION ALL
28  SELECT rate_day, shop_currency, shop_to_base
29  FROM fx_shop_to_base_ranked WHERE rn = 1
30)

Three things this handles that a naive join doesn't: nearest-day fallback (weekends and holidays), rate inversion (your feed may only carry one direction), and identity (base currency to itself is 1, not a lookup).

One BigQuery-specific note: resolving several currency pairs in separate ranked CTEs can blow the query planner's complexity limit on large tables. Rank all target currencies in a single pass instead.

Part 9 — Testing and validating against Shopify

A sales model nobody has reconciled is a rumour. Here's the validation ladder.

Level 1 — internal consistency (dbt tests)

YAML
1version: 2
2
3models:
4  - name: core_shopify__sales_over_time
5    tests:
6      - dbt_utils.unique_combination_of_columns:
7          combination_of_columns:
8            - date
9            - shopify_store
10            - order_id
11            - location_id
12            - source_name
13            - report_row_type
14    columns:
15      - name: date
16        tests: [not_null]
17      - name: shopify_store
18        tests: [not_null]
19      - name: net_sales
20        tests: [not_null]
21      - name: discounts
22        tests:
23          - dbt_utils.expression_is_true:
24              expression: "<= 0"        # sign convention enforced
25      - name: returns
26        tests:
27          - dbt_utils.expression_is_true:
28              expression: "<= 0"

Level 2 — the sales equation holds

SQL
1-- Should return zero rows
2SELECT date, shopify_store,
3       SUM(net_sales) AS net_sales,
4       SUM(gross_sales + discounts + returns) AS recomputed
5FROM {{ ref('core_shopify__sales_over_time') }}
6GROUP BY 1, 2
7HAVING ABS(SUM(net_sales) - SUM(gross_sales + discounts + returns)) > 0.01

Level 3 — reconcile against Shopify itself

The one that actually matters. Export a month of the Sales report from the Shopify admin, load it into a seed table, and diff:

SQL
1WITH mine AS (
2  SELECT date,
3         SUM(gross_sales_shopify_parity) AS gross_sales,
4         SUM(discounts_shopify_parity)   AS discounts,
5         SUM(returns)                    AS returns,
6         SUM(net_sales)                  AS net_sales,
7         SUM(total_shopify_sales)        AS total_sales
8  FROM {{ ref('core_shopify__sales_over_time') }}
9  WHERE shopify_store = 'store_1'
10    AND date BETWEEN '2026-01-01' AND '2026-01-31'
11  GROUP BY 1
12),
13theirs AS (SELECT * FROM {{ ref('shopify_export_january') }})
14SELECT
15  m.date,
16  m.net_sales                         AS mine,
17  t.net_sales                         AS shopify,
18  ROUND(m.net_sales - t.net_sales, 2) AS diff,
19  ROUND(SAFE_DIVIDE(m.net_sales - t.net_sales, NULLIF(t.net_sales, 0)) * 100, 2) AS diff_pct
20FROM mine m
21JOIN theirs t USING (date)
22ORDER BY ABS(m.net_sales - t.net_sales) DESC

Reading the diffs

SymptomAlmost certainly
Uniform small daily offset, cancels over a monthTimezone not localised before DATE()
Your numbers consistently higherTest orders not excluded, or pending/voided included
Returns land on the wrong daysAttributing returns to order date instead of refund date
Gross sales too high, net correctTax not subtracted - check amount_ex_tax
Units off but revenue rightOrder-edit handling - compare the parity quantity columns
Off by an exact FX factor on a subsetCurrency resolution - orders denominated in an old base currency
One day wildly offA single large order edit or refund - find it and trace it end to end

Expect to land within 0.5% on the first serious pass and within 0.05% after handling edits and timezone. Exact-to-the-cent parity across all history is not a realistic goal; Shopify restates some historical figures and rounds in places you can't observe. Agree an acceptable tolerance with finance up front and monitor it.

Part 10 — Performance and materialization

These models scan every order event you've ever had. Configure accordingly.

SQL
1{{ config(
2    materialized='table',
3    partition_by={'field': 'date', 'data_type': 'date', 'granularity': 'month'},
4    cluster_by=['shopify_store']
5) }}
  • Monthly partitions, not daily. These are moderate-sized tables queried in month and quarter ranges. Daily partitioning creates thousands of small partitions and metadata overhead that costs more than it saves.
  • Full-refresh tables beat incremental - until they don't. Returns, edits and refunds mutate history, so incremental models need a lookback window and merge logic. Below roughly 50M order-event rows a nightly full rebuild is simpler, cheaper in engineering time, and immune to drift. Above that, go incremental on date with a 90-day lookback.
  • Materialize hot staging models as tables. order_agreement_sale gets referenced many times; as a view it re-scans raw every single time.
  • Watch the planner on wide CTE chains. BigQuery has a query-complexity ceiling. If you hit it, the usual culprit is many near-identical ranked CTEs - collapse them into one ranked pass, as in the FX section.
  • Keep the analytics layer thin. A SELECT * view over a materialized core table costs nothing and gives you a stable contract to refactor behind.

The order to build in

  1. Staging models for the eight core tables. One evening.
  2. sales_over_time with just net sales. Reconcile that one number against the admin before adding anything else.
  3. Add components one at a time - discounts, returns, shipping, taxes - reconciling after each. Adding all of them then debugging is how a two-day job becomes a two-week job.
  4. Handle the gotchas: timezone first, then test orders, then order edits.
  5. Add currencies once single-currency numbers are signed off.
  6. Build the product-level model at order × line × day grain, and immediately add a test that diffs it against the order-level model daily.
  7. Layer COGS and profit on top.

The payoff is that "what were sales last Tuesday" and "what's the fully-loaded margin on SKU X in Q3" become the same query against the same grain - and both tie to the number in the Shopify admin.

Running dbt on top of your Shopify sync

The models are only useful if they run after fresh data lands. Weld's Shopify connector syncs the raw streams, and the dbt Cloud integration triggers your dbt jobs directly after each sync completes, so the sales report rebuilds on the same schedule your data arrives on. If you'd rather not run dbt at all, Weld transforms with GitHub Sync give you push-to-deploy SQL models with lineage and no dbt project to maintain - that's the path the standalone SQL guide takes.

FAQ: Shopify dbt models