SQL для маркетингового аналитика: задачи скрининга

Маркетинговый аналитик работает с данными о привлечении, конверсии и удержании клиентов. SQL-задачи для этой роли специфичны: упор на атрибуцию, когортный анализ и расчёт маркетинговых метрик.

Специфика маркетинговой аналитики

Маркетинговый аналитик должен уметь:

• Считать ROI и ROAS рекламных кампаний
• Строить воронки привлечения по каналам
• Анализировать когорты по источнику трафика
• Рассчитывать CAC (стоимость привлечения клиента)
• Работать с UTM-параметрами и сессионными данными

Задача 1: Эффективность рекламных каналов

-- Таблицы:
-- acquisitions(user_id, channel, campaign, acquired_at, acquisition_cost)
-- orders(id, user_id, amount, created_at)

-- Задача: рассчитайте ROI по каналам привлечения
-- ROI = (выручка - затраты) / затраты × 100%

WITH channel_stats AS (
    SELECT
        a.channel,
        COUNT(DISTINCT a.user_id)           AS acquired_users,
        SUM(a.acquisition_cost)             AS total_cost,
        COALESCE(SUM(o.amount), 0)          AS total_revenue
    FROM acquisitions a
    LEFT JOIN orders o ON o.user_id = a.user_id
                      AND o.created_at >= a.acquired_at
    WHERE a.acquired_at >= '2026-01-01'
    GROUP BY a.channel
)
SELECT
    channel,
    acquired_users,
    total_cost,
    total_revenue,
    ROUND(total_revenue / NULLIF(acquired_users, 0), 0)         AS avg_ltv,
    ROUND(total_cost    / NULLIF(acquired_users, 0), 0)         AS cac,
    ROUND((total_revenue - total_cost) / NULLIF(total_cost, 0) * 100, 1) AS roi_pct
FROM channel_stats
ORDER BY roi_pct DESC;

Что проверяем: понимание маркетинговых метрик, LEFT JOIN для учёта каналов без выручки, защита от деления на ноль через NULLIF.

Задача 2: Когортный анализ по каналам

-- Задача: retention по каналу привлечения за первые 3 месяца

WITH cohorts AS (
    SELECT
        a.user_id,
        a.channel,
        DATE_TRUNC('month', a.acquired_at) AS cohort_month
    FROM acquisitions a
),
activity AS (
    SELECT DISTINCT
        user_id,
        DATE_TRUNC('month', created_at) AS active_month
    FROM orders
)
SELECT
    c.channel,
    c.cohort_month,
    COUNT(DISTINCT c.user_id)                                           AS cohort_size,
    COUNT(DISTINCT CASE WHEN a.active_month = c.cohort_month + INTERVAL '1 month'
                        THEN a.user_id END)                             AS m1_retained,
    COUNT(DISTINCT CASE WHEN a.active_month = c.cohort_month + INTERVAL '2 months'
                        THEN a.user_id END)                             AS m2_retained,
    ROUND(100.0 * COUNT(DISTINCT CASE WHEN a.active_month = c.cohort_month + '1 month'
                                      THEN a.user_id END)
               / COUNT(DISTINCT c.user_id), 1)                         AS m1_retention_pct
FROM cohorts c
LEFT JOIN activity a ON a.user_id = c.user_id
GROUP BY c.channel, c.cohort_month
ORDER BY c.cohort_month, c.channel;

Задача 3: Атрибуция по последнему касанию

-- Таблица: touchpoints(user_id, channel, touched_at)
-- Задача: last-click атрибуция — какой канал был последним перед покупкой?

WITH last_touch AS (
    SELECT DISTINCT ON (o.id)
        o.id                    AS order_id,
        o.amount,
        t.channel               AS attributed_channel
    FROM orders o
    JOIN touchpoints t ON t.user_id = o.user_id
                      AND t.touched_at <= o.created_at
    ORDER BY o.id, t.touched_at DESC
)
SELECT
    attributed_channel,
    COUNT(*)        AS attributed_orders,
    SUM(amount)     AS attributed_revenue,
    ROUND(AVG(amount), 0) AS avg_order_value
FROM last_touch
GROUP BY attributed_channel
ORDER BY attributed_revenue DESC;

Вопрос для обсуждения: «Какие ограничения у last-click атрибуции? Какие альтернативы знаете?» — позволяет оценить широту мышления кандидата.

Задача 4: Анализ кампаний с UTM-параметрами

-- Таблица: sessions(id, user_id, utm_source, utm_medium, utm_campaign, started_at)
-- Задача: найдите кампании с наибольшим числом уникальных пользователей
-- и конверсией в регистрацию

WITH session_stats AS (
    SELECT
        utm_source,
        utm_medium,
        utm_campaign,
        COUNT(DISTINCT user_id)     AS unique_visitors,
        COUNT(DISTINCT CASE WHEN u.registered_at IS NOT NULL
                            THEN s.user_id END) AS registered
    FROM sessions s
    LEFT JOIN users u ON u.id = s.user_id
                     AND u.registered_at >= s.started_at
                     AND u.registered_at <= s.started_at + INTERVAL '1 day'
    WHERE utm_campaign IS NOT NULL
    GROUP BY utm_source, utm_medium, utm_campaign
)
SELECT
    utm_source,
    utm_medium,
    utm_campaign,
    unique_visitors,
    registered,
    ROUND(100.0 * registered / NULLIF(unique_visitors, 0), 1) AS reg_conversion_pct
FROM session_stats
WHERE unique_visitors >= 100  -- отсекаем кампании с маленькой выборкой
ORDER BY reg_conversion_pct DESC
LIMIT 20;

Теоретические вопросы для маркетингового аналитика

«Объясните разницу между CAC, CPL и CPA»:

• CAC (Customer Acquisition Cost) — стоимость привлечения одного клиента
• CPL (Cost per Lead) — стоимость одного лида
• CPA (Cost per Action) — стоимость целевого действия

«Как бы вы посчитали LTV через SQL?» — ищем понимание LTV как суммы выручки за период или дисконтированного потока.

«Что такое payback period и как его посчитать?» — период возврата инвестиций в привлечение клиента.

Специфические знания для маркетинговых аналитиков

Проверьте понимание этих концепций:

• Разница между атрибуцией first-click, last-click и linear
• Что такое view-through attribution
• Проблема кросс-девайсного отслеживания
• Как правильно считать конверсию при мультисессионных визитах

Маркетинговый аналитик с глубоким SQL и пониманием атрибуции — ценный специалист. SQLlab.ru поможет кандидатам подготовиться к техническому скринингу.