Case Study · Retail
Defacto ELOSS: measuring the sales lost to stock-outs in e-commerce
A weekly calculation answering 'what would have sold had the product been there' for every SKU that runs out of stock on Defacto's own site and the marketplaces. It computes lost sales from pre-stock-out velocity with special-day and trend effects, then splits them by day using the daily sales distribution. An incremental design cut a calculation that took hours down to minutes.
- Weekly
- Run
- ~30 min
- Runtime
- Last 3 weeks
- Refresh
- Day · SKU
- Granularity
Monday morning, two procedures
Hours before the incremental design
Past movements kept in mind
Weekly loss distributed by daily sales
Problem
When a product runs out of stock in e-commerce, what shows in the sales report is zero — yet on those days customers looked for the product and did not find it. That invisible loss distorts decisions about how much of which product to procure and which stock-outs are truly expensive. Defacto needed to measure this loss per SKU, with a consistent method, across its own site and several marketplaces.
Solution
ELOSS runs in two steps every week.
Calculation. Stock in the warehouses defined for e-commerce is checked to find the days each SKU was out of stock. The sales velocity before the stock-out is the basis for what the product “would have” sold; corrections for special-day effects, weekly sales change and seasonal trend are applied on top. The result is weekly lost sales per SKU.
Daily split. Dividing the weekly loss evenly across the stock-out days would be easy but would not reflect sales swings. Instead, the daily total sales of e-commerce and the marketplaces are pulled, and each SKU’s loss in each stock-out interval is distributed across the days according to the daily sales distribution in that interval. If weekend sales are high, so is the weekend’s share of the loss.
Incremental design
When ELOSS was first built it recomputed the entire history on every run; as data grew, runtime stretched to hours. The code was rebuilt around two structures:
- Delete-write: historical stock, stock adjustments and sales accumulate incrementally, with protection against the procedure accidentally running twice.
- Fill-empty: stock-out detection, lost sales, uplift and the other corrections are recalculated from scratch in these tables every week.
Because lost sales need the movements of the weeks before the stock-out, each run refreshes the last three weeks. One calculation block went from hours to minutes with this change; the weekly run now takes about 30 minutes. Every step writes to the DSO’s log structure.
Outcome
Lost revenue is visible in Power BI, broken down by own site and marketplace. Which products genuinely lose sales to stock-outs and where the loss is negligible — procurement and stock decisions rest on this measure. Lumtify coded, integrated and maintained the project; ELOSS is also one of the data sources of the JUMP-UP executive dashboard.