ASIN profitability

1-- amazon_seller_asin_profitability - contribution margin per SKU per day, after
2-- Amazon's fees, returns and your COGS.
3--
4-- Grain: day x seller x marketplace x ASIN x SKU. Currency: the marketplace's.
5-- Depends on: core.amazon_seller.product_sales_over_time,
6--             core.amazon_seller.settlement_ledger,
7--             staging.amazon_seller.fba_reimbursements, .sku_cost
8--
9-- THE MODEL EVERY AMAZON SELLER WANTS AND ALMOST NONE HAS, because it needs three
10-- reports that do not join to each other and disagree about what day it is:
11--
12--   sales      attributed to the ORDER date
13--   fees       attributed to the SETTLEMENT date, days or weeks later
14--   returns    attributed to the RETURN date, weeks later still
15--
16-- There is no correct way to reconcile those on a single day, and pretending
17-- otherwise is the usual bug: joining fees to sales by date makes a SKU look
18-- gloriously profitable on order day and catastrophic on settlement day.
19--
20-- WHAT THIS MODEL DOES INSTEAD: it keeps each component on its own date and adds
21-- them up over a WINDOW. Each column is honest about which event it counts, and
22-- contribution is only meaningful once aggregated over a period long enough to
23-- contain both the sale and its settlement - a month, in practice, and never a day.
24-- Read a single row and you are reading a cash-flow event, not a margin.
25--
26-- Weekly and monthly rollups are in core; put anything shorter in front of a
27-- warning label.
28
29WITH sales AS (
30    SELECT
31        date, amazon_seller, marketplace, asin, sku,
32        product_name,
33        SUM(CASE WHEN report_row_type = 'SALE' THEN quantity ELSE 0 END)   AS units_sold,
34        SUM(CASE WHEN report_row_type = 'SALE' THEN net_sales ELSE 0 END)  AS gross_revenue,
35        SUM(CASE WHEN report_row_type = 'RETURN' THEN returns ELSE 0 END)  AS returned_revenue,
36        SUM(returned_quantity)                                            AS returned_units,
37        SUM(resellable_returned_quantity)                                 AS resellable_returned_units,
38        SUM(return_label_cost)                                            AS return_label_cost
39    FROM {{core.amazon_seller.product_sales_over_time}}
40    GROUP BY 1, 2, 3, 4, 5, 6