Retail BI · Walmart M5 data · 10 stores · 2011–2016

Where is a 10-store retailer losing sales?

Five years of daily sales for 3,049 products, modelled in SQL with dbt, checked by automated tests, and compared with the forecast that won the M5 competition.

47.7M item-store-day rows dbt + DuckDB · 13 models · 17 tests Prices in US dollars

Finding 1Food is 56% of revenue but 80% of sales lost to empty shelves.

Suspected stock-outs cost about $0.79M in the last year, 1.7% of revenue. Store CA_3 alone accounts for almost a quarter of it.

Finding 2The forecast is excellent for the store and weak for the shelf.

Store-day error is 5%, with almost no bias. Item-day error is 75%, so replenishment needs safety stock at item level.

Finding 3Hobbies is under-forecast by 4.4%.

It has the highest error in every store and is under-forecast in 8 of 10, the pattern that leads to empty shelves.

Weekly revenue

By state, full weeks from Jan 2011 to Jun 2016

Forecast against actual sales

Units per day over the 28 out-of-sample days, 23 May to 19 Jun 2016

ActualM5 winning forecast

Forecast error by store and category

WAPE at store-day level for each category; darker means less accurate. Bias below zero means under-forecast.

Estimated revenue lost to suspected stock-outs

Last 365 days, by store

Largest suspected stock-outs

Fast-moving items with 7 to 56 days in a row without a sale, ranked by estimated lost revenue

How the numbers are built

Data: the public M5 Forecasting dataset (Walmart, 2020): daily unit sales for 3,049 items in 10 stores in California, Texas and Wisconsin, weekly shelf prices and a calendar. The forecast is the published submission of the competition winner.
Model: dbt on DuckDB. Staging views unpivot the wide CSVs; marts form a star schema (dim_date, dim_item, dim_store, fct_sales_daily, fct_forecast_eval) with tests for keys, relationships, accepted values and row counts.
WAPE = sum of absolute errors ÷ sum of actual units. Bias = (forecast − actual) ÷ actual. MAPE is not used because most item-days sell zero units.
Suspected stock-out: M5 has no inventory data. A run of 7 to 56 days with zero sales, for an item that averages 2 or more units a day, is treated as an empty shelf. Lost units = run length × the item's average daily units; lost revenue uses its average price. This is an estimate, not a measured loss.