Title


Привет, Хабр! Меня зовут Артемий Козырь.


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


Не так давно также разгорелся интерес к сфере интернет-маркетинга и Сквозной Аналитики, и не на пустом месте. Мой друг из действующего агентства снабдил меня данными и кейсами реальных клиентов, и тут засквозило понеслось.
Получается довольно интересно: Azure SQL + dbt + Github Actions + Metabase.


Половина успеха в правильном понимании задачи


Итак, постараемся без дифирамб и сразу к делу. Портрет Клиента (это тот, для кого мы делаем сервис): владелец интернет-магазина / розничной сети / мобильного приложения / образовательной платформы. Он преследует следующие цели:


  • Позиционирование и продвижение своего продукта; рост бизнеса
  • Оптимизация каналов продвижения: фокус на направлениях, которые дают лучший результат
  • Контроль расходов и показателей возврата на каждый вложенный рубль
  • Сегментация пользователей и выстраивание коммуникации с ними

В среднем он пользуется 4-мя группами сервисов:


  • CRM (AmoCRM, Bitrix24) — лиды, воронки и продажи; жизненный цикл и фактические атрибуты сделок
  • Счетчики посещаемости (Yandex.Metrika, Google Analytics) — просмотры, клики, интерес, вовлеченность аудитории
  • Рекламные кабинеты (Facebook, Google Adwords, Yandex.Direct) — платный трафик, медиа-продвижение, контекстная реклама, промоушены и партнерки
  • Call-tracking/Event tracking — сбор поведенческих факторов, оффлайн-событий

На помощь Клиенту приходит Агентство, которое оказывает комплексные услуги: разработка стратегии, создание креативов, настройка счетчиков и CRM, закупка рекламы. С Клиентом согласовывается объем работ и целевые показатели. Выглядеть это может примерно так:


Согласованная форма отчетности Агентства перед Клиентом
Согласованная форма отчетности Агентства перед Клиентом


Самой интересной и сложной частью, на мой взгляд, является формирование сводной отчетности по результатам деятельности. С инженерной точки зрения задача сводится к следующему:


  • Выгрузить данные из источников
  • Собрать в одной базе данных (интеграция)
  • Построить логическую модель Хранилища Данных и Витрины (вся соль здесь!)
  • Визуализировать метрики на дашбордах (это тоже нетривиально)
  • Обеспечить сопровождаемость и поддержку инфраструктуры (может быть очень нудно)

Пылесосинг данных сервисов (Интеграция)


Даже ниндзя-одиночке сложно собрать и поддерживать набор коннекторов в актуальном и работоспособном состоянии. Ранее я выступал с небольшим докладом на эту тему: Сквозная аналитика: коробочные решения или самостоятельная сборка? (с 3:13).


Для своего решения я выбрал сервис myBI Connect. Алексей и его команда делают по-настоящему качественный сервис, который в состоянии удовлетворить даже самые изощренные требования инженеров и бизнес-пользователей. Давайте взглянем, что доступно из коробки:


1. Базовые выгрузки и модель детального слоя


Заботливо подготовлены и задокументированы модели детального слоя (те самые звездочки и снежинки), суррогатные ключи, метаданные обновлений и ETL-джобов.


Например, для Facebook доступны уровни детализации Кампаний (Campaigns), Групп Объявлений (Adsets) и Объявлений (Ads), включая невероятный набор метрик, таких как среднее количество просмотров на человека, охват, реакции на публикации, репосты и т.д.


Схема детального слоя Facebook myBI Connect
Схема детального слоя Facebook myBI Connect


2. Кастомизированные отчеты/выгрузки/представления


Все работали с Яндекс.Метрикой? По сути это конструктор отчетов с ныне очень известным Яндекс.Clickhouse под капотом. Чтобы собрать отчет, необходимо выбрать ряд Измерений, Метрик, Фильтров (образующих Сегменты).


Запрос по API к Яндекс.Метрика может выглядеть так
https://api-metrika.yandex.net/stat/v1/data.csv
   ?ids=55254416
   &dimensions=ym:s:date, ym:s:UTMSource, ym:s:UTMMedium, ym:s:UTMCampaign
   &metrics=ym:s:visits, ym:s:ecommercePurchases, ym:s:ecommerceRevenue
   &date1=2020-12-01
   &date2=2020-12-31
   &group=day
   &lang=en
   &accuracy=full
   &sort=ym:s:date
   &limit=100000
   &pretty=true

А теперь вспомните о трансформации полученного в ответ JSON-документа, регулярность выгрузок (установка на расписание или cron), обработку статусов запросов (requests), удаление дубликатов и т.д. Не хотелось бы отягощать свое решение поддержкой всего этого.


С использованием myBI Connect я один раз декларативно задаю структуру результирующего набора данных и регулярно получаю свежую выгрузку в реляционную СУБД без всякой головной боли.


Пользовательская выгрузка из Яндекс.Метрика
Пользовательская выгрузка из Яндекс.Метрика


3. Webhook для данных, к которым пока нет коннектора


  • Самописная CRM? Экзотический формат выгрузки?
  • Нет проблем! Webhook в помощь.

И такие данные тоже можно довольно легко собирать. Так может выглядеть скрипт получения конверсий из inhouse-CRM, отдающей данные в формате XML:


# get data from XML endpoint with curl utility
curl "https://www.internal-crm.ru/order-list.xml?date-from=01-12-2020&date-to=31-12-2020" -o export.xml

# convert XML to JSON with xq utility
xq . export.xml > export.json

# parse JSON doc with jq utility
jq '[."order-list".date[] | .order[]]' export.json > parsed.json

# post to myBI Connect Webhook endpoint with curl utility
curl --header "Content-Type: application/json"  --request POST  --data @parsed.json  https://app.mybi.ru/webhook/23576/xhsfcxmlyh/

В примере получаем выгрузку данных в формате XML, преобразуем в JSON, парсим данные, отсылаем структурированный набор в myBI Connect через Webhook. Один несложный shell-скрипт, и данные почти мгновенно оказываются в таблице с конверсиями в Хранилище.


4. Приятные дополнительные возможности



В итоге я имею исходные наборы данных в понятном и ожидаемом формате, с регулярным обновлением. И это основа решения.


Организация Хранилища Данных


В значительной мере работа над Хранилищем Данных заключается в Моделировании данных (Data Modeling), или построении логической модели. Формально это означает преобразование, обогащение, сведение разрозненных наборов данных с целью получить интересующий набор показателей (метрик) с соблюдением бизнес-правил и логики расчетов.


Основные блоки DWH: источники, детальный слой, витрины данных
Основные блоки DWH: источники, детальный слой, витрины данных


Концептуально DWH можно разделить на ряд областей:


  • (1) Источники данных — исходные наборы из рекламных кабинетов, счетчиков посещаемости, CRM
  • (2) Детальные данные — очищенные и унифицированные таблицы атомарной детализации (например, клик); имеют общую структуру для каждого Клиента
  • (3) Витрины данных — ключевые бизнес-метрики, которые мы отслеживаем; индивидуальная структура для каждого Клиента

Для формирования логической модели я воспользовался dbt. В моем случае Хранилище Данных представляет из себя git-репозиторий, состоящий из набора файлов .sql (код витрин) и .yaml (конфигурация). На Хабре есть хороший обзор dbt на русском языке: Data Build Tool или что общего между Хранилищем Данных и Смузи.


В роли движка-СУБД может выступать любая из популярных сегодня аналитических баз данных: BigQuery, Redshift, Snowflake, Postgres, Spark, Presto. В моем случае это Azure SQL Database (managed SQL Server). По сути для меня не имеет значения, на какой инфраструктуре строить Хранилище; важна смысловая составляющая проекта, бизнес-логика, алгоритмы (читай код).


Структура проекта: git-репо с кодом (.sql) и конфигурацией (.yaml)
Структура проекта: git-репо с кодом (.sql) и конфигурацией (.yaml)


Для последовательности и структурированности я делю DWH на ряд слоев:


Цепочка зависимостей моделей: Источники -> Стейдж -> Вспомогательные -> Витрины
Цепочка зависимостей моделей: Источники -> Стейдж -> Вспомогательные -> Витрины


1. Источники (Sources)


По сути это ссылки-указатели на таблицы, которые регулярно обновляются сервисом myBI Connect. Для моего решения это исходные данные.


2. Стейдж (Staging)


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


  • Выполняю очистку полей, парсинг UTM-меток и значений
  • Привожу типы данных и форматы: текст в числа, таймстемпы и т.д.
  • Унифицирую наименования полей (при необходимости)

3. Вспомогательный (Auxiliary)


Модели слоя Aux предназначены для формирования срезов по отдельным предметным областям. Здесь появляются первые денормализованные (широкие) аналитические таблицы:


  • Объединение фактов и измерений — клики, конверсии, продажи обретают контекст (алгоритмически это джоин таблиц)
  • Формирование суррогатных ключей для соединения таблиц
  • Применение коэффициентов: НДС, комиссии агентства и т.д.

4. Витрины (Marts)


Витрины данных — это то, на что смотрит конечный пользователь. Это семантический слой доступа, прослойка между бизнес-атрибутами и алгоритмами их формирования. Здесь я расположил максимально прилизанные и корректные данные. Именно здесь фокусируется внимание и интерес пользователей:


  • Агрегация и объединение данных, например, суточные показатели из всех рекламных кабинетов
  • Расчет бизнес-метрик: абсолютные и относительные показатели, их динамика
  • Формирование Сквозной Аналитики: как раз тот самый кейс для full join, когда ни одна из составляющих уравнения не должна быть потеряна

Дашбординг и удобный доступ к данным


— Что кроме дашборда можно показать клиенту?
— Правильно, почти ничего.

Поэтому с точки зрения клиента визуальная подача результатов — это самое основное. И неважно, сколько времени вы потратили на выгрузку по API, отладку функций и макросов, или создание Github Action, который регулярно обновляет витрины.


Визуализация динамики основных показателей на интерактивном дашборде
Визуализация динамики основных показателей на интерактивном дашборде


Дашборд это ни в коем случае не статический элемент. Здесь важен интерактив и динамика. Это портал к данным, откуда можно прыгнуть в детализацию или сфокусироваться на части данных (сегменты).


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


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


Семантический слой доступа к метаданным витрин и детального слоя
Семантический слой доступа к метаданным витрин и детального слоя


В удобном интерфейсе все метаданные как на ладони:


  • Доступные витрины и их описание
  • Атрибутный состав, типы данных
  • Преднастроенные сегменты, метрики, вопросы созданные кем-то другим
  • Приложение любезно предложит автоматический дашборд (x-ray) на данных витрины

Работу всей этой красоты обеспечивает Open Source BI решение Metabase (рекомендую!). Я разместил его на Amazon Elastic Beanstalk, и это уже полноценное продуктивное развертывание:


  • Официальный Docker-образ и предсказуемое окружение
  • Использование Postgres (AWS RDS) в качестве бэкенда метаданных
  • Балансировщик нагрузки (Load Balancing) и постоянный пинг-Healthcheck сервиса
  • Понятное и простое обновление версии приложения Metabase

Продуктивное развертывание BI Metabase в облаке AWS Elastic Beanstalk
Продуктивное развертывание BI Metabase в облаке AWS Elastic Beanstalk


Рейтинг нетривиальных проблем


Неужели всё так просто? Конечно нет! Если к текущему моменту сложилось ощущение, что всё выстраивается гладко и бесшовно, то это большое заблуждение. Ниже я системно опишу те болевые точки, с которыми столкнулся.


1. Кривая разметка и парсинг идентификаторов


Вся Сквозная Аналитика строится вокруг идентификаторов, по которым можно сопоставить данные из различных источников. Потому она и сквозная, т.е. проходящая сквозь пространство и время сервисы и учетные системы.

Это ключевая фишка. Нет идентификаторов-якорей — нет сквозной аналитики. Важно следовать лучшим практикам и быть консистентным при запусках Кампаний во всех Рекламных Кабинетах.


(1) Легко допустить ошибку: поставить лишний символ, например, {фигурные скобки}, забыть указать UTM-метки (или указать дважды!), теги, ключевые слова. После запуска, к сожалению, это восстановлению уже не подлежит. Здесь наши полномочия всё, окончены.


(2) В другом случае мы теряли метки при обработке редиректа на веб-сервере. Веб-разработчик установил какой-то хитрый php-скрипт, назначение которого осталось для меня тайной.


(3) В третьих, это особенности разметки конкретных кабинетов. Я эмпирически выяснил, что Google Adwords к числовому идентификатору может подставлять буквенный префикс типа aud-, kwd-, pla-.


(4) Хорошая разметка, для примера. В ходе парсинга удалось достать все идентификаторы!


Примеры учета особенностей разметки для последующего парсинга идентификаторов
Примеры учета особенностей разметки для последующего парсинга идентификаторов


Все эти и другие скрытые особенности необходимо неустанно контролировать и учитывать в алгоритмах парсинга, чтобы получать качественные результаты.


2. Хаотичный учет сделок и воронок в CRM


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


— Нет желания поддерживать актуальность, полноту и достоверность в CRM-системе, но при этом хочется иметь красивый результат в отчетах?
— Запомните: это так не работает.

Каков мой ответ? Я вывел на дашборд таблицу со сделками, в которых с разметкой не всё в порядке.


Проверочный дашборд по проблемным сделкам в CRM
Проверочный дашборд по проблемным сделкам в CRM


3. Правила матчинга (поиск совпадений) и суррогатные ключи


Хорошо, предположим, что метки есть. Давайте склеивать данные. Решение в лоб: сделать джоин таблиц с условием совпадения всех полей. Выглядит как-то так:


from costs c
   full join conversions cv on
           c.[Дата] = cv.[Дата]
       and c.[Идентификатор кампании] = cv.[Идентификатор кампании]
       and c.[Идентификатор группы объявлений] = cv.[Идентификатор группы объявлений]
       and c.[Идентификатор условия показа] = cv.[Идентификатор условия показа]   

Что если значение одного из столбцов NULL? Совпадения не случится (гуглим NULL = NULL).


Я поступил несколько иначе: прежде чем делать джоин, я готовлю конкатенированный ключ и хеш-ключ:


-- фиксируем список полей для составного ключа
{%- set key_field_list = [
       '[Дата]',
       '[Идентификатор кампании]',
       '[Идентификатор группы объявлений]',
       '[Идентификатор условия показа]'
   ]
-%}

-- собираем хеш-ключ и ключ конкатенации в макросах
select

     {{ concat_key(key_field_list) }} as concat_key
   , {{ surrogate_key(key_field_list) }} as hash_key

...

-- условие джоина приобретает вид:
from costs c
   full join conversions cv on c.hash_key = cv.hash_key

Простое и элегантное решение. И при этом ключ конкатенации может быть однозначно интерпретирован человеком.


Суррогатный хеш-ключ идеален для джоина; ключ конкатенации понятен человеку
Суррогатный хеш-ключ идеален для джоина; ключ конкатенации понятен человеку


4. Механика формирования Сквозной Аналитики


Должно быть уже заметили, что я использую full join. Да, это как раз тот самый кейс, когда мне важно не потерять ни одну из частей уравнения в случае, если совпадения не произошло: ни лид из CRM, ни строку трат из РК, ни конверсию из Я.Метрики.


Во-первых, необходимо следить за тем, чтобы показатели не задваивались при склейке (очень запросто получается в таблицах с несколькими строками по одному ключу). Для этого я написал дата-тест, сверяющий суммы и количества с целью учета их один и только один раз.


Во-вторых, всем становится как-то досадно, когда для лида не находится источник трафика и сумма трат из РК. Для быстрого поиска причин я ввёл две мета-колонки:


  • meta_is_row_match (true/false) — случилось ли совпадение кусочков из разных систем?
  • meta_row_origin — из какой системы пришла оригинальная строка (Я.Директ/Я.Метрика/AmoCRM)?

Мета-колонки is_match, row_origin помогают в поиске источников проблем
Мета-колонки is_match, row_origin помогают в поиске источников проблем


В третьих, что если у владельца CRM всё схвачено и есть хитрый мастер-план по подсчету метрик? Нужно только считать как задумано. Нет проблем, сделать можно всё, что угодно. Даже так:


   select 
       ...
       , sum(1) as [Сделки]
       , sum(CASE WHEN [Теги] LIKE '%первич%' THEN 1 ELSE 0 END) as [Сделки - первичная коммуникация]
       , sum(CASE WHEN [Статус сделки] LIKE '%успешн%' THEN 1 ELSE 0 END) as [Сделки - успешно реализовано]
       , sum(CASE WHEN [Статус сделки] LIKE '%успешн%' THEN [Сумма] ELSE 0 END) as [Сумма - успешно реализовано]
       ...

5. Модель атрибуции может стать причиной расхождений в показателях


Несоответствие данных это моя самая большая боль. Непрекращающиеся итерации сверки данных и поиска причин расхождений. С одной из них я довольно долго промучился, пока не нашел способ явно задавать модель атрибуции для выгрузки по API из Яндекс.Метрики. В теле запрашиваемого измерения необходимо явно указать модель атрибуции, например ym:s:<AttributionModel>UTMSource:


ym:s:lastsignUTMSource -- последний значимый источник
ym:s:firstUTMSource -- первый источник
ym:s:lastUTMSource -- последний источник
ym:s:last_yandex_direct_clickUTMSource -- последний переход из Директа

6. Витрины необходимо регулярно актуализировать (обновлять)


Оказывается это не так-то просто. Я могу дергать запуски скриптов вручную, но в продуктивном решении хочется расчеты выполнять по заданному расписанию и делать это надежно.


Автоматизация формирования витрин данных с помощью Github Action
Автоматизация формирования витрин данных с помощью Github Action


И тогда я создал Github Action kzzzr / mybi-dbt-action — действие, которое автоматически разворачивает Docker-контейнер, устанавливает зависимости, подключается к СУБД, выполняет расчеты витрин, прогоняет тесты. Потратил значительное количество времени, зато теперь витрины собираются в автономном режиме, пока я спокойно сплю.


Достигнуты значительные успехи


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


Да, порог входа для новичка довольно высок. Зато любые изменения — максимально быстро. Я могу слепить из данных всё, что угодно. Я не скован рамками и ограничениями других решений. И мне это нравится.


Добавь новый аккаунт? Поменяй коэффициент?
1 минута на точечное редактирование кода и 5 минут на расчет витрин.
Что, так быстро?
Именно.

Считайте, что к этим пунктам я уже поставил галочку в своём TODO:


  • Выгрузки из источников: базовые, кастомные API, любые другие события через Webhook
  • Детальный слой Хранилища Данных, импортируемый как модуль dbt CORE и используемый для всех проектов. Содержит код и тесты.
  • Логика матчинга данных уровня Кампания + Группа Объявлений + Объявление + Ключевое слово (Хеш- и Конкат-ключи)
  • Вариант визуализации (Дашборд + Фильтры), прошедший приемку клиента
  • Регулярное обновление витрин (пока раз в сутки) через Github Actions
  • Статический вебсайт с документацией, метаданными, графом зависимостей витрин (DAG)
  • Условия для совместной работы над проектом: Git-репо, изолированные контуры DEV и PROD.
  • Бриф на быстрый запуск для Клиента: аккаунты, бизнес-правила, метрики.

Вектор дальнейшего развития


Конечно, он у меня есть. Следующими шагами я хотел бы сделать:


  • Покрытие тестами кода и данных — Continuous Integration & Data Tests. И мне уже приснилось, как это сделать.
  • Пульс Качества данных (Data Quality): актуальность данных, выявление проблем в разметке. Кстати, подобный серьезный проект я реализую для Wheely и уже писал об этом: Кто ответит за качество аналитики: QA для Хранилища Данных.
  • Домен 1-го уровня для дашбордов. Чтобы солидно, а не http://spasibo-metabase.eu-central-1.elasticbeanstalk.com/
  • Попробовать визуализацию с помощью Apache Superset вместо Metabase. Просто очень хочется, ищу повод.
  • Бизнес-документация логики формирования витрин. Всё прозрачно: зашел и сам разобрался, откуда взялись эти числа.
  • Движение в сторону self-service BI. Даю витрины и их описание. Далее строите те метрики, отчеты, дашборды и фильтры, какие пожелаете.
  • Фишки для Агентства: сводный анализ по всем Клиентам, Рекламным Кабинетам, учет индивидуальных ставок НДС.
  • Больше кейсов. Больше данных. Развитие! Хочется больше погрузиться в болевые точки и потребности клиентов.

Итак, зачем я здесь?


Мне нравится мой pet-project, хотя я всё еще не дал ему легендарное название. Этой публикацией я преследую несколько целей:


  • Помочь тем, кому моё решение принесет пользу. Есть здесь те, у кого болит?
  • Найти единомышленников и неравнодушных контрибьюторов. Пока код в статусе Private, но высока вероятность вывести проект в public Open Source.
  • Услышать экспертное мнение. Возможно, именно ты наступал на эти грабли и мог бы посоветовать что-то дельное.
  • Просто пообщаться. Если возникли мысли после прочтения материала — поделитесь в комментариях.

Я буду публиковать новости, связанные с этим проектом в telegram-канале https://t.me/enthusiastech.


Следите за обновлениями и задавайте вопросы, я буду рад на них ответить.


Благодарю за внимание.