Мультиканальная атрибуция: first/last/linear touch

Условие задания

**Контекст:** Интернет-магазин использует мультиканальный маркетинг (Google Ads, Facebook, email, SEO, direct). Каждая покупка проходит через цепочку касаний (touchpoints). Нужно распределить выручку между каналами по трём моделям атрибуции: first touch (100% первому каналу), last touch (100% последнему), linear (поровну между всеми).

**Данные:**

[см. код в задании]

**Задание:**
1. Для каждой конверсии найдите все touchpoints за 30 дней до конверсии
2. Определите first touch channel, last touch channel и все промежуточные каналы
3. Распределите revenue: first touch = 100% первому, last touch = 100% последнему, linear = revenue / count(touchpoints) каждому
4. Агрегируйте по каналам: суммарная выручка по каждой модели атрибуции
5. Рассчитайте ROAS (Return on Ad Spend) если известна стоимость касания

Пример данных

Структура для ориентира — реальные значения из эталонного решения.

CREATE TABLE touchpoints (
    id SERIAL PRIMARY KEY,
    user_id INT NOT NULL,
    session_id VARCHAR(50) NOT NULL,
    channel VARCHAR(50) NOT NULL,           -- 'google_ads', 'facebook', 'email', 'seo', 'direct'
    campaign VARCHAR(200),
    touched_at TIMESTAMP NOT NULL
);

CREATE TABLE conversions (
    id SERIAL PRIMARY KEY,
    user_id INT NOT NULL,
    order_id INT NOT NULL,
    revenue NUMERIC(12,2) NOT NULL,
    converted_at TIMESTAMP NOT NULL
);

Темы

атрибуция FIRST_VALUE LAST_VALUE воронка revenue split

Подсказки

• FIRST_VALUE(channel) OVER (PARTITION BY conversion_id ORDER BY touched_at) — первый канал
• LAST_VALUE с правильным frame: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
• Linear: revenue / COUNT(*) OVER (PARTITION BY conversion_id) — доля каждого касания

Все тестовые задания →

Частые вопросы

Какой уровень знаний нужен для задачи "Мультиканальная атрибуция: first/last/linear touch"?

Это задание для уровня Senior. Senior-уровень — глубокое понимание темы, опыт решения нестандартных задач, обсуждение trade-off на собеседовании.

На каких собеседованиях встречается такая задача?

Подобные задания в категории «SQL» регулярно дают на собеседованиях аналитика данных в Яндекс, Сбер, Ozon, Авито, Тинькофф, Wildberries, T-Bank, X5, ВТБ и других крупных IT-компаниях. Тематика: атрибуция, FIRST_VALUE, LAST_VALUE, воронка, revenue split.

Сколько времени даётся на решение?

На реальном собеседовании на подобную задачу отводится 30-60 минут с обсуждением подходов, оптимизаций и trade-off. Для тренировки рекомендуем сначала решить самостоятельно, потом сверить с эталонным решением и подсказками.

Где ещё потренироваться по теме «SQL»?

На zasqlpython.ru есть 425 SQL задач в песочнице с автопроверкой кода, конспекты SQL для аналитика, AI мок-собеседование с разбором ваших ответов.

← Все задания