# Forecasting upgrade — per-channel + per-region (design, no code yet)

*Target: forecast demand split by **channel** (retail / webshop / wholesale) and **region** (HR / AT / SI), reconciled up to today's per-SKU total — without breaking anything downstream. Design only; each phase below is independently shippable.*

## Guiding principle
**Forecast at the level the data supports; allocate the rest.** We have enough signal to forecast each SKU × channel nationally. We do **not** have enough to statistically forecast every SKU × channel × country (too sparse), so country is an **allocation layer** (historical share), reconciled so the parts always sum to the whole. Per-store stays out of scope entirely (allocation problem, not forecasting — see POLLEO_AI_KNOWLEDGE.md §store sparsity).

## Where we are today
- `forecasts` stores **one `total` per SKU per week** (plus baseline / on_top_* / promo_uplift / planner_factor components). `channel_mode` is *how* it was computed (`split`/`total`/`dead`), not an output dimension.
- The engine already splits **retail vs wholesale internally**, but **webshop is folded in** and there is **no region** anywhere.
- Downstream — supply (coverage, reorder, lost sales), finance, ABC, monthly snapshots — all read `forecasts.total` and/or `forecast_for_supply.csv`. **These must keep working unchanged.**

## Grounding facts (verified live, 2026)
- Region key = **`store_id → dim_stores.country`**, clean values **HR / AT / SI**, with coverage **retail 90% · wholesale 92% · webshop 88%**. Use this for all three channels.
- `dim_partners.country` is dirty (export codes, `SI` vs `SLO`, junk like `0382`) → **export dimension for a later phase**, not the primary region key.
- ~8–12% of transactions have no resolvable country → an **`UNK` bucket** to handle explicitly.

## Target grain
`SKU × channel × region × week`, reconciled bottom-up so: region rows sum to the SKU×channel forecast, and channels sum to the **same `forecasts.total` we produce today**.

---

## 1. Data model (additive, backward-compatible)
- **New table `forecasts_detail`** (the only new storage): `run_id, product_id, channel, region, year, week, baseline, on_top, promo_uplift, total`. One row per SKU×channel×region×week.
- **`forecasts` stays the canonical SKU rollup** — `total` = SUM of detail, written exactly as today. *Nothing downstream changes.*
- **Read views**: `v_forecast_channel` (SKU×channel×week) and `v_forecast_region` (SKU×region×week) over the detail, for pages/AI.
- Why a new table, not columns on `forecasts`: avoids mixing grains (every existing `SUM(total)` query would otherwise double-count). Clean, reversible, opt-in.

## 2. Attribution layer (the foundation — build & validate first)
- **New view `v_sales_weekly_channel_region`**: `product_id, year, week, channel (lookup_channel_map), region (dim_stores.country, else 'UNK'), qty, net_revenue, ruc`. This is the **training/allocation source**.
- Region = store country for every channel (per grounding facts). `UNK` for the ~10% with no store.
- **Gate:** confirm UNK share per SKU×channel is small enough that allocation is meaningful; decide UNK handling (recommend: allocate UNK proportionally to the known HR/AT/SI mix, so no demand is lost).

## 3. Engine design
For each planned SKU (the ~489 in `sku_planning`):
1. **Per-channel national forecast** — fit the existing model pool (ARIMA/CES/Theta/Croston/ADIDA/IMAPA/TSB/SES…, Holt still banned) separately on each channel series that has enough history: **retail, webshop (now its own series), wholesale.** Channels with no/thin history fall back to run-rate (8w non-promo) per the canonical coverage rule.
2. **Region allocation** — split each channel's weekly forecast across HR/AT/SI by that SKU×channel's **trailing region share** (e.g. last 26 weeks from the attribution view). Default = share-based allocation, *not* a separate model. (Future: fit direct country models only for high-volume, stable SKU×channel×country combos.)
3. **Reconcile bottom-up** — region rows sum to channel; channels sum to SKU `total`. Guarantees consistency and that `forecasts.total` is unchanged in aggregate. (Future: MinT/optimal reconciliation if we later fit some levels directly.)
4. **Guardrails** stay per channel (retail 13w-mean×2, wholesale 26w-max×1.5, floor 0.5×8w-median) and apply before allocation; the per-region split inherits them proportionally.
5. **On-top mapping** — VP (wholesale) and MP (retail) on-top are already channel-tagged. Region for on-top v1: allocate by the channel's region share (VP can use the KAM buyer's store/region if reliable). Keep the rule that **MP replaces the retail baseline** on promo weeks.
6. **Promo** — per the agreed decision: CM forecast (on-top) is authoritative forward; the statistical uplift is a **gap-filler only** (fires only where a promo has no CM number, never stacks). This pairs naturally with breaking out webshop (the most promo/coupon-driven channel).

## 4. Downstream & backward compatibility (the safety constraint)
- `forecasts.total` and `forecast_for_supply.csv` are written **identically to today** (supply orders nationally to the WH — country split is a store-allocation concern, separate). → supply / finance / ABC untouched.
- New surfaces read the detail/views: Revenue Forecast & Executive get **per-channel and per-region breakdowns**; FA can be sliced by channel/region (thin combos will score worse — expected, label it).
- AI: add `forecasts_detail` + the two views to the system prompt so it can answer "wholesale forecast for HR next month" etc.
- **Regression check each run:** assert `SUM(forecasts_detail.total) == forecasts.total` per SKU/week within tolerance before publishing.

## 5. Phasing (each phase ships on its own)
- **Phase 0 — Attribution & validation.** Build `v_sales_weekly_channel_region`; measure UNK; decide UNK handling + wholesale-region question (below). *Gate before any modelling.*
- **Phase 1 — Schema.** `forecasts_detail` table + read views (additive migration, like `canonical_sku_universe.sql`).
- **Phase 2 — Engine.** Break out webshop; per-channel fit; region allocation; bottom-up reconcile; write detail + unchanged rollup; regression-assert the total.
- **Phase 3 — On-top + guardrails** per channel×region; promo uplift demoted to gap-filler.
- **Phase 4 — Surface it.** Channel/region breakdowns on Revenue Forecast + Executive; FA by channel/region; AI prompt update.
- **Phase 5 — Future.** Direct country models for high-signal combos; MinT reconciliation; partner-based **export** region (normalize the dirty partner countries first).

## 6. Decisions — LOCKED
1. **Wholesale region = buyer's market** (dim_partners country): HR/AT/SI kept, everything else = **EXPORT**. (Each KAM's buyers map to a country — Patrik→SI buyers, Selma→HR buyers — so on-top regionalises cleanly.)
2. **Region codes = HR / AT / SI** (+ EXPORT for wholesale), `SLO` normalised to `SI`.
3. **UNK** (no resolvable country, ~10–14%) → kept **visible in the attribution view**; **allocated proportionally** to the known regions only at forecast time (Phase 2).
4. **On-top by region:** VP (wholesale) → buyer's country; CM/MP (retail) entered as total → allocated by retail store-country share.

## Status
- **Phase 0 (attribution view) + Phase 1 (schema) — DONE** (`db/migrations/forecast_channel_region.sql`): `v_sales_weekly_channel_region` live; `forecasts_detail` + `v_forecast_channel`/`v_forecast_region` created (empty until Phase 2). Live, additive, nothing downstream touched.
- Data notes for Phase 2: wholesale **EXPORT (239k qty 2026) and UNK (161k)** are both large — handle explicitly; **retail is ~all HR** (negligible AT/SI retail), so retail region allocation is mostly trivial; webshop ~all HR too.
- **Phase 2a (allocation) — DONE & AUTO-WIRED.** Logic in `backend/services/forecast_detail_service.py` (`allocate(run_id)`), CLI wrapper `scripts/allocate_forecast_detail.py`. Splits the published `forecasts.total` into channel×region by trailing 26-week sales mix (UNK redistributed), reconciled exactly to total → `forecasts_detail`, and exposed to the AI. **Now called automatically after every forecast run** (`forecast_service.run_forecast`, non-fatal/decoupled — never fails the run). Live forecast untouched.
- **Phase 2b — DECLINED (decision, May 2026).** Webshop will NOT be broken out as its own fitted channel: it's ~4% of units, non-core, and behaves like retail (B2C). The engine's existing **retail+webshop vs wholesale** split is the meaningful one; the channel×region *breakdown* is served by 2a's allocation. (For the record: a retail/MP promo IS assumed to lift webshop — consistent with keeping them bundled.) Revisit only if webshop becomes strategic.
- Phases 3–5 (on-top by region, surface on pages, export dimension) remain optional/future.

**Net result: the "forecast per channel + region" goal is delivered** — retail-vs-wholesale split (engine) + HR/AT/SI/EXPORT region split (allocation, reconciled), queryable by the AI and refreshed every run.

## 7. Risks & effort
- **Biggest risk = attribution** (the UNK tail, wholesale region meaning). Phase 0 de-risks it before any modelling.
- **Sparsity** is handled by allocation, not modelling — keeps accuracy honest.
- **Compute/backtest** grows (more series); FA by thin channel×region will look low (structural).
- **Backward compatibility** is the hard guarantee: `forecasts.total` + `forecast_for_supply.csv` byte-for-byte behaviour preserved.
- Effort: this is the largest single change to the system, but the phasing means Phase 1–2 already deliver "webshop broken out + per-channel/region numbers" with everything downstream intact; later phases refine.