Привет! Меня зовут Антон Астахов, я руководитель компании Mello.
Наш клиент – крупная фармкомпания, которая запускает много интернет-рекламы на десятках площадках Рунета. Для оценки эффективности запусков формируется отчет в MS Excel на основе разрозненных данных: вручную формирующиеся выгрузки и листы в файле xls. Процесс выглядит так – аналитик еженедельно выгружает данные из рекламных кабинетов в определенном формате, складывает их в Excel и потом производится анализ.
Конечно, у такой схемы есть пара очевидных плюсов – это просто и не требует дополнительных усилий и инвестиций.
Давайте теперь посмотрим на минусы:
- Человеческий фактор – если один человек занимается формированием такой отчетности, то он может отказываться от внесения каких-то правок, ошибиться, взять больничный, уйти в отпуск, уволиться, не успеть из-за текущей загрузки. технологический фактор – вы собрали данные за прошлую неделю, а через какое-то время площадка пересчитала статистику (так действительно бывает;). В этот момент ваша отчетность перестает сходится с данными в рекламных площадках. Чтобы исправить ситуацию, нужно сначала определить время, когда произошло изменение данных, а потом исправить выгрузку за это время
- Жесткость системы – вы решили изменить отчет и добавить в него какие-то данные, с большой долей вероятности придется переделывать все предыдущие выгрузки, возможно отчет в целом. Причем мы возлагаем на сотрудника абсолютно скучную и рутинную работу – выкачать, скопировать, вставить.
- Время и деньги – на сбор и обработку нужной информации, аналитики могут тратить гораздо больше времени, чем на ее анализ. Вместо достижения поставленных KPI компании, сотрудник думает о том, как бы вручную собрать отчет, чтобы данные в нем были корректные. В результате на дистанции это обходится дороже для компании.
Ручной сбор данных в отчет – это не плохо, наоборот это отличный старт для проверки гипотез и создания нужного формата отчетности. Но в какой-то момент компания понимает, что минусы такой системы начали перечеркивать все ее плюсы, тогда возникает мысль – “а давайте автоматизируем процесс сбора данных”. Именно так и произошло у нашего клиента.
Основная задача – разработка отчета по четко регламентированной структуре, которая принята в организации. Именно поэтому отчет, который вы увидите в конце статьи достаточно прост в оформлении. Отчет должен был решить главную задачу – автоматизировать получение и отображение данных о множестве рекламных кампаниях – от контекстных до медийных. Этому и посвящена наша статья. Поехали.
Выбор стека технологий
Любая автоматизация должна начинаться с выбора стека технологий, на котором будет работать созданная система. Выбор нужного стека это тема для отдельной публикации, поэтому мы не будем описывать общие принципы, а расскажем какой конкретный стек выбрал клиент.
Для реализации данной задачи нужно:
- Произвести выгрузку данных из рекламных кабинетов и Google Analytics;
- Обеспечить хранение и обновление этих данных;
- Визуализировать их – превратить в конечные отчеты.
Клиент рассмотрел множество возможных реализаций от готовых систем сквозной аналитики, до совсем персональных решений и решил выбрать инструменты компании Microsoft, а именно:
- База данных – Azure SQL Database
- Программа для визуализации – Power BI
Под этот стек технологий отлично подходит сервис myBI Connect, который автоматизирует задачу выгрузки данных из онлайн-источников.
UPD. На текущий момент сервис mybi connect производит выгрузку в базы PostgreSQL в Яндекс Облако. Azure не используется.
Первые проблемы
Несмотря на то, что myBI Connect имеет массу коннекторов, вместе с тем не все рекламные источники были “перекрыты” сервисом. А это значит, что по ряду рекламных площадок мы не можем получить расходы в нашу БД. В основном эти проблемы возникают с медийными рекламными системами.
Что делать, если не хватает готовых коннекторов для выгрузки данных?
После обдумывания ситуации вместе с клиентом пришли к выводу, что часть источников данных будем вручную загружать в гугл таблицы. Да, ручная выгрузка частично осталась, но формат и структура данных подверглись серьезным изменениям – все выгрузки привели к единому формату для всех возможных вариантов, чтобы упростить этот процесс и снизить вероятность появления ошибок.
Разработка и поддержка своих коннекторов это достаточно затратная статья расходов, поэтому в большинстве случаев проще купить подписку в готовом сервисе, типа myBI Connect. Но, даже используя готовые сервисы, вы не застрахованы от ситуации, когда в этом сервисе и вообще на рынке нет нужного готового коннектора. Тогда вы возвращаетесь к идее разработки и поддержки коннектора. И вот тут важно просчитать что выгоднее, делать свое решение для текущего рекламного источника или сделать ручную выгрузку.
Наша позиция: если данная рекламная площадка работает не на постоянной основе или доля ее в общих расходах невысока, то лучше сделать ручную выгрузку.
Также в процессе изучения рекламных кампаний была выявлена проблема, не позволяющая построить необходимым образом иерархию данных в отчете, а именно: UTM-метки хоть и составлялись по определенной схеме, но не всегда позволяли однозначно идентифицировать источник / канал трафика (на основе этих данных необходимо было построить фильтры по каналу и направлению трафика). Аналогично и с названиями рекламных кампаний.
Эта проблема поставила под угрозу разработку отчетности. Дело в том что данные по расходам мы берем из рекламной площадки, а информацию о посещении сайта и конверсиях из Google Analytics. Связать эти данные в БД мы можем по какому-то ключу. В качестве такого ключа выступают UTM метки, и если они составлены таким образом, что по ним нельзя сделать корректный ключ для связи данных, тогда вы не можете корректно связать расходы и конверсии. В итоге разработка отчета заканчивается провалом.
Что делать если есть проблемы с UTM тегированием?
Поскольку одним из требований являлось создание определенной иерархии данных, для UTM меток и названий рекламных кампаний был разработан стандарт, к которому в результате работы были приведены все рекламные кампании во всех системах.
Разработка единого стандарта для UTM тегирования и следование ему в дальнейшем – это обязательное условие для корректной работы отчета. Для проектов с небольшим количеством рекламных источников и с отсутствием требованием к определенной иерархии каналов в отчете может хватить базовых рекомендаций по тегированию, которые легко найти в хороших статьях о UTM метках. А вот для проектов с большим количеством источников и определенным требованиям к иерархии в отчете нужно будет разрабатывать персональный стандарт.
Использование такого стандарта позволило выстроить необходимым образом иерархию для анализа данных:
Поскольку весь отчет строится на анализе показателей недельными интервалами, то высшей точкой в иерархии является неделя, далее идет “направление” (или канал привлечения трафика), ниже источник, далее возможные варианты для конкретного источника и нижней точкой является кампания.
Другими слова – отчет отображает информацию от сводной информации за неделю до конкретной рекламной кампании в эту неделю.
Сложности учета медийной рекламы в отчете.
В этом проекте мы столкнулись с тем, что не все рекламные каналы можно было отследить в Google Analytics. Может возникнуть вопрос – а как такое возможно? На самом деле все очень просто – видеореклама без переходов на сайт. При этом задачу о загрузке данных по расходам на рекламу и сведение ее в отчетности никто не отменял.
Учитывая специфику рекламных кампаний (значительный объем рекламные – видеоролики), клиентом была поставлена задача в обязательном порядке обеспечить вывод в отчете таких показателей как:
- VTR – соотношение числа просмотренных полностью роликов к показам;
- 1000CPV – стоимость 1000 досмотров роликов до 100%.
Для Google Ads были сформированы отдельные выгрузки в myBI Connect с данными рекламным кампаниям, которые транслируют видеоролики. Функционал пользовательских отчетов позволяет самостоятельно сформировать список параметров и показателей, которые требуются для решения задачи:
После такой настройки мы получили табличку в БД, которая далее ежедневно обновлялась и содержала все нужные показатели.
По оставшаяся медийка разделилась на два типа:
- реклама, которая не содержит ссылку на сайт, ее нет в Google Analytics
- реклама, по которой нет готового коннектора для получения расходов и статистики
Данные по таким рекламным кампаниям также нужно включить в итоговый отчет. Для решения этих проблем совместно с клиентом был разработан шаблон структуры файла, в которую вручную вносятся данные по результатам просмотров видео материалов.
Чтобы обеспечить необходимый функционал в отчете для рекламных площадок без посещений в Google Analytics, нужно сделать несколько дополнительных действий к разработанному шаблону. В Power Query формируется сводная таблица по рекламным кампаниям без посещений и они добавляются к основным кампаниям (по которым можно отследить трафик из рекламных систем):
Такое решение позволит при необходимости добавить новый источник в отчет за несколько минут.
Сводим все данные в единое целое.
Одной из задач, которую пришлось решать в процессе работы, это получение в различных наборах данных реального идентификатора рекламной кампании, так как именно это значение используется в качестве ключа, по которому “связаны” все источники данных в модели.
Изначально в модели данных myBI Connect эти сведения присутствовали в данных по РК (ID кампании в каждой из систем) и в данных из Google Analytics (источники трафика).
Поскольку для utm меток используется специальный формат (единый для всех систем) и он предусматривает включение id кампании на определенной позиции в соответствующей метке, то проблем с его получением не возникает. Имея для источника трафика id кампании и такой-же id в данных по рекламных кампаниях, мы можем получить данные в связке: расходы на РК, сессии и достижения целей.
В некоторых исключительных случаях использовался метод сопоставления названия рекламной кампании и значения метки для получения ID кампании. Ключ, используемый для связи данных выглядит как 1234567890, может возникнуть вопрос: Каким образом все рекламные кампании были сведены в единое целое?
Для этого использована модель myBI, в которой для каждой рекламной площадки или сервиса выгружается свой набор данных. Для решения задачи потребовалось взять для каждой из систем следующие таблицы:
- Параметры кампаний
- Статистика по объявлениям
Так как такие данные используются в каждой системе, то для получения общей информации полученные данных были объединены в две указанных таблицы, которые используются в отчете:
Поскольку на сайте от посетителя ожидается совершение определенных целевых действий, то они фиксируются целями в Google Analytics. Они тоже выгружаются и их можно увидеть в отчете:
Вычисления
Еще один важный этап превращения данных в отчеты это вычисления, которые в данном случае мы производили на DAX. Когда мы производим выгрузку данных, то получаем таблицы с фактов и измерений, но привычных относительных показателей, на которые привыкли ориентироваться аналитики там нет. Их нужно вычислить. Останавливаться и разбирать все мы не будет, но пример приведем. Одним из основных показателей является показатель отказов, который рассчитывается как:
и отображается в отчете наряду с остальными показателями:
Что в итоге получилось
Общий вид отчета – это сводная таблица с данными по неделям и фильтрами по направлению и каналу:
Сама сводная таблица за несколько кликов трансформируется на разные уровни от общей картины, до детальной картины по рекламным кампаниям.
Специфика отчета в том, что в нем нет графиков, но есть сводная таблица с хорошими возможностями трансформации. На первом этапе клиенту важно было получить именно такую сводную таблицу, так как внутри компании уже сформирован ряд процессов вокруг нее. Стоит отметить, что структура модели данных и отчета допускает очень широкие возможности по визуализации этих данных и в дальнейшем клиент сможет добавить красивые графики самостоятельно или снова обратиться к нам.
Выполненная работа по созданию отчета помогла клиенту решить основную проблему – автоматизацию процесса сбора и оперативное получение данных, доступных в удобной и привычной форме. С учетом специфики продукта и источников трафика не все получилось автоматизировать на текущем этапе, однако и предложенное решение значительно упростило ранее выполнявшиеся операции по подготовке отчета.
Стоимость и сроки выполнения проекта
Стоимость проекта мы не можем озвучить, потому что это коммерческая тайна. А вот временные затраты можем, вместе со всеми встречами, скайпами, мозговыми штурмами,разработкой обучающих материалов, составлении документации, шаблонов и работами затраты составили 190 часов со стороны команды Mello.
Примечание редакции. Если вы заинтересованы в реализации подобной задачи – напоминаем вам о возможности заполнить бриф для дальнейшей оценки ваших задач и реализации их нашими партнерами.
