Как исправить ошибки в UTM-метках при анализе в BigQuery

В этой статье мы рассмотрим достаточно частый кейс — ошибки в UTM-метках и необходимость их исправить при подготовке отчетности в BigQuery. Кто-то совершил опечатку или поставил совсем неверные метки в рекламных ссылках, а мы потом видим сеансы и конверсии с неверными названиями источников в отчетах.

О том, что такое Google BigQuery и каким образом ее можно использовать для веб-аналитики можно прочитать в моей вводной статье или о том, как загружать статистику из рекламных систем в эту базу данных.

Представим, что в BigQuery у нас есть таблица или view example.conversions, содержащая источники трафика и конверсии:

В рамках этого материала мы не будем рассматривать создание view example.conversions, так как разбирали это в предыдущих материалах. Для простоты будем считать что эта таблица (или view) у нас уже есть.

В названия некоторых из приведенных источников нужно внести изменения. Первая мысль — добавить в SQL-скрипт, рассчитывающий конверсии в example.conversions, операторы IF или CASE для каждого исправляемого источника. Но это плохой вариант, так как, во-первых, SQL-запрос будет разрастаться, если вы или ваши маркетологи будут допускать новые ошибки. И во-вторых, если у вас несколько view для анализа трафика, то нужно будет прописывать исправления в каждом скрипте.

Мы же будем действовать по-другому — создадим в BigQuery таблицу example.renaming_rules, содержащую правила переименования источников и кампаний. И напишем SQL-запрос, который будет обрабатывать эти правила:

• n1 — источник fcebook / cpc с опечаткой, кампания webinar_theory переименуем в facebook / cpc, кампания webinar_theory;
• n3 — referral / yabs.yandex.ru, кампания (not set) — таким образом Google Analytics обозначает переходы из Яндекс.Директа в случае, если UTM-метки не проставлены. Переименуем в yandex / cpc и назовем кампанией yabs.yandex.ru, чтобы отличать от других кампаний в Яндексе.
• n4,5 — переименуем источник vk_com / cpc в vk / cpc. При этом знак звездочки указывает, что ошибка может быть допущена не в одной кампании, а значит и исправление будет во всех.
• n6,7 — referral / searchengines.ru с кампанией (not set) — это реферальные переходы с сайта searchengines.ru без UTM-меток. А referral / searchengines.ru с кампанией digest_2017_november — это размеченная ссылка из нашего материала. Такие переходы (все кампании, кроме (not set)) переименуем в social / searchengines — они будут относиться к работе нашего отдела PR и SMM.
• n8,9 — во всех источниках и кампаниях с каналом emaill изменим канал на email.

Таблицу example.renaming_rules можно вести в обычном Google Sheets документе, а в BigQuery загружать через плагин для браузера OWOX BI BigQuery Reports или встроенную интеграцию BigQuery и Google Sheets (см. скриншот из BigQuery):

Для получения исходных данных из view example.conversions нам потребуется простейший SQL-запрос:

SELECT n, utm_medium, utm_source, utm_campaign, conversions
FROM example.conversions
ORDER BY n

Чтобы исправить UTM-метки по таблице правил переименования example.renaming_rules, вы можете использовать следующий SQL-скрипт:

SELECT
   q.n as n,
   CASE
      WHEN ren3.to_utm_medium IS NOT NULL THEN ren3.to_utm_medium
      WHEN ren2.to_utm_medium IS NOT NULL AND ren2.except_campaign!=q.utm_campaign THEN ren2.to_utm_medium
      WHEN ren1.to_utm_medium IS NOT NULL THEN ren1.to_utm_medium
      ELSE q.utm_medium
   END as utm_medium,
   CASE
      WHEN ren3.to_utm_medium IS NOT NULL THEN ren3.to_utm_source
      WHEN ren2.to_utm_medium IS NOT NULL AND ren2.except_campaign!=q.utm_campaign THEN ren2.to_utm_source
      ELSE q.utm_source
   END as utm_source,
   CASE
      WHEN ren3.to_utm_medium IS NOT NULL THEN ren3.to_utm_campaign
      ELSE q.utm_campaign
   END as utm_campaign,   
   q.conversions as conversions,
   
   
FROM   
(   
   SELECT n, utm_medium, utm_source, utm_campaign, conversions
   FROM example.conversions
   ORDER BY n
) as q


//rename3 по полному совпадению 3х полей
LEFT JOIN 
(
   SELECT from_utm_medium, from_utm_source, from_utm_campaign, to_utm_medium, to_utm_source, to_utm_campaign
   FROM example.renaming_rules 
   WHERE from_utm_source!='*' AND to_utm_campaign!='*'
   GROUP BY from_utm_medium, from_utm_source, from_utm_campaign, to_utm_medium, to_utm_source, to_utm_campaign
) as ren3 ON q.utm_medium=ren3.from_utm_medium AND q.utm_source=ren3.from_utm_source AND q.utm_campaign=ren3.from_utm_campaign

//rename2 по utm_medium и utm_source
LEFT JOIN 
(
   SELECT from_utm_medium, from_utm_source, to_utm_medium, to_utm_source, except_campaign
   FROM example.renaming_rules 
   WHERE from_utm_campaign='*' AND to_utm_campaign='*'
   GROUP BY from_utm_medium, from_utm_source, to_utm_medium, to_utm_source, except_campaign
) as ren2 ON q.utm_medium=ren2.from_utm_medium AND q.utm_source=ren2.from_utm_source 

//rename1 по utm_medium
LEFT JOIN 
(
   SELECT from_utm_medium, to_utm_medium
   FROM example.renaming_rules
   WHERE from_utm_campaign='*' AND to_utm_campaign='*' AND from_utm_source='*' AND to_utm_source='*'
   GROUP BY from_utm_medium, to_utm_medium
) as ren1 ON q.utm_medium=ren1.from_utm_medium

ORDER BY n

Результат выполнения скрипта:

Как видите, источники исправлены согласно нашим правилам из example.renaming_rules.

SQL-скрипт получился универсальным, вы можете скопировать его себе и использовать в каждой view собирающей статистику по каналам трафика. Теперь можно не переживать из-за неверных UTM-меток в рекламных материалах, всё можно исправить без потерь.