Как вручную свести конверсии и стоимость площадок РСЯ в Google Analytics

Агентство контекстной рекламы 1jam.ru пришло к нам с рассказом о том, как вручную свести расходы и конверсии в разрезе РСЯ площадок. Мы спросили, зачем это делать, и узнали, что «Метрика была и остается «деревянным» инструментом, профессионалы работают в Google Analytics». Окей, давайте посмотрим, как это делают профессионалы.

Чаще всего данные собираются в Google Analytics, но названия площадок РСЯ при импорте не передаются, так что мы не можем посмотреть, сколько конверсий принесла та или иная площадка. Аналогично не передаются и расходы, то есть сколько денег было потрачено на каждую площадку.

Например, мы получаем конверсии по 9500 руб. по площадке pogoda.yandex.ru, ее точно нужно отключить

Содержание

Зачем Google Analytics, если можно в Яндекс Директе или Метрике?
Импорт площадок из UTM-меток в Google Analytics
Вставлять {source} в utm_content ссылок объявлений
Выгружаем отчет в Google Sheets
Выгрузка расходов по площадкам РСЯ
Какие выводы можно сделать из этой таблицы

Зачем Google Analytics, если можно в Яндекс Директе или Метрике?

Большинство сервисов коллтрекинга не умеют импортировать данные о конверсиях в Метрику.
Большинство онлайн-чатов и сервисов обратных звонков не умеют импортировать данные о конверсиях в Метрику.
Популярные AmoCRM и RetailCRM не умеют импортировать данные об отложенных конверсиях в Метрику.
В Метрике нет когортного анализа, нет инструментов исследования атрибуции конверсий и прочих полезных отчетов.
Если вы используете Adwords, то данные о рекламных кампаниях вы не сможете импортировать в Метрику.
Метрика плохо интегрируется с Google Tag Manager, вам будет неудобно настраивать передачу целей и конверсий с фронтенда.
Из Метрики неудобно выгружать данные в инструменты визуализации данных Power BI или Google Data Studio.
Метрика была и остается «деревянным» инструментом, вы будете встречать сопротивление на каждом шагу, профессионалы работают в Google Analytics.

Мы используем ручные, а порой и примитивные методы, автоматизация будет рассмотрена в следующих статьях, если у аудитории будет интерес.

Импорт площадок из UTM-меток в Google Analytics

Нам нужно в Analytics видеть название площадки и конверсии. Яндекс Директ во время клика по объявлениям умеет передавать названия РСЯ площадки в UTM-метку через динамический параметр {source}, мы можем использовать эту возможность.

Вставлять {source} в utm_content ссылок объявлений

Стандартные UTM-метки имеют следующую структуру:

site.ru?utm_medium=тип трафика: платный|не платный

&utm_source=сайт, на котором размещается ссылка

&utm_campaign=название рекламной кампании

&utm_term=ключевое слово|тема поста

&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}.

На выходе мы получим вот такую структуру отчета (рассмотрим на примере из Яндекс Метрики, так как в ней структура нагляднее).

То есть сначала идет название источника трафика (utm_source=yandex), затем тип канала трафика (utm_medium=cpc), название рекламной кампании (utm_campaign=название|{campaign_id}), ключевое слово (utm_term={keyword}) и содержание объявления (utm_content=…).

Как вы могли заметить, в utm_content мы добавляем все, что не уместилось в остальные метки — просто для того, чтобы это сохранилось в Google Analytics и при необходимости мы могли этим воспользоваться.

В Google Analytics мы можем построить аналогичного вида Custom Report (Специальный отчет):

Вместо KPI вы можете указывать цели, в нашем случае KPI — это вычисляемая метрика (calculated metric). И получить следующие значения на уровне Ad Content (utm_content=…)

Вы можете заметить, что вместо src|{source_type}_{source} в utm_content подставляются тип площадки (context в случае РСЯ или search в случае поиска) и домен площадки — отлично, теперь они у нас есть в Google Analytics, как их использовать?

Здесь небольшое разочарование — нам придется выгрузить площадки в Google Sheets и свести их вручную. Далее мы рассмотрим автоматический способ, чтобы смотреть все данные в интерфейсе, но часто бывает, что они есть только в utm_content и с этим тоже нужно что-то делать.

Выгружаем отчет в Google Sheets

В настройках ранее созданного отчета меняем тип на Flat Table (Плоская таблица):

Получаем все параметры по строкам:

Выгружаем отчет прямо в Google Sheets, но перед выгрузкой выбираем, сколько строк хотим выгрузить, так как он выгружает только то, что видно в интерфейсе. Если у вас >5000 строк, то придется выгружать по отдельности и потом соединять, можно и через API, но эта история сложнее — легче вручную.

И экспортируем:

Получаем вот такой отчет:

Теперь нам нужно сделать новый столбик с названием РСЯ. В него извлекаем домен площадки с помощью функции REGEXEXTRACT из столбика Ad Content (utm_content):

=REGEXEXTRACT(ячейка, регулярное выражение)

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

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

Теперь выделяем всю таблицу и создаем на основе нее сводную таблицу (pivot table):

Используем вот такие настройки сводной таблицы:

Внутри настроек Value (Значения) мы создали новую колонки Calculated Field 1, в котором вывели конверсию, поделив KPI на Sessions. Для наглядности добавляем форматирование по градиенту (Формат > Условное форматирование):

Теперь вы видим конверсию по площадкам РСЯ, можно добавить названия рекламных кампаний в блок Rows, чтобы посмотреть, как ведет себя площадка в разрезе рекламных кампаний:

Все вроде бы круто, но мы не видим стоимость конверсий, это важно, так как мы можем иметь конверсию 1%, но получать ее за 100 рублей и это будет выгодно, а можем иметь конверсию 10%, но ее стоимость будет 5000 рублей, это невыгодно.

Выгрузка расходов по площадкам РСЯ

Рассмотрим самый простой способ — выгрузим расходы прямо из интерфейса Яндекс Директ в Excel. Для этого в мастере отчетов Яндекс Директ настроим отчет за тот же период, что мы выгружали площадки из Google Analytics.

Если у вас очень много строк, например 100 тысяч, можете ограничить выгрузку следующими условиями, чтобы браузер не подвисал:

Выгрузим отчет в Excel и загрузим его в Google Sheets на отдельную вкладку:

Теперь нам нужно подтянуть данные о расходах в таблицу с площадками Google Analytics, сделаем это с помощью функции VLOOKUP (ВПР), но сначала форматируем значения.

Google Sheets не может прочесть наши числа (в английской версии, которую я использую), так как они с запятой, — нужно удалить запятую и знаки после нее. Нажимаем CTRL+F (CMD+F), нажимаем на галочку «Search using regular expression» (Искать, используя регулярное выражение) и вводим \,.* , далее нажимаем Replace all (Заменить все):

Еще момент, площадки имеют разный формат названий в Google Analytics и Яндекс Директ, для этого удалим «www.» из обеих таблиц и приставки context_ и search_.

Теперь подтягиваем данные по расходам площадок из текущей таблицы в таблицу Google Analytics. Сложность в том, что у нас есть еще и кампании, то есть у нас строки c площадкой Авито дублируются для каждой кампании и нам нужно подтянуть данные не только по площадке Авито, а по площадке Авито определенной кампании.

Для этого создаем новый столбик, который будет уникальным в двух таблицах, это ID кампании + название площадки.

Но сначала извлекаем номер кампании из utm_content, как делали ранее с названием площадки регулярным выражением .*cid\|([^\|]*).* .

Затем сцепляем столбик с кампанией и названием площадки в новой колонке.

Аналогично делаем в таблице Яндекс Директ, но обратите внимание, делать это нужно в первой колонке.

И теперь момент истины — подтягиваем расходы в таблицу Google Analytic с помощью функции VLOOKUP (ВПР):

Из чего состоит функция VLOOKUP:

=VLOOKUP(Какое значение искать в таблице, Таблица, в которой искать значение, Номер колонки, из которой подтягивать значение из строки, в которой было найдено значение, Просто ставим 0)

Проще говоря, мы берем наш уникальный key и ищем его в таблице Яндекс Директ в первой колонке, если нашли, то из этой строки забираем значение, которое нам нужно, с помощью указания номера колонки.

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

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

Фильтруем строки, где данные не подтянулись, например, добавляем в сводную таблицу фильтр по кликам и убираем галочку #N/A (Не найдено):

В итоге получается аккуратная таблица:

Какие выводы можно сделать из этой таблицы

Глубоко разбирать не будем, так как оптимизация РСЯ — это отдельная тема, перечислим основное.

У площадок разная конверсия и CPO, но мы не можем назначать разные ставки на уровне площадок. Так что можно создать 2 отдельные кампании, с помощью запрещенных площадок отсечь их друг от друга и назначить разные ставки, при которых CPO будет оптимальным.
Явно отрицательные площадки добавляем в запрещенные площадки в настройках кампании.
Есть гипотеза, что если вместо ключевого слова указать titile РСЯ-площадки, то показов на ней будет в разы больше.

Внимание: если в комментариях будет 3 голоса за продолжение темы, агентство контекстной рекламы 1jam.ru продолжит тему и расскажет про автоматические методы.