Running out of stock costs more than the missed sale. You lose the customer to a competitor, you lose the search ranking, and if it's a key product, you lose repeat buyers who assumed you'd have it. This project was built to stop that from happening by predicting stockouts before they occur.
I built a Python pipeline that pulls historical sales data daily, runs a time series forecast per SKU, compares predicted demand against current stock levels, and fires a Slack alert for any product that will hit zero before the next replenishment cycle. The purchasing team gets a prioritised reorder list every morning without opening a spreadsheet.
The Architecture
Step 1 — Loading and Cleaning the Data
Sales history comes from a Google Sheets export (or database query). The key columns are: date, sku, units_sold, and current stock_level. Clean it with pandas: parse dates, fill gaps with zeros for days with no sales (not NaN), and resample to daily frequency per SKU.
You need at least 60 days of history for a meaningful forecast. Less than that and you're guessing. More than 180 days and you need to handle trend drift — products that were popular six months ago may not follow the same pattern today.
Step 2 — Choosing the Right Model
For most retail SKUs, a simple approach outperforms complex models. I use a combination depending on the SKU's sales pattern:
- Regular sellers: 7-day rolling average with a seasonal multiplier for day-of-week effects
- Trending products: Linear regression on the last 30 days
- Seasonal/bursty sellers: Holt-Winters exponential smoothing (statsmodels
ExponentialSmoothing)
The model selection is automated: calculate the coefficient of variation on each SKU's sales history and route to the appropriate model. High CV (bursty) → Holt-Winters. Low CV with trend → linear. Low CV without trend → rolling average.
The forecast horizon
Forecast 14 days forward. That's typically enough to cover a reorder lead time for most suppliers. Sum the 14-day forecast for each SKU — that's your predicted demand. Compare it to current stock.
Step 3 — Calculating Days Until Stockout
avg_daily_demand = forecast_14d / 14
days_until_stockout = current_stock / avg_daily_demand if avg_daily_demand > 0 else 999Any SKU with days_until_stockout under your reorder lead time (say, 7 days) goes on the alert list. Sort by urgency — the one running out soonest is at the top.
Step 4 — Slack Alerts
The Slack message uses Block Kit formatting for readability. Each alert item shows the SKU name, current stock, days remaining, and suggested reorder quantity (forecast demand × 1.2 for safety buffer). Critical items (under 3 days) get flagged with a different colour block.
The webhook call is a simple POST with the Block Kit JSON payload. No Slack SDK needed — just Python's requests library. The alert fires every morning at 07:00 via a cron job on the same Hetzner VPS that runs n8n.
Data Quality Checks
Before running forecasts, the pipeline checks for data issues and alerts separately if any are found: missing SKUs that were present yesterday (possible data sync failure), stock levels that haven't updated in 48 hours (possible integration issue), and anomalous sales spikes that would distort the forecast (flagged but not excluded automatically).
Bad data produces confident wrong forecasts. The data quality checks save more stockouts than the model itself.
Results
For a mid-sized online retailer with 340 active SKUs, this pipeline caught 23 potential stockouts in the first month that the team would not have noticed manually. Average daily time spent on inventory review dropped from 45 minutes to reviewing a pre-prioritised Slack list that takes 5 minutes. Zero stockouts on high-margin products in the two months since deployment.