Как сделать dashboard для мониторинга контекстной рекламы с помощью Power BI

Автор: Соболев Денис, руководитель отдела аналитики digital-агентства webit

Сейчас на рынке очень популярен такой инструмент, как Power BI. Его применяют в самых разных областях, в том числе и в интернет-маркетинге. Мы решили рассказать, как его можно использовать для работы с показателями контекстной рекламы на примере выгрузки статистики из рекламной системы Яндекс.Директ и Google Analytics.

Получение данных через API из необходимых источников

Для того чтобы собирать статистику, сначала ее необходимо получить. Давайте начнем с Яндекс.Директа.

Яндекс.Директ

Для получения статистики из Яндекс.Директа, проще всего использовать API Яндекс.Метрики. Для этого мы используем уже готовый Power Query скрипт Максима Уварова.

Для простоты можно скачать уже готовый Power BI файл c последней версией коннектора.

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

Для начала надо понять, какие именно параметры и метрики мы хотим получить.

Полный список можно посмотреть здесь.

Для Яндекс.Директа нас интересуют следующие параметры (dimensions): ym:ad:date,ym:ad:directOrder,ym:ad:directPhraseOrCond и метрики (metrics): ym:ad:clicks,ym:ad:RUBAdCost

То есть мы будем выгружать дату, название кампании, условие показа, клики и стоимость.

Давайте настроим сам BI файл. Для этого сначала получаем токен:

А также находим id нужного нам счетчика метрики:

Теперь заполняем все эти данные в BI-файл. Для начала вызываем функцию getDirectIDs:

В ответ получает ID нашего аккаунта Яндекс.Директе:

Теперь у нас есть вся необходимая информация, чтобы выгрузить статистику. Выбираем функцию PQYM, заполняем поля и нажимаем «Вызывать»:

В результате мы получаем статистику по нашим рекламным кампаниям:

Далее нам необходимо преобразовать эту информацию для дальнейшей работы:

1) Указать тип данных в каждом столбце.

2) Убрать номера РК, оставить только названия.

3) Убрать минус-слова и кавычки из условий показа.

4) Переименовать столбцы для более удобной работы с ними и дальнейшего сведения, чтобы не было путаницы.

Статистика по Яндекс.Директу готова, можем двигаться дальше. В целом, алгоритм получения статистики из Яндекс.Метрики аналогичен, просто применяем другие показатели и метрики при вызове функции.

Google Analytics

Для оценки эффективности клиентских рекламных кампаний мы используем данные по достижению целей из Google Analytics. Давайте рассмотрим, как их можно получить через API.

NB: В Power BI есть встроенный коннектор для Google Analytics, но в нем присутствует проблема семплирования данных на больших объемах, так что мы используем сторонний вариант чтобы ее избежать.

Первым делом необходимо установить нужный аддон для Google Spreadsheets:

Находим нужный нам аддон и устанавливаем его:

Следующим этапом формируем конфигурационный файл для отчета:

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

1) ID представления в Google Analytics, его можно найти в настройках представления

2) Необходимые параметры и метрики для выгрузки

3) Временной отрезок, за который выгружаются данные

Аддон позволяет выгружать несколько отчетов, например, достижения по целям и расход из Google Adwords, но нам сейчас нужен только один, заполняем его по примеру:

Как видно, здесь у нас присутствуют такие метрики, как ga:goal1Completions, это как раз данные по достижению целей. Вместо цифры 1 необходимо указать номера нужных вам целей, они есть в настройках Google Analytics:

После того, как данные будут заполнены, запускаем отчет:

В итоге мы получаем всю статистику по достижению целей на отдельной вкладке:

Не забываем настроить автообновление раз в сутки:

Самое сложное осталось позади, теперь добавляем наши данные в Power BI. Для этого публикуем их по ссылке:

Полученную ссылку добавляем в Power BI:

Получаем в итоге таблицу:

Преобразовываем ее в следующем порядке:

1) Удаляем верхние 14 строк

2) Используем первую строку как заголовки

3) Устанавливаем типы данных для столбцов

4) Переименовываем столбцы

5) Складываем информацию по всем достижениям целей

На выходе получаем таблицу, аналогичную статистике из Яндекс.Директа.

Небольшое отступление:

Если при настройке отчета Big Analytics Data использовать следующие настройки, то можно получить статистику по расходам Google Adwords, если между Adwords и Analytics установлена связь:

Сведение данных в общую таблицу

Теперь нам нужно для работы объединить запросы:

Данные по столбцам, название которых совпадает, сведутся автоматически, отсутствующие данные заполняются значением null, которое лучше заменить на ноль:

Все, данные собраны и подготовлены, можно переходить к визуальной части.

Вспомогательные данные

Сегменты кампаний

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

Для создания сегментов берем нашу таблицу с данными из Яндекс.Директа и преобразовываем ее:

1) Удаляем ненужные столбцы

2) Удаляем дубликаты

3) Разделяем столбец с названиями РК по разделителю “_”

4) Переименовываем столбцы для удобства, оставив необходимые

На выходе получаем такую таблицу:

Работа с датами

Часто возникает ситуация, что нам необходимо смотреть динамику по месяцам или неделям, для того чтобы анализировать текущую ситуацию. Средствами Power BI это реализовывается не очень удобно, так что мы сделали вспомогательную таблицу:

Далее, аналогично таблице с данными из Google Analytics мы добавляем ее в наш BI файл и немного преобразовываем, добавляя в нее месяц:

Визуальная часть

Установление связей

Первое, что нам необходимо сделать - установить связи между нашими таблицами:

Это позволит нам строить фильтры для визуализаций более гибко.

Создание мер

Теперь давайте подумаем, каких метрик нам не хватает для отслеживания эффективности рекламных кампаний. Мы выделили что дополнительно хотим видеть CPA и конверсию, создаем их:

1) Считаем сумму обращений:

2) Считаем конверсию:

3) Считаем CPA:

Создание визуализаций

Давайте сделаем общий dashboard, который нам позволит мониторить текущую ситуацию по клиенту в целом:

Так как реклама запущена недавно, то статистики по клиенту не очень много, но мы наглядно видим текущую ситуацию.

Не забываем добавить разные уровни даты для удобства переключения на графиках:

Переходя на разные уровни мы можем оценить динамику:

Дополнительно построим матрицу с информацией по кампаниям и ключевым словам:

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

Результат работы

И в завершение статьи мы публикуем данную визуализацию, с которой можно повзаимодействовать динамически и посмотреть ее возможности (данные предоставлены тестовые и не являются реальными).

Ссылка на просмотр, если отключены iframe:

https://app.powerbi.com/view?r=eyJrIjoiYjdkZTdhN2MtZDY3OC00YTYxLWExNjAtNzNhYzBjNDQyODA4IiwidCI6ImNlZjk4ZTU4LTgxZjctNDE5NC1iZTgyLWY2M2E3ZGU4YTdhZSIsImMiOjl9

https://app.powerbi.com/view?r=eyJrIjoiYjdkZTdhN2MtZDY3OC00YTYxLWExNjAtNzNhYzBjNDQyODA4IiwidCI6ImNlZjk4ZTU4LTgxZjctNDE5NC1iZTgyLWY2M2E3ZGU4YTdhZSIsImMiOjl9
preview Как Google определяет страницы низкого качества

Как Google определяет страницы низкого качества

Какие факторы Google принимает во внимание, оценивая качество страниц, и как определить страницы низкого и высокого качества самостоятельно...
preview О чем говорить со специалистом по SEO. Инструкция для клиентов

О чем говорить со специалистом по SEO. Инструкция для клиентов

Существуют такие аспекты оптимизации, о которых знают толковые сеошники, но не подозревают их клиенты...
preview JavaScript и SEO: сканирует ли Google JavaScript?

JavaScript и SEO: сканирует ли Google JavaScript?

По мере эволюции интернета растёт спрос на сайты, имеющие всё более интерактивную функциональность...
preview Особенности SEO для сайтов-агрегаторов

Особенности SEO для сайтов-агрегаторов

Статья будет полезна тем, кто работает с агрегаторами и другими сайтами, на которых представлены сотни или тысячи предложений: по недвижимости, путешествиям, билетам, товарам и...
preview Алгоритм «Королёв» - очередная порка сеошников откладывается

Алгоритм «Королёв» - очередная порка сеошников откладывается

Как никогда весь «сеошный» мир ждал запуска нового алгоритма ранжирования, анонсированного на 22 августа 2017 года
preview 18 SEO-мифов, о которых нужно забыть

18 SEO-мифов, о которых нужно забыть

SEO-специалисты и маркетологи всего мира меняют свои приоритеты, отказываясь от одержимости линкбилдингом и ключевыми словами, и все больше обращая внимание на качественный...