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

Корякин Павел, веб-аналитик агентства контекстной рекламы Jam, специально для блога Нетологии написал статью о том, как сводить конверсии и расходы по РСЯ-площадкам в Google Analytics.

В первой статье мы рассмотрели, как руками свести данные в 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:

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

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

При переходе на сайт, мы видим, что у нас появилось окно 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, а внутри задаем следующие настройки:

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

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

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

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

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

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

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

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

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

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

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

Публикуем новую версию тега: это значит, что мы выводим новую версию 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, но этот вариант рассмотрим в следующих статьях.

Мнение автора и редакции может не совпадать. Хотите написать колонку для «Нетологии»? Читайте наши условия публикации.

p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial; -webkit-text-stroke: #000000}
span.s1 {font-kerning: none}