Всем привет!

На одном из проектов я настраивал дашборд в 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-инструмент обращается к уже подготовленным данным;

• нагрузка на базу данных при построении отчётов существенно снижается;

• при использовании индексов повышается скорость выполнения запросов и загрузки отчётов.

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

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

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

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

• количество заказов;

• сумма продаж;

• средний чек;

• CR в оплату и продажу.

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

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

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

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

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

• сложные операции с данными (такие как JOIN) лучше выносить на этап предварительной обработки, а не выполнять на стороне BI;

• валидация данных до загрузки в БД позволяет заранее выявить проблемы и лучше спланировать дальнейшую обработку;

• наличие слоя сырых данных (raw_data) очень важно — это позволяет контролировать корректность преобразований и находить ошибки на этапе обработки.

Объём данных

• объём таблицы published_metrika — ~5 ГБ;

• объём мат. представления — ~7.8 ГБ;

• количество строк мат. представления — ~16.7 млн.

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

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

• фильтры по ключевым срезам (5 параметров) и выбор периода;

• график динамики заказов и оплат;

• таблица с показателями и продажами в разрезе источников трафика;

• таблица с показателями и продажами в разрезе городов;

• таблица с показателями продаж в разрезе рекламных кампаний.

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

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

• 365 дней — до 10 секунд;

• 90 дней — до 5 секунд;

• 30 дней — до 3 секунд.

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