Как настроить автоматическую отчетность по РСЯ-площадкам в разрезе конверсий и CPA

В первой статье мы рассмотрели, как руками свести данные в Google Sheets, но если у вас много кампаний и клиентов, то вручную это делать долго — нужно автоматизировать процесс. Сделать это можно разными способами: через аддон к Google Sheets Supermetrics, через Excel Power Query или Microsoft Power BI. Мы выберем наиболее быстрый и простой способ —Supermetrics, а в следующих статьях рассмотрим Power BI.

Импорт площадок через Custom Dimensions (Пользовательские параметры)

Есть два способа: забираем из utm_content или добавляем новую UTM-метку. Для обоих способов нам понадобится Google Tag Manager (далее GTM).

Способ 1. Забираем из utm_content

Предположим, что наша UTM-метка имеет вид:

site.ru?utm_medium=cpc&utm_source=yandex&utm_campaign=nazvanie_kampanii|{campaign_id}&utm_term={keyword}&utm_content=id|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}

И utm_content содержит src|{source_type}_{source}, которая передает название площадки вместо {source}, с помощью GTM нам нужно его достать.

Предполагаем, что GTM уже установлен и не будем тратить время на его «разжевывание», данной информации в интернете достаточно.

Внутри GTM во вкладке Variables нажимаем Configure и ставим галочку в Page URL:

Далее создаем новую переменную (Variable):

Выбираем RegEx Table, это регулярное выражение, которое будет извлекать название площадки из utm_content:

Указываем настройки, как на скрине, в качестве регулярного выражения указываем .*src\|([^\|]*).* а в качестве выхода $1 , и сохраняем:

Новую переменную назовем Extract source YD, где на вход мы подаем Page URL, это полный URL перехода пользователи вместе с UTM-метками, а далее извлекаем из него необходимые параметр с помощью регулярного выражения.

В качестве регулярного выражения используем .*src\|([^\|]*).*— как вы могли заметить, вы можете менять src, тем самым доставая необходимые параметры из строки вида:

название|параметр|название|параметр|название|параметр|

pos|{position_type}{position}|src|{source_type}_{source}

Теперь проверяем, как работает наша переменная, активируя режим правки в GTM:

Появится такое вот окно, которое говорит, что режим тестирования активирован:

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

site.ru?utm_content=test|value|src|context_name|test|value

При переходе на сайт, мы видим, что у нас появилось окно GTM, в котором мы видим, как название площадки извлекается в нашу новую переменную Extract source YD:

Всё, видим, что переменная извлекла название площадки из ссылки.

Если у вас что-то не так, и вы внесли какие-либо изменения в настройки GTM, то вам нужно нажать Refresh в окне режима тестирования, чтобы изменения стали активны в контейнере. Далее перезагружаем сам сайт и смотрим изменения:

Способ 2. Передаем через новую UTM-метку

Ко всем объявлениям нам нужно добавить новую utm_place={source}, итого у нас получится:

site.ru?utm_medium=cpc&utm_source=yandex&utm_campaign=name|{campaign_id}&utm_term={keyword}&utm_content=id|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}|main&k50id=01000000{phrase_id}_{retargeting_id}&utm_place={source}

Далее в GTM создаем новую переменную, как делали ранее, и выбираем URL, а внутри задаем следующие настройки:

Всё, теперь снова тестируем, но немного другой ссылкой:

site.ru?utm_content=test|value|src|context_name|test|value&utm_place=test

Видим, что мы снова извлекли значение test из utm_place=test:

Передаем значения в Google Analytics через Custom Dimensions

В настройках Google Analytics (далее GA) выбираем Custom Dimensions:

Добавляем новое:

Задаем название и тип Session:

Запоминаем индекс:

Снова возвращаемся в GTM и переходим в настройки тега Google Analytics, надеемся, что он установлен через GTM:

Нажимаем и добавляем такую настройку: в качестве индекса указываем индекс созданной нами ранее Custom Dimensions, а в качестве переменной для передаче Exctract utm_place, которую мы создали ранее, сохраняем и делаем Refresh в режиме тестирования:

Теперь снова тестируем по ссылке:

site?utm_content=test|value|src|context_name|test|value&utm_place=test

Видим, что значение передается, вуаля!

Публикуем новую версию тега: это значит, что мы выводим новую версию GTM со всем нововведениями в продакшн:

Теперь создаем отчет в Google Analytics:

В качестве Dimensions добавляем созданные ранее Place:

Итого у нас получается:

Далее получаем отчет по площадкам:

Конечно, нужно подождать, пока накопятся данные для отчета. Также стоит заметить, что сами данные поступают в отчет с задержкой минут 30 от факта.

Как свести данные с расходами через Supermetrics

Создаем новый Google Sheets и скачиваем аддон:

Подключаем и сразу активируем триал-версию:

Включаем аддон:

Выгружаем данные из Яндекс.Директа

Внутри Data Source выбираем Yandex.Direct (beta) и добавляем новый аккаунт Add new account:

После несложной процедуры авторизации вы сможете выбрать свой аккаунт Яндекс.Директа в Select Accounts. Выбираем из выпадающего списка:

В блоке Select dates выбираем дату, например три последних месяца:

В блоке Select Metrics выбираем показатели Impressions, Clicks, Cost из выпадающего списка:

В блоке Split by выбираем Campaign ID и Ad display location:

Нажимаем Get data to table и получаем данные:

Выгружаем данные из Google Analytics

Создаем новый лист и аналогично Яндекс Директ авторизуемся и выбираем аккаунт для выгрузки данных в Supermetrics:

Выбираем метрики, при этом замечу, что по API нельзя выгружать Calculated metrics (Вычисляемые показатели), а только цели, но вы можете выгрузить все цели и сами сложить их, как вам нужно:

В качестве параметра для выгрузки выбираем Custom Dimensions с индексом 2, который мы создали ранее и Campaign:

Нажимаем Get Data to Table и получаем таблицу:

Сводим расходы с доходами

Свести таблицы, это значит, что нам нужно подтянуть в таблицу Google Analytics данные о расходах по определенной площадке и ID кампании в директе, названия площадок у нас есть, а вот ID вытягиваем из названия кампании (utm_campaign) с помощью регулярного выражения:

=REGEXEXTRACT(A2,".*\|(.*)")

Итого у нас есть столбик с ID кампании, альтернативный вариант, можно создать еще один Custom Dimensions, как мы сделали с площадками и извлекать туда ID кампании.

Теперь у нас есть две таблицы с данными из Яндекс.Директа и Google Adwords, в прошлой статье, чтобы свести их, мы использовали примитивный метод с помощью функции VLOOKUP (ВПР). Сейчас нам нужно будет выполнить аналогичную задачу, но мы сделаем это более технологично с помощью функции QUERY:

QUERY (откуда берем данные, например таблица; запрос к таблице на языке SQL)

Создаем новую вкладку и вставляем функцию в первую ячейку:

Вы увидите, как мы полностью подтянули таблицу из соседней вкладки, попробую перевести функцию:

=QUERY('Google Analytics'!A:D, "SELECT *")
=QUERY(Таблица Google Analytics с колонками от A до D, "Выбрать все")
SELECT — Выбрать
* — Все, что в диапазоне

Теперь подтягиваем данные из таблицы Яндекс.Директа.

Добавляем новую колонку Cost и пишем аналогичный запрос. Видим, как подтянулась вся таблица:

=QUERY('Яндекс Директ'!A:E, "SELECT *")

Но нам в данной строке нужна только колонка Cost кампании E2 и площадки B2 — модифицируем запрос:

=QUERY('Яндекс Директ'!$A$2:$E$1001, "SELECT E WHERE A="&E2&" and B='"&B2&"'")

=QUERY(В таблице Яндекс Директ в диапазоне $A$2:$E$1001 (без первой строки со знаками фиксации $), "ВЫБРАТЬ столбик E ГДЕ колонка A=" & (знак сцепить) значению E2 в нашей таблице & (знак сцепить) " И колонка B='" & (знак сцепить) значению B2 в нашей таблице &(знак сцепить)"'(одинарная кавычка")

У нас как бы получается запрос "SELECT E WHERE A=28512929 and B=’avito.ru’"

В итоге мы получаем одно значение:

Размножаем его на другие строки и в некоторых строках получаем #N/A, так как значение не найдено, добавляем:

=IFERROR(Если в выражении ошибка, то ставим=>, 0)

=IFERROR(QUERY('Яндекс Директ'!$A$2:$E$1001, "SELECT E WHERE A="&E2&" and B='"&B2&"'"),0)

То есть, где ошибка, подставляем ноль.

Все, таблица готова, теперь на ее основании делаем сводную:

И получаем вот такую красоту:

Самое главное, чтобы обновить все данные, вам нужно только нажать одну кнопку:

Но минус в том, что сам Supermetrics стоит 45 евро в месяц, но триал на один gmail-аккаунт 30 дней, поэтому это никого не останавливает.

Выводим данные в Data Studio

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

Создаем новый отчет в Data Studio (далее DS) и в качестве источника добавляем нашу таблицу, вкладку Data, где мы свели Google Analytics с расходами из Яндекс Директа:

Нажимаем Connect (подключить) и видим все колонки, которые определили DS:

Но нам нужно добавить новые показатели, это CPA (стоимость лида) и CR (конверсия в лиды), нажимаем Add field (Добавить поле). Добавим формулу CPA, это Cost (Расходы) / KPI (Конверсии):

Аналогично делаем для CR.

Жмем Add to report (Добавить данные к отчету):

Всё, теперь перед нами чистый лист для графиков. Добавим таблицу площадок и кампаний, чтобы смотреть их разрезе количества и стоимости лидов:

Добавим доли расходов по площадкам:

Добавим фильтры по кампаниям и площадкам и, отдельные показатели, в итоге получаем таблицу ниже:

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

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

Итого у нас получилась связка Google Sheets + Supermetrics + Data Studio, где всё обновляется. Если сделать один раз, то готовую модель можно достаточно быстро скопировать на новый проект. Еще круче можно сделать только в Power BI, но этот вариант рассмотрим в следующих статьях.