Inventory health

1-- amazon_vendor_inventory_health - the operational scorecard Amazon grades you on,
2-- with sales velocity attached so the numbers mean something.
3--
4-- Grain: day x vendor x marketplace x distributor view x selling program x ASIN.
5-- Currency: the marketplace's.
6-- Depends on: staging.amazon_vendor.inventory, .sales
7--
8-- WHAT MAKES THIS DIFFERENT FROM A SELLER INVENTORY MODEL. You do not control this
9-- inventory - Amazon bought it and holds it. What you control is whether you confirm
10-- and ship the purchase orders Amazon sends, and how fast. So the metrics that matter
11-- are about the SUPPLY RELATIONSHIP, not about stock levels:
12--
13--   vendor_confirmation_rate  yours to fix. Amazon's read on whether you are
14--                             dependable supply, and it feeds how much it orders next.
15--   average_vendor_lead_time  yours to fix. Long lead times make Amazon carry more
16--                             safety stock, which it accounts for by ordering less.
17--   sell_through_rate         shared. Slow turns lead to smaller POs.
18--   unhealthy_inventory       Amazon's problem that becomes yours. Excess stock is
19--                             what precedes a markdown request or a return-to-vendor.
20--
21-- The ratios below put those against actual sales, which is the part Vendor Central
22-- will not do for you: an ASIN with 400 units of unhealthy inventory is either two
23-- weeks of cover or two years of it, and the report alone cannot tell you which.
24
25-- VELOCITY IS PER DATE, NOT A SINGLE TRAILING WINDOW. This model's grain includes
26-- `date`, because the vendor inventory report is a daily time series. A velocity
27-- computed once against CURRENT_DATE() would be joined onto every historical row, so
28-- last February's cover would be measured against this week's demand - every ratio
29-- below silently wrong on every row except the most recent.
30--
31-- So the window is trailing-30-days AS AT EACH DATE, evaluated over a spine of every
32-- date either report has for that ASIN. Sales dates alone would not do: an ASIN with
33-- stock but no shipment on a given day has no sales row, and would drop out of the
34-- join and lose its velocity.
35WITH daily_sales AS (
36    SELECT
37        amazon_vendor, marketplace, distributor_view, selling_program, asin, date,
38        SUM(shipped_units) AS shipped_units
39    FROM {{staging.amazon_vendor.sales}}
40    GROUP BY 1, 2, 3, 4, 5, 6