Базовая задача интернет маркетолога заключается в умении сопоставить данные по рекламе с данными о ее эффективности. Например, сопоставить расходы по Яндекс.Директ с данными по продажам из Google Analytics.
Так как при работе с большими данными приходится проводить анализ в СУБД, то без знаний SQL не обойтись.
Представим, что в качестве данных из рекламной системы Яндекс.Директа у нас имеется следующая таблица с полями:
- date — дата показа рекламы
- campaign_id — идентификатор рекламной кампании
- campaign_name — название кампании
- impressions — количество показов
- clicks — количество кликов
- cost — расход
И данные из Google Analytics с полями:
- ga_date — дата визита
- ga_campaign — содержание UTM_campaign объявления
- ga_source — источник
- ga_medium — канал
- goal_1_completions — достижения ключевой (нужной для отчета) цели
На первом шаге нужно определить “ключ”, при помощи которого мы будем сопоставлять данные в двух таблицах.
“Ключом” объединения данных нам будут служить поля с датой и идентификатором рекламной кампании.
SQL-запрос будет показан на примере “синтетических” данных.
Сам запрос выглядит следующим образом:
SELECT *
FROM (
SELECT
date
, campaign_id
, campaign_name
, SUM(impressions) AS impressions
, SUM(clicks) AS clicks
, SUM(cost) AS cost
FROM yandex_direct
GROUP BY
date
, campaign_id
, campaign_name)
LEFT JOIN
(SELECT
ga_date AS date
, CASE
WHEN regexp_contains(ga_source, '(?i)yandex|mytarget') THEN ifnull(regexp_extract(ga_campaign, 'w*\\|?(\\d+)\\|?'), ga_source)
ELSE ga_source END AS campaign_id
, SUM(goal_1_completions) AS goal_1_completions
FROM analytics
GROUP BY
date
, campaign_id
)
USING (date, campaign_id)
Давайте попробуем упростить запрос для лучшего понимания.
SELECT * FROM (А) LEFT JOIN (B) USING (date, campaign_id)
Конструкцию, что вы видите выше объединяет между собой при помощи метода LEFT JOIN два таблицы (А) и (B) в одну при помощи столбцов date, campaign_id, которые есть в обоих таблицах за счет кляузы USING (date, campaign_id).
При этом LEFT JOIN берет все данные из таблицы (А) и подтягивает к ним данные из таблицы (B) по столбцам даты и идентификатору кампании. В том случае, если для какой-то из строк таблицы (А) будут отсутствовать данные из таблицы (B), то подтянется значение NULL.
Теперь разберемся, как формируется левая таблица (А):
SELECT
date
, campaign_id
, campaign_name
, SUM(impressions) AS impressions
, SUM(clicks) AS clicks
, SUM(cost) AS cost
FROM yandex_direct
GROUP BY
date
, campaign_id
, campaign_name
При помощи данного запроса мы выбираем столбцы даты, идентификатора кампании, названия компании, а также суммируем данные по полям показов, кликов и расходов из таблицы FROM yandex_direct и группируем их по дате, кодификатору кампании и названию рекламной кампании при помощи GROUP BY date , campaign_id, campaign_name.
Теперь сформируем таблицу (B).
SELECT
ga_date AS date
, CASE
WHEN regexp_contains(ga_source, '(?i)yandex|mytarget') THEN ifnull(regexp_extract(ga_campaign, 'w*\\|?(\\d+)\\|?'), ga_source)
ELSE ga_source END AS campaign_id
, SUM(goal_1_completions) AS goal_1_completions
FROM analytics
GROUP BY
date
, campaign_id
При помощи данного вложенного запроса мы создаем таблицу, которая содержит столбец дата ga_date AS date, идентификатор рекламной кампании, а также сумму по полю цель 1 SUM(goal_1_completions)ASgoal_1_completions.
Далее группируем по дате и идентификатору кампании при помощи конструкции GROUP BY date, campaign_id.
Все довольно тривиально за исключением условия, которое формирует столбец с идентификатором рекламной кампании:
CASE
WHEN regexp_contains(ga_source, '(?i)yandex|mytarget') THEN ifnull(regexp_extract(ga_campaign, 'w*\\|?(\\d+)\\|?'), ga_source)
ELSE ga_source END AS campaign_id
Данный фрагмент кода представляет из себя конструкцию CASE. Этот оператор проверяет выполнение условия и отображает тот или иной результат в столбце в зависимости от заданных условий.
В данном случае мы видим следующие условия:
WHEN regexp_contains(ga_source, '(?i)yandex|mytarget') -- при помощи регулярного выражения проверяем входит ли у нас в ячейку столбца ga_source исходного набора данных значения yandex или mytarget. Конструкция (?i) говорит системе, что можно игнорировать различия в строчных и прописных буквы в диапазоне [a-zA-Z]. Если это условие выполняется, то переходим к THEN THEN ifnull(regexp_extract(ga_campaign, 'w*\\|?(\\d+)\\|?'), ga_source) -- в данном примере мы смотрим, есть ли в названии кампании ga_campaign текст, который подчиняется регулярному выражению 'w*\\|?(\\d+)\\|?' (его мы разберем ниже). Если да, то выводим его. Если нет или значение NULL, тогда выводим ga_source ELSE ga_source -- выводим значение, которое находилось в столце ga_source изначально END AS campaign_id -- присваиваем название столбцу campaign_id
Обратите внимание на то, что как в таблице А, так и в таблице B мы суммировали показатели показы, клики, расход и достижение цели 1 на уровне Дата и идентификатор кампании с помощью агрегационных функций и конструкции GROUP BY.
В таблице A:
GROUP BY date , campaign_id , campaign_name
В таблице B:
GROUP BY date , campaign_id
Сделать это необходимо для того, чтобы при сопоставлении данных ничего не задваивалось.
Что может удваиваться в данных без группировки?
Допустим, в таблице с расходами по Яндекс.Директу у нас есть несколько строк с одинаковой датой и названием кампании, но разными показателями, а в Google Analytics за эту дату и по этой кампании только одна запись с количеством конверсий 10. Когда мы будет соединять данные с помощью LEFT JOIN, то получится отношение таблиц многие-к-одному.
Это значит, КАЖДОЙ строке, имеющей одинаковую дату и ID кампании прикрепится одинаковое количество конверсий из Google Analytics и данные получатся некорректными.
На скриншоте снизу показано, как те самые 2 конверсии за 10 октября по этой кампании приписались сразу ко всем записям по расходам.
Второй момент.
В Google Analytics нет такого поля, как ID кампании. Идентификатор туда попадает через utm-метку в поле ga_campaign. Поэтому значение идентификатора оттуда необходимо как-то достать.
Проще всего это сделать с помощью регулярных выражений. Для качественной работоспособности этого способа очень важно, чтобы utm-метка была настроена по единой логике во всех кампаниях и на всем периоде размещения. Например, у нас используется вертикальный слеш, по которому очень просто отделить название кампании от идентификатора — …y_search_brand_rf|{campaign_id}&utm_term=….
Если у вас сделано так же, то в среднем значение ga_campaign будет выглядеть примерно так:
y_search_brand_rf|12365489
И тогда вам подойдет такое регулярное выражение:
w*\\|?(\\d+)\\|? где:
w* — любой буквенный символ в любом количестве
\\|? — вертикальный слеш в количестве 0 или 1
(\\d+) — любой числовой символ в любом количестве
Целиком это регулярное выражение извлечет все цифры из ga_campaign, даже если по какой-то причине туда попало что-то после идентификатора и даже если туда НЕ попали вертикальные слеши.
Кстати, на сайте https://regex101.com/ очень удобно проверять такие выражения.
Чтобы вам было просто протестировать все вышеизложенное, вы можете активировать запрос ниже в своем аккаунте Google BigQuery и поиграть с данными:
WITH yandex_direct AS (
SELECT
date('2020-10-10') AS date, '123456789' AS campaign_id, 'y_search_brand_rf' AS campaign_name, 50 AS impressions, 10 AS clicks, 130 AS cost
UNION ALL (SELECT date('2020-10-10') AS date, '123456789' AS campaign_id, 'y_search_brand_rf' AS campaign_name, 530 AS impressions, 10 AS clicks, 130 AS cost)
UNION ALL (SELECT date('2020-10-10') AS date, '123456789' AS campaign_id, 'y_search_brand_rf' AS campaign_name, 530 AS impressions, 108 AS clicks, 1870 AS cost)
UNION ALL (SELECT date('2020-10-10') AS date, '123456789' AS campaign_id, 'y_search_brand_rf' AS campaign_name, 530 AS impressions, 10 AS clicks, 160 AS cost)
UNION ALL (SELECT date('2020-10-9') AS date, '123456789' AS campaign_id, 'y_search_brand_rf' AS campaign_name, 150 AS impressions, 100 AS clicks, 130 AS cost)
UNION ALL (SELECT date('2020-10-8') AS date, '123456789' AS campaign_id, 'y_search_brand_rf' AS campaign_name, 504 AS impressions, 50 AS clicks, 130 AS cost)
UNION ALL (SELECT date('2020-10-7') AS date, '123456789' AS campaign_id, 'y_search_brand_rf' AS campaign_name, 550 AS impressions, 40 AS clicks, 130 AS cost)
UNION ALL (SELECT date('2020-10-6') AS date, '123456789' AS campaign_id, 'y_search_brand_rf' AS campaign_name, 57 AS impressions, 7 AS clicks, 130 AS cost)
UNION ALL (SELECT date('2020-10-5') AS date, '123456789' AS campaign_id, 'y_search_brand_rf' AS campaign_name, 180 AS impressions, 15 AS clicks, 130 AS cost)
UNION ALL (SELECT date('2020-10-10') AS date, '23454324' AS campaign_id, 'y_search_category_rf' AS campaign_name, 50 AS impressions, 10 AS clicks, 130 AS cost)
UNION ALL (SELECT date('2020-10-9') AS date, '23454324' AS campaign_id, 'y_search_category_rf' AS campaign_name, 50 AS impressions, 10 AS clicks, 130 AS cost)
UNION ALL (SELECT date('2020-10-8') AS date, '23454324' AS campaign_id, 'y_search_category_rf' AS campaign_name, 50 AS impressions, 10 AS clicks, 130 AS cost)
UNION ALL (SELECT date('2020-10-7') AS date, '23454324' AS campaign_id, 'y_search_category_rf' AS campaign_name, 50 AS impressions, 10 AS clicks, 130 AS cost)
UNION ALL (SELECT date('2020-10-6') AS date, '23454324' AS campaign_id, 'y_search_category_rf' AS campaign_name, 50 AS impressions, 10 AS clicks, 130 AS cost)
UNION ALL (SELECT date('2020-10-5') AS date, '23454324' AS campaign_id, 'y_search_category_rf' AS campaign_name, 50 AS impressions, 10 AS clicks, 130 AS cost)
UNION ALL (SELECT date('2020-10-1') AS date, '23454324' AS campaign_id, 'y_search_category_rf' AS campaign_name, 50 AS impressions, 10 AS clicks, 130 AS cost)
)
, analytics AS(
SELECT date('2020-10-10') AS ga_date, 'y_search_brand_rf|123456789' AS ga_campaign, 'yandex' AS ga_source, 'cpc' AS ga_medium, 2 AS goal_1_completions
UNION ALL (SELECT date('2020-10-9') AS ga_date, 'y_search_brand_rf|123456789' AS ga_campaign, 'yandex' AS ga_source, 'cpc' AS ga_medium, 2 AS goal_1_completions )
UNION ALL (SELECT date('2020-10-7') AS ga_date, 'y_search_brand_rf|123456789' AS ga_campaign, 'yandex' AS ga_source, 'cpc' AS ga_medium, 0 AS goal_1_completions )
UNION ALL (SELECT date('2020-10-8') AS ga_date, 'y_search_brand_rf|123456789' AS ga_campaign, 'yandex' AS ga_source, 'cpc' AS ga_medium, 28 AS goal_1_completions )
UNION ALL (SELECT date('2020-10-6') AS ga_date, 'y_search_brand_rf|123456789' AS ga_campaign, 'yandex' AS ga_source, 'cpc' AS ga_medium, 7 AS goal_1_completions )
UNION ALL (SELECT date('2020-10-5') AS ga_date, 'y_search_brand_rf|123456789' AS ga_campaign, 'yandex' AS ga_source, 'cpc' AS ga_medium, 4 AS goal_1_completions )
UNION ALL (SELECT date('2020-10-10') AS ga_date, 'y_search_category_rf|23454324' AS ga_campaign, 'yandex' AS ga_source, 'cpc' AS ga_medium, 12 AS goal_1_completions )
UNION ALL (SELECT date('2020-10-9') AS ga_date, 'y_search_category_rf|23454324' AS ga_campaign, 'yandex' AS ga_source, 'cpc' AS ga_medium, 22 AS goal_1_completions )
UNION ALL (SELECT date('2020-10-8') AS ga_date, 'y_search_category_rf|23454324' AS ga_campaign, 'yandex' AS ga_source, 'cpc' AS ga_medium, 15 AS goal_1_completions )
UNION ALL (SELECT date('2020-10-7') AS ga_date, 'y_search_category_rf|23454324' AS ga_campaign, 'yandex' AS ga_source, 'cpc' AS ga_medium, 1 AS goal_1_completions )
UNION ALL (SELECT date('2020-10-6') AS ga_date, 'y_search_category_rf|23454324' AS ga_campaign, 'yandex' AS ga_source, 'cpc' AS ga_medium, 0 AS goal_1_completions )
UNION ALL (SELECT date('2020-10-5') AS ga_date, 'y_search_category_rf|23454324' AS ga_campaign, 'yandex' AS ga_source, 'cpc' AS ga_medium, 2 AS goal_1_completions )
)
SELECT *
FROM (
SELECT
date
, campaign_id
, campaign_name
, SUM(impressions) AS impressions
, SUM(clicks) AS clicks
, SUM(cost) AS cost
FROM yandex_direct
GROUP BY
date
, campaign_id
, campaign_name)
LEFT JOIN
(SELECT
ga_date AS date
, CASE
WHEN regexp_contains(ga_source, '(?i)yandex|mytarget') THEN ifnull(regexp_extract(ga_campaign, 'w*\\|?(\\d+)\\|?'), ga_source)
ELSE ga_source END AS campaign_id
, SUM(goal_1_completions) AS goal_1_completions
FROM analytics
GROUP BY
date
, campaign_id
)
USING (date, campaign_id)
В результате вы увидите сопоставленные данные:
Искренне надеемся, что этот материал был вам полезен.
