Привет! Меня зовут Антон Перепечаев. Я специалист по контекстной рекламе, BI аналитик. Подписывайтесь на мой новый Телеграмм-канал по рекламной аналитике "Пытаюсь посчитать". В статье ниже я постараюсь объяснить и показать, что может инструмент Power Query.
Перед тем как погружаться в детали, я хотел бы поделиться мыслью - почему вообще важно знать о PQ?
Перед специалистами по контекстной рекламе с каждым годом ставится все больше аналитических задач. Эти задачи не решаемы в "Мастере отчетов" и сложно выполнимы в Excel с его базовыми функциями.
Как примеры таких задач:
1) Как в «Мастере отчетов» сделать отчет с эффективностью дней недели (Понедельник-Воскресенье)?
2) Как создать отчет по эффективности каждой позиции в спецразмещении?
Ответ — никак.
Задача есть, решения нет. Поэтому я и начал изучать инструменты анализа рекламы, вне рекламного кабинета Яндекса и Гугла.
На рынке есть очень много решений для работы с данными. Но Power Query — это как самая простая отправная точка, с которой каждый специалист подберет самый удобный для себя инструмент.
Статья имеет короткую вводную и остальную практическую часть. Лучше 1 раз попробовать, чем 10 прочитать. Надеюсь, что у вас получится сразу применить прочитанное на практике и внедрить PQ в пул своих инструментов.
Перед началом закину удочку. Даже если вы вообще не знаете, что такое Power Query, то прочитав статью и сделав все пошагово вы получите вот такой дашборд:
Потребуется от силы 20-25 мин вашего времени.
Все, приступим.
Что такое Power Query?
PQ — встроенный в Excel функционал для обработки данных и формирования отчетов.
В нашем случае данные — это статистика из кабинета Директа.
Выглядит интерфейс таким образом:
Очень похож на интерфейс Excel.
Вверху мы видим 2 вкладки для работы с данными — «Преобразование» и «Добавить столбец».
Вкладка «Добавить столбец» понятна интуитивно.
Поясню, что можно делать во вкладке «Преобразование»:
- Сгруппировать данные
- Изменить тип данных
- Посчитать сумму, среднее, найти максимальные и минимальные значения
- Отфильтровать таблицу
Сразу приведу пример применения этих функций.
Вот мои изначальные данные:
Вот они же, только сгруппированные по кампаниям:
Потратил на группировку я около 30 секунд.
Думаю, уже начинает проясняться, что Power Query не только дает дополнительные возможности, но существенно ускоряет процесс анализа и делает его удобным.
С вводной частью я закончил, далее — практика.
Формирование отчета имеет 4 основных этапа работы с данными:
- Загрузка из кабинета
- Обработка
- Группировка и агрегирование
- Готовый отчет и визуализация
Мы с вами будем строить отчет, который нам понадобился в начале статьи — эффективность каждого дня недели.
*Данное руководство предназначено для пользователей Excel на Windows.
Этап №1. Загрузка из кабинета
Зайдите в «Мастер отчетов» и повторите настройки из скриншота:
Далее нажмите «Экспорт» — «xlsx».Формирование отчета займет до 5 мин, это нормально.
Данные в файле мы очень условно назовем «сырыми», т.е. из которых мы потом будем лепить наши отчеты.
Этап №2. Обработка данных
Открываем отчет. Он нам представлен в таком виде:
Обработка — это самый важный этап работы с данными, поэтому будьте внимательны.
Что мы делаем:
- Удаляем первые 4 строчки с общей информацией об аккаунте
- В столбце «Конверсии» через поиск и замену (CTRL+H) меняем «-» на 0
- Выбираем всю нашу табличку через сочетание CTRL+A
- Переходим в раздел "Данные" и нажимаем «Из таблицы»:
- Excel предложит нам создать из выделенной области таблицу с заголовками, нажимаем «ОК»
В результате наших манипуляций мы попали в интерфейс Power Query:
Перед следующим действием я обязан объяснить, что мы делаем и зачем, иначе, вы не сможете понять из-за чего у вас могут возникнуть ошибки.
В аналитике есть такой термин «Типы данных». Основных типов несколько: целое число, число с плавающей запятой, текст, дата и время, логический (True или False).
Какая может возникнуть ошибка на примере, если не учитывать тип данных:
В столбце конверсия у нас изначально был знак «-», поэтому Excel присвоил этому столбцу тип данных «Текст».
В результате при сложении «2» и «3» из столбца Конверсии мы получим «23», а не цифру 5.
Теперь нам нужно нажать на каждый столбец с числовыми данными и проверить стоит ли там тип «Целое число» или «Десятичное число».
Нажимаем ПКМ на Конверсии — Тип изменения — Целое число.Если у вас так было по умолчанию, то отлично.
С этапом обработки данных мы разобрались, идем дальше.
Этап №3 — Группировка и агрегирование.
Нажимаем на наш столбец «Дата» ПКМ и выбираем создать дубликат.
Справа появится столбец «Копия Дата».
Кликаем дважды на название столбца и меняем названием на «День недели».
Выбираем этот столбец, нажимаем Преобразование — Дата — День — День недели.
На выходе получаем отчет с цифрами от 0 до 6.
0 — это понедельник, 6 — это воскресенье. Мы привыкли считать от 1 до 7, но в программировании все считается от 0, никакой ошибки тут нет.
Далее выбираем в левом углу «Группировать по» и повторяем настройки за мной и нажимаем ОК:
На выходе получаем вот такой отчет:
Готово. Теперь осталось этот отчет допилить до совершенства.
Нажимаем вкладку Главная — Закрыть и загрузить.На отдельном листе создался наш отчет. Если отчет не появляется, нажмите ПКМ на Таблица 1 — загрузить в — Таблица
Этап №4 — Отчет и визуализация.
В нашем отчете явно не хватает показателей.
Давайте добавим по формуле CPL — расход / конверсии.
Дальше самая красивая часть отчетности. Мы построим несколько баров для каждого из показателей.
Выбираем вставка — гистограмма.
Появляется пустая область. Нажимаем на нее ПКМ — Выбрать данные.
Далее в окне нажимаем значок выбора данных и выбираем ВСЮ таблицу:
Далее убираем галочки кроме «Клики» и нажимаем ОК.
Получаем график распределения трафика по дням:
Видим, что под графиком изменились данные. Вместо от 0 до 6, имеем график от 1 до 7. То есть 1 это теперь вторник.
Чтобы это исправить нажимаем ПКМ — Выбор данных — Изменить — Выбираем наш столбец с днями недели БЕЗ названия столбца.
Нажимаем ок.
Копируем нашу гистограмму, выбираем ячейку справа от графика и вставляем копию.
ПКМ — Выбор данных — Слева меняем галочку «Клики» на «Расход».
Проделываем ту же операцию с конверсиями и CPL.
В итоге мы получаем вот такой отчет:
Для красоты применяем в столбце дата сортировку по возрастанию:
И меняем вручную названия каждого из дней:
Все. Наш отчет полностью готов. Из него уже можно сделать кое-какие выводы, обратите внимание на вторник, но это уже тема для другой статьи.
Спасибо, что дочитали! Надеюсь, было полезно.
Статья подготовлена для Telegram-канала «Корбут и коллеги».
Если вам нужна помощь с любыми аналитическими задачами, пишите мне в ТГ: @tonyornot
#яндексдирект #директ #яндекс_директ #настройкадирект #инструменты_маркетолога #маркетинг #powerquery #pq #дашборд
Антон, добрый день. Power Query - это уже гораздо лучше, чем просто Excel и формулы, но для конечного пользователя все равно получается слишком много телодвижений. С помощью VBA можно сократить все до 2-х действий: получить файл xlsx из кабинета и нажать всего ОДНУ кнопку. Да, это требует на порядок больше знаний и навыков, зато для постоянного использования одно удовольствие. Если хотите, можете прислать ваш файл выгрузки, сделаем чудо:) Для примера бесплатно. Все не доходят руки написать развернутую статью про VBA - реальная и простая автоматизация для огромного круга задач и не только в Excel. Пишите на [email protected]
Добрый день, Дмитрий!
VBA - отличный инструмент, но это следующий этап познания Excel после формул, сводных таблиц, PQ.
Я думаю аудитории будет сложно влиться с 0 в написание макросов, без понимания основ.
мне вот интересно, построили вы что-то там по дням недели. дальше что? толку с этого графика на уровне всего аккаунта?
Александр, статья нужна для ознакомления с инструментом.
За комментарий спасибо, надеюсь он улучшит ранжирование.