01The problem

Forecasting retail demand across products and geographies means more than fitting a model. Runs have to be repeatable, long jobs have to be diagnosable, and a single blended accuracy number can hide the products where the model is badly wrong.

02The pipeline

An eleven-phase pipeline on BigQuery ML with ARIMA_PLUS_XREG covers feature preparation, back-testing, training, promotion forecasting and result enrichment. It generates ad hoc SQL for self-guided data transformation.

03Same inputs, same series

Runs execute in parallel, so which product lands in which series must not depend on timing. Deterministic series mapping keeps assignments stable, along with regressor ordering and standardization. That preserves comparability and reproducibility between runs.

04Scoring every SKU

Holdout scorecards compare each SKU against naive and trend baselines. This exposes lifecycle-driven failures that a blended accuracy figure conceals, and it stops weak product series from hiding inside a good average.

05Making long runs diagnosable

Phase checkpoints, execution limits, BigQuery job polling, status updates and cost tracking keep long runs observable and bounded. A failed run can be diagnosed from its checkpoints instead of restarted blind.

06Optimizing promotions

A per-product optimizer searches a 2.5-percentage-point discount ladder across four objectives under margin and sell-through constraints. It uses a constant-elasticity demand response, and I verified monotonic uplift across discount levels with synthetic scenarios, since historical business outcomes were limited.