Как с помощью Power BI и BigQuery сделать сквозную аналитику для контекстной рекламы

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

Исходные данные

У нашей компании есть клиент - Московская Меховая Компания, который относится к вопросу сбора статистики очень серьезно. Как и с любыми ecommerce-проектом, отчетность и сбор статистики занимает очень много времени работы специалистов, так что мы решили оптимизировать данный процесс и сделать информационные дашборды в Power BI. Расскажем подробнее, как это было.

Схема проекта

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

• Яндекс.Директ
• Google Adwords
• База данных сайта, в которой хранится информация по заказам, включая его источник

Базовая информация, которая необходима для отчетности:

• Дата
• Источник
• Кампания
• Ключевое слово
• Количество кликов
• Количество заказов
• Расход на рекламу
• Выручка от рекламы

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

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

Сбор данных из источников

Для того, чтобы реализовать данные проекта, была выбрана в качестве базы данных BigQuery от Google, так как она быстрая, есть встроенный коннектор к Power BI, и в целом данная связка работает очень быстро и может спокойно обновляться онлайн.

Что касается инструмента импорта рекламных расходов в эту базу, то мы использовали сервис наших партнеров renta.im, который позволяет всего в пару нажатий настроить импорт из Яндекс Директа, Google Adwords или Google Analytics в BigQuery, избегая проблемы семплирования, автообновления и прочие, с которыми вы можете столкнуться.

Для того чтобы настроить процесс синхронизации MySQL c BigQuery мы использовали библиотеку mysql-to-google-bigquery. Более подробно о том, как ее настроить можно прочесть на странице проекта.

Примечание: немного позднее ребята из renta.im также сделали возможность синхронизации MySQL и BigQuery, так что данный способ сейчас является самым простым, в отличие от mysql-to-google-bigquery.

Рассмотрим процесс сбора данных.

Импорт данных из Яндекс Директ и Google Adwords в BigQuery

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

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

• Выбираем Яндекс.Директ

2) Авторизируемся в аккаунте, разрешаем доступ приложению.

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

4) Далее нажимаем кнопку ADD и выбираем источник BigQuery, подтверждаем доступ

Поток создан и готов к использованию:

Аналогично инструкции выше, настраиваем Google Adwords, вот пример наших настроек:

На этом импорт данных завершен, на выходе мы получаем следующую информацию в BigQuery:

В базе Renta_dataset мы храним информацию по статистике из рекламных систем, а в базе - по заказам.

Итого, настройка потоков импорта данных в BigQuery может быть проведена в достаточно сжатые сроки.

Создание отчетности

Как и говорилось ранее, для визуализации информации мы использовали Power BI от Microsoft.

Предварительная обработка данных

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

После этого преобразовываем данные и получаем несколько таблиц:

• Сводная таблица с заказами и статистикой из рекламных кампаний:

• Таблица по целевым CPO в разрезе направлений от клиента:

• Служебная таблица с календарем для более удобного формирования дат:

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

Первым этапом были сделаны все вычисляемые меры, на основании которых и принимаются решения по оптимизации рекламных кампаний:

В качестве базовой визуализации была использована матрица с фильтрами, на которой были собраны основные параметры по рекламным кампаниям:

 

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

Дополнительно были сделаны панели мониторинга ситуации с графиками, которые позволяют быстро отслеживать динамику по всем ключевым показателям (на данном примере мы выбрали рекламные кампании РСЯ в Яндекс.Директ):

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

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

 

Или понять, что средний чек в выходные у нас ниже:

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

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

В результате нашей работы мы получили удобные панели для мониторинга контекстной рекламы, которые использует как клиент, так и специалисты нашего агентства. Было существенно сокращено время, которое специалисты тратят на подготовку внутренней отчетности, а также отчетности для клиента. Отдельно можно отметить, что отчетность обновляется автоматически в облаке app.powerbi.com и не требует вмешательства специалиста, а также может быть доступна с любого компьютера через браузер.

Как обычно, мы опубликовали для вас отчет, и вы можете посмотреть, как он работает: