МОДУЛЬ 5. Построение витрин данных для мониторинга Out-of-Stock: архитектура, логику, примеры, автоматизация
На этом этапе курса мы переходим к практической реализации всей предыдущей теории: построению аналитических витрин в хранилище данных (DWH). Именно витрины являются основой для работы BI-систем, алгоритмов тревог и аналитических панелей, которые бизнес использует ежедневно.
В этом модуле мы подробно разберем:
- какие витрины нужно строить для мониторинга Out-of-Stock;
- как их проектировать и наполнять;
- какие источники подключать и как синхронизировать данные;
- как заложить логику расчета всех нужных метрик;
- как автоматизировать процессы обновления и контроля качества.
Что такое витрина данных
Витрина данных (data mart) — это агрегированная таблица или набор таблиц, созданных в хранилище данных для поддержки конкретного аналитического сценария. В нашем случае — для анализа, предупреждения и управления Out-of-Stock (OOS).
Хорошо спроектированная витрина должна:
- быть легко читаемой и интерпретируемой пользователями BI;
- быть обновляемой и воспроизводимой ежедневно;
- иметь четкую логику расчета каждого показателя;
- опираться на качественные источники данных;
- быть масштабируемой.
Общая архитектура витрин по OOS
Основная витрина: dm_oos_metrics
Дополнительные витрины:
- dm_oos_patterns — повторяющиеся паттерны отсутствия
- dm_oos_root_causes — потенциальные причины OOS
- dm_oos_forecast_vs_actual — сравнение прогнозов и факта
- dm_oos_planogram_vs_sales — несоответствие планограмм
- dm_oos_replenishment_analysis — проблемы в пополнении
Источники данных для витрины OOS
Для полноценной витрины мы должны соединить минимум 6 источников:
- Продажи (POS): SKU, Store, Date, Qty, Time
- Остатки (stock): SKU, Store, Date, Stock_qty
- Прогноз (forecast): SKU, Store, Date, Forecast_qty
- Поставки (delivery): SKU, Store, Date, Delivered_qty
- Планограммы (planogram): SKU, Store, Facing, Shelf_space
- Справочники (SKU, Store): категории, бренды, KVI и т.д.
Методология загрузки:
- данные загружаются в staging-слой (raw данные)
- нормализуются и очищаются
- объединяются в модель данных на уровне SKU x Store x Date
Структура витрины dm_oos_metrics
|
Поле |
Описание |
|---|---|
|
SKU_ID |
Идентификатор товара |
|
STORE_ID |
Идентификатор магазина |
|
DATE |
Дата наблюдения |
|
SALES_QTY |
Продажи за день |
|
STOCK_QTY |
Остаток на складе |
|
IS_OOS |
Флаг OOS (0/1) |
|
OOS_DURATION |
Продолжительность отсутствия |
|
LOST_SALES_EST |
Оценка потерь в штуках |
|
LOST_REVENUE_EST |
Потери в деньгах |
|
FORECAST_QTY |
Прогноз спроса на дату |
|
DELIVERY_QTY |
Количество поставленного товара |
|
IS_KVI |
Флаг KVI-товара |
|
SHELF_SPACE |
Занимаемое полочное пространство |
|
CATEGORY |
Категория товара |
|
OOS_TYPE |
Тип отсутствия: shelf или store |
Логика расчета метрик
Флаг OOS:
CASE WHEN STOCK_QTY = 0 AND SALES_QTY = 0 AND FORECAST_QTY > 0 THEN 1 ELSE 0 END AS IS_OOS
Оценка потерь:
CASE WHEN IS_OOS = 1 THEN FORECAST_QTY - SALES_QTY ELSE 0 END AS LOST_SALES_EST
Потери в деньгах:
LOST_SALES_EST * AVERAGE_PRICE AS LOST_REVENUE_EST
Тип OOS:
CASE WHEN DELIVERY_QTY = 0 AND STOCK_QTY = 0 THEN 'store' WHEN DELIVERY_QTY > 0 AND STOCK_QTY = 0 THEN 'shelf' ELSE NULL END AS OOS_TYPE
Алгоритмы определения повторяющихся паттернов
Повторяющиеся шаблоны — мощный инструмент в BI. Мы ищем, например, такие паттерны:
- OOS возникает каждую субботу
- OOS появляется через 2 дня после поставки
- один и тот же SKU имеет OOS в одних и тех же магазинах
Пример SQL-алгоритма:
WITH base AS ( SELECT SKU_ID, STORE_ID, DATE, IS_OOS FROM dm_oos_metrics WHERE DATE BETWEEN CURRENT_DATE - INTERVAL '30 days' AND CURRENT_DATE ) SELECT SKU_ID, STORE_ID, COUNT(*) FILTER (WHERE EXTRACT(DOW FROM DATE) = 6 AND IS_OOS = 1) AS Saturday_OOS, COUNT(*) FILTER (WHERE EXTRACT(DAY FROM DATE) = 1 AND IS_OOS = 1) AS FirstDay_OOS FROM base GROUP BY SKU_ID, STORE_ID
Витрина причин Out-of-Stock: dm_oos_root_causes
Цель — дать техническую гипотезу: почему произошел OOS.
Поля:
- SKU_ID
- STORE_ID
- DATE
- REASON_CODE: 'phantom_stock', 'late_delivery', 'shelf_noncompliance', 'underforecast'
Алгоритм:
- phantom_stock: есть остаток, но продаж нет
- late_delivery: не было поставки > X дней
- underforecast: прогноз был занижен на Y%
BI-практика: как использовать витрину
BI-интерфейс получает данные из витрины. Примеры визуализации:
- Динамика доли OOS по дням
- ТОП товаров по потерям
- Геокарта по магазину и категории
- Распределение причин OOS
- Сравнение плана (прогноза) и факта
Автоматизация обновления витрин
- Все витрины должны обновляться по расписанию (обычно раз в день)
- Используются ETL-процессы на базе Airflow, SSIS, Talend, DataStage или Spark
- Внедряется SLA и система мониторинга (например, через Grafana + Prometheus)
- Обязательная валидация контрольных сумм и полноты данных
Работа с рисками
|
Риск |
Меры |
|---|---|
|
Потеря данных при загрузке |
Контроль record count, лог ошибок |
|
Противоречивые данные |
Использование мастер-справочников |
|
Задержка данных |
Резервный слот пересчета, SLA по источникам |
|
Расхождение цен и остатков |
Стандартизация источников, DQ-мониторинг |
|
Сложность поддержки |
Комментарии в SQL, документация витрин |
Построение витрин — это ключевая точка, где логика, данные и бизнес-вопросы сходятся. Именно здесь мы трансформируем "сырые" события в понятные и интерпретируемые метрики.
