Специалисты агентства тратили на создание и обновление отчетов для клиента сотни часов в год. Дмитрий Сергеев, аналитик в MediaNation, рассказывает, как мы помогли автоматизировать подготовку отчетности с помощью Python и Apache AirFlow и перераспределить время специалистов с подготовки отчетов на более плотную работу с кампаниями.
Клиент
«Читай-город» — одна из крупнейших в России сетей книжных магазинов
Проблема
Для «Читай-город» мы оказываем комплекс услуг: ведем контекстную и таргетированную рекламу, продвигаем мобильное приложение. Чтобы регулярно отслеживать эффективность рекламы в перечисленных каналах и оптимизировать кампании, специалисты по рекламе и аккаунт-менеджеры MediaNation активно использовали сложные отчеты.
Такие отчеты содержали большое количество источников данных, дополнительные параметры для анализа статистики и показатели, которые трудоемко извлекать из сотен рекламных кампаний. Команде агентства требовалось нескольких часов в неделю на создание и обновление таких отчетов, что было очень трудозатратно.
Отдел аналитики MediaNation решил помочь коллегам и предложил создать автообновляемые отчеты.
Изучение отчетов и погружение в проект
Наши аккаунт-менеджеры вручную создавали и обновляли два ключевых отчета:
Первый отчет — сводная таблица в Excel по всем рекламным кампаниям
В этой таблице агрегированы данные из нескольких источников:
- статистика Яндекс Директ,
- данные из Яндекс Метрики,
- информация из Clickhouse клиента,
- словари с данными о кампаниях в Google Sheets.
Такой отчет был нужен для анализа статистики кампаний. Он включал множество срезов, которые можно было настраивать в зависимости от задач и гипотез. На создание двух отчетов в неделю у специалистов уходило четыре часа.
Второй отчет — таблица в Google Sheets со статистикой продвижения индивидуальных изданий (книг)
Она содержала данные за весь рекламный период, агрегированные по каждому изданию, поскольку одно издание может продвигаться в нескольких кампаниях.
Эта таблица позволяла увидеть статусы кампаний и оценить эффективность продвижения отдельных изданий. На создание такого отчета у специалистов уходило около трех часов в неделю.
Формирование первого отчета
Для построения автообновляемого отчета №1 «Статистика по всем кампаниям» мы шли по следующему плану:
Импортировали библиотеки и создали логи для отслеживания ошибок
Мы импортировали все необходимые библиотеки, разработанные нашей командой, а также стандартные библиотеки языка Python. Далее создали объект Logger для отслеживания ошибок в процессе работы программы. Он присылал сообщения об успешном выполнении или невыполнении каждого значимого фрагмента кода, что позволяло нам оперативно находить и устранять ошибки.
Обратились к API Яндекс Директа и Яндекс Метрики
Полученная выгрузка отфильтровалась, обработалась и агрегировалась по дням, после чего мы привели данные к нужным форматам. Например, у дробных чисел отбросили лишние знаки после запятой, а оставшиеся округлили.
Работа с API может представлять сложности для многих маркетологов, поэтому мы заранее разработали высокоуровневые библиотеки Python для каждого сервиса. Они обладают упрощенными и понятными методами и ими удобнее пользоваться.
Объединили данные Директа, Метрики и всех словарей по общим ключам
Надежнее всего использовать идентификаторы, а не названия кампаний, поэтому мы объединили все данные по campaign_id.
Еще раз обратились к API Директа
Это было нужно, чтобы получить данные о статусах кампаний, стратегиях и пакетных стратегиях. Они отдавались сервером в формате json и преобразовывались в таблицу.
Обновили словари
В частности, словарь типов рекламных кампаний, который был разработан так, чтобы специалисты по контекстной рекламе могли самостоятельно заполнять его пользовательскими значениями. Словарь содержал колонки с уникальными идентификаторами кампаний и их типами.
Если специалисты создавали и запускали новую рекламную кампанию, фрагмент кода обнаруживал ее и добавлял в конец таблицы. При следующем просмотре этого словаря специалисты видели пустые значения, которые они могли быстро заполнить. При этом новые значения появлялись в статистике уже в ближайшую ночь. Остальные словари обновлялись аналогично.
Сформировали и выгрузили первый отчет.
Финальный отчет со статистикой по всем кампаниям по дням мы загрузили в Google Таблицу. Так у менеджера проекта и специалистов по контекстной рекламе появился доступ отчетам.
По запросу отчет ежедневно обновляется статистику за последние 90 дней. Код также работает ежедневно, полностью перезаписывая таблицу, и, таким образом, у нас теперь всегда есть доступ к актуальным данным.
Формирование второго отчета
Процесс построения отчета №2 «Статистика по индивидуальным изданиям (книгам)» шел по следующему плану:
Импортировали библиотеки и создали логи для отслеживания ошибок
Мы импортировали все необходимые библиотеки, разработанные нашей командой, а также стандартные библиотеки языка Python. Далее создали объект Logger для отслеживания ошибок в процессе работы программы. Он присылал сообщения об успешном выполнении или невыполнении каждого значимого фрагмента кода, что позволяло нам оперативно находить и устранять ошибки.
Рассчитали ассоциированные конверсии для второго отчета
Нам было важно определить не только прямое влияние каждой кампании на продажи, но и ее косвенный вклад в принятие решения о покупке.
В Яндекс.Метрике изначально отсутствовало отслеживание ассоциированных конверсий, в отличие от Universal Analytics, который ушел с рынка. Но у нас был доступ к сырым данным Метрики, включая идентификатор пользователей (client_id) в каждом визите, и мы смогли рассчитать эти показатели самостоятельно. Для оценки эффективности рекламных кампаний мы использовали комплексный анализ данных
Настроили трекинг пути клиента и получили сводку вклада каналов
На схеме справа мы видим, что клиент взаимодействовал с Яндекс Директом 1 января, совершил прямой вход на сайт 3 января и посетил VK 4 января. Позже 8 января клиент получил email-рассылку и в тот же день он взаимодействовал с рекламой ASA (Apple Search Ads).
Важным моментом является то, что после первого взаимодействия с Яндекс Директом клиент совершил две покупки: одну после прямого перехода с рекламы 3 января и вторую после email-рассылки 8 января.
В итоге Директ ассоциировался с двумя покупками, прямой вход — с двумя покупками, VK и mail — с одной покупкой, в то время как ASA не привел к покупкам в этой цепочке.
Агрегировали данные для второго отчета
Отфильтрованные данные агрегируются на уровне автора и названия книги. Если одна и та же книга продвигается в нескольких кампаниях, вся статистика по ним складывается.
В дополнение к статистике по книгам мы вычисляли общий статус продвигающих их кампаний. Если все кампании остановлены, у них появлялся статус «Выключено». Если все кампании велись на стратегиях с оплатой за конверсии, появлялся статус «На оплате за конверсии». А если хоть одна из кампаний была активна, ставился статус «Активна».
Настройка ежедневного автообновления отчетов
Когда мы написали полный код для выполнения всех необходимых задач, необходимо было сделать так, чтобы он работал ежедневно без участия человека. В этом нам помог Apache AirFlow — open-source инструмент, который позволяет разрабатывать, планировать и мониторить сложные рабочие процессы.
Весь написанный нами код необходимо было представить в виде DAG, основной сущности AirFlow. DAG — направленный ацикличный граф (Directed Acyclic Graph), где вершинами графа являлись задачи. В ходе разработки мы разделили код на пять задач (task), где некоторые из них не зависели друг от друга и выполнялись параллельно, что в итоге позволило сократить время обновления всего отчета
Каждая отдельная задача представляет из себя Python-функцию, обернутую в декоратор @task (функция, которая может изменять поведение других функций без изменения их кода)
В нашем случае Apache AirFlow был развернут в кластере Kubernetes. Он подошел нам, поскольку позволял
- каждой задаче выделить определенное количество ресурсов CPU и RAM. Например, для задачи выше выделили 2 CPU и 10 RAM.
- настроить GitSync, который позволяет обеспечить синхронизацию с GitHub и AirFlow один раз в определенное количество секунд.
- автомасштабировать количество узлов в кластере.
После добавление кода в GitHub в интерфейсе AirFlow появился созданный нами DAG. Мы могли запустить его первый раз вручную или дождаться автоматического выполнения, чтобы окончательно проверить, что таблицы корректно обновляются.
После того, как обновленные данные были загружены в Google Таблицы, аккаунт-менеджер проекта скачивал их и в несколько кликов обновлял уже сформированную в Excel сводную таблицу с помощью дополнения Power Query.
Результат
Нам удалось автоматизировать сложную маркетинговую отчетность, которой пользуются аккаунт-менеджеры и специалисты по контекстной рекламе.
Мы настроили автоматическое автообновление двух отчетов:
Таблица со статистикой всех рекламных кампаний по дням, включающая в себя все необходимые показатели: расход, показы, клики, конверсии из Яндекс Директ, конверсии из Яндекс Метрики, покупки, доход, статусы кампаний и др.
Таблица со статистикой по индивидуальным размещениям (книгам), включающая в себя все те же необходимые показатели, а также ассоциированные конверсии, рассчитанные по собственному алгоритму на основе сырых данных Яндекс Метрики.
Автоматизация позволила сэкономить время специалистов, которые ранее тратили около 200 часов в год на создание и актуализацию отчетов. Теперь отчеты генерируются автоматически за 10 минут, а освободившиеся часы специалистов пойдут на еще более плотную работу над оптимизацией и повышением эффективности рекламных кампаний.
