Павел Корякин , 19 сентября 2019

Визуализация данных Яндекс Директ в PBI

В статье покажем как построить полезный отчет в Power BI на данных яз Яндекс Директ.


Очень рекомендуем посмотреть видео, в котором некоторые моменты показаны более наглядно.

В предыдущей статье мы рассказали как выгрузить данные из Яндекс Директ с помощью Python в CSV таблицу. Теперь построим на этих данных красивый и полезный отчет.

Подготовка данных

Открываем программу Power BI и оказываемся в новом, пока пустом отчете. Для начала нужно выбрать источник данных. Мы возьмем отредактированный XLSX-файл, чтобы не раскрывать конфиденциальные данные, но в вашем случае это будет CSV-файл из прошлой статьи.

Нажимаем кнопку «Get Data», выбираем тип источника и указываем расположение файла.
1.jpg

Если это Excel-файл, программа попросит выбрать листы. Указываем и жмем «Load»
2.jpg

После окончания загрузки вы все еще будете видеть пустой отчет, ведь показатели и измерения еще не выбраны. Данные нуждаются некоторой в обработке, для этого переходим в редактор запросов, нажав «Edit queries».

3.jpg

Напомним, что наша выгрузка – это обычный отчет Яндек Директ, но в форме таблицы. Каждый столбец соответствует полю отчета. По-умолчанию Power BI иногда указывает формат данных в каждом столбце, но это не всегда нужно.

В правой части экрана вы видите историю всех операций и можете изменить последовательность или отменить ненужные. Удаляем шаг «Changed Type», который автоматически поменял все форматы, так как он не всегда работает корректно.
4.jpg

Форматы всех столбцов сбросились, давайте нужные нам проставим вручную. Для этого выбираем столбец, кликаем на значок типа данных в шапке столбца и указываем нужное значение.
5.jpg

В статье не будем подробно рассказывать о значении каждого столбца, чтобы не растягивать. Смысл большинства понятен из названия, некоторые другие будут описаны чуть ниже. Просто дадим список столбцов, формат которых нужно изменить. Более подробно поля рассмотрены в видео.
6.jpg

Обратите внимание, что в столбцах «Conversions» и «Revenue» вместо пустых значений указан двойной дефис «--». Это значение нужно заменить на 0, иначе будет ошибка. Выбираем оба столбца, зажав «Ctrl» и жмем команду «Replace Value» и меняем дефисы на «0».
7.jpg
8.jpg

Последним шагом удаляем все пустые столбцы документа. Для этого выделяем их все сразу через клавишу «Shift», кликаем правой кнопкой мыши и удаляем.9.jpg

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

  • «Cost» в «Cost RAW»
  • «Conversions_***_LSC» в название вашей цели, у нас «транзакции»
  • «Revenue» в «Revenue RAW»

Теперь данные готовы к построению отчетов. Нажимаем в верхней панели кнопку «Close & Apply» и возвращаемся в окно с отчетом.10.jpg


Строим отчет

Выбираем тип графика «Matrix» и увидим в окне отчета пустую таблицу.
11.jpg

Справа вы видите список всех доступных для отчета полей и окно настройки выбранной «визуализации». Тут мы указываем строки, столбцы и значения для таблиц и графиков, а точнее просто перетаскиваем их из «Fields».
12.jpg

В качестве строк берем кампании – «CampaignName». В качестве значений - клики, расходы, транзакции и выручку.
13.jpg

В отчете вы увидите огромные значения расходов и выручке. Это особенность хранения данных Директа. Для того, чтобы видеть корректные цифры, расходы и выручку нужно разделить на миллион. Создадим новые «меры» для отчета. Жмем правой кнопкой на любом из полей и выбираем «New Measure».
15.jpg


Нас просят ввести формулу. Нужно всю сумму расходов разделить на миллион, с помощью такой формулы:

Cost = DIVIDE(SUM(YD[Cost RAW]);1000000)

Похожим образом создаем и корректную выручку:

 Revenue = DIVIDE(SUM(YD[Revenue RAW]);1000000)

Полученные поля добавляем в отчет, а показатели с припиской «RAW» убираем.
16.jpg

Для удобного отображения уберем лишние знаки после запятой. Для этого кликаем на нужное поле, переходим на вкладку «Modeling» и указываем 0 знаков после запятой.17.jpg

Повторяем шаг для всех полей с лишними знаками после запятой и получаем аккуратную таблицу с понятными значениями.
18.jpg

Теперь давайте создадим и выведем таблицу другие интересные показатели: CTR, % конверсии, стоимость конверсии.


Пользовательские показатели

Создаем новую меру. CTR – это отношение кликов к показам, поэтому формула будет такой:

CTR = DIVIDE(SUM(YD[Clicks]);SUM(YD[Impressions]))

Сразу укажем на вкладке «Modeling» формат проценты и 1 знак после запятой.
19.jpg

CR(Conversion Rate) – отношение конверсий к переходам:

CR = DIVIDE(SUM(YD[Transactions]);SUM(YD[Clicks])))

CPO (Cost per Order) – стоимость одной конверсии:

 CPO = DIVIDE([Cost];SUM(YD[Transactions]))

ROI – отношение выручки к расходам:

ROI = DIVIDE([Revenue];[Cost])

PF – прибыль за вычетом рекламных расходом.

Тут оговоримся, что не всегда есть возможность учесть разную маржинальность товаров, но даже усредненное значение будет полезным. Для примера возьмем среднюю маржинальность в 30%.

Pf = [Revenue]*0,3 - [Cost]

Каждому созданному показателю, как в первом шаге укажите формат на вкладке «Modeling». CR, ROI - проценты; CPO, PF - рубли. На этом создание показателей завершено, добавим их в таблицу.
20.jpg

Получаем вот такую таблицу, которую уже можно анализировать, но мы ее улучшим.
21.jpg

Мы с вами вывели данные в разрезе кампаний, но есть еще группы и ключевые слова. Их можно добавить в ту же таблицу 2-м и 3-м уровнем. Для этого добавляем в строки таблицы поля «AdGroupName» и «Criterion».
22.jpg

Для того, чтобы развернуть нужную кампанию до уровня групп или ключей, можно использовать меню по при клике правой кнопкой мыши на строку, или значки в углу таблицы.
23.jpg
24.jpg

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

Обычно мы отправляем наценку и стоимость доставки по каждому товару в системы аналитики, но когда таких данных нет – берем среднее значение.

Итак, давайте добавим градиент. Для этого в панели настроек визуализации переходим на вкладку «Format», там ищем и раскрываем строку «Conditional formatting», выбираем поле «ROI» и активируем для него «Background color».
25.jpg

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

Поэтому рекомендуем провалиться в продвинутые настройки, и указать минимальное и максимальное значение для градиента.
26.jpg

В открывшемся окне указываем тип значения – «Number», значения «5» и «9» для минимального и максимального значения соответственно.28.jpg

Аналогично активируем градиент для поля «PF», но значения для градиента можно не менять. Теперь данные стало гораздо проще проанализировать визуально и сделать некоторые выводы. Например, мы уже видим, что основную часть прибыли сформировали всего несколько кампаний, а остальные в сумме почти ничего не принесли.
29.jpg

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

  • общая ось – «Month» 
  • значения для столбцов графика – «PF» 
  • значения для линии графика – «ROI»

30.jpg

Обратили внимание, что при добавлении «Month» сразу появились год, квартал, месяц и день? Power BI сам высчитывает эти значения из дат, это нам пригодится очень скоро. По умолчанию в таблицах и графиках показывается самый верхний уровень этой иерархии, но мы без дополнительных настроек можем опуститься на более низкие уровни. Чтобы график был разбит по месяцам, нажмите соответствующую кнопку в углу графика.
31.jpg

В наших данных статистика всего за 2 месяца, поэтому график получился не очень показательным, но суть вам понятна.

Добавим другие полезные показатели в область отчета, чтобы делать еще более точный анализ. Первым делом выводим фильтр по дате.

Щелкаем на пустом месте, выбираем «фильтр» на панели визуализаций и переносим в него поле «Month». Получаем «ползунок» выбора даты, надеемся знаете как им пользоваться.32.jpg

Далее добавим небольшую таблицу, в которой значениями будут: тип кампании, тип площадки, расходы, ROI и прибыль. Настройки будут такими:33.jpg

Похожим образом добавим таблицы со статистикой по полу и возрасту, и по устройствам. Параметры этих таблицы на скринах ниже.35.jpg
36.jpg

Теперь самое интересное. Причина, по которой Power BI всем нравится и приобретает популярность – это интерактивность отчетов. При нажатии на любую строку все данные перестроятся. Можно просто кликнуть на любую кампанию, и увидеть более детальную статистику по трафику, который она привела: пол, возраст, устройства. Зажав «Ctrl» можно одновременно выбрать несколько кампаний.

34.jpg

Статистика в разрезе регионов.

Каждому «гео» Яндекс присваивает числовой идентификатор, поэтому если просто включить это поле в отчет, ничего понятного мы не увидим. Сначала подключить к нашему файлу таблицу соответствия регионов, которую мы заранее получили из API Яндекс Директа и сохранили в Google Sheets для быстрого подключения.

Переходим в «Редактор запросов» и добавляем новый пустой запрос, щелкнув правой кнопкой на левой панели.
38.jpg
39.jpg

Затем жмем правой кнопкой на уже созданный запрос, и переходим в расширенный редактор, где все содержимое заменяем текстом из прикрепленного документа. Предварительно переименуйте запрос в «Regions».
40.jpg
41.jpg
42.jpg

Мы получили «словарь» с реальным названием каждого «гео» и его «родителем» более высокого уровня – регион, область, округ. Сохраняем изменения и возвращаемся к отчету.
43.jpg

Если сейчас просто добавить в отчет регионы, то ничего не произойдет, сначала нужно настроить связь между таблицами. Простыми словами нужно научить основную таблицу «подтягивать» название региона по его «id» из второй таблицы. Сделать это очень просто.

Из главного окна переходим на вкладку «Связи», где увидим схематичное изображение имеющихся данных. И простым перетаскиванием соединяем поле «LocationOfPresecnceId» из «YD» с полем «GeoRegionId» из таблицы «Regions».
44.jpg

Результатом будет вот такая связь, которая позволит использовать поля разных исходных таблиц в одной:
45.jpg

Для наглядности давайте в таблице с устройствами вместо «девайсов» добавим названия регионов – «GeoRegionName». Все, теперь у нас есть статистика в разрезе регионов.46.jpg

Чуть выше мы показывали, как весь отчет меняется при клике на кампанию, это работает и в обратную сторону. Кликнув на регион, все таблицы и графики перестроятся, а вы увидите, какие именно кампании сработали в регионе.
47.jpg

Текущей странице дадим название «Поиск» и создадим ее дубликат с названием «Площадки».
48.jpg

На новой странице в основной таблице вместо кампаний выведем РСЯ площадки – «Placement».
49.jpg

И применим для всей страницы «Площадки» фильтр, который исключит все кампании кроме РСЯ. На панели настроек визуализаций опускаемся в самый низ и ищем «Page level filters» – фильтр уровня страницы. Пока там пусто, но мы перетащим туда поле «AdNetworkType» и отметим галочкой только «AD_NETWORK» – это РСЯ.
50.jpg

Теперь на этой странице у нас подробная статистика в разрезе РСЯ-площадок. Аналогично добавьте фильтр и для первой страницы, оставив там только «SEARCH».51.jpg

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


Считаем процент охвата

Снова переходим в «Редактор запросов», где мы меняли форматы столбцов и удаляли лишние.
52.jpg

В столбце «Slot» видим значения «PREMIUMBLOCK» и «OTHER», которые соответствуют спецразмещению и гарантии.
53.jpg

Эти данные потребуются для создания нового столбца. Верху страницы переходим на вкладку добавления столбцов «Add Column» и выбираем условный столбец – «Conditional Column».
54.jpg

В появившемся окне прописываем настройки:

  1. Название нового столбца – «Premium»
  2. Исходный столбец – «Slot»
  3. Условие – «equals», означающее равенство
  4. Значение, с которым будет сравниваться исходная ячейка – «PREMIUMBLOCK»
  5. Тип результата, если условие верно – ввести значение, или взять из столбца. Выбираем «Select column», будем брать из столбца.

  6. Название столбца, из которого брать значение при соблюдении условия – «Impressions»

  7. Значение, которое будет записано, если условие не выполнено – просто указываем «0»
    55.jpg

Простыми словами: создаем дубликат столбца «Impressions», но берем только показы из спецразмещения, а остальные заменяем на «0». Сразу меняем формат нового столбца на «целое число». Затем сохраняем изменения и возвращаемся к главному отчету.
56.jpg
57.jpg

Добавляем полученное поле к основной таблице на странице «Поиск». И теперь, кроме общего количества показов, видим отдельно показы в спецразмещении.
58.jpg
59.jpg

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

Coverage = DIVIDE(SUM(YD[Premium]);SUM(YD[Impressions])

Количество показов в спецразмещении делим на общее количество показов. На вкладке «Modeling» выбираем формат «проценты» и добавляем новый показатель в главную таблицу. Для каждой кампании теперь видим процент охвата. На примере наших данных можем заметить, что кампания «№8» приносит хорошую прибыль и имеет охват 89%, то есть потенциал для роста почти исчерпан. А кампания «№18» с почти такой же прибылью, имеет охват всего 62%, значит по ней еще можно увеличить количество трафика и прибыли.60.jpg

С полученными данными можно строить прогнозы и делать огромное количество вычислений. Материал получился очень объемным, честь вам и хвала, если дошли до конца! 

Power BI дает очень большое количество возможностей для анализа, и мы показали лишь малую часть. Надеемся, что статья окажется полезной для вас. Спасибо за внимание!

Хотите дальше узнавать новое про контекстную рекламу для интернет-магазинов?
Подписывайтесь на наш канал в телеграме.

Перейти на канал
Другие материалы по теме
Основные стратегии Яндекс.Директа 2023 для управления рекламными кампаниями
Основные стратегии Яндекс.Директа 2023 для управления рекламными кампаниями
Лучшая стратегия — та, которая решает задачи бизнеса. Если верно поставить цели и определиться с KPI, настроить рекламу будет проще.
5 типичных проблем в контексте, которые мы видим у 90% клиентов
5 типичных проблем в контексте, которые мы видим у 90% клиентов
Мы провели аудит более чем для 100 интернет-магазинов и постоянно видим одни и те же ошибки. Например, клиент хочет масштабироваться, поднимает бюджет — и реклама начинает работать в минус.
За 2 года подняли контекст с минусов до 330 тысяч ₽/месяц магазину тканей, хотя Google ушел и автостратегии не работали
За 2 года подняли контекст с минусов до 330 тысяч ₽/месяц магазину тканей, хотя Google ушел и автостратегии не работали
Какие инструменты использовали, какие гипотезы тестировали, чем компенсировали уход Google из России и почему не для всех подходят автостратегии и товарные площадки.
Пакетные стратегии в Яндекс Директ: для чего нужны и как настроить
Пакетные стратегии в Яндекс Директ: для чего нужны и как настроить
Как работают пакетные стратегии и как их настраивать — с примерами из практики
Модели атрибуции в Яндекс Директе: как использовать в аналитике и какую выбрать при настройке рекламы
Модели атрибуции в Яндекс Директе: как использовать в аналитике и какую выбрать при настройке рекламы
Объясняем на картинках, что такое модели атрибуции в Директе и как они помогают анализировать рекламу и обучать автостратегии.
Ретаргетинг в Яндекс Директ: что это такое, какой бывает и как настроить
Ретаргетинг в Яндекс Директ: что это такое, какой бывает и как настроить
Пошаговая инструкция, как собрать аудиторию и настроить ретаркетинг на поиске и в РСЯ.
читать наш блог

Оставьте заявку

После того, как вы оставите заявку: интервью ~15 минут → гостевые доступы для аудита ~15 минут → аудит в течение недели → согласование предложения → начало первой итерации. По нашему опыту реально начать что-то делать уже через неделю.

Менеджер проектов Александр
Александр

Менеджер проектов



Пишите на — или в Телеграмм