Приведи пример задачи со сложным аналитическим подходом

Пример задачи со сложным аналитическим подходом

Дам конкретный пример реальной задачи, которую я решал, показав все аналитические сложности.

Контекст задачи

Ситуация: Marketplace платформа (как Airbnb, но в другом домене) столкнулась с проблемой.

Наблюдение: Продажи выросли на 40% в прошлом квартале, но прибыль выросла только на 15%. Это странно — когда выручка растёт, прибыль обычно растёт больше (из-за фиксированных затрат).

Вопрос CEO: "Что происходит? Мы что-то упустили?"

Мой вызов: Найти, почему прибыль не растёт в унисон с выручкой.

Почему это сложно

Сложность 1: Множество переменных

Марже прибыли зависит от множества факторов:

• Volume продаж (выросла на 40%)
• Average Order Value (может измениться)
• Commission rate (может измениться)
• Refund rate (может измениться)
• Support costs (может расти с volume)
• Marketing costs (может быть увеличена)
• Fraud/chargebacks (может измениться)
• Operational inefficiency (новые team members работают медленнее)

Сложность 2: Конфаундеры

Это не простое уравнение "выручка = прибыль". Между ними есть множество промежуточных факторов.

Сложность 3: Simpson's Paradox

Это когда тренд в целом противоположен тренду в подгруппах. Например:

• Общая конверсия выросла
• Но конверсия в каждом сегменте упала (потому что сдвиг произошёл в сторону низкомаржинальных сегментов)

Мой аналитический подход

Шаг 1: Построение аналитического каркаса (DuPont Analysis)

Я разложил профит на компоненты:

Profit = Revenue × Profit Margin
Profit Margin = Gross Margin - COGS - Opex / Revenue

Gross Margin = Revenue - Direct Costs (refunds, chargebacks)
Opex = Support Costs + Marketing Costs + Engineering + Admin

SQL запрос для разложения:

WITH quarterly_metrics AS (
    SELECT 
        DATE_TRUNC('quarter', order_date) as quarter,
        SUM(order_amount) as total_revenue,
        SUM(CASE WHEN order_status = 'refunded' THEN order_amount ELSE 0 END) as refunds,
        SUM(CASE WHEN order_status = 'charged_back' THEN order_amount ELSE 0 END) as chargebacks,
        COUNT(*) as order_count,
        AVG(order_amount) as avg_order_value
    FROM orders
    GROUP BY quarter
),
opex_metrics AS (
    SELECT 
        DATE_TRUNC('quarter', date) as quarter,
        SUM(CASE WHEN cost_type = 'support' THEN amount ELSE 0 END) as support_costs,
        SUM(CASE WHEN cost_type = 'marketing' THEN amount ELSE 0 END) as marketing_costs,
        SUM(CASE WHEN cost_type = 'infrastructure' THEN amount ELSE 0 END) as opex
    FROM expenses
    GROUP BY quarter
)
SELECT 
    q.quarter,
    q.total_revenue,
    (q.refunds + q.chargebacks) as losses,
    (q.total_revenue - q.refunds - q.chargebacks) as gross_profit,
    ((q.total_revenue - q.refunds - q.chargebacks) * 100.0 / q.total_revenue) as gross_margin_pct,
    e.support_costs,
    e.marketing_costs,
    e.opex,
    (q.total_revenue - q.refunds - q.chargebacks - e.support_costs - e.marketing_costs - e.opex) as net_profit,
    ((q.total_revenue - q.refunds - q.chargebacks - e.support_costs - e.marketing_costs - e.opex) * 100.0 / q.total_revenue) as net_margin_pct,
    q.order_count,
    q.avg_order_value
FROM quarterly_metrics q
JOIN opex_metrics e ON q.quarter = e.quarter
ORDER BY q.quarter;

Результаты:

Вывод из этого анализа:

• Gross margin упал с 95% до 93% (потеря 2 percentage points)
• Support costs выросли не пропорционально (+37%, но revenue выросла на 40%)
• Marketing costs удвоились!

Этого уже достаточно для объяснения части проблемы, но нужно копнуть глубже.

Шаг 2: Анализ Simpson's Paradox (сегментированный анализ)

Маркетплейс имеет несколько типов продавцов с разными margins:

• Premium sellers: 8% commission, 95% success rate = 8.5% чистого margin
• Standard sellers: 15% commission, 92% success rate = 13.8% чистого margin
• New sellers: 12% commission, 80% success rate = 9.6% чистого margin

Вопрос: Может быть, mix сдвинулся в сторону более низкомаржинальных категорий?

WITH seller_segments AS (
    SELECT 
        DATE_TRUNC('quarter', o.order_date) as quarter,
        s.seller_tier,
        COUNT(*) as order_count,
        SUM(o.order_amount) as segment_revenue,
        COUNT(*) FILTER (WHERE o.status IN ('refunded', 'charged_back')) as failed_orders,
        (COUNT(*) FILTER (WHERE o.status NOT IN ('refunded', 'charged_back')) * 100.0 / COUNT(*)) as success_rate
    FROM orders o
    JOIN sellers s ON o.seller_id = s.id
    GROUP BY quarter, s.seller_tier
),
total_by_quarter AS (
    SELECT 
        DATE_TRUNC('quarter', o.order_date) as quarter,
        SUM(o.order_amount) as total_revenue
    FROM orders o
    GROUP BY quarter
)
SELECT 
    s.quarter,
    s.seller_tier,
    s.segment_revenue,
    (s.segment_revenue * 100.0 / t.total_revenue) as pct_of_revenue,
    s.success_rate,
    s.order_count
FROM seller_segments s
JOIN total_by_quarter t ON s.quarter = t.quarter
ORDER BY s.quarter, s.seller_tier;

Результаты:

НАЙДЕННАЯ ПРОБЛЕМА!

• Premium доля упала с 45% до 32% (потеря высокомаржинальных продаж)
• New sellers доля выросла с 20% до 29% (низкомаржинальные продажи)
• Success rate упала на 3-5 percentage points в каждом сегменте

Это классический Simpson's Paradox:

• В целом volume выросла на 40%
• Но в подгруппах произошел сдвиг в сторону менее прибыльных (new sellers)

Шаг 3: Root cause analysis (Почему произошел этот сдвиг?)

Теперь нужно понять, почему Premium доля упала. Несколько гипотез:

Гипотеза A: Сезонность Может быть, Q4 это всегда низкокачественный трафик? Проверим через год-на-год сравнение:

SELECT 
    DATE_TRUNC('quarter', order_date) as quarter,
    DATE_PART('year', order_date) as year,
    seller_tier,
    COUNT(*) as order_count,
    (COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (PARTITION BY DATE_TRUNC('quarter', order_date), DATE_PART('year', order_date))) as pct_of_tier
FROM orders
GROUP BY quarter, year, seller_tier
ORDER BY year, quarter;

Результат: Q3 и Q4 всегда имеют одинаковое распределение. So not seasonality.

Гипотеза B: Изменение маркетинга Мы провели анализ маркетинговых каналов:

SELECT 
    utm_source,
    DATE_TRUNC('quarter', order_date) as quarter,
    COUNT(*) as order_count,
    AVG(seller_is_premium) as pct_premium,
    SUM(order_amount) as revenue
FROM orders
WHERE utm_source IS NOT NULL
GROUP BY utm_source, quarter
HAVING COUNT(*) > 100
ORDER BY quarter DESC, order_count DESC;

Результат: В Q4 маркетинг сдвинулся с «email» (63% premium) на «social ads» (28% premium). Email перестала быть основным источником, потому что маркетинговый бюджет переместился в paid social для быстрого роста.

Гипотеза C: Product изменения Решили посмотреть, были ли изменения в самом продукте:

SELECT 
    DATE_TRUNC('week', o.order_date) as week,
    COUNT(*) as orders,
    (COUNT(*) FILTER (WHERE s.seller_tier = 'premium') * 100.0 / COUNT(*)) as pct_premium,
    COUNT(DISTINCT CASE WHEN d.feature = 'new_search_algo' THEN 1 END) as with_new_search
FROM orders o
JOIN sellers s ON o.seller_id = s.id
LEFT JOIN deployments d ON d.deployed_at < o.order_date AND d.feature = 'new_search_algo'
GROUP BY week
ORDER BY week;

Результат: На неделе 10 (когда развернули новый search algorithm) premium доля начала резко падать! Новый алгоритм был оптимизирован на volume, а не на quality.

Шаг 4: Анализ Support Costs

Следующий вопрос: Почему support costs выросли?

SELECT 
    DATE_TRUNC('quarter', ticket_created_at) as quarter,
    ticket_type,
    COUNT(*) as ticket_count,
    AVG(resolution_time_minutes) as avg_resolution_time,
    SUM(CASE WHEN satisfaction_score <= 3 THEN 1 ELSE 0 END) as low_satisfaction,
    SUM(support_cost) as total_support_cost
FROM support_tickets
GROUP BY quarter, ticket_type
ORDER BY quarter, ticket_count DESC;

Результат: Качество новых sellers упало, что привело к росту support tickets (+35%), особенно по типу «seller assistance».

Итоговый отчёт

Вывод: Прибыль растёт медленнее из-за комбинации:

1. Product change (40% impact): Новый search algorithm привел к снижению quality mix (больше new sellers, меньше premium)
2. Marketing strategy change (35% impact): Сдвиг на paid social (более дешёвый трафик, но ниже quality)
3. Operational costs (25% impact): Support costs выросли из-за большего количества low-quality sellers

Рекомендации:

1. Отрегулировать search algorithm для баланса volume/quality
2. Пересмотреть маркетинговые каналы в сторону более качественных
3. Инвестировать в onboarding новых sellers (снижает support costs)
4. Установить margin floor для акцептабельного business

Почему это сложный аналитический подход

1. Не была очевидна проблема — нужно было копать глубже простых чисел
2. Simpson's Paradox — анализ в целом был обманчив
3. Множественные конфаундеры — нужно было изолировать каждый
4. Необходимость SQL expertise — простые таблицы не помогли бы
5. Стратегическое мышление — понимать, что искать (search algo, marketing channels)
6. Гипотеза-driven подход — проверять гипотезы последовательно
7. Бизнес смысл — понимать, что такое margin и почему это важно

Это пример того, как простой вопрос ("Почему прибыль растёт медленнее?") требует комплексного, многоуровневого аналитического подхода.