Forecast vs actuals

1-- amazon_vendor_forecast_vs_actuals - Amazon's demand forecast against what actually
2-- shipped, per ASIN per forecast window.
3--
4-- Grain: forecast window x vendor x marketplace x ASIN.
5-- Depends on: staging.amazon_vendor.forecast, .sales
6--
7-- WHY BUILD THIS AT ALL. Amazon's forecast drives Amazon's purchase orders. If the
8-- forecast for an ASIN is 40% above what has ever shipped, POs are coming that will
9-- leave Amazon overstocked - and overstock becomes your markdown request. If it is
10-- well below, Amazon will under-order and you will lose sales you could have made.
11-- Either way you find out months later, from the consequence rather than the cause.
12--
13-- This model puts the forecast next to the outcome so you can see the error while it
14-- still matters, and take a specific number into a Vendor Manager conversation.
15--
16-- FORWARD-LOOKING WINDOWS HAVE NO ACTUALS YET, and that is the useful half. Rows
17-- where forecast_status = 'FORECAST' are what Amazon expects next; 'REALISED' rows
18-- are windows that have fully elapsed and can be scored. 'IN_PROGRESS' is partially
19-- elapsed - the actuals are real but incomplete, so do NOT read the error columns on
20-- those rows. They are NULL for exactly that reason.
21--
22-- ONE LIMITATION, AND IT IS STRUCTURAL. Amazon retains only the current forecast, so
23-- a realised window is scored against whatever forecast was live when you last
24-- synced - which, for a window already in the past, may have been revised toward the
25-- outcome. Measuring true forecast accuracy needs the forecast AS IT STOOD before the
26-- window opened, which means retaining generations: enable Weld history tables on the
27-- vendor_forecasting_report stream and read the history table here instead. Until
28-- then, treat these errors as indicative, not as a forecast-accuracy KPI.
29
30WITH actuals AS (
31    SELECT
32        f.amazon_vendor,
33        f.marketplace,
34        f.asin,
35        f.forecast_start_date,
36        f.forecast_end_date,
37        SUM(s.shipped_units)  AS actual_shipped_units,
38        SUM(s.ordered_units)  AS actual_ordered_units,
39        COUNT(DISTINCT s.date) AS days_with_sales
40    FROM {{staging.amazon_vendor.forecast}} f