Модуль 13. Аналитика розничного бизнеса (Retail)
Картина домена: из чего состоит «ритейл-сквозняк»
Слои данных и решений
Источники → DWH (RAW/CORE/MARTS) → семантика → дашборды/алерты → решения категорий/коммерции → эффект (GM%, OOS, LFL).
- POS: чеки/строки продаж, способы оплаты, скидки, кассы.
- ERP/Закупки/Цены: заказы, прайс-листы, себестоимости.
- CRM/Лояльность: карты, купоны, сегменты, RFM, кампании.
- Промо-план: типы промо, периоды, механики, бюджеты.
- Склады/Запасы (WMS/TMS): остатки, движения, OOS, OTIF поставщиков.
- Онлайн-канал: заказы, отмены, доставка, click-and-collect.
Ключевые роли/решения
- Категорийный менеджер: ассортимент, цены, промо, GM%.
- Коммерция/маркетинг: промо-календарь, купоны, KVI.
- Операции: наличие, OOS, пополнения, оборачиваемость.
- Финансы: методология GM/GM%, план-факт, возвраты/сторно.
Сущности и витрины для розницы (минимальный набор)
Факты
- fact_sales — строки чеков (grain: чек×SKU). Поля: date_id, store_id, sku_id, qty, price_list, discount_amt, price_net, revenue_net, is_promo, is_return_flag, receipt_id, channel_order, channel_fulfillment.
- fact_returns — строки возвратов (дата возврата, связка с исходной продажей).
- fact_inventory_snapshot — остатки на конец дня (store×sku×date).
- fact_price — история цен (store/zone×sku×valid_from/to, list_price, promo_price).
- fact_promo_effect — расчёты baseline/actual (по периодам промо).
Измерения
- dim_product (SCD2) — категория→подкатегория→бренд→SKU; атрибуты: вес/объём, KVI-флаг, сезонность.
- dim_store — регион→город→магазин; формат магазина.
- dim_calendar — календарные и фискальные уровни; флаги праздников/распродаж.
- dim_promo — промо-события: типы механик (-Х%/2-по-цене-1/бандл/купон/лояльность).
- dim_customer (SCD2) — сегменты CRM, RFM, household_id (если есть согласие).
Агрегаты/витрины
- mart_margin_day_sku_store — GM/GM% по дню.
- mart_promo_sku_week — uplift/инкрементальная маржа по SKU×неделе.
- mart_oos_day_sku_store — доля OOS (по правилам ниже).
- mart_lfl_month_store — LFL YoY для сопоставимых магазинов.
Историчность (важно)
— dim_product и dim_price — SCD2; fact_inventory_snapshot — снапшот по дням; промо-признаки — по периодам промо + факту применения скидки/купона.
Базовые метрики ритейла (формулы и ловушки)
GM и GM%
GM = Выручка (без НДС) − COGS − Логистика;
GM% = GM / Выручка.
Ловушки: возвраты (когда признаем), промо-бюджеты/купонные компенсации (куда относить), мультивалюта/НДС, перерасчёт «задним числом».
LFL (like-for-like) YoY
Правила: магазин открыт ≥T-12, не был на ремонте; сравнение «май к маю» одного календаря; исключить переезды/переформаты.
Ловушка: «последние 30 дней vs предыдущие 30» — не LFL.
OOS (out-of-stock)
Простая прокси: доля SKU-дней с stock_qty <= 0 при наличии спроса (или прогнозного спроса).
Уточнения: полочный OOS по POS (продаж нет при явной сезонной/рекламной нагрузке), лаги данных склада.
Промо-эффект (uplift) и инкрементальная маржа
Идея: сравнить факт продаж в промо с базовой линией (что было бы без промо).
uplift_qty = qty_actual − qty_baseline uplift_rev = rev_actual − rev_baseline incremental_margin = uplift_rev − uplift_qty * cogs − promo_cost
Ловушки: каннибализация соседних SKU/каналов, эффект «послепродажного падения», OOS в промо, сезонность, цена в промо как эндогенная переменная.
Эластичность цены
Определение: ε = %ΔQ / %ΔP.
Практично: лог-регрессия ln(Q) = β0 + β1 ln(P) + controls (сезон, промо, тренды, конкуренты); эластичность ≈ β1.
Ловушки: промо и цена «двигаются вместе», нужна инструменталка/контрольные переменные.
KVI (Key Value Items)
Небольшой список SKU, формирующих восприятие цен.
Правила: стабильные цены, мониторинг конкурентов, жёсткие NFR по наличию (OOS < 1–2%).
POS/CRM/Лояльность: как стыкуется
- POS → fact_sales с receipt_id и строками.
- Лояльность/карты → связка receipt_id → customer_id (аккуратно с согласием/PII).
- CRM-кампании/купоны → dim_promo + fact_redemption (погашения).
-
RFM:
- Recency — дней с последней покупки,
- Frequency — транзакций за период,
-
Monetary — сумма за период.
Сегменты храним в dim_customer (SCD2), пересчитываем ежемесячно.
Омниканал
- channel_order (web/app/store), channel_fulfillment (store/DC/courier; click&collect).
- Правило учёта выручки: по событию «отгрузка/передача покупателю», а не «заказ».
- Возвраты онлайн в магазин — корректно связываем (original_receipt_line_id) и не «ломаем» LFL офлайна.
Сезонность и праздники
- Календарь: государственные/религиозные/«чёрные» распродажи; флаги: is_holiday, is_black_friday, is_school_opening.
- Базовая линия: для промо-оценок и эластичностей учитываем сезон, тренд, день недели.
- Скользящие праздники: Пасха/Рамадан — отдельные флаги/смещения.
Возвраты/сторно: политика и влияние
- Дата признания: в периоде продажи (пересчёт прошлого) или в текущем (корректировка сейчас) — методологию фиксируем.
- Типы: дефект/ошибка кассира/отмена онлайн-заказа.
- Влияние на GM%: возврат → минус выручка и GM; возможные логистические затраты.
- Проверка: связи продажа↔возврат, чтобы не считать возврат как «отрицательную промо-продажу».
Практические SQL-фрагменты (проверки и расчёты)
GM% по неделям для категории (исключая возвраты)
SELECT cal.year, cal.week,
SUM(fs.revenue_net) AS revenue,
SUM(fs.cogs) AS cogs,
SUM(fs.logistics_cost) AS logistics,
(SUM(fs.revenue_net) - SUM(fs.cogs) - SUM(fs.logistics_cost))
/ NULLIF(SUM(fs.revenue_net),0) AS gm_pct
FROM fact_sales fs
JOIN dim_product p ON fs.sku_id = p.sku_id
JOIN dim_calendar cal ON fs.date_id = cal.date_id
WHERE p.category = 'Beverages'
AND fs.is_return_flag = 0
GROUP BY cal.year, cal.week;
LFL «май к маю» (сопоставимые магазины)
WITH comparable AS (
SELECT store_id
FROM dim_store
WHERE open_date <= DATE '2024-05-01'
AND (close_date IS NULL OR close_date > DATE '2025-05-31')
)
SELECT
SUM(CASE WHEN cal.year=2025 THEN fs.revenue_net ELSE 0 END) AS rev_2025,
SUM(CASE WHEN cal.year=2024 THEN fs.revenue_net ELSE 0 END) AS rev_2024,
(SUM(CASE WHEN cal.year=2025 THEN fs.revenue_net ELSE 0 END)
-SUM(CASE WHEN cal.year=2024 THEN fs.revenue_net ELSE 0 END))
/ NULLIF(SUM(CASE WHEN cal.year=2024 THEN fs.revenue_net ELSE 0 END),0) AS lfl_yoy
FROM fact_sales fs
JOIN dim_calendar cal ON fs.date_id=cal.date_id
WHERE cal.month=5
AND fs.store_id IN (SELECT store_id FROM comparable);
Простая OOS-метрика
SELECT cal.date, st.store_id, COUNT(*) FILTER (WHERE inv.stock_qty <= 0 AND dem.demand_qty > 0) * 1.0 / NULLIF(COUNT(*),0) AS oos_rate FROM fact_inventory_snapshot inv JOIN dim_calendar cal ON inv.date_id = cal.date_id JOIN dim_store st ON inv.store_id=st.store_id LEFT JOIN fact_demand dem ON dem.store_id=inv.store_id AND dem.sku_id=inv.sku_id AND dem.date_id=inv.date_id WHERE cal.date BETWEEN DATE '2025-05-01' AND DATE '2025-05-31' GROUP BY cal.date, st.store_id;
Промо-uplift (наивный baseline — среднее соседних недель)
WITH baseline AS (
SELECT sku_id, week,
AVG(revenue_net) OVER (PARTITION BY sku_id
ORDER BY week ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS rev_baseline
FROM mart_sales_week_sku
),
promo AS (
SELECT s.sku_id, s.week, s.revenue_net AS rev_actual, b.rev_baseline
FROM mart_sales_week_sku s
JOIN baseline b USING (sku_id, week)
JOIN dim_promo p ON p.sku_id=s.sku_id AND p.week=s.week
)
SELECT sku_id, week,
rev_actual, rev_baseline,
(rev_actual - rev_baseline) AS uplift_rev
FROM promo;
Заметка: для качественной оценки нужен контролируемый baseline (DiD/модели, см. §9).
Макет дашборда «Promo & GM%» (скелет + NFR)
Цели: видеть, где промо улучшает GM% и выручку без каннибализации и OOS.
Аудитория: категории/коммерция/финансы.
Зоны:
- KPI-плашки: GM%, Revenue, Promo Uplift (₽/pp), OOS rate, % промо-продаж.
- Тепловая карта: Категория×Канал — GM% и доля промо.
- Таблица SKU: GM%, Revenue, Promo Uplift, OOS, эластичность (если есть), флаги KVI.
- Временной график: неделя→GM% и доля промо (dual axis избегаем — лучше два графика, синхронизированные).
- Фильтры: период, канал заказа/исполнения, регион/магазин, категория/бренд, тип промо, KVI-флаг.
- DQ-виджет: Freshness, полнота промо-флагов, наличие цен.
NFR: p95 ≤ 5 сек при 12 мес данных; Freshness D-1 к 10:00; RLS по региону.
Служебное: в шапке — версия методологии метрик и дата свежести.
Как считать промо-эффект «правильно» (минимум теории)
Почему наивный baseline опасен: сезон, тренды, соседние промо, OOS и каннибализация искажают оценку.
База методов:
- Difference-in-Differences (DiD): сравнить динамику SKU/магазинов в промо vs похожих без промо (контроль), до/после.
- Regressions with controls: ln(Q) ~ ln(P) + promo + season + store FE + week FE; коэффициент promo ≈ uplift в лог-масштабе.
- Matched controls/Propensity score: подобрать контрольные SKU/магазины с похожим профилем.
- Uplift-модели: для клиентского уровня (кто купит из-за промо).
Практический минимум: DiD по однотипным магазинам + исключение OOS дней + флаги крупных праздников.
Риски и как их снимать
|
Риск |
Симптом |
Меры |
|---|---|---|
|
Каннибализация |
Рост SKU A, падение SKU B |
Смотрим категорию/бренд целиком; считаем «категорийный uplift» |
|
OOS в промо |
Всплеск спроса, пустые полки |
Включаем OOS в анализ; промо без обеспечения — к «красным» |
|
Неверная дата признания выручки |
Ломает тренды |
Политика «shipment/receipt», единые правила для онлайн/офлайн |
|
«Сезон съел эффект» |
До/после раздуто |
DiD/контроли календаря, исключение «аномальных недель» |
|
Несогласованный промо-флаг |
Спор, что считать промо |
Единый источник флага + fallback (скидка>Х% + календарь) |
|
Двойной учёт купонов |
И в скидке, и в бюджете |
Правила: купон — в promo_cost; скидка — в net price |
|
Возвраты ломают GM% |
«Провалы» после акций |
Политика возвратов; отдельный виджет «возвраты промо» |
|
Омниканал пересчитывается дважды |
Онлайн-заказ и офлайн-выдача сложены вместе |
Разделение канал заказа и канал исполнения |
Вопрос–ответ (FAQ)
Q: Чем «промо-продажа» отличается от «продажи со скидкой»?
A: Промо — событие с механикой и бюджетом; скидка — факт цены. Промо может не привести к скидке (купоны не погашены), а скидка может быть «операционной». Флаг промо = календарь + факт применения механики.
Q: Где считать GM% — в BI или в DWH?
A: Базовая формула и исключения — в витрине (DWH) для единообразия; в BI — лёгкие сценарии и разрезы.
Q: LFL ломается при закрытии магазина — что делать?
A: Исключить магазин из сопоставимой базы или удерживать LFL на «кластере» сопоставимых магазинов.
Q: Как учитывать возвраты онлайн, сделанные офлайн?
A: Привязать к исходной транзакции; в отчётах офлайна показывать как «возврат онлайн». Политика должна быть прозрачна.
Q: Как выбрать KVI?
A: Частота покупок, вклад в корзину, видимость, мониторинг конкурентов; стабильность цен и строгие NFR по OOS.
Q: Можно ли делать A/B на промо?
A: Да (разные механики/скидки/подбор SKU), но следите за интерференцией: покупатели видят обе витрины — лучше рандомизировать магазины или кластеры.
Практика модуля (что сдать)
A. Мини-витрина для категорийного менеджмента
- Факты: fact_sales, fact_inventory_snapshot, fact_price.
- Измерения: dim_product (SCD2), dim_store, dim_calendar, dim_promo.
- Витрины: mart_margin_day_sku_store, mart_promo_sku_week, mart_oos_day_sku_store.
- Метрики: GM/GM%, Revenue, OOS rate, Promo Uplift (rev & margin), % promo sales.
- DQ-правила: полнота продаж ≥ 99,5%; промо-флаг ≥ 98%; «цена нетто ≥ 0»; связка возвратов.
B. Макет дашборда «Promo & GM%»
- Секции и фильтры — как в §8; добавить паспорт дашборда (методология в шапке).
- Acceptance (минимум 10 AC): совпадение GM% по эталону (±0,2 п.п.), фильтры совместно, RLS, баннер «черновик» при Freshness < 98%, OOS-учёт в промо, перф p95 ≤ 5 сек.
C. «Быстрые победы» (quick wins) для пилота
- Нормализация промо-флага + fallback-правило.
- OOS-виджет и алерт на KVI при запасе < порога.
- ДиD-оценка для 3 промо-SKU категории А.
- Паспорт дашборда и словарь терминов (GM%, OOS, LFL, uplift).
Чек-листы готовности
Данные
- Промо-флаг и цены консистентны; price_net = list_price − discount.
- Возвраты связаны с продажами; методика признания зафиксирована.
- Остатки — снапшоты по дням; лаги источников известны.
- Календарь с праздниками и фискальными периодами.
Метрики
- GM/GM% формула и исключения утверждены (версия методологии).
- OOS-правило определено; KVI-флаг есть.
- LFL — база сопоставимых магазинов собрана.
Дашборд/NFR
- p95 ≤ 5 сек; Freshness ≥ 98% (D-1 к 10:00).
- RLS по региону/каналу; экспорт — только видимое.
- DQ-виджет/баннер/алерты настроены.
- Паспорт дашборда и ссылки на глоссарий.
Вы получили «скелет» ритейл-аналитики: какие витрины и таблицы нужны, как считать GM%, LFL, OOS и промо-uplift, где типичные ловушки и как их снимать, какой дашборд нужен категории здесь-и-сейчас, какие NFR задавать. Этот набор позволяет быстро войти в домен и говорить с бизнесом на одном языке, показывая измеримый эффект.
