Кейс — коммерческие инсайты по данным SKU онлайн-магазина (Bolt Market)

Условие

Дана city-level выгрузка по 4 онлайн-магазинам Bolt Market — таблица htdata с колонками:

• Time — день,
• Category level 0/1/2 — иерархия категорий («Fresh Food → 🧀 Cheese → Hard Cheese»),
• Unique SKUs Listed — число уникальных SKU в активном статусе и видимых в приложении,
• Unique SKUs Sold — число уникальных SKU, по которым была хотя бы 1 продажа,
• # of sold SKU items — суммарный объём проданных штук,
• Price before Discount (EUR) — выручка до скидок (нетто? брутто? — уточнить),
• Discount Value (EUR) — общая скидка на проданные SKU,
• Item COGS (net VAT) — себестоимость проданных SKU (нетто НДС),
• Waste, # of items — списания по сроку годности.

Задание: обеспечить Commercial и Supply Chain команды релевантными инсайтами из датасета. Дать рекомендации и гипотезы для дальнейшей валидации.

Это open-ended кейс: оценивают глубину аналитического мышления и умение превращать сырые цифры в action items.

Решение

Подход

Senior-уровень требует структурированного подхода. Каркас «инсайтов»:

1. EDA + sanity-checks (нет ли дыр в данных, выбросов).
2. KPI первого уровня для коммерции и supply chain.
3. Декомпозиция и сегменты: где «жжёт».
4. Гипотезы с измеримым ожиданием эффекта.

Шаг 1. Метрики для коммерции

Шаг 2. Метрики для Supply Chain

Шаг 3. Реализация EDA

import pandas as pd
import matplotlib.pyplot as plt
 
df = pd.read_excel("htdata.xlsx", parse_dates=["Time"])
 
# 1. Базовые KPI
df["revenue_net"]     = df["Price before Discount (EUR)"] - df["Discount Value (EUR)"]
df["gross_profit"]    = df["revenue_net"] - df["Item COGS (net VAT)"]
df["margin_pct"]      = df["gross_profit"] / df["revenue_net"]
df["sell_through"]    = df["Unique SKUs Sold"] / df["Unique SKUs Listed"]
df["velocity"]        = df["# of sold SKU items"] / df["Unique SKUs Sold"]
df["discount_share"]  = df["Discount Value (EUR)"] / df["Price before Discount (EUR)"]
df["waste_rate"]      = df["Waste, # of items"] / df["# of sold SKU items"]
 
# 2. По категориям level 1
agg = df.groupby("Category level 1").agg(
    revenue=("revenue_net", "sum"),
    gp=("gross_profit", "sum"),
    margin=("margin_pct", "mean"),
    waste_items=("Waste, # of items", "sum"),
    listed_avg=("Unique SKUs Listed", "mean"),
    sold_avg=("Unique SKUs Sold", "mean"),
).assign(margin_overall=lambda d: d["gp"] / d["revenue"])
 
print(agg.sort_values("revenue", ascending=False))

Шаг 4. Инсайты, которые ищем

1. Топ и хвост ассортимента (правило Парето). Какие 20% SKU дают 80% выручки? Какие SKU вообще не продаются (мертвый ассортимент)? Кандидаты на де-листинг или на промо.

sku_perf = df.groupby("Category level 2").agg(
    rev=("revenue_net", "sum"),
    sold=("# of sold SKU items", "sum"),
    listed=("Unique SKUs Listed", "mean"),
).sort_values("rev", ascending=False)
sku_perf["cum_share"] = sku_perf["rev"].cumsum() / sku_perf["rev"].sum()
print(sku_perf[sku_perf.cum_share <= 0.80])

2. Категории с высоким списанием. Свежие продукты (мясо, рыба, овощи) ожидаемо имеют waste. Но если waste rate в категории > 10% — это уже потеря маржи. Сопоставить с margin% и понять, окупает ли продажа списание.

3. Дисконт-зависимая выручка. Категории с discount_share > 15% — вопрос: эта скидка драйвит спрос или просто «съедает маржу»? Оценить эластичность через изменение velocity при росте discount_share (по дням / категориям).

4. Сезонность / day-of-week. По датам — есть ли провалы по дням недели или временные тренды? Можно ли сократить заказы поставщику в low-days?

df["dow"] = df["Time"].dt.day_name()
print(df.groupby("dow")[["revenue_net", "Waste, # of items"]].sum())

5. Out-of-stock / under-listing. Категории, где sell_through > 0.95 — почти весь ассортимент продаётся. Это либо отлично подобрана матрица, либо не хватает SKU (товары быстро улетают). Гипотеза: расширение ассортимента поднимет revenue.

6. Cannibalisation / комплементарные категории. Если по дням есть отрицательная корреляция между revenue двух категорий, возможно, одна каннибализирует другую. Например, готовая еда vs продукты для готовки.

Шаг 5. Рекомендации (формулировка)

Структура каждой рекомендации:

Что увидели в данных: Категория «Bakery → Gluten Free» имеет 1 SKU listed, 0 sold за весь период. waste_rate = 100%.

Гипотеза: Этот SKU не находит спрос либо из-за плохой видимости в приложении, либо из-за высокой цены. Альтернатива — спрос есть, но SKU-микс не угадан.

Что сделать: Снять SKU с матрицы или заменить на популярный альтернативный gluten-free продукт. Параллельно — A/B-тест размещения категории в приложении.

Метрика успеха: revenue в подкатегории > X EUR/день в течение 2 недель после запуска.

Эталонный отчёт — список из 4–6 таких рекомендаций, отсортированных по ожидаемому бизнес-эффекту.

Подводные камни

1. «Discount Value» = только user-discount. В легенде — «discounts made for users». Поставщические скидки и промо могут быть в другом месте. Нельзя считать revenue_net без уточнения.
2. Waste ≠ потеря выручки 1:1. Списанная единица товара не была бы продана с вероятностью 100%. Реальная потеря — Waste * P(sale | not listed) * AvgPrice. Без модели спроса это оценка сверху.
3. Items vs SKUs. Unique SKUs Sold (сколько разных) ≠ # of sold items (сколько штук). Ошибка в формуле = инсайт неверный.
4. Симпсон по городам. Если данные city-level (4 магазина), агрегат «весь датасет» может скрывать сильно разное поведение по городам. Всегда проверять разбивку.
5. Свежесть данных. Период (Jul–Sep 2023) — летний. Зимние категории (горячая выпечка, согревающие напитки) выглядят пусто. Не делать стратегические выводы по короткому сезонному окну.
6. Listed = 0 — это либо stock-out, либо категории не было в магазине. По одному значению не отличить — нужен контекст от Supply Chain.
7. Outliers в velocity. Один день с резкой акцией дорогого SKU всё ломает. Использовать медиану / робастные метрики при сравнении.
8. Единицы измерения. Цены в EUR, COGS — net VAT. Если сравнивать с Price (брутто или нетто? — в легенде неоднозначно), маржа может быть искажена.

Эталонный ответ (структура)

1. Шапка: 5–7 ключевых KPI за период, разбивка по категориям level 1.
2. Парето-анализ ассортимента: топ SKU, мёртвые SKU, рекомендации по матрице.
3. Waste hot-spots: топ категорий по waste_rate, оценка потерь, рекомендации (заказ меньшими партиями, динамические скидки на товар у срока годности).
4. Скидочная политика: где скидка реально драйвит спрос, где «жжёт маржу».
5. Out-of-stock/under-listing: категории с sell_through > 95% — кандидаты на расширение.
6. Day-of-week / сезонность: оптимизация заказов поставщикам.
7. Гипотезы для дальнейшей валидации (минимум 3–4) с метрикой и ожидаемым эффектом.

Senior-разница от middle: не отчёт «вот цифры», а «вот цифры → вот что они значат → вот что делать → вот как измерить эффект».