Quarterly Sales Forecasting

Context

Regional leadership wanted an early read on where each APAC market, and the region overall, would land by quarter-end, based on historical sales. A colleague had built a proof of concept in Prophet, but it only forecast APAC in aggregate, never broke down to individual markets, and was still rough when he left. I took it over and rebuilt it.

The real problem was the data, not the model

Fitting Prophet is a few lines. Prophet expects one clean, regularly spaced series per entity: a single date column, a single value column, no gaps, one row per period. Our sales data looked nothing like that. It sat in the data lake as transactional records: individual shipments and cancellations, multiple product configurations, several source systems each with their own product names and market codes, irregular timing, and no consistent grain. Feeding that to a forecaster directly would have produced a model that was really fitting noise and structural artefacts.

Redesigning the ingestion

I rebuilt the pipeline that turns raw sales into model-ready input. That meant:

• extracting the raw transactional sales from the data lake with SQL, and resolving each record to a net movement (a shipment counts as +1, a cancellation as -1) so the series reflected real demand rather than gross activity;
• reconciling product and market identifiers across source systems onto one standard set of keys, reusing the same cleaned sales definitions the rest of the team's reporting relied on;
• aggregating to a single regular grain per market, and zero-filling the periods with no sales so every market had a continuous, evenly spaced history rather than a sparse one that would distort Prophet's trend and seasonality;
• writing the result to one unified, versioned table that both the model and the dashboard read from, so a re-run each quarter produced consistent numbers.

The modelling loop

On top of that clean layer the model is straightforward: iterate over the 15 markets, fit a Prophet model on each market's own history, capture the forecast and its uncertainty interval, and union everything back into one flat table tagged by market. The region is rolled up from the individual market forecasts rather than modelled as a single series. Output feeds a Power BI dashboard where market leaders see projected versus actual landing by quarter. By the time I left Align, it was running market by market across all 15 APAC markets.

Result

Backtested against prior quarters, forecasts came within roughly 8 to 10 percent WAPE, close enough to serve as a directional early indicator for planning well before actuals closed.

What I'd improve

The evaluation needs more rigour. WAPE is volume-weighted, so it flatters the model on large markets and hides error on small ones. A next pass would add a per-market error breakdown, a seasonal-naive baseline to confirm the model actually beats "same quarter last year," and a bias measure to catch systematic over- or under-forecasting. With only a few years of quarterly history per market, Prophet's yearly seasonality is thin, so benchmarking against Holt-Winters or a simple run-rate is worth doing. The retrain and refresh could be scheduled rather than run by hand.