15 апреля 2026 г.

SQL-запросы для маркетинга

Схема данных

Типичная для маркетинга:

  • users (user_id, channel, registered_at, first_purchase_at)
  • marketing_spend (date, channel, spend)
  • orders (order_id, user_id, amount, created_at)
  • events (user_id, event, source, created_at)

1. CAC по каналам

WITH spend AS (
    SELECT channel, SUM(spend) AS total_spend
    FROM marketing_spend
    WHERE DATE >= '2026-04-01' AND DATE < '2026-05-01'
    GROUP BY channel
),
customers AS (
    SELECT channel, COUNT(DISTINCT user_id) AS new_paying
    FROM users
    WHERE first_purchase_at >= '2026-04-01'
      AND first_purchase_at < '2026-05-01'
    GROUP BY channel
)
SELECT s.channel, s.total_spend, c.new_paying,
    s.total_spend / NULLIF(c.new_paying, 0) AS cac
FROM spend s
LEFT JOIN customers c USING (channel)
ORDER BY cac;

2. ROAS (Return on Ad Spend)

WITH spend AS (
    SELECT channel, SUM(spend) AS spend FROM marketing_spend
    WHERE DATE >= '2026-04-01' GROUP BY channel
),
revenue AS (
    SELECT u.channel, SUM(o.amount) AS revenue
    FROM orders o
    JOIN users u USING (user_id)
    WHERE o.created_at >= '2026-04-01' GROUP BY u.channel
)
SELECT s.channel, s.spend, r.revenue,
    ROUND(r.revenue / s.spend, 2) AS roas
FROM spend s JOIN revenue r USING (channel)
ORDER BY roas DESC;

ROAS > 3 — канал работает. ROAS < 1 — убыточен.

3. LTV/CAC по каналам

WITH ltv_90 AS (
    SELECT u.channel,
        AVG(total_rev) AS avg_ltv_90d
    FROM (
        SELECT user_id, SUM(amount) AS total_rev
        FROM orders
        WHERE created_at <= registered_at + INTERVAL '90 day'
        GROUP BY user_id
    ) t
    JOIN users u USING (user_id)
    GROUP BY u.channel
),
cac AS (
    SELECT channel, SUM(spend) / NULLIF(COUNT(DISTINCT user_id), 0) AS cac
    FROM marketing_spend s
    JOIN users u USING (channel)
    WHERE u.first_purchase_at >= s.DATE
      AND u.first_purchase_at < s.DATE + INTERVAL '30 day'
    GROUP BY channel
)
SELECT l.channel, l.avg_ltv_90d, c.cac,
    l.avg_ltv_90d / c.cac AS ltv_cac_ratio
FROM ltv_90 l JOIN cac c USING (channel)
ORDER BY ltv_cac_ratio DESC;

4. Воронка регистрации по каналам

SELECT channel,
    COUNT(DISTINCT user_id) FILTER (WHERE event = 'landing_visit') AS visits,
    COUNT(DISTINCT user_id) FILTER (WHERE event = 'form_open') AS form_opens,
    COUNT(DISTINCT user_id) FILTER (WHERE event = 'signup') AS signups,
    ROUND(
        COUNT(DISTINCT user_id) FILTER (WHERE event = 'signup') * 100.0 /
        NULLIF(COUNT(DISTINCT user_id) FILTER (WHERE event = 'landing_visit'), 0),
    2) AS conv_pct
FROM events e
JOIN users u USING (user_id)
GROUP BY channel;

Прокачать тему на реальных задачах удобно в боте @kariernik_bot — база вопросов собрана с собеседований в Яндексе, Авито, Ozon, Тинькофф.

5. Last-click атрибуция

WITH last_touch AS (
    SELECT user_id, source,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
    FROM events
    WHERE event = 'landing_visit'
      AND created_at < (
          SELECT first_purchase_at FROM users u WHERE u.user_id = events.user_id
      )
)
SELECT source, COUNT(*) AS conversions
FROM last_touch
WHERE rn = 1
GROUP BY source
ORDER BY conversions DESC;

Подробнее про атрибуцию.

6. First-click атрибуция

WITH first_touch AS (
    SELECT user_id, source,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at) AS rn
    FROM events WHERE event = 'landing_visit'
)
SELECT source, COUNT(*) AS conversions
FROM first_touch WHERE rn = 1
GROUP BY source;

7. Retention по когортам привлечения

WITH cohort AS (
    SELECT user_id, channel,
        DATE_TRUNC('month', registered_at)::DATE AS cohort_month
    FROM users
),
active_d30 AS (
    SELECT DISTINCT u.user_id, u.channel, u.cohort_month
    FROM cohort u
    WHERE EXISTS (
        SELECT 1 FROM events
        WHERE user_id = u.user_id
          AND created_at::DATE = (u.cohort_month + 30)
    )
)
SELECT c.channel, c.cohort_month,
    COUNT(*) AS cohort_size,
    COUNT(a.user_id) * 1.0 / COUNT(*) AS retention_d30
FROM cohort c
LEFT JOIN active_d30 a USING (user_id)
GROUP BY c.channel, c.cohort_month
ORDER BY c.cohort_month, retention_d30 DESC;

8. CTR рассылок

SELECT campaign_id,
    COUNT(*) FILTER (WHERE event = 'email_sent') AS sent,
    COUNT(*) FILTER (WHERE event = 'email_opened') AS opened,
    COUNT(*) FILTER (WHERE event = 'email_clicked') AS clicked,
    ROUND(
        COUNT(*) FILTER (WHERE event = 'email_opened') * 100.0 /
        NULLIF(COUNT(*) FILTER (WHERE event = 'email_sent'), 0),
    2) AS open_rate,
    ROUND(
        COUNT(*) FILTER (WHERE event = 'email_clicked') * 100.0 /
        NULLIF(COUNT(*) FILTER (WHERE event = 'email_opened'), 0),
    2) AS ctr
FROM email_events
GROUP BY campaign_id;

9. Повторные покупки по каналам

SELECT u.channel,
    COUNT(DISTINCT u.user_id) AS total_customers,
    COUNT(DISTINCT o.user_id) FILTER (WHERE order_num >= 2) AS repeat_customers,
    ROUND(
        COUNT(DISTINCT o.user_id) FILTER (WHERE order_num >= 2) * 100.0 /
        NULLIF(COUNT(DISTINCT u.user_id), 0),
    2) AS repeat_rate
FROM users u
LEFT JOIN (
    SELECT user_id, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at) AS order_num
    FROM orders WHERE status = 'paid'
) o USING (user_id)
GROUP BY u.channel;

10. ROI кампании

WITH campaign_revenue AS (
    SELECT c.campaign_id, SUM(o.amount) AS revenue
    FROM campaigns c
    JOIN users u ON u.attribution_campaign = c.campaign_id
    JOIN orders o USING (user_id)
    WHERE o.status = 'paid'
    GROUP BY c.campaign_id
)
SELECT c.name, c.budget, r.revenue,
    ROUND((r.revenue - c.budget) * 100.0 / c.budget, 2) AS roi_pct
FROM campaigns c
JOIN campaign_revenue r USING (campaign_id)
ORDER BY roi_pct DESC;

На собесе такие штуки часто спрашивают. Быстрый способ довести до автоматизма — тренажёр в Telegram с задачами из реальных интервью.

11. Дневная динамика spend vs installs

SELECT s.DATE, s.spend, i.installs,
    s.spend / NULLIF(i.installs, 0) AS cpi
FROM marketing_spend s
LEFT JOIN (
    SELECT DATE(registered_at) AS DATE, COUNT(*) AS installs
    FROM users GROUP BY 1
) i USING (DATE)
WHERE s.channel = 'mobile_ads'
ORDER BY s.DATE;

12. Топ-5 кампаний по новым пользователям

SELECT campaign_id, COUNT(DISTINCT user_id) AS new_users
FROM users
WHERE registered_at >= CURRENT_DATE - INTERVAL '30 day'
  AND attribution_campaign IS NOT NULL
GROUP BY campaign_id
ORDER BY new_users DESC
LIMIT 5;

Как использовать

Запросы — шаблоны для Data Mart в маркетинговых дашбордах.

Читайте также

FAQ

Last-click или first-click?

Last-click — для оптимизации performance. First-click — для понимания, что двигает user к продукту. Оба ограничены — для сложных флоу нужна multi-touch atribution.

Как считать CAC с учётом зарплат маркетинга?

Включайте в total spend: media + agency + зарплаты + подписки на инструменты.

ROAS vs ROI?

ROAS = revenue / spend (без учёта costs). ROI = profit / spend (с учётом всех costs).

Как считать с time-lag?

Пользователь увидел рекламу в январе, купил в марте. Атрибутируйте по first-touch или ставьте cutoff (60 дней).

Тренироваться в Telegram