Как посчитать stockout rate в SQL

Зачем stockout rate

Когда товара нет на складе — теряем продажи, customer goes to competitor. Stockout rate — главная operational метрика retail/e-com. 5% stockout = 5% потерянных продаж минимум. Снижение rate напрямую растит revenue без новых customers.

Формула

stockout_rate = (SKU-hours with zero inventory) / (total SKU-hours)

Или проще: доля дней-SKU с нулевым остатком.

Stockout в SQL

WITH inventory_snapshots AS (
    SELECT
        sku_id,
        warehouse_id,
        snapshot_date,
        quantity_on_hand
    FROM inventory_history
    WHERE snapshot_date >= CURRENT_DATE - INTERVAL '30 days'
)
SELECT
    sku_id,
    COUNT(*) AS observations,
    SUM(CASE WHEN quantity_on_hand = 0 THEN 1 ELSE 0 END) AS stockout_observations,
    SUM(CASE WHEN quantity_on_hand = 0 THEN 1 ELSE 0 END)::NUMERIC * 100
    / NULLIF(COUNT(*), 0) AS stockout_pct
FROM inventory_snapshots
GROUP BY sku_id
ORDER BY stockout_pct DESC
LIMIT 50;

Топ SKU с stockout — flagship products, требующие приоритетного reorder.

Lost sales estimate

Сколько продали бы при наличии:

WITH baseline_velocity AS (
    SELECT
        sku_id,
        AVG(units_sold_per_day) AS avg_daily_sales
    FROM sku_sales_history
    WHERE in_stock = TRUE
      AND sale_date >= CURRENT_DATE - INTERVAL '60 days'
    GROUP BY sku_id
),
stockout_days AS (
    SELECT
        sku_id,
        COUNT(DISTINCT stockout_date) AS days_out
    FROM inventory_history
    WHERE quantity_on_hand = 0
      AND snapshot_date >= CURRENT_DATE - INTERVAL '30 days'
    GROUP BY sku_id
)
SELECT
    s.sku_id,
    s.days_out,
    b.avg_daily_sales,
    s.days_out * b.avg_daily_sales AS estimated_lost_units,
    s.days_out * b.avg_daily_sales * sku.price AS estimated_lost_revenue
FROM stockout_days s
JOIN baseline_velocity b USING (sku_id)
JOIN sku_pricing sku USING (sku_id)
ORDER BY estimated_lost_revenue DESC
LIMIT 50;

Estimate lost revenue даёт sense, насколько важна prevention.

По SKU и складу

SELECT
    warehouse_id,
    COUNT(DISTINCT sku_id) AS active_skus,
    COUNT(DISTINCT CASE WHEN quantity_on_hand = 0 THEN sku_id END) AS sku_in_stockout,
    COUNT(DISTINCT CASE WHEN quantity_on_hand = 0 THEN sku_id END) * 100.0
    / NULLIF(COUNT(DISTINCT sku_id), 0) AS stockout_sku_pct
FROM inventory_history
WHERE snapshot_date = CURRENT_DATE - INTERVAL '1 day'
GROUP BY warehouse_id
ORDER BY stockout_sku_pct DESC;

Один склад с stockout 20% — supply chain проблема, а не demand.

Частые ошибки

Ошибка 1. Считать только полную stockout. «Low stock» (1-5 units) → тоже потерянные продажи если demand 10/day.

Ошибка 2. Игнорировать backorders. Backorder — формально stockout, но revenue не теряется (customer ждёт).

Ошибка 3. Считать на slow movers. SKU не продаётся 6 месяцев — его stockout не теряет ничего. Сегментируйте.

Ошибка 4. Lost sales overestimate. Customer может купить substitute, не уходить. Substitution rate — 30-60%.

Ошибка 5. Stockout как single metric. Combine с inventory turnover. Низкий stockout + high turnover = optimal.

Связанные темы

• Как посчитать inventory turnover в SQL
• Как посчитать sell-through rate в SQL
• Как посчитать fill rate в SQL
• Как посчитать on-time delivery в SQL

FAQ

Какой stockout норма?

E-com: 2-5%. Retail: 5-10%. Топ-SKU: меньше 1%.

Substitution rate?

30-60% customers купят похожий. Остальные уйдут.

Stockout vs out-of-stock?

Synonym. Out-of-stock — более customer-facing термин.

Safety stock?

Buffer для variance. Стандарт — 2-4 weeks demand.

Cost of stockout?

Lost sales + customer LTV impact + brand damage. Часто 2-3× lost sales monetary.