1-- shopify_product_sales_over_time
2--
3-- The sales report at line grain: day x store x order x line. This is the model
4-- merchandising lives in, and the one that makes SKU-level margin possible.
5--
6-- It reconciles to sales_over_time. That is the whole point, and it is why the
7-- non-product rows are here: shipping, fees, gift cards and unattributable refund
8-- adjustments are emitted as rows with a NULL sku. Drop them and this model sits
9-- below the sales report by exactly the value of shipping and fees, and someone
10-- spends a week finding out why.
11--
12-- The hard part is attribution. An agreement records that money moved on an order
13 , -- not always which line it belongs to, so product money is distributed across the
14-- order's lines by weight - units follow quantity share, money follows revenue
15-- share. Using one weight for both distorts mixed-price baskets.
16--
17-- Returns arrive from two places: agreement RETURN events and order_line_refund
18-- rows. Same money, two sources. Naively unioning both doubles returns, so the
19-- refund rows attribute per line and only the unexplained residual is spread.
20--
21-- Depends on: staging.shopify.{order, order_line, order_agreement
22 , -- order_agreement_sale, order_refund, order_line_refund, location
23 , -- shop} and core.shopify.sku_cost_per_day
24with
25 shop as (
26 select
27 shopify_store
28 , any_value(currency) as shop_currency
29 , coalesce(any_value(iana_timezone), 'UTC') as report_timezone
30 from
31 {{staging.shopify.shop}}
32 group by
33 shopify_store
34 )
35 , locations as (
36 select
37 shopify_store
38 , location_id
39 , location_name
40 , location_country_code
41 from
42 {{staging.shopify.location}}
43 )
44 , orders as (
45 select
46 o.shopify_store
47 , o.order_id
48 , o.order_name
49 , o.location_id
50 , o.source_name
51 , o.financial_status
52 , o.cancelled_at
53 , date(datetime(o.processed_at, s.report_timezone)) as order_created_day
54 , -- No presentment-currency fallback and no default code: presentment is
55 -- the buyer's currency, and a guessed code mislabels every amount.
56 coalesce(
57 o.order_shop_currency
58 , o.currency
59 , s.shop_currency
60 ) as shop_currency
61 , -- Shipping country is the right geography but is NULL for digital goods
62 -- and POS, so cascade to billing, then the location's country.
63 coalesce(
64 o.shipping_country_code
65 , o.billing_country_code
66 , l.location_country_code
67 ) as country
68 , -- The order's own location, straight from Shopify, NULL when there is
69 -- none. Identical expression in sales_over_time so the two models
70 -- reconcile on this dimension - they previously disagreed, which meant
71 -- a per-location comparison between them could never tie.
72 l.location_name as location_name
73 from
74 {{staging.shopify.order}} o
75 join shop s using (shopify_store)
76 left join locations l using (shopify_store, location_id)
77 -- Voided only - see sales_over_time. Keeping 'pending' matters for parity:
78 -- if the two models filter orders differently they cannot reconcile.
79 where
80 o.financial_status != 'voided'
81 )
82 , -- Untitled lines are dropped by Shopify's product reports: deleted products
83 , -- draft-order custom lines, API artifacts.
84 order_lines as (
85 select
86 shopify_store
87 , order_id
88 , line_id
89 , product_id
90 , variant_id
91 , product_title
92 , variant_title
93 , sku
94 , vendor
95 , cast(quantity as numeric) as ordered_quantity
96 , unit_price
97 , discounts as line_discount
98 , gross_sales as line_gross_sales
99 , gross_sales - discounts as line_net_sales
100 from
101 {{staging.shopify.order_line}}
102 where
103 not is_gift_card
104 and product_title is not null
105 )
106 , -- Two weights, deliberately: money follows revenue share, units follow quantity
107 -- share. The ELSE branch splits evenly for fully discounted orders, where every
108 -- line is zero and a share would be undefined.
109 line_weights as (
110 select
111 ol.*
112 , case
113 when sum(greatest(ol.ordered_quantity, 0)) over (
114 partition by
115 ol.shopify_store
116 , ol.order_id
117 ) > 0 then safe_divide(
118 greatest(ol.ordered_quantity, 0)
119 , sum(greatest(ol.ordered_quantity, 0)) over (
120 partition by
121 ol.shopify_store
122 , ol.order_id
123 )
124 )
125 else safe_divide(
126 1
127 , count(*) over (
128 partition by
129 ol.shopify_store
130 , ol.order_id
131 )
132 )
133 end as quantity_weight
134 , case
135 when sum(greatest(ol.line_net_sales, 0)) over (
136 partition by
137 ol.shopify_store
138 , ol.order_id
139 ) > 0 then safe_divide(
140 greatest(ol.line_net_sales, 0)
141 , sum(greatest(ol.line_net_sales, 0)) over (
142 partition by
143 ol.shopify_store
144 , ol.order_id
145 )
146 )
147 else safe_divide(
148 1
149 , count(*) over (
150 partition by
151 ol.shopify_store
152 , ol.order_id
153 )
154 )
155 end as revenue_weight
156 from
157 order_lines ol
158 )
159 , agreements as (
160 select
161 shopify_store
162 , order_agreement_id
163 , order_id
164 , happened_at
165 , reason
166 from
167 {{staging.shopify.order_agreement}}
168 )
169 , sales as (
170 select
171 shopify_store
172 , order_agreement_id
173 , order_id
174 , line_type
175 , action_type
176 , cast(quantity as numeric) as quantity
177 , total_tax
178 , discount_before_tax
179 , amount_ex_tax
180 from
181 {{staging.shopify.order_agreement_sale}}
182 )
183 , events as (
184 select
185 date(datetime(a.happened_at, s.report_timezone)) as date
186 , a.shopify_store
187 , a.order_id
188 , a.reason
189 , sa.line_type
190 , sa.action_type
191 , sa.quantity
192 , sa.total_tax
193 , sa.discount_before_tax
194 , sa.amount_ex_tax
195 from
196 agreements a
197 join shop s using (shopify_store)
198 join sales sa using (shopify_store, order_agreement_id, order_id)
199 )
200 , -- ---------------------------------------------------------------- product sales
201 sale_order_day as (
202 select
203 date
204 , shopify_store
205 , order_id
206 , sum(quantity) as sale_quantity
207 , sum(
208 case
209 when action_type = 'ORDER'
210 and reason not in ('RETURN', 'ORDER_EDIT') then quantity
211 else 0
212 end
213 ) as quantity_ordered_shopify_compat
214 , sum(
215 case
216 when action_type = 'ORDER'
217 and reason != 'RETURN' then quantity
218 else 0
219 end
220 ) as quantity_ordered_shopify_export_parity
221 , sum(amount_ex_tax) as net_sales
222 , (-1) * sum(greatest(discount_before_tax, 0)) as discounts
223 , sum(total_tax) as taxes
224 from
225 events
226 where
227 line_type = 'PRODUCT'
228 and action_type in ('ORDER', 'UPDATE')
229 group by
230 1
231 , 2
232 , 3
233 )
234 , sale_line_events as (
235 select
236 d.date
237 , o.shopify_store
238 , o.order_id
239 , o.order_name
240 , o.financial_status
241 , o.cancelled_at
242 , o.location_id
243 , o.source_name
244 , o.shop_currency
245 , o.country
246 , o.location_name
247 , 'SALE' as report_row_type
248 , lw.line_id
249 , lw.product_id
250 , lw.variant_id
251 , lw.product_title
252 , lw.variant_title
253 , lw.sku
254 , lw.vendor
255 , lw.unit_price
256 , d.sale_quantity * lw.quantity_weight as quantity
257 , d.quantity_ordered_shopify_compat * lw.quantity_weight as quantity_ordered_shopify_compat
258 , d.quantity_ordered_shopify_export_parity * lw.quantity_weight as quantity_ordered_shopify_export_parity
259 , -- Fractional on purpose: each line carries its share of one order, so the
260 -- lines still sum to exactly 1.0 per order-day and the two reports tie.
261 case
262 when d.date = o.order_created_day then lw.revenue_weight
263 else 0
264 end as orders
265 , (d.net_sales - d.discounts) * lw.revenue_weight as gross_sales
266 , d.discounts * lw.revenue_weight as discounts
267 , cast(0 as numeric) as returns
268 , d.net_sales * lw.revenue_weight as net_sales
269 , cast(0 as numeric) as shipping_charges
270 , cast(0 as numeric) as duties
271 , cast(0 as numeric) as additional_fees
272 , cast(0 as numeric) as return_fees
273 , d.taxes * lw.revenue_weight as taxes
274 , cast(0 as numeric) as sales_reversals
275 , cast(0 as numeric) as discount_reversals
276 , cast(0 as numeric) as tax_reversals
277 , cast(0 as numeric) as shipping_reversals
278 , cast(0 as numeric) as reversed_quantity
279 , cast(0 as numeric) as gift_card_gross_sales
280 , cast(0 as numeric) as gift_card_net_sales
281 , cast(0 as numeric) as gift_card_discounts
282 , cast(0 as numeric) as gift_card_taxes
283 from
284 line_weights lw
285 join sale_order_day d using (shopify_store, order_id)
286 join orders o using (shopify_store, order_id)
287 )
288 , -- ---------------------------------------------------------------------- returns
289 refunds as (
290 select
291 r.shopify_store
292 , r.refund_id
293 , r.order_id
294 , date(datetime(r.refund_created_at, s.report_timezone)) as date
295 from
296 {{staging.shopify.order_refund}} r
297 join shop s using (shopify_store)
298 )
299 , -- What the agreements say the return was worth, per order-day.
300 return_order_day as (
301 select
302 date
303 , shopify_store
304 , order_id
305 , sum(quantity) as return_quantity
306 , sum(amount_ex_tax) as return_net_sales
307 , (-1) * sum(discount_before_tax) as return_discount_reversals
308 , sum(total_tax) as return_taxes
309 from
310 events
311 where
312 action_type = 'RETURN'
313 and line_type in ('PRODUCT', 'ADJUSTMENT')
314 group by
315 1
316 , 2
317 , 3
318 )
319 , -- What the per-line refund rows already account for.
320 refund_line_totals as (
321 select
322 r.date
323 , r.shopify_store
324 , r.order_id
325 , olr.order_line_id as line_id
326 , sum(cast(olr.quantity as numeric)) as refund_quantity
327 from
328 {{staging.shopify.order_line_refund}} olr
329 join refunds r using (shopify_store, refund_id)
330 group by
331 1
332 , 2
333 , 3
334 , 4
335 )
336 , refund_order_day as (
337 select
338 date
339 , shopify_store
340 , order_id
341 , sum(refund_quantity) as refund_quantity
342 from
343 refund_line_totals
344 group by
345 1
346 , 2
347 , 3
348 )
349 , -- Returns land per line where the refund rows say so.
350 return_line_events as (
351 select
352 rlt.date
353 , o.shopify_store
354 , o.order_id
355 , o.order_name
356 , o.financial_status
357 , o.cancelled_at
358 , o.location_id
359 , o.source_name
360 , o.shop_currency
361 , o.country
362 , o.location_name
363 , 'RETURN' as report_row_type
364 , lw.line_id
365 , lw.product_id
366 , lw.variant_id
367 , lw.product_title
368 , lw.variant_title
369 , lw.sku
370 , lw.vendor
371 , lw.unit_price
372 , (-1) * rlt.refund_quantity as quantity
373 , cast(0 as numeric) as quantity_ordered_shopify_compat
374 , cast(0 as numeric) as quantity_ordered_shopify_export_parity
375 , cast(0 as numeric) as orders
376 , cast(0 as numeric) as gross_sales
377 , cast(0 as numeric) as discounts
378 , (-1) * rlt.refund_quantity * lw.unit_price as returns
379 , (-1) * rlt.refund_quantity * lw.unit_price as net_sales
380 , cast(0 as numeric) as shipping_charges
381 , cast(0 as numeric) as duties
382 , cast(0 as numeric) as additional_fees
383 , cast(0 as numeric) as return_fees
384 , cast(0 as numeric) as taxes
385 , (-1) * rlt.refund_quantity * lw.unit_price as sales_reversals
386 , cast(0 as numeric) as discount_reversals
387 , cast(0 as numeric) as tax_reversals
388 , cast(0 as numeric) as shipping_reversals
389 , (-1) * rlt.refund_quantity as reversed_quantity
390 , cast(0 as numeric) as gift_card_gross_sales
391 , cast(0 as numeric) as gift_card_net_sales
392 , cast(0 as numeric) as gift_card_discounts
393 , cast(0 as numeric) as gift_card_taxes
394 from
395 refund_line_totals rlt
396 join line_weights lw using (shopify_store, order_id, line_id)
397 join orders o using (shopify_store, order_id)
398 )
399 , -- Whatever the agreements say was returned but the refund rows did not explain -
400 -- partial refunds, goodwill credits - spread across the order's lines. Without
401 -- this the two reports disagree; with it counted twice, returns double.
402 return_residual as (
403 select
404 rod.date
405 , rod.shopify_store
406 , rod.order_id
407 , rod.return_quantity + coalesce(rfd.refund_quantity, 0) as residual_quantity
408 , rod.return_net_sales
409 , rod.return_discount_reversals
410 , rod.return_taxes
411 from
412 return_order_day rod
413 left join refund_order_day rfd using (date, shopify_store, order_id)
414 )
415 , return_adjustment_events as (
416 select
417 rr.date
418 , o.shopify_store
419 , o.order_id
420 , o.order_name
421 , o.financial_status
422 , o.cancelled_at
423 , o.location_id
424 , o.source_name
425 , o.shop_currency
426 , o.country
427 , o.location_name
428 , 'RETURN' as report_row_type
429 , cast(null as int64) as line_id
430 , cast(null as int64) as product_id
431 , cast(null as int64) as variant_id
432 , cast(null as string) as product_title
433 , cast(null as string) as variant_title
434 , cast(null as string) as sku
435 , cast(null as string) as vendor
436 , cast(null as numeric) as unit_price
437 , rr.residual_quantity as quantity
438 , cast(0 as numeric) as quantity_ordered_shopify_compat
439 , cast(0 as numeric) as quantity_ordered_shopify_export_parity
440 , cast(0 as numeric) as orders
441 , cast(0 as numeric) as gross_sales
442 , cast(0 as numeric) as discounts
443 , rr.return_net_sales as returns
444 , rr.return_net_sales as net_sales
445 , cast(0 as numeric) as shipping_charges
446 , cast(0 as numeric) as duties
447 , cast(0 as numeric) as additional_fees
448 , cast(0 as numeric) as return_fees
449 , rr.return_taxes as taxes
450 , rr.return_net_sales as sales_reversals
451 , rr.return_discount_reversals as discount_reversals
452 , rr.return_taxes as tax_reversals
453 , cast(0 as numeric) as shipping_reversals
454 , rr.residual_quantity as reversed_quantity
455 , cast(0 as numeric) as gift_card_gross_sales
456 , cast(0 as numeric) as gift_card_net_sales
457 , cast(0 as numeric) as gift_card_discounts
458 , cast(0 as numeric) as gift_card_taxes
459 from
460 return_residual rr
461 join orders o using (shopify_store, order_id)
462 -- Floating-point residue would otherwise produce thousands of near-zero rows.
463 where
464 abs(rr.return_net_sales) > 0.0001
465 or abs(rr.return_discount_reversals) > 0.0001
466 or abs(rr.residual_quantity) > 0.0001
467 )
468 , -- ------------------------------------------------- non-product money, NULL sku
469 -- These belong to the order, not to any line. Shopify's own product exports show
470 -- them with an empty product column, and they are what makes this model tie out
471 -- against sales_over_time.
472 non_product_day as (
473 select
474 date
475 , shopify_store
476 , order_id
477 , sum(
478 case
479 when line_type = 'SHIPPING' then amount_ex_tax
480 else 0
481 end
482 ) as shipping_charges
483 , sum(
484 case
485 when line_type = 'SHIPPING'
486 and action_type = 'RETURN' then amount_ex_tax
487 else 0
488 end
489 ) as shipping_reversals
490 , sum(
491 case
492 when line_type = 'DUTY' then amount_ex_tax
493 else 0
494 end
495 ) as duties
496 , sum(
497 case
498 when line_type = 'FEE' then amount_ex_tax
499 else 0
500 end
501 ) as return_fees
502 , sum(
503 case
504 when line_type in ('SHIPPING', 'DUTY', 'FEE') then total_tax
505 else 0
506 end
507 ) as taxes
508 , sum(
509 case
510 when line_type = 'GIFT_CARD' then amount_ex_tax + greatest(discount_before_tax, 0)
511 else 0
512 end
513 ) as gift_card_gross_sales
514 , sum(
515 case
516 when line_type = 'GIFT_CARD' then amount_ex_tax
517 else 0
518 end
519 ) as gift_card_net_sales
520 , (-1) * sum(
521 case
522 when line_type = 'GIFT_CARD' then greatest(discount_before_tax, 0)
523 else 0
524 end
525 ) as gift_card_discounts
526 , sum(
527 case
528 when line_type = 'GIFT_CARD' then total_tax
529 else 0
530 end
531 ) as gift_card_taxes
532 from
533 events
534 where
535 line_type in ('SHIPPING', 'DUTY', 'FEE', 'GIFT_CARD')
536 group by
537 1
538 , 2
539 , 3
540 )
541 , non_product_line_events as (
542 select
543 d.date
544 , o.shopify_store
545 , o.order_id
546 , o.order_name
547 , o.financial_status
548 , o.cancelled_at
549 , o.location_id
550 , o.source_name
551 , o.shop_currency
552 , o.country
553 , o.location_name
554 , 'SALE' as report_row_type
555 , cast(null as int64) as line_id
556 , cast(null as int64) as product_id
557 , cast(null as int64) as variant_id
558 , cast(null as string) as product_title
559 , cast(null as string) as variant_title
560 , cast(null as string) as sku
561 , cast(null as string) as vendor
562 , cast(null as numeric) as unit_price
563 , cast(0 as numeric) as quantity
564 , cast(0 as numeric) as quantity_ordered_shopify_compat
565 , cast(0 as numeric) as quantity_ordered_shopify_export_parity
566 , cast(0 as numeric) as orders
567 , cast(0 as numeric) as gross_sales
568 , cast(0 as numeric) as discounts
569 , cast(0 as numeric) as returns
570 , cast(0 as numeric) as net_sales
571 , d.shipping_charges as shipping_charges
572 , d.duties as duties
573 , cast(0 as numeric) as additional_fees
574 , d.return_fees as return_fees
575 , d.taxes as taxes
576 , cast(0 as numeric) as sales_reversals
577 , cast(0 as numeric) as discount_reversals
578 , cast(0 as numeric) as tax_reversals
579 , d.shipping_reversals as shipping_reversals
580 , cast(0 as numeric) as reversed_quantity
581 , d.gift_card_gross_sales
582 , d.gift_card_net_sales
583 , d.gift_card_discounts
584 , d.gift_card_taxes
585 from
586 non_product_day d
587 join orders o using (shopify_store, order_id)
588 )
589 , -- UNION ALL matches positionally, not by name: every branch must emit the same
590 -- columns in the same order. Verbose, but adding an event type is copy-paste
591 -- plus one union line.
592 line_events as (
593 select
594 *
595 from
596 sale_line_events
597 union all
598 select
599 *
600 from
601 return_line_events
602 union all
603 select
604 *
605 from
606 return_adjustment_events
607 union all
608 select
609 *
610 from
611 non_product_line_events
612 )
613 , final_base as (
614 select
615 date
616 , shopify_store
617 , order_id
618 , order_name
619 , financial_status
620 , cancelled_at
621 , location_id
622 , source_name
623 , shop_currency
624 , country
625 , location_name
626 , report_row_type
627 , line_id
628 , product_id
629 , variant_id
630 , product_title
631 , variant_title
632 , sku
633 , vendor
634 , max(unit_price) as unit_price
635 , sum(quantity) as quantity
636 , sum(quantity_ordered_shopify_compat) as quantity_ordered_shopify_compat
637 , sum(quantity_ordered_shopify_export_parity) as quantity_ordered_shopify_export_parity
638 , sum(orders) as orders
639 , sum(gross_sales) as gross_sales
640 , sum(discounts) as discounts
641 , sum(returns) as returns
642 , sum(net_sales) as net_sales
643 , sum(shipping_charges) as shipping_charges
644 , sum(duties) as duties
645 , sum(additional_fees) as additional_fees
646 , sum(return_fees) as return_fees
647 , sum(taxes) as taxes
648 , sum(sales_reversals) as sales_reversals
649 , sum(discount_reversals) as discount_reversals
650 , sum(tax_reversals) as tax_reversals
651 , sum(shipping_reversals) as shipping_reversals
652 , sum(reversed_quantity) as reversed_quantity
653 , sum(gift_card_gross_sales) as gift_card_gross_sales
654 , sum(gift_card_net_sales) as gift_card_net_sales
655 , sum(gift_card_discounts) as gift_card_discounts
656 , sum(gift_card_taxes) as gift_card_taxes
657 from
658 line_events
659 group by
660 1
661 , 2
662 , 3
663 , 4
664 , 5
665 , 6
666 , 7
667 , 8
668 , 9
669 , 10
670 , 11
671 , 12
672 , 13
673 , 14
674 , 15
675 , 16
676 , 17
677 , 18
678 , 19
679 )
680select
681 fb.date
682 , fb.shopify_store
683 , fb.order_id
684 , fb.order_name
685 , fb.report_row_type
686 , fb.financial_status
687 , fb.cancelled_at is not null as is_cancelled
688 , fb.location_name
689 , fb.country
690 , fb.source_name
691 , lower(fb.source_name) as sales_channel
692 , fb.shop_currency
693 , fb.line_id
694 , fb.product_id
695 , fb.variant_id
696 , fb.product_title
697 , fb.variant_title
698 , fb.sku
699 , fb.vendor
700 , fb.unit_price
701 , fb.orders
702 , fb.quantity
703 , fb.quantity_ordered_shopify_compat
704 , fb.quantity_ordered_shopify_export_parity
705 , round(fb.gross_sales, 2) as gross_sales
706 , round(fb.discounts, 2) as discounts
707 , round(fb.returns, 2) as returns
708 , round(fb.net_sales, 2) as net_sales
709 , round(fb.shipping_charges, 2) as shipping_charges
710 , round(fb.duties, 2) as duties
711 , round(fb.additional_fees, 2) as additional_fees
712 , round(fb.return_fees, 2) as return_fees
713 , round(fb.taxes, 2) as taxes
714 , round(
715 fb.net_sales + fb.shipping_charges + fb.duties + fb.return_fees + fb.additional_fees + fb.taxes
716 , 2
717 ) as total_shopify_sales
718 , round(
719 fb.net_sales + fb.shipping_charges + fb.return_fees + fb.additional_fees
720 , 2
721 ) as total_sales
722 , round(fb.sales_reversals, 2) as net_sales_reversals
723 , round(fb.sales_reversals - fb.discount_reversals, 2) as gross_sales_reversals
724 , round(
725 fb.sales_reversals + fb.tax_reversals + fb.shipping_reversals + fb.return_fees
726 , 2
727 ) as total_sales_reversals
728 , round(fb.discount_reversals, 2) as discount_reversals
729 , round(fb.tax_reversals, 2) as tax_reversals
730 , round(fb.shipping_reversals, 2) as shipping_reversals
731 , fb.reversed_quantity
732 , round(fb.gift_card_gross_sales, 2) as gift_card_gross_sales
733 , round(fb.gift_card_net_sales, 2) as gift_card_net_sales
734 , round(fb.gift_card_discounts, 2) as gift_card_discounts
735 , round(fb.gift_card_taxes, 2) as gift_card_taxes
736 , round(fb.taxes - fb.gift_card_taxes, 2) as taxes_excluding_gift_cards
737 , -- Point-in-time cost, so a January order is valued at January's cost. NULL
738 -- rather than 0 where cost is unknown: a zero cost reads as 100% margin and
739 -- ends up in a board deck, a NULL shows up as the coverage gap it is.
740 scd.standard_cost
741 , round(fb.quantity * scd.standard_cost, 2) as cogs
742 , round(
743 fb.net_sales - (fb.quantity * scd.standard_cost)
744 , 2
745 ) as gross_profit
746from
747 final_base fb
748 left join {{core.shopify.sku_cost_per_day}} scd using (date, shopify_store, sku)
749order by
750 fb.date
751 , fb.shopify_store
752 , fb.order_id
753 , fb.line_idRequired models (12)
This model reads from staging models rather than raw tables. Deploy these first — Weld resolves the order for you.
staging.shopify.orderOrders with test orders removed, types cast, currency precedence resolved.
-- staging.shopify.order
-- Thin wrapper over raw `order`. Casts, renames, drops test orders. No other logic.
--
-- Single store. To add another, UNION ALL a second block below pointing at that
-- store's connector with a different shopify_store label. Keep it in staging so
-- the core models never have to know how many stores there are.
select
'store_1' as shopify_store
, cast(id as int64) as order_id
, cast(name as string) as order_name
, cast(customer_id as int64) as customer_id
, cast(location_id as int64) as location_id
, lower(cast(source_name as string)) as source_name
, cast(processed_at as timestamp) as processed_at
, cast(created_at as timestamp) as created_at
, cast(cancelled_at as timestamp) as cancelled_at
, cast(closed_at as timestamp) as closed_at
, -- COALESCE to '' so downstream NOT IN (...) filters keep NULL-status orders
-- rather than silently dropping them.
lower(coalesce(cast(financial_status as string), '')) as financial_status
, lower(cast(fulfillment_status as string)) as fulfillment_status
, -- The order's own currency, preferred over the shop's CURRENT currency.
-- Stores that changed base currency, or imported history from another
-- platform, hold orders denominated in something else; defaulting to today's
-- shop currency would mislabel them by the full FX factor.
upper(
nullif(
trim(
cast(
current_total_price_set_shop_money_currency_code as string
)
)
, ''
)
) as order_shop_currency
, upper(nullif(trim(cast(currency as string)), '')) as currency
, upper(
nullif(
trim(
cast(
current_total_price_set_presentment_money_currency_code as string
)
)
, ''
)
) as order_presentment_currency
, upper(
nullif(trim(cast(presentment_currency as string)), '')
) as presentment_currency
, upper(
nullif(
trim(cast(shipping_address_country_code as string))
, ''
)
) as shipping_country_code
, upper(
nullif(
trim(cast(billing_address_country_code as string))
, ''
)
) as billing_country_code
from
{{raw.shopify.order}}
where
coalesce(test, false) = falsestaging.shopify.order_lineProduct identity and line money, plus gift-card and requires-shipping flags.
-- staging.shopify.order_line
-- Product identity and line money. Gift cards are NOT filtered here - staging
-- stays neutral and consumers decide. `is_gift_card` and `requires_shipping` are
-- exposed because the sales report needs them to classify orders.
--
-- Single store. To add another, UNION ALL a second block below pointing at that
-- store's connector with a different shopify_store label. Keep it in staging so
-- the core models never have to know how many stores there are.
select
'store_1' as shopify_store
, cast(id as int64) as line_id
, cast(order_id as int64) as order_id
, cast(product_id as int64) as product_id
, cast(variant_id as int64) as variant_id
, cast(sku as string) as sku
, nullif(trim(cast(title as string)), '') as product_title
, cast(variant_title as string) as variant_title
, cast(vendor as string) as vendor
, cast(quantity as int64) as quantity
, coalesce(cast(gift_card as bool), false) as is_gift_card
, coalesce(cast(requires_shipping as bool), true) as requires_shipping
, coalesce(
cast(price_set_shop_money_amount as numeric)
, cast(price as numeric)
) as unit_price
, coalesce(
cast(price_set_shop_money_amount as numeric)
, cast(price as numeric)
) * cast(quantity as int64) as gross_sales
, coalesce(
cast(total_discount_set_shop_money_amount as numeric)
, cast(total_discount as numeric)
, 0
) as discounts
from
{{raw.shopify.order_line}}staging.shopify.order_agreementThe financial event log; voided agreements removed.
-- staging.shopify.order_agreement
-- The financial event log, one row per change to an order's value.
-- 'voided' agreements were cancelled before they ever represented money.
--
-- Single store. To add another, UNION ALL a second block below pointing at that
-- store's connector with a different shopify_store label. Keep it in staging so
-- the core models never have to know how many stores there are.
select
'store_1' as shopify_store
, cast(id as string) as order_agreement_id
, cast(order_id as int64) as order_id
, cast(happened_at as timestamp) as happened_at
, lower(cast(app_handle as string)) as app_handle
, upper(coalesce(cast(reason as string), '')) as reason
from
{{raw.shopify.order_agreement}}
where
lower(coalesce(cast(reason as string), '')) != 'voided'staging.shopify.order_agreement_saleLine-level money per event, with amount_ex_tax derived.
-- staging.shopify.order_agreement_sale
-- Line-level money per event, in both shop money (the store's base currency) and
-- presentment money (what the customer actually paid in).
--
-- amount_ex_tax is derived here because total_amount is tax-inclusive while every
-- sales component except taxes is not - computing it once avoids repeating the
-- subtraction in every consumer.
--
-- Single store. To add another, UNION ALL a second block below pointing at that
-- store's connector with a different shopify_store label. Keep it in staging so
-- the core models never have to know how many stores there are.
select
'store_1' as shopify_store
, cast(order_agreement_id as string) as order_agreement_id
, cast(order_id as int64) as order_id
, upper(cast(line_type as string)) as line_type
, upper(cast(action_type as string)) as action_type
, coalesce(cast(quantity as int64), 0) as quantity
, coalesce(
cast(total_amount_shop_money_amount as numeric)
, 0
) as total_amount
, coalesce(
cast(total_tax_amount_shop_money_amount as numeric)
, 0
) as total_tax
, coalesce(
cast(
total_discount_amount_before_taxes_shop_money_amount as numeric
)
, 0
) as discount_before_tax
, coalesce(
cast(total_amount_shop_money_amount as numeric)
, 0
) - coalesce(
cast(total_tax_amount_shop_money_amount as numeric)
, 0
) as amount_ex_tax
, coalesce(
cast(total_amount_presentment_money_amount as numeric)
, 0
) as presentment_total_amount
, coalesce(
cast(
total_tax_amount_presentment_money_amount as numeric
)
, 0
) as presentment_total_tax
, coalesce(
cast(
total_discount_amount_before_taxes_presentment_money_amount as numeric
)
, 0
) as presentment_discount_before_tax
, coalesce(
cast(total_amount_presentment_money_amount as numeric)
, 0
) - coalesce(
cast(
total_tax_amount_presentment_money_amount as numeric
)
, 0
) as presentment_amount_ex_tax
, upper(
cast(
total_amount_presentment_money_currency_code as string
)
) as presentment_currency
from
{{raw.shopify.order_agreement_sale}}staging.shopify.order_refundRefund headers - created_at is the date returns are attributed to.
-- staging.shopify.order_refund
-- Refund headers. created_at is the date the refund was processed, which is the
-- date returns are attributed to.
--
-- Single store. To add another, UNION ALL a second block below pointing at that
-- store's connector with a different shopify_store label. Keep it in staging so
-- the core models never have to know how many stores there are.
select
'store_1' as shopify_store
, cast(id as int64) as refund_id
, cast(order_id as int64) as order_id
, cast(created_at as timestamp) as refund_created_at
from
{{raw.shopify.order_refund}}staging.shopify.order_line_refundPer-line refund detail, used to attribute returns to the right SKU.
-- staging.shopify.order_line_refund
-- Per-line refund detail. restock_type is what distinguishes a cancellation from
-- a genuine return from a refund-without-restock.
--
-- Single store. To add another, UNION ALL a second block below pointing at that
-- store's connector with a different shopify_store label. Keep it in staging so
-- the core models never have to know how many stores there are.
-- order_id is deliberately not selected: it is reached through order_refund
, -- which is how Shopify's own schema links these.
select
'store_1' as shopify_store
, cast(refund_id as int64) as refund_id
, cast(order_line_id as int64) as order_line_id
, cast(location_id as int64) as location_id
, coalesce(cast(quantity as int64), 0) as quantity
, lower(cast(restock_type as string)) as restock_type
from
{{raw.shopify.order_line_refund}}staging.shopify.locationLocation names, used to resolve location_name on the reports.
-- staging.shopify.location
-- Physical and virtual locations, used to resolve location_name on the reports.
--
-- Single store. To add another, UNION ALL a second block below pointing at that
-- store's connector with a different shopify_store label. Keep it in staging so
-- the core models never have to know how many stores there are.
select
'store_1' as shopify_store
, cast(id as int64) as location_id
, nullif(trim(cast(name as string)), '') as location_name
, upper(nullif(trim(cast(country_code as string)), '')) as location_country_code
from
{{raw.shopify.location}}staging.shopify.shopBase currency and the store IANA timezone used to localise dates.
-- staging.shopify.shop
-- One row per store. Supplies the base currency and, usefully, the store's own
-- IANA timezone - so the reports localise correctly without hardcoding one.
--
-- Single store. To add another, UNION ALL a second block below pointing at that
-- store's connector with a different shopify_store label. Keep it in staging so
-- the core models never have to know how many stores there are.
select
'store_1' as shopify_store
, cast(id as int64) as shop_id
, cast(name as string) as shop_name
, upper(nullif(trim(cast(currency as string)), '')) as currency
, nullif(trim(cast(iana_timezone as string)), '') as iana_timezone
from
{{raw.shopify.shop}}staging.shopify.inventory_itemCurrent cost per SKU, straight from Shopify.
-- staging.shopify.inventory_item
-- Current cost per inventory item. Shopify stores cost here, not on the variant.
--
-- Single store. To add another, UNION ALL a second block below pointing at that
-- store's connector with a different shopify_store label. Keep it in staging so
-- the core models never have to know how many stores there are.
select
'store_1' as shopify_store
, cast(id as int64) as inventory_item_id
, cast(sku as string) as sku
, cast(cost as numeric) as cost
, cast(created_at as timestamp) as created_at
, cast(updated_at as timestamp) as updated_at
from
{{raw.shopify.inventory_item}}
where
sku is not null
and trim(cast(sku as string)) != ''staging.shopify.inventory_item__historyEvery cost change over time - Weld's history table for inventory_item.
-- staging.shopify.inventory_item__history
-- Cost changes over time, from Weld's history table for inventory_item. A history
-- table carries the same schema as the source, with one row per observed version -
-- which is what makes point-in-time COGS possible rather than restating every
-- historical order at today's cost.
--
-- Enable it under Data Source -> the Shopify stream -> History tables. Without it
-- this model is empty and costs fall back to the current value.
--
-- Single store. To add another, UNION ALL a second block below pointing at that
-- store's connector with a different shopify_store label. Keep it in staging so
-- the core models never have to know how many stores there are.
select
'store_1' as shopify_store
, cast(id as int64) as inventory_item_id
, cast(sku as string) as sku
, cast(cost as numeric) as cost
, cast(updated_at as timestamp) as updated_at
from
{{raw.shopify.inventory_item__history}}
where
sku is not null
and trim(cast(sku as string)) != ''core.shopify.sku_cost_per_dayPoint-in-time standard cost per SKU, for every day that SKU actually sold.
-- shopify_sku_cost_per_day
-- Point-in-time standard cost per SKU, for every day that SKU actually sold.
--
-- Costs change. Valuing a January order at today's cost overstates or understates
-- margin for the whole of history, so this picks the cost that was in effect on
-- the day of the sale: the most recent cost change at or before that day, falling
-- back to the earliest known cost for orders that predate any recorded change.
--
-- Only (day, SKU) pairs that sold are produced, so this stays small rather than
-- materialising a full SKU x calendar grid.
--
-- Depends on: staging.shopify.order, .order_line, .inventory_item
, -- .inventory_item__history, .shop
with
shop as (
select
shopify_store
, coalesce(any_value(iana_timezone), 'UTC') as report_timezone
from
{{staging.shopify.shop}}
group by
shopify_store
)
, -- Every cost that has ever applied: the history table plus the current value.
cost_grid as (
select
shopify_store
, sku
, cost
, updated_at as effective_ts
from
{{staging.shopify.inventory_item__history}}
where
cost is not null
union all
select
shopify_store
, sku
, cost
, coalesce(updated_at, created_at) as effective_ts
from
{{staging.shopify.inventory_item}}
where
cost is not null
)
, -- Only the days each SKU actually sold.
day_skus as (
select distinct
date(datetime(o.processed_at, s.report_timezone)) as date
, ol.shopify_store
, ol.sku
from
{{staging.shopify.order_line}} ol
join {{staging.shopify.order}} o using (shopify_store, order_id)
join shop s using (shopify_store)
where
not ol.is_gift_card
and ol.sku is not null
and trim(ol.sku) != ''
)
select
ds.date
, ds.shopify_store
, ds.sku
, cg.cost as standard_cost
, cg.effective_ts as cost_effective_at
from
day_skus ds
left join cost_grid cg using (shopify_store, sku)
-- Prefer the newest cost effective on or before the sale day. The ELSE branch
-- keeps the earliest known cost for orders predating any recorded change, so old
-- orders get a cost rather than NULL.
qualify
row_number() over (
partition by
ds.date
, ds.shopify_store
, ds.sku
order by
case
when cg.effective_ts < timestamp(date_add(ds.date, interval 1 day)) then 0
else 1
end
, cg.effective_ts desc
) = 1analytics.shopify.product_sales_over_timeThin BI-facing contract over the core model - bind dashboards here, not to core.
-- analytics.shopify.product_sales_over_time
-- BI-facing contract over the core model. See analytics/sales_over_time.sql for
-- why this layer exists even when it is a passthrough.
-- Cancelled orders are excluded here rather than in core: core keeps them so
-- their reversal events still land, and a report that shows a cancelled order's
-- sale without context is misleading. Voided orders are already gone in core.
-- Pending payments are deliberately kept - Shopify counts them as sales.
select
*
from
{{core.shopify.product_sales_over_time}}
where
cancelled_at is nullExample output
+ ------------+----------+-----------------+----------+---------------+----------+-----------+------------------+---------------+--------+--------------+
| date | order_id | report_row_type | sku | product_title | quantity | net_sales | shipping_charges | standard_cost | cogs | gross_profit | + ------------+----------+-----------------+----------+---------------+----------+-----------+------------------+---------------+--------+--------------+
| 2026 -03 -01 | 5001 | SALE | SKU -0001 | Product 1 | 2 | 166.00 | 0.00 | 42.00 | 84.00 | 82.00 | | 2026 -03 -01 | 5001 | SALE | SKU -0002 | Product 2 | 1 | 58.10 | 0.00 | 19.50 | 19.50 | 38.60 | | 2026 -03 -01 | 5001 | SALE | | | 0 | 0.00 | 9.95 | | | | | 2026 -03 -05 | 5001 |
return | SKU -0001 | Product 1 | -1 | -83.00 | 0.00 | 42.00 | -42.00 | -41.00 | + ------------+----------+-----------------+----------+---------------+----------+-----------+------------------+---------------+--------+--------------+Recreates Shopify's "Total sales by product", but on the event grain rather than by summing order_line, so returns land on the refund date and the numbers actually tie to your sales-over-time report. Three things make that reconciliation work. Shipping, duties, fees and gift cards belong to the order rather than to any line, so they are carried as rows with a NULL sku - drop them and the product report sits below the sales report by exactly their value. Order-level money is distributed across lines by two separate weights, with units following quantity share and money following revenue share, because using one weight for both distorts mixed-price baskets. And returns arrive from both agreement RETURN events and per-line refund rows - the same money, twice - so the refund rows attribute per line and only the unexplained residual is spread across the order. COGS comes from Shopify itself: inventory_item for the current cost and its history table for every change, so a January order is valued at January's cost rather than today's. Cost is left NULL where unknown rather than zero, because a zero cost reads as 100% margin.