редакции
Визуализация данных Яндекс Директ в Power BI
Очень рекомендуем посмотреть видео, в котором некоторые моменты показаны более наглядно.
В одном из материалов мы рассказали как выгрузить данные из Яндекс Директ с помощью Python в CSV таблицу. Теперь построим на этих данных красивый и полезный отчет.
Подготовка данных
Открываем программу Power BI и оказываемся в новом, пока пустом отчете. Для начала нужно выбрать источник данных. Мы возьмем отредактированный XLSX-файл, чтобы не раскрывать конфиденциальные данные, но в вашем случае это будет CSV-файл из прошлой статьи.
Нажимаем кнопку «Get Data», выбираем тип источника и указываем расположение файла.
Если это Excel-файл, программа попросит выбрать листы. Указываем и жмем «Load»
После окончания загрузки вы все еще будете видеть пустой отчет, ведь показатели и измерения еще не выбраны. Данные нуждаются некоторой в обработке, для этого переходим в редактор запросов, нажав «Edit queries».
Напомним, что наша выгрузка — это обычный отчет Яндекс Директ, но в форме таблицы. Каждый столбец соответствует полю отчета. По-умолчанию Power BI иногда указывает формат данных в каждом столбце, но это не всегда нужно.
В правой части экрана вы видите историю всех операций и можете изменить последовательность или отменить ненужные. Удаляем шаг «Changed Type», который автоматически поменял все форматы, так как он не всегда работает корректно.
Форматы всех столбцов сбросились, давайте нужные нам проставим вручную. Для этого выбираем столбец, кликаем на значок типа данных в шапке столбца и указываем нужное значение.
В статье не будем подробно рассказывать о значении каждого столбца, чтобы не растягивать. Смысл большинства понятен из названия, некоторые другие будут описаны чуть ниже. Просто дадим список столбцов, формат которых нужно изменить. Более подробно поля рассмотрены в видео.
Обратите внимание, что в столбцах «Conversions» и «Revenue» вместо пустых значений указан двойной дефис «—». Это значение нужно заменить на 0, иначе будет ошибка. Выбираем оба столбца, зажав «Ctrl» и жмем команду «Replace Value» и меняем дефисы на «0».
Последним шагом удаляем все пустые столбцы документа. Для этого выделяем их все сразу через клавишу «Shift», кликаем правой кнопкой мыши и удаляем.
Сразу рекомендуем переименовать некоторые столбцы:
- «Cost» в «Cost RAW»
- «Conversions_***_LSC» в название вашей цели, у нас «транзакции»
- «Revenue» в «Revenue RAW»
Теперь данные готовы к построению отчетов. Нажимаем в верхней панели кнопку «Close & Apply» и возвращаемся в окно с отчетом.
Строим отчет
Выбираем тип графика «Matrix» и увидим в окне отчета пустую таблицу.
Справа вы видите список всех доступных для отчета полей и окно настройки выбранной «визуализации». Тут мы указываем строки, столбцы и значения для таблиц и графиков, а точнее просто перетаскиваем их из «Fields».
В качестве строк берем кампании — «CampaignName». В качестве значений — клики, расходы, транзакции и выручку.
В отчете вы увидите огромные значения расходов и выручке. Это особенность хранения данных Директа. Для того, чтобы видеть корректные цифры, расходы и выручку нужно разделить на миллион. Создадим новые «меры» для отчета. Жмем правой кнопкой на любом из полей и выбираем «New Measure».
Нас просят ввести формулу. Нужно всю сумму расходов разделить на миллион, с помощью такой формулы:Cost = DIVIDE(SUM(YD[Cost RAW]);1000000)
Похожим образом создаем и корректную выручку: Revenue = DIVIDE(SUM(YD[Revenue RAW]);1000000)
Полученные поля добавляем в отчет, а показатели с припиской «RAW» убираем.
Для удобного отображения уберем лишние знаки после запятой. Для этого кликаем на нужное поле, переходим на вкладку «Modeling» и указываем 0 знаков после запятой.
Повторяем шаг для всех полей с лишними знаками после запятой и получаем аккуратную таблицу с понятными значениями.
Теперь давайте создадим и выведем таблицу другие интересные показатели: CTR, % конверсии, стоимость конверсии.
Пользовательские показатели
Создаем новую меру. CTR — это отношение кликов к показам, поэтому формула будет такой:CTR = DIVIDE(SUM(YD[Clicks]);SUM(YD[Impressions]))
Сразу укажем на вкладке «Modeling» формат проценты и 1 знак после запятой.
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 — рубли. На этом создание показателей завершено, добавим их в таблицу.
Получаем вот такую таблицу, которую уже можно анализировать, но мы ее улучшим.
Мы с вами вывели данные в разрезе кампаний, но есть еще группы и ключевые слова. Их можно добавить в ту же таблицу 2-м и 3-м уровнем. Для этого добавляем в строки таблицы поля «AdGroupName» и «Criterion».
Для того, чтобы развернуть нужную кампанию до уровня групп или ключей, можно использовать меню по при клике правой кнопкой мыши на строку, или значки в углу таблицы.
Зрительно сравнивать такое количество строк становится неудобно, поэтому добавим для ROI и прибыли цветовые индикаторы в зависимости от значения. Обратим еще раз внимание, что кроме ROI обязательно нужно смотреть прибыль. Даже при высоком ROI абсолютное значение прибыли может быть очень маленьким.
Обычно мы отправляем наценку и стоимость доставки по каждому товару в системы аналитики, но когда таких данных нет — берем среднее значение.
Итак, давайте добавим градиент. Для этого в панели настроек визуализации переходим на вкладку «Format», там ищем и раскрываем строку «Conditional formatting», выбираем поле «ROI» и активируем для него «Background color».
По умолчанию система сама распределяет градиент между максимальным и минимальным значением, но мы знаем, что даже положительный ROI может давать убыток, если он меньше определенного значения.
Поэтому рекомендуем провалиться в продвинутые настройки, и указать минимальное и максимальное значение для градиента.
В открывшемся окне указываем тип значения — «Number», значения «5» и «9» для минимального и максимального значения соответственно.
Аналогично активируем градиент для поля «PF», но значения для градиента можно не менять. Теперь данные стало гораздо проще проанализировать визуально и сделать некоторые выводы. Например, мы уже видим, что основную часть прибыли сформировали всего несколько кампаний, а остальные в сумме почти ничего не принесли.
Для анализа данных в динамике добавим график. Щелкаем на пустом месте, выбираем подходящий тип графика на панели визуализаций и переносим нужные поля:
- общая ось — «Month»
- значения для столбцов графика — «PF»
- значения для линии графика — «ROI»
Обратили внимание, что при добавлении «Month» сразу появились год, квартал, месяц и день? Power BI сам высчитывает эти значения из дат, это нам пригодится очень скоро. По умолчанию в таблицах и графиках показывается самый верхний уровень этой иерархии, но мы без дополнительных настроек можем опуститься на более низкие уровни. Чтобы график был разбит по месяцам, нажмите соответствующую кнопку в углу графика.
В наших данных статистика всего за 2 месяца, поэтому график получился не очень показательным, но суть вам понятна.
Добавим другие полезные показатели в область отчета, чтобы делать еще более точный анализ. Первым делом выводим фильтр по дате.
Щелкаем на пустом месте, выбираем «фильтр» на панели визуализаций и переносим в него поле «Month». Получаем «ползунок» выбора даты, надеемся знаете как им пользоваться.
Далее добавим небольшую таблицу, в которой значениями будут: тип кампании, тип площадки, расходы, ROI и прибыль. Настройки будут такими:
Похожим образом добавим таблицы со статистикой по полу и возрасту, и по устройствам. Параметры этих таблицы на скринах ниже.
Теперь самое интересное. Причина, по которой Power BI всем нравится и приобретает популярность — это интерактивность отчетов. При нажатии на любую строку все данные перестроятся. Можно просто кликнуть на любую кампанию, и увидеть более детальную статистику по трафику, который она привела: пол, возраст, устройства. Зажав «Ctrl» можно одновременно выбрать несколько кампаний.
Статистика в разрезе регионов.
Каждому «гео» Яндекс присваивает числовой идентификатор, поэтому если просто включить это поле в отчет, ничего понятного мы не увидим. Сначала подключить к нашему файлу таблицу соответствия регионов, которую мы заранее получили из API Яндекс Директа и сохранили в Google Sheets для быстрого подключения.
Переходим в «Редактор запросов» и добавляем новый пустой запрос, щелкнув правой кнопкой на левой панели.
Затем жмем правой кнопкой на уже созданный запрос, и переходим в расширенный редактор, где все содержимое заменяем текстом из прикрепленного документа. Предварительно переименуйте запрос в «Regions».
Мы получили «словарь» с реальным названием каждого «гео» и его «родителем» более высокого уровня — регион, область, округ. Сохраняем изменения и возвращаемся к отчету.
Если сейчас просто добавить в отчет регионы, то ничего не произойдет, сначала нужно настроить связь между таблицами. Простыми словами нужно научить основную таблицу «подтягивать» название региона по его «id» из второй таблицы. Сделать это очень просто.
Из главного окна переходим на вкладку «Связи», где увидим схематичное изображение имеющихся данных. И простым перетаскиванием соединяем поле «LocationOfPresecnceId» из «YD» с полем «GeoRegionId» из таблицы «Regions».
Результатом будет вот такая связь, которая позволит использовать поля разных исходных таблиц в одной:
Для наглядности давайте в таблице с устройствами вместо «девайсов» добавим названия регионов — «GeoRegionName». Все, теперь у нас есть статистика в разрезе регионов.
Чуть выше мы показывали, как весь отчет меняется при клике на кампанию, это работает и в обратную сторону. Кликнув на регион, все таблицы и графики перестроятся, а вы увидите, какие именно кампании сработали в регионе.
Текущей странице дадим название «Поиск» и создадим ее дубликат с названием «Площадки».
На новой странице в основной таблице вместо кампаний выведем РСЯ площадки — «Placement».
И применим для всей страницы «Площадки» фильтр, который исключит все кампании кроме РСЯ. На панели настроек визуализаций опускаемся в самый низ и ищем «Page level filters» — фильтр уровня страницы. Пока там пусто, но мы перетащим туда поле «AdNetworkType» и отметим галочкой только «AD_NETWORK» — это РСЯ.
Теперь на этой странице у нас подробная статистика в разрезе РСЯ-площадок. Аналогично добавьте фильтр и для первой страницы, оставив там только «SEARCH».
Мы уже имеем очень полезный и удобный отчет с большим количеством срезов, но и это еще не все. Предлагаем добавить примерный процент охвата рекламной кампании, чтобы понять, есть ли по ней потенциал роста.
Считаем процент охвата
Снова переходим в «Редактор запросов», где мы меняли форматы столбцов и удаляли лишние.
В столбце «Slot» видим значения «PREMIUMBLOCK» и «OTHER», которые соответствуют спецразмещению и гарантии.
Эти данные потребуются для создания нового столбца. Верху страницы переходим на вкладку добавления столбцов «Add Column» и выбираем условный столбец — «Conditional Column».
В появившемся окне прописываем настройки:
- Название нового столбца — «Premium»
- Исходный столбец — «Slot»
- Условие — «equals», означающее равенство
- Значение, с которым будет сравниваться исходная ячейка — «PREMIUMBLOCK»
- Тип результата, если условие верно — ввести значение, или взять из столбца. Выбираем «Select column», будем брать из столбца.
- Название столбца, из которого брать значение при соблюдении условия — «Impressions»
- Значение, которое будет записано, если условие не выполнено — просто указываем «0»
Простыми словами: создаем дубликат столбца «Impressions», но берем только показы из спецразмещения, а остальные заменяем на «0». Сразу меняем формат нового столбца на «целое число». Затем сохраняем изменения и возвращаемся к главному отчету.
Добавляем полученное поле к основной таблице на странице «Поиск». И теперь, кроме общего количества показов, видим отдельно показы в спецразмещении.
Для того, чтобы посчитать процент охвата, создадим новую меру, с такой формулой:Coverage = DIVIDE(SUM(YD[Premium]);SUM(YD[Impressions])
Количество показов в спецразмещении делим на общее количество показов. На вкладке «Modeling» выбираем формат «проценты» и добавляем новый показатель в главную таблицу. Для каждой кампании теперь видим процент охвата. На примере наших данных можем заметить, что кампания «№ 8» приносит хорошую прибыль и имеет охват 89%, то есть потенциал для роста почти исчерпан. А кампания «№ 18» с почти такой же прибылью, имеет охват всего 62%, значит по ней еще можно увеличить количество трафика и прибыли.
С полученными данными можно строить прогнозы и делать огромное количество вычислений. Материал получился очень объемным, честь вам и хвала, если дошли до конца!
Power BI дает очень большое количество возможностей для анализа, и мы показали лишь малую часть. Надеемся, что статья окажется полезной для вас.