Степан Мишин , 22 Апреля 2018

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

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

Аналитика

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

Аналитика

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


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, но этот вариант рассмотрим в следующих статьях.

Подпишитесь на Facebook или Телеграмм, чтобы не пропустить новые статьи

Разделяешь наш подход? Вступай в команду!
Другие материалы по теме
Ошибка при внедрении автостратегий Яндекс ВИДЕО
Ошибка при внедрении автостратегий Яндекс
После успешного применения автоматической стратегии на небольшом проекте решили сделать то же на еще одном интернет-магазине, но допустили серьезную ошибку.
Перевод РСЯ на автоматические стратегии ВИДЕО
Перевод РСЯ на автоматические стратегии
Перевели РСЯ кампанию на автоматические стратегии. Рассказываем о нюансах настройки и даем свои рекомендации.
Как популярность бренда и уровень жизни влияют на конверсию интернет-магазина ВИДЕО
Как популярность бренда и уровень жизни влияют на конверсию интернет-магазина
На примере одного из наших клиентов показываем как популярность бренда и зарплаты в регионах влияют на конверсию сайта.
Визуализация данных Яндекс Директ в PBI HOWTO
Визуализация данных Яндекс Директ в PBI
В предыдущей статье мы рассказали как выгрузить данные из Яндекс Директ с помощью Python в CSV таблицу. Теперь построим на этих данных красивый и полезный отчет.
Интеграция клиента и агентства HOWTO
Интеграция клиента и агентства
В статье расскажем, как происходит интеграция клиента и агентства, и как выстраиваются коммуникации.
Выгрузка данных из API Яндекс Директ через Python HOWTO
Выгрузка данных из API Яндекс Директ через Python
Рассказываем как выгрузили 30 млн. строк данных для последующего анализа в Power BI.
читать наш блог

Оставьте заявку

После того, как вы оставите заявку: интервью ~15 минут → гостевые доступы для аудита ~15 минут → аудит в течении 2-х дней → согласование предложения → начало первой итерации. По нашему опыту реально начать что-то делать уже через 2-3 дня.

Менеджер проектов Александр
Александр

Менеджер проектов



Пишите на — info@1jam.ru , звоните в скайпе — jam.agency , или по телефону — 8 (800) 551-85-03