Как свести данные из Яндекс.Директа и Google AdWords в едином отчете: инструкция26.01.2017 15:33

Инструменты и формулы для создания единого сводного отчета для эффективного анализа рекламной кампании

Дата публикации: 26.01.2017

Большую часть времени специалиста по контекстной рекламе занимает сбор и анализ отчетов из Яндекс.Директа, Яндекс.Метрики, Google AdWords и Google Analytics. Некоторые инструменты хороши для быстрого просмотра статистики, другие позволяют анализировать данные и даже прогнозировать их в пределах погрешности. Но когда аккаунт большой, а статистики очень много, возникает много новых вопросов. Как проанализировать свою рекламную кампанию? Как сопоставить данные по кликам и показам? Как обойти сэмплирование данных в Google Analytics? Какие инструменты лучше всего использовать для анализа эффективности? Давайте разберемся вместе.

Метод, который мы будем использовать для анализа аккаунта, несложный. Главное — запомнить все шаги и быть внимательными с формулами.

Легче всего работать со средой разработки RStudio. С ее помощью можно выгрузить все данные по клиенту за определенный период. При этом очень важно до того, как вставлять код в программу, указать адрес папки, в которую будет закачиваться файл.

Необходим сайт, мобильное приложение, услуги по SEO или контекстной рекламе? Тендерная площадка WORKSPACE поможет выбрать оптимального исполнителя. База проекта насчитывает более 10 500 агентств. Сервис работает БЕСПЛАТНО как для заказчиков, так и для исполнителей.

Адаптация кода

Вставляем кусочек кода в пустое окно слева:

В коде меняем название будущего файла, номер кампании, диапазон дат, метрики (все метрики можно найти на сайтe для разработчиков инструментов Google Analytics):

install.packages("devtools")
library(devtools)
install_github("rga", "skardhamar")
library(rga)
options(RCurlOptions = list(cainfo = system.file("CurlSSL", «cacert.pem», package = "RCurl")))
Sys.setlocale("LC_CTYPE", "russian»")
rga.open(instance="ga")

//название_файла.csv
ga.data = ga$getData("ga:12345678", batch = TRUE, walk = TRUE, "2016-11-01","2016-12-01",
metrics = "ga:impressions,ga:adclicks,ga:adcost,ga:transactions,ga:sessions,ga:transactionRevenue",
dimensions = "ga:date,ga:campaign,ga:addestinationurl,ga:adContent",
sort = "", filters = "", segment = "")
write.csv(ga.data, file = "название_файла.csv")

Ставим курсор перед первым словом кода и нажимаем Ctrl+Enter. В нижнем окне начнут добавляться данные, которые мы ранее отметили для скачивания в коде. Нужно повторять комбинацию до тех пор, пока на строке «rga.open…» нас не перекинет в браузер, где нужно будет войти в аккаунт клиента. Тогда копируем код и вставляем его как это показано на скрине, и нажимаем Enter:

Почти все операции проделаны, осталось только скачать данные. Для этого копируем участок кода, как это показано на скриншоте далее, вставляем его в нижнее окно, после чего нажимаем Enter:

Отчет начнет скачиваться. Когда все готово, переходим ко второму этапу.

Приводим отчет в божеский вид

Отчет скачивается в нечитаемом виде. Зато полностью и за весь заданный период. Конечно, можно скачать его прямо из Google Analytics, но наш способ позволяет обойти проблему сэмплирования данных — при большом объеме статистики Google Analytics начинает строить отчеты не по всем данным, а только части из них. Для целей глубокого анализа кампаний это, конечно, неприемлемо.

Чтобы привести наш отчет в рабочий вид, мы выделяем первый столбец и разделяем данные по запятым.

На отдельный лист, назовем его «Выгрузка Директ», скачиваем отчет по кликам, показам и средней стоимости клика с Яндекс.Директа с помощью «Мастера Отчетов». Важно: в нем должна быть информация о номерах кампании, объявления, фразы. Не забудьте выбрать правильный диапазон дат, совпадающий с диапазоном из Google Analytics.

Чтобы было удобнее, не забывайте называть листы в Excel понятными и содержательными именами. Иначе в конце вы просто запутаетесь в их нумерации.

Выгрузка с GA

На листе «Выгрузка из GA» выделяем данные из содержания объявлений в отдельные столбцы. Это необходимо, чтобы структурировать весь имеющийся массив данных (в моем примере это ячейка Е2):

Запрос — копируем содержание объявлений → Ctrl+F → |* меняем на «Пусто»; Ctrl+F → . меняем на «Пусто».

№ Кампании:

=ЕСЛИОШИБКА(ПСТР($E2;ПОИСК("|c";$E2)+3;8);"")

ГЕО (ссылка на ячейку с названием кампании):

=ЕСЛИ(ЕЧИСЛО(ПОИСК("msk";C2));"Москва";ЕСЛИ(ЕЧИСЛО(ПОИСК("spb";C2));"Санкт Петербург";ЕСЛИ(ЕЧИСЛО(ПОИСК("reg";C2));"Регионы";ЕСЛИ(ЕЧИСЛО(ПОИСК("rus";C2));"Россия";"нет"))))

№ Группы:

=ЕСЛИОШИБКА(ПСТР($E2;ПОИСК("|b";$E2)+3;10);"")

№ Фразы:

=ЕСЛИОШИБКА(ПСТР($E2;ПОИСК("|k";$E2)+3;10);"")

Тип площадки:

=ЕСЛИ(ЕЧИСЛО(ПОИСК("|st:sea";$E2));"поиск";ЕСЛИ(ЕЧИСЛО(ПОИСК("source_type";$E2));"нет";ЕСЛИ(ЕЧИСЛО(ПОИСК("|st:cont";$E2));"контекст";"нет")))

ДРФ:

=ЕСЛИ(ЕЧИСЛО(ПОИСК("|a:no";$E2));"no";ЕСЛИ(ЕЧИСЛО(ПОИСК("|a:yes";$E2));"yes";""))

Площадка:

=ЕСЛИОШИБКА(ЛЕВСИМВ(ЕСЛИ(ЕЧИСЛО(ПОИСК("|s:no";$E2));"no";ЕСЛИ(ЕЧИСЛО(ПОИСК("|s:{";$E2));"no";ЕСЛИ(ЕЧИСЛО(ПОИСК("test";$E2));"no";ПСТР($E2;ПОИСК("|s:";$E2)+3;99999))));ПОИСК("|";ЕСЛИ(ЕЧИСЛО(ПОИСК("|s:no";$E2));"no";ЕСЛИ(ЕЧИСЛО(ПОИСК("|s:{";$E2));"no";ЕСЛИ(ЕЧИСЛО(ПОИСК("test";$E2));"no";ПСТР($E2;ПОИСК("|s:";$E2)+3;99999)))))-1);"no")

Сцепка: Дата+Запрос+№ Кампании+№Группы

Пока с этой вкладкой закончили, переходим к следующей.

Выгрузка с Директа

Меняем формат Даты:

=ДАТА(ПРАВСИМВ(A6;4);ПСТР(A6;4;2);ЛЕВСИМВ(A6;2))

Копируем столбец с Запросами и вставляем в любую «Транслитерацию фраз» (рус-англ). Далее вставляем этот список в столбец справа и также ».» меняем на Пусто. Точки в ключевых фразах могут появиться, если у вас есть операторы в ключе.

Удаляем в столбце »№ Объявления» все буквы и знаки до первой цифры.

Создаем Сцепку — ДатаНовая+Запрос+№ Кампании+№ Объявления:

Далее создаем 3 таблицы справа от основной: Сцепка+Клики, Сцепка+Показы, Сцепка+СРС:

Создаем сводную таблицу для каждой из них, можно поместить их рядом. Это нужно, чтобы собрать статистику одного конкретного запроса за определенный день:

Копируем столбцы со «Сводными таблицами» и вставляем только «Значения»:

Теперь на лист «Выгрузка с GA» можно добавить столбцы с нужными формулами. В формулах ниже Т2 — это ячейка «Сцепки»:

Показы Директ:

=ВПР($T2;’Выгрузка с Директа’!$R$6:$S$164543;2;ЛОЖЬ)

Клики Директ:

=ЕСЛИОШИБКА(ВПР($T2;’Выгрузка с Директа’!$X$6:$Y$164543;2;ЛОЖЬ);0)

СРС Директ:

=ВПР($T2;’Выгрузка с Директа’!$AC$6:$AD$164543;2;ЛОЖЬ)

CTR:

=ЕСЛИОШИБКА(V2/U2;0)

CPO:

=ЕСЛИОШИБКА(AA2/I2;0)

CR:

=ЕСЛИОШИБКА(I2/V2*100;0)

Расход&:

=ЕСЛИОШИБКА(V2*W2;0)

ROI:

=(K2-AA2)/AA2

Средний чек:

=ЕСЛИОШИБКА(AA2/I2;0)

Чтобы сделать качественный отчет, нужно рассчитать пороговое значение для каждой кампании. Об этом я уже рассказывала в одной из прошлых статей. Для этого берем коэффициент конверсии по каналу, гео или площадке за весь или конкретный диапазон дат.

Доверительный интервал по кликам = 1/CR*100×3

Это покажет нам, какие кампании достигли порогового значения по кликам, соответственно, их можно анализировать.

Создаем сводную из листа «Выгрузка с GA» (Название кампаний + Клики Директ):

По-разному помечаем кампании, которые достигли и не достигли порогового значения. Например: «Достигли порогового значения», «Не достигли порогового значения».

После этого добавляем еще один столбец на листе «Выгрузка с GA»:

Порог:

=ВПР($C2;Порог!$E$4:$F$105;2;ЛОЖЬ)

Где C2 — это ячейка с названием кампании.

Когда мы все это сделали, можно приступать к общей сводной таблице. Для этого выделяем всю таблицу на листе «Выгрузка с GA» и создаем сводную. Выбираем те KPI, которые будем анализировать. Расчет CTR, CPO, расхода и среднего чека желательно произвести вручную: только тогда вы будете уверены в правильности результатов. Также можно добавить временную шкалу, любой срез по таблице, парочку интересных сводных или обычных диаграмм.

Поздравляю — теперь можно приступать к анализу полученных данных!

Отчет трудоемкий, но результат стоит вложенных усилий. Надеюсь, этот метод будет полезен как начинающим специалистам, так и «матерым волкам» контекстной рекламы.

Полный текст статьи читайте на CMS Magazine