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

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

Обучение технологиям

• Документация по dataCraft Core
• Git
• Clickhouse (платный, но стоящий)
• dbt
• Airbyte (платный, в принципе, можно пропустить)
• Airflow

Настройка инфраструктуры

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

Используйте единое хранилище данных

Для удобного и простого использования сквозной аналитики необходимо куда-то складывать данные. Мы рекомендуем использовать «единое хранилище данных»:

Преимущества

• Решится проблема «поломки» дашбордов при изменении структуры в исходных данных. В BI систему будут поступать уже подготовленные данные.
• Просто получать данные и управлять ими: информация из разных источников хранится в одном месте.
• Однозначность данных: единое хранилище предотвращает дублирование и противоречия в информации.
• Улучшенная аналитика и отчетность: появится возможность оперативно и удобно «доставать» нужные данные для анализа параметров, которые не входят в регулярные отчеты. Аналитики смогут проводить более глубокий и сложный анализ, используя данные из различных областей компании.
• Безопасность данных: единое хранилище позволит обеспечить лучшую защиту данных и контроль доступа к ним.

Используйте dbt

dbt — надстройка над «чистым» SQL, которая добавляет функции для выстраивания процесса обработки данных средствами СУБД без привлечения внешних инструментов вроде Python. Даже если без Python не обойтись, через dbt можно проводить всю подготовительную работу.

В dbt SQL-запросы для обработки данных записываются в виде набора файлов. Эти файлы могут храниться в git, поэтому появляется возможность контроля версий. 

dbt отвечает за вызов файлов в нужном порядке. Он дает единую точку запуска разных вариантов пайплайна в виде консольного интерфейса. Фреймворк отслеживает зависимости SQL-запросов друг от друга и может запускать их в нужном порядке в несколько потоков. 

Организация проекта dbt делает его самодокументируемым. Типичные задачи обработки данных выделяются в макросы. Статичные данные и константы записываются в сиды. Для примеров запросов, использующих обработанные данные, есть отдельная папка. 

Макросы позволяют решать задачи, которые традиционно были трудоемки для SQL. Макрос отличается от SQL-функции тем, что работает с текстом самого запроса, выполняется до отправки запроса в СУБД и использует Python (Jinja). С их помощью можно, например, сделать UNION таблицы, у которых колонки расположены в разном порядке, или поменять местами строки и столбцы.

Преимущества

• Проще проводить отладку, так как dbt показывает промежуточные шаги обработки данных в виде таблиц и представлений, а значит для поиска причин ошибки доступна вся мощь SQL-запросов.
• Возможность контроля версий и документирования упрощает работу в команде и передачу задач между сотрудниками. 
• Возможность проверки/тестирования данных: встроенные (нулевые значения, допустимые значения, уникальность) и пользовательские. 
• Небольшие инвестиции в обучение окупятся тем, что сократится время на написание новых запросов и поддержку.

Используйте Clickhouse

ClickHouse — высокопроизводительная система управления и анализа данных, разработанная Яндексом. Эта система предназначена для обработки больших объемов данных в реальном времени. Она использует колоночное хранение данных и параллельные запросы для достижения высокой производительности.

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

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

Преимущества

• Скорость обработки больших объемов данных.
• Оптимизация для аналитических запросов.
• Горизонтальное масштабирование (увеличение производительности путем добавления новых узлов кластера).
• Эффективное использование ресурсов.
• Открытый исходный код.

Построение системы аналитики

Источник — например, Амо CRM, 1С, Яндекс.Директ.

Аккаунт — идентификатор, позволяющий различать разные кабинеты одного источника (Главный аккаунт Директ, HR-аккаунт Директа).

Стрим — одна таблица выгрузки из одного источника (статистика по кампаниям, статистика по объявлениям).

Шаблон — идентичный набор полей при выгрузке.

Пайплайн — набор данных с одинаковыми правилами накопления данных (полная перезапись/загрузка по датам/загрузка по периодам) и сходным набором полей.

Линк — набор данных с одинаковым правилом расчета бизнес-ключа (VisitID, дата + ID кабинета + ID кампании, Дата и время + номер телефона).

Что такое dataCraft Core

dataCraft Core — комплексный инструмент для построения хранилища данных, разработанный в Adventum. Он включает в себя методологию и автоматизацию для ее реализации. Основная цель dataCraft Core — упростить создание и структурирование моделей dbt.

Методология определяет, в каком порядке писать код, и как он должен быть разбит на небольшие модули (слои).

Автоматизация представляет собой разработку и использование макросов (шаблонов кода), которые позволяют сотруднику писать минимум своего кода и максимально использовать готовый.

Результат — более эффективное управление потоками данных, возможность быстро передавать рабочие проекты от сотрудника к сотруднику и в

перспективе строить базовые дашборды без аналитика. dataCraft Core в первую очередь предназначен для работы с источниками данных, поступающими в хранилище через Airbyte, но его универсальный дизайн позволяет использовать его и с другими источниками данных.

Рассмотрим подробнее каждый слой методологии.

1. Нормализация (Normalize)

На этом этапе происходит подготовка сырых данных для дальнейшей работы. Обычно сырые данные из Airbyte передаются в хранилище в формате JSON.

На этапе normalize происходит преобразование в более удобный формат: данные извлекаются и распределяются по столбцам, формируется таблица с привычной для пользователей структурой.

В настоящий момент этап нормализации полностью автоматизирован в dataCraft. Макрос:  {{ datacraft.normalize() }}

На этом этапе только извлекаем данные из JSON, никаких специальных преобразований не производится.

Зависимости

Поскольку это самый первый этап обработки данных, то зависит только от таблиц, передаваемых из Airbyte в хранилище данных.

Файл модели

Правило наименования

Название файлов модели на этом этапе должно состоять из пяти частей:

normalize_{название источника данных}_{название пайплайна}_{название

шаблона}_{название стрима}

Правило создания

Создается один файл на каждую уникальную комбинацию источника данных, шаблона и стрима.

Правила материализации

В dbt есть понятие материализация (materialization) — то, в каком виде будут сохраняться результаты запроса. В данном случае мы не указываем специальный тип материализации, и данные по умолчанию сохраняются как view (представление).

2. Догрузка (Incremental)

На этом этапе происходит обновление данных путем полной перезаписи либо постепенное обновление данных путем добавления новых данных к существующим. Это позволяет эффективно обрабатывать большие объемы информации и уменьшить нагрузку на систему при обновлении данных. 

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

В настоящий момент этап инкрементализации полностью автоматизирован в

dataCraft. 

Макрос: {{ datacraft.incremental() }}

Зависимости

Поскольку это второй этап обработки данных, то он зависит только от таблиц

нормализации, полученных на первом шаге Normalize.

Файл модели

Правило наименования

Название файлов модели на этом этапе должно состоять из четырех частей:

incremental_{название источника данных}_{пайплайн}_{название шаблона}_{название стрима}

Правило создания

Создается один файл соответственно каждому файлу нормализации.

Правила материализации

Тип материализации зависит типа пайплайна. В методологии мы

определили 4 вида пайплайнов: Events, DateStat, PeriodStat, Registry.

• Для Events и DateStat задаем инкрементальный тип материализации (materialized=’incremental’). Инкрементальные модели позволяют dbt вставлять или обновлять записи в таблице с момента последнего запуска этой модели без необходимости полной перезаписи таблицы.
• Для пайплайнов PeriodStat и Registry задается {{ config( materialized=’table’ ) }}. В таком случае при каждом запуске модели таблица полностью перезаписывается.

3. Сведение стримов (Join)

В рамках этого слоя объединяются в одну таблицу все стримы, которые есть для комбинации источник+пайплайн, за исключением пайплайнов Registry. Например, у AppMetrica для пайплайна events 5 стримов: deeplinks, events, install, screen_view и sessions_starts. На этом слое все они объединяются в таблицу join_appmetrica_events.

Помимо этого, перед объединением на этом же слое производится предобработка данных: 

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

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

• в Яндекс Метрике поле с id кампании называется ‘CampaignId’;
• в VK Ads поле с id кампании называется ‘id’.

Оба поля нужно переименовать одинаково, в нашем случае как ‘adId’. Еще одно важное действие на слое Join — добавление новой колонки __link, содержащей название линка, к которому относятся данные. Этот столбец понадобится на этапе Hash.

Сейчас этап join полностью автоматизирован в dataCraft.

Макрос: {{ datacraft.join() }}

Способ объединения таблиц зависит от типа источника данных:

• Например, для AppMetrica схожие стримы объединяются в один пайплайн с помощью UNION ALL. Чтобы объединить таблицы таким способом, поля, содержащие одни и те же показатели, должны называться одинаково, а количество столбцов должно быть равным, и они должны располагаться в одинаковом порядке.
• Стримы других источников, например, VK Ads или MyTarget, объединяются с помощью JOIN по общему полю, например, id кампании.

Если у комбинации источник+пайплайн только один стрим, или данные относятся к пайплайну Registry, то на этом шаге проводится только предобработка данных и приведение названий столбцов к единому стандарту.

Перед переименованием полей в разных источниках необходимо составить справочник стандартных названий.

Зависимости

Зависит от всех таблиц слоя Incremental, относящихся к одному источнику и пайплайну.

Файл модели

Правило наименования

Для всех пайплайнов, крооме Regestry, название состоит из трех частей:  join_{название источника}_{название пайплайна}. 

Для Regestry название формируется следующим образом:

join_{название источника}_{название пайплайна}_{название линка}.

Правило создания

Создается один файл на каждую комбинацию источник+пайплайн. Исключение — пайплайн Registry. У одного источника может быть несколько справочников, и на каждый из них создается файл модели.

Правила материализации

Для всех join таблиц тип материализации ‘view’.

4. Объединение источников (Combine)

На этом слое происходит объединение всех одинаковых пайплайнов. 

Например, пайплайн ‘events’ есть у двух источников: Яндекс Метрика и AppMetrica. Следовательно, данные по событиям из этих двух источников мы объединяем в одну таблицу.

Поскольку на этапе Join мы подготовили данные и переименовали одинаково столбцы в разных источниках, то объединение сводится к простому UNION ALL всех таблиц с одинаковым пайплайном. Если каких-то столбцов не хватает в одной из таблиц, то создается столбец:

• либо с пустыми значениями: » as utmSource,
• либо с нулевыми: 0 AS sales.

Исключение составляют таблицы, относящиеся к пайплайну Registry.

Сейчас этап combine полностью автоматизирован в dataCraft.

Макрос: {{ datacraft.combine() }}

Зависимости

Зависит от таблиц слоя Join c одинаковыми пайплайнами.

Файл модели

Правило наименования

Название файлов модели формируется так:

• combine_{название пайплайна} — для всех пайплайнов, кроме Registry;
• combine_{название пайплайна}_{название линка} — для Registry.

Правило создания

Создается один файл для каждого пайплайна. В случае пайплайна Registry для каждой комбинации пайплайн+линк.

Правила материализации

В данном случае мы указываем materialized=’view’.

5. Хэширование (Hash)

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

Зачем нужен:

• для удаления дубликатов;
• для джойна со справочниками;
• для графовых таблиц по расчету уникальных id пользователей.

Процесс формирования бизнес-ключа и затем хэша включает несколько

этапов:

1. Определяем, из каких полей состоит бизнес-ключ. В dataСraft Core используется специальный файл — metadata.sql. В нем описываются все сущности в данных, и какие поля уникально их идентифицируют, а также, какие сущности к каким линкам относятся. 
2. Готовим поля, из которых формируется бизнес-ключ: применяем все технические преобразования, приводим строки к верхнему регистру, чтобы из-за разницы в регистре не менялся хэш, объединяем все компоненты бизнес-ключа через разделитель.
3. На основе подготовленного бизнес-ключа получается хэш с помощью специальной хэш-функции. Мы используем MD5.

Схематично получение хэша можно представить так:

Каждый прямоугольник на схеме обозначает отдельную функцию.

Специальный макрос с помощью metadata.sql и колонки __link отбирает поля, необходимые для формирования бизнес-ключа, производит все необходимые преобразования и формирует поля с хэшами. В методологии мы выделяем две группы хэшей:

1. Для линков. Эти хэши рассчитываются только на основе main_entities и являются основными. По ним проводится дедупликация данных на следующем слое. Для формирования этой группы хэшей в основном макросе используется дополнительный — link_hash(). Колонка, содержащая эти хэши, называется __id.
2. По сущностям. Их можно разделить на 2 подгруппы:
   1. По которым можно идентифицировать пользователя (например, номер телефона). В метадате такие сущности помечены меткой glue: yes. Эти хэши необходимы для графовой склейки.
   2. По которым можно сделать JOIN между разными пайплайнами. Например, соединить данные по событиям (пайплайн Events) с каким-то справочником (пайплайн Registry). В этом случае хэш будет формироваться на основе пересекающихся сущностей, то есть если в одном линке сущность из main_entities или other_entities входит main_entities другого линка, относящегося к пайплану Registry, то создается хэш для данной сущности в табличке hash_ первого линка.

Вторая группа — дополнительные хэши, которые не всегда необходимы. Они  используются на этапах, которые идут после слоя Link. Для формирования хэшей по сущностям внутри основного макроса используется макрос entity_hash().

Сейчас в dataСraft Core для этапа хэширования готова alpha-версия автоматизации.  

Макрос: {{ datacraft.hash() }}

Зависимости

Зависит от таблиц, полученных на этапе Combine.

Файл модели

Правило наименования

Название файлов модели на этом этапе:

• hash_{название_пайплайна} — для всех пайплайнов, кроме Registry;
• hash_{название_пайплайна}_{линк} — для Registry.

Правило создания

Для всех пайплайнов, кроме Registry, создается один файл соответственно каждому файлу слоя Combine. Так как на слое Combine происходит объединение всех данных в рамках одного пайплайна, получается одна модель Hash на каждый пайплайн.

Для пайплайна Registry создаются модели для всех комбинаций пайплайн+линк.

Правила материализации

Зависит от типа пайплайна:

• для Events и DateStat — инкрементальный тип. Инкрементальность идет по тем же полям, что и на слое incremental — по дате;
• для PeriodStat, Registry инкрементальности нет, в этом случае данные материализуются как `table’.

6. Дедупликация (Link)

Если не требуются дополнительные преобразования, то это последний слой, на котором происходит дедупликация данных. В link-таблицы попадают только уникальные строки.

Дедупликация проводится с помощью хэшей, которые мы рассчитали на прошлом слое. Оставляем уникальные строки:

1. По комбинации хэша и __date для пайплайнов с инкрементальным типом материализации (Events и DateStat).
2. По хэшу там, где инкрементальности нет (пайплайны Registry и PeriodStat). Данные группируются по значениям хэшей, выбирается максимальное значение для нечисловых полей и сумма (или другая агрегация) для числовых полей.

Зависимости

Зависит от таблиц, полученных на слое Hash.

Файл модели

Правило наименования

Название моделей на этом слое формируется из двух частей: link_{название

пайплайна}.

Правило создания

Создается по одной модели на каждую модель слоя Hash.

Правила материализации

Правило материализации аналогично предыдущему слою:

• для наборов данных, относящихся к пайплайнам Events и DateStat, используется инкрементализация: materialized=’incremental’;
• для всех остальных — материализация без инкрементализации с полной перезаписью: materialized=’table’.

7. Визуализация

На этом этапе необходимо выбрать инструмент для создания отчетных витрин, которые смогут полноценно закрыть потребности бизнес-процессов компании. Ключевой фактор — наличие мощной BI-системы, достаточно гибкой для построения отчетов и анализа данных. В рамках нашего подхода предлагается два варианта: dataCraft и Apache Superset.

Сравнение dataCraft и Superset

dataCraft — аналитическая система для работы с данными из множества источников, включая рекламные площадки, веб-трекеры, статистические сервисы и таблицы произвольной структуры. Система оптимизирована для работы с инструментами рынка РФ, имеет готовые коннекторы и шаблоны для работы с множеством источников данных.
Superset — open-source BI-платформа, предоставляющая базовые функции построения отчетов и визуализаций, доступных для самостоятельного использования и настройки.

Преимущества dataCraft

1. Единое управление данными: предоставляет возможность через интерфейс подключать, настраивать и удалять источники данных для актуализации аналитики при изменениях.
2. Единое управление данными: можно подключать, настраивать и удалять источники данных напрямую через интерфейс.
3. Добавление собственных KPI: позволяет легко создавать и добавлять в отчеты свои KPI с помощью знакомых формул.
4. Сравнение периодов: возможность отслеживать динамику показателей за разные периоды (день, месяц, год).
5. Локализация и удобный интерфейс: полностью русскоязычный интерфейс, адаптированный для удобной работы.
6. Поддержка от специалистов: эксперты dataCraft помогают с настройкой дашбордов и разработкой новых коннекторов.

Более подробное сравнение dataCraft и Superset.

Успехов в построении аналитической системы инхаус!

Если у вас возникнут вопросы или хотите, чтобы наши специалисты быстро и качественно построили для вас аналитическую систему — оставьте заявку, и мы с вами свяжемся.

Запись Чек-лист для построения системы сквозной аналитики inhouse впервые появилась БЛОГ ADVENTUM.