Привет, Хабр! Меня зовут Давид, я performance-маркетолог в агентстве Molinos. Не так давно я начал изучать BI-аналитику — и приобретенные знания применил в своей работе. В кейсе расскажу как мне удалось разгрузить пару часов из рабочего дня наших сотрудников и автоматизировать клиентскую отчетность. Расскажу, как и с помощью чего построил дашборды для аналитики рекламных кампаний в Яндекс Директе — и дам полезные инсайты тем, кто думает о внедрении такого инструмента в свою агентскую практику.

С чего все начиналось

Когда-то в компании уже существовала система для автоматизации клиентской отчетности. Она основывалась на базе расширения для гугл-таблиц Adveronix и сервиса Looker Studio. Однако в прошлом году они прекратили свою работу в РФ — и с этого момента отчетность для клиента стало вести в разы сложнее. 

Специалистам приходилось вручную выгружать данные из Яндекс Директа и Метрики и сводить их в Excel, чтобы потом на их основе построить дашборд в сервисе. При этом, чтобы зайти туда, нужен был VPN. В итоге для клиента приходилось выгружать pdf-файл с дашбордом, в котором никакие селекторы уже нельзя было поменять — ни диапазон дат для данных, ни части кампании.

Такая схема занимала много времени, не хватало гибкости для мониторинга, а готовых решений, которые подошли бы именно нам, не было — нужно было что-то делать.

В чем заключается специфика отчетности для digital-агентства

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

В отчете для специалистов важна высокая параметризация, возможность постоянно отслеживать динамику показателей в разных разрезах аудитории и вариантах объявлений. Это необходимо для того, чтобы вовремя внести корректировки в рекламные кампании, отключить невыгодные сегменты аудитории или перераспределить бюджет. Также для специалистов с 4-5 проектами важно иметь под рукой удобную систему мониторинга по всем клиентам, чтобы не терять время на переключение между ними.

Приступаем к разработке

Расскажу поэтапно на какие части разделился процесс создания дашбордов. Кстати, суммарно на все про все ушло 100 часов.

Шаг 1. Анализируем рынок решений

Сперва я изучил решения, которыми мы могли бы заменить иностранный сервис.  Было необходимо, чтобы все важные для рекламных кампаний данные точно были в продукте и вовремя обновлялись. Большинство готовых программ аналитики, к примеру, выгружают данные в Google Sheets — это неудобно, так как они не обладают такой скоростью, как базы данных. Ну и конечно важно было потратить минимум денежных ресурсов. 

После недолгих размышлений я принял решение создать свою агентскую систему с дашбордами — этим мы бы убили всех зайцев одновременно. А я бы прокачал навыки в BI-аналитике. 

Шаг 2. Выбираем стек технологий

Изначально сложно сказать, какой мощности нужен сервер. Выбор зависит от объема используемых данных, количества активных пользователей, сложности визуализаций и запросов. Я взял сервер на базе ОС Ubuntu 22.04 со следующими характеристиками: 8 x 3.3 ГГц CPU • 16 ГБ RAM • 160 ГБ NVMe. На текущий момент основной диск загружен на 20%. Вообще можно было и арендовать сервер с меньшим количеством памяти, так как хостинг-провайдер может ее расширить в будущем.

Для визуализации и анализа данных я выбрал Yandex DataLens. В нем я подготовил два шаблона дашбордов: агентский и клиентский. При этом казалось, что если DataLense, Директ и Метрика — все продукты Яндекса, значит сбор аналитики будет лучше работать, но нет. 

Сейчас DataLense на стадии активной разработки, поэтому многих функций нужных нет — к примеру, глобального селлектора (так дату нужно выбирать отдельно на каждой странице дашборда, нельзя выбрать на одной и применить ко всем остальным). Еще один пример - это  экспорт/импорт и перенос воркбуков между окружениями в даталенс, этот функционал добавили буквально в конце мая. 

В качестве СУБД был выбран ClickHouse  — система колоночного типа, оптимизированная для аналитики больших объемов данных. Она позволяет быстро выполнять сложные OLAP-запросы (Online Analytical Processing). Изначально было создана для обработки петабайтов данных Яндекс Метрики.

Для выгрузки данных из рекламных систем выбрал «Поток» — готовое решение для работы с API Яндекс Директа и Яндекс Метрики и некоторых других сервисов. Его разработал Перепечаев Антон, маркетолог, BI-аналитик и автор телеграм-канала «Пытаюсь посчитать». Приложение разворачивается из докера на собственном сервере и работает в формате Enterprise, то есть доступ к чувствительным данным есть только у владельца сервера. Специально для читателей Хабра я попросил Антона немного рассказать про архитектуру приложения:

Приложение построено на микросервисной архитектуре. Выделены следующие сервисы:

1. Frontend-сервис — обеспечивает пользовательский интерфейс и взаимодействие с API.

2. Backend-сервис — содержит основную бизнес-логику и управляет внутренними процессами.

3. Scheduler-сервис — отвечает за планирование и запуск задач по расписанию.

4. Data Extraction-сервис — выполняет сбор и передачу данных из внешних источников

Микросервисы разворачиваются через Docker Compose. Для хранения аналитических данных используется внешний экземпляр ClickHouse. Основные сущности приложения:

1. Подключения — определяют источники данных. Поддерживаются Яндекс.Директ и Яндекс.Метрика. Хранят авторизационную информацию и параметры доступа к API.

2. Хранилище — место, куда загружаются данные. В частности, используется ClickHouse.

3. Импорты — описывают, какие данные необходимо выгружать. Включают выбор отчёта, набор полей и модель атрибуции.

4. Задачи — определяют расписание и правила выгрузки. Представляют собой аналог DAG в Airflow. Связывают импорт, подключение и хранилище, позволяют настроить периодичность и отслеживать статус выполнения.

Основное преимущество такого решения для агентства — простота интеграции в работу. Сотрудников достаточно один раз обучить созданию дашбордов по шаблону, не погружая их в технические детали. При этом они не пишут код, не работают с API напрямую и не занимаются оркестрацией процессов. По сути, приложение является user-friendly интерфейсом для настройки ELT-процесса (Extract, Load, Transform), где данные автоматически извлекаются из рекламных систем, загружаются в ClickHouse, где уже выполняется последующая трансформация и аналитика. 

Шаг 3. Аренда и настройка сервера

Изначально я думал выбрать хостинг Zomro. Однако на нем серверы находились в Нидерландах. Посоветовались с ИТ-отделом и решили разместиться на проверенном Timeweb, серверы которого находятся в Петербурге. Для доступа к консоли сервера использовали приложение Tabby

Шаг 4. Развертывание на сервере приложения для работы с API рекламных систем

В первую очередь, через терминал сервера установил на сервер Git. Затем с помощью команды git clone скачал файлы приложения на сервер. После оставалось только установить на сервер docker и docker compose. Далее — авторизация и развертывание приложения. Все это делалось с помощью команд в терминале. 

Шаг 5. Развертывание на сервере Clickhouse

Это я делал все так же из контейнера, а для работы с СУБД использовал приложение DBeaver. Опять же понравился простой и понятный интерфейс.

Шаг 6. Настройка и тестирование приложения

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

{
  "import_name": "main_stat",
  "source_system_name": "YANDEX_DIRECT",
  "download_details": {
    "report_name": "main_statistics",
    "report_type": "CUSTOM_REPORT",
    "field_names": [
      "AdGroupId",
      "AdGroupName",
      "AdId",
      "Age",
      "Sessions",
      "Bounces",
      "CampaignId",
      "CampaignName",
      "Clicks",
      "ClientLogin",
      "Conversions",
      "Cost",
      "Criterion",
      "CriterionId",
      "Date",
      "Device",
      "Gender",
      "Impressions",
      "MobilePlatform",
      "TargetingLocationId",
      "TargetingLocationName"
    ],
    "attribution_models": [
      "AUTO"
    ],
    "include_vat": true,
    "order_by_columns": []
  },
  "transform_details": null,
  "id": 17,
  "created_at": "2025-02-04T11:22:48.474987"
}

Шаг 7. Предобработка данных

На этом этапе провел предобработку данных в DBeaver: объединил их из разных источников, создал представления (view) для оптимизации и ускорения выполнения запросов.

Шаг 8. Работа в DataLens

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

Шаг 9. Подготовка шаблона агентского и клиентского дашбордов

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

Пример клиентского дашборда

Пример агентского дашборда

Шаг 10. Написание руководства для отдела

Осталось только написать пошаговое руководство: как с нуля по готовому шаблону подготовить агентский и клиентский дашборд. Готовую инструкцию протестировал на коллеге. Что-то я не учел сразу, к примеру — для замены источника данных в дашборде, нужно определенным образом перенести новый источник в существую структуру. Для таких интуитивно непонятных моментов в будущем я встроил в руководство видео/скриншоты с иллюстрацией процесса. 

Если суммировать, то вот такое решение получилось у нас на выходе:

1. Для агентского и клиентского дашбордов настроены специфические импорты данных под нужды специалистов и клиентов. 

2. Каждую ночь данные автоматически выгружаются за предудыщий день, а данные по бюджетам аккаунтов обновляются каждый час — это позволяет клиентам и специалистам отслеживать состояние бюджета без необходимости входа в Яндекс Директ. 

3. Все данные поступают в ClickHouse. Там уже созданы представления (view), которые обрабатывают часть данных, объединяют данные из разных источников (операция join) и ускоряют выполнения запросов.

4. Часть данных финально обрабатывается внутри DataLens. На основе представлений и таблиц строятся чарты (таблицы, графики, индикаторы, тепловые карты). А уже на их основе строятся дашборды: у клиентов — с ключевыми метриками, у специалистов — с детализированной информацией только по его проектам.

Для сотрудников агентства получили решение, которое значительно ускорило ежедневный сбор и мониторинг аналитики –– без необходимости дополнительного обучения. Помимо этого специалист может остлеживать степень достижения KPI по проектам в режиме реального времени — плановые показатели вносятся прямо в вычисляемые поля в DataLens, а заранее подготовленные формулы считают план-факт. Это делает трек и продвижение по нему прозрачнее. 

Что сейчас

Дашборды мы начали запускать в мае. На текущий момент у каждого специалиста есть свой дашборд с проектами, которые он ведет. Это ощутимо сокращает трудозатраты специалистов на аналитику и мониторинг, повышает шанс на своевременное реагирование на изменение в рекламных кампаниях. Больше нет необходимости постоянно вручную выгружать статистику по рекламным кампаниям из Яндекс Директа и самому считать показатели в разрезе различных группировок. Все основные показатели считаются автоматически. 

У части клиентов тоже есть свои дашборды. Сейчас они получают все те же данные, что получали раньше, но в режиме реального времени. Для некоторых клиентов, к примеру, важно отслеживать показатели в не шаблонных срезах — например в разрезе городов рекламирования. В таком случае, мы можем отойти от стандартного шаблона, добавить новые срезы и селекторы. В общем, сделать все, что потребуется тому или иному клиенту.

И что потом

Сейчас я планирую обогащать данные: начать автоматизировано выгружать статистику из других рекламных источников (к примеру, GoogleAds). Это дополнительно сократит трудозатраты специалистов и позволит иметь полноценную отчетность и по комплексным проектам. 

Также кроме оптимизации самих дашбордов ставлю перед собой задачу еще больше интегрировать решение в агентскую практику. Несмотря на то, что у каждого сотрудника есть доступ к детальным инструкциям с видео и скриншотами, сталкиваюсь с обратной связью о том, что все выглядит «страшно». Возможно это нормальная реакция на все новые технологии, но хочется подойти к вопросу стратегически и с человеческой точки зрения. Так что ждите следующий материал на тему улучшения пользовательского опыта :)