Использование регулярных выражений в SQL для получения более подробной сводной статистики рекламных данных

Ранее вы уже могли ознакомиться с нашей статьей по базовому сопоставлению данных из рекламного кабинета Яндекс.Директ с конверсиями из аналитических систем Google Analytics и Яндекс.Метрики. Если нет, ознакомиться можно по ссылке — Как соединить данные из Яндекс.Директ с конверсиями из Google Analytics при помощи SQL

Сегодня же мы покажем, как можно наполнить эти данные еще большим количеством параметров и показателей с помощью SQL-запросов.

Исходные данные

Первым делом вспомним, какие поля были в наших табличках:

Яндекс.Директ:

• date — дата показа рекламы
• campaign_id — идентификатор рекламной кампании
• campaign_name — название кампании
• impressions — количество показов
• clicks — количество кликов
• cost — расход

Google Analytics:

• ga_date — дата визита
• ga_campaign — содержание UTM_campaign объявления
• ga_source — источник
• ga_medium — канал
• goal_1_completions — достижения ключевой (нужной для отчета) цели

Этих данных достаточно для того, чтобы в конечном итоге мы могли видеть расход, количество кликов, показов, конверсий на уровне даты и каждой кампании. А что если мы хотим добавить показатели по группам объявлений, по конкретному объявлению и его тексту, по ключевому слову?

Структура UTM

Сначала необходимо убедиться или сделать так, чтобы во всех объявлениях рекламных кампаний была корректная utm-разметка.
Типовая разметка в нашем агентстве (ее мы делаем через K50) выглядит следующим образом:

?utm_medium=cpc&utm_source=yandex&utm_campaign=НАЗВАНИЕ РК|{campaign_id}&utm_term={keyword}&utm_content=k50id|01000000{phrase_id}_{retargeting_id}|cid|{campaign_id}|gid|{gbid}|aid|{ad_id}|adp|{addphrases}|pos|{position_type}{position}|src|{source_type}_{source}|dvc|{device_type}|reg|{region_id}|main&k50id=01000000{phrase_id}_{retargeting_id}

Для нашей задаче, нам важно обратить внимание на содержание utm_term и utm_content:

• utm_term={keyword} — динамический параметр. Сюда передается ключевая фраза, по которой было показано объявление, без минус-слов. Соответственно, если кампания не поисковая, то параметр примет пустое значение
• gid|{gbid} — такую конструкцию можно считать парой ключ-значение, где ключ это gid, а значение — динамический параметр {gbid}. Этот параметр принимает значение идентификатора группы объявлений, к которой относится показанное объявление. Ключ может быть назван вами как вам будет удобнее и понятнее
• aid|{ad_id} — соответственно идентификатор объявления
• dvc|{device_type} — тип устройства
• pos|{position_type}{position} — тип и ранг позиции показа
• и т.д.

Вы можете наполнить свою разметку какими угодно удобными и нужными для вас параметрами, в рамках которых в дальнейшем можно сводить данные. Подробнее о каждом из таких параметров всегда можно прочитать в справке яндекса — https://yandex.ru/support/direct/statistics/url-tags.html

Теперь нужно убедиться, что в наших таблицах Яндекс.Директ и в данных аналитики присутствуют необходимые дополнительные поля.

Яндекс.Директ:

• AdGroupId — идентификатор группы объявлений
• AdId — идентификатор объявления

Названия полей могут отличаться, самое главное, чтобы они правильно отображали суть их содержимого.

В данных из системы аналитики нам дополнительно нужны поля:

• ga_content — содержание UTM_Content объявления
• ga_term — содержание UTM_Term объявления

Тогда наши данные из Директа будут выглядеть примерно так:

А данные аналитики так:

Итоговый SQL код

Теперь давайте взглянем на наш SQL-запрос:

SELECT * FROM (
  SELECT
    date
    , campaign_id
    , campaign_name
    , AdGroupId AS group_id
    , AdId AS ad_id
    , SUM(impressions) AS impressions
    , SUM(clicks) AS clicks
    , SUM(cost) AS cost
  FROM yandex_direct
  GROUP BY
    date
    , campaign_id
    , campaign_name
    , group_id
    , ad_id
    )
 
LEFT JOIN
 
(SELECT 
    ga_date AS date
    , CASE
      WHEN regexp_contains(ga_source, '(?i)yandex') 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') THEN ifnull(regexp_extract(ga_content, 'gid\\|?(\\d+)\\|'), ga_source)
      ELSE 'not set' END AS group_id
    , CASE WHEN regexp_contains(ga_source, '(?i)yandex') THEN ifnull(regexp_extract(ga_content, 'aid\\|?(\\d+)\\|'), ga_source)
      ELSE 'not set' END AS ad_id
    , CASE WHEN regexp_contains(ga_source, '(?i)yandex') THEN ifnull(regexp_extract(ga_content, 'pos\\|?(\\w+)\\|'), ga_source)
      ELSE 'not set' END AS position_type
    , CASE WHEN regexp_contains(ga_source, '(?i)yandex') THEN ifnull(regexp_extract(ga_content, 'dvc\\|?(\\w+)\\|'), ga_source)
      ELSE 'not set' END AS device_type
    , ga_term AS search_term
    , SUM(goal_1_completions) AS goal_1_completions
  FROM analytics
  GROUP BY
    date
    , campaign_id
    , group_id
    , ad_id
    , position_type
    , device_type
    , search_term
)
 
USING (date, campaign_id, group_id, ad_id)

Этот запрос очень похож на тот, который мы уже показывали

Детальный разбор SQL запроса

Получение данных из таблицы Яндекс.Директ

Отличается он только количеством выводимых полей из таблиц. Давайте посмотрим на него подробнее. Первая его часть получает данные из таблицы Яндекс.Директ:

SELECT
    date
    , campaign_id
    , campaign_name
    , AdGroupId AS group_id
    , AdId AS ad_id
    , SUM(impressions) AS impressions
    , SUM(clicks) AS clicks
    , SUM(cost) AS cost
  FROM yandex_direct
  GROUP BY
    date
    , campaign_id
    , campaign_name
    , group_id
    , ad_id

В блоке SELECT мы перечисляем названия полей (колонок), которые мы хотим видеть. Где-то, как вы видите, присутствует назначение алиаса с помощью оператора AS. Иными словами мы просто переименовываем колонку для удобства и читаемости.

В строке с FROM мы просто указываем название нашей таблица с данными.

В блоке GROUP BY мы агрегируем или “схлопываем” данные (в нашем случае суммируем показы, клики и расход) на уровне перечисленных полей — даты, ID кампании, названии кампании, ID группы, ID объявления.

Получение данных из таблицы веб-аналитической системы

Вторая часть кода формирует данные из аналитических систем:

SELECT 
    ga_date AS date
    , CASE
      WHEN regexp_contains(ga_source, '(?i)yandex') 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') THEN ifnull(regexp_extract(ga_content, 'gid\\|?(\\d+)\\|'), ga_source)
      ELSE 'not set' END AS group_id
    , CASE WHEN regexp_contains(ga_source, '(?i)yandex') THEN ifnull(regexp_extract(ga_content, 'aid\\|?(\\d+)\\|'), ga_source)
      ELSE 'not set' END AS ad_id
    , CASE WHEN regexp_contains(ga_source, '(?i)yandex') THEN ifnull(regexp_extract(ga_content, 'pos\\|?(\\w+)\\|'), ga_source)
      ELSE 'not set' END AS position_type
    , CASE WHEN regexp_contains(ga_source, '(?i)yandex') THEN ifnull(regexp_extract(ga_content, 'dvc\\|?(\\w+)\\|'), ga_source)
      ELSE 'not set' END AS device_type
    , ga_term AS search_term
    , SUM(goal_1_completions) AS goal_1_completions
  FROM analytics
  GROUP BY
    date
    , campaign_id
    , group_id
    , ad_id
    , position_type
    , device_type
    , search_term

Здесь всё очень похоже, но добавляются условные конструкции и регулярные выражения.

Конструкция CASE WHEN проверяет выполнение условия и отображает тот или иной результат в столбце в зависимости от заданных условий. В нашем случае, например, это работает так:

CASE WHEN regexp_contains(ga_source, '(?i)yandex') – при помощи регулярного выражения проверяем входит ли у нас в ячейку столбца ga_source исходного набора данных значения yandex или mytarget. Конструкция (?i) говорит системе, что можно игнорировать различия в строчных и прописных буквы в диапазоне [a-zA-Z]. Если это условие выполняется, то переходим к THEN
THEN ifnull(regexp_extract(ga_content, 'gid\\|?(\\d+)\\|'), ga_source) -- в данном примере мы смотрим, есть ли в содержании UTM-метки ga_content текст, который подчиняется регулярному выражению 'gid\\|?(\\d+)\\|' (его и остальные мы разберем ниже). Если да, то выводим его. Если нет или значение NULL, тогда выводим ga_source
ELSE 'not set' -- в противном случае, то есть, если условие WHEN не сработало (в метке отсутствует такая часть строки), возвращаем строку ‘not set’
END AS group_id -- заканчиваем наше условие и присваиваем название столбцу group_id. Так мы получили наш идентификатор кампании

Остальные строки, использующие такую конструкцию, работают аналогичным образом. Отличие лишь в том, что мы используем разные регулярные выражения для извлечения разных частей UTM-метки, и присваиваем соответствующие названия.

Разбор регулярных выражений в SQL запросе

Теперь о регулярных выражениях. В нашем примере кода для извлечения данных из UTM-content их несколько:

• gid\\|?(\\d+)\\|? — извлекает числовое значение, находящееся между “gid|” и первым последующим вертикальным слэшем, то есть ID группы
• aid\\|?(\\d+)\\|? — извлекает числовое значение, находящееся между “aid|” и первым последующим вертикальным слэшем, то есть ID объявления
• pos\\|?(\\w+)\\|? — извлекает значение, находящееся между “pos|” и первым последующим вертикальным слэшем, то есть позицию показа
• dvc\\|?(\\w+)\\|? — извлекает значение, находящееся между “dvc|” и первым последующим вертикальным слэшем, то есть тип устройства

Регулярные выражения это целый отдельный язык, в котором можно много практиковаться. В нашем примере всё достаточно просто:

• gid — означает начало подстроки с соответствующего значения
• \\|? — означает один или 0 вертикальных слэшей
• (\\d+) — означает любые числовые символы в любом количестве. Как-раз то, что нам нужно для ID группы
• \\|? — означает вертикальный слэш в количество от 0 до 1, но на этот раз это правая граница нашего поиска

В сумме такая регулярка достанет нам все цифры после “gid|” и до первого вертикального слэша, не включая его.

Имейте ввиду, что в разных рекламных системах могут передаваться разные параметры, соответственно и прометка там разная, а значит, опять же, придется подстраивать регулярные выражения под свой случай

Объединение двух частей SQL запроса в один

Обе части нашего кода объединяем при помощи LEFT JOIN по нескольким ключам объединения

USING (date, campaign_id, group_id, ad_id)

Дате, ID кампании, ID группы, ID объявления.

Теперь наша финальная таблица принимает примерно такой вид:

Мы получили все необходимые показатели до уровня объявления, а также информацию по типу девайса и поисковому запросу. Теперь эти данные можно импортировать в вашу любимую систему визуализации и смотреть статистику в разрезе чего вам только хочется

Обогащение данных заголовками и текстами рекламных объявлений

И еще бонусный момент. В статистике Яндекс.Директа, как и в аналитических системах, невозможно вывести тексты ваших объявлений. Но если вы хотите их добавить в финальный отчет и смотреть эффективность в разрезе определенных заголовков или описаний, то можно достаточно просто сделать это, создав собственный словарь объявлений.

Выгрузка текстов и заголовков объявлений через Директ Комманде

Для этого заходим в Директ Коммандер, выбираем все группы всех ваших рекламных кампаний и переходим на уровень объявлений. Там нам нужно будет полностью скопировать столбцы с ID объявления, заголовками и описаниями. Никак не фильтруя, всё что есть. Для этого жмем правой кнопкой мыши по шапке столбца и копируем его целиком:

И создаем Google-таблицу, в которую всё вставляем. По итогу должно получиться что-то подобное:

Ручной импорт заголовков и текстов Яндекс.Директ в Google BigQuery

Теперь такую таблицу вы можете импортировать с свою базу данных. Мы используем Google BigQuery и покажем на ее примере.

Рядом с нашим датасетом жмем на 3 точки и затем “Create table”

Далее заполняем всё как на скриншоте — выбираем тип загрузки, вставляем ссылку на таблицу, даем ей название:

А также заполняем названия и типы полей. Важно, если у вас в таблице присутствует шапка, то в поле Header rows to skip необходимо ввести единицу, чтобы эта строка не появилась в таблице как значения.

После чего сохраняем нашу таблицу.

Улучшаем SQL запрос для добавления заголовков и текстов

Теперь мы можем сделать еще один LEFT JOIN по ключу ad_id после нашего основного SQL-запроса и получим новые данные из нашего словаря:

Полный код запроса на создание базы и сопоставления данных

Полный код SQL вместе с созданием игрушечных данных для проверки:

WITH yandex_direct AS
  (SELECT date('2020-10-10') AS date,
          '123456789' AS campaign_id,
          'y_search_brand_rf' AS campaign_name,
          '1235415' AS AdGroupId,
          '22233344' AS AdId,
          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,
             '1235415' AS AdGroupId,
             '33344455' AS AdId,
             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,
             '1235415' AS AdGroupId,
             '33344455' AS AdId,
             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,
             '3214565' AS AdGroupId,
             '44455566' AS AdId,
             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,
             '3214565' AS AdGroupId,
             '44455566' AS AdId,
             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,
             '3214565' AS AdGroupId,
             '66655544' AS AdId,
             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,
             '3214565' AS AdGroupId,
             '66655544' AS AdId,
             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,
             '3214565' AS AdGroupId,
             '66655544' AS AdId,
             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,
             '1235415' AS AdGroupId,
             '77788899' AS AdId,
             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,
             '4567890' AS AdGroupId,
             '111112222' AS AdId,
             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,
             '4567890' AS AdGroupId,
             '111112222' AS AdId,
             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,
             '4567890' AS AdGroupId,
             '555554444' AS AdId,
             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,
             '4567890' AS AdGroupId,
             '555554444' AS AdId,
             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,
             '7418529' AS AdGroupId,
             '777778888' AS AdId,
             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,
             '7418529' AS AdGroupId,
             '777778888' AS AdId,
             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,
             '7418529' AS AdGroupId,
             '777778888' AS AdId,
             50 AS impressions,
             10 AS clicks,
             130 AS cost)) -- 'k50id|0100000018958659455_18958659455|cid|47738135|gid|7418529|aid|777778888|adp|no|pos|premium1|src|search_none|dvc|mobile|reg|213|main' AS ga_content
,
     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,
          'k50id|0100000018958659455_18958659455|cid|123456789|gid|1235415|aid|22233344|adp|no|pos|premium1|src|search_none|dvc|desktop|reg|213|main' AS ga_content,
          'купить iphone' AS ga_term,
          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,
             'k50id|0100000018958659455_18958659455|cid|123456789|gid|3214565|aid|44455566|adp|no|pos|premium1|src|search_none|dvc|desktop|reg|213|main' AS ga_content,
             'купить iphone 14' AS ga_term,
             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,
             'k50id|0100000018958659455_18958659455|cid|123456789|gid|3214565|aid|66655544|adp|no|pos|premium1|src|search_none|dvc|mobile|reg|213|main' AS ga_content,
             'купить iphone 11' AS ga_term,
             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,
             'k50id|0100000018958659455_18958659455|cid|123456789|gid|3214565|aid|66655544|adp|no|pos|premium1|src|search_none|dvc|mobile|reg|213|main' AS ga_content,
             'купить iphone 14 pro' AS ga_term,
             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,
             'k50id|0100000018958659455_18958659455|cid|123456789|gid|3214565|aid|66655544|adp|no|pos|premium1|src|search_none|dvc|desktop|reg|213|main' AS ga_content,
             'купить iphone 13' AS ga_term,
             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,
             'k50id|0100000018958659455_18958659455|cid|123456789|gid|1235415|aid|77788899|adp|no|pos|premium1|src|search_none|dvc|mobile|reg|213|main' AS ga_content,
             'купить iphone недорого' AS ga_term,
             4 AS goal_1_completions)
   UNION ALL
     (SELECT date('2020-10-10') AS ga_date,
             'y_search_brand_rf|123456789' AS ga_campaign,
             'yandex' AS ga_source,
             'cpc' AS ga_medium,
             'k50id|0100000018958659455_18958659455|cid|123456789|gid|1235415|aid|22233344|adp|no|pos|premium1|src|search_none|dvc|mobile|reg|213|main' AS ga_content,
             'купить iphone недорого' AS ga_term,
             0 AS goal_1_completions)
   UNION ALL
     (SELECT date('2020-10-10') AS ga_date,
             'y_search_brand_rf|123456789' AS ga_campaign,
             'yandex' AS ga_source,
             'cpc' AS ga_medium,
             'k50id|0100000018958659455_18958659455|cid|123456789|gid|1235415|aid|33344455|adp|no|pos|premium1|src|search_none|dvc|mobile|reg|213|main' AS ga_content,
             'купить iphone недорого' AS ga_term,
             0 AS goal_1_completions)
   UNION ALL
     (SELECT date('2020-10-10') AS ga_date,
             'y_search_brand_rf|123456789' AS ga_campaign,
             'yandex' AS ga_source,
             'cpc' AS ga_medium,
             'k50id|0100000018958659455_18958659455|cid|123456789|gid|3214565|aid|44455566|adp|no|pos|premium1|src|search_none|dvc|mobile|reg|213|main' AS ga_content,
             'купить iphone недорого' AS ga_term,
             0 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,
             'k50id|0100000018958659455_18958659455|cid|23454324|gid|4567890|aid|111112222|adp|no|pos|premium1|src|search_none|dvc|mobile|reg|213|main' AS ga_content,
             'купить телефон' AS ga_term,
             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,
             'k50id|0100000018958659455_18958659455|cid|23454324|gid|4567890|aid|111112222|adp|no|pos|premium1|src|search_none|dvc|mobile|reg|213|main' AS ga_content,
             'купить смартфон' AS ga_term,
             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,
             'k50id|0100000018958659455_18958659455|cid|23454324|gid|4567890|aid|555554444|adp|no|pos|premium1|src|search_none|dvc|desktop|reg|213|main' AS ga_content,
             'купить телефон новый' AS ga_term,
             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,
             'k50id|0100000018958659455_18958659455|cid|23454324|gid|4567890|aid|555554444|adp|no|pos|premium1|src|search_none|dvc|desktop|reg|213|main' AS ga_content,
             'купить телефон' AS ga_term,
             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,
             'k50id|0100000018958659455_18958659455|cid|23454324|gid|7418529|aid|777778888|adp|no|pos|premium1|src|search_none|dvc|mobile|reg|213|main' AS ga_content,
             'купить смартфон недорого' AS ga_term,
             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,
             'k50id|0100000018958659455_18958659455|cid|23454324|gid|7418529|aid|777778888|adp|no|pos|premium1|src|search_none|dvc|desktop|reg|213|main' AS ga_content,
             'купить телефон недорого' AS ga_term,
             2 AS goal_1_completions)
   UNION ALL
     (SELECT date('2020-10-1') AS ga_date,
             'y_search_category_rf|23454324' AS ga_campaign,
             'yandex' AS ga_source,
             'cpc' AS ga_medium,
             'k50id|0100000018958659455_18958659455|cid|23454324|gid|7418529|aid|777778888|adp|no|pos|premium1|src|search_none|dvc|desktop|reg|213|main' AS ga_content,
             'купить телефон недорого' AS ga_term,
             3 AS goal_1_completions))
SELECT *
FROM
  (SELECT date , campaign_id ,
                 campaign_name ,
                 AdGroupId AS group_id ,
                 AdId AS ad_id ,
                 SUM(impressions) AS impressions ,
                 SUM(clicks) AS clicks ,
                 SUM(cost) AS cost
   FROM yandex_direct
   GROUP BY date , campaign_id ,
                   campaign_name ,
                   group_id ,
                   ad_id)
LEFT JOIN
  (SELECT ga_date AS date ,
          CASE
              WHEN regexp_contains(ga_source, '(?i)yandex') 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') THEN ifnull(regexp_extract(ga_content, 'gid\\|?(\\d+)\\|?'), ga_source)
              ELSE 'not set'
          END AS group_id ,
          CASE
              WHEN regexp_contains(ga_source, '(?i)yandex') THEN ifnull(regexp_extract(ga_content, 'aid\\|?(\\d+)\\|?'), ga_source)
              ELSE 'not set'
          END AS ad_id ,
          CASE
              WHEN regexp_contains(ga_source, '(?i)yandex') THEN ifnull(regexp_extract(ga_content, 'pos\\|?(\\w+)\\|?'), ga_source)
              ELSE 'not set'
          END AS position_type ,
          CASE
              WHEN regexp_contains(ga_source, '(?i)yandex') THEN ifnull(regexp_extract(ga_content, 'dvc\\|?(\\w+)\\|?'), ga_source)
              ELSE 'not set'
          END AS device_type ,
          ga_term AS search_term ,
          SUM(goal_1_completions) AS goal_1_completions
   FROM analytics
   GROUP BY date , campaign_id ,
                   group_id ,
                   ad_id ,
                   position_type ,
                   device_type ,
                   search_term) USING (date, campaign_id,
                                             group_id,
                                             ad_id) /* убрать комментарий для джоина словаря

LEFT JOIN

(SELECT * FROM `dataupload-230410.dima_tasks_sql.ads_dictionary`)

USING (ad_id)
*/