Сквозная аналитика на PostgreSQL: Метрика, Директ и Битрикс24

Обновлено 3 сентября 2026 года.

PostgreSQL может объединять данные рекламы, веб-аналитики и CRM, но точность сквозного отчёта определяется моделью данных. Визит, клик, сделка и платёж — разные сущности. Их нельзя соединить в одну плоскую таблицу без правил детализации и атрибуции.

Ниже — архитектура и учебный SQL-пример. Для внедрения потребуется настроить доступы, загрузчики и контрольные сверки под конкретные аккаунты. Наличие контейнера с базой само по себе не создаёт готовую систему аналитики.

Роль PostgreSQL и слоёв хранения

Удобно разделить технический приём пакетов staging, актуальное состояние объектов ods, необходимую историю и подготовленные витрины mart. Это логические роли: небольшой проект может разместить их в одной базе.

PostgreSQL поддерживает JSONB, транзакции, оконные функции, индексы и обновление по уникальному ключу. Ветка 17 остаётся поддерживаемой, но актуальная стабильная основная версия на дату проверки — 18. Выбор версии зависит от совместимости расширений, драйверов и процедуры обновления. См. политику поддержки PostgreSQL.

MySQL тоже подходит для ряда интеграционных задач, а выбор ClickHouse не определяется порогом «100 миллионов строк в сутки». Нужно измерять запросы, частоту изменений и ресурсы. Утверждение, что ClickHouse умеет только append-only и тяжёлые мутации, устарело: появились lightweight updates, с условиями применения конкретной версии. Это не делает его полностью взаимозаменяемым с PostgreSQL. См. описание изменений ClickHouse 25.7.

Какие факты хранить отдельно

Сумма сделки opportunity не является автоматически полученной оплатой или признанной выручкой. Дата создания сделки не заменяет дату продажи. Частичный возврат уменьшает соответствующую сумму, а не обязательно исключает всю сделку.

Идентификаторы: что они связывают

ClientID Метрики относится к посетителю в браузере. Он не является безусловным идентификатором человека: смена браузера, устройства или cookie меняет доступную связь. При отправке формы сохраняйте ClientID вместе с заявкой, если он получен. Метод — ym(counterId, 'getClientID', callback); результат передаётся строкой. См. официальный getClientID.

UserID задаётся владельцем сайта. Для его передачи используется ym(counterId, 'setUserID', userId), а не произвольный параметр userParams с похожим названием. Метод связывает идентификаторы только для посещений, где он вызывался. См. setUserID.

yclid — идентификатор рекламного клика. Его можно сохранить из URL при поступлении заявки и использовать в поддерживаемых сценариях передачи конверсий. Reports API Директа предоставляет агрегированную статистику, а не цену каждого отдельного клика. Поэтому кампания + дата — ключ для сравнения агрегатов, а не доказательство связи конкретного клика и визита.

Наличие yclid во всех вариантах выгрузки нельзя описывать одним запретом. В стандартном списке полей Logs API отдельного поля yclid нет; при этом Data Streaming Метрики предоставляет расширенную детализацию, включая YCLID. См. Data Streaming.

В CRM сохраняйте также портал, ID объекта, время получения заявки, UTM-метки и способ определения источника. Отсутствующие идентификаторы оставляйте отсутствующими. Нельзя объявлять все неопознанные заявки прямыми заходами или подбирать им случайный визит.

Метрика: Logs API и важные ограничения схемы

Logs API отдаёт неагрегированные данные. Текущий день недоступен; допустимый период одного запроса — до года, длина fields — до 3000 символов. Базовая квота подготовленных логов составляет 10 ГБ; увеличение связано с Метрикой Про. Визиты могут дополняться после первой выгрузки, поэтому вчерашний пакет не следует считать навсегда окончательным. См. ограничения Logs API.

В текущем справочнике есть ym:s:isRobotPro для Метрики Про. Универсальное ym:s:isRobot из прежнего примера там не описано. Нельзя требовать его у каждого счётчика. Поля и типы проверяются по списку полей визитов.

Загрузчик создаёт запрос, дожидается готовности, скачивает все части и проверяет их запись. Только после успешного сохранения освобождается квота подготовленных файлов. Нужны тайм-ауты HTTP и общего ожидания, обработка ошибок, ограниченные повторы и журнал номера запроса. Бесконечный цикл ожидания без проверки статуса ошибки не подходит для регулярного конвейера.

TSV следует разбирать с учётом экранирования, массивов, пустых значений и единиц каждого поля. Нельзя объявлять любой текст с табуляцией совместимым с произвольным режимом PostgreSQL COPY. Клиентская загрузка также отличается от серверного чтения файла по пути.

Для меньшей задержки есть отдельный Data Streaming в управляемый ClickHouse в Yandex Cloud: документация указывает задержку до 15 минут и подключение пакета Метрика Про или Data Streaming. Формат не обратно совместим с Logs API и содержит версии записей. Это отдельная интеграция, которую нужно учесть в архитектуре и стоимости.

Директ: расходы, валюта и округления

Загружайте отчёт Reports API в согласованных разрезах: например, дата и кампания. Сохраните аккаунт, валюту, состав полей и параметры отчёта. Готовые строки не являются журналом индивидуальных кликов. Официальный пример отчёта по кликам и стоимости показывает формат запроса и TSV-ответа.

Без returnMoneyInMicros: false денежные показатели возвращаются в микроединицах. IncludeVAT управляет включением НДС для соответствующих показателей, но не превращает произвольную сумму из Метрики или CRM в сумму с тем же налоговым смыслом. Не нужно самостоятельно прибавлять фиксированные 20% к уже полученному расходу. Из-за округления суммы детальных строк могут слегка отличаться от итогового отчёта без группировок. См. правила денежных значений.

Дата расхода — календарная дата отчётности источника. Перевод её в «полночь UTC» не восстановит время кликов и может сдвинуть день. Для событий храните момент времени и правила часового пояса, а для дневных агрегатов — исходную бизнес-дату и её контекст.

Битрикс24: выгрузка объектов и история

Для сделок доступен crm.item.list с entityTypeId = 2. У метода страницы по 50 записей; следующая страница определяется ответом next. Поля, включая пользовательские, узнают через crm.item.fields. Формат имён пользовательских полей зависит от useOriginalUfNames; нельзя заранее придумать имя вроде ufCrm_YANDEX_CID и ожидать его на любом портале. См. crm.item.list.

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

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

Для переходов по стадиям существует crm.stagehistory.list. Вымышленный универсальный crm.timeline.list не следует использовать вместо него. При работе с таймлайном и делами выбирают документированные методы нужного типа данных.

Атрибуция: связь должна быть однозначной

JOIN по ClientID и условию «визит за 30 дней до сделки» может найти несколько визитов и несколько сделок. Обновление каждой сессии суммой сделки создаёт двойной счёт, а MERGE может завершиться ошибкой при нескольких подходящих исходных строках.

Для учебной модели last touch сначала выберите ровно одно допустимое касание перед заявкой: с заданным окном, правилами исключения и устойчивым порядком при одинаковом времени. Затем сохраните связь отдельно. Для многоканальной модели храните веса, сумма которых по атрибутируемому объекту равна единице. «Источник не определён» должен оставаться отдельным результатом.

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

Почему CPC нельзя просто записать каждому визиту

Учебный пример: Директ показал 100 кликов и расход 1 000 ₽, Метрика — 80 связанных визитов. Средний CPC равен 10 ₽. Если назначить каждому визиту по 10 ₽, сумма станет 800 ₽: 200 ₽ исчезнут из отчёта.

Основной расход следует сохранить в таблице расходов полностью. При необходимости распределения по 80 визитам по 12,50 ₽ итог сохранится, но это будет расчётное распределение, а не измеренная стоимость каждого визита. Для группы без найденных визитов нужен нераспределённый остаток.

SQL-пример: соединение после агрегации

Следующий пример использует только вымышленные данные и не обращается к API. Оплаты сначала суммируются до даты и кампании. Полное соединение сохраняет кампании без оплат и оплаты без расхода. Значения уже приведены к одной валюте; unattributed означает неопределённый источник. В реальном ключе также учитывается рекламный аккаунт.

WITH costs(day, campaign_key, cost) AS (
    VALUES (DATE '2026-08-01', '101', 1000::numeric),
           (DATE '2026-08-01', '102',  300::numeric)
), payments(day, campaign_key, payment_id, amount) AS (
    VALUES (DATE '2026-08-01', '101', 'p1', 4000::numeric),
           (DATE '2026-08-01', '101', 'p2', 1500::numeric),
           (DATE '2026-08-01', '103', 'p3',  700::numeric),
           (DATE '2026-08-01', 'unattributed', 'p4', 200::numeric)
), payment_totals AS (
    SELECT day, campaign_key, SUM(amount) AS paid
    FROM payments
    GROUP BY day, campaign_key
)
SELECT COALESCE(c.day, p.day) AS day,
       COALESCE(c.campaign_key, p.campaign_key) AS campaign_key,
       COALESCE(c.cost, 0) AS ad_cost,
       COALESCE(p.paid, 0) AS paid
FROM costs AS c
FULL JOIN payment_totals AS p
  ON p.day = c.day AND p.campaign_key = c.campaign_key
ORDER BY 1, 2;

Результат: для кампании 101 расход 1 000 ₽ и оплаты 5 500 ₽; для 102 — 300 ₽ и 0; для 103 — 0 и 700 ₽; для неопределённого источника — 0 и 200 ₽. Общий расход 1 300 ₽, оплаты 6 400 ₽. Эти календарные суммы сами по себе не доказывают, что все оплаты вызваны расходом того же дня.

Метрики без подмены смысла

• CPL: выбранные затраты на привлечение, делённые на число соответствующих лидов. Определите правила дублей и квалификации.
• CAC: затраты на привлечение новых клиентов, делённые на число новых клиентов согласованной группы. Это не цена одной сессии.
• ROAS: атрибутированная выручка / рекламный расход. Оплаты вместо выручки следует подписывать отдельно.
• ROMI: (маржинальный доход, относимый к маркетингу, − маркетинговые затраты) / маркетинговые затраты. Простое вычитание рекламы из выручки не учитывает себестоимость.
• ДРР: рекламный расход / согласованная выручка × 100%.
• LTV: ценность клиента за выбранный или прогнозируемый срок; для сравнения с CAC нужно учитывать маржу и возвраты. Сумма сделок клиента — другой показатель.

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

BI, обновление и обратная загрузка

Power BI, DataLens и Superset могут читать подготовленные витрины PostgreSQL. Настройте права, доступность базы и обновление/кеш для конкретного продукта. Открытие дашборда не гарантирует немедленную загрузку всех свежих данных. Материализованное представление PostgreSQL также требует обновления.

Стоимость решения включает серверы, сопровождение, резервные копии и условия BI. В облачном DataLens одно рабочее место бесплатно, а при двух и более оплачиваются все места по действующим правилам; это не тариф «бесплатно при небольшом объёме данных». См. цены DataLens.

Для передачи офлайн-конверсий в Метрику нужен поддерживаемый идентификатор и цель. В CSV официальная инструкция использует ClientId, UserId, Yclid или PurchaseId, а также Target и DateTime; ценность и валюта передаются отдельно. Время указывается как Unix timestamp. Документация ориентирует на появление данных в отчётах в течение трёх часов. Проверяйте статус обработки и привязку, а не только успешный HTTP. См. передачу офлайн-конверсий.

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

Эксплуатация и приёмка

База для интеграции не должна быть доступна всему интернету без необходимости. Разделяйте роли загрузчика и BI, управляйте секретами и контролируйте диск. Фиксация тега postgres:17 фиксирует основную ветку, но не точный образ: обновления внутри неё продолжаются. Совместимость образа, каталога данных и процедуры major-upgrade проверяют отдельно.

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

До использования отчёта сверяйте расходы с Директом, объекты CRM с порталом и денежные операции с учётом. Отдельно контролируйте неопределённые источники, повторную загрузку, старые исправления, отсутствие страниц API и права доступа. Несовпадение кликов, визитов и лидов не обязательно является ошибкой: сначала сравните их определения и периоды.