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

как-контролировать-весь-отдел-PPC_03

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

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

Только полезная информация

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

  • реализовать просмотр динамики по основным показателям с различных аккаунтов 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-специалистам;
  • аккаунт-менеджерам;
  • рекламным аккаунтам (отдельно стоит упомянуть о детализации вглубь аккаунта Кампания > Группа объявлений > Ключевое слово);
  • статусам кампаний;
  • типам устройств;
  • типам сетей;
  • и ярлыкам.

Отчет по аккаунтам AdWords в Power BI

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

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

Пример отчета со сравнением в Power BI

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

Таблица по сравнению данных за разные периоды

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

Таблица по сравнению данных по ключевым словам за разные периоды

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

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

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

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

  • Название аккаунта
  • Название кампании
  • Название группы объявлений
  • Статус кампании
  • Ключевое слово
  • Тип устройства
  • Тип сети
  • Показы
  • Клики
  • Затраты
  • Валюта аккаунта
  • Конверсии
  • Ярлыки

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

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

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

Пример отчета в Google Spreadsheets

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

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

Ключ Google Spreadsheets

Более подробно об использовании пакета 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

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

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

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

Сравнение периодов в Power BI

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

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

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

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

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

Построение связей между таблицами в Power BI

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

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

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

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

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

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

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

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

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

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

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

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

Настройка автообновления в Power BI (шаг 1)

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

Настройка автообновления в Power BI (шаг 2)

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

Настройка автообновления в Power BI (шаг 3)

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

  • перейти в настройки наборов данных (1),
  • выбрать нужный набор данных (2),
  • в пункте “Подключение шлюза” указать ваш шлюз (3),
  • в пункте “Запланированное обновление” указать дни и время для обновления данных (4)

Настройка автообновления в Power BI (шаг 4)

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

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

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

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

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

Если хотите видеть больше таких статей на нашем блоге, не забывайте ставить лайки 😉

  • Игорь

    Сложновато как-то, но интересно

  • https://maxt.pro/ Макс Терещенко

    Самое простое решения для клиентов отчеты в Google Data Studio. А по Power BI на Analyze! был хороший пример в Алексея Селезнёва с Netpeak

    • Maks Hapchuk

      Data Studio хороший инструмент, но для решения наших задач он не подходил, в основном из-за ограниченности функционала: Data Studio работает только с данными с сервисов Google, а мы планируем дополнять отчет данными с Facebook, Yandex Direct и т.д.

      У Алексея очень хороший пример, но он не совсем подходил для решения именно наших задач, поэтому мы немного изменили подход и опубликовали свое решение :)

      • https://maxt.pro/ Макс Терещенко

        Спасибо за это! Хотя данные с Facebook, Yandex Direct также можно добавлять в Data Studio.

        • Maks Hapchuk

          Максим, спасибо за эту информацию, когда я начинал работать с этим инструментом такой возможности не было.
          А когда начал работать с Power BI, то и желания возвращаться на Data Studio не было уже.

  • apipko

    Ребята, подскажите, как избавиться от дублирования данных при запись в бд? Т.е. при приведенном внизу коммента коде, если мы выгружаем данные за последний месяц к примеру, и делаем это каждый день, то будет 30 записей с данными за определенный день. (Пример: Выгрузили просмотры за 15 сентября:

    15.09.17 300 просмотров

    После повторного выполнения RMYSQL запроса, итог в БД будет уже таким:

    15.09.17 300 просмотров

    15.09.17 300 просмотров

    Это происходит из за того, что append = TRUE. А power bi покажет что 15.09.17 у нас уже 600 просмотров. Подскажите, как можно проверять, есть ли данные уже в БД, и если нет, то дописывать недостающие. Использовать owerwrite = TRUE не подходит, т.к. тогда перезапишется вся БД, а нужно именно накапливать данные в БД. Спасибо!
    Вы показали в статье, как можно загружать только измененные даннные, но у меня не получается воспроизвести ваш опыт. Расскажите как можно подробнее, как загружать только изменненные данные, спасибо за статью!

    Пример используемого кода:
    if(!»RMySQL» %in% installed.packages()[,1]){install.packages(«RMySQL»)}

    library(RMySQL)

    #Подключаемся к базе данных

    conMySQL <- dbConnect(MySQL(), dbname = "yourdbname", user = "youruser", password = "yourpassword", host = "yourhost")

    #Загружаем данные в базу данных

    dbWriteTable (conMySQL, "yourtable", data_name, row.names = FALSE, append = TRUE)

    #Закрываем соединение

    dbDisconnect(conMySQL)

    • Maks Hapchuk

      Ответ на ваш вопрос есть в самой статье http://clip2net.com/s/3NDGEin
      чтобы данные не дублировались, мы написали скрипт, который определяет последнюю дату с данными в БД и подгружает только недостающие данные.
      Вы можете использовать что-то похожее.

      • apipko

        Получилось, спасибо! Подскажите, как использовать ваш метод с Яндекс директ? У него в колонке с датами выводится «total rows: » всегда внизу при выгрузке по api. Это приводит к ошибке когда мы ищем последнюю дату в базе данных. Есть ли способ обойти этот косяк?

        • Maks Hapchuk

          А что мешает удалять эту последнюю строчку перед загрузкой в базу данных?

  • Кирилл

    Максим, добрый день.
    Подскажите, пожалуйста, зачем проверяем наличие последней даты в базе? Я понимаю это так — я вытянул данные с Adwords, например, по вчера и с завтрашнего дня настраиваю ежедневный запуск скрипта где start date и end date будет вчерашняя дата. Такой способ не подойдет или я что то упускаю?

    • Maks Hapchuk

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

      • Кирилл

        Спасибо большое за ответ.