Product Sales Over Time

1-- shopify_product_sales_over_time
2--
3-- The sales report at line grain: day x store x order x line. This is the model
4-- merchandising lives in, and the one that makes SKU-level margin possible.
5--
6-- It reconciles to sales_over_time. That is the whole point, and it is why the
7-- non-product rows are here: shipping, fees, gift cards and unattributable refund
8-- adjustments are emitted as rows with a NULL sku. Drop them and this model sits
9-- below the sales report by exactly the value of shipping and fees, and someone
10-- spends a week finding out why.
11--
12-- The hard part is attribution. An agreement records that money moved on an order,
13-- not always which line it belongs to, so product money is distributed across the
14-- order's lines by weight - units follow quantity share, money follows revenue
15-- share. Using one weight for both distorts mixed-price baskets.
16--
17-- Returns arrive from two places: agreement RETURN events and order_line_refund
18-- rows. Same money, two sources. Naively unioning both doubles returns, so the
19-- refund rows attribute per line and only the unexplained residual is spread.
20--
21-- Depends on: staging.shopify.{order, order_line, order_agreement,
22--             order_agreement_sale, order_refund, order_line_refund, location,
23--             shop} and core.shopify.sku_cost_per_day
24
25WITH shop AS (
26    SELECT
27        shopify_store,
28        ANY_VALUE(currency)                       AS shop_currency,
29        COALESCE(ANY_VALUE(iana_timezone), 'UTC') AS report_timezone
30    FROM {{staging.shopify.shop}}
31    GROUP BY shopify_store
32),
33
34locations AS (
35    SELECT shopify_store, location_id, location_name, location_country_code
36    FROM {{staging.shopify.location}}
37),
38
39orders AS (
40    SELECT