SKU-level sales forecasting
LightGBM with honest backtesting: separate validation and test, error analysis by weekday and an architecture on Google Cloud.
WAPE on test
0.730
Fold 5, never seen, recursive 7-day evaluation
Improvement vs. baseline
−26%
WAPE 0.784 vs. 1.062 for Seasonal Naive (28-day variant)
Modeled panel
20 × 373
20 SKUs by 373 days = 7,460 rows
Features
14
Calendar, lags and rolling statistics, no leakage
The problem
A store with one year of transactions (December 2010 to December 2011) and 20 products needs to anticipate its sales. I framed the problem as net units per product per day with a 7-day horizon, aligned with the weekly seasonality that shows up in the data.
I worked with two files: the line-by-line transactions (24,215 records) and the catalog with each SKU's category.
The solution explained in 10 minutes
A video walkthrough of the whole process: data, EDA, features, backtesting, results and architecture. It is narrated in Spanish.
Open on YouTube ↗Data quality and cleaning
- Only CustomerID has nulls (~20% of rows) and it isn't used as a feature, so it blocks nothing.
- I removed 182 exact duplicates (0.75%) and excluded the last day because it was cut off mid-day.
- I aggregated to daily net sales per SKU with a full date grid: a day without sales is zero, never NaN.
Distribution of daily net sales
Highly skewed (skew of 14), with a long tail and negative values from returns.
SKU-days per range of units sold per day · logarithmic Y axis
Logarithmic Y scale: each tick is 10 times the previous one. This shows the tail of large orders and returns (1 to 7 observations per range) next to the ~6,900 peak.
Aggregate time series
A rising trend toward year end and a very regular weekly oscillation, across the panel's 373 days.
Total net sales per day, Dec 2010 – Dec 2011 (373 days). Hover over the series to see the exact value.
Errors or wholesalers?
The red dots (>300 units) for SKU 85123A aren't noise: they are repeated purchases and cancellations from the same accounts throughout the year — real demand, not a data-entry error.
SKU 85123A · all transactions in the year
Sales by day of the week
The business doesn't operate on Saturdays: it isn't missing data, it's a calendar rule.
Why WAPE
With 24% of days at zero, MAPE breaks from division by zero. WAPE first sums all the errors and all the sales and divides once, so it never breaks and is comparable across SKUs of different scale. MAE and RMSE stay as a reference in units.
Example with 5 days of one SKU (units). Pick a metric to see which columns it uses.
| Day | Actual sales | Prediction | Abs. error | Error² |
|---|---|---|---|---|
| 1 | 10 | 8 | 2 | 4 |
| 2 | 0 | 1 | 1 | 1 |
| 3 | 15 | 20 | 5 | 25 |
| 4 | 0 | 0 | 0 | 0 |
| 5 | 100 | 70 | 30 | 900 |
| Σ | 125 | 38 | 930 |
38 / 125 = 30.4%
It sums everything first and divides once at the end: individual zeros don't break anything. It's a relative, comparable number.
Features and model
- 1
Features without information leakage
Calendar (day of week, week of year, month), product identity, lags of 1, 7, 14 and 28 days, and rolling means and standard deviations over 7, 14 and 28. Every feature uses only information from before the predicted day, and I verified it by recomputing rolling_mean_7 independently.
- 2
Signed-log target
The target is highly skewed; I trained LightGBM on a signed-log transformation that keeps zeros and negatives, compresses the extremes without discarding transactions and is reversible (checked with a round-trip assert).
- 3
Fixed hyperparameters
No automated search: the focus is the evaluation method, not squeezing out the last tenth of WAPE.
Backtesting: separate validation and test
I didn't use a random split because it would mix dates. I used an expanding window with 5 folds and a 7-day horizon. Folds 1 to 4 are the validation where I built all the analysis; fold 5 stayed untouched until the end and was evaluated only once. The evaluation is recursive —predict one day, insert it into the history, recompute features and continue— because that's the real production scenario.
Results (WAPE, lower is better)
Validation WAPE is a bit optimistic because that's where the intuition was built; the test one is the number I stand behind.
Actual vs. predicted (folds 1–4)
Most points cluster near the diagonal, where most of the demand lives; the large spikes (wholesale orders) are underestimated, as expected since signed-log compresses those extremes.
560 predictions (folds 1–4, validation). The red line is the perfect prediction; most points cluster near the diagonal and the large spikes are underestimated.
Residuals by day of the week
Saturday is almost perfect (it always predicts zero and it is always zero). Monday concentrates the large errors: 18 of 80 Mondays exceeded 100 units, with a peak of 1,844 on a single wholesale order. Wednesday is the most variable day and Tuesday the cleanest of the working days. The median is slightly positive on almost every day: the model underestimates a bit more often than it overestimates.
Box = interquartile range (folds 1–4). Dots are outliers by Tukey's rule; those beyond the visible range are marked at the edge (the worst one, on a Monday, reaches 1,844 units).
Final 7-day forecast, by SKU
With the result already reported, I retrained on the full history and generated the recursive 7-day forecast for the 20 SKUs: 140 predictions with no nulls, infinities, duplicates or negative values, with Saturdays forced to zero by a business rule (all checked with asserts). Pick a product to see its recent history and the forecast.
SKU
Kitchen · #5 by volume of the 20
Interpretability
With SHAP, day_of_week dominates, followed by StockCode, lag_7 and rolling_mean_28.
Mean importance of each variable (absolute SHAP value) on the final model, over the full history.
Architecture on Google Cloud
This isn't a theoretical plan: it's the state verified on GCP. The data and the model are in Cloud Storage and the model is registered in the Vertex AI Model Registry. There is no deployed endpoint on purpose, because the registry is free and an endpoint bills by the hour. Weekly retraining follows the same cycle as the horizon, and drift monitoring compares the real weekly WAPE against the test one (0.730), not the validation one.
1 · From data to model
ventas.csv · productos.csv
Transactions and catalog for the 20 SKUs.
Cloud Storage
Bucket with the data and the model artifacts.
Training
Pipeline: signed-log LightGBM over the full history.
Vertex AI Model Registry
Versioned, registered model. No cost as long as there is no endpoint.
2 · From model to forecast
Serving container
Custom image to serve the predictions.
Vertex AI Endpoint
Deliberately not deployed: an endpoint bills by the hour.
Forecast consumed
7-day forecast per SKU for replenishment.
3 · Continuous operation
Cloud Scheduler
Retrains every week, the same cycle as the 7-day horizon.
Monitoring / Logging
Detects drift by comparing the real weekly WAPE against the test one (0.730).
Limitations I left explicit
- A 7-day horizon is shorter than a typical stock-replenishment cycle; for that decision a longer horizon would be more relevant.
- The 7-day variant has no baseline of its own; the comparison against Seasonal Naive comes from the 28-day variant.
- Fixed hyperparameters and a single global model for the 20 SKUs.