Примеры Power Query для разных рекламных источников

Примеры Power Query для разных рекламных источников

05.02.2022 0 Автор pavezlo

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

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

Условимся, что некий бизнес ведёт рекламу в трёх рекламных источниках. Допустим это будет Яндекс Директ, Гугл Эдс, Фейсбук — традиционный набор для российского бизнеса.

Для каждого необходимо выгрузить статистику. В Яндекс Директ это «Мастер отчётов», в Гугл Эдс это просто «Отчёты», в Фейсбук «Отчёты по рекламе». Выгруженные данные помещаем на отдельные листы файла Excel.

Источники данных на разных листах Excel
Источники данных на разных листах Excel

Источники Power Query

Данные на листах пока находятся в Excel и отсутствуют в Power Query. Поэтому их необходимо поместить туда с помощью запросов Power Query.

В нашем примере данные по рекламе мы просто вставили на разные листы и отсюда будем формировать Power Query запросы, но их также можно забирать сразу из файлов выгрузок с рекламных систем. Разницы при этом никакой — просто мне удобнее работать внутри Excel и при необходимости довносить какие-то правки не в самом Power Query, а именно в таблице данных.

Чтобы сформировать запрос Power Query нужно данные преобразовать в таблицы с заголовками вот так.

Преобразуем данные из рекламных источников в таблицы для формирования запроса Power Query на основе таблиц
Преобразуем данные из рекламных источников в таблицы для формирования запроса Power Query на основе таблиц

После этого выделяем таблицу и жмём «Создание запроса Power Query из таблицы».

Пример создания запроса Power Query из таблицы
Пример создания запроса Power Query из таблицы

Таким образом у нас в Power Query есть три запроса на основе таблиц данных. Таких запросов может быть гораздо больше, в зависимости от ваших источников данных. На рабочих проектах примеры запросов Power Query содержат данные из Гугл Аналитикса или Метрики, данные по продажам из CRM, данные по колтрекингу из соответствующих систем.

Запросы в Power Query на основе таблиц
Запросы в Power Query на основе таблиц

Обработка запросов Power Query

Данные в Power Query — теперь необходимо провести первичную предобработку для того, чтобы соединить в будущем и выгружать в сводную таблицу.

Предстоит:
— привести все заголовки к единому наименованию;
— привести порядок и структуру столбцов к единому порядку.

Для этого нужно переименовать заголовки в каждой таблице, удалить лишние столбцы и поправить их порядок. Параллельно можно поменять формат столбцов, например для валюты сделать денежный формат или заменить ненужные символы (например дифис в конверсиях Яндекс Директа).

Обработка данных в запросе Power Query
Обработка данных в запросе Power Query

Проводим подобное для каждой таблицы и на выходе получаем готовые запросы для объединения в Power Query.

Объединение запросов Power Query

Для объединения файлов или иначе говоря запросов нужно встать на любой из них и выбрать в меню «Добавить запросы в новый».

Добавление запросов в новый для объединения запросов Power Query
Добавление запросов в новый для объединения запросов Power Query

Далее выбираем три таблицы и более, переносим все таблицы в правое окно и жмём ОК.

Объединение запросов Power Query
Объединение запросов Power Query

Получаем один запрос, где все данные из трёх рекламных источников сливаются в один запрос и большой датасет. Назовём его «all ads».

Общий запрос Power Query на основе трёх
Общий запрос Power Query на основе трёх

Поменяем ещё раз формат столбца «cost» на денежный и нажмём галочку рядом с кнопкой «Закрыть и загрузить», где выберем второй нижний вариант и следующие настройки. Это позволит загрузить данные в модель данных Excel.

Добавление запросов в модель данных Excel
Добавление запросов в модель данных Excel

Построение отчёта

Теперь данные по всем трём источникам данных через Power Query попали в модель данных Excel (своего рода база данных), откуда их легко забирать и строить отчёты. Для этого создадим новый лист и через меню вставка выберем Модель данных.

Построение сводной таблицы на основе запроса Power Query из модели данных Excel
Построение сводной таблицы на основе запроса Power Query из модели данных Excel

Получаем сводную таблицу, где в строки выводим Кампанию, группу, объявление. А в значения все показатели.

Сводная таблица с помощью Power Query
Сводная таблица с помощью Power Query

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

Пример объединения файлов в Power Query
Пример объединения файлов в Power Query

Добавление метрик в отчёт Excel

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

Именно для этого нужно стандартищированно именовать кампании и utm_разметку. Как это делать я уже рассказывал в статье https://pavezlo.ru/analytica_dannyh/kak-nazvat-reklamnuyu-kampaniyu/

Добавление формулы Excel для вывода значений в зависимости от содержимого
Добавление формулы Excel для вывода значений в зависимости от содержимого

То же делаем в остальных источниках данных и в параметрах сводной таблицы у нас появляются «source» и «category», которые мы выводим в отчёт. Например источники:

Отчёт по рекламным источникам в Power Query
Отчёт по рекламным источникам в Power Query

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

Отчёт по продуктовым категориям в Power Query
Отчёт по продуктовым категориям в Power Query

Теперь можно анализировать какая из категорий и в каком рекламном канале лучше работает.

Заключение

Таким образом с помощью статьи вы можете с помощью Excel и Power Query объединить разные источники данных и вывести всё в единые дашборды.

Если вам интересна тема аналитики рекламы, подписывайтесь на наш телеграмм канал.