Трансформируем ваши данные в прибыль

Пн — Пт: с 10:00 до 19:00

ГлавнаяБлогКак соединить данные из Яндекс.Директ с конверсиями из Google Analytics при помощи SQL

Как соединить данные из Яндекс.Директ с конверсиями из Google Analytics при помощи SQL

7 минут(ы)

Базовая задача интернет маркетолога заключается в умении сопоставить данные по рекламе с данными о ее эффективности. Например, сопоставить расходы по Яндекс.Директ с данными по продажам из 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, то получится отношение таблиц многие-к-одному.

Количество конверсий из Google Analytics

Это значит, КАЖДОЙ строке, имеющей одинаковую дату и ID кампании прикрепится одинаковое количество конверсий из Google Analytics и данные получатся некорректными.
На скриншоте снизу показано, как те самые 2 конверсии за 10 октября по этой кампании приписались сразу ко всем записям по расходам.

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)

В результате вы увидите сопоставленные данные:

Результаты

Искренне надеемся, что этот материал был вам полезен.