Сквозная Аналитика на Azure SQL + dbt + Github Actions + Metabase

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

За последние годы у меня накопился довольно обширный опыт работы с данными и тем, что сейчас называют 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

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. Приятные дополнительные возможности

• Конфигурирование периодичности и глубины выгрузок. Хотел бы задать период с горизонтом 3 дня назад (late arriving data)? Супер, готово.
• Проверка загруженных данных с помощью “коэффициента отклонения”.
• Взаимодействие с сервисом через API.

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

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

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

Основные блоки 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)

Для последовательности и структурированности я делю 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

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

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

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

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

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

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

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

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

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

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

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

2. Хаотичный учет сделок и воронок в 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 помогают в поиске источников проблем

В третьих, что если у владельца 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 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.

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

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