Всем привет!

На одном из проектов я настраивал дашборд в Yandex DataLens, который объединяет данные о трафике из Яндекс Метрики с данными о заказах из CRM и позволяет анализировать доход в разрезе источников трафика.

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

Предыстория

Задача проекта заключалась в построении отчёта по доходу в разрезе источников трафика на сайте.

Исходные условия

На момент постановки задачи на проекте уже была интегрирована CRM с данными о товарах, заказах и продажах а так же Яндекс Метрика с настроенными целями.

Предложенное решение

После анализа текущей ситуации и обсуждения с командой было предложено следующее решение:

в качестве источника данных о заказах и продажах использовать CRM;
в качестве источника данных о трафике — Яндекс Метрику;
дополнительно подключить электронную торговлю Яндекс Метрики (так как проект был в сфере e-commerce);
для связи данных CRM и Метрики использовать Дату, ClientID, а также Order_ID из электронной торговли;
для загрузки и обработки данных использовать Python;
в качестве хранилища данных — PostgreSQL;
в качестве BI-инструмента — Yandex DataLens (облачная версия).

Ограничения и архитектура

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

Эта проблема решалась выбранным стеком: объединение и обработка данных были вынесены на сторону Python.

Такой подход имеет ряд преимуществ.
Оптимизация: Снижение нагрузки на BI-инструмент, DataLens получает уже готовые «витрины» данных.

Упрощение работы: В DataLens отпадает необходимость писать сложные запросы к БД.
При создании датасета необходимо только задать типы данных и создать вычисляемые показатели.

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

Реализация

1. Настройка электронной торговли Яндекс Метрики

Так как идентификатор заказа из ЕТ Метрики планировалось использовать в качестве ключа для маппинга данных, на первом этапе было необходимо внедрить электронную торговлю на сайт.

По этому я подготовил ТЗ для разработчиков, в котором описал:

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

После реализации ТЗ в Яндекс Метрику начали передаваться данные о заказах: состав товаров и суммы.

2. Передача идентификаторов в CRM

На втором этапе я подготовил ещё одно ТЗ, в рамках которого требовалось передавать в CRM дополнительные идентификаторы.

client_id — идентификатор браузера пользователя из Яндекс Метрики;
order_id — идентификатор заказа из электронной торговли Яндекс Метрики.

В результате в CRM стали сохраняться оба идентификатора, что позволило в дальнейшем связать данные о заказах с данными о пользовательских сессиях и источниках трафика.

3. Анализ и подготовка данных

На этом этапе от заказчика были получены данные в формате JSON.

Перед тем как ставить задачу разработчику на загрузку данных в БД, я изучил полученные данные, чтобы:

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

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

преобразовал данные в таблицу;
создал дополнительные столбцы, рассчитал необходимых показатели;
привел названия городов к единому виду;
исправил опечатки в названиях;
добавил бинарные флаги для подсчета показателей;
извлёк суммы товаров из JSON-массива, агрегировал их и записал в отдельный столбец «Сумма продаж», так как в исходных данных итоговая сумма заказа отсутствовала и один заказ мог содержать несколько товаров;
проверил корректности данных, агрегировал количество заказов, суммы продаж;
сравнил полученные результаты с первоисточником что бы избежать ошибок.

И тоге получилась вот такая таблица из которой можно делать нужные сводные для анализа и сравнения результатов с JSON.

4. Загрузка заказов из JSON в БД

На этом этапе я подготовил техническое задание для разработчика в котором описал:

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

После загрузки данных в БД была создана первая таблица — raw_data, содержащая сырые данные из JSON.

На её основе с помощью SQL я сформировал вторую таблицу — published_data, в которой выполнил обработку данных в соответствии с логикой, ранее реализованной в Power Query:

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

После этого я провёл валидацию данных: сравнил результаты в published_data с исходными JSON-данными — проверил количество заказов, товаров и суммы продаж в разрезе месяцев. Убедившись в корректности данных, я поставил задачу разработчикам на перенос логики формирования таблиц на сервер с использованием Python.

В результате мы с командой реализовали процесс автоматической выгрузки и загрузки данных в БД с их регулярным обновлением и автоматическим формированием таблиц.

В базе данных были созданы две таблицы:

raw_data — сырые данные из JSON;
published_data — обработанные и подготовленные данные.

5. Загрузка данных из Яндекс Метрики в БД

Следующим этапом была загрузка данных из API Яндекс Метрики для последующего объединения с данными CRM.

На этом этапе я подготовил техническое задание, в котором описал:

какие данные необходимо выгружать из API Яндекс Метрики;
структуру итоговой таблицы в БД;
параметры запросов (метрики, группировки, модель атрибуции).

Параметры выгрузки из Logs API Метрики:

ym:s:date — дата;
ym:s:regionCity — город;
ym:s:lastsignTrafficSource — источник трафика (последний значимый);
ym:s:lastsignUTMSource — UTM-источник;
ym:s:lastsignUTMMedium — UTM-канал;
ym:s:lastsignUTMCampaign — UTM-кампания;
ym:s:lastsignUTMContent — UTM-контент;
ym:s:lastsignUTMTerm — UTM-ключевое слово;
ym:s:clientID — идентификатор посетителя;
ym:s:purchaseID — идентификатор заказа (из электронной торговли);
ym:s:pageviews — количество просмотров страниц за визит.

В результате в базе данных была создана таблица published_metrika, содержащая данные о визитах пользователей, где каждая строка соответствует одному визиту.

Эти данные можно использоваться для маппинга с заказами из CRM по ключам: date, clientID, purchaseID.

6. Маппинг данных

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

Для этого в базе данных было создано материализованное представление data_mat_view, в котором происходило объединение таблиц published_data и published_metrika.

Использование мат. представления обусловлено тем, что при работе с обычным VIEW объединение данных (JOIN) выполнялось бы при каждом запросе со стороны BI.

Поскольку объединение данных из CRM и Яндекс Метрики является ресурсоёмкой операцией, было принято решение вынести его в отдельный слой предобработки.

Материализованное представление обновлялось на стороне сервера по расписанию ночью.

Таким образом:

все тяжёлые вычисления (JOIN, агрегации) выполняются заранее;
BI-инструмент обращается к уже подготовленным данным;
нагрузка на базу данных при построении отчётов существенно снижается;
при использовании индексов повышается скорость выполнения запросов и загрузки отчётов.

Пример data_mat_view — Пример **data_mat_view**

7. Создание датасета и дашборда в DataLens

На финальном этапе было выполнено подключение базы данных к DataLens и настройка датасета.

В качестве источника данных использовалось мат. представление, содержащее объединённые данные из CRM и Яндекс Метрики.

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

количество заказов;
сумма продаж;
средний чек;
CR в оплату и продажу.

Это позволило переиспользовать метрики в различных чартах без дублирования расчётов.

На основе датасета был построен дашборд, позволяющий анализировать ключевые бизнес-показатели в разрезе источников трафика.

В силу ограничений NDA детали реализации дашборда и точная логика расчёта показателей не раскрываются.

Выводы и результаты

В ходе реализации проекта можно выделить несколько важных моментов:

сложные операции с данными (такие как JOIN) лучше выносить на этап предварительной обработки, а не выполнять на стороне BI;
валидация данных до загрузки в БД позволяет заранее выявить проблемы и лучше спланировать дальнейшую обработку;
наличие слоя сырых данных (raw_data) очень важно — это позволяет контролировать корректность преобразований и находить ошибки на этапе обработки.

Объём данных

объём таблицы published_metrika — ~5 ГБ;
объём мат. представления — ~7.8 ГБ;
количество строк мат. представления — ~16.7 млн.

Структура дашборда

На основном дашборде реализованы:

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

Производительность

Скорость загрузки дашборда зависит от выбранного периода и фильтров:

365 дней — до 10 секунд;
90 дней — до 5 секунд;
30 дней — до 3 секунд.

В статье используются обезличенные данные и обобщённая структура проекта без раскрытия информации о заказчике.

Как объединить данные Яндекс Метрики и CRM: от загрузки данных до дашборда в DataLens