Как создать из хаоса DWH для сквозной аналитики 6 проектов и 40 филиалов

У не‑IT бизнеса очень абстрактные представления об аналитике и аналитиках. Первым моим опытом работы аналитиком был крупный оптовый селлер. Там аналитика была связана с выгруженными Excel файлами из 1С. Суть проста — проанализировать продажи и товарные остатки и одобрить/изменить количество товаров, необходимые для закупки. Просто, но очень муторно, потому что товаров и номенклатурных групп великое множество. 

Вторым моим работодателем была компания, которая предоставляла услуги сопровождения строительных проектов. Первый заказчик — очень крупная компания добывающей промышленности. Информация по стадиям строительства и календарно‑сетевому планированию велась в ПО Oracle Primavera P6. К данному ПО предустановлена СУБД MS SQL Server — так я познакомился первый раз с реальной БД, начал работать непосредственно с сырыми данными и знакомиться с первыми понятиями о DWH. Но пока напрямую подключался через Power Pivot. В Power Pivot создавался OLAP‑куб и прописывал взаимосвязи таблиц. Далее использовал язык формул DAX для того, чтобы создавать меры для будущих дашбордов. 

Первый опыт создания DWH

Изучив достаточно материала, я приступил к созданию DWH. Основная цель — перенести часть расчётов на СУБД, тем самым уменьшить время обновления дашбордов. 

Структура DWH состояла из следующих элементов:

1. Синонимы. Синонимы — это способ взаимодействия СУБД MSSQL Server с другими базами данных. Я создавал синонимы к таблицам БД Oracle Primavera P6 для переноса данных на нашу БД.

2. Процедуры. Важнейшая часть для автоматизации процессов. В процедурах я сразу проводил все необходимые расчёты, добавлял данные, прописывал условия для создания колонок. Каждая процедура под каждую таблицу. Также, на процедуры можно устанавливать триггеры или крон. Наша DWH запускала процедуры по триггеру обновления оригинальных таблиц программы Oracle Primavera P6. Также, можно проводить ручной запуск процедур. Обязательно надо иметь мастер‑процедуры для обновления всех таблиц, удаления и создания. 

3. Физические таблицы. Физические таблицы хранят в себе уже обработанные данные с необходимыми расчётами. Именно эти таблицы далее подключались к Power Pivot для создания OLAP‑куба и построения дашбордов отчётности о стадиях строительства объектов заказчиков.

Также, стоит отдельно поговорить непосредственно о схеме передачи данных.

У ПО Oracle Primavera P6 есть функция расчёта расписания. Её запускают проектные менеджеры, когда обновят информацию об актуальных стадиях строительных объектов. После завершения расчёта расписания обновлялись таблицы в БД. Сущность TRIGGER проверяла изменения в таблицах, если таблицы изменились, то она запускала процедуры. Сущность SYNONYM позволяла обращаться к оригинальным таблицам напрямую. Сущности PROCEDURE’s выполняли обновление таблиц.

Проектов было 5, поэтому мы с наставником старались всеми силами унифицировать DWH для того, чтобы можно было брать всё больше и больше новых клиентов. Но, к сожалению, руководство компании не было заинтересовано в поиске новых клиентов. Поэтому последний год моей работы это настоящая аналитическая база всех крупных не‑IT компаний — всю отчётность необходимо было делать только в эксельках из других экселек. 

Несмотря на то, что последний год я вообще не работал с DWH, этот опыт и знания пригодились мне уже на новом месте работы.

Создание единой DWH для сквозной аналитики 6 проектов и 40 филиалов

Когда я пришёл на новое место, там уже была аналитика на 3-х проектах. Существовали парсеры/коннекторы (тут кому как удобнее называть получение данных по API) для передачи данных из нескольких источников, а именно: Битрикс 24, UIS, Яндекс Директ. Но схема выглядела следующим образом:

У данной схемы есть самый главный существенный недостаток — изменение. Так как все проекты обслуживала наша организация, доработки и изменения должны были касаться всех проектов. Следовательно, менять каждый парсер, менять каждый запрос в БД, каждую меру и BI‑отчёт каждого проекта. Так как планировалось подключение новых источников и новых проектов, я начал продумывать модель DWH, чтобы общая логическая схема выглядела следующим образом:

Описание и предназначение источников данных

Битрикс 24. Это CRM система, где содержится вся информация о лидах, сделках, клиентах, сотрудниках. Именно этот источник данных является основным и все остальные источники для насыщения данных, сопоставляются по ключам таблиц Битрикс 24.

Мини‑гайд по изучению структуры хранения данных Битрикс 24:

1. Начать можно не с документации Б24, а лучше всего с данных BI‑конструктора. Там можно увидеть, как хранятся данные, в каких форматах. Тут можно сразу продумывать взаимодействие с другими таблицами и, в целом, формируется понимание, какие поля необходимы для вашей DWH.

2. Продолжение изучения API Битрикс 24 уже непосредственно в документации. Тут есть недостатки, с которыми мы столкнулись уже после написания парсеров: Б24 не отдаёт значения некоторых полей. В нашем случае это поле source_name таблицы crm_lead и поле stage_name таблицы crm_deal. 

Сразу хочется описать, как решили проблему с полями, и для чего они нужны. Поле source_name таблицы crm_lead предоставляет данные источника лида. По источнику определяются необходимая для аналитики информация, а именно: проект, с которого пришёл лид; филиал, который обработал лид с проекта; тип обращения (почта, звонок, заявка с сайта, онлайн‑чат). Первые два необходимы для понимания кому присваивать лид, последнее для отслеживания конверсии по типам обращений и, соответственно, эффективности отдела или менеджера. Поле stage_name таблицы crm_deal — для определения статуса сделки. У сделок существует множество стадий, для аналитики необходимы только понятия выиграна, проиграна, сделка в процессе. Но для правильной работы отдела продаж, бизнес‑аналитки создали систему из 10 стадий, из которых 4 считаются успехом для аналитики, 1 проигрышем, остальные в процессе.

Данную проблему решили следующим образом — созданием пользовательских ручных таблиц. По сути это таблицы‑справочники получаемые ручным обновлением BI‑конструктора Б24 в случае изменения информации. Преимущества решения:

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

2. Благодаря справочным таблицам (u_table обозначение в нашей DWH), снимается нагрузка на запросы типа CASE WHEN. На 6 проектов и 40 филиалов не напасёшься CASE WHEN, к тому же, изменять такие запросы придётся очень долго.

Изучив всю информацию об API Битрикса 24, я написал техническое задание отделу разработки для создания парсера Б24. Обновление происходит каждый день в ночное время. Основная информация (таблицы), необходимая аналитике — это лиды, сделки, сотрудники, департаменты, клиенты. Теперь, при добавлении нового портала Б24 необходимо только лишь в файл config вставить название проекта и webhook самого портала, с которого получаем данные.

UIS

Компания занимается предоставлением услуг Email‑tracking и IP‑телефонии. Для аналитики это подключение важно, так как UIS передаёт достаточно данных для разметки лиды на рекламные/нерекламные. Так как надо оценивать эффективность маркетинга, нам необходима эта разметка. Лиды в Б24 это всего лишь сущность, лид можно создать вручную или обычный нерекламный звонок может быть лидом, письмо на почту от уже постоянного клиента. Наиболее важная информация хранится в полях с приставкой UTM. Например, поле UTM_CONTENT хранит в себе информацию из Я.Директа, такую как ID группы объявлений, регион и тип устройства, с которого пришло рекламное обращение. С парсингом UIS особо не было проблем, за исключением одной: в документации указаны не все поля, которые можно забрать. Мы опытным путём вытащили поле, которого в документации не было. Также, если вы вдруг будете пользоваться API, то необходимо писать в техническую поддержку, чтобы ваш IP добавили в белый список, иначе ваш IP заблокируют, и вы будете ловить 403 ответ.

Яндекс.Директ

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

Яндекс.Директ необходим для аналитики из‑за возможности оценивать эффективность рекламных кампаний. В качестве эффективности подразумевается информация о самих лидах, которые привела та или иная кампания. Директологи всегда опираются исключительно на достижения целей Яндекс.Метрики, но цели могут дублироваться. Пример: пользователь два раза оставил заявку с сайта — получается, что он выполнил 2 цели; или пользователь оставил заявку с сайта, позвонил и написал на почту — это уже 3 цели, но во всех ситуациях ЛИД один (отдел Б24 совместно с разработкой создали систему контроля дублей, чтобы лиды не дублировались в подобных ситуациях). 

Сопоставление данных Яндекс.Директа с лидами Б24 происходит следующим образом: поля Яндекс.Директа имеют основную информацию для сопоставления: дата показа, id рекламной кампании, id группы объявлений, тип критерия (автотаргетинг или ключевое слово), тип устройства (мобильное устройство, десктопное). Если пользователь перешёл на сайт по рекламе и создал лид любым способом, то информация идёт через UIS или сразу Битрикс 24, там вся информация хранится в полях с UTM‑метками, мы эту информацию сопоставляем с информацией Яндекс.Директ и понимаем какая кампания в эту дату принесла какой лид. 

Таким образом мы смогли сопоставить реальную информацию о лидах контекстной рекламы и оценить эффективность: посчитать ROMI, сделать список Лучшие кампании / худшие кампании (для доработок и удаления), а также оффлайн‑конверсии, которые помогают рекламным кампаниям дообучаться (коллега напишет об этом статью, я её обязательно приложу).

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

Яндекс.Метрика

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

Подключение Яндекс.Метрики к лидам осуществляется по полю YM_CLIENT_ID. 

Топвизор/keys.so

Эти два подключения необходимы для отдела органического трафика. Первый показывает позицию сайта по определённому запросу, второй необходим для мониторинга и определения сайтов‑конкурентов по ключевым запросам.

Устройство DWH

Изначально в базе было около 20 таблиц с сырыми данными (сейчас 40). Все расчёты и махинации были на стороне BI‑системы. Какая именно это BI система, не скажу, потому что очень много негатива она вызвала, единственное, что могу сказать — это импортозамещённый продукт. Я работал в 2-х импортозамещённых BI‑системах, и, ощущение, что обе сделаны на коленках. Так как я пришёл на частично готовую инфраструктуру, я изучил взаимодействие, которое было, и делал также. Потом, после переписывания всех парсеров и созданной DWH, я пока делал также. Но, после того, как мне пришлось целых 3 раза пересобирать абсолютно всю отчётность (из‑за того, что одни колонки добавлялись, другие переименовывались, третьи удалялись), я начал думать в другом направлении и вспомнил свой первый опыт создания DWH. 

Что ситуация с OLAP‑кубами, что возможность строить взаимосвязи внутри BI‑системы вели к одной проблеме — взаимодействие между таблицами. Получается, что данные, необходимые для аналитики не были независимыми, и я решил это исправить.

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

1. Для аналитики продаж необходима таблица сделок (deals). Эта основная таблица, следовательно, информация из других таблиц должна присоединяться без дублирования строк. К сделкам добавляется информация о лидах (leads), карточках клиентов, стадий сделок (u_table) и прочее.

2. Для аналитики маркетинга, то есть лидов (leads), нужна информация о лидах, поэтому уже в лидах не должно быть дублей

3. Для аналитики контекстной рекламы должна быть информация таблицы Direct, то есть самих рекламных показов и уже к ней добавляется информация о лидах и сумме сделок, для расчёта ROMI.

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

Представления (View)

Я подумал, что создание представлений (View) в SQL запросах — это лучшее решение. Какие преимущества у этого подхода я видел:

1. Хранение — код запроса сохраняется внутри объекта представления, также, представление является всего лишь кодом запроса, то есть физически не хранит никаких данных.

2. Обновление — так как это представление, можно сказать, что при выполнении кода запроса, полученные данные всегда будут отражать текущую ситуацию.

Мне понравилось как это выглядит и перенёс все BI‑отчёты под новую систему — получилось сделать всю отчётность в течение 2-х рабочих дней, в то время как первоначальная схема требовала целой рабочей недели. Также, моя новая схема прошла боевое крещение. Когда я из BI‑системы удалял старые графики, я решил их удалить целиком, мне всплыло предупреждение: «вы хотите удалить все графики и объекты, связанные с ними?». Я подумал, что не может BI‑система удалить дашборды, ведь на одном листе отчёта старые графики, а на другом новые. Оказывается, может. Это можно сравнить с тем, что вы удаляете вообще весь эксель документ, а не только лист. Ну и опять же, благодаря новой схеме, мне в течение 4-х часов удалось восстановить всю утерянную отчётность.

В целом я был доволен. Но произошло несколько событий, из‑за которых я поменял своё мнение о новой схеме:

1. Приходилось очень часто менять запросы. Это связано было с тем, что пока всё равно шла и откладка нашей системы и налаживание правильных взаимоотношений между проектами: проектные менеджеры и другие отделы старались унифицировать работу филиалов в Б24, начиналось унификация источников, подключений и прочего. А так как некоторые части запросов повторялись, например разложение департаментов (как принято в иерархических таблицах есть id родителя и дочернее id, и чтобы найти департамент, к которому принадлежит сотрудник эту таблицу надо было разложить). Разложение департаментов надо было и в таблице лидов и в таблице сделок. То есть одно изменение требовало внесения правок уже в 2 представления. Аналогичная ситуация с таблицей Direct, ведь сначала в таблице leads, мы определяем является ли лид рекламным и в случае рекламы, этот лид соединяем с direct. То есть опять, 1 изменение равно правкам в 2 представления. Это было крайне неудобно

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

3. Время выполнения запросов. Таблицы сами по себе большие, а для обработки информации было необходимо создавать CTE, например в таблице deals, я сразу выводил данные по АВС анализу и RFM анализу. Множество CTE и дополнительно оконных функций сильно тормозили выполнение запросов. Одна только таблица deals грузилась почти 2 минуты. Запросы выполняли часто, так как перепроверяли данные, для откладки запросов. 

Тогда я принял решение, что всё‑таки обработанные данные надо где‑то хранить.

Материализованные представления (Materialized View)

Материализованные представления по сути являются физическими таблицами, созданными по запросу SQL. Их отличие от обычных таблиц в том, что они хранят в себе запрос. Какие проблемы решает использование материализованных

1. Сокращение масштаба запроса. Как уже говорилось ранее: несколько одинаковых запросов было в разных представлениях, теперь эти основные запросы вынесены в отдельные материализованные представления. Например: разложение иерархии департаментов на сотрудников, определение лида как рекламного через таблицу UIS.

2. Сокращение времени запроса. Чтобы проверять валидность и корректность данных, надо постоянно обращаться к таблицам, так как таблицы стали физическими, стало гораздо быстрее проводить запрос. Та самая таблица deals, чьё представление по любому запросу грузилось около 2-х минут, теперь отдаёт все строки менее, чем за 5 секунд.

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

Процедуры и мастер‑процедуры

Для каждого материализованного представления я сделал процедуру, вызов которой создаёт это представление. Когда в одних запросах использовать уже готовые материализованные представления, то автоматически появляются зависимости. Поэтому нельзя просто взять и удалить/изменить представление, у которого есть взаимосвязь. Здесь на помощь приходят мастер‑процедуры. Там записываешь последовательность создания/удаления/обновления представлений. Таким образом появляется автоматизация.

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

Также прилагаю пример запроса мастер‑процедуры на удаление

create procedure master_drop_all_mv()
    language plpgsql
as
$$
BEGIN

	RAISE NOTICE 'Удаление mv_keysso';
    DROP MATERIALIZED VIEW IF EXISTS mv_keysso;
	COMMIT;

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

Если необходимо изменить запрос. Сначала делаем сам запрос, проверяем его корректность, если всё устраивает, меняем процедуру создания материализованного представления, вызываем сначала мастер‑процедуру на удаление всех представлений, потом на создание. В общей сложности создание всех таблиц составляет около 5-ти минут. 

Мастер‑процедура на обновление всех материализованных представлений master_refresh() используется по крону. То есть после обработки всех парсеров, скрипт на Python вызывает процедуру. Таким образом, приходя на работу в 8–30, все сотрудники видят полностью обновлённые отчёты.

Итог работы с материализованными представлениями и процедурами

На данный момент, 15 материализованных представлений, хранят в себе обработанную информацию 40 таблиц. Управляют представлениями 22 процедуры, из которых 3 это мастер‑процедуры.

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

Общий итог создания DWH

Так как после создания DWH правки и ошибки занимают минимум работы, можно было заняться непосредственно аналитикой. Работа аналитиков обычно делится на прямое влияния и косвенное.

Прямым влиянием является то, что удалось, благодаря полной информации оценить эффективность маркетинга, а именно контекстной рекламы. После составления метрик и проведённой работой коллегами директологами, удалось увеличить количество рекламных обращений на 20% при неизменном бюджете.

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

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