Sales Over Time

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
20with
21    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
28            {{staging.shopify.shop}}
29        group by
30            shopify_store
31    )
32  , locations as (
33        select
34            shopify_store
35          , location_id
36          , location_name
37          , location_country_code
38        from
39            {{staging.shopify.location}}
40    )
41  , orders as (
42        select
43            o.shopify_store
44          , o.order_id
45          , o.order_name
46          , o.location_id
47          , o.source_name
48          , o.financial_status
49          , o.fulfillment_status
50          , o.cancelled_at
51          , -- The order's own currency wins over the shop's CURRENT currency, which
52            -- may have changed since. Deliberately NOT falling back to presentment
53            -- currency: that is what the customer was charged in, so using it here
54            -- would label shop-currency money with the buyer's currency. If none of
55            -- these is set, NULL is the honest answer - a guessed currency code
56            -- silently mislabels every amount on the row.
57            coalesce(
58                o.order_shop_currency
59              , o.currency
60              , s.shop_currency
61            ) as shop_currency
62          , -- Shipping country is the right geography, but it is NULL for digital
63            -- goods and POS. Cascade to billing, then the location's country.
64            coalesce(
65                o.shipping_country_code
66              , o.billing_country_code
67              , l.location_country_code
68            ) as country
69        from
70            {{staging.shopify.order}} o
71            join shop s using (shopify_store)
72            left join locations l using (shopify_store, location_id)
73            -- Only voided orders are dropped here: the money never existed. Everything
74            -- else stays, including 'pending' - an authorised-but-uncaptured order
75            -- (bank transfer, cash on delivery, manual payment) is a sale Shopify counts
76          , -- so excluding it here would understate every report downstream. Cancelled
77            -- and refunded orders also stay: their reversals arrive as their own RETURN
78            -- events, so removing the order would lose the sale AND the reversal.
79            -- financial_status and cancelled_at are passed through, so the analytics
80            -- layer can narrow this without core having destroyed the rows.
81        where
82            o.financial_status != 'voided'
83    )
84  , -- Orders carrying only untitled lines are dropped by Shopify's reports: deleted
85    -- products, draft-order custom lines, API artifacts. Flag at order level so a
86    -- partial order does not half-appear. Gift-card lines count as titled.
87    order_line_flags as (
88        select
89            shopify_store
90          , order_id
91          , max(
92                case
93                    when is_gift_card
94                    or product_title is not null then 1
95                    else 0
96                end
97            ) as has_product_title
98          , logical_or(requires_shipping) as any_line_requires_shipping
99        from
100            {{staging.shopify.order_line}}
101        group by
102            shopify_store
103          , order_id
104    )
105  , agreements as (
106        select
107            shopify_store
108          , order_agreement_id
109          , order_id
110          , happened_at
111          , app_handle
112          , reason
113        from
114            {{staging.shopify.order_agreement}}
115    )
116  , sales as (
117        select
118            shopify_store
119          , order_agreement_id
120          , order_id
121          , line_type
122          , action_type
123          , quantity
124          , total_amount
125          , total_tax
126          , discount_before_tax
127          , amount_ex_tax
128          , presentment_total_amount
129          , presentment_total_tax
130          , presentment_discount_before_tax
131          , presentment_amount_ex_tax
132          , presentment_currency
133        from
134            {{staging.shopify.order_agreement_sale}}
135    )
136  , -- Earliest ORDER agreement per order, used to spot checkout-flow edits.
137    order_first_time as (
138        select
139            shopify_store
140          , order_id
141          , min(happened_at) as order_created_at
142        from
143            agreements
144        where
145            reason = 'ORDER'
146        group by
147            shopify_store
148          , order_id
149    )
150  , -- Shopify's own implied FX rate per order: presentment per 1 unit of shop money.
151    -- Using this beats converting shop money at your own daily rate, because Shopify
152    -- used its rate at checkout and yours will not match what the customer paid.
153    order_presentment as (
154        select
155            shopify_store
156          , order_id
157          , any_value(presentment_currency) as presentment_currency
158          , safe_divide(
159                sum(presentment_total_amount)
160              , nullif(sum(total_amount), 0)
161            ) as shopify_fx_rate
162        from
163            sales
164        group by
165            shopify_store
166          , order_id
167    )
168  , joined as (
169        select
170            -- Localise BEFORE truncating to a date. happened_at is UTC while Shopify
171            -- reports in store-local time.
172            date(datetime(a.happened_at, s.report_timezone)) as date
173          , a.shopify_store
174          , a.order_id
175          , a.reason
176          , a.app_handle
177          , o.location_id
178          , o.source_name
179          , o.shop_currency
180          , sa.line_type
181          , sa.action_type
182          , sa.quantity
183          , sa.total_tax
184          , sa.discount_before_tax
185          , sa.amount_ex_tax
186          , sa.presentment_total_tax
187          , sa.presentment_discount_before_tax
188          , sa.presentment_amount_ex_tax
189          , -- An ORDER_EDIT within 90 seconds of creation is part of the original
190            -- purchase (a post-purchase upsell), not a later restatement.
191            case
192                when a.reason = 'ORDER_EDIT'
193                and timestamp_diff(a.happened_at, oft.order_created_at, second) <= 90 then true
194                else false
195            end as is_checkout_edit
196        from
197            agreements a
198            join shop s using (shopify_store)
199            join sales sa using (shopify_store, order_agreement_id, order_id)
200            join orders o using (shopify_store, order_id)
201            join order_line_flags olf using (shopify_store, order_id)
202            left join order_first_time oft using (shopify_store, order_id)
203        where
204            olf.has_product_title = 1
205    )
206  , typed as (
207        select
208            *
209          , case
210                when action_type in ('ORDER', 'UPDATE') then 'SALE'
211                when action_type = 'RETURN' then 'RETURN'
212            end as report_row_type
213        from
214            joined
215        where
216            action_type in ('ORDER', 'UPDATE', 'RETURN')
217    )
218  , -- Shopify lumps cancellations, returns and refunds together; your business does
219    -- not. restock_type on the refunded lines tells them apart. Return rate measured
220    -- on RETURN alone is a product-quality signal; on all three it is noise.
221    refunds as (
222        select
223            r.shopify_store
224          , r.refund_id
225          , r.order_id
226          , date(datetime(r.refund_created_at, s.report_timezone)) as date
227        from
228            {{staging.shopify.order_refund}} r
229            join shop s using (shopify_store)
230    )
231  , refund_detail as (
232        select
233            r.shopify_store
234          , r.date
235          , r.order_id
236          , olr.restock_type
237          , olr.location_id
238          , sum(olr.quantity) as qty
239        from
240            {{staging.shopify.order_line_refund}} olr
241            join refunds r using (shopify_store, refund_id)
242        group by
243            1
244          , 2
245          , 3
246          , 4
247          , 5
248    )
249  , reversal_type as (
250        select
251            shopify_store
252          , date
253          , order_id
254          , case
255                when restock_type = 'cancel' then 'CANCELLATION' -- never shipped
256                when restock_type = 'return' then 'RETURN' -- came back, restocked
257                else 'REFUND' -- money back, no restock
258            end as reversal_type
259        from
260            refund_detail
261            -- The dominant restock type wins when one refund mixes them.
262        qualify
263            row_number() over (
264                partition by
265                    shopify_store
266                  , date
267                  , order_id
268                order by
269                    qty desc
270                  , restock_type
271            ) = 1
272    )
273  , -- Carry the last known refund location forward so a multi-day return sequence
274    -- does not fragment across locations.
275    refund_location as (
276        select
277            shopify_store
278          , date
279          , order_id
280          , last_value(location_id ignore nulls) over (
281                partition by
282                    shopify_store
283                  , order_id
284                order by
285                    date rows between unbounded preceding
286                    and current ROW
287            ) as refund_location_id
288        from
289            (
290                select
291                    shopify_store
292                  , date
293                  , order_id
294                  , max(location_id) as location_id
295                from
296                    refund_detail
297                group by
298                    1
299                  , 2
300                  , 3
301            )
302    )
303  , agg as (
304        select
305            date
306          , shopify_store
307          , order_id
308          , location_id
309          , source_name
310          , shop_currency
311          , report_row_type
312          , -- MAX not SUM: one order is one order however many lines it has.
313            -- Excluding ORDER_EDIT stops an edited order counting again on the edit date.
314            max(
315                case
316                    when report_row_type = 'SALE'
317                    and line_type = 'PRODUCT'
318                    and action_type = 'ORDER'
319                    and reason not in ('RETURN', 'ORDER_EDIT') then 1
320                    else 0
321                end
322            ) as orders
323          , -- 1. Net unit movement: sales minus returns.
324            sum(
325                case
326                    when report_row_type = 'SALE'
327                    and line_type in ('PRODUCT', 'GIFT_CARD') then quantity
328                    when report_row_type = 'RETURN'
329                    and line_type in ('PRODUCT', 'ADJUSTMENT') then quantity
330                    else 0
331                end
332            ) as quantity
333          , -- 2. Strict units ordered: no returns, no edits. Matches the admin UI.
334            sum(
335                case
336                    when report_row_type = 'SALE'
337                    and line_type = 'PRODUCT'
338                    and action_type = 'ORDER'
339                    and reason not in ('RETURN', 'ORDER_EDIT') then quantity
340                    else 0
341                end
342            ) as quantity_ordered_shopify_compat
343          , -- 3. Keeps checkout-flow edits, drops post-order edits. Matches Shopify's
344            --    CSV export, which differs from the UI.
345            sum(
346                case
347                    when report_row_type = 'SALE'
348                    and line_type in ('PRODUCT', 'GIFT_CARD')
349                    and action_type = 'ORDER'
350                    and reason != 'RETURN'
351                    and (
352                        reason != 'ORDER_EDIT'
353                        or is_checkout_edit
354                    ) then quantity
355                    else 0
356                end
357            ) as quantity_ordered_shopify_export_parity
358          , -- RETURN rows carry negative amounts, so they reduce net sales on their
359            -- own. ADJUSTMENT lines are refund value Shopify cannot tie to a product
360            -- line (partial refunds, goodwill credits) - including them is what makes
361            -- returns tie out.
362            sum(
363                case
364                    when report_row_type = 'SALE'
365                    and line_type = 'PRODUCT' then amount_ex_tax
366                    when report_row_type = 'RETURN'
367                    and line_type in ('PRODUCT', 'ADJUSTMENT') then amount_ex_tax
368                    else 0
369                end
370            ) as net_sales
371          , -- Same, excluding POST-ORDER edits: reconciles to the admin UI.
372            sum(
373                case
374                    when report_row_type = 'SALE'
375                    and line_type = 'PRODUCT'
376                    and (
377                        reason != 'ORDER_EDIT'
378                        or is_checkout_edit
379                    ) then amount_ex_tax
380                    when report_row_type = 'RETURN'
381                    and line_type in ('PRODUCT', 'ADJUSTMENT') then amount_ex_tax
382                    else 0
383                end
384            ) as net_sales_shopify_parity
385          , -- GREATEST guards negative discounts, which appear on some edit and
386            -- return events and would otherwise inflate the total.
387            (-1) * sum(
388                case
389                    when report_row_type = 'SALE'
390                    and line_type = 'PRODUCT' then greatest(discount_before_tax, 0)
391                    else 0
392                end
393            ) as discounts
394          , sum(
395                case
396                    when report_row_type = 'RETURN'
397                    and line_type in ('PRODUCT', 'ADJUSTMENT') then amount_ex_tax
398                    else 0
399                end
400            ) as returns
401          , sum(
402                case
403                    when report_row_type = 'RETURN'
404                    and line_type = 'ADJUSTMENT' then amount_ex_tax
405                    else 0
406                end
407            ) as return_adjustments
408          , -- No row_type filter: shipping and fees occur on sales AND refunds, and a
409            -- refunded shipping charge should reduce the total.
410            sum(
411                case
412                    when line_type = 'SHIPPING' then amount_ex_tax
413                    else 0
414                end
415            ) as shipping_charges
416          , sum(
417                case
418                    when line_type = 'DUTY' then amount_ex_tax
419                    else 0
420                end
421            ) as duties
422          , sum(
423                case
424                    when line_type = 'FEE' then amount_ex_tax
425                    else 0
426                end
427            ) as return_fees
428          , cast(0 as numeric) as additional_fees
429          , sum(total_tax) as taxes
430          , -- Returns detail block.
431            sum(
432                case
433                    when report_row_type = 'RETURN'
434                    and line_type in ('PRODUCT', 'ADJUSTMENT') then amount_ex_tax
435                    else 0
436                end
437            ) as sales_reversals
438          , (-1) * sum(
439                case
440                    when report_row_type = 'RETURN'
441                    and line_type = 'PRODUCT' then discount_before_tax
442                    else 0
443                end
444            ) as discount_reversals
445          , sum(
446                case
447                    when report_row_type = 'RETURN' then total_tax
448                    else 0
449                end
450            ) as tax_reversals
451          , sum(
452                case
453                    when report_row_type = 'RETURN'
454                    and line_type = 'SHIPPING' then amount_ex_tax
455                    else 0
456                end
457            ) as shipping_reversals
458          , sum(
459                case
460                    when report_row_type = 'RETURN'
461                    and line_type in ('PRODUCT', 'ADJUSTMENT') then quantity
462                    else 0
463                end
464            ) as reversed_quantity
465          , -- Gift cards are deferred revenue, kept out of the sales equation.
466            sum(
467                case
468                    when line_type = 'GIFT_CARD' then amount_ex_tax + greatest(discount_before_tax, 0)
469                    else 0
470                end
471            ) as gift_card_gross_sales
472          , sum(
473                case
474                    when line_type = 'GIFT_CARD' then amount_ex_tax
475                    else 0
476                end
477            ) as gift_card_net_sales
478          , (-1) * sum(
479                case
480                    when line_type = 'GIFT_CARD' then greatest(discount_before_tax, 0)
481                    else 0
482                end
483            ) as gift_card_discounts
484          , sum(
485                case
486                    when line_type = 'GIFT_CARD' then total_tax
487                    else 0
488                end
489            ) as gift_card_taxes
490          , -- Presentment money: what the customer actually paid, before any
491            -- conversion of ours.
492            sum(
493                case
494                    when report_row_type = 'SALE'
495                    and line_type = 'PRODUCT' then presentment_amount_ex_tax
496                    when report_row_type = 'RETURN'
497                    and line_type in ('PRODUCT', 'ADJUSTMENT') then presentment_amount_ex_tax
498                    else 0
499                end
500            ) as presentment_net_sales
501          , sum(presentment_total_tax) as presentment_taxes
502        from
503            typed
504        group by
505            1
506          , 2
507          , 3
508          , 4
509          , 5
510          , 6
511          , 7
512    )
513select
514    a.date
515  , a.shopify_store
516  , a.order_id
517  , o.order_name
518  , a.report_row_type
519  , a.shop_currency
520  , o.country
521  , a.source_name
522  , -- The order's own location, straight from Shopify. NULL means Shopify
523    -- recorded no location - usually an online-store order - and is left NULL
524    -- rather than labelled, because "no physical location" is not the same claim
525    -- as "online". Identical expression in product_sales_over_time, so the two
526    -- models reconcile on this dimension.
527    own_loc.location_name as location_name
528  , -- Where a refund was processed, which is often NOT where the order was
529    -- placed: a POS sale returned through the web admin has a store
530    -- location_name and no refund location. Kept as its own column rather than
531    -- coalesced into location_name - they are two different facts, and merging
532    -- them makes it impossible to ask either question. NULL on SALE rows.
533    rf_loc.location_name as refund_location_name
534  , -- Shopify's own channel value, not an interpretation of it: 'web' for the
535    -- online store, 'pos' for retail, 'shopify_draft_order' for manual orders
536  , -- otherwise the handle of the app or marketplace that created the order.
537    -- Split retail from online with this, or with location_name IS NOT NULL.
538    lower(a.source_name) as sales_channel
539  , -- An order of only digital goods never gets a fulfillment status, which reads
540    -- as "unfulfilled" in every dashboard and panics ops. Distinguish it.
541    case
542        when o.fulfillment_status is null
543        and olf.any_line_requires_shipping = false then 'fulfillment_not_required'
544        else o.fulfillment_status
545    end as fulfillment_status
546  , o.financial_status
547  , rt.reversal_type
548  , o.cancelled_at is not null as is_cancelled
549  , a.orders
550  , -- Exactly ONE row per order, on its first SALE day. Summing over any window
551    -- gives "orders placed", the AOV denominator Shopify uses. Counts edit-only
552    -- orders with no ORDER row, ignores return-only orders, and never
553    -- double-counts across locations or days.
554    case
555        when a.report_row_type = 'SALE'
556        and row_number() over (
557            partition by
558                a.shopify_store
559              , a.order_id
560            order by
561                case
562                    when a.report_row_type = 'SALE' then 0
563                    else 1
564                end
565              , a.date asc
566              , a.location_id asc
567              , a.source_name asc
568        ) = 1 then 1
569        else 0
570    end as is_order_placed
571  , a.quantity
572  , a.quantity_ordered_shopify_compat
573  , a.quantity_ordered_shopify_export_parity
574  , -- discounts and returns are stored negative, so subtracting adds them back.
575    round(a.net_sales - a.discounts - a.returns, 2) as gross_sales
576  , round(a.discounts, 2) as discounts
577  , round(a.returns, 2) as returns
578  , round(a.return_adjustments, 2) as return_adjustments
579  , round(a.net_sales, 2) as net_sales
580  , round(a.net_sales_shopify_parity, 2) as net_sales_shopify_parity
581  , round(a.shipping_charges, 2) as shipping_charges
582  , round(a.duties, 2) as duties
583  , round(a.return_fees, 2) as return_fees
584  , round(a.additional_fees, 2) as additional_fees
585  , round(a.taxes, 2) as taxes
586  , -- Matches the Shopify admin UI.
587    round(
588        a.net_sales + a.shipping_charges + a.duties + a.return_fees + a.additional_fees + a.taxes
589      , 2
590    ) as total_shopify_sales
591  , -- Excludes tax and duties (pass-through money); what finance usually wants.
592    round(
593        a.net_sales + a.shipping_charges + a.return_fees + a.additional_fees
594      , 2
595    ) as total_sales
596  , round(a.sales_reversals, 2) as net_sales_reversals
597  , round(a.sales_reversals - a.discount_reversals, 2) as gross_sales_reversals
598  , round(
599        a.sales_reversals + a.tax_reversals + a.shipping_reversals + a.return_fees
600      , 2
601    ) as total_sales_reversals
602  , round(a.discount_reversals, 2) as discount_reversals
603  , round(a.tax_reversals, 2) as tax_reversals
604  , round(a.shipping_reversals, 2) as shipping_reversals
605  , a.reversed_quantity
606  , round(a.gift_card_gross_sales, 2) as gift_card_gross_sales
607  , round(a.gift_card_net_sales, 2) as gift_card_net_sales
608  , round(a.gift_card_discounts, 2) as gift_card_discounts
609  , round(a.gift_card_taxes, 2) as gift_card_taxes
610  , round(a.taxes - a.gift_card_taxes, 2) as taxes_excluding_gift_cards
611  , -- What the customer actually paid, and the rate Shopify used at checkout.
612    op.presentment_currency
613  , round(a.presentment_net_sales, 2) as presentment_net_sales
614  , round(a.presentment_taxes, 2) as presentment_taxes
615  , op.shopify_fx_rate
616from
617    agg a
618    join orders o using (shopify_store, order_id)
619    join order_line_flags olf using (shopify_store, order_id)
620    left join order_presentment op using (shopify_store, order_id)
621    left join reversal_type rt on rt.shopify_store = a.shopify_store
622    and rt.order_id = a.order_id
623    and rt.date = a.date
624    left join refund_location rf on rf.shopify_store = a.shopify_store
625    and rf.order_id = a.order_id
626    and rf.date = a.date
627    left join locations rf_loc on rf_loc.shopify_store = a.shopify_store
628    and rf_loc.location_id = rf.refund_location_id
629    left join locations own_loc on own_loc.shopify_store = a.shopify_store
630    and own_loc.location_id = a.location_id
631order by
632    a.date
633  , a.shopify_store
634  , a.order_id
635  , a.report_row_type