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

Архитектура хранения: PostgreSQL 17 в роли ODS

В основе системы — PostgreSQL 17 (релиз вышел 26 сентября 2024 года), развёрнутый как ODS-хранилище (Operational Data Store). Сырые данные из рекламных систем и CRM сначала стекаются в ODS «как есть», а уже затем моделируются под BI. Почему именно PostgreSQL, а не MySQL или ClickHouse?

Первая причина — богатый аналитический SQL: CTE и рекурсивные запросы, оконные функции, типы jsonb и tstzrange, а с версии 15 — оператор MERGE. В PostgreSQL 17 MERGE получил предложение RETURNING, функцию merge_action() и ветку WHEN NOT MATCHED BY SOURCE — это удобно для инкрементального обновления витрин. JSON-функции (jsonb_array_elements, jsonb_populate_recordset, операторы ->, ->>, #>) позволяют разбирать ответы API Яндекс.Директа, Метрики или Bitrix24 прямо в SQL, без промежуточного парсинга на стороне приложения.

Вторая причина — переносимость. PostgreSQL открыт и совместим практически с любым BI: к нему напрямую подключаются Apache Superset, Metabase, Yandex DataLens, а также Power BI и Tableau. Данные не запираются в проприетарном формате конкретной SaaS-платформы сквозной аналитики — их можно визуализировать в любом инструменте и при необходимости перенести.

Почему не MySQL. Современные версии MySQL подтянули JSON и оконные функции, но в аналитических сценариях PostgreSQL по-прежнему гибче: полноценный MERGE, INSERT ... ON CONFLICT DO UPDATE, частичные и выражательные индексы, расширения вроде pg_partman. Для ODS, где постоянно идут идемпотентные дозагрузки и обновления справочников, это принципиально.

Почему не ClickHouse. ClickHouse — превосходный колоночный движок для агрегаций по большим объёмам, но он рассчитан на append-only нагрузку. Точечные UPDATE/DELETE в нём реализованы как асинхронные мутации — тяжёлые и не транзакционные. А в ODS нам нужно регулярно мерджить свежие порции данных, переписывать статус сделки при возврате, обновлять справочники кампаний. Для такой смешанной нагрузки реляционная СУБД подходит лучше, а порог входа у неё ниже: не нужно подбирать движок таблицы и схему партиционирования под каждую задачу. Для среднего бизнеса PostgreSQL как единое оперативное хранилище закрывает задачу целиком; ClickHouse имеет смысл добавлять отдельным слоем только когда объёмы событий переваливают за десятки-сотни миллионов строк в сутки.

Такой подход уже работает на практике: рекламные и CRM-данные собираются в PostgreSQL и подключаются напрямую к Yandex DataLens для дашбордов. Сквозная атрибуция строится без лишних прослоек — все системы хранятся централизованно, а отчёты обращаются к единому источнику.

Хранение и резервное копирование. PostgreSQL 17 удобно поднимать в Docker. Минимальный docker-compose.yml с паролем, который берётся из .env, а не хранится в репозитории:

services:
  db:
    image: postgres:17
    environment:
      POSTGRES_DB: analytics
      POSTGRES_USER: analytics
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}   # значение из .env
    volumes:
      - ./data/db:/var/lib/postgresql/data
    ports:
      - "5432:5432"
    restart: unless-stopped

Для простого логического бэкапа достаточно pg_dump в формате custom по cron на хосте:

# ежедневный дамп в 03:00
0 3 * * * docker exec analytics-db pg_dump -U analytics -Fc analytics \
  > /backups/analytics_$(date +\%F).dump

Если нужна точечная reconstruct-точка во времени (PITR), используют pgBackRest. Готовый контейнер — образ woblerr/pgbackrest (его же стоит брать вместо часто путаемого с ним backrest — это другой проект на базе restic). pgBackRest требует, чтобы в postgresql.conf был включён архив WAL:

archive_mode = on
archive_command = 'pgbackrest --stanza=main archive-push %p'

Версии образов лучше фиксировать (postgres:17, woblerr/pgbackrest:2.54), а не тянуть latest — это спасает от внезапных несовместимостей при пересборке.

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

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

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

yclid (Yandex Click ID) — метка клика по объявлению Директа.
ClientID Метрики — анонимный идентификатор устройства посетителя (хранится в cookie _ym_uid).
ID контакта/сделки в Bitrix24 — запись клиента в CRM.

Как передаётся yclid. Когда аккаунты Директа и Метрики связаны, к ссылкам объявлений автоматически добавляется параметр yclid с уникальным номером клика. Метрика считывает его из URL и привязывает визит к конкретному клику. Важная оговорка, которая дальше повлияет на модель данных: yclid Метрика использует внутри себя для атрибуции, но наружу — ни в Logs API, ни в Reports API Директа — он как отдельное поле не выгружается. Поэтому связывать клики с визитами на уровне базы мы будем не по yclid, а по идентификаторам кампании/объявления и дате (об этом — в разделе про моделирование). Прямое же применение yclid — обратная загрузка офлайн-конверсий в Метрику, к ней вернёмся в конце.

Как пробросить ClientID в CRM. Самый распространённый приём: при отправке лид-формы записать текущий ClientID в скрытое поле и передать его в CRM вместе с заявкой. Получать ClientID нужно через актуальный интерфейс ym(...) — устаревший объект yaCounterXXXX.getClientID() больше не используется:

<!-- Скрытые поля в форме заявки -->
<input type="hidden" name="ym_client_id" id="ym_client_id">
<input type="hidden" name="ga_client_id" id="ga_client_id">
<script>
  // ClientID Яндекс Метрики. 12345678 — замените на номер вашего счётчика.
  // Вызов ym() ставится в очередь и отработает после загрузки счётчика.
  ym(12345678, 'getClientID', function (clientID) {
    document.getElementById('ym_client_id').value = clientID;
  });

  // ClientID Google Analytics 4 из cookie _ga (формат GA1.2.XXXXXXXXX.YYYYYYYYY)
  var ga = document.cookie.match(/_ga=GA\d\.\d\.(\d+\.\d+)/);
  if (ga) {
    document.getElementById('ga_client_id').value = ga[1];
  }
</script>

В Bitrix24 заводим пользовательские поля (тип UF_*) для лида или сделки — например, «Yandex ClientID» и «Google ClientID», — куда эти значения и запишутся. Для звонков и офлайн-продаж связку обеспечивает коллтрекинг (он умеет передавать ClientID в CRM); для офлайн-точек применяют промокоды или QR с UID. Главное — не потерять связку «онлайн-визит → офлайн-продажа», именно такие продажи чаще всего выпадают из отчётов.

Что такое _ym_uid, ya_client_id, ya_uuid. Эти названия часто путают:

_ym_uid — cookie Метрики, в которой и хранится ClientID (случайное число плюс метка времени первого визита).
ya_client_id — то же значение, просто имя переменной в чьём-то коде; мы получили его методом getClientID и положили в clientID.
ya_uuid — к веб-визитам и кликам отношения не имеет; так иногда называют идентификатор авторизованного пользователя в экосистеме Яндекса. В сквозной аналитике его можно игнорировать.

Если на сайте есть личный кабинет, в Метрику можно передавать собственный UserID (метод ym(ID, 'userParams', { UserID: '12345' })) — он идентифицирует человека на разных устройствах, в отличие от привязанного к браузеру ClientID. Для базовой задачи это усложнение необязательно: мы связываем ClientID Метрики с записью в Bitrix24, сохраняя ClientID в сделке, а затем «склеиваем» таблицы веб-сессий и сделок уже в PostgreSQL.

Стоит пробрасывать в CRM и источник заявки. Метрика разметит визиты UTM-метками автоматически, но отделу продаж полезно видеть канал прямо в сделке (поле «Источник» вида Yandex Direct / поиск). Это либо штатное поле Bitrix24, либо кастомное, которое заполняется из UTM при отправке формы. Если нужна атрибуция вплоть до объявления, в CRM можно сохранять и utm_content с ID креатива.

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

Первое препятствие при сведении данных — разное понятие сессии в веб-аналитике и в рекламных кликах.

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

Яндекс.Директ. Здесь учёт строится на кликах: каждое нажатие фиксируется со своим yclid и временем. Понятия «сессия» в Директе нет — поведением на сайте после клика занимается Метрика. Поэтому источник по поведению (визиты, цели) мы берём из Метрики, а расход и клики — из Директа, и сопоставляем их. Иногда один визит Метрики соответствует нескольким кликам; в большинстве случаев всё же 1 клик ⇒ 1 визит, особенно для первого клика пользователя.

Нормализация времени. Метрика отдаёт время в часовом поясе счётчика, Директ — даты строкой YYYY-MM-DD, Bitrix24 — в часовом поясе портала. Чтобы корректно соединять данные, всё приводим к единому стандарту — UTC. В PostgreSQL удобнее всего хранить отметки времени в типе timestamptz: внутри значение нормализуется к UTC, а при выборке форматируется в нужный пояс.

При построении объединённой витрины визитов полезно опираться на встроенный ym:s:visitID из Logs API (уникален в пределах счётчика) — он станет первичным ключом таблицы сессий и свяжет визит со всеми его событиями.

Читай также: Power Apps и Power Automate в Microsoft Power BI

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

Теперь о конвейере — как вытащить данные из источников и загрузить в PostgreSQL.

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

Logs API отдаёт сырые несводные данные — каждый визит отдельной строкой со множеством полей. Ограничения, которые стоит учитывать заранее:

данные за текущий день недоступны (могут быть неполными) — запрашиваем вчерашний день и старше;
в одном запросе — период не более 1 года;
суммарный объём подготовленных и не удалённых логов на счётчик — не более 10 ГБ (квоту освобождают, удаляя скачанные лог-файлы);
список полей в параметре fields — не длиннее 3000 символов;
не более 5000 обращений к одному счётчику в сутки.

Выгрузка асинхронная и идёт в четыре шага:

Отправляем запрос на подготовку логов (диапазон дат и набор полей).
API ставит задачу в очередь и возвращает её идентификатор.
Опрашиваем статус задачи, пока он не станет processed.
Скачиваем готовый файл (TSV).

Базовый адрес — https://api-metrika.yandex.net/management/v1/counter/{id}; вся последовательность на Python укладывается в несколько строк:

import time, requests

COUNTER = 12345678                       # номер счётчика Метрики
TOKEN   = "<OAuth-токен>"
BASE    = f"https://api-metrika.yandex.net/management/v1/counter/{COUNTER}"
HEAD    = {"Authorization": f"OAuth {TOKEN}"}

# 1. создаём задачу на выгрузку визитов за вчера
#    (перед этим можно дёрнуть /logrequests/evaluate и проверить объём)
params = {
    "date1": "2026-06-25",
    "date2": "2026-06-25",
    "source": "visits",
    "fields": "ym:s:visitID,ym:s:clientID,ym:s:dateTime,"
              "ym:s:lastUTMSource,ym:s:lastDirectClickOrder,"
              "ym:s:lastDirectClickBanner,ym:s:isRobot",
}
req = requests.post(f"{BASE}/logrequests", headers=HEAD, params=params)
request_id = req.json()["log_request"]["request_id"]

# 2. ждём, пока Метрика подготовит лог
while True:
    info = requests.get(f"{BASE}/logrequest/{request_id}", headers=HEAD)
    info = info.json()["log_request"]
    if info["status"] == "processed":
        break
    time.sleep(20)

# 3. скачиваем все части (большой лог делится на parts) и грузим COPY
for part in info["parts"]:
    tsv = requests.get(
        f"{BASE}/logrequest/{request_id}/part/{part['part_number']}/download",
        headers=HEAD,
    ).text
    # ... сохранить в файл и выполнить COPY в stg_yametrika_visits

# 4. освобождаем квоту хранилища
requests.post(f"{BASE}/logrequest/{request_id}/clean", headers=HEAD)

Метод clean в конце освобождает квоту хранилища — про лимит в 10 ГБ на счётчик забывать нельзя, иначе новые запросы начнут отклоняться.

Для сквозной аналитики из таблицы визитов берут как минимум такие поля (префикс ym:s: — это визиты, `

` подставляет модель атрибуции, например `last` или `lastsign`): * `ym:s:visitID`, `ym:s:clientID`, `ym:s:dateTime`, `ym:s:date` — идентификаторы и время; * `ym:s: UTMSource`, `UTMMedium`, `UTMCampaign`, `UTMContent` — метки; * `ym:s: DirectClickOrder` (ID кампании Директа), `DirectBannerGroup` (ID группы), `DirectClickBanner` (ID объявления), `DirectPhraseOrCond` (условие показа) — именно по ним мы свяжемся с расходами Директа; * `ym:s:goalsID` — достигнутые цели; * `ym:s:isRobot` — флаг робота (Logs API не умеет фильтровать данные на стороне сервера, поэтому ботов отсеивают уже в SQL по этому полю). Запросы удобно автоматизировать на Python (`requests` + OAuth-токен Яндекса). Полученный TSV грузим в базу командой `COPY` — это самый быстрый путь: «`sql COPY stg_yametrika_visits FROM ‘/path/to/metrika_2026-06-01.tsv’ (FORMAT csv, DELIMITER E’\t’, HEADER true, ENCODING ‘UTF8’); «` TSV с табуляцией удобнее CSV — не споткнёмся о запятые внутри значений. Некоторые поля Метрика отдаёт как Unix-время в миллисекундах — их приводим через `to_timestamp(ms / 1000)`. О задержке данных. Logs API работает с лагом примерно в сутки; публичного потокового API для веб-Метрики, который отдавал бы визиты в реальном времени, нет. Если нужна почти-онлайн картина, события собирают параллельно на стороне сайта (свой beacon или `dataLayer`) и пишут в базу напрямую, а Logs API используют как «эталон» для ежедневной сверки. ### Получение кликов и затрат из Яндекс.Директа (Reports API) Для расхода используем **Reports API v5**. Он выгружает агрегированную статистику по кампаниям, группам, объявлениям и ключевым фразам. Важно понимать его природу: **это агрегаты, а не журнал кликов**. Отдельного `ClickID`/yclid в отчёте нет — в наборе полей есть `CampaignId`, `AdGroupId`, `AdId`, `Criterion`, `Date`, `Impressions`, `Clicks`, `Cost`, `AvgCpc`, `Ctr`, `Conversions` и т. п. Поэтому связь с Метрикой мы строим по идентификаторам кампании/объявления и дате, а не по клику. Запрашиваем отчёт `CUSTOM_REPORT` методом POST на `https://api.direct.yandex.com/json/v5/reports`. Тело запроса — JSON с описанием отчёта, а сами данные приходят таблицей TSV (JSON-вывод Reports API не поддерживает): «`json { «params»: { «SelectionCriteria»: { «DateFrom»: «2026-06-25», «DateTo»: «2026-06-25» }, «FieldNames»: [«Date», «CampaignId», «AdGroupId», «AdId», «Impressions», «Clicks», «Cost»], «ReportName»: «costs_2026-06-25», «ReportType»: «CUSTOM_REPORT», «DateRangeType»: «CUSTOM_DATE», «Format»: «TSV», «IncludeVAT»: «YES», «IncludeDiscount»: «NO» } } «` Два места, где у новичков «не сходятся деньги» с кабинетом: * **Микроединицы.** Без HTTP-заголовка `returnMoneyInMicros: false` поле `Cost` приходит умноженным на 1 000 000. С этим заголовком значения возвращаются в валюте, округлённые до копеек. * **НДС.** Параметр `IncludeVAT` (`YES`/`NO`) определяет, включён ли в `Cost` налог (+20%). Выберите одно значение и используйте его постоянно, иначе расход не сойдётся с выручкой из CRM. Готовый TSV грузим в `stg_yadirect_stats` тем же `COPY`, что и данные Метрики. JSON-разбор в SQL пригодится на следующем источнике — в Bitrix24, который как раз отдаёт JSON. Покажем приём там. ### Экспорт сделок и лидов из Bitrix24 (REST API) Bitrix24 предоставляет REST API; авторизоваться проще всего через входящий вебхук (без OAuth — достаточно сгенерировать URL с нужными правами). Для выгрузки сущностей CRM используют методы `crm.item.list`, `crm.lead.list`, `crm.contact.list`. Метод `crm.deal.list` всё ещё работает, но Bitrix пометил его как устаревший и рекомендует универсальный `crm.item.list` (для сделок `entityTypeId = 2`). Ключевые особенности, без которых выгрузка ломается на больших объёмах: * **Пагинация по 50.** Списочные методы отдают максимум 50 записей за вызов — увеличить размер страницы нельзя. Следующую порцию запрашивают параметром `start` (кратен 50). На больших списках вместо медленного `start` применяют «быструю» постраничную выборку: сортируют по `ID` и фильтруют `>ID` последней записи, передавая `start = -1`, чтобы не считать общее число строк. * **Инкрементальная загрузка.** Берём только изменённые сущности фильтром по дате модификации (`>=dateModify`) — не перевыгружаем всё каждый раз. * **Лимиты запросов.** У облачного Bitrix24 действует ограничение около 2 запросов в секунду; пакетный метод `batch` объединяет до 50 команд в один вызов и заметно ускоряет выгрузку. На стороне PostgreSQL создаём `stg_bx_deals`, `stg_bx_leads`, `stg_bx_contacts`. Из сделки нам нужны: `id`, название, стадия и её семантика (успех/провал), сумма (`opportunity`), даты создания и закрытия, ответственный, ID связанного контакта и наше кастомное поле с ClientID. Контакты нужны, чтобы агрегировать LTV на уровне клиента. Ответ Bitrix24 — это JSON, поэтому грузить его можно прямо в `jsonb` и разбирать в SQL, заодно делая дедупликацию через `INSERT … ON CONFLICT DO UPDATE`: «`sql CREATE TEMP TABLE tmp_bx (raw jsonb); — сюда кладём тело ответа crm.item.list целиком INSERT INTO tmp_bx VALUES (‘{«result»: {«items»: [ /* … */ ]}}’); INSERT INTO stg_bx_deals (id, title, stage_id, opportunity, date_create, yandex_cid) SELECT (e->>’id’)::bigint, e->>’title’, e->>’stageId’, (e->>’opportunity’)::numeric, (e->>’createdTime’)::timestamptz, e->>’ufCrm_YANDEX_CID’ FROM tmp_bx, jsonb_array_elements(raw->’result’->’items’) AS e ON CONFLICT (id) DO UPDATE SET stage_id = EXCLUDED.stage_id, opportunity = EXCLUDED.opportunity, title = EXCLUDED.title; «` Сам обход страниц через входящий вебхук выглядит так: «`python import requests WEBHOOK = «https://your-portal.bitrix24.ru/rest/1//» def fetch_deals(updated_from): start, items = 0, [] while True: r = requests.post(WEBHOOK + «crm.item.list», json={ «entityTypeId»: 2, # 2 — сделка «filter»: {«>=updatedTime»: updated_from}, «order»: {«id»: «ASC»}, «start»: start, }).json() items += r[«result»][«items»] if «next» not in r: # страниц больше нет break start = r[«next»] # смещение, кратное 50 return items «` При необходимости подтягивают и таймлайн сделки (`crm.timeline.list`) — чтобы анализировать, сколько времени прошло от лида до продажи. Для базового отчёта это необязательно. **Docker-compose для конвейера.** Все три источника заворачиваем в контейнеры. Типовой сценарий — контейнер `etl` на Python, который по расписанию (например, ночью) выполняет три шага: тянет Logs API Метрики за вчера и делает `COPY`; запрашивает Reports API Директа за вчера; вызывает методы Bitrix24 за изменённые сделки/лиды и делает `UPSERT`. Рядом — контейнер `db` (PostgreSQL) и по желанию резервное копирование. Запуск `docker-compose up -d` поднимает всё сразу. Токены API храните в `.env`, а не в репозитории. После нескольких прогонов в базе будет три блока таблиц — Метрика, Директ и CRM, — и можно переходить к моделированию. ## Моделирование данных: от staging к витринам фактов Когда сырые данные в ODS готовы, строим модель для аналитики — звёздную схему из таблиц фактов (визиты, расходы, продажи) и измерений (даты, каналы, кампании, клиенты). **Staging-слой (`stg_*`)** — данные «как есть» из источников: `stg_yametrika_visits`, `stg_yadirect_stats`, `stg_bx_deals`, `stg_bx_contacts`. У визита Метрики уже есть `client_id`, `visit_id` и ID кампании/объявления Директа; у сделки — сохранённый ClientID. Задача — слить их по общим ключам и посчитать метрики. **Витрины фактов и измерений.** Центральная таблица — `fact_sessions`, по строке на визит. Покажем её определение, чтобы было видно, какие поля откуда берутся: «`sql CREATE TABLE fact_sessions ( visit_id bigint PRIMARY KEY, — ym:s:visitID client_id bigint NOT NULL, — ym:s:clientID visit_dttm timestamptz NOT NULL, — ym:s:dateTime, в UTC utm_source text, utm_medium text, utm_campaign text, direct_campaign_id bigint, — ym:s: DirectClickOrder direct_ad_id text, — ym:s: DirectClickBanner is_robot boolean DEFAULT false, — ym:s:isRobot cost numeric(12,2), — из Reports API Директа deal_id bigint, — из Bitrix24 revenue numeric(14,2), sale_date timestamptz ); «`

Читай также: Интеграция 1С с BI-системами (Power BI, Apache Superset, Yandex DataLens)

Рядом — `fact_costs` (расход по дню/кампании/объявлению из Директа) и справочники `dim_date`, `dim_channel`, `dim_campaign`. Выручку можно хранить отдельной таблицей `fact_revenue` (по датам оплат) либо, как здесь, прямо в `fact_sessions` на уровне визита. **Связка визита со сделкой — по ClientID.** Сделка из Bitrix24 несёт сохранённый ClientID, по нему и соединяем. Дополнительно ограничиваем окном по дате, чтобы старый визит случайно не привязался к новой сделке с тем же ClientID: «`sql MERGE INTO fact_sessions AS s USING stg_bx_deals AS d ON s.client_id = d.yandex_cid AND d.date_create::date BETWEEN s.visit_dttm::date AND s.visit_dttm::date + INTERVAL ’30 days’ WHEN MATCHED THEN UPDATE SET deal_id = d.id, revenue = d.opportunity, sale_date = d.date_create; «` Оговорка из практики: `MERGE` при параллельных загрузках не гарантирует отсутствие конфликтов уникальности, поэтому для идемпотентных дозагрузок надёжнее `INSERT … ON CONFLICT DO UPDATE`. `MERGE` хорош там, где обновление идёт в один поток. **Связка визита с расходом — по кампании/объявлению и дате.** Поскольку Директ отдаёт агрегаты, а не стоимость каждого клика, точную цену конкретного визита взять неоткуда. Корректный приём — рассчитать среднюю стоимость клика за день и разнести её на визиты: «`sql — суточная стоимость клика по объявлению CREATE VIEW v_direct_cpc AS SELECT date, campaign_id, ad_id, cost / NULLIF(clicks, 0) AS cost_per_click FROM stg_yadirect_stats; — проставляем затраты визитам по кампании/объявлению и дате UPDATE fact_sessions s SET cost = c.cost_per_click FROM v_direct_cpc c WHERE s.direct_campaign_id = c.campaign_id AND s.direct_ad_id = c.ad_id AND s.visit_dttm::date = c.date; «` Это приближение (равномерное распределение дневного расхода по кликам), но оно сходится с кабинетом на уровне суммы за день — а именно сумма и важна для расчёта окупаемости. **Метрики.** Получив на визите `cost` и `revenue`, считаем: * **CAC** (стоимость привлечения) — расход на привлекшую сделку сессию; * **ROMI / ROI** — `(revenue − cost) / cost × 100%`; * **ДРР** (доля рекламных расходов) — `cost / revenue × 100%`, привычная для российского рынка обратная метрика; * **LTV** — сумма `revenue` по клиенту (`dim_customer`): группируем все его сессии и суммируем выручку. Поскольку вся история в базе, поверх неё можно строить и более сложные модели атрибуции (first click, last click, линейную) — распределять вес стоимости между каналами, когда клиент пришёл по нескольким касаниям. Для базового сценария достаточно last click. Итог — сквозной срез «клики → сессии → сделки». Из него собирается витрина `fact_marketing_performance` (день, канал, кампания, клики, сессии, заявки, продажи, расход, доход, ROMI) обычными `JOIN` между `fact_sessions` и `fact_costs`. Сам отчёт — это одна группировка, сразу считающая ROMI и ДРР: «`sql SELECT date_trunc(‘day’, s.visit_dttm) AS day, s.utm_source AS channel, s.direct_campaign_id AS campaign_id, count(*) FILTER (WHERE NOT s.is_robot) AS sessions, count(s.deal_id) AS deals, coalesce(sum(s.cost), 0) AS cost, coalesce(sum(s.revenue), 0) AS revenue, round((sum(s.revenue) — sum(s.cost)) / nullif(sum(s.cost), 0) * 100, 1) AS romi_pct, round(sum(s.cost) / nullif(sum(s.revenue), 0) * 100, 1) AS drr_pct FROM fact_sessions s WHERE s.visit_dttm >= now() — INTERVAL ’30 days’ GROUP BY 1, 2, 3 ORDER BY revenue DESC; «` Чтобы такие выборки и связки по `client_id` и кампаниям не упирались в полное сканирование таблицы, заведите индексы под основные ключи соединения: «`sql CREATE INDEX ON fact_sessions (client_id); CREATE INDEX ON fact_sessions (direct_campaign_id, direct_ad_id, visit_dttm); CREATE INDEX ON fact_sessions (visit_dttm); «` Качество модели проверяем сверкой агрегатов: клики и расход должны совпасть с интерфейсом Директа, заявки — с отчётом Метрики по целям, продажи — с CRM. Расхождения указывают на потерянные связки (например, лид без ClientID — звонок напрямую без заполнения формы). ## Data Quality: чистим и проверяем данные Качество данных на интеграции решает всё. На что смотреть: * **Дедупликация.** Уникальные ограничения там, где это естественно: индекс на `visit_id` для визитов, на `id` для сделок. При повторной загрузке диапазона `crm.item.list` может вернуть уже сохранённые сделки — спасает `UPSERT`. Визит в Метрике тоже может «дополниться» (например, офлайн-конверсией спустя пару дней), поэтому за последние N дней проще перезагружать данные целиком. * **Форматы дат и часовые пояса.** Метрика — Unix-мс или RFC3339, Директ — `YYYY-MM-DD`, Bitrix — в поясе портала. Приводим всё к UTC и храним в `timestamptz`. Дату визита полезно дублировать отдельной колонкой `date` — агрегаты по дням считаются быстрее. * **Полнота.** Сверяем число кликов Директа и число визитов с заполненным `direct_campaign_id`: какой процент кликов остался без визита? Часть теряется штатно (пользователь закрыл страницу до загрузки счётчика), но крупные расхождения — повод искать причину. Контролируем и совпадение суммы `cost` из Директа с суммой `cost` в `fact_sessions`. * **Боты.** Отсеиваем строки с `is_robot = true` (поле `ym:s:isRobot`) и служебный трафик (IP офиса, тестовые заявки и промокоды). * **Поздние данные и возвраты.** Сквозная аналитика не статична: офлайн-конверсия может закрыться через неделю, а возврат — уменьшить выручку. Храните актуальный статус сделки и флаг `is_refunded`, исключайте возвраты из LTV и ROMI. Практичное решение — ежедневно перезагружать последние 7 дней целиком, чтобы поймать изменения. * **Согласованность атрибуции.** Метрика по умолчанию относит конверсию к последнему значимому источнику, отчёт Директа может считать по первому клику — отсюда расхождения с «кабинетами». В своих отчётах модель выбираем сами, но полезно выводить ROMI и по первому, и по последнему клику, чтобы видеть разницу. ## BI-слой: визуализация end-to-end в DataLens и Superset Данные лежат в PostgreSQL — подключаем BI. Схема не привязана к конкретному инструменту, к Postgres коннектятся почти все: от Excel Power Query до Tableau. Разберём два варианта. **Yandex DataLens** — облачный BI Яндекса с бесплатным тарифом для небольших объёмов. Подключаем ODS как источник, создаём датасеты на SQL или таблицах и строим дашборды: воронку «клики → сессии → лиды → продажи», динамику ROMI по дням, LTV по каналам. DataLens подтягивает свежие данные при каждом открытии и находится «рядом» с данными — минимум задержки и никаких сложностей с зарубежными сервисами. Чтобы тяжёлые джойны не считались на лету, витрину можно материализовать (`MATERIALIZED VIEW` с обновлением по расписанию). **Apache Superset** — open-source BI для self-hosted-варианта; разворачивается в том же `docker-compose` и тоже отлично работает с Postgres. После настройки источника собираете дашборды с фильтрами по датам и каналам: Cost, Revenue, ROMI, число новых клиентов и их LTV. Архитектура не зажимает в одном приложении: при желании владелец бизнеса подключит к той же базе Power BI или Excel. Данные на вашей стороне, в удобной SQL-базе, а не внутри SaaS. **Обратная связь: возвращаем выручку в Метрику и Директ.** Сильная сторона своей сквозной аналитики — не только отчёты, но и петля оптимизации. Посчитав в PostgreSQL выручку по ClientID (или yclid), её загружают обратно в Метрику как офлайн-конверсии: метод `POST /management/v1/counter/{counterId}/offline_conversions/upload`, в CSV — `ClientID` (или `yclid`/`UserID`/`PurchaseId`), `Target`, `DateTime`, `Price`, `Currency`. Данные появляются в отчётах примерно за два часа. После этого автостратегии Директа («оплата за конверсии», оптимизация по ценности) учитывают реальные деньги из CRM, а не только заявки на сайте — реклама начинает оптимизироваться на прибыль, а не на лиды. Для финальной проверки соберите отчёт за месяц по кампаниям: колонки «Клики», «Сессии», «Заявки», «Продажи», «Расход», «ROMI». Если ROMI выше 100% — реклама окупается, ниже — убыточна. При наличии повторных продаж в LTV виден и payback period — за сколько дней клиент окупает стоимость привлечения. ## 10 ошибок при построении сквозной аналитики (чек-лист) * **ClientID не сохраняется в CRM.** Без него не склеить офлайн-продажу с онлайн-визитом. Проверьте, что идентификатор уходит с сайта в сделку. * **Связывание Директа с визитами «по yclid».** yclid не выгружается ни из Reports API, ни из Logs API. Соединяйте по ID кампании/объявления и дате (`DirectClickOrder` и др.), а yclid используйте по назначению — для загрузки офлайн-конверсий. * **Устаревший вызов `getClientID`.** Объект `yaCounterXXXX` больше не работает — используйте `ym(id, ‘getClientID’, callback)`. * **Пагинация Bitrix24 «по 250».** Списочные методы отдают максимум 50 записей за вызов; листайте через `start` или быстрым способом по `ID`, объединяйте вызовы в `batch`. * **Несогласованные часовые пояса.** Данные источников не приведены к UTC — конверсии и расходы «разъезжаются» по датам. Нормализуйте время, храните в `timestamptz`. * **Нет автоматизации ETL.** Ручная выгрузка CSV и копипаст в Excel не масштабируется. Настройте скрипты и расписание (cron, а на сложных зависимостях — оркестратор вроде Airflow). * **Игнорирование возвратов и отмен.** Учитывать только оплаты и не вычитать возвраты — значит завышать LTV и ROMI. Помечайте сделку как возврат и исключайте из расчётов. * **Неверная атрибуция.** Списывать всю выручку на последний клик и забывать про предыдущие касания — частая ошибка. Фиксируйте хотя бы первый источник клиента, а лучше сравнивайте несколько моделей. * **Нет резервного копирования.** Потерять базу сквозной аналитики обидно и дорого. Настройте `pg_dump` или pgBackRest с архивом WAL. * **Нет единых справочников.** Разные написания источников (`YANDEX` vs `Yandex`) и разные валюты в расходах и доходах дают кривые цифры. Заведите справочники кампаний и каналов, приводите валюту к единому стандарту. Следуя этим рекомендациям, вы получите сквозную аналитику на стеке PostgreSQL + API Яндекса + Bitrix24, прозрачную от первого клика до последнего рубля выручки. Все инструменты — open-source или с бесплатным тарифом, а данные остаются на вашей стороне: ни один SaaS не знает ваш бизнес так, как ваши собственные данные.