Кейс: Анализ снижения продаж

Решение

Пошаговый план анализа снижения продаж на 15%

Снижение продаж на 15% может быть вызвано множеством факторов. Проведу систематический анализ для выявления корневых причин.

---

1. Требуемые данные

Основные таблицы:

• orders — id, date, amount, status, customer_id, product_id
• products — id, name, category, price, supplier_id
• customers — id, region, acquisition_date, segment
• order_items — order_id, product_id, quantity, price
• traffic — date, source, sessions, orders
• inventory — product_id, stock_quantity, date
• marketing — date, channel, budget, impressions, clicks

Дополнительные источники:

• Внешние события (конкурентная активность, маркетинг)
• Сезонные индексы
• Рыночные данные

---

2. Основные гипотезы для проверки

Гипотеза 1: Снижение трафика и конверсии

WITH quarterly_metrics AS (
  SELECT 
    EXTRACT(QUARTER FROM date) AS quarter,
    EXTRACT(YEAR FROM date) AS year,
    COUNT(DISTINCT customer_id) AS unique_customers,
    COUNT(*) AS total_orders,
    SUM(amount) AS total_sales,
    ROUND(COUNT(*) / COUNT(DISTINCT customer_id)::NUMERIC, 2) AS orders_per_customer,
    ROUND(SUM(amount) / COUNT(*)::NUMERIC, 2) AS avg_order_value
  FROM orders
  WHERE status IN ('completed', 'shipped')
  GROUP BY EXTRACT(YEAR FROM date), EXTRACT(QUARTER FROM date)
)
SELECT 
  year || '-Q' || quarter AS period,
  unique_customers,
  total_orders,
  total_sales,
  orders_per_customer,
  avg_order_value,
  ROUND((total_sales - LAG(total_sales) OVER (ORDER BY year, quarter)) / LAG(total_sales) OVER (ORDER BY year, quarter) * 100, 2) AS sales_change_pct
FROM quarterly_metrics
ORDER BY year, quarter DESC;

Проверяем: Упал ли трафик? Снизилась ли конверсия? Упала ли среднего стоимость заказа?

Гипотеза 2: Проблемы с продуктами (убыль, качество)

WITH product_performance AS (
  SELECT 
    p.id,
    p.name,
    p.category,
    COUNT(oi.order_id) AS orders,
    SUM(oi.quantity) AS units_sold,
    SUM(oi.price * oi.quantity) AS revenue,
    ROUND(SUM(oi.price * oi.quantity) / COUNT(oi.order_id)::NUMERIC, 2) AS avg_order_value,
    ROUND(SUM(CASE WHEN o.status = 'returned' THEN 1 ELSE 0 END) / COUNT(*)::NUMERIC * 100, 2) AS return_rate,
    ROUND(AVG(CASE WHEN r.rating IS NOT NULL THEN r.rating END), 2) AS avg_rating
  FROM products p
  LEFT JOIN order_items oi ON p.id = oi.product_id
  LEFT JOIN orders o ON oi.order_id = o.id
  LEFT JOIN reviews r ON p.id = r.product_id
  WHERE EXTRACT(QUARTER FROM o.date) = (EXTRACT(QUARTER FROM CURRENT_DATE) - 1)
    AND EXTRACT(YEAR FROM o.date) = EXTRACT(YEAR FROM CURRENT_DATE)
  GROUP BY p.id, p.name, p.category
  ORDER BY revenue DESC
)
SELECT * FROM product_performance
WHERE return_rate > 10 OR avg_rating < 4 OR orders < 5;

Проверяем: Какие товары упали в продажах? Выросла ли доля возвратов? Снизилась ли оценка?

Гипотеза 3: Региональное падение

SELECT 
  c.region,
  EXTRACT(QUARTER FROM o.date) AS quarter,
  COUNT(DISTINCT o.customer_id) AS unique_customers,
  COUNT(*) AS orders,
  SUM(o.amount) AS revenue,
  ROUND(SUM(o.amount) / COUNT(*)::NUMERIC, 2) AS avg_order_value,
  ROUND((SUM(o.amount) - LAG(SUM(o.amount)) OVER (PARTITION BY c.region ORDER BY EXTRACT(QUARTER FROM o.date))) / LAG(SUM(o.amount)) OVER (PARTITION BY c.region ORDER BY EXTRACT(QUARTER FROM o.date)) * 100, 2) AS sales_change_pct
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
WHERE EXTRACT(YEAR FROM o.date) = EXTRACT(YEAR FROM CURRENT_DATE)
GROUP BY c.region, EXTRACT(QUARTER FROM o.date)
ORDER BY c.region, quarter DESC;

Проверяем: В каких регионах упали продажи? Есть ли географическая локализация проблемы?

Гипотеза 4: Канальное падение (маркетинг)

SELECT 
  m.channel,
  EXTRACT(QUARTER FROM o.date) AS quarter,
  COUNT(DISTINCT CASE WHEN o.status IN ('completed', 'shipped') THEN o.customer_id END) AS conversions,
  SUM(CASE WHEN o.status IN ('completed', 'shipped') THEN o.amount ELSE 0 END) AS revenue,
  m.budget,
  ROUND(SUM(CASE WHEN o.status IN ('completed', 'shipped') THEN o.amount ELSE 0 END) / m.budget::NUMERIC, 2) AS roi,
  ROUND((SUM(CASE WHEN o.status IN ('completed', 'shipped') THEN o.amount ELSE 0 END) - LAG(SUM(CASE WHEN o.status IN ('completed', 'shipped') THEN o.amount ELSE 0 END)) OVER (PARTITION BY m.channel ORDER BY EXTRACT(QUARTER FROM o.date))) / LAG(SUM(CASE WHEN o.status IN ('completed', 'shipped') THEN o.amount ELSE 0 END)) OVER (PARTITION BY m.channel ORDER BY EXTRACT(QUARTER FROM o.date)) * 100, 2) AS channel_change_pct
FROM marketing m
LEFT JOIN orders o ON EXTRACT(QUARTER FROM o.date) = EXTRACT(QUARTER FROM CURRENT_DATE) - 1
  AND EXTRACT(YEAR FROM o.date) = EXTRACT(YEAR FROM CURRENT_DATE)
GROUP BY m.channel, EXTRACT(QUARTER FROM o.date), m.budget
ORDER BY channel, quarter DESC;

Проверяем: Упали ли продажи в определённых маркетинговых каналах? Снизилась ли эффективность рекламы?

Гипотеза 5: Проблемы с воронкой конверсии

WITH conversion_funnel AS (
  SELECT 
    DATE_TRUNC('week', t.date) AS week,
    COUNT(DISTINCT t.session_id) AS sessions,
    COUNT(DISTINCT CASE WHEN ov.user_id IS NOT NULL THEN t.session_id END) AS viewed_product,
    COUNT(DISTINCT CASE WHEN oc.user_id IS NOT NULL THEN t.session_id END) AS added_to_cart,
    COUNT(DISTINCT CASE WHEN o.id IS NOT NULL THEN t.session_id END) AS purchased
  FROM traffic t
  LEFT JOIN order_views ov ON t.session_id = ov.session_id
  LEFT JOIN order_carts oc ON t.session_id = oc.session_id
  LEFT JOIN orders o ON t.session_id = o.session_id
  GROUP BY DATE_TRUNC('week', t.date)
)
SELECT 
  week,
  sessions,
  viewed_product,
  ROUND(viewed_product / NULLIF(sessions, 0)::NUMERIC * 100, 2) AS view_rate,
  added_to_cart,
  ROUND(added_to_cart / NULLIF(viewed_product, 0)::NUMERIC * 100, 2) AS cart_rate,
  purchased,
  ROUND(purchased / NULLIF(added_to_cart, 0)::NUMERIC * 100, 2) AS checkout_rate,
  ROUND(purchased / NULLIF(sessions, 0)::NUMERIC * 100, 2) AS overall_conversion
FROM conversion_funnel
ORDER BY week DESC;

Проверяем: На каком этапе воронки произошло падение? (просмотры → корзина → оплата)

---

3. Метрики для анализа

---

4. Визуализации

1. Трендовый график (Line Chart)

• Ось X: недели/месяцы
• Ось Y: выручка
• Показать точку начала падения

2. Разбор по категориям (Bar Chart)

• Категории товаров
• Изменение выручки по категориям
• Выделить проблемные категории

3. Воронка конверсии (Funnel Chart)

• Сессии → Просмотры → Корзина → Оплата
• Сравнить текущий и прошлый период
• Выявить узкие места

4. Тепловая карта (Heatmap)

• Оси: регионы × каналы маркетинга
• Цвет: изменение выручки в %

5. Скважность по когортам (Cohort Analysis)

• Когорты по дате первой покупки
• Матрица: сколько повторно купили в следующем периоде

---

5. Валидация найденных причин

Метод 1: A/B тестирование

-- Если гипотеза: "проблема в юзер-интерфейсе"
SELECT 
  test_group,
  COUNT(*) AS users,
  SUM(CASE WHEN converted THEN 1 ELSE 0 END) AS conversions,
  ROUND(SUM(CASE WHEN converted THEN 1 ELSE 0 END) / COUNT(*)::NUMERIC * 100, 2) AS conversion_rate,
  -- Chi-square test статистика
  NULL AS p_value
FROM ab_test
GROUP BY test_group;

Метод 2: Корреляционный анализ

-- Проверка корреляции между бюджетом маркетинга и продажами
SELECT 
  CORR(m.budget, o.amount) AS correlation,
  COUNT(*) AS sample_size
FROM marketing m
LEFT JOIN orders o ON DATE(o.date) = DATE(m.date)
WHERE EXTRACT(QUARTER FROM o.date) = (EXTRACT(QUARTER FROM CURRENT_DATE) - 1);

Метод 3: Сегментирование по влиянию

-- Какие факторы вносят наибольший вклад в падение
SELECT 
  'Traffic' AS factor,
  SUM(CASE WHEN traffic_declined THEN 1 ELSE 0 END) AS affected_customers,
  ROUND(100.0 * SUM(CASE WHEN traffic_declined THEN 1 ELSE 0 END) / COUNT(*), 2) AS impact_pct
FROM (
  SELECT 
    customer_id,
    CASE WHEN sessions_q2 < sessions_q1 * 0.85 THEN TRUE ELSE FALSE END AS traffic_declined
  FROM customer_traffic_comparison
) sub
UNION ALL
SELECT 
  'AOV',
  SUM(CASE WHEN aov_declined THEN 1 ELSE 0 END),
  ROUND(100.0 * SUM(CASE WHEN aov_declined THEN 1 ELSE 0 END) / COUNT(*), 2)
FROM (SELECT CASE WHEN aov_q2 < aov_q1 * 0.85 THEN TRUE ELSE FALSE END AS aov_declined FROM ...)
UNION ALL
SELECT 'Retention', ...

---

6. Действие после анализа

Если причина: Снижение трафика

• ✓ Проверить SEO рейтинги
• ✓ Проанализировать PPC бюджеты
• ✓ Оценить влияние конкурентов

Если причина: Низкая конверсия

• ✓ UX audit сайта
• ✓ Анализ воронки конверсии
• ✓ А/Б тест улучшений

Если причина: Качество товара

• ✓ Анализ отзывов и возвратов
• ✓ Опросить клиентов
• ✓ Обсудить с поставщиками

Если причина: Конкуренция

• ✓ Бенчмарк конкурентов
• ✓ Анализ их маркетинга
• ✓ Пересмотр ценовой политики

---

Итоговый чеклист анализа

• Разбиение падения по видам (трафик vs конверсия vs AOV)
• Анализ по продуктам, регионам, каналам
• Исследование воронки конверсии
• Проверка качества данных и возможных багов
• Анализ внешних факторов
• Построение гипотез с приоритизацией
• Валидация через А/Б тесты
• Документирование выводов и рекомендаций

Временной фрейм: 2-3 дня на диагностику + 1-2 недели на тесты улучшений.