МОДУЛЬ 3. Как BI и DWH помогают бороться с Out-of-Stock: технологии, витрины, архитектура
В первых двух модулях мы говорили о том, как измеряется Out-of-Stock (OOS) и какие основные причины его возникновения. Теперь мы переходим к следующему уровню — как BI-системы, хранилища данных (DWH) и современные аналитические архитектуры могут быть использованы для автоматического обнаружения, анализа и снижения OOS.
Этот модуль будет особенно полезен тем, кто участвует в создании архитектуры аналитических систем: аналитикам, архитекторам данных, разработчикам DWH, владельцам бизнес-процессов.
Роль DWH в управлении Out-of-Stock
Хранилище данных (Data Warehouse) — это центральный компонент для сбора, обработки и хранения информации из различных источников. В контексте OOS оно выполняет три ключевые роли:
- Интеграция данных из разнородных источников (POS, ERP, WMS, MDM, планограммы)
- Формирование аналитических витрин, позволяющих делать измерения и выявлять причины
- Обеспечение единой версии правды для BI и ML-моделей
Типовые источники:
- Продажи (POS): SKU, чек, дата, магазин, касса
- Остатки (WMS или ERP): склад, SKU, количество, дата, источник (бэкрум, полка)
- Справочники: SKU, категория, бренд, KVI-признак
- Прогноз: SKU, магазин, день, прогнозный объем
- Планограммы: SKU, полочное пространство, Facing, Store Plan
- Данные о поставках: SKU, дата, поставщик, объем, статус поставки
Архитектура DWH для анализа OOS
Рекомендуемая логическая структура DWH:
- Факт-продажи: таблица fact_sales
- Факт-остатков: fact_stock
- Факт-поставок: fact_delivery
- Факт-прогноза: fact_forecast
- Факт-планограммы: fact_planogram
- Измерения: dim_store, dim_sku, dim_date, dim_category
Типовые связи:
- Все факты соединяются через ключи SKU, Store, Date
- Прогноз и продажи сравниваются по SKU-Store-Date
- Остатки сопоставляются с продажами и поставками
- Планограмма связывается с продажами через SKU и Store
BI-витрины:
- oos_metrics (основная витрина для анализа Out-of-Stock)
- oos_patterns (паттерны повторяющихся OOS)
- oos_root_causes (оценка причин)
- replenishment_analytics (анализ пополнений)
- forecast_vs_actual (сравнение прогноза и факта)
Как строятся витрины OOS в DWH
Рассмотрим пример построения витрины oos_metrics.
Ключевые поля:
- SKU_ID
- Store_ID
- Date
- Sales_Qty
- Stock_Qty
- Forecast_Qty
- Delivery_Qty
- Shelf_Space (в метрах или фейсингах)
- OOS_Flag (1 если товар отсутствует на полке)
- OOS_Duration (в часах)
- Lost_Sales_Estimated (в штуках)
- Lost_Revenue_Estimated (в рублях)
- Phantom_Flag (признак phantom inventory)
Пример SQL-фрагмента:
SELECT s.SKU_ID, s.Store_ID, s.Date, CASE WHEN st.Stock_Qty = 0 AND s.Sales_Qty = 0 THEN 1 ELSE 0 END AS OOS_Flag, CASE WHEN st.Stock_Qty = 0 AND s.Sales_Qty > 0 THEN 'phantom' END AS Phantom_Flag, f.Forecast_Qty, d.Delivery_Qty, pg.Shelf_Space, -- расчет потерь f.Forecast_Qty - s.Sales_Qty AS Lost_Sales_Estimated, (f.Forecast_Qty - s.Sales_Qty) * sku.Price AS Lost_Revenue_Estimated FROM fact_sales s LEFT JOIN fact_stock st ON s.SKU_ID = st.SKU_ID AND s.Store_ID = st.Store_ID AND s.Date = st.Date LEFT JOIN fact_forecast f ON s.SKU_ID = f.SKU_ID AND s.Store_ID = f.Store_ID AND s.Date = f.Date LEFT JOIN fact_delivery d ON s.SKU_ID = d.SKU_ID AND s.Store_ID = d.Store_ID AND s.Date = d.Date LEFT JOIN fact_planogram pg ON s.SKU_ID = pg.SKU_ID AND s.Store_ID = pg.Store_ID LEFT JOIN dim_sku sku ON s.SKU_ID = sku.SKU_ID
BI-компоненты: что и как визуализировать
-
KPI-дашборд:
- Общий уровень OOS (в процентах и рублях)
- ТОП-10 категорий по потерям
- ТОП-10 магазинов с наихудшей доступностью
- Детализация SKU-Store:
- Таблица с флагом OOS
- Lost Sales
- План vs факт
- График тренда
- Повторяющиеся периоды отсутствия
- Графики с периодичностью
- Автоматическая классификация: поставка, полка, спрос
- Географическая карта магазинов с уровнем OOS
- Тепловая карта по категориям и датам
- Паттерны OOS:
- Карта рисков:
Методология работы с Lost Sales и Lost Revenue
Формулы:
-
Lost Sales = Прогноз – Факт, если факт < прогноза и stock_qty = 0
-
Lost Revenue = Lost Sales * Цена (средняя или рекомендованная)
Важно: если нет прогноза, используем метод “forward-fill”: берём среднее по предыдущим неделям или по аналогичным дням.
Вариант расчета через POS-анализ:
- Если продажи регулярно происходят ежедневно, а в конкретный день = 0, это может быть признак OOS
- BI-алгоритм проверяет такие разрывы и метит как подозрительные
Примеры автоматизации
- BI-алерт: если SKU в категории KVI имеет OOS > 6 часов — создается уведомление
- ML-модель: предсказание вероятности OOS по SKU-Store-Day на основе погодных данных, промо, праздников
- Регламент: если Shelf Availability ниже 92% в течение 3 дней — автоматическое задание логистике
- Дашборд для руководства: агрегированные потери и план по их снижению
Работа с рисками
|
Риск |
Меры |
|---|---|
|
Неполные данные |
Витрины строятся через staging, используется контроль полноты загрузки |
|
Дублирование SKU |
Справочники нормализуются через MDM |
|
Расхождения между системами |
Внедрение витрины "System Reconciliation" |
|
Плохая детализация POS |
Переход на чековую детализацию, агрегаты нельзя использовать для OOS |
|
Несвоевременное обновление |
Использование SLA на ETL-процессы и мониторинг задержек загрузки |
DWH и BI — это не просто инструменты визуализации. Это стратегическая платформа, которая позволяет превратить проблему OOS из хаоса и ручной отчетности в управляемую систему с автоматическим контролем, предикцией и предупреждением сбоев.
Для эффективного использования BI и DWH необходимо выстроить:
- Правильную архитектуру данных (от источников до витрин)
- Сквозные цепочки метрик и диагностики
- Интеграцию с действиями: уведомления, пересчёты, прогнозы
- Постоянный контроль качества данных и SLA
