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

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

 

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

 

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

 

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

 

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

 

 

В коде меняем название будущего файла, номер кампании, диапазон дат, метрики (все метрики можно найти на сайт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 таблицы справа от основной: Сцепка+Клики, Сцепка+Показы, Сцепка+СРС.

 

 

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

 

 

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

 

 

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

 

=ВПР($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)

 

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

 

Доверительный интервал по кликам

 

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

 

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

 

 

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

 

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

 

 

Порог:

 

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

 

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

 

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

 

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

 

 

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

 

Автор:Лина Хорунжая(Специалист по контекстной рекламе)

Самый простой и быстрый способ понять, можем ли мы работать вместе - пообщаться:)
Оставь свои контакты - я позвоню, обсудим все детали твоего проекта.
Нужные услуги
Если хочешь оставить действительно крутой и многим людям полезный отзыв -
Ответь вот на эти вопросы
Я не гарантирую ничего. Никому и никогда.

Это приходится объяснять всем клиентам: кто-то хмыкает и уходит на FL.RU, а кто-то немного подумав, возвращается.

Пример

Ты приходишь к врачу, он настоящий профи (ты его долго искал, ждал приёма полгода, заплатил тучу денег). Он - тот, кто тебе нужен ( у него много счастливых здоровых пациентов, он обладает эксклюзивными знаниями и все стены завешаны сертификатами и дипломами). Но будет ли он гарантировать тебе, что вылечит всё на свете? - Ну не будет! Но он точно сделает Всё, что в его силах - и этого достаточно. Я отношусь к своей профессии так же - я не могу наобещать клиенту золотые горы, но я применю ВСЕ свои знания, навыки и опыт. Я буду стараться при работе с каждым проектом в одинаковой степени. Каждый раз я выкладываюсь на полную. А если знаю, что выложиться не смогу или не захочу - я отказываюсь.

Я могу взять на себя ответственность за результаты только при условии, что я становлюсь совладельцем компании, с процентом от прибыли и всеми полномочиями. А как иначе? Недавно у меня появился интересный клиент с задачей сделать лендинг. Клиент не из России, с другим мышлением и менталитетом. На минуточку, лендинг у меня стоит 150к. И он спрашивает: «Давид, у тебя крутые работы. А я могу заплатить тебе в 2 раза больше, чтоб ты еще больше постарался?». Ты понял, что произошло? Человек попросил взять больше его денег. Я первый раз такое вижу. Я ему отказал. Я взял ровно столько, сколько беру с других клиентов, потому что выложиться больше, чем на 100% я не смогу. Правда, я сказал ему, что он может заплатить больше, если захочет. Но только когда работа будет доделана и сдана.

Но мне продолжают звонить предприниматели, которые такие : «Огооо, 150 тысяч. А что ты гарантируешь? Ничего? Пффф. Давай пока. А нет, стой… Может подешевле, а?».

Нет, не подешевле.
Нет, никаких гарантий.

Самый простой и быстрый способ понять, можем ли мы работать вместе - пообщаться:)
Оставь свои контакты - я позвоню, обсудим все детали твоего проекта.
Спасибо!

Я свяжусь с тобой в ближайшее время

Для получения доступа необходимо войти в личный кабинет
Получить доступ при помощи Вконтакте Получить доступ при помощи Facebook