Shopify dbt Models: A Production Sales Report That Matches the Admin

  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

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

1-- analytics__shopify_sales_over_time.sql
2select
3    *
4from
5    {{ ref('core_shopify__sales_over_time') }}

1-- stg_shopify__order_agreement_sale.sql
2{{ config(materialized='table') }}
3select
4    'store_1' as shopify_store
5  , /* ...columns... */
6from
7    {{ source('shopify_store_1', 'order_agreement_sale') }}
8union all
9select
10    'store_2' as shopify_store
11  , /* ...same columns... */
12from
13    {{ source('shopify_store_2', 'order_agreement_sale') }}

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 %}
51

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) }}
11

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)

1with
2    shop as (
3        select
4            cast(shopify_shop as string) as shopify_shop
5          , any_value(upper(nullif(trim(cast(currency as string)), ''))) as shop_currency
6        from
7            {{ ref('stg_shopify__shop') }}
8        group by
9            1
10    )
11  , orders as (
12        select
13            cast(o.shopify_store as string) as shopify_store
14          , cast(o.id as int64) as order_id
15          , cast(o.name as string) as order_name
16          , cast(o.location_id as int64) as location_id
17          , lower(cast(o.source_name as string)) as source_name
18          , -- Currency resolution: the ORDER's own recorded currency wins over the
19            -- shop's CURRENT currency. Stores that changed base currency, or imported
20            -- historical orders from another platform, have orders genuinely
21            -- denominated in a different currency than the shop is today. Falling back
22            -- to the shop's current currency silently mislabels those amounts and can
23            -- inflate converted figures by the entire FX factor.
24            upper(
25                nullif(
26                    trim(
27                        coalesce(
28                            cast(
29                                o.current_total_price_set_shop_money_currency_code as string
30                            )
31                          , cast(o.currency as string)
32                          , sh.shop_currency
33                          , cast(
34                                o.current_total_price_set_presentment_money_currency_code as string
35                            )
36                          , cast(o.presentment_currency as string)
37                          , '{{ var("default_currency", "USD") }}'
38                        )
39                    )
40                  , ''
41                )
42            ) as shop_currency
43          , o.created_at
44          , lower(cast(o.financial_status as string)) as financial_status
45          , lower(cast(o.fulfillment_status as string)) as fulfillment_status
46          , upper(
47                nullif(
48                    trim(cast(o.shipping_address_country_code as string))
49                  , ''
50                )
51            ) as shipping_country_code
52          , upper(
53                nullif(
54                    trim(cast(o.billing_address_country_code as string))
55                  , ''
56                )
57            ) as billing_country_code
58          , cast(o.cancelled_at as timestamp) as cancelled_at
59        from
60            {{ ref('stg_shopify__order') }} o
61            left join shop sh on sh.shopify_shop = cast(o.shopify_store as string)
62        where
63            lower(cast(o.financial_status as string)) not in ('pending', 'voided', 'expired')
64            and o.test = false
65    )

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
10        {{ ref('stg_shopify__order_agreement') }}
11    where
12        lower(reason) != 'voided'
13)
14  , sales as (
15    select
16        cast(shopify_store as string) as shopify_store
17      , cast(order_id as int64) as order_id
18      , cast(order_agreement_id as string) as order_agreement_id
19      , upper(cast(line_type as string)) as line_type
20      , upper(cast(action_type as string)) as action_type
21      , cast(quantity as int64) as quantity
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(
26            total_discount_amount_before_taxes_shop_money_amount as numeric
27        ) as discount_before_tax
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(
31            total_tax_amount_presentment_money_amount as numeric
32        ) as presentment_total_tax
33      , cast(
34            total_discount_amount_before_taxes_presentment_money_amount as numeric
35        ) as presentment_discount_before_tax
36      , upper(
37            cast(
38                total_amount_presentment_money_currency_code as string
39            )
40        ) as presentment_currency
41    from
42        {{ ref('stg_shopify__order_agreement_sale') }}
43)

1-- Earliest ORDER agreement per order - used to detect checkout-flow edits
2order_first_time as (
3    select
4        shopify_store
5      , order_id
6      , min(happened_at) as order_created_at
7    from
8        agreements
9    where
10        lower(reason) = 'order'
11    group by
12        1
13      , 2
14)
15  , joined as (
16    select
17        -- Localise BEFORE truncating to a date. This is not optional.
18        date(datetime(a.happened_at, '{{ var("report_timezone", "UTC") }}')) as event_day
19      , a.shopify_store
20      , a.order_id
21      , a.order_agreement_id
22      , o.location_id
23      , o.source_name
24      , s.line_type
25      , s.action_type
26      , a.reason
27      , coalesce(s.quantity, 0) as quantity
28      , coalesce(s.total_amount, 0) as total_amount
29      , coalesce(s.total_tax, 0) as total_tax
30      , coalesce(s.discount_before_tax, 0) as discount_before_tax
31      , (
32            coalesce(s.total_amount, 0) - coalesce(s.total_tax, 0)
33        ) as amount_ex_tax
34      , -- Presentment equivalents, carried in parallel
35        coalesce(s.presentment_total_amount, 0) as presentment_total_amount
36      , coalesce(s.presentment_total_tax, 0) as presentment_total_tax
37      , coalesce(s.presentment_discount_before_tax, 0) as presentment_discount_before_tax
38      , s.presentment_currency
39      , -- Checkout-flow edit: an ORDER_EDIT within 90 seconds of order creation is
40        -- part of the original purchase (upsell widget, post-purchase offer), not a
41        -- later restatement of the order.
42        case
43            when a.reason = 'ORDER_EDIT'
44            and timestamp_diff(a.happened_at, oft.order_created_at, second) <= 90 then true
45            else false
46        end as is_checkout_edit
47    from
48        agreements a
49        join sales s on s.order_agreement_id = a.order_agreement_id
50        and s.order_id = a.order_id
51        and s.shopify_store = a.shopify_store
52        join orders o on o.order_id = a.order_id
53        and o.shopify_store = a.shopify_store
54        left join order_first_time oft on oft.order_id = a.order_id
55        and oft.shopify_store = a.shopify_store
56)
57  , typed as (
58    select
59        *
60      , case
61            when action_type in ('ORDER', 'UPDATE') then 'SALE'
62            when action_type = 'RETURN' then 'RETURN'
63            else 'OTHER'
64        end as report_row_type
65    from
66        joined
67    where
68        action_type in ('ORDER', 'UPDATE', 'RETURN')
69)

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') then 1
7        else 0
8    end
9) as orders

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
9            shopify_store
10          , order_id
11        order by
12            case
13                when report_row_type = 'SALE' then 0
14                else 1
15            end
16          , day_date asc
17          , location_id asc
18          , source_name asc
19          , reason asc
20    ) = 1 then 1
21    else 0
22end as is_order_placed

1-- 1. Net quantity: sales minus returns. The "real" unit movement.
2sum(
3    case
4        when report_row_type = 'SALE'
5        and line_type in ('PRODUCT', 'GIFT_CARD') then quantity
6        when report_row_type = 'RETURN'
7        and line_type in ('PRODUCT', 'ADJUSTMENT') then quantity
8        else 0
9    end
10) as quantity
11  , -- 2. Strict "units ordered": excludes returns AND all order edits.
12--    Matches the Shopify admin UI's units-ordered figure.
13sum(
14    case
15        when report_row_type = 'SALE'
16        and line_type = 'PRODUCT'
17        and action_type = 'ORDER'
18        and coalesce(reason, '') not in ('RETURN', 'ORDER_EDIT') then quantity
19        else 0
20    end
21) as quantity_ordered_shopify_compat
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 (
31            coalesce(reason, '') != 'ORDER_EDIT'
32            or is_checkout_edit
33        ) then quantity
34        else 0
35    end
36) as quantity_ordered_shopify_export_parity

1sum(
2    case
3        when report_row_type = 'SALE'
4        and line_type = 'PRODUCT' then amount_ex_tax
5        when report_row_type = 'RETURN'
6        and line_type in ('PRODUCT', 'ADJUSTMENT') then amount_ex_tax
7        else 0
8    end
9) as net_sales
10  , sum(
11    case
12        when report_row_type = 'RETURN'
13        and line_type in ('PRODUCT', 'ADJUSTMENT') then amount_ex_tax
14        else 0
15    end
16) as returns
17  , -- Split out separately: useful for diagnosing "unattributed" refund value
18sum(
19    case
20        when report_row_type = 'RETURN'
21        and line_type = 'ADJUSTMENT' then amount_ex_tax
22        else 0
23    end
24) as return_adjustments
25  , (-1) * sum(
26    case
27        when report_row_type = 'SALE'
28        and line_type = 'PRODUCT' then greatest(discount_before_tax, 0)
29        else 0
30    end
31) as discounts

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  , -- No report_row_type filter on these four: shipping and fees can occur on both
5-- SALE and RETURN events, and both belong in the totals. A refunded shipping
6-- charge arrives as a SHIPPING line on a RETURN action and correctly reduces
7-- the shipping total.
8sum(
9    case
10        when line_type = 'SHIPPING' then amount_ex_tax
11        else 0
12    end
13) as shipping_charges
14  , sum(
15    case
16        when line_type = 'DUTY' then amount_ex_tax
17        else 0
18    end
19) as duties
20  , sum(
21    case
22        when line_type = 'FEE' then amount_ex_tax
23        else 0
24    end
25) as return_fees
26  , cast(0 as numeric) as additional_fees
27  , sum(total_tax) as taxes
28  , (
29    net_sales + shipping_charges + duties + return_fees + additional_fees + taxes
30) as total_shopify_sales
31  , (
32    net_sales + shipping_charges + return_fees + additional_fees
33) as total_sales

1sum(
2    case
3        when report_row_type = 'RETURN'
4        and line_type in ('PRODUCT', 'ADJUSTMENT') then amount_ex_tax
5        else 0
6    end
7) as sales_reversals
8  , (-1) * sum(
9    case
10        when report_row_type = 'RETURN'
11        and line_type = 'PRODUCT' then discount_before_tax
12        else 0
13    end
14) as discount_reversals
15  , sum(
16    case
17        when report_row_type = 'RETURN' then total_tax
18        else 0
19    end
20) as tax_reversals
21  , sum(
22    case
23        when report_row_type = 'RETURN'
24        and line_type = 'SHIPPING' then amount_ex_tax
25        else 0
26    end
27) as shipping_reversals
28  , sum(
29    case
30        when report_row_type = 'RETURN'
31        and line_type in ('PRODUCT', 'ADJUSTMENT') then quantity
32        else 0
33    end
34) as reversed_quantity

1sales_reversals as net_sales_reversals
2  , sales_reversals - discount_reversals as gross_sales_reversals
3  , sales_reversals + tax_reversals + shipping_reversals + return_fees as total_sales_reversals

1sum(
2    case
3        when line_type = 'GIFT_CARD' then (
4            amount_ex_tax - (-1) * greatest(discount_before_tax, 0)
5        )
6        else 0
7    end
8) as gift_card_gross_sales
9  , sum(
10    case
11        when line_type = 'GIFT_CARD' then amount_ex_tax
12        else 0
13    end
14) as gift_card_net_sales
15  , (-1) * sum(
16    case
17        when line_type = 'GIFT_CARD' then greatest(discount_before_tax, 0)
18        else 0
19    end
20) as gift_card_discounts
21  , sum(
22    case
23        when line_type = 'GIFT_CARD' then total_tax
24        else 0
25    end
26) as gift_card_taxes
27  , -- Convenience column: tax on gift cards behaves differently by jurisdiction
28taxes - gift_card_taxes as taxes_excluding_gift_cards

1-- WRONG
2date(happened_at)
3-- RIGHT
4date(datetime(happened_at, '{{ var("report_timezone") }}'))

1-- Shopify-parity net sales: exclude POST-ORDER edits, keep checkout edits
2sum(
3    case
4        when report_row_type = 'SALE'
5        and line_type = 'PRODUCT'
6        and (
7            coalesce(reason, '') != 'ORDER_EDIT'
8            or is_checkout_edit
9        ) then amount_ex_tax
10        when report_row_type = 'RETURN'
11        and line_type in ('PRODUCT', 'ADJUSTMENT') then amount_ex_tax
12        else 0
13    end
14) as net_sales_shopify_parity

1refund_reversal_type_ranked as (
2    select
3        event_day
4      , shopify_store
5      , order_id
6      , case
7            when restock_type = 'cancel' then 'CANCELLATION' -- never shipped
8            when restock_type = 'return' then 'RETURN' -- came back, restocked
9            else 'REFUND' -- money back, no restock
10        end as reversal_type
11    from
12        refund_reversal_type
13        -- Dominant restock type wins when a refund mixes them
14    qualify
15        row_number() over (
16            partition by
17                event_day
18              , shopify_store
19              , order_id
20            order by
21                qty desc
22              , restock_type
23        ) = 1
24)

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(
9                    coalesce(cast(title as string), cast(name as string), '')
10                ) != '' then 1
11                else 0
12            end
13        ) as has_product_title
14    from
15        {{ ref('stg_shopify__order_line') }}
16    group by
17        1
18      , 2
19)
20-- ... then in the final query:
21where
22    coalesce(opt.has_product_title, 0) = 1

1case
2    when fulfillment_status is null
3    and coalesce(order_any_line_requires_shipping, true) = false then 'fulfillment_not_required'
4    else fulfillment_status
5end as fulfillment_status

1datetime(cancelled_at, '{{ var("report_timezone") }}') as cancelled_at
2  , cancelled_at is not null as is_cancelled

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
11        agreement_sales
12    group by
13        1
14      , 2
15)

1coalesce(
2    case
3        when presentment_currency = 'EUR' then net_sales * shopify_fx_rate
4    end
5  , safe_divide(net_sales * shop_to_base, eur_rate)
6) as net_sales_eur

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
7            and p.target_currency = 'BASE' then p.exchange_rate
8            when p.source_currency = 'BASE'
9            and p.target_currency = dc.shop_currency 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
13                dc.rate_day
14              , dc.shop_currency
15            order by
16                abs(date_diff(p.rate_day, dc.rate_day, day))
17              , p.rate_day desc
18        ) as rn
19    from
20        fx_day_currency dc
21        join fx_pairs_by_day p on dc.shop_currency != 'BASE'
22        and (
23            (
24                p.source_currency = dc.shop_currency
25                and p.target_currency = 'BASE'
26            )
27            or (
28                p.source_currency = 'BASE'
29                and p.target_currency = dc.shop_currency
30            )
31        )
32)
33  , fx_shop_to_base as (
34    select
35        rate_day
36      , shop_currency
37      , cast(1 as numeric) as shop_to_base
38    from
39        fx_day_currency
40    where
41        shop_currency = 'BASE'
42    union all
43    select
44        rate_day
45      , shop_currency
46      , shop_to_base
47    from
48        fx_shop_to_base_ranked
49    where
50        rn = 1
51)

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"
29

1-- Should return zero rows
2select
3    date
4  , shopify_store
5  , sum(net_sales) as net_sales
6  , sum(gross_sales + discounts + returns) as recomputed
7from
8    {{ ref('core_shopify__sales_over_time') }}
9group by
10    1
11  , 2
12having
13    abs(
14        sum(net_sales) - sum(gross_sales + discounts + returns)
15    ) > 0.01

1with
2    mine as (
3        select
4            date
5          , sum(gross_sales_shopify_parity) as gross_sales
6          , sum(discounts_shopify_parity) as discounts
7          , sum(returns) as returns
8          , sum(net_sales) as net_sales
9          , sum(total_shopify_sales) as total_sales
10        from
11            {{ ref('core_shopify__sales_over_time') }}
12        where
13            shopify_store = 'store_1'
14            and date between '2026-01-01' and '2026-01-31'
15        group by
16            1
17    )
18  , theirs as (
19        select
20            *
21        from
22            {{ ref('shopify_export_january') }}
23    )
24select
25    m.date
26  , m.net_sales as mine
27  , t.net_sales as shopify
28  , round(m.net_sales - t.net_sales, 2) as diff
29  , round(
30        safe_divide(m.net_sales - t.net_sales, nullif(t.net_sales, 0)) * 100
31      , 2
32    ) as diff_pct
33from
34    mine m
35    join theirs t using (date)
36order by
37    abs(m.net_sales - t.net_sales) desc

1{{ config(
2    materialized='table',
3    partition_by={'field': 'date', 'data_type': 'date', 'granularity': 'month'},
4    cluster_by=['shopify_store']
5) }}
6