Power Query для анализа Я.Директа

Привет! Меня зовут Антон Перепечаев. Я специалист по контекстной рекламе, BI аналитик. Подписывайтесь на мой новый Телеграмм-канал по рекламной аналитике "Пытаюсь посчитать". В статье ниже я постараюсь объяснить и показать, что может инструмент Power Query.

Перед тем как погружаться в детали, я хотел бы поделиться мыслью - почему вообще важно знать о PQ?

Перед специалистами по контекстной рекламе с каждым годом ставится все больше аналитических задач. Эти задачи не решаемы в "Мастере отчетов" и сложно выполнимы в Excel с его базовыми функциями.

Как примеры таких задач:

1) Как в «Мастере отчетов» сделать отчет с эффективностью дней недели (Понедельник-Воскресенье)?

2) Как создать отчет по эффективности каждой позиции в спецразмещении?

Ответ — никак.

Задача есть, решения нет. Поэтому я и начал изучать инструменты анализа рекламы, вне рекламного кабинета Яндекса и Гугла.

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

Статья имеет короткую вводную и остальную практическую часть. Лучше 1 раз попробовать, чем 10 прочитать. Надеюсь, что у вас получится сразу применить прочитанное на практике и внедрить PQ в пул своих инструментов.

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

Потребуется от силы 20-25 мин вашего времени.

Все, приступим.

PQ — встроенный в Excel функционал для обработки данных и формирования отчетов.

В нашем случае данные — это статистика из кабинета Директа.

Выглядит интерфейс таким образом:

Очень похож на интерфейс Excel.

Вверху мы видим 2 вкладки для работы с данными — «Преобразование» и «Добавить столбец».

Вкладка «Добавить столбец» понятна интуитивно.

Поясню, что можно делать во вкладке «Преобразование»:

Сгруппировать данные

Изменить тип данных
Посчитать сумму, среднее, найти максимальные и минимальные значения
Отфильтровать таблицу

Сразу приведу пример применения этих функций.

Вот мои изначальные данные:

Вот они же, только сгруппированные по кампаниям:

Потратил на группировку я около 30 секунд.

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

С вводной частью я закончил, далее — практика.

Формирование отчета имеет 4 основных этапа работы с данными:

Загрузка из кабинета
Обработка
Группировка и агрегирование
Готовый отчет и визуализация

Мы с вами будем строить отчет, который нам понадобился в начале статьи — эффективность каждого дня недели.

*Данное руководство предназначено для пользователей Excel на Windows.

Зайдите в «Мастер отчетов» и повторите настройки из скриншота:

Далее нажмите «Экспорт» — «xlsx».Формирование отчета займет до 5 мин, это нормально.

Данные в файле мы очень условно назовем «сырыми», т.е. из которых мы потом будем лепить наши отчеты.

Открываем отчет. Он нам представлен в таком виде:

Обработка — это самый важный этап работы с данными, поэтому будьте внимательны.

Что мы делаем:

Удаляем первые 4 строчки с общей информацией об аккаунте
В столбце «Конверсии» через поиск и замену (CTRL+H) меняем «-» на 0
Выбираем всю нашу табличку через сочетание CTRL+A
Переходим в раздел "Данные" и нажимаем «Из таблицы»:

Excel предложит нам создать из выделенной области таблицу с заголовками, нажимаем «ОК»

В результате наших манипуляций мы попали в интерфейс Power Query:

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

В аналитике есть такой термин «Типы данных». Основных типов несколько: целое число, число с плавающей запятой, текст, дата и время, логический (True или False).

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

В столбце конверсия у нас изначально был знак «-», поэтому Excel присвоил этому столбцу тип данных «Текст».

В результате при сложении «2» и «3» из столбца Конверсии мы получим «23», а не цифру 5.

Теперь нам нужно нажать на каждый столбец с числовыми данными и проверить стоит ли там тип «Целое число» или «Десятичное число».

Нажимаем ПКМ на Конверсии — Тип изменения — Целое число.Если у вас так было по умолчанию, то отлично.

С этапом обработки данных мы разобрались, идем дальше.

Нажимаем на наш столбец «Дата» ПКМ и выбираем создать дубликат.

Справа появится столбец «Копия Дата».

Кликаем дважды на название столбца и меняем названием на «День недели».

Выбираем этот столбец, нажимаем Преобразование — Дата — День — День недели.

На выходе получаем отчет с цифрами от 0 до 6.

0 — это понедельник, 6 — это воскресенье. Мы привыкли считать от 1 до 7, но в программировании все считается от 0, никакой ошибки тут нет.

Далее выбираем в левом углу «Группировать по» и повторяем настройки за мной и нажимаем ОК:

На выходе получаем вот такой отчет:

Готово. Теперь осталось этот отчет допилить до совершенства.

Нажимаем вкладку Главная — Закрыть и загрузить.На отдельном листе создался наш отчет. Если отчет не появляется, нажмите ПКМ на Таблица 1 — загрузить в — Таблица

В нашем отчете явно не хватает показателей.

Давайте добавим по формуле CPL — расход / конверсии.