Павел Корякин , 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 дает очень большое количество возможностей для анализа, и мы показали лишь малую часть. Надеемся, что статья окажется полезной для вас. Спасибо за внимание!

Подпишитесь на Facebook или Телеграмм, чтобы не пропустить новые статьи

Разделяешь наш подход? Вступай в команду!
Другие материалы по теме
Визуализация данных Яндекс Директ в PBI HOWTO
Визуализация данных Яндекс Директ в PBI
В предыдущей статье мы рассказали как выгрузить данные из Яндекс Директ с помощью Python в CSV таблицу. Теперь построим на этих данных красивый и полезный отчет.
Интеграция клиента и агентства HOWTO
Интеграция клиента и агентства
В статье расскажем, как происходит интеграция клиента и агентства, и как выстраиваются коммуникации.
Выгрузка данных из API Яндекс Директ через Python HOWTO
Выгрузка данных из API Яндекс Директ через Python
Рассказываем как выгрузили 30 млн. строк данных для последующего анализа в Power BI.
Прогноз стоимости и количества лидов HOWTO
Прогноз стоимости и количества лидов
Рассказываем об одном из способов медиапланирования - получения прогнозной стоимости и количества лидов.
Модели атрибуции на примере интернет-магазина HOWTO
Модели атрибуции на примере интернет-магазина
В этой статье продолжим рассматривать атрибуции на примере реального интернет-магазина. и расскажем какие выводы можно сделать, сравнивая модели.
Модели атрибуции в Google Analytics HOWTO
Модели атрибуции в Google Analytics
Что такое модель атрибуции? Какими они бывают? Как правильно выбрать модель атрибуции?
читать наш блог

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

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

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

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



Пишите на — info@1jam.ru , звоните в скайпе — jam.agency , или по телефону — 8 (800) 551-85-03