Маркетинг

Как настроить автоматическую отчетность по РСЯ-площадкам в разрезе конверсий и 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:

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

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

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

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

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

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

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

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

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

название|параметр|название|параметр|название|параметр|
pos|{position_type}{position}|src|{source_type}_{source}

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

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

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

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

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

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

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

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

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

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

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

Способ 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, а внутри задаем следующие настройки:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Но нам в данной строке нужна только колонка 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’"

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Читать ещё: «Кейс: как работать со сквозной аналитикой»

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

Итого у нас получилась связка 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}

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