Как контролировать весь отдел PPC и видеть полную картину в одном отчёте Power BI

От редакции: Статья просто отличная. Поэтому не удержались и сделали репост. Пусть все хорошее и полезное будет в нашем блоге. Тема контроля и отчетности актуальна всегда.

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

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

Какие задачи стояли перед нами:

• реализовать просмотр динамики по основным показателям с различных аккаунтов Google AdWords в одном месте для более удобного контроля руководителем отдела;

• реализовать возможность быстро отвечать на вопросы и генерировать отчеты для клиента по основным показателям;

• реализовать поиск слабых мест в работе аккаунтов в графическом виде.

Также в ходе работы с проектом нам удалось решить еще две важных задачи, о которых более подробно в конце статьи.

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

• Google Spreadsheets — для хранения части данных для отчета с возможностю корректировки (информация о PPC-специалисте и аккаунт-менеджере);

• пакет R RAdWords — для загрузки данных с Google AdWords;

• пакет R googlesheets — для загрузки данных с Google Spreadsheets;

• пакет R RMySQL — для работы с базами данных MySQL;

• база данных MySQL — для хранения данных с AdWords;

• Power BI — для визуализации данных и формирования удобных отчетов.

Все работы мы разделили на три логических этапа:

1. Сбор необходимых данных

2. Визуализация полученных данных

3. Настройка автообновления отчета

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

1. Общий отчет по всем рекламным кампаниям по следующим показателям:

• Показы

• Клики

• CTR

• CPC

• Затраты

• Валюта аккаунта

• Количество конверсий

• Коэффициент конверсии

• Стоимость конверсии

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

• PPC-специалистам;

• аккаунт-менеджерам;

• рекламным аккаунтам (отдельно стоит упомянуть о детализации вглубь аккаунта Кампания > Группа объявлений > Ключевое слово);

• статусам кампаний;

• типам устройств;

• типам сетей;

• и ярлыкам.

2. Сравнительный отчет по рекламным кампаниям

Отчет, аналогичный предыдущему, за исключением одного важного момента — в нем можно сравнивать два любых периода (подробнее о том, как это реализовать читайте ниже). Показатели и фильтры такие же как и в первом отчете.

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

Дополнительным бонусом для удобства является возможность просмотреть эти же показатели вплоть до ключевых слов. Все эти данные также представлены в диаграммах и графиках.

А теперь поговорим о том, как все это было реализовано.

Техническая реализация проекта

Этап 1. Сбор необходимых данных

Для построения отчетов нам необходимы были следующие данные с AdWords:

• Название аккаунта

• Название кампании

• Название группы объявлений

• Статус кампании

• Ключевое слово

• Тип устройства

• Тип сети

• Показы

• Клики

• Затраты

• Валюта аккаунта

• Конверсии

• Ярлыки

Для получения этих данных мы воспользовались R пакетом RAdwords. Написали небольшую функцию, которая на входе принимает ID аккаунта Google AdWords, а на выходе отдает данные по этому аккаунту. Пример кода ниже:

Более подробно о работе с пакетом можно почитать в официальной документации.

Кроме этих данных нужно было также получить данные о PPC-специалисте и аккаунт-менеджере, которые ведут аккаунт. Для этого мы создали в Google Spreadsheets таблицу со следующим шаблоном:

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

где вместо your_key нужно ввести ваш ключ. Где его взять показано на скриншоте.

Более подробно об использовании пакета googlesheets можно почитать в официальной документации.

После того, как мы загрузили нужные нам данные в R, их нужно куда-то сохранить. Для решения этой задачи мы решили использовать базу данных MySQL у нас на хостинге.

Для загрузки данных в БД использовали пакет R RMySQL. Пример кода ниже.

где вместо значений «yourdbname», «youruser», «yourpassword», «yourhost” и «yourtable» нужно ввести свои значения.

Вроде как все хорошо, и все нужные данные уже есть в нашей базе данных, но нам нужно было реализовать возможность загрузки новых данных каждый день. Чтобы не подгружать каждый день данные за весь период мы написали небольшой код, который определяет последнюю дату с данными в БД и подгружает только недостающие данные. Если по определенному ID данные и вовсе отсутствуют, то данные загружаются с определенной даты указанной в скрипте.

##Получаем данные о последней дате с данными и определяем дату начала подгрузки данных

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

Этап 2. Визуализация полученных данных

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

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

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

Как использовать пользовательские визуализации в Power BI

В Power BI множество стандартных визуализаций, но иногда их недостаточно для реализации ваших целей. В таких случаях вам помогут пользовательские, которые можно скачать по ссылке в Office Store. Для нашего отчета мы использовали визуализацию Smart Filter by OKViz. Для использования в Power BI её нужно сначала добавить, для этого нужно нажать на троеточие в панели визуализаций и выбрать пункт “Импортировать из файла”.

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

Как реализовать сравнение произвольных периодов в Power BI

В нашем отчете есть вот таких два ползунка:

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

Создать две новые таблицы с периодами, которые вы хотите использовать для сравнения. В нашем случае мы хотели сравнивать данные любых произвольных периодов, за которые у нас были данные.

Для создания этих таблиц можно воспользоваться функцией List.Dates.

Пример кода для создания такой таблицы ниже:

Такой код создает таблицу с датами с 01-06-2017 по сегодняшний день. После этого нужно соединить таблицы с периодами, назовем их условно DimDate и DimDate Compare, с нашими основными данными.

При этом связь с таблицей DimDate делаем активной, а связь с таблицей DimDate Compare нет.

После этого остается только создать соответствующие меры (рассмотрим на примере данных по кликам):

• для расчета кликов за текущий период:

• для расчета кликов за период для сравнения:

• для сравнения данных за периоды:

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

Этап 3. Настройка автообновления отчета

• Автообновление данных в БД

Так как данные в БД мы передаем с помощью R-скрипта, то все, что нужно сделать это настроить запуск скрипта по расписанию. Здесь особо останавливаться не будем, так как у Анастасии Каминской есть отличная статья на эту тему.

• Автообновление данных в Power BI

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

После этого переходим в онлайн версию Power BI в раздел Настройки > Управление шлюзами:

Заполняем необходимые данные и нажимаем “Добавить источник данных” как показано на скриншоте:

В появившимся окне заполняем все необходимые даные и нажимаем кнопку “Добавить”.

После того как источник данных создан, нужно:

• перейти в настройки наборов данных (1),

• выбрать нужный набор данных (2),

• в пункте “Подключение шлюза” указать ваш шлюз (3),

• в пункте “Запланированное обновление” указать дни и время для обновления данных (4)

ВАЖНО! При настройке автообновления делайте так, чтобы сначала данные обновлялись в БД MySQL, а потом уже обновленные данные подтягивались в Power BI.

Что мы получили в результате всей проделанной работы?

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

• мы обеспечили быстрый доступ к статистике AdWords по всем аккаунтам без предоставления доступов в AdWords для аккаунт-менеджеров;

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

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

От редакции: главное после прочтения статьи что? Правильно! Внедрять. А если у вас есть хорошие идеи, кейсы, гипотезы — смело пишите главреду по адресу zaiats@web-promo.ua. Будем публиковать.