Постановка задачи
Специалист по рекламе ведет кампании на разных площадках, привлекая трафик на сайт компании, оказывающей некие услуги, или продающей товары, или предоставляющей платную онлайн-функциональность. Как правило, посетителю для начала работы необходимо сначала зарегистрироваться на сайте, иногда подтвердить контактную информацию и затем приступить к покупкам. Таким образом, воронка продаж выглядит так: посещение — регистрация — активация (то есть первый платеж). Маркетолог для каждой кампании хочет знать:
- объем трафика;
- стоимость трафика;
- количество микроконверсий (то есть промежуточных шагов к главной цели);
- количество целевых действий;
- эффективность привлечения.
Кроме того, ему интересна динамика во времени, то есть нужен столбец с датой. Состав колонок витрины такой:
- date — дата;
- channel — источник и канал трафика (например, “yandex / cpc”);
- campaign — имя кампании (обычно заданное в метке utm_campaign);
- impressions — количество показов объявления;
- ctr — Click Through Rate, показатель кликабельности;
- clicks — количество кликов по объявлению;
- cost — расходы на кампанию;
- cpc — Cost Per Click, стоимость клика;
- cr — Conversion Rate, коэффициент конверсии из клика в регистрацию;
- regs — количество регистраций;
- cpr — Cost Per Registration, стоимость регистрации;
- acts — количество активаций;
- cpa — Cost Per Activation, стоимость активации.
То есть имеем воронку от показов до первого платежа, а также конверсию между шагами и показатели стоимости, так? Не совсем.
Дело в первой колонке — date. Витрина начинается с метрик кампании, значит, речь идет о дате показов, кликов и расходов. Но пользователь может совершить регистрацию далеко не в тот же день, как кликнул по объявлению, а активацию — еще позже. Покажу два способа построения витрины для учета этой «нестыковки».
Склейка по кампании и дате
Первый вариант — принять допущение, что большинство целевых действий совершается в день клика либо что среднее количество конверсий не меняется изо дня в день. Тогда мы просто делаем LEFT JOIN всех данных по кампании и дате.
При этом придется обойти подводный камень: может случиться, что в один день определенная кампания не крутилась, но зато зарегистрировался пользователь, который зашел с нее на сайт несколько дней назад. Такая регистрация не попадет в выборку, ведь мы используем LEFT JOIN к данным кампании.
Поэтому используем FULL OUTER JOIN, а затем выберем не-NULL значение кампании.
WITH cost AS
(
SELECT date,
CONCAT(utm_source, ' / ', utm_medium) AS channel,
utm_campaign AS campaign,
impressions, clicks, cost
FROM costs -- данные о кампаниях
),
reg AS
(
SELECT DATE(date_registration) AS date,
CONCAT(utm_source, " / ", utm_medium) AS channel,
utm_campaign AS campaign,
COUNT(user_id) AS regs
FROM registrations
WHERE utm_medium = 'cpc' -- только кампании с оплатой за клик
GROUP BY ALL
),
act AS
(
SELECT date, channel, campaign,
COUNT(user_id) AS acts -- количество активаций
FROM
(
SELECT MIN(date_paid) AS date, -- вычисляем дату активации
CONCAT(utm_source, " / ", utm_medium) AS channel,
utm_campaign AS campaign,
user_id
FROM payments -- все платежи пользователя
WHERE utm_medium = 'cpc' -- только кампании с оплатой за клик
GROUP BY ALL
)
GROUP BY ALL
)
SELECT COALESCE(c.date, r.date, a.date) AS date,
COALESCE(c.channel, r.channel, a.channel) AS channel,
COALESCE(c.campaign, r.campaign, a.campaign) AS campaign,
impressions, clicks, cost,
regs,
acts
FROM cost AS c
FULL OUTER JOIN reg AS r ON r.date = c.date
AND r.channel = c.channel
AND r.campaign = c.campaign
FULL OUTER JOIN act AS a ON a.date = c.date
AND a.channel = c.channel
AND a.campaign = c.campaign
Соединение FULL OUTER JOIN решит нашу проблему, правда? Нет. В этом примере если не будет данных в таблице cost, то регистрации подтянутся нормально, а активации — только отдельной строкой. Поэтому мы сделаем по-другому: выберем все кампании из cost и присвоим им дни через генератор дат, а затем присоединим к ним всё остальное.
SELECT COALESCE(c.date, r.date, a.date) AS date,
COALESCE(c.channel, r.channel, a.channel) AS channel,
COALESCE(c.campaign, r.campaign, a.campaign) AS campaign,
impressions,
clicks / impressions AS ctr,
clicks,
cost / clicks AS cpc,
cost,
regs / clicks AS cr,
regs,
cost / regs AS cpr,
acts,
cost / acts AS cpa
FROM
(
SELECT arrayJoin(arrayMap(x -> toDate(x), -- генерируем даты (ClickHouse)
range(toInt32(toStartOfDay(toDate('2025-01-01'))), -- дата начала изучаемого периода
toInt32(now() + toIntervalDay(1)), 24 * 3600))) AS date,
channel, campaign
FROM
(
SELECT DISTINCT channel, campaign
FROM cost
)
) AS dc
LEFT JOIN cost AS c ON c.date = dc.date
AND c.channel = dc.channel
AND c.campaign = dc.campaign
LEFT JOIN reg AS r ON r.date = dc.date
AND r.channel = dc.channel
AND r.campaign = dc.campaign
LEFT JOIN act AS a ON a.date = dc.date
AND a.channel = dc.channel
AND a.campaign = dc.campaign
WHERE c.date IS NOT NULL -- оставляем только строки со значимыми данными
OR r.date IS NOT NULL
OR a.date IS NOT NULL
Этот подход годится, если длительность сделки невелика, а кампании не останавливаются. Он позволяет увидеть текущий, дневной снимок ситуации с рекламой и оперативно реагировать на изменения в показателях. Если же мы хотим более точной, более «правдивой» оценки эффективности кампаний, лучше применять другой подход.
Склейка по идентификатору пользователя
Для построения полноценной воронки необходимо, чтобы каждый новый ее шаг привязывался к предыдущему. Можно получить отчет по воронке, убрав из витрины дату и присоединив к имеющимся кампаниям количество действий, которые к ним относятся. Но это получится сводный отчет, не отражающий динамики кампаний, а она может сильно меняться.
В любом случае отчет по воронке будет когортным, а когорты будут строиться либо на основе только кампании, либо по кампании и дате клика. Разберем второе.
Теперь для нас недопустимо приравнивать дату регистрации к дате клика. Мы хотим знать, сколько принесла конверсий, в том числе отложенных, каждая кампания за каждый день. Для этого нам придется использовать данные инструмента веб-аналитики, например Google Analytics. К данным о кампаниях мы сначала присоединим данные о посещениях сайта в тот день, считая клик = посещение страницы. А уже к посещениям присоединим по идентификатору пользователя (точнее, его веб-клиента) регистрации. А к регистрациям — активации. Дата будет браться только из первого шага.
SELECT c.date AS date,
CONCAT(utm_source, ' / ', utm_medium) AS channel,
utm_campaign AS campaign,
SUM(c.impressions) AS impressions,
SUM(c.clicks) / SUM(c.impressions) AS ctr,
SUM(c.clicks) AS clicks,
SUM(c.cost) / SUM(c.clicks) AS cpc,
SUM(c.cost) AS cost,
COUNT(DISTINCT r.user_id) / SUM(c.clicks) AS cr,
COUNT(DISTINCT r.user_id) AS regs,
SUM(c.cost) / COUNT(DISTINCT r.user_id) AS cpr,
COUNT(DISTINCT p.user_id) AS acts,
SUM(c.cost) / COUNT(DISTINCT p.user_id) AS cpa
FROM costs c -- данные о кампаниях
LEFT JOIN
(
SELECT DISTINCT date,
client_id, -- идентификатор клиента
CONCAT(utm_source, ' / ', utm_medium) AS channel,
utm_campaign AS campaign,
FROM hits -- данные о хитах из системы веб-аналитики
) w ON w.date = c.date
AND w.channel = c.channel
AND w.campaign = c.campaign
LEFT JOIN
(
SELECT client_id, -- идентификатор клиента при регистрации
user_id -- идентификатор пользователя в приложении
FROM registrations
) AS r ON r.client_id = w.client_id
LEFT JOIN
(
SELECT DISTINCT user_id
FROM payments -- платежи
) AS p ON p.user_id = r.user_id
GROUP BY date, channel, campaign
Такой отчет дает более точные данные об эффективности каждой кампании, но в силу своей когортности требует постоянного переизучения данных за прошлые дни.
Заключение
Мы рассмотрели два подхода к построению отчета об эффективности рекламных кампаний.
Соединение по кампании и дате действия — годится, если можно пренебречь разницей между датой клика и датой целевого действия. Хорош для оценки оперативной динамики ситуации, так как данные в отчете не изменяются задним числом.
Соединение по идентификатору клиента — подходит, если средний срок сделки сильно больше одного дня. Количество целевых действий может меняться «в прошлом», поэтому следует изучать и прошедшие периоды в пределах некоторого окна.
