Forecast Service¶

CPU-only hierarchical sales forecasting for Plenty and Amazon.

Source: backend/service_forecast
Docker: docker/service_forecast/Dockerfile (port 8008)
Tables: plenty_sales_forecast, amazon_sales_forecast, forecast_run_status, forecast_accuracy_daily, forecast_hpo_params
UI: Dashboard → Forecast (/forecast)

How predictions work¶

→ How sales forecasts (predictions) work — full pipeline: history panels, filters, LightGBM lags, recursive 30-day predict, fallback, and how to interpret columns.

Short version:

Aggregate order lines into daily series at platform / channel|marketplace / product.
Drop cold-start products (< ~30 positive sale-days).
For each metric (qty, orders, revenue, profit), fit mlforecast + LightGBM on lags 1/7/14/28 + dayofweek/month, then predict 30 days ahead recursively.
Write the new 30-day horizon; lock in forecasts for calendar days that left the horizon (for vs-actual); delete superseded future ds from older runs; prune ds older than 1 year. UI shows the latest live horizon.

Overview¶

Forecasts run separately per platform at three grains:

Grain	Plenty	Amazon
Platform	all Plenty / day	all Amazon / day
Channel / marketplace	per `origin`	per `marketplace`
Product	`asin_sku` × `origin` × ship-to country	ASIN/SKU × marketplace

v1 metrics (same pipeline, independent models):

forecast_qty — units
forecast_order_count — distinct orders / day
forecast_revenue
forecast_profit

Horizon: 30 days. Model: mlforecast + LightGBM (CPU, thread-capped). Nightly cron default 0 4 * * * Europe/Berlin.

Phase 2 (not implemented): return qty / rate, refund expenses, fee load.

API¶

Base path: /api/v1/forecast

Method	Path	Description
GET	`/health`	Liveness (also root `/health`)
GET	`/status`	Run status per source + model / HPO config knobs
GET	`/dimensions`	Distinct origins / countries / marketplaces for UI pickers
POST	`/run?source=all\\|plenty\\|amazon`	Start training (background; `wait=true` for sync; optional `hpo=true`)
POST	`/hpo?source=…`	Optional Optuna walk-forward HPO (CPU-heavy; not nightly by default)
GET	`/hpo`	Stored best LightGBM params per source × metric
POST	`/backtest?source=…`	Walk-forward MAPE with stored/default params; fills Ops accuracy rows
GET	`/stockout`	OOS projection + bias-based date range
GET	`/stockout/oos_candidates`	Ranked OOS candidates for the Forecast OOS tab
GET	`/internal/oos`	Internal compact OOS stats (auth: `X-Internal-Secret`)
GET	`/internal/oos/{product_key}`	Internal single-key OOS projection
GET	`/accuracy`	Ops: MAPE / MAE / bias for locked-in forecast vs actual
GET	`/plenty`	List Plenty forecast rows (filters: granularity, unique_id, channel, …)
GET	`/amazon`	List Amazon forecast rows
GET	`/plenty/series`	Distinct series for drill-down
GET	`/amazon/series`	Distinct series for drill-down

NPM / reverse proxy: forward /api/v1/forecast → service-forecast:8008.

Frontend env: VITE_FORECAST_API_URL=/api/v1/forecast (local often http://localhost:8008/api/v1/forecast).

Ops¶

Env knobs:

FORECAST_HORIZON_DAYS (default 30)
FORECAST_MIN_HISTORY_DAYS (product cold-start, default 30 positive sale-days)
FORECAST_MAX_PRODUCT_SERIES (default 0 = unlimited; set e.g. 2000 to cap product grain only)
FORECAST_N_ESTIMATORS (default 300 LightGBM trees)
FORECAST_N_JOBS / LIGHTGBM_NUM_THREADS / OMP_NUM_THREADS (default 2)
FORECAST_RETAIN_HISTORY_DAYS (default 365) — delete locked-in past ds older than this
FORECAST_HPO_ON_RUN (default false) — if true, Optuna runs before each forecast (cron / POST /run without hpo=)
FORECAST_HPO_TRIALS (default 30)
FORECAST_HPO_FOLDS (default 4) — walk-forward origins
FORECAST_HPO_STEP_DAYS (default 30) — gap between fold origins
FORECAST_HPO_EXCLUDE_RECENT_DAYS (default 1) — drop last N calendar days from today in HPO/backtest
FORECAST_HPO_HOLDOUT_DAYS (default 0) — final unseen window after Optuna (0 disables)
FORECAST_HPO_METRICS (default qty orders) — space- or comma-separated metrics for backtest + Optuna (compose must not use an unquoted comma default)

DDL / migration:

docker/postgres/tables/plenty_sales_forecast.sql
docker/postgres/tables/amazon_sales_forecast.sql
docker/postgres/updates/sales_forecast_tables.sql
docker/postgres/tables/forecast_hpo_params.sql / updates/forecast_hpo_params.sql

Apply the update script on existing DBs before first run (or rely on SQLModel create_all on startup).

Optional Optuna HPO¶

Nightly cron stays on fixed / stored LightGBM knobs. To tune once (or when Ops MAPE drifts):

# Background (recommended)
curl -X POST "http://localhost:8008/api/v1/forecast/hpo?source=all"

# Or fit immediately after HPO
curl -X POST "http://localhost:8008/api/v1/forecast/run?source=all&hpo=true"

HPO scores platform grain only with walk-forward MAPE, stores best params in forecast_hpo_params, and (by default) upserts day scores into forecast_accuracy_daily so the Ops tab shows real historical MAPE. Later production fits load those params automatically.

See How predictions work — HPO and Ops tab.

Metabase¶

Suggested questions (live chart = latest model_run_at; vs-actual = locked-in past ds):

Platform units & orders by day — granularity = 'platform', latest run only.
Channel / marketplace breakdown — granularity in (channel,marketplace).
Product × country/MP — granularity = 'product', filter product_key.
Forecast vs actual — past ds before the live horizon start.
(Phase 2) return rate & refund expenses — not in these tables yet.

Example SQL (Plenty platform — live horizon):

SELECT ds, forecast_qty, forecast_order_count, forecast_revenue, forecast_profit
FROM plenty_sales_forecast
WHERE granularity = 'platform'
  AND model_run_at = (SELECT MAX(model_run_at) FROM plenty_sales_forecast)
ORDER BY ds;

Do not sum product rows to rebuild platform totals — use granularity = 'platform'.

Example: locked-in past-day forecasts vs actual Plenty units:

WITH live AS (
  SELECT MAX(model_run_at) AS model_run_at
  FROM plenty_sales_forecast
),
horizon_start AS (
  SELECT MIN(ds) AS min_ds
  FROM plenty_sales_forecast f
  JOIN live ON f.model_run_at = live.model_run_at
)
SELECT
  f.ds,
  f.forecast_qty,
  COALESCE(a.actual_qty, 0) AS actual_qty
FROM plenty_sales_forecast f
CROSS JOIN horizon_start h
LEFT JOIN (
  SELECT (order_date AT TIME ZONE 'UTC')::date AS ds, SUM(quantity) AS actual_qty
  FROM plenty_orders
  GROUP BY 1
) a ON a.ds = f.ds
WHERE f.granularity = 'platform'
  AND f.ds < h.min_ds
ORDER BY f.ds;

Stockout / days of cover (product)¶

GET /api/v1/forecast/stockout?source=plenty|amazon&product_key=…

Resolve stock from repricer_articles (match sku, or FBA asin / seller_sku, or FBM seller_sku).
Take future (ds >= today) forecast_qty from the latest run (summed across all product series for that key by default).
Burn down stock day by day → remaining_stock, oos_date, days_until_oos.
Apply platform bias_pct (Ops average offset) to scale demand → range:
oos_date_min / days_until_oos_min (earlier / faster burn)
oos_date_max / days_until_oos_max (later / slower burn)
Point estimate stays on raw forecast. If no accuracy rows exist, a default ±15% band is used.

UI: Forecast → OOS and product stockout chips show ranges like OOS in 2–3d.

Internal OOS API (other containers)¶

For service_plenty Renner / sheet exports (and similar):

GET /api/v1/forecast/internal/oos?source=both&max_days_until_oos=14&limit=500
X-Internal-Secret: <INTERNAL_API_SECRET>

Optional query params:

Param	Meaning
`product_keys`	Comma-separated SKU/ASIN/seller_sku filter
`query`	ILIKE filter on `product_key`
`max_days_until_oos`	Keep items whose earliest OOS is within N days
`include_covers_horizon`	Also return items that do not OOS in-horizon
`bias_days`	Accuracy window for platform bias (default 30)

Docker: http://service-forecast:8008/api/v1/forecast/internal/oos
Auth: X-Internal-Secret or X-API-KEY = INTERNAL_API_SECRET (open only if DEV_MODE=true and no secret set).

Single key: GET /internal/oos/{product_key}?source=plenty.

Ops accuracy (MAPE)¶

→ Ops tab — accuracy & MAPE — how to read MAPE / MAE / Bias, the two charts, data source, and local seed script.

Short version: after locked-in past ds exist or after POST /backtest / POST /hpo, compare predictions to actuals in forecast_accuracy_daily. Dashboard → Forecast → Ops.

Local seed for empty DBs:

cd backend/service_forecast
python scripts/seed_forecast_accuracy.py

DDL: docker/postgres/tables/forecast_accuracy_daily.sql / docker/postgres/updates/forecast_accuracy_daily.sql.