How sales forecasts (predictions) work¶

This page explains exactly what service_forecast does from order rows to the 30-day charts on /forecast and in Metabase.

Code entry points:

Step	Module
Orchestration	`backend/service_forecast/app/services/pipeline.py`
History panels	`backend/service_forecast/app/data/load_sales.py`
Train + predict	`backend/service_forecast/app/models/train_forecast.py`
Calendar helpers	`backend/service_forecast/app/features/calendar.py`
Persist	`backend/service_forecast/app/persist/write_forecast.py`

Big picture¶

flowchart LR PO[plenty_orders / amazon_orders] LOAD[Daily panels at 3 grains] FILT[Cold-start + product filters] TRAIN[mlforecast + LightGBM per metric] FALL[Seasonal-naive fallback] STORE[live horizon + locked-in past ds] UI[Forecast page / Metabase] PO --> LOAD --> FILT --> TRAIN TRAIN -->|fit/predict OK| STORE TRAIN -->|fail| FALL --> STORE STORE --> UI

Important design choices

Plenty and Amazon never share a model. Each platform is loaded, trained, and written separately (source=plenty|amazon|all).
Three grains are trained explicitly (platform, channel/marketplace, product). Platform totals are not a sum of SKU forecasts.
Four targets are trained independently (units, order count, revenue, profit). They are not constrained to be consistent with each other (e.g. orders can move differently from units on a given day).
Each successful run writes a new live 30-day horizon. Older rows for the same future ds values are deleted (superseded). Rows for calendar days that fell out of the new horizon (e.g. yesterday’s prediction for “today”) are kept for vs-actual. Rows with ds older than FORECAST_RETAIN_HISTORY_DAYS (default 365) are pruned. The UI/API default to the latest live run.

Step 1 — Build daily history panels¶

Plenty (`load_plenty_daily`)¶

Reads line rows from plenty_orders (order_date, origin, shipping_destination, asin_sku, order_id, quantity, price_gross, profit).

Per calendar day (ds = normalized date):

Target column	Aggregation
`y_qty`	`SUM(quantity)`
`y_orders`	`COUNT(DISTINCT order_id)`
`y_revenue`	`SUM(price_gross * quantity)`
`y_profit`	`SUM(profit)`

Then three grains are stacked into one panel:

`granularity`	`unique_id` example	Dimensions
`platform`	`plenty__ALL`	channel/country = `ALL`
`channel`	`plenty__Otto`	`channel` = Plenty `origin`
`product`	`SKU__Otto__DE`	`asin_sku` × `origin` × ship-to country

History includes lines even if has_retour is true (returns are phase 2).

Amazon (`load_amazon_daily`)¶

Same idea from amazon_orders (purchase_date, marketplace, asin/seller_sku, amazon_order_id, quantity_ordered, item_price_amount, profit).

Cancelled / Canceled orders are excluded.
Product key = COALESCE(asin, seller_sku).
Grains: platform (amazon__ALL), marketplace (amazon__IT), product (B0…__IT).

Calendar fill¶

For each unique_id, missing days between first and last sale day are inserted with zeros for all y_* metrics. That gives continuous daily series so lags (1/7/14/28) are well-defined.

Step 2 — Which series are kept?¶

Before training, _filter_series applies:

Grain	Keep rule
Platform / channel / marketplace	At least 7 calendar days in the filled panel
Product	At least `FORECAST_MIN_HISTORY_DAYS` (default 30) days with `y_qty > 0`
Product cap	If `FORECAST_MAX_PRODUCT_SERIES > 0`, keep top-N products by total historical qty; `0` = unlimited

Products that fail cold-start are skipped entirely (no row in the forecast table). data_points_used on written rows is the count of positive-target days used for that series.

Additionally, LightGBM requires ≥ 29 calendar days of filled history (longest lag is 28). Shorter series are dropped inside _mlforecast_predict.

Step 3 — What “predict” means (mlforecast + LightGBM)¶

For each platform and each target metric (y_qty, y_orders, y_revenue, y_profit), the service:

Builds a training frame with columns unique_id, ds, y (that metric).
Fits one global LightGBM regressor over all kept series of that platform at once (platform + channel + product stacked). Series identity is carried by unique_id; the model shares patterns across series via lags and date features.
Predicts the next FORECAST_HORIZON_DAYS (default 30) days recursively.

Features the model actually uses (v1)¶

Training matrix is intentionally minimal (stable fit/predict):

Feature group	Details
Lags of `y`	`1, 2, 3, 7, 14, 21, 28`
Lag transforms	rolling mean of lag-1 (7d) and lag-7 (4)
Date features	`dayofweek`, `month`, `week`
Seasonal blend	mix with per-series DOW mean (`FORECAST_SEASONAL_BLEND`)

So the model learns things like: “given last week / last month’s level and that tomorrow is Saturday in August, what is tomorrow’s y?”

Not currently fed into LightGBM at predict time (code prepares calendar/holiday helpers, but the live fit path does not pass static channel/marketplace columns or holiday flags into MLForecast, to avoid dtype/shape failures across versions):

Country holidays / Black Friday flags (features/calendar.py)
Static categoricals (granularity, channel, country, marketplace)

Seasonality still enters through month and dayofweek, and through the lag structure.

Recursive multi-step forecast¶

For horizon day 1, lags come from real history.
For day 2, lag-1 becomes the day-1 prediction, and so on out to day 30.

That is why early horizon days are usually more trustworthy than day 30, and why separate models for qty vs orders can diverge (they recurse on different predicted paths).

LightGBM settings¶

From env / config (baseline when no HPO row exists):

Setting	Default	Role
`FORECAST_N_ESTIMATORS`	300	Trees
`FORECAST_N_JOBS`	2	CPU threads (also `LIGHTGBM_NUM_THREADS` / `OMP_NUM_THREADS`)
`force_col_wise=True`	on	Stable CPU training
`boosting_type`	`gbdt`	Fixed (not searched by Optuna)

Other LightGBM knobs (learning_rate, num_leaves, …) use library defaults until Optuna writes overrides into forecast_hpo_params.

Model tag stored on rows: mlforecast_lgbm_cpu.

Optional Optuna HPO¶

Hyperparameter search is optional and off for nightly cron (FORECAST_HPO_ON_RUN=false). Use it for an initial tune or when Ops MAPE degrades.

flowchart LR PANEL[Platform panel] WF[Walk-forward folds] OPT[Optuna trials] STORE[(forecast_hpo_params)] ACC[(forecast_accuracy_daily)] FIT[Production fit all grains] PANEL --> WF --> OPT --> STORE OPT --> ACC STORE --> FIT

Walk-forward (no future leakage):

Exclude last EXCLUDE days from today (default 1) → eval_max
Optional HOLDOUT days before eval_max for final check (default 0 = off)
Fold 1…K: train on ds ≤ origin (origins within tune window only)
After Optuna (if holdout > 0): train through tune_max, score hold-out once

Average walk-forward MAPE is the Optuna objective (minimize). Hold-out MAPE is reported separately (stored under params.holdout_mape_pct) as an overfitting check. Scoring uses platform grain only (plenty__ALL / amazon__ALL) for CPU cost; best params are then applied to the full multi-grain production fit.

Tune Plenty and Amazon independently (source=plenty|amazon) when one platform drifts — params are stored per source × metric.

Search space (small, bounded to avoid flat forecasts):

Parameter	Range (approx.)
`learning_rate`	0.03 – 0.15 (log)
`num_leaves`	20 – 64
`min_child_samples`	5 – 35
`subsample`	0.7 – 1.0
`colsample_bytree`	0.7 – 1.0
`reg_lambda`	1e-2 – 5 (log)

Stored HPO values are also clamped on load (min_child_samples ≤ 40, learning_rate ≤ 0.2, …) so an older over-smoothed tune cannot flatten product series.

HPO trains on platform + channel/marketplace (not product), scores platform MAPE, and penalizes trials whose forecast std is much lower than actual std.

Production fit also uses richer lags (1/2/3/7/14/21/28 + rolling means) and a small day-of-week seasonal blend (FORECAST_SEASONAL_BLEND, default 0.25) so weekly shape survives even if trees under-react.

Fixed during search: boosting_type=gbdt, n_estimators from env, thread/verbosity/seed.

Triggers:

Trigger	Behavior
`POST /api/v1/forecast/hpo`	Tune only; store params; optionally write walk-forward days to Ops
`POST /api/v1/forecast/run?hpo=true`	HPO then full forecast
`FORECAST_HPO_ON_RUN=true`	HPO before cron / default `POST /run` (usually leave false)
`POST /api/v1/forecast/backtest`	Walk-forward with stored (or default) params; fill Ops without searching

Default metrics to tune / backtest: FORECAST_HPO_METRICS=qty,orders (comma-separated; add revenue / profit if needed).

Code: backend/service_forecast/app/services/hpo.py, params CRUD in persist/hpo_params.py.

Fallback: seasonal naive¶

If import, fit, or predict fails for a metric, that metric alone falls back to day-of-week historical mean per series (_seasonal_naive_predict): for each future date, use the mean of that weekday in history (or overall mean if that weekday never appeared). Predictions are clipped to ≥ 0.

Check logs for Falling back to seasonal naive for metric=… if charts look “too flat / too regular”.

Step 4 — Merge four metrics and write¶

forecast_all_targets merges the four metric runs on (unique_id, ds) into wide columns:

Training target	Stored column
`y_qty`	`forecast_qty`
`y_orders`	`forecast_order_count`
`y_revenue`	`forecast_revenue`
`y_profit`	`forecast_profit`

All values are clipped to ≥ 0. Then persistence (write_*_forecasts):

Insert the new horizon rows (model_run_at = now).
Supersede futures: delete older runs where ds >= min(ds of new horizon) — those days are re-forecasted.
Lock in the past: older rows with ds < min(new horizon) stay (e.g. run on 28.07 predicted ds=29.07; on 29.07 the new horizon starts at 30.07 → keep 29.07).
Prune backlog: delete any row with ds < today − FORECAST_RETAIN_HISTORY_DAYS (default 1 year).

forecast_run_status stores per-source running|success|error, message, series/row counts, timestamps.

Reads (UI / default API): filter to MAX(model_run_at) for the live forward chart.

Backtesting: rows with ds before the live horizon start are the locked-in predictions — join to actuals on that ds.

How to read a prediction¶

Example Plenty platform row:

Field	Meaning
`granularity=platform`	Whole Plenty, not one SKU
`unique_id=plenty__ALL`	Series key for the chart
`ds=2026-08-15`	Forecast for that calendar day
`forecast_qty≈90`	Expected units that day
`forecast_order_count≈60`	Expected distinct orders that day
`forecast_revenue` / `forecast_profit`	Same idea for € metrics
`data_points_used`	How many positive history days fed that series

Do not sum product forecasts to get platform totals — use granularity='platform' (or channel/marketplace) rows.

Do not expect forecast_qty / forecast_order_count to equal a stable AOV every day — metrics are independent models.

When forecasts refresh¶

Trigger	Behavior
Nightly cron	Default `0 4 * * *` Europe/Berlin (`FORECAST_CRON`) if `FORECAST_AUTO_START_CRON=true`
UI / API	`POST /api/v1/forecast/run?source=plenty\\|amazon\\|all` (background unless `wait=true`)
Optional HPO	`POST /hpo` or `POST /run?hpo=true` — not part of the default nightly path
Only one job	Concurrent runs / HPO / backtest rejected (`409` / “already running”)

Cron should run after order imports so history is fresh.

Mental model vs “classic ML”¶

Question	Answer
Is there one neural net over all ASINs?	No — gradient-boosted trees on lag features, one fit per platform × metric
Is hierarchy reconciled (SKU sum = platform)?	No in v1 — filter by `granularity`
Are prediction intervals available?	No — point forecasts only
Do we tune LightGBM every night?	No — optional Optuna (`POST /hpo`); production reuses `forecast_hpo_params`
Why only ~200 product series from thousands of SKUs?	Cold-start (30 positive days) + lag length; intermittent SKUs are skipped
Why can Tuesday’s “orders” look low vs “units”?	Separate recursive models; inspect both metrics
How do we know if past predictions were good?	Locked-in past `ds` vs actuals or walk-forward backtest → Ops / MAPE

See also: Ops tab — accuracy & MAPE.

Implementation map (for debugging)¶

run_forecast → optional run_hpo → _run_one(source)
load_*_daily → filled panel
load_params_map → forecast_all_targets → filter → for each metric _mlforecast_predict (or naive)
write_*_forecasts → insert horizon, supersede future ds, keep past ds, prune >1y

Smoke-test pattern inside the container: load panel → _filter_series → _mlforecast_predict(..., "y_qty") and confirm logs have no Falling back lines.