SQL для маркетинговой аналитики

Зачем это знать

Marketing аналитик живёт в SQL. Attribution, channel performance, CAC / ROAS per source. На собесах в performance marketing teams (Yandex Direct, Ozon Ads, Тинькофф) — SQL вопросов много marketing-specific.

Ключевые таблицы

-- ad_spend: расходы
(DATE, channel, campaign, spend)

-- signups: регистрации
(user_id, signup_date, source_channel)

-- orders: покупки
(user_id, order_date, amount)

-- sessions: посещения
(session_id, user_id, DATE, source, utm_source, utm_campaign)

CAC по каналам

WITH spend AS (
    SELECT channel, SUM(spend) AS total_spend
    FROM ad_spend
    WHERE DATE BETWEEN '2026-04-01' AND '2026-04-30'
    GROUP BY channel
),
signups AS (
    SELECT source_channel AS channel, COUNT(*) AS new_users
    FROM signups
    WHERE signup_date BETWEEN '2026-04-01' AND '2026-04-30'
    GROUP BY 1
)
SELECT
    s.channel,
    s.total_spend,
    si.new_users,
    s.total_spend / si.new_users AS cac
FROM spend s
JOIN signups si ON s.channel = si.channel
ORDER BY cac;

LTV по каналам

WITH cohort AS (
    SELECT user_id, source_channel, signup_date
    FROM signups
    WHERE signup_date BETWEEN '2025-04-01' AND '2025-06-30'
),
user_revenue AS (
    SELECT c.user_id, c.source_channel,
           COALESCE(SUM(o.amount), 0) AS lifetime_revenue
    FROM cohort c
    LEFT JOIN orders o ON o.user_id = c.user_id
        AND o.order_date <= c.signup_date + INTERVAL '365 days'
    GROUP BY c.user_id, c.source_channel
)
SELECT source_channel,
       AVG(lifetime_revenue) AS avg_ltv_365d,
       COUNT(*) AS cohort_size
FROM user_revenue
GROUP BY source_channel
ORDER BY avg_ltv_365d DESC;

ROAS (Return on Ad Spend)

WITH spend AS (...),
revenue AS (
    SELECT s.source_channel AS channel, SUM(o.amount) AS attributed_revenue
    FROM signups s
    JOIN orders o USING (user_id)
    WHERE o.order_date BETWEEN '2026-04-01' AND '2026-04-30'
    GROUP BY 1
)
SELECT
    sp.channel,
    sp.total_spend,
    r.attributed_revenue,
    r.attributed_revenue / sp.total_spend AS roas
FROM spend sp
JOIN revenue r ON sp.channel = r.channel
ORDER BY roas DESC;

ROAS 1.0 — break-even. 3.0 — good.

Attribution

Last-click (default)

Credit goes к last channel before conversion:

WITH last_touch AS (
    SELECT
        user_id,
        order_id,
        FIRST_VALUE(utm_source) OVER (
            PARTITION BY order_id ORDER BY session_time DESC
        ) AS attributed_source
    FROM sessions s
    JOIN orders o ON o.user_id = s.user_id AND s.DATE <= o.order_date
)
SELECT attributed_source, COUNT(DISTINCT order_id) AS conversions
FROM last_touch
GROUP BY 1;

First-click

Credit к first channel:

FIRST_VALUE(utm_source) OVER (PARTITION BY user_id ORDER BY session_time ASC)

Linear

Equal credit ко всем touchpoints:

-- Simplified
SELECT utm_source, COUNT(*) * 1.0 / touchpoints_count AS fractional_credit
FROM touchpoints
GROUP BY utm_source;

Position-based

40% first, 40% last, 20% middle.

Funnel per channel

SELECT
    source_channel,
    COUNT(DISTINCT s.user_id) AS visitors,
    COUNT(DISTINCT signed.user_id) AS signups,
    COUNT(DISTINCT paid.user_id) AS payers,
    COUNT(DISTINCT signed.user_id) * 100.0 / COUNT(DISTINCT s.user_id) AS visit_to_signup,
    COUNT(DISTINCT paid.user_id) * 100.0 / COUNT(DISTINCT signed.user_id) AS signup_to_paid
FROM sessions s
LEFT JOIN signups signed USING (user_id)
LEFT JOIN payments paid USING (user_id)
WHERE s.DATE BETWEEN '2026-04-01' AND '2026-04-30'
GROUP BY 1;

Cohort по cohort_month × channel

SELECT
    DATE_TRUNC('month', signup_date) AS cohort,
    source_channel,
    COUNT(*) AS new_users,
    AVG(CASE WHEN purchased_within_30d THEN 1 ELSE 0 END) AS conversion_30d
FROM users_with_flags
GROUP BY 1, 2
ORDER BY 1 DESC, 2;

Multi-channel journey

WITH journey AS (
    SELECT user_id,
           STRING_AGG(DISTINCT utm_source, ' → ' ORDER BY session_time) AS path
    FROM sessions
    WHERE user_id IN (SELECT user_id FROM orders)
    GROUP BY user_id
)
SELECT path, COUNT(*) AS users
FROM journey
GROUP BY path
ORDER BY users DESC LIMIT 20;

Top journey paths.

Incrementality

Hardest: «насколько conversions would happen without this channel».

Holdout testing:

• Random 10% users — no ads shown
• Compare conversion — incremental effect

SQL joins test / holdout marks.

On собесе

«CAC vs channel» — группировка spend / signups.

«Attribution модели — разница» — last-click vs multi-touch.

«LTV cohort» — cohort query с JOIN purchases.

«ROAS» — attributed revenue / spend.

Частые ошибки

Double counting

User через 2 channels — credit к both? Defined by attribution model.

Ignoring time

Conversion через 90 days — attribute recent or old touch?

Organic included

«Organic» — not paid. Different metrics.

Wrong period

Align spend period с conversion period carefully.

Сложности

Cross-device

User мобильный → desktop. Attribution challenges.

View-through

Impressions without click. Valuable?

Brand vs performance

Branded search — already interested. Don't over-credit.

Dashboard для marketing

• Spend vs revenue per channel
• CAC trends over time
• ROAS leaders / laggards
• Funnel per channel
• Cohort LTV
• Incrementality test results

Связанные темы

• Как посчитать CAC в SQL
• Как посчитать LTV в SQL
• Как посчитать CTR в SQL
• Attribution модели
• Attribution простыми словами

FAQ

Best attribution модель?

Depends. Multi-touch (data-driven) complex но nuanced. Last-click simple.

How long attribution window?

Typical 7-30 days. Varies by product.

В cookies ограничениях 2026?

Server-side tracking growing. Probabilistic models improving.

---

Тренируйте SQL — откройте тренажёр с 1500+ вопросами для собесов.