В этой статье мы рассмотрим достаточно частый кейс — ошибки в 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-меток в рекламных материалах, всё можно исправить без потерь.
Ваша реклама на ppc.world
от 10 000 ₽ в неделю
Последние комментарии