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

Q: Где практиковать?

[SQL-тренажёр](/sql-trenazher), open-data sets, репозитории Mode Analytics SQL challenges.

Зачем SQL на собесе маркетинг-аналитика

SQL — главный навык маркетинг-аналитика. Через SQL считаются: воронки конверсии, LTV / CAC по каналам, retention cohorts, attribution. На собесе маркетинг-аналитика SQL — обязательный live-coding раздел.

Слабый ответ — «select count(*)». Сильный — window functions, cohort analysis, attribution-логика в SQL, оптимизация на больших объёмах.

Funnel-анализ в SQL

Funnel — последовательность шагов user. Where dropoff happens?

Пример: signup → activation → first_purchase

WITH events AS (
  SELECT user_id, event_name, event_time,
    ROW_NUMBER() OVER (PARTITION BY user_id, event_name ORDER BY event_time) AS rn
  FROM events
  WHERE event_name IN ('signup', 'activation', 'first_purchase')
    AND rn = 1
)
SELECT
  COUNT(*) FILTER (WHERE event_name = 'signup') AS step_1,
  COUNT(*) FILTER (WHERE event_name = 'activation') AS step_2,
  COUNT(*) FILTER (WHERE event_name = 'first_purchase') AS step_3
FROM events;

Conversion rates: step_2 / step_1, step_3 / step_2.

Подробнее — funnel-анализ простыми словами.

Cohort retention

Cohort = users, signed up in same period. Сравниваем retention по cohorts.

WITH cohorts AS (
  SELECT user_id, DATE_TRUNC('month', MIN(event_time)) AS cohort_month
  FROM events WHERE event_name = 'signup'
  GROUP BY user_id
),
activity AS (
  SELECT c.cohort_month, c.user_id,
    DATE_TRUNC('month', e.event_time) AS activity_month
  FROM cohorts c JOIN events e USING (user_id)
)
SELECT cohort_month, activity_month,
  EXTRACT(YEAR FROM AGE(activity_month, cohort_month)) * 12 +
    EXTRACT(MONTH FROM AGE(activity_month, cohort_month)) AS months_since_signup,
  COUNT(DISTINCT user_id) AS active_users
FROM activity
GROUP BY 1, 2;

Подробнее — cohort-анализ простыми словами.

Attribution в SQL

Last click — простой стандарт:

SELECT user_id, channel
FROM (
  SELECT user_id, channel,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY touch_time DESC) AS rn
  FROM touchpoints
  WHERE touch_time <= conversion_time
)
WHERE rn = 1;

First click: ORDER BY touch_time ASC.

Linear: равная атрибуция всем touches:

SELECT t.user_id, t.channel,
  1.0 / COUNT(*) OVER (PARTITION BY t.user_id) AS attribution_share
FROM touchpoints t
WHERE t.touch_time <= conversion_time;

Time decay: больше веса последним. Через EXP(-LAMBDA * (conv_time - touch_time)).

Подробнее — attribution-модели в аналитике.

LTV в SQL

Avg revenue × retention rate × time horizon.

WITH user_revenue AS (
  SELECT user_id, SUM(amount) AS total_revenue,
    DATE_TRUNC('month', MIN(order_date)) AS cohort_month,
    COUNT(*) AS orders_count
  FROM orders
  GROUP BY user_id
)
SELECT cohort_month,
  AVG(total_revenue) AS avg_ltv,
  AVG(orders_count) AS avg_orders
FROM user_revenue
GROUP BY cohort_month;

Подробнее — LTV и CAC на собесе.

ROAS / CAC по каналам

SELECT channel,
  SUM(spend) AS total_spend,
  SUM(revenue) AS total_revenue,
  SUM(revenue) / NULLIF(SUM(spend), 0) AS roas,
  SUM(spend) / NULLIF(COUNT(DISTINCT user_id), 0) AS cac
FROM channel_performance
GROUP BY channel;

Optimization

Маркетинг-аналитика обычно tools на DWH (ClickHouse / Snowflake / BigQuery), но запросы могут быть тяжёлыми.

Pre-aggregate: daily / weekly metric tables (dbt mart)
Partitioning: по дате (date_id) для filter pruning
Materialized views: для dashboard queries

Подробнее — materialized views в SQL.

Типичные вопросы

«Посчитай D7 retention для cohort января»

CTE: user_id + cohort_month. Join с activity events. Days_since_signup = AGE(activity_time, signup_time). Count distinct user_id с days_since_signup = 7.

«ROAS по каналам — какие каналы выключать?»

ROAS < 1 (revenue ниже spend) — кандидат на pause. Но: attribution может underestimate (last-click). Cohort LTV > spend? Если да — оставлять.

«Cohort retention падает. Что делать?»

Drilldown: по каким cohorts падает? По acquisition channel?
Activation rate — изменился?
Sample qualitative interviews с lost users.
A/B test improvements.

«Last click везде. Это правильно?»

Нет. Underestimates upper-funnel channels (brand, content). Hybrid: last-click для optimization decisions + multi-touch / data-driven для strategic budget allocation.

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

Без timezone handling. Cross-region cohorts ломаются.
COUNT(*) вместо COUNT(DISTINCT user_id). Дубликаты затыкают retention.
Без partition pruning. Запросы на 10B rows tail.
Last click без multi-touch sanity check.
Cohort retention без period normalization. Сравниваем yesterday cohort и last year cohort — нелогично.

FAQ

PostHog / GA — нужны ли, если есть SQL?

Дополняют. PostHog / GA — событийный data → SQL для глубинной аналитики.

dbt для marketing?

Да. Marketing mart как набор dbt моделей: channel_performance, user_attribution, cohort_retention. Стандарт в modern stack.

Python вместо SQL?

Python для advanced (ML attribution, MMM). SQL для большинства day-to-day задач.

Сколько SQL-фокус на собесе?

Live coding 60-90 мин. Несколько задач: funnel, cohort, attribution, optimization.

Где практиковать?

SQL-тренажёр, open-data sets, репозитории Mode Analytics SQL challenges.

Смотрите также

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