Большую часть времени специалиста по контекстной рекламе занимает сбор и анализ отчетов из Яндекс.Директа, Яндекс.Метрики, 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 таблицы справа от основной: Сцепка+Клики, Сцепка+Показы, Сцепка+СРС:

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

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

Теперь на лист «Выгрузка с 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, расхода и среднего чека желательно произвести вручную: только тогда вы будете уверены в правильности результатов. Также можно добавить временную шкалу, любой срез по таблице, парочку интересных сводных или обычных диаграмм.

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

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