Отчёт по ключевым словам: из Яндекс.Директ в Power BI (часть1)

Сегодня разберём визуализацию данных по ключевикам.

Огорчу многих - коннекторы разбирать не будем.
За источник возьмем обычный файл из двух таблиц:

Цель: составление отчёта для оптимизации ставок по ключевым словам.

Статья состоит из 3 частей:
● загрузка таблиц в Power BI (мало интересного, особенно для спеца)
● построение сводной (более интересная часть)
● группировка фраз (будет интересно всем, кто анализирует фразы).

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

Затягиваем файлики (мало интересного)

Открываем Power BI, затягиваем таблицы.

"Изменить запросы" - переход в Power Query.

Затягиваем файл из Excel в Power BI

Далее выбираем нужный файл. Начнём с расходов.

Выбор файла с расходами

Выбор листа в файле

После загрузки справа должно быть 2 шага - Источник и Навигация:

Над таблицей могут быть пустые строки, которые нужно удалить.

Удаление пустых строк сверху

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

Меняем типы данных. Для текстовых выбираем "текст", для цифр - либо целые, либо дробные:

Смена типа данных для каждого столбца

Обрабатываем фразы чтобы они писались одинаково.
Жмём правой кнопкой на название столбца, выбираем "замена значений". Нужно заменить на пустое значение ряд символов: кавычки, квадратные скобки (открывающие и закрывающие), восклицательные знаки и плюсы.

Чтобы все фразы были строчными буквами, делаем нижний регистр, убираем пробелы справа и слева кнопкой "усечь" (преобразование):

1) Замена значений 2) Преобразование -> нижний регистр 3) Преобразование -> Усечь

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

Загружаем таблицы в модель данных:

Закрываем Power Query, переходим в модель данных

Строим сводную таблицу (уже интереснее)

Мы загрузили 2 таблицы: "Расходы по фразам" и "Конверсии по фразам". Статистика в них разбита на кампании и фразы. Для корректного объединения данных, создаём 2 промежуточных таблицы: "Все кампании" и "Все фразы".

Создание таблицы в DAX

Для таблицы всех фраз вставляем формулу:

Все фразы = DISTINCT(UNION(
SUMMARIZE('Конверсии по фразам';'Конверсии по фразам'[Фраза]);
SUMMARIZE('Расходы по фразам';'Расходы по фразам'[Фраза])))

Для таблицы всех кампаний будет так:

Все кампании = DISTINCT(UNION(
SUMMARIZE('Конверсии по фразам';'Конверсии по фразам'[Кампания]);
SUMMARIZE('Расходы по фразам';'Расходы по фразам'[Кампания])))

В этих формулах мы собираем сводные из наших таблиц по одному столбцу (например SUMMARIZE по фразам), затем объединяем 2 таблицы в одну (UNION) и берём уникальные значения из полученной таблицы (DISTINCT):

Создание полного списка фраз

Переходим на отображение связей и перетягиваем столбцы от новых таблиц к уже загруженным - по фразам и по кампаниям. Должно получиться так:

Схема данных: все фразы и все кампании фильтруют статистику расходов и конверсий

Возвращаемся к графикам, добавляем визуализацию "Матрица", выбираем галочки в соответствии с рисунком. Кампании и фразы берём из новых таблиц, все данные - из старых. Распределяем как показано на рисунке:

Создаём визуально удобную таблицу с данными

Не хватает расчётных показателей, добавим CPL:

Стоимость обращения это отношение расхода к количеству конверсий. Если конверсий нет, будет ошибка. В случае ошибки выдаём CPL = Расход:

CPL = IFERROR(
sum('Расходы по фразам'[Расход])/
sum('Конверсии по фразам'[Конверсии]);
sum('Расходы по фразам'[Расход]))

Задействуем CPL, получаем таблицу. По ней уже можно делать выводы:

Визуализация таблицы с данными

Группировка фраз (самое интересное)

Разбить фразы на группы несложно. Но для качественного анализа нужно группировать фразы быстро и на лету. Используем группировщик Директ-ПРО.

Загружаем в сервис все фразы аккаунта кнопкой "из буфера".
Нажимаем на заголовок столбца "Листы" (отмечен стрелочкой):

Интерфейс Директ-ПРО, публикация разбивки

Появится окно экспорта данных, нужно выбрать "опубликовать":

Всплывающее окно в Директ-ПРО

Появляется ссылка, нужно нажать на неё и перейти в браузер:

Всплывающее окно в Директ-ПРО

Группы опубликованы, копируем ссылку из браузера:

Опубликованные группы по ссылке

Получаем данные по этой ссылке в Power BI:

Интерфейс Power BI, получение разбивки фраз на группы из Директ-ПРО

Вставляем ссылку, в навигаторе выбираем Table 1 - там расположены фразы и листы. Жмём изменить:

Всплывающее окно в Power BI, разбивка Лист-Фраза находится в Table 1

Получили таблицу в power Query:

Интерфейс Power Query

Назовём её "Группы-Фразы". Заменим во фразах плюсики на пустой символ.

Загружаем данные в модель. Добавляем связку новой таблицы с таблицей "Все фразы":

Интерфейс Power BI, схема данных с разбивкой на группы (разбивка тянется из Директ-ПРО)

Строим статистику по группам. Сейчас все фразы лежат в общем списке:

По умолчанию все фразы в Директ-ПРО лежат в общем списке

Разбиваем фразы по стартовым. Нужно включить мультивыделение, выбрать все стартовые и нажать "Распределить":

Интерфейс Директ-ПРО, разбивка по стартовым фразам (как делается)

В результате получаем 7 листов с фразами:

Интерфейс Директ-ПРО, разбивка по стартовым фразам (готова)

После распределения обновляем отчёт в Power BI:

Интерфейс Power BI Desktop, разбивка фраз подтянулась из Директ-ПРО

Видно, что стартовая фраза "домашние торты" работает хуже всех (максимальный CPL). Стартовая "доставка тортов" показывает лучший результат (CPL минимален).

Стартовые не всегда дают понимание, поэтому удалим разбивку:

Удаление листа перекидывает фразы обратно в общий список

Все листы кроме общего списка удалятся.
Теперь сделаем разбивку по собственному списку слов:

Списки -> Создать список -> вставляете слова и название списка -> Жмёте "Создать"

В столбце "Слова" нужно выделить слова из списка. Жмём "выбрать":

Выбираем список, все слова из него выделяются в столбце "Слова".

Сортируете столбец "Слова" по возрастанию частоты и группируете:

От порядка слов (по убыванию/возрастанию) зависит разбивка. Верхние слова разбиваются первыми.

Получили 12 листов. Часть фраз осталась в общем списке:

Распределение по своему списку слов

После распределения обновляем отчёт. Видим, что по тортам для детей самые дешевые заявки, а причина высокого CPL - слово "фото":

Отчёт Power BI в разбивке по своему списку слов.

Итог

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

С помощью Директ-ПРО можно группировать ключевые слова на лету. После обновления отчёта, там отобразится свежая разбивка на группы. Это помогает посмотреть на фразы с разных сторон и сделать выводы в условиях нехватки данных (в разрезе фраз данных всегда не хватает). Такая возможность сильно облегчает анализ и оптимизацию.

Один из вариантов - использовать для разбивки список брендов/марок. Подойдёт любой список из похожих слов. Нужно избегать сильного пересечения фраз при разбивке - это может испортить статистику.
Если такого списка нет, можно делать группировку по леммам. Группировку статистики по леммам мы разберём в следующей статье.

Если вам понравилась статья, ставьте лайк и подписывайтесь на канал!

Свежие новости и статьи: https://vk.com/ppc.webanalytics
Автоматизация настройки Яндекс.Директ: http://директ-про.рф/
Яндекс Дзен: https://zen.yandex.ru/id/5b79b78c5d4ed100a9278752
Канал на YouTube: youtube.com/channel/UCSNUZldO5VgMMvUk4EojQyw