Запрос по API к Яндекс.Метрика может выглядеть так

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

Пользовательская выгрузка из Яндекс.Метрика
3. Webhook для данных, к которым пока нет коннектора

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

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

В примере получаем выгрузку данных в формате 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
3. Правила матчинга (поиск совпадений) и суррогатные ключи
Хорошо, предположим, что метки есть. Давайте склеивать данные. Решение в лоб: сделать джоин таблиц с условием совпадения всех полей. Выглядит как-то так:

Что если значение одного из столбцов NULL? Совпадения не случится (гуглим NULL = NULL).
Я поступил несколько иначе: прежде чем делать джоин, я готовлю конкатенированный ключ и хеш-ключ:

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

Суррогатный хеш-ключ идеален для джоина; ключ конкатенации понятен человеку
4. Механика формирования Сквозной Аналитики
Должно быть уже заметили, что я использую full join. Да, это как раз тот самый кейс, когда мне важно не потерять ни одну из частей уравнения в случае, если совпадения не произошло: ни лид из CRM, ни строку трат из РК, ни конверсию из Я.Метрики.
Во-первых, необходимо следить за тем, чтобы показатели не задваивались при склейке (очень запросто получается в таблицах с несколькими строками по одному ключу). Для этого я написал дата-тест, сверяющий суммы и количества с целью учета их один и только один раз.
Во-вторых, всем становится как-то досадно, когда для лида не находится источник трафика и сумма трат из РК. Для быстрого поиска причин я ввёл две мета-колонки:

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

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

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

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

Автоматизация формирования витрин данных с помощью Github Action
И тогда я создал Github Action kzzzr / mybi-dbt-action — действие, которое автоматически разворачивает Docker-контейнер, устанавливает зависимости, подключается к СУБД, выполняет расчеты витрин, прогоняет тесты. Потратил значительное количество времени, зато теперь витрины собираются в автономном режиме, пока я спокойно сплю.
Достигнуты значительные успехи
Абсолютный контроль над данными и результатами. Прозрачность и управляемость. Все алгоритмы, функции, парсинг и соединения как на ладони. Самодокументируемый код.
Да, порог входа для новичка довольно высок. Зато любые изменения — максимально быстро. Я могу слепить из данных всё, что угодно. Я не скован рамками и ограничениями других решений. И мне это нравится.
Считайте, что к этим пунктам я уже поставил галочку в своём 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.
Следите за обновлениями и задавайте вопросы, я буду рад на них ответить.
Благодарю за внимание.
===========
Источник:
habr.com
===========
Похожие новости:

• [Open source, JavaScript, Программирование, Визуализация данных] Новый график на Moiva.io с данными от #StateOfJS
  
• [Анализ и проектирование систем, Совершенный код, Angular, TypeScript] Open-Closed Principle в Angular
  
• [Amazon Web Services, Big Data] AWS Athena: GEOIP lookups (перевод)
  
• [SQL, API] Дизайн пагинации страниц в API (перевод)
  
• [SaaS / S+S, Big Data, Облачные сервисы, Периферия] От появления ЭВМ до периферийных вычислений в телекоме
  
• [Data Mining, Big Data, Биотехнологии, Здоровье, Будущее здесь] COVID-19 и фитнес-трекеры
  
• [Высокая производительность, PostgreSQL, SQL, Администрирование баз данных] DBA: Ночной Дозор
  
• [PostgreSQL, SQL, Администрирование баз данных, Управление разработкой] «PostgreSQL в кейсах» — продвинутый вебинар 28 января
  
• [Python, Машинное обучение, Контент-маркетинг, Искусственный интеллект, Социальные сети и сообщества] Нейросеть для раскрутки собачьего аккаунта в Инстаграм или робопёс в действии
  
• [Программирование, Анализ и проектирование систем, Проектирование и рефакторинг] Представление модели предметной области (МПО) и точек интеграции

Теги для поиска: #_analiz_i_proektirovanie_sistem (Анализ и проектирование систем), #_sql, #_big_data, #_vizualizatsija_dannyh (Визуализация данных), #_data_engineering, #_skvoznaja_analitika (сквозная аналитика), #_marketing (маркетинг), #_vizualizatsija_dannyh (визуализация данных), #_dashbord (дашборд), #_analiz_i_proektirovanie_sistem (
Анализ и проектирование систем
), #_sql, #_big_data, #_vizualizatsija_dannyh (
Визуализация данных
), #_data_engineering