Архитектура сквозной аналитики на PostgreSQL ODS

Начнём с архитектуры хранения данных. В центре нашей системы – PostgreSQL 17, развёрнутый как ODS-хранилище (Operational Data Store). Все сырые данные из рекламных систем и CRM стекаются в ODS, а уж затем моделируются для BI. Почему выбран PostgreSQL, а не MySQL или ClickHouse? Во-первых, Postgres – универсально признанная СУБД с богатыми возможностями аналитического SQL (CTE, оконные функции, JSON и т.д.) и новыми фичами вроде MERGE (начиная с версии 15). Он отлично справляется с задачами интеграции данных: например, JSON-функции позволяют сразу парсить ответы API тех же Яндекс.Директа или Метрики в таблицы. Во-вторых, PostgreSQL – open-source и переносимая платформа: практически любой open-source BI инструмент (Apache Superset, Metabase и пр.) или коммерческий (Power BI, Tableau) умеет подключаться к Postgres. Это реализует требование собирать данные так, чтобы визуализировать их можно было в любом инструменте – никаких проприетарных форматов.

Почему не MySQL? Хотя современные версии MySQL подтянули функциональность, в аналитических сценариях Postgres по-прежнему впереди. Построение сквозной аналитики на PostgreSQL даёт более высокую гибкость в обработке данных (те же UPSERT, широкие JSON-возможности) и надёжность транзакций. А вот ClickHouse – отличный колонночный движок для агрегаций, но для ODS он избыточен. Нам важно не только быстро читать, но и регулярно мерджить (сливать) свежие порции данных, обновлять справочники, проводить нормализацию – с этим реляционная СУБД справляется лучше. К тому же порог входа у Postgres ниже: вам не нужно тонко настраивать партиции и движки под каждую задачу, как в ClickHouse. Мы закладываем простое, но масштабируемое решение, где PostgreSQL служит оперативным хранилищем всех данных, а уже поверх него строится витрина для анализа. Практика показывает, что такого подхода хватает даже среднему e-commerce: например, в одном из кейсов аналитики рекламные и CRM-данные собраны в PostgreSQL и подключены напрямую в Yandex DataLens для дашбордов. End-to-end attribution реализована без лишних прослоек: данные разных систем хранятся централизованно, а BI-отчеты обращаются к единому источнику. В итоге уходим от разрозненности данных и ручного копирования между файлами.

Хранение и резервное копирование. PostgreSQL 17 легко поднять в Docker-контейнере. Рекомендуется организовать периодические бэкапы – например, связкой pgBackRest. Ниже приведён упрощённый пример docker-compose.yml, где поднимается контейнер Postgres и сервис резервного копирования:

services:
  db:
    image: postgres:17
    environment:
      - POSTGRES_PASSWORD=mysecretpassword
    volumes:
      - ./data/db:/var/lib/postgresql/data
  backup:
    image: pgbackrest/backrest:latest
    volumes:
      - ./data/db:/pg_data:ro
      - ./backups:/backrest_backup
    entrypoint: ["pgbackrest", "--stanza=main", "backup"]

Развернув такую инфраструктуру, мы получаем надежный ODS-слой. Данные складываются в схему staging (stg), откуда после очистки и нормализации перетекают в витринные таблицы. Теперь перейдем к самому важному – как связать разрозненные данные кликов, веб-аналитики и CRM.

Проброс идентификаторов: связываем клик → сессию → сделку

Чтобы сцепить данные Яндекс.Директа, Яндекс Метрики и Bitrix24 CRM в единый сквозной путь, нужно прокидывать идентификаторы между всеми звеньями цепочки. Основные идентификаторы, с которыми предстоит работать:

• yclid – Yandex Click ID, метка клика из Я.Директа.
• ym_client_id (client-id Метрики) – уникальный идентификатор посетителя в Я.Метрике.
• user-id (в Bitrix24) – идентификатор пользователя/контакта в CRM.

Схема передачи ID: Когда пользователь кликает по объявлению Яндекс.Директа, в URL целевой страницы автоматически добавляется параметр yclid – уникальный номер клика. Отключить эту автопометку нельзя – Директ всегда подставляет yclid при включенной метке для Метрики. Яндекс.Метрика “считывает” yclid из URL и тем самым привязывает визит к конкретному клику Я.Директа. В самой Метрике каждый визит получает ещё и свой client-id – анонимный идентификатор устройства посетителя (хранится в cookie _ym_uid). Наша задача – пронести этот client-id дальше, до создания сделки в CRM. Самый распространенный подход: при отправке лид-формы на сайте записывать текущий client-id в скрытое поле и отправлять в CRM вместе с данными лида. Пример (на чистом JS без тег-менеджеров):

<!-- Скрытые поля в форме заявки -->
<input type="hidden" name="ycid" id="ycid"><!-- Yandex Metrica clientID -->
<input type="hidden" name="gcid" id="gcid"><!-- Google Analytics clientID, за компанию :) -->
<script>
  // Скрипт подставляет client-id в скрытые поля при загрузке страницы
  document.addEventListener('DOMContentLoaded', function(){
      // Я.Метрика: достаем clientID из счетчика
      try {
          var yaId = window['yaCounter<Номер счетчика>'].getClientID();
          document.getElementById('ycid').value = yaId;
      } catch(e){}
      // GA: из cookie _ga (для общего развития)
      var gaCookie = document.cookie.match('(?:^|;)\\s*_ga=([^;]*)');
      if (gaCookie) {
          var gaId = gaCookie[1].match(/(\d+\.\d+)$/);
          document.getElementById('gcid').value = gaId ? gaId[1] : '';
      }
  });
</script>

Таким образом, в момент любой конверсии (заявки) мы фиксируем, какой client-id Яндекс Метрики ей соответствует. В Bitrix24 создаём дополнительные поля для лидов или контактов, например “Yandex ClientID” и “Google ClientID”, куда эти значения будут писаться. При звонке или офлайн-продаже связать клиента сложнее, но и это решаемо: коллтрекинги обычно тоже умеют передавать client-id в CRM, а для оффлайн каналов можно применять генерацию QR-кодов с UID или просто вручную сопоставлять при оформлении продажи. Главное – не потерять связку онлайн User → офлайн продажа (той самой off-line продажи, которые часто выпадают).

Что делать с _ym_uid, ya_client_id, ya_uuid? Эти термины могут сбить с толку. Проясним:

• _ym_uid – та самая cookie Метрики, где хранится client-id (она состоит из случайного числа + метка времени первого визита).
• ya_client_id – по сути то же самое, просто название переменной. В коде выше мы получали его через метод getClientID() счётчика (в примере мы положили его в переменную yaId).
• ya_uuid – устаревшее или специфичное понятие. Иногда так называют идентификатор пользователя Яндекса (autentificated) или UUID в Яндекс ID, но в контексте сквозной аналитики можно игнорировать. К веб-визитам и кликам он не относится напрямую.

А как же User ID? В Метрике есть возможность передавать собственный идентификатор пользователя (аналог User ID в Google Analytics) – например, если у вас на сайте есть личный кабинет, вы можете при авторизации вызвать yaCounterXYZ.userParams({ UserID: '12345' }). Однако в рамках данной задачи можно обойтись без этого усложнения. Мы используем user-id Битрикс24 как идентификатор клиента в CRM (ID контакта или клиента). То есть, связываем client-id Метрики ↔ запись CRM. Для этого и сохраняем client-id в сделке. Затем уже на уровне PostgreSQL “склеим” таблицу веб-сессий с таблицей сделок по этому идентификатору (об этом в разделе про моделирование).

Важно также пробрасывать источник и кампанию: Метрика автоматически разметит визиты UTM-метками или параметром yclid. Но в CRM имеет смысл хранить и канал заявки – хотя бы в виде поля “Источник” (например, ‘Yandex Direct / поисковая реклама’), чтобы отдел продаж тоже видел, откуда пришел лид. Это можно сделать либо через штатные поля Битрикс24 (Источник сделки), либо кастомным полем, которое заполнится на основе UTM при отправке формы. Custom UTM-метки (например, utm_content с ID объявления) можно также сохранять в CRM, если нужна более детальная атрибуция на уровне объявления или креатива.

Разница в определении “сессии”: Метрика vs Директ

Одно из первых препятствий при сведении данных – разное понятие сессии в веб-аналитике и рекламных кликах. Рассмотрим, как считают визиты разные системы:

Яндекс.Метрика: под сессией (визитом) понимается последовательность действий пользователя, разделённых не более чем 30 минутами неактивности. Проще говоря, если пользователь не совершал новых хитов больше 30 минут – предыдущий визит завершается. (Таймаут можно изменить в настройках счётчика, максимум до 360 мин, но по умолчанию 30). Кроме того, Метрика разделяет визиты при смене источника трафика. Например, если пользователь зашел на сайт напрямую, а через 10 минут кликнул рекламное объявление, то начнётся новый визит, несмотря на короткий интервал. В официальной справке указано: первый переход с рекламной системы всегда открывает отдельный визит; повторные клики по тому же объявлению во время текущего визита не создают нового. То есть, если человек дважды кликнет по вашему объявлению с интервалом в 5 минут, Метрика засчитает один визит (но в логе визитов будет зафиксировано два перехода). Это нюанс: Метрика старается не дублировать визиты, пока сессия активна, даже при повторном клике. С другой стороны, если истёк таймаут или поменялись UTM-метки (например, другой источник/канал) – начнется новая сессия. Для сквозной аналитики это означает, что в Метрике не всегда 1 клик = 1 визит. Нужно иметь это в виду при сопоставлении с данными Директа.

Яндекс.Директ: здесь учет основан на кликах. Каждое нажатие на объявление фиксируется со своим Click ID (yclid) и временем. Отчёты Директа оперируют либо агрегированными показателями (например, суммарные клики, расход по кампаниям), либо срезами вплоть до ключевой фразы. Понятия “сессия” в Я.Директе нет, т.к. рекламная система не отслеживает поведение на сайте после клика – этим занимается Метрика. Поэтому сквозная аналитика строится так: мы берём логи Метрики по визитам и сопоставляем их с данными кликов из Директа. При этом может возникнуть ситуация, когда один визит Метрики связан с несколькими кликами (как описано выше). В PostgreSQL можно это учесть, введя, например, таблицу соответствия fact_clicks_to_sessions, где каждому yclid ставится в соответствие session_id визита. В большинстве случаев же 1 клик ⇒ 1 сессия, особенно если речь про первый клик пользователя.

Нормализация времени и единой сессии. Ещё одно отличие – время. Метрика записывает время визита по часовому поясу, заданному в счетчике (например, для России это GMT+3 или другой регион). Директ при выгрузке статистики обычно отдает время в UTC или тоже в Москве – нужно проверить настройки API. Bitrix24 CRM хранит даты создания лидов/сделок, скорее всего, в своем стандартном формате (UTC или локальное время портала). Чтобы правильно объединить данные Яндекс Директ и CRM, нужно привести все временные метки к единому стандарту – обычно к UTC. В ETL-конвейере мы можем сразу конвертировать время: например, указать в запросе к API Директа часовой пояс, а при загрузке CSV из Метрики явно указать TIMEZONE. В PostgreSQL удобно хранить времена в типе TIMESTAMP WITH TIME ZONE, тогда все данные автоматически будут в UTC внутри, а при выборке можно форматировать в нужную TZ.

Когда мы будем строить объединённый fact_sessions (визиты) в БД, имеет смысл добавить поле session_uid – уникальный идентификатор сессии, который связывает между собой таблицу визитов и все события/клики в ней. В Я.Метрике есть встроенный VisitID в Logs API (уникален в пределах одного счетчика), его можно использовать. Тогда клик из Директа с yclid связываем с визитом, присваивая ему VisitID. Все события или хиты внутри также будут иметь этот VisitID. Таким образом, в хранилище можно проставить единую сессию, несмотря на разницу определений.

ETL-конвейер: сбор данных из Метрики, Директа и Битрикс24

Теперь о конвейере данных (ETL) – как мы будем вытаскивать данные из источников и загружать в PostgreSQL. Рассмотрим по блокам:

Выгрузка сырых визитов из Яндекс.Метрики (Logs API)

Яндекс.Метрика предоставляет Logs API v2 – интерфейс для получения сырых несводных данных о визитах и хитах. В отличие от стандартных отчетов, Logs API выгружает покапосетительные данные (каждый визит отдельной строкой, с множеством полей: от источника и региона до списка просмотренных страниц). Это именно то, что нужно для сквозной аналитики. По регламенту Метрики данные текущего дня недоступны, лучше запрашивать вчерашний день и старше. Также за раз можно выгружать не более 1 года и не более 10 ГБ данных, но эти ограничения нам пока не страшны.

Выгрузка по Logs API – процесс асинхронный. Шаги такие:

1. Отправляем запрос на подготовку логов (указываем диапазон дат и набор полей – например, визитID, время начала, источник, campaignID, clientID, yclid, целевые действия и т.д.).
2. API ставит задачу в очередь и возвращает идентификатор.
3. Периодически опрашиваем метод получения статуса – как только статус processed, можно скачивать файл.
4. Скачиваем CSV-файл с данными визитов.

Такие запросы можно автоматизировать на Python с помощью библиотеки requests + встроенные методы авторизации OAuth Яндекса. После получения CSV остается загрузить его в базу. Самый быстрый способ – командой COPY. Например, сохранили файл как metrika_logs_2023-06-01.csv – тогда в psql:

COPY stg_yametrika_visits FROM '/path/to/metrika_logs_2023-06-01.csv' 
    (FORMAT CSV, DELIMITER '\t', HEADER TRUE, ENCODING 'UTF8');

Logs API умеет отдавать TSV с табуляцией, что удобно – не столкнёмся с запятыми внутри значений. Обратите внимание: Метрика может возвращать Unix-время в миллисекундах для некоторых полей (например, время визита в формате timestamp_ms). Их стоит приводить к нормальному timestamp через TO_TIMESTAMP(ms/1000). Кроме того, на этапе выгрузки можно сразу фильтровать боты и внутренний трафик – в Logs API есть параметры фильтра.

Отдельно отметим Streaming API Метрики: это инструмент для потоковой отправки данных о визитах в реальном времени (через очередь сообщений). В рамках нашего гайда можно обойтись без него – дневной задержки от Logs API достаточно для большинства задач. Но если вдруг понадобится почти онлайн-обновление, Streaming API позволяет получать события сразу на ваш консьюмер (например, Python-скрипт) и тут же писать в базу.

Получение кликов и затрат из Яндекс.Директа (Reports API)

Для сбора данных из Яндекс.Директа используем Reports API v5. Он позволяет программно выгружать статистику по рекламным кампаниям, группам, объявлениям и ключевым фразам. Мы можем сформировать отчет типа CUSTOM_REPORT с нужными полями – например, CampaignId, Clicks, Cost, Date, ClickID (последнее – идентификатор клика, аналог yclid). Формат выгрузки – TSV по умолчанию, либо XML. JSON напрямую не выдаётся, но мы можем запросить TSV и затем сконвертировать. Практичный подход: вызывать Reports API через HTTP POST, полученный TSV парсить в Python (или с помощью утилиты) и вставлять в PostgreSQL. Можно даже миновать сохранение на диск – Postgres позволяет вставлять JSON напрямик.

Предположим, у нас есть JSON-массив кликов и расходов в переменной data_json. Тогда благодаря JSON-возможностям Postgres можно сделать вот так:

-- Пустая таблица для сырых данных Директа (jsonb поле)
CREATE TEMP TABLE tmp_direct (raw jsonb);
-- Вставляем весь JSON текстом (например, передали как параметр)
INSERT INTO tmp_direct VALUES ('{ "data": [...]}');

-- Парсим массив JSON в записи итоговой таблицы stg_direct_clicks
INSERT INTO stg_direct_clicks (campaign_id, date, click_id, clicks, cost)
SELECT campaign_id, date::DATE, click_id, clicks::INT, cost::NUMERIC
FROM jsonb_populate_recordset(NULL::stg_direct_clicks, (raw->'data')::jsonb) 
     AS t(campaign_id INT, date TEXT, click_id TEXT, clicks INT, cost NUMERIC);

Здесь мы воспользовались функцией jsonb_populate_recordset, которая превращает JSON-массив объектов в таблицу строк. В результате быстро загрузим даже сложно вложенные ответы API. Конечно, можно и просто читать TSV через COPY, аналогично Метрике. Выбор способа зависит от удобства. Главное – получить в ODS таблицу с ежедневными кликами и расходами по объявлениям Директа.

Ещё один важный набор данных из Директа – конверсии и ROI по меткам. Если вы настраивали конверсии (цели Метрики, связанные с Директом) или загрузку офлайн-конверсий, отчеты API Директа могут вернуть метрики по целям (конверсии, cost per conversion, ROI). Их тоже можно выгружать, указав ID целей и модель атрибуции при запросе. Но в нашем случае мы пойдём другим путём: считать конверсии и выручку будем сами, на основе CRM-данных.

Экспорт сделок и лидов из Bitrix24 (REST API)

Переходим к CRM. Bitrix24 предоставляет богатый REST API для чтения и выгрузки данных. Мы будем использовать методы crm.deal.list, crm.lead.list, crm.contact.list и подобные. Они возвращают данные в формате JSON (по 50 записей пачкой, можно увеличивать до 250 максимум). Так как у нас нет готового коннектора к Битрикс24, пишем небольшой скрипт на Python: он будет дергать API и складывать результаты. Bitrix24 позволяет подключиться по webhook URL без OAuth, что упрощает задачу – достаточно сгенерировать входящий вебхук со всеми правами.

На стороне PostgreSQL создаём таблицы stg_bx_deals, stg_bx_leads, stg_bx_contacts. Полей там может быть много – Bitrix отдаёт и стандартные, и кастомные поля (например, UF_*). Основные, которые понадобятся: ID сделки, название, статус (успешно/провалено), сумма, дата создания и закрытия, ответственный, ID связанного контакта, и наше кастомное поле с client-id (YCID). Для лидов – похожий набор, если используете лиды. Контакты нужны для связи сделок с клиентами (чтобы, например, агрегировать LTV на уровень контакта).

Bitrix API гибкий: можно фильтровать по дате, выбирать только нужные поля, что ускоряет выгрузку. К примеру, для ежедневной инкрементальной загрузки запрашиваем только сделки, изменённые за последний день (параметр >DATE_MODIFY). Наш ETL-скрипт может каждый час дергать новые сделки и писать/обновлять их в Postgres. Здесь важно учесть дедупликацию: если сделка обновилась, нужно внести изменения, а не дублировать строку. Лучше всего писать обновления во временную таблицу, а затем мержить с основной через INSERT ... ON CONFLICT DO UPDATE (или MERGE). Так мы сохраним целостность данных.

Bitrix24 REST API — мощный, но требует навыков программирования. Преимуществом нашего пути является независимость от платных сервисов: мы сами контролируем периодичность выгрузки и формат данных. Например, мы можем дополнительно вытянуть таймлайн сделки (метод crm.timeline.list), чтобы анализировать, сколько времени прошло от лида до продажи, или какие этапы проходил клиент. Однако для базового сквозного отчета это необязательно.

Docker-compose для конвейера. Все три блока (Метрика, Директ, Битрикс) можно автоматизировать и завернуть в контейнеры. Один из возможных сценариев – контейнер etl на Python, который по cron (say, каждую ночь) выполняет: 1) запрос Logs API Метрики за вчера, скачивает CSV и делает COPY в Postgres; 2) запрос к Reports API Директа за вчера, заливает данные; 3) вызовы к Bitrix24 API за новые сделки/лиды, выполняет UPSERT в таблицы. Контейнер Postgres (как в примере выше) хранит данные, а pgBackRest по расписанию бэкапит. Таким образом, docker-compose.yml объединяет сервисы:

• postgres – БД;
• etl – образ на основе Python 3 с нашим скриптом, пробрасываем ему переменные окружения (токены API и т.д.);
• pgbackrest – опционально, контейнер для бэкапов, монтирующий volume с данными Postgres.

Запуск docker-compose up -d поднимет всё сразу. Важно: не храните чувствительные токены в репозитории, задавайте их через .env файл. После нескольких итераций ETL у нас в PostgreSQL будет три блока таблиц: Метрика (визиты, достигающие цели и т.п.), Директ (клики, расходы) и CRM (лиды, сделки, контакты). Следующий шаг – моделирование данных для аналитики.

Моделирование данных: от staging к витринам фактов

Когда сырые данные в ODS готовы, формируем модель данных для аналитики – обычно это звездная схема: таблицы фактов (события, расходы, продажи) и справочники-измерения (кампании, клиенты, источники и т.д.). Рассмотрим дизайн схемы:

Staging (stg_*) слои – здесь данные почти без изменений, “как есть” из источников. Например:

• stg_yametrika_visits (сырые визиты Метрики),
• stg_yadirect_stats (сырые статистики Директа, например по дням и кампаниям),
• stg_bx_deals, stg_bx_contacts (сырые сделки и контакты из Bitrix24).

Мы уже позаботились об основных ключах: у визита Метрики есть client_id и yclid, у сделки B24 есть сохранённый YandexClientID (который по сути = client_id визита). Задача моделирования – слить эти данные по общим ключам и расчитать метрики.

Промежуточные представления / витрины. Можно создать представления или материальные таблицы:

• dim_date, dim_channel, dim_campaign – справочники дат, каналов, кампаний. Например, dim_channel на основе utm_source/utm_medium визита (в Метрике эти поля есть).
• fact_sessions – факт визитов: одна строка = 1 сессия сайта. Поля: время начала, источник, campaign_id, client_id, yclid, достигнута ли целевая конверсия, связанная сделка ID, выручка. Здесь мы обогащаем данные Метрики данными из CRM. Именно в fact_sessions произойдёт слияние онлайн и офлайн данных.
• fact_costs – факт затрат: одна строка = расходы за день по кампании/каналу. Поля: дата, campaign_id, показы, клики, расходы. Из Директа получим эти агрегаты.
• fact_revenue – факт выручки: по сути, сделки/продажи. Но можно организовать по-разному. Например, fact_revenue по датам оплаты: дата, сумма оплат, число продаж. Либо вообще обойтись без отдельного fact_revenue, а хранить выручку сразу в fact_sessions на уровне визита (привязав сделку к визиту).

Способов моделировать несколько, но ключевой момент – связывание идентификаторов. Приведём пример SQL, как связать визиты с сделками: допустим, в stg_bx_deals у нас есть поле UF_YANDEX_CID (куда мы писали client-id в CRM). Тогда:

-- Обновляем fact_sessions, проставляя информацию о сделке и выручке
UPDATE fact_sessions AS s
SET deal_id = d.id,
    revenue = d.amount,
    sale_date = d.close_date
FROM stg_bx_deals AS d
WHERE s.client_id = d.uf_yandex_cid
  AND s.visit_date::DATE >= d.create_date::DATE - INTERVAL '1 day'
  AND s.visit_date::DATE <= d.create_date::DATE + INTERVAL '7 days';

Здесь мы связали сессию и сделку по равенству client_id = Yandex CID сделки. Дополнительно можно условием по датам ограничить – например, сделка должна быть создана в течение ±7 дней от визита (чтобы случайно не привязать совсем старый визит к новой сделке с тем же CID, хотя шанс низкий). В Postgres 15+ можно переписать это через удобный MERGE:

MERGE INTO fact_sessions AS s
USING stg_bx_deals AS d
ON s.client_id = d.uf_yandex_cid
WHEN MATCHED THEN 
  UPDATE SET deal_id = d.id, revenue = d.amount;

После этого в таблице сессий появится столбец revenue для визитов, которые сконвертились в продажи. Аналогично можно привязать и расходы Директа. Например, в fact_sessions можно добавить cost и проставлять стоимость клика для визитов с yclid:

UPDATE fact_sessions s
SET cost = c.cost
FROM stg_yadirect_clicks c
WHERE s.yclid = c.click_id;

В итоге наш fact_sessions обогащается полями Cost (затраты на привлечение) и Revenue (доход от сделки). Отсюда недалеко до расчета CAC, ROI и LTV. CAC (cost acquisition) для конкретной сделки = cost соответствующего визита. ROI = (revenue — cost) / cost * 100%. LTV (lifetime value) можно считать на уровне dim_customer: группируем все fact_sessions по клиенту (контакту CRM) и суммируем revenue. Тогда LTV – это просто сумма revenue по клиенту, а CAC – средняя или первая затрата на привлечение. Поскольку у нас вся история в базе, можно строить сложные модели атрибуции: к примеру, если один клиент пришел по нескольким каналам, а покупка совершилась не сразу, можно распределять weight стоимости по каналам (first click, last click и т.д.). Но это выходит за рамки базового сценария.

Итак, мы получаем сквозной срез “Клики → Сессии → Сделки”. Например, можно собрать витрину fact_marketing_performance с полями: день, канал, кампании, кликов, сессий, заявок, продаж, расход, доход, ROI. Всё это считается SQL-запросом с джойнами между fact_sessions, fact_costs и fact_revenue (или напрямую stg_bx_deals). Ни одного внешнего сервиса – только ваши данные и SQL. Для проверки качества модели сравниваем агрегаты с официальными отчетами: суммы кликов/расходов должны совпасть с интерфейсом Директа, суммы заявок – с отчетом Метрики по целям, суммы продаж – с данными CRM. Если где-то расхождения, ищем потерянные связки (например, лид без привязанного client_id – такое возможно, если кто-то позвонил напрямую и попал в CRM без заполнения формы).

Data Quality: чистим и проверяем данные

На этапе интеграции важно уделить внимание качеству данных. Вот несколько моментов:

• Дедупликация. Возможны дубли при загрузке данных. Например, вы дважды загрузили одни и те же визиты за день из Метрики. Или CRM вернула сделку, которую вы уже сохраняли. Нужно либо ставить уникальные ограничения в таблицах (например, уникальный индекс на VisitID для визитов, на id для сделок), либо очищать staging перед каждой загрузкой дня. В Bitrix24 API помните, что crm.deal.list может возвращать одно и то же при пересечении диапазонов – от дубликатов спасёт UPSERT. Также в Метрике визит может обновиться (например, дополнился офлайн-конверсией спустя пару дней) – Logs API отдаст его как новый файл. Здесь стоит либо полностью перезагружать данные за последние N дней, либо обновлять выборочно поля (сложнее).

• Форматы дат и часовых поясов. Мы уже говорили о разных форматов времени: Метрика – UNIX ms или RFC3339, Директ – даты строкой YYYY-MM-DD, Bitrix – может быть YYYY-MM-DD HH:MI:SS в UTC. Приводим всё к TIMESTAMP WITHOUT TIME ZONE в UTC внутри базы. При загрузке CSV Метрики, который содержит время в формате YYYY-MM-DDTHH:MI:SS+03:00 (RFC3339 с зоной), можно пользоваться SET TIMEZONE 'UTC' для сдвига. Лучше хранить отдельно дату и время, или хотя бы дату визита отдельной колонкой, чтобы облегчить агрегации по дням.

• Проверка на полноту. Сверяем количество кликов Директа и число визитов с yclid в Метрике – сколько процентов кликов потерялись без визита? Если выявляются значительные расхождения, ищем причины (например, пользователь кликнул и закрыл страницу до загрузки Метрики – такой визит не зафиксируется). Также контролируем, что сумма cost из Директа совпадает с суммой cost в fact_sessions (после связывания) – иначе где-то клики не матчатся.

• Обновление данных. Сквозная аналитика – не статичное хранилище. Данные могут приходить с опозданием (офлайн-конверсия закрылась через неделю, возврат товара уменьшил выручку). Нужно продумывать, как обновлять уже загруженные данные. Например, если сделка поменяла стадию на “Возврат”, мы должны отнять ее сумму из LTV. Решение – вести в fact_revenue поле is_refunded или хранить актуальный статус сделки и фильтровать в отчетах. Data Quality процесса – постоянно актуализировать данные, близкие к реальности. Для этого ETL-конвейер может, скажем, раз в сутки перезагружать последние 7 дней целиком, чтобы поймать изменения.

Наконец, убедитесь, что настройки атрибуции в источниках согласованы с вашими расчетами. Например, разница моделях атрибуции: Метрика по умолчанию считает конверсию на последний значимый источник, Директ может использовать атрибуцию первого клика для отчета ROI. В нашей собственной модели мы можем выбирать любую – но важно понимать, почему цифры могут отличаться от “кабинетов”. Хорошей практикой будет добавить в отчеты сразу несколько метрик: ROI по первому клику и ROI по последнему клику, чтобы видеть разницу.

BI-слой: визуализация end-to-end в Superset и DataLens

Когда данные готовы и лежат в PostgreSQL, подключаем их к BI-инструменту для визуализации. Поскольку мы решили не ограничиваться одним сервисом, убедимся, что схема удобна для любых инструментов. PostgreSQL совместим практически со всеми платформами: от Excel Power Query до мощных систем типа Tableau. Рассмотрим два варианта: Yandex DataLens и Apache Superset.

Yandex DataLens – облачный BI от Яндекса, который бесплатно (для небольших объемов) позволяет строить дашборды. Мы можем подключить наше ODS-хранилище PostgreSQL как источник данных (DataLens поддерживает подключение PostgreSQL напрямую). Далее создаём в DataLens необходимые датасеты на основе SQL-запросов или напрямую таблиц. Например, делаем датасет “Сквозная аналитика” с запросом, объединяющим клики, сессии и выручку. Строим несколько графиков: воронка “Клики → Сессии → Лиды → Продажи”, динамика ROI по дням, LTV по каналам и т.д. Все эти дашборды будут обновляться автоматически, так как DataLens при каждом открытии подтягивает свежие данные из PostgreSQL. Это open-source BI решение (точнее, бесплатное облачное) очень удобное для связки с Яндекс-экосистемой. К тому же, в нашем случае оно находится “рядом” с данными – минимальная задержка и никакого VPN (актуально, поскольку многие западные сервисы сейчас затруднены в РФ).

Apache Superset – альтернатива, которую можно развернуть на своём сервере (тоже open-source BI). Superset также отлично коннектится к Postgres. Вы можете поднять его в том же docker-compose файле, чтобы он доступился по локальному порту. После настройки источника (указываете хост Postgres, бд, логин/пароль) Superset позволит в пару кликов создавать дашборды. Он поддерживает и диаграммы, и сводные таблицы, и метрики с фильтрами – всё, что нужно маркетологу для анализа end-to-end attribution данных. К примеру, можно сделать интерактивный дешборд: выбор диапазона дат, фильтр по каналам, и ниже показатели: сколько денег потрачено (Cost), сколько заработано (Revenue), какой ROI, сколько новых клиентов (из CRM) и их LTV.

Важно, что при нашей архитектуре ничто не мешает подключить Power BI или Excel напрямую к PostgreSQL – вдруг владельцу бизнеса так привычнее. Мы не зажаты в рамках одного приложения, как это бывает с SaaS-платформами сквозной аналитики. Данные на нашей стороне, в удобной SQL-базе.

Для финальной проверки сквозной аналитики можно построить отчёт наподобие: таблица за месяц по кампаниям, колонки – Клики, Сессии, Заявки (конверсии), Продажи (выручка), Расход, ROI. Свести все цифры и убедиться, что они имеют смысл. Если ROI показывает >100% – реклама окупается, если <100% – убыточна. При наличии данных по каждому клиенту можно также увидеть payback period (через сколько дней клиент окупается, если считать повторные продажи в LTV).

Итого, мы без помощи Roistat/Alytics выстроили свою сквозную аналитику на стеке PostgreSQL + API Яндекса + API Битрикс24. Использованы только open-source и облачные инструменты. Напоследок – небольшой чек-лист распространенных ошибок, чтобы вы избежали типичных граблей.

10 ошибок при построении сквозной аналитики (чек-лист)

• Не сохраняется client-id посетителя в CRM. Без этого не склеить офлайн продажу с онлайн-визитом. Убедитесь, что передаете идентификатор с сайта в сделку.
• Дублирование и путаница в идентификаторах. Например, перепутали yclid и client_id, или используете разные поля для разных форм. Настройте единообразно.
• Несогласованные временные зоны. Данные разных источников не приведены к UTC – в отчетах появляются “разъехавшиеся” по датам конверсии и расходы. Проведите нормализацию дат.
• Отсутствие автоматизации ETL. Ручная выгрузка CSV и копипаст в Excel – путь в никуда. Настройте скрипты или [[airflow-пайплайн автоматизации]] для регулярной загрузки.
• Игнорирование возвратов/отмен. Считать только доход по факту оплаты – и не вычитать возвраты – исказит LTV и ROI. Обновляйте данные при отмене продаж (например, помечайте сделку как “возврат” и исключайте).
• Неправильная атрибуция конверсий. Списывать всю выручку на последний клик и забывать про предыдущие контакты – распространенная ошибка. Рассмотрите разные модели или хотя бы фиксируйте первый источник клиента.
• Отсутствие резервного копирования. Потерять свою базу сквозной аналитики – очень обидно. Настройте pg_dump или pgBackRest (как мы указали) – данные должны быть в безопасности.
• Смешивание продакшн-данных с тестовыми. Например, учет тестовых заявок или внутренних визитов в общей статистике. Фильтруйте служебные данные (IP-адрес офиса, тестовые промокоды и т.д.).
• Привязка отчётов к одному инструменту. Если сделать сквозную аналитику через сложные формулы только в Google Sheets или только в Power BI, будет трудно мигрировать. Наша цель – независимость данных. Лучше хранить расчетные таблицы в БД и лишь визуализировать в BI [[гибкость-инструментов BI]].
• Отсутствие единых справочников. Разные написания источников (например, “YANDEX” vs “Yandex”) или разная валюта в расходах vs доходах приведут к кривым данным. Введите справочники для кампаний, каналов, конвертируйте валюты к единому стандарту.

Следуя этим рекомендациям, вы построите надежную систему сквозной аналитики на PostgreSQL, которая даст прозрачность от первого клика до последней рубля выручки. Ни один SaaS не знает ваш бизнес так, как ваши собственные данные – используйте их по максимуму! [[Детальный разбор настройки PostgreSQL для аналитики]] [[Сравнение подходов к end-to-end attribution]]