Сквозная аналитика без roistat

Сквозная аналитика – это способ связать все точки контакта клиента с бизнесом от первого рекламного клика до продажи. Такой подход позволяет понять, какие рекламные каналы действительно приносят деньги, а какие работают в минус. В этом гайде мы разберём, как настроить сквозную аналитику своими руками (без платных сервисов вроде Roistat) на связке Яндекс Метрика + PostgreSQL + Bitrix24. Будем отслеживать UTM-метки и уникальный идентификатор посетителя (client_id) из Метрики и связывать их с лидами в CRM (Битрикс24). В результате вы сможете ответить на ключевой вопрос: «Какой рекламный источник кормит компанию?» – с цифрами по ROI, CPL и LTV для каждого канала.

1. Обзор задачи: зачем нужна сквозная аналитика и как работает связка client_id → CRM-лид

Зачем это нужно? Без сквозной аналитики маркетолог видит разрозненные данные: сколько кликов и заявок пришло из каждого канала, расходы на рекламу, продажи в CRM и т.д. Но без связи между этими системами сложно понять, конвертируются ли клики в деньги. Сквозная аналитика объединяет данные веб-аналитики, рекламных кабинетов и CRM, показывая полный путь клиента: от первого перехода по рекламе до сделки. Это помогает выявить самые эффективные каналы, оптимизировать бюджет и избавляться от убыточной рекламы. Например, без сквозной аналитики последний касательный канал получает всю заслугу за продажу, хотя на решение могли повлиять несколько касаний (соцсети, поиск, email). Сквозная аналитика показывает вклад каждого канала, позволяя справедливо распределить бюджет и усилия.

Как это работает технически? Ключевое звено – уникальный идентификатор посетителя сайта (client_id), который генерирует Яндекс Метрика для каждого нового пользователя. Метрика сохраняет client_id в cookie _ym_uid в браузере посетителя. Когда посетитель конвертируется (например, отправляет форму заявки), наша задача – передать его client_id (и UTM-метки рекламы) в CRM вместе с данными лида. В CRM (Битрикс24) client_id сохраняется в специальном поле лида. Таким образом, client_id выступает связующим ключом между анонимным поведением на сайте и конкретным клиентом в CRM. Позже, выгрузив из Метрики все визиты с их UTM-метками и client_id, мы можем соединить их в базе данных с лидами из CRM по этому идентификатору. Получится полная картина: какой пользователь с каким рекламным источником в итоге стал лидом, и чем всё закончилось (сделкой, суммой продажи и т.п.).

Пример: клиент пришёл по рекламе Google (utm_source=google) – Метрика присвоила ему client_id 1551977723718998723. Через неделю он снова пришёл напрямую на сайт и оставил заявку, которая сохранилась как лид в Битрикс24 с тем же client_id. Сопоставив данные, видим, что первоначальный источник этого лида – Google, хоть конверсия произошла позже и из другого визита. Так бизнес узнаёт, что Google реклама окупилась, и может инвестировать в неё больше.

2. Получение client_id и UTM-меток из Яндекс Метрики

Чтобы связать лиды с рекламными каналами, нужно выгрузить сырые данные из Яндекс Метрики: список визитов с идентификаторами пользователей и параметрами трафика (UTM-метки, реферальный источник и т.д.). Рассмотрим несколько способов получения этих данных, их плюсы и минусы:

API отчётов (Management & Reporting API): Стандартный API Метрики позволяет делать агрегированные запросы по разным метрикам и группировкам. Однако у него есть ограничения – например, нельзя напрямую получить client_id визитов через обычный API, там просто нет поля для идентификатора пользователя. Максимум – выгружать агрегаты по группировке ClientID в интерфейсных отчётах, но без деталей по каждому визиту. Обычный API также ограничен 10 группировками в запросе. Итог: для нашей задачи (сырые связки client_id ↔ utm) стандартный Reporting API не годится.
Logs API (сырые логи визитов): Специальный API Метрики для выгрузки неагрегированных данных. Logs API – наш главный инструмент, потому что он позволяет получить каждую сессию/визит со всеми параметрами, включая client_id, источники, UTM-метки, время визита, цели, достижения и пр.. По сути, Метрика хранит логи визитов в своей внутренней базе (ClickHouse), и через Logs API отдаёт их в TSV-формате. Это даёт большую свободу анализа: мы можем самостоятельно построить любые отчёты и объединять с другими данными. Недостаток – несколько более сложная процедура выгрузки (нужно создавать задачу, ждать обработки, скачивать частями), но с кодом или готовыми библиотеками это решаемо. Ограничения: обычно Logs API позволяет выгружать данные за последние 6 месяцев (исторические данные старше 6 мес могут быть недоступны таким способом), и есть суточные лимиты на объем выгрузки. Но для большинства задач этого хватает.
Лог-файлы (export CSV): В веб-интерфейсе Метрики есть возможность заказа выгрузки “сырых логов” визитов за период. Это фактически обёртка над Logs API – вы заказываете файл, через некоторое время его можно скачать. Удобно для разовой выгрузки, но вручную и без автоматизации. Мы же будем автоматизировать.
Сторонние ETL-сервисы и парсеры: Если не хотите кодировать самостоятельно, есть сервисы, которые возьмут данные из Метрики и переложат их куда нужно. Например, Renta, OWOX BI, ApiMonster и другие. Они умеют подключаться к Logs API и складывать данные в базы или BigQuery автоматически. Например, Renta может экспортировать логи Метрики напрямую в хранилища вроде Snowflake, BigQuery, S3, ClickHouse, SQL Server. Такие решения экономят время: обычно достаточно подключить счётчик Метрики и целевую базу, выбрать необходимые поля – и данные будут синхронизироваться по расписанию. Минус – это платные сервисы (после тестового периода), и приходится доверять им доступ к аналитике. В нашем гайде мы делаем всё самостоятельно, но помните, что они существуют.
Google BigQuery (если есть данные GA): Вопрос “Google BigQuery (если есть)” из задачи намекает, что у компаний часто данные из Google Analytics (например, GA4) уже экспортируются в BigQuery. Если у вас параллельно велась аналитика в GA и вы выгружали client_id + UTM туда, можно объединять с этими таблицами. Однако Яндекс Метрика напрямую в BigQuery ничего не отправляет (только через сторонние коннекторы вроде OWOX). Мы будем работать именно с данными Метрики, поэтому BigQuery можно использовать либо как альтернативу PostgreSQL для хранения, либо как дополнительный источник (например, сверить данные с GA). В общем, BigQuery – это опционально и уместно, если у вас мульти-аналитика. В рамках нашего решения достаточно PostgreSQL.
Webvisor (Вебвизор): Вебвизор – инструмент Метрики для записи сессий пользователей. Упоминание Webvisor в задаче скорее для полноты вариантов: теоретически, имея идентификатор сессии в Webvisor, можно увидеть цепочку действий. Но практически Вебвизор не предназначен для массового экспорта данных; он даёт качественный взгляд (видеозапись поведения пользователя) и может фильтровать по client_id, но не выгружать тысячами сессий в CSV. Поэтому Webvisor оставим в стороне. Разве что лайфхак: в интерфейсе Метрики можно сегментировать посетителей по clientID и просматривать их визиты и цели (отчёт «Посетители и клиенты»), однако выгрузить всё сразу – опять-таки через Logs API.

Резюме выбора: Используем Logs API Метрики как основной способ получить связку client_id ↔ визиты ↔ UTM. Это даёт максимальную детализацию. Для автоматизации напишем Python-скрипт, благо Yandex предоставляет открытый API. Теперь перейдём к практике получения данных.

2.1 Пример выгрузки данных из Метрики через Logs API (Python)

Чтобы пользоваться API, нужен токен Яндекс.ОAuth с доступом к Метрике (с правами на чтение статистики). Предположим, токен уже получен и ID счётчика Метрики известен. Общий алгоритм работы с Logs API:

Проверка запроса (evaluate): Отправляем запрос на /logrequests/evaluate – указав счётчик, диапазон дат и список полей. Метрика проверит, можно ли выгрузить такой объём данных с выбранными параметрами, и вернёт статус (например, allowed или причину отказа).
Создание выгрузки: Если всё ок, отправляем запрос на создание /logrequests (метод POST) с теми же параметрами. В ответ получаем request_id – идентификатор сформированного набора данных.
Ожидание обработки: Ждём (несколько секунд или минут) пока статус запроса станет processed. Это можно отслеживать GET-запросами к /logrequests (без параметров, только токен) – там будет статус нашего request_id.
Скачивание данных: Когда запрос обработан, появляется возможность скачать части данных. Logs API разбивает выгрузку на части (parts) по \~10 млн строк каждая. Обычно для небольших периодов это одна часть part/0. Скачиваем GET-запросом на /logrequest/<request_id>/part/0/download – в ответ придёт файл (TSV) с нашими колонками.
Повторяем по необходимости: Если данных много и есть part/1, part/2, ... – скачиваем их тоже, изменяя номер части в URL. Затем сливаем части в один набор.

Давайте рассмотрим, как это сделать на Python с помощью библиотеки requests. Ниже приведён пример скрипта, выгружающего визиты за январь 2024 года:

import requests

TOKEN = "YA-XXXXXXXXXXXXXXXXXXXXXXXX"  # OAuth-токен Я.Метрики с правами на статистику
COUNTER_ID = "12345678"               # ID вашего счетчика Метрики
date1 = "2024-01-01"
date2 = "2024-01-31"

# Список полей для выгрузки:
fields = ",".join([
    "ym:s:clientID",        # идентификатор пользователя
    "ym:s:visitID",         # идентификатор визита
    "ym:s:visitDateTime",   # время визита
    "ym:s:lastTrafficSource", # источник трафика (прямой, органика, рекламный и т.д.)
    "ym:s:UTMSource", "ym:s:UTMMedium", "ym:s:UTMCampaign",  # UTM-метки
    "ym:s:UTMContent", "ym:s:UTMTerm",
    "ym:s:goal<goal_id>Reach",  # достижение конкретной цели (подставьте ID цели сайта)
    "ym:s:purchaseRevenue"     # выручка по электронной торговле, если есть
])

# 1. Проверка возможности выгрузки
url_eval = (f"https://api-metrika.yandex.ru/management/v1/counter/{COUNTER_ID}"
            f"/logrequests/evaluate?oauth_token={TOKEN}"
            f"&source=visits&date1={date1}&date2={date2}&fields={fields}")
resp = requests.get(url_eval)
print(resp.json())  # должна вернуться структура с {"log_request_evaluation": {"possible": true, ...}}

# 2. Создание запроса на формирование данных
url_create = (f"https://api-metrika.yandex.ru/management/v1/counter/{COUNTER_ID}"
             f"/logrequests?oauth_token={TOKEN}"
             f"&source=visits&date1={date1}&date2={date2}&fields={fields}")
resp = requests.post(url_create)
data = resp.json()
request_id = data["log_request"]["request_id"]
print(f"Request ID: {request_id}")

# 3. Ожидание готовности (polling статуса каждые пару секунд, максимум пару минут)
status = None
while status not in ("processed", "cancelled", "processing_failed"):
    resp = requests.get(f"https://api-metrika.yandex.ru/management/v1/counter/{COUNTER_ID}/logrequests?oauth_token={TOKEN}")
    log_requests = resp.json().get("logs_request", [])
    # найдём наш request_id
    for lr in log_requests:
        if lr.get("request_id") == request_id:
            status = lr.get("status")
            print(f"Current status: {status}")
    import time; time.sleep(2)

if status != "processed":
    raise RuntimeError(f"Logs API request failed with status {status}")

# 4. Скачивание первой части данных
download_url = (f"https://api-metrika.yandex.ru/management/v1/counter/{COUNTER_ID}"
                f"/logrequest/{request_id}/part/0/download?oauth_token={TOKEN}")
resp = requests.get(download_url)
open("metrika_logs_2024-01.tsv", "wb").write(resp.content)
print("Data downloaded successfully!")

Эта программа последовательно делает все шаги и сохраняет результат в файл metrika_logs_2024-01.tsv. В TSV-файле будут колонки, которые мы запросили (порядок колонок соответствует порядку в параметре fields). Для наглядности можно сперва открыть файл в Excel или любым текстовым редактором и убедиться, что там есть clientID и UTM-метки для каждого визита.

Примечание: Не забудьте, что при использовании Logs API время и дата визитов будут в часовом поясе, указанном в счетчике Метрики. Также, идентификатор clientID выдаётся в виде очень большого числа (UInt64). Например, 1737030877992681689. В нём зашифрована дата первого визита (первые 10 цифр – это Unix Timestamp первого захода). Нам это пригодится при вычислении первого касания, но об этом позже.

2.2 Альтернативы: получение client_id на сайте

Мы пошли путём выгрузки данных постфактум, но стоит упомянуть альтернативный или дополнительный подход: собирать client_id сразу при отправке формы на сайте. Это часто практикуется, чтобы не зависеть от последующей загрузки из Метрики. Суть такая: в момент, когда пользователь заполняет форму заявки или делает целевое действие, выполнить небольшой JavaScript, который вытащит значение client_id из cookie и подставит его в скрытое поле формы. Далее это поле уходит в CRM вместе с остальными данными лида.

Яндекс.Метрика предоставляет для этого метод yaCounterXXXXXX.getClientID() в JS (где XXXXXX – номер вашего счётчика). Однако нужно дождаться инициализации счётчика. Обычно код выглядит так:

<!-- Пример вставки на страницу сайта -->
<script>
window.addEventListener("load", function() {
    try {
        var clientId = yaCounter99999999.getClientID();
        document.getElementById('client_id_input').value = clientId;
        console.log("Yandex Metrika client_id =", clientId);
    } catch(e) {
        console.error("Error getting client_id from Yandex Metrika:", e);
    }
});
</script>

Где client_id_input – hidden-элемент в форме для отправки client_id. Здесь мы используем событие load или DOMContentLoaded, чтобы подождать загрузки Метрики. На практике бывает, что скрипт Метрики не успел загрузиться – тогда можно повторить попытку через небольшой таймаут или использовать колбэк yaCounterXXXXXX.reachGoal (но это сложнее). В сообществе советуют оборачивать вызов в window.onload, что решает проблему в 99% случаев.

Альтернативно, можно вообще не обращаться к объекту счетчика, а прочитать cookie _ym_uid напрямую через document.cookie. Пример функции на чистом JS из Habr Q\&A:

function getYandexClientId() {
    var match = document.cookie.match('(?:^|; )_ym_uid=([^;]*)');
    return match ? decodeURIComponent(match[1]) : null;
}

Этот код вернёт строку client_id, если cookie установлена. Далее её также можно отправить через форму. Такой подход не требует ожидать загрузки счётчика (cookie уже прописана, если Метрика успела отработать).

Зачем это нужно, если мы выгружаем логи? Дело в том, что прямая передача client_id в CRM сразу при заявке даёт несколько плюсов:

В карточке лида менеджер сразу видит client_id и/или источник (UTM) откуда пришёл клиент. Это полезно для ручного анализа, не дожидаясь сводных отчётов.
Если вы планируете использовать сквозную аналитику Яндекс.Метрики, где нужно отправлять данные о лидах обратно в Метрику, то наличие client_id в CRM – обязательное условие. (Метрика по client_id связывает офлайн-конверсию с онлайн-сессией).
Бывают случаи, когда не все обращения можно отследить через сайт (например, звонки, чаты). Если у вас настроен колтрекинг или чат-бот, они тоже могут передавать client_id в CRM при создании лида (многие calltracking-системы и онлайн-чаты умеют работать с clientID через интеграции). Поэтому единый подход – везде пробрасывать client_id – повышает полноту данных.

В нашем решении можно комбинировать: и выгружать сырые данные из Метрики для массового анализа, и собирать client_id+UTM в CRM для надёжности. Эти данные должны совпадать; если где-то не совпадут – сигнал ошибки в сборе.

Получение UTM-меток: По аналогии, UTM-параметры (utm_source, utm_medium, utm_campaign и т.д.) тоже можно сохранить через форму. Как минимум utm_source полезно записать сразу, чтобы быстренько понимать источник лида. Обычно делают так: при заходе на сайт скрипт сохраняет текущие UTM в cookie (или sessionStorage), затем при отправке формы подтягивает их и отправляет. Если этого не сделать, не беда – Logs API даёт UTM для каждого визита. Мы сможем соединить по client_id и узнать, с каким utm_source был первый визит пользователя.

Итак, мы получили данные из Метрики: либо из логов, либо сразу в CRM, либо комбинированно. Теперь пора складывать всё в базу для дальнейшей обработки.

3. Как передать данные в PostgreSQL

Использование базы данных для хранения и обработки сырых данных – ключевое решение в нашем подходе «без Roistat». PostgreSQL – отличный выбор в силу своей надёжности и возможностей SQL-аналитики. План такой: создаём несколько таблиц в базе, куда будем складывать данные из Метрики и из CRM, а затем с помощью SQL джойним их, рассчитываем метрики и строим витрины для визуализации.

3.1 Структура таблиц и индексы

Предлагаем такую структуру данных в PostgreSQL:

Таблица yandex_visits – хранит визиты/сессии из Метрики. Основные поля:
- client_id (BIGINT) – идентификатор пользователя (из _ym_uid). Индексируем его, т.к. по нему будем связывать с лидами.
- visit_id (BIGINT) – идентификатор визита (уникален в рамках счетчика Метрики, можно взять ym\:s\:visitID).
- visit_datetime (TIMESTAMP) – дата и время визита.
- traffic_source (TEXT) – источник трафика (например, «organic», «direct», «ad»).
- utm_source, utm_medium, utm_campaign, utm_content, utm_term (TEXT) – UTM-метки.
- goal_<id> (INT или BOOL) – достигнута ли конкретная цель, если выгружали цели.
- ecommerce_revenue (NUMERIC) – сумма покупки, если настроена электронная коммерция.
Пример создания таблицы на SQL:
```
CREATE TABLE yandex_visits (
  client_id       BIGINT,
  visit_id        BIGINT PRIMARY KEY,
  visit_datetime  TIMESTAMP,
  traffic_source  TEXT,
  utm_source      TEXT,
  utm_medium      TEXT,
  utm_campaign    TEXT,
  utm_content     TEXT,
  utm_term        TEXT,
  goal_form_send  BOOLEAN,
  purchase_revenue NUMERIC
);
CREATE INDEX idx_yandex_visits_client ON yandex_visits(client_id);
```
Примечание: В качестве PRIMARY KEY можно использовать (visit_id) или составной (client_id, visit_id). Визитов много, поэтому Primary Key лучше для уникальности и чтобы исключить дубликаты при повторных загрузках.
Таблица crm_leads – хранит лиды/сделки из CRM (Битрикс24). Основные поля:
- lead_id (INT, PRIMARY KEY) – уникальный ID лида в CRM.
- client_id (BIGINT) – тот самый client_id, полученный из Метрики и сохранённый в карточке лида (пользовательское поле CRM).
- lead_date (TIMESTAMP) – дата создания лида.
- utm_source, utm_medium, … (TEXT) – можно дублировать UTM-метки, если передавали их в CRM (не обязательно, но полезно для проверки).
- deal_amount (NUMERIC) – сумма сделки/продажи, если лид конвертировался в продажу (можно обновлять, когда сделка закрывается).
- status (TEXT) – этап или статус (лид, квалифицирован, в работе, успешная сделка и т.д.).
Пример создания:
```
CREATE TABLE crm_leads (
  lead_id       SERIAL PRIMARY KEY,
  client_id     BIGINT,
  lead_date     TIMESTAMP,
  utm_source    TEXT,
  utm_medium    TEXT,
  utm_campaign  TEXT,
  deal_amount   NUMERIC,
  status        TEXT
);
CREATE INDEX idx_crm_leads_client ON crm_leads(client_id);
```
Здесь client_id может повторяться (например, один и тот же клиент оставил 2 разные заявки – будет 2 лида с одинаковым client_id). Индекс всё равно нужен для связи с визитами.

Читай также: Использование BI-отчетов в бизнесе: примеры и возможности

Конечно, вы можете расширить схему под свои задачи. Например, отдельная таблица для рекламных расходов по дням (ad_costs с полями date, source, cost) – чтобы считать ROI внутри базы. Или таблица deals для успешных продаж. Но в рамках базового сценария достаточно визитов и лидов.

3.2 ETL: загрузка данных в PostgreSQL

Из Метрики в PostgreSQL. После выгрузки Logs API у нас есть TSV-файлы (или CSV). Их надо залить в таблицу yandex_visits. Самый быстрый способ – воспользоваться COPY. Вот пример скрипта на Python, использующего psycopg2 для копирования напрямую из файла:

import psycopg2
conn = psycopg2.connect(dbname="analytics", user="postgres", password="secret", host="localhost")
cur = conn.cursor()
with open("metrika_logs_2024-01.tsv", "r", encoding="utf-8") as f:
    # Пропустим заголовок (если Logs API вернул его) – проверим первую строку
    header = f.readline()
    # Выполним COPY из файла, указываем формат CSV, разделитель таб
    cur.copy_expert("COPY yandex_visits FROM STDIN WITH (FORMAT CSV, DELIMITER '\t')", f)
conn.commit()
cur.close()
conn.close()

Функция copy_expert позволяет задать STDIN (наш файл) и скопировать данные построчно очень быстро (гораздо быстрее, чем выполнять тысячи INSERT). Предполагается, что столбцы в файле идут в том же порядке, что и колонки таблицы. Поэтому либо при выгрузке из Logs API указывайте поля в нужном порядке, либо перечислите их явно в COPY-запросе: COPY yandex_visits(client_id, visit_id, visit_datetime, ...) FROM STDIN ....

Альтернативный метод – использовать pandas. Например:

import pandas as pd
from sqlalchemy import create_engine
df = pd.read_csv("metrika_logs_2024-01.tsv", sep="\t")
engine = create_engine("postgresql://postgres:secret@localhost/analytics")
df.to_sql("yandex_visits", engine, if_exists="append", index=False)

Pandas сам позаботится о создании INSERT-ов. Но для больших файлов этот способ медленнее, чем COPY. Он удобен, если нужно попутно трансформировать данные (например, конвертировать формат даты).

Из CRM в PostgreSQL. Как получить данные из Bitrix24? Есть несколько способов:

Экспорт CSV из самой CRM (вручную через интерфейс) – подходит разово, но не для постоянной интеграции.
Использование REST API Bitrix24 – можно программно получать список лидов, сделок и пр. Bitrix24 имеет методы crm.lead.list, crm.deal.list и т.д., которые возвращают JSON. Их можно вызывать через вебхуки или авторизованный API. В Python удобно использовать библиотеку requests или готовые обёртки. Пример запроса: GET https://<Ваш портал>.bitrix24.ru/rest/<user_id>/<webhook_token>/crm.lead.list?filter[>ID]=0&select[]=ID&select[]=UF_CRM_CLIENT_ID&select[]=DATE_CREATE&select[]=STATUS&select[]=OPPORTUNITY... Здесь UF_CRM_CLIENT_ID – код пользовательского поля для client_id (о создании этого поля – в следующем разделе).
Вебхуки при создании лида: Bitrix24 позволяет настроить входящий вебхук на событие нового лида. То есть, при каждом новом лиде CRM сама уведомит ваш скрипт (напр. через POST-запрос) с данными. Тогда можно сразу писать их в базу. Это продвинутый путь, требующий поднять веб-сервер или использовать серверless функции.
Промежуточное хранение: Если у вас сайт на своем сервере, можно при отправке заявки вызывать два действия: 1) записать лид в Битрикс через API; 2) сразу записать те же данные в свою PostgreSQL (например, с помощью простого REST-сервиса). Это снижает зависимость от Bitrix24 API (не нужно потом выгружать отдельно).

Для простоты можно начать с периодического выгружения. Допустим, раз в сутки дергать crm.lead.list и дозагружать новые лиды в таблицу. Bitrix API поддерживает постраничный вывод, придётся учитывать это (получать по 50/100 записей).

ETL-пример для лидов (псевдокод):

# Допустим, мы получили список лидов из Bitrix24 в виде списка словарей lead_data_list
lead_data_list = [
   {"ID": "101", "UF_CRM_CLIENT_ID": "1737030877992681689", "DATE_CREATE": "2024-01-15T12:34:56", "STATUS_ID": "NEW", "OPPORTUNITY": "0"},
   {"ID": "102", "UF_CRM_CLIENT_ID": "1551977723718998723", "DATE_CREATE": "2024-01-16T09:10:11", "STATUS_ID": "CONVERTED", "OPPORTUNITY": "50000"},
   # ... и т.д.
]
# Преобразуем и вставляем
conn = psycopg2.connect(...)
cur = conn.cursor()
for lead in lead_data_list:
    cur.execute(
      "INSERT INTO crm_leads(lead_id, client_id, lead_date, status, deal_amount) VALUES (%s, %s, %s, %s, %s) "
      "ON CONFLICT (lead_id) DO UPDATE SET status=EXCLUDED.status, deal_amount=EXCLUDED.deal_amount",
      (
         int(lead["ID"]),
         int(lead["UF_CRM_CLIENT_ID"]) if lead.get("UF_CRM_CLIENT_ID") else None,
         lead["DATE_CREATE"],
         lead["STATUS_ID"],
         Decimal(lead["OPPORTUNITY"] or 0)
      )
    )
conn.commit()
cur.close()

Здесь мы используем ON CONFLICT по lead_id, чтобы при повторном запуске обновлять изменения (например, статус лида поменялся на сконвертированный, и появилась сумма сделки).

🎓 Совет: Настройте в таблице crm_leads индексацию по дате (lead_date) и продумайте, как будете обновлять данные: например, хранить флажок is_converted или отдельную таблицу сделок, чтобы не терять данные. Для простоты мы обновляем запись лида суммой сделки, но можно делать нормализацию (лиды отдельно, продажи отдельно). Все зависит от потребностей отчётности.

Теперь у нас в PostgreSQL есть две таблицы: визиты (с UTM) и лиды (с client_id и результатами). Переходим к самому интересному – как их связать и что с этим делать.

4. Связка client_id с лидами из Битрикс24

Как уже упоминалось, client_id – наш общий ключ. Чтобы эта связка работала, нужно позаботиться о двух вещах на стороне CRM:

Создать поле для хранения client_id – в Битрикс24 это делается через настройку полей CRM. Например, создаём у сущности «Лид» пользовательское текстовое поле Client ID (или сразу называем его metrika_client_id). Тип – строка (вмещающая 20 символов минимум, т.к. client_id \~ 18-19 цифр). Это поле будет заполняться из формы или через API при создании лида.
Передавать client_id при каждом создании лида – как мы обсуждали: либо формой с сайта, либо если лид создаётся менеджером, нужно иметь механизм (в случае звонка – коллтрекинг, в случае чата – интеграция чат-системы), который добавит client_id. Иначе для обращений не с сайта данные потеряются. Но если большая часть лидов – с сайта через форму, достаточно там реализовать передачу.

В Bitrix24 у пользовательских полей есть внутренние коды, обычно начинающиеся с UF_CRM_.... Их можно узнать в настройках CRM (например, в URL редактирования поля видно код). Допустим, код нашего поля – UF_CRM_CLIENT_ID. Тогда при создании лида через REST API мы добавляем его как FIELDS[UF_CRM_CLIENT_ID] = <значение>.

Если вы используете CRM-формы Битрикс24 на сайте, то вопрос передачи решается чуть иначе: в настройках формы можно включить автоподстановку UTM-меток и метрик. Но про client_id Я.Метрики по умолчанию Bitrix24 не знает. Можно добавить скрытое поле в форму CRM и с помощью JS, аналогичного примеру выше, заполнять его перед отправкой. Bitrix24 формы позволяют вставлять свой HTML и JS.

Хранить UTM-метки в CRM или нет? Bitrix24 уже умеет определять некоторые источники (например, помечает лид, если он с определённой формы или рекламной кампании Bitrix). Но лучше надёжно записать основные UTM тоже. Мы можем создать поля UTM Source, UTM Campaign и т.п. и так же передавать их. Многие компании так делают для быстрого обзора. Тем не менее, имея client_id, можно достать UTM из Метрики, поэтому это опционально. Дублирование в CRM просто даёт удобство.

Способы передачи данных в Bitrix24:

CRM Webhook/API: Ваш сайт при отправке формы вызывает вебхук Bitrix24 crm.lead.add с JSON-параметрами лида. Это делается серверным кодом (PHP, Python, etc.), либо AJAX-запросом с фронта (но тогда нужно скрыть webhook-ключ).
Заполнение формы CRM: Если форма создана в Bitrix24 и вставлена на сайт (через скрипт или айфрейм), то можно настроить hidden-поле для client_id и заполнение через JS (как описано). После отправки, лид создастся сразу с нужным полем.
Интеграции коллтрекинга/чатов: Упоминалось, если используется коллтрекинг (Calltouch, Ringostat, Comagic и др.) – они обычно могут передавать данные звонков в CRM. Там, как правило, свой идентификатор сессии, но некоторые могут сохранять и client_id веб-аналитики (например, Calltouch при интеграции с сайтом может вытащить client_id Я.Метрики). Тут зависит от возможностей сервиса.
Через промежуточное хранение: Самый гибкий вариант – ваш бекенд сам принимает обращения (с сайта, с телефонии, с чата) и сначала пишет в свою БД, а потом пушит в CRM. Тогда точно ничего не потеряется и можно кастомно обогащать данные. Но это, конечно, трудозатратно реализовать.

Итог: На момент, когда в CRM появляется новый лид, у него в карточке есть поле Client ID (например, 1737030877992681689) и, допустим, поле Источник = «Google Ads». Менеджер может не видеть этих цифр (поле можно скрыть), но для нас они – мостик к Метрике. Когда сделка закрыта, мы можем взять сумму продажи и связать её с initial client_id.

В следующем разделе рассмотрим, как соединить таблицы yandex_visits и crm_leads по client_id и вычислить интересующие метрики – первый/последний контакт, ROI, CPL, LTV и т.д.

5. Сведение данных в PostgreSQL: объединение и атрибуция

Теперь – самое главное: анализ полученных данных. В PostgreSQL мы можем писать SQL-запросы, которые:

Соединяют визиты с лидами по client_id.
Находят первый канал привлечения клиента (First Click) и сравнивают с каналом конверсии (Last Click).
Считают агрегированные показатели по каналам: сколько визитов, сколько лидов, конверсия, сумма продаж, расходы (если добавить) и т.п.
Рассчитывают метрики эффективности: CPL (Cost Per Lead, стоимость лида), ROI (Return on Investment, возврат инвестиций), LTV (Lifetime Value, пожизненная ценность клиента) и др.

Соединение данных (JOIN): Базовый запрос для связи лидов с информацией о визите может выглядеть так:

SELECT 
    l.lead_id,
    l.lead_date,
    l.deal_amount,
    v.utm_source,
    v.utm_medium,
    v.utm_campaign,
    v.visit_datetime as visit_date
FROM crm_leads l
LEFT JOIN yandex_visits v 
    ON l.client_id = v.client_id
    AND v.visit_datetime <= l.lead_date
;

Здесь мы берём все визиты пользователя до момента создания лида. Такой JOIN потенциально выдаст несколько строк для одного лида (если у клиента было много визитов до отправки заявки). Нам может понадобиться выделить один из них – в зависимости от модели атрибуции:

First click (первый контакт): Нас интересует самый первый визит этого пользователя (самая ранняя дата). Значит, для каждого лида из объединённых строк выберем визит с минимальным visit_datetime. Это можно сделать подзапросом или функцией ранжирования (ROW_NUMBER).
Last click (последний существенный контакт): Можно взять последний визит непосредственно перед созданием лида (например, максимальный visit_datetime меньший или равный lead_date).
Last non-direct click (последний непрямой): Атрибуция “последний значимый источник” – исключаем прямые заходы, если были, и берём последний рекламный/поисковый визит.
Мультиканальная (U-Shape, Time Decay и пр.): Это сложнее – нужно несколько записей и доли конверсии, выходит за рамки нашего гайда 😉.

В задаче явно указано показать, как вычленить «первый контакт» и сделать атрибуцию по модели first-click. То есть, мы будем приписывать конверсию тому источнику, через который клиент впервые узнал о нас.

Найдём первый визит клиента: Помним, что client_id «зашит» в себя дату первого посещения (в Unix-времени). Но можно пойти простым путём: взять минимальную дату визита из таблицы yandex_visits по каждому client_id. Например, создадим временную таблицу или представление:

CREATE VIEW first_visit AS
SELECT client_id,
       MIN(visit_datetime) as first_visit_date
FROM yandex_visits
GROUP BY client_id;

И параллельно можно определить, какой был источник у этого первого визита. Например, если хотим utm_source первого касания:

SELECT v.client_id, v.utm_source as first_utm_source
FROM yandex_visits v
JOIN first_visit f 
  ON v.client_id = f.client_id 
  AND v.visit_datetime = f.first_visit_date;

Это даст таблицу (client_id, first_utm_source). Аналогично можно вытянуть first_utm_medium, first_campaign и т.д. или сразу весь ряд визита через JOIN с собой.

Теперь связываем с лидами:

SELECT 
    l.lead_id,
    l.lead_date,
    fv.first_utm_source,
    fv.first_utm_medium,
    l.deal_amount
FROM crm_leads l
LEFT JOIN (
    SELECT v.client_id,
           v.utm_source as first_utm_source,
           v.utm_medium as first_utm_medium,
           v.utm_campaign as first_utm_campaign
    FROM yandex_visits v
    JOIN (
        SELECT client_id, MIN(visit_datetime) as first_date
        FROM yandex_visits
        GROUP BY client_id
    ) sub ON v.client_id = sub.client_id AND v.visit_datetime = sub.first_date
) fv ON l.client_id = fv.client_id;

Получаем для каждого лида поля, указывающие, откуда пришёл этот клиент в первом касании. Если у какого-то лида first_utm_source пустой – возможно, не найден визит (например, если client_id не был записан или пользователь пришёл без UTM). В идеале такого не должно быть, но в реальности бывает, поэтому используем LEFT JOIN, чтобы лид вовсе не потерять.

Атрибуция first-click: Теперь можем агрегировать данные по first_utm_source. Например, посчитаем, сколько лидов и на какую сумму принёс каждый канал первоначального касания:

SELECT 
    fv.first_utm_source as channel,
    COUNT(DISTINCT l.lead_id) as leads_count,
    SUM(l.deal_amount) as total_revenue,
    AVG(l.deal_amount) as avg_revenue_per_lead
FROM crm_leads l
JOIN first_visit_info fv ON l.client_id = fv.client_id
GROUP BY fv.first_utm_source
ORDER BY total_revenue DESC;

(Здесь first_visit_info – можно поместить подзапрос как в предыдущем примере, для удобства предположим, что это представление с first_utm_source).

Этот запрос покажет, например: канал «google» – 30 лидов, суммарная выручка 500k, средняя 16.7k; канал «yandex» – 20 лидов, выручка 300k, ср.15k; и т.д. Таким образом, какой канал привёл больше денег изначально. Это и есть ответ на вопрос «какой канал кормит компанию» в разрезе первого касания.

Конечно, можно сделать аналогичный отчёт и по последнему касанию, заменив логику на MAX(visit_date) <= lead_date. Или вовсе пойти дальше: некоторые лиды могут иметь несколько продаж (LTV), тогда first-click канал всё равно ценен, но нужно учитывать повторные покупки.

ROI, CPL, LTV расчет:

CPL (Cost per Lead) – если у нас есть данные о расходах на канал. Допустим, у нас есть таблица ad_costs с колонками date, utm_source, cost. Мы можем агрегировать расходы за период по каналу и разделить на число лидов. Пример:
```
SELECT 
  source,
  SUM(cost) as cost_sum,
  leads_count,
  (SUM(cost) / NULLIF(leads_count,0))::NUMERIC(10,2) as CPL
FROM ad_costs c
JOIN (
  SELECT fv.first_utm_source as source, COUNT(*) as leads_count
  FROM crm_leads l JOIN first_visit_info fv ON l.client_id = fv.client_id
  WHERE l.lead_date BETWEEN '2024-01-01' AND '2024-01-31'
  GROUP BY fv.first_utm_source
) q ON c.utm_source = q.source AND c.date BETWEEN '2024-01-01' AND '2024-01-31'
GROUP BY source, leads_count;
```
Здесь NULLIF(leads_count,0) чтобы не делить на ноль. CPL покажет стоимость одного лида по источнику.
ROI (Return on Investment) – обычно рассчитывается как (Revenue - Cost) / Cost * 100%. Если ROI > 0% (или >100%, в другой интерпретации), канал окупается. В нашем примере:
```
SELECT 
  source,
  SUM(revenue) as revenue,
  SUM(cost) as cost,
  (CASE WHEN SUM(cost) = 0 THEN NULL 
        ELSE (SUM(revenue) - SUM(cost)) / SUM(cost) * 100 END) as ROI_percent
FROM (
  -- предположим, есть таблица сделок или мы берем суммы из leads
  SELECT fv.first_utm_source as source, COALESCE(l.deal_amount,0) as revenue
  FROM crm_leads l JOIN first_visit_info fv ON l.client_id = fv.client_id
  WHERE l.status = 'WON'  -- успешные сделки
) rev
JOIN ad_costs c ON c.utm_source = rev.source  -- нужно соотнести расходы
GROUP BY source;
```
Проще, можно сначала свести revenue по source (как мы делали), и отдельно cost по source, а потом свести вместе. Смысл в том, что ROI показывает окупаемость: например, ROI = 150% означает +50% прибыли на вложенный рубль (полтора рубля возвращается). ROI ниже 100% (или <0% если в процентах как разница) означает, что канал убыточен. Важно: Нужно договориться, считаем ROI именно как (доход/расход100%) или (прибыль/расход100%). Разные источники трактуют по-разному. В любом случае, мы можем показать эту метрику.
LTV (Lifetime Value) – ценность клиента за всё время. Для её расчёта нужно суммировать все сделки по одному клиенту. У нас в crm_leads каждая заявка – но, возможно, одна и та же клиентID может покупать несколько раз (будет несколько лидов с одним client_id, или одна сделка, потом повторная). Простой подход: сгруппировать crm_leads по client_id, суммировать deal_amount – это и будет LTV для данного клиента. Затем LTV по каналу = среднее или сумма LTV всех клиентов, пришедших с этого канала. Чаще считают среднее. Пример:
```
SELECT fv.first_utm_source as channel, AVG(customer_value) as avg_LTV
FROM (
  SELECT client_id, SUM(deal_amount) as customer_value
  FROM crm_leads
  WHERE status = 'WON'
  GROUP BY client_id
) cust
JOIN first_visit_info fv ON cust.client_id = fv.client_id
GROUP BY fv.first_utm_source;
```
Это даст средний LTV клиентов по каналам. Если хотим понимать совокупную ценность, можно и SUM, но средний показатель чаще используется для сравнений (например, с CPA или CAC).

Все эти вычисления можно материализовать в виде представлений или таблиц агрегатов, чтобы легко строить дашборд. Например, итоговая таблица marketing_performance с колонками: channel, leads, cost, revenue, cpl, roi_percent, avg_ltv. Наполнять её можно ежедневным SQL-запросом.

6. Визуализация результатов

Данные ради данных мало полезны – нужно показать их понятным и наглядным языком для руководства и коллег. Рассмотрим варианты визуализации в Power BI и Apache Superset, и какие графики стоит построить.

Подключение к BI:

Power BI: позволяет подключаться к PostgreSQL напрямую (через ODBC или специальный коннектор PostgreSQL). Вы можете либо написать в Power Query нужные SQL запросы, либо подгрузить таблицы и делать меры (measures) средствами DAX. В нашем случае, проще может быть сделать view marketing_performance и подтянуть его в Power BI как источник данных.
Superset: это open-source BI платформа, которая тоже умеет коннектиться к PostgreSQL. Там можно через UI составлять диаграммы, или писать SQL прямо в Explore. Superset отлично подходит, если вы хотите развернуть дашборд внутри компании без лицензий.

Читай также: Основы Power BI для начинающих

Примеры метрик на дашборде:

Количество лидов по каналам. Бар-чарт: по оси X – канал (utm_source, или источник трафика агрегировано: Директ, Google Ads, VK, SEO и т.п.), по Y – число лидов. Можно включить сегментацию: успешные сделки другим цветом, отвалившиеся – другим.
ROI по каналам. Для каждого канала – показатель ROI%. Это можно отобразить столбчатой диаграммой или таблицей с условным форматированием (зеленым выделять >100%, красным <100%). Или даже ранжировать каналы по ROI – посмотреть, где эффективность выше.
CPL по каналам. Например, линейчатый график или ранжированный столбик, где видно, сколько стоит лид из каждого источника. Чем ниже, тем лучше. Можно нанести среднюю линию CPL.
LTV и ROI вместе. Интересно показать, что некоторые каналы могут быть дорогими (высокий CPL), но дают высокий LTV. Например, контекстная реклама дорогая, но приводит клиентов, которые приносят много повторных покупок. Тут подойдет bubble chart (рассеянная диаграмма): по оси X – CPL, по Y – LTV, размер пузыря – количество лидов, цвет – ROI или канал. Так сразу видно, какие каналы в каком квадранте (дорого/дешево и низкий/высокий LTV).
Воронка от визита до продажи. Можно построить в Superset Sankey diagram или funnel: Источники → Лиды → Продажи. Или несколько KPI-карточек: общий трафик, лиды (конверсия %), сделки (конверсия %).
Динамика по времени. Графики по месяцам: например, ROI по месяцам для каждого канала (линии), чтобы видеть тренд окупаемости рекламы. Или столбики – расход vs выручка помесячно по каналам.
ТОП кампаний или ключевых фраз. Если заглянуть глубже, можно сделать вкладку дашборда с детализацией: разбивка внутри канала (например, для Google Ads – какие кампании или ключевые слова дали лучшие показатели ROI). Это поможет оптимизировать не только каналы, но и кампании.

В Power BI можно также сделать дашборд интерактивным: фильтры по периодам, по сегментам клиентов, и т.п. Superset тоже поддерживает фильтры и дашбордные панели.

Вот пример простого графика – сравнение ROI по источникам первого касания (условные данные):

Пример: ROI по каналам первого касания. На графике серой пунктирной линией отмечен уровень окупаемости 100% (выше – канал окупается, ниже – убыточен). Видно, например, что Email и SEO имеют высокий ROI (приводят значительную прибыль с минимальными затратами), а вот Facebook Ads – лишь \~60% (вложили больше, чем заработали). Yandex Direct тоже ниже 100%, что повод присмотреться к кампаниям, а Google Ads даёт 150% – т.е. на каждый рубль вложений 1.5 рубля выручки (ROI = +50% чистой прибыли). Такие визуализации наглядно отвечают на вопрос: какие каналы эффективны, а какие – «сливают» бюджет.

В Superset дашборд может выглядеть, например, так: таблица каналов с колонками Лиды, Конверсия в продажи, Расходы, Выручка, CPL, ROI, LTV. Зеленым подсвечиваются окупаемые каналы, красным – убыточные. Рядом – график «Цена лида (CPL) по каналам», чтобы сразу видеть, где лид самый дорогой. Ниже – график LTV по каналам, показывающий, с каких каналов приходят более ценные клиенты. Можно добавить фильтр по сегменту времени (например, последний квартал или конкретная кампания).

Для руководства часто делают сводный индикатор: сколько всего потрачено, сколько заработано, общий ROI. А ниже – диаграмма вклада каналов: например, pie chart по доле выручки от каждого источника и по доле затрат. Сравнивая два пирога (затраты vs доходы), легко увидеть, где не оправдывается инвестиция.

Помните, цель визуализации – упростить принятие решений. Хороший дашборд сразу укажет, куда нажать: отключить нерентабельный канал, увеличить бюджет на эффективный, проверить почему в этом месяце упал ROI у конкретной кампании и т.д. Можно даже настроить алерты: например, если ROI какого-то канала падает ниже 50% или CPL превышает заданный предел, система отправит уведомление.

7. Рекомендации, кейсы и вопросы-ответы

Напоследок – несколько советов, примеров и ответов на частые вопросы, которые помогут успешно внедрить сквозную аналитику своими силами.

7.1 Полезные рекомендации

Начните с малого: Если пока нет ресурсов сразу тянуть все данные автоматически, можно начать с ручной выгрузки за последний месяц и анализа в Excel или той же Metabase. Получив первые инсайты (например, узнав, что канал X даёт отрицательный ROI, а канал Y – сверхприбыльный), вы убедитесь в ценности сквозной аналитики для бизнеса. Это поможет обосновать время/бюджет на полноценную автоматизацию.
Следите за качеством данных: Сквозная аналитика чувствительна к корректности меток и идентификаторов. Проверьте, что все рекламные ссылки промечены UTM-метками – без пропусков. Иначе трафик попадёт в «неопределено». Также убедитесь, что на сайте один счётчик Метрики, иначе может быть несколько client_id на одного человека.
Обрабатывайте дубликаты и пустые значения: Бывает, один и тот же client_id имеет несколько лидов (повторные обращения) – решите, как это учитывать: считать это одним клиентом или несколькими? Обычно LTV решает это, но на этапе лидов можно фильтровать повторные лиды, если нужно. Пустой client_id у лида – тревожный знак: либо скрипт не сработал, либо лид пришёл не с сайта (например, добавлен вручную менеджером). Такие лиды не забудьте учесть отдельно (можно сегмент «Без источника»).
Используйте возможности Метрики: В будущем, возможно, вы захотите отправлять офлайн-конверсии обратно в Метрику (через Yandex Metrica API для загрузки CRM-данных). Это даст вам красивые отчёты прямо в интерфейсе Метрики: источники продаж из CRM, ROI по кампаниям и т.д. Но для этого, повторимся, необходимо правильно передавать client_id и на стороне Метрики создать соответствующую интеграцию по API. Наш подход с PostgreSQL можно сочетать с этим: база – для гибкости и детальных кастомных отчётов, а Метрика – для быстрого доступа и некоторой автоматизации (например, сегменты в Метрике по группам клиентов).
Безопасность и GDPR: Не храните персональные данные клиентов в аналитической базе без необходимости. client_id – анонимный идентификатор, тут проблем нет. Но если вы, например, будете привязывать email или телефон к client_id, убедитесь, что база защищена и соответствуете закону о данных. В EU, возможно, нельзя так просто связывать веб-ид с персональными данными без определённых оснований. В РФ – тоже стоит соблюдать ФЗ-152.
Мониторинг и поддержка: Настройте регулярную проверку: например, раз в день скрипт выгружает вчерашние визиты из Метрики и обновляет базу. И раз в день тянет новые лиды из CRM. Можно настроить логирование успеха/неуспеха, чтобы вы получали уведомление, если вдруг данные не обновились (например, истёк токен API или изменилось поле). Такая операционная поддержка убережёт от ситуации, когда отчёт устарел на неделю, а никто не заметил.
Сравнение с другими системами: Если раньше вы использовали, скажем, Google Analytics, сравните цифры конверсий. Они могут отличаться на несколько процентов из-за разных моделей атрибуции и срезов (Метрика могла отфильтровать какой-то трафик как бот, GA нет, и т.д.). Важно убедиться, что расхождения в пределах разумного. Если нет, ищите ошибку: возможно, не все лиды передаются, или где-то client_id теряется.
Кейсы успешного внедрения: Например, у одного из наших проектов (интернет-магазин) после внедрения сквозной аналитики выяснилось, что 30% продаж приносит SEO, хотя на него тратили всего 10% бюджета. В то же время, часть расходов на таргетинг в соцсетях вообще не конвертилась в продажи. Перераспределив бюджет, через пару месяцев ROI всего маркетинга вырос с \~120% до 160%. Другой кейс – сервис услуг: благодаря отслеживанию client_id узнали, что многие клиенты сначала приходят по недорогим каналам (контент-маркетинг), но конвертируются только после ремаркетинга. Это помогло убедить руководство продолжать инвестировать в контент, хоть он и не давал прямых лидов – он подогревал аудиторию для конверсии позже через ретаргет.
Сравнение с Roistat и ко: Да, существуют готовые решения (Roistat, СквизАналитикс, OWOX BI и т.д.). Их плюс – быстрое развёртывание, много интеграций из коробки, красивые отчёты сразу. Минус – стоимость (обычно немалая ежемесячная плата) и ограниченная гибкость (что дали, то и смотри, сильно кастомить трудно). Наш DIY-подход требует вложиться временем разработчика, зато экономит бюджет и даёт полный контроль над данными. Как показала практика, для среднего бизнеса с парой каналов и CRM вполне реально настроить сквозную аналитику самостоятельно и сократить стоимость лида на 20–30% благодаря оптимизации рекламы.

7.2 Частые вопросы (FAQ)

Q: Что делать, если у лида не проставился client_id? A: Такое случается. Например, пользователь позвонил по телефону, минуя сайт – у него не было client_id (если только коллтрекинг не подставил). Или на сайте сбой скрипта. В CRM такой лид будет без идентификатора, и мы его не свяжем с визитом. Решение: учитывайте таких лидов отдельно. Можно завести источник «Неопределено» и относить их туда. А лучше – внедрять omnichannel-трекинг: для звонков использовать коллтрекинг с интеграцией (который по номеру звонка может определить рекламный источник), для чатов – интеграцию, передающую UTM. Полностью избежать «осиротевших» лидов сложно, но надо минимизировать. В отчётах можно выводить процент лидов без источника – он должен стремиться к нулю.

Q: Можно ли вместо client_id использовать что-то ещё, например, UserID? A: Если у вас на сайте есть собственная авторизация (пользователи логинятся), можно присваивать им UserID и передавать его в Метрику (в Я.Метрике есть параметр UserID, а также в GA). Тогда вы могли бы линковать данные по реальным людям даже при смене устройств. Но это более сложная схема и подходит, когда у вас, скажем, личный кабинет у каждого клиента. Для большинства маркетинговых задач проще использовать анонимный client_id. Другой вариант – использовать параметр yclid (Яндекс.Директ click ID) или gclid (Google Ads), которые передаются в URL клика. Их тоже можно сохранять в CRM и потом сопоставлять с рекламными кликами. Однако yclid/gclid привязаны к конкретному клику рекламы, а не ко всем визитам пользователя. Они полезны для отслеживания конверсий конкретно в рекламных системах. Для общей картины лучше client_id. В общем, client_id – самый универсальный и простой способ.

Q: Как быть, если один клиент (один номер телефона) заходил с разных устройств (разные client_id)? A: Такое обязательно будет: человек с работы зашёл (client_id1), потом дома с телефона (client_id2) – а в CRM он один, с одним телефоном. В нашей схеме они считались бы двумя разными посетителями до момента лидов, а в CRM сольются в одного клиента (если менеджер объединил или распознал дубликат). Здесь вступает уровень сквозной аналитики 2.0: объединение по контактным данным. Можно, например, после выгрузки данных пытаться мёрджить записи по телефону или email (если есть в визитах – можно же и на сайте спросить почту для подписки, привязать…). Но это очень непросто и обычно требует отдельного инструментария. В рамках first-click атрибуции мы этим не заморачиваемся – считаем каждого client_id уникальным посетителем. Учтите лишь, что реальных людей может быть чуть меньше. Если важно, чтобы LTV был точнее, то нужно агрегировать по контактам. Bitrix24, кстати, умеет связывать лиды с контактом/компанией, и можно выгружать аналитику на уровень контактов. Это уже тонкости – в базовой реализации не учитываем.

Q: Сколько хранятся данные в Метрике? A: В агрегированном виде – вечно, пока счётчик живёт. А вот сырые логи (Logs API) доступны за последние 6 месяцев (точнее 180 дней). Поэтому, если вы хотите иметь исторические данные, нужно регулярно выгружать и хранить их у себя. Мы рекомендуем настроить ежедневную выгрузку и складирование в Postgres или файлы. Тогда у вас будет своя “история”. Если же нужно проанализировать период старше 6 мес, а выгрузок не делали – к сожалению, через API не получить. Отчасти это компенсируется тем, что CRM-то хранит все лиды с начала времён, так что продажи вы знаете, а вот через какие визиты давние клиенты пришли – уже нет данных. Планируйте внедрение заранее 😊.

Q: Какие метрики точно показывают «какой канал кормит компанию»? A: В конечном счёте – доход (выручка) и прибыль от канала. Маркетологи смотрят на ROI как на главный индикатор эффективности канала. Также ROMI (Return on Marketing Investment) – это по сути тот же ROI, просто часто считается в %. DRR (доля рекламных расходов) – обратный показатель, доля затрат в выручке, тоже полезно. Но для простоты:

Если ROI% высокий, канал прибыльный. Если ниже 100% – убыточный (не окупает вложения). Например, ROI 300% – отлично, ROI 50% – плохо, надо либо оптимизировать, либо выключать канал.
CPL – важно для оценки эффективности в воронке: можно сравнить со средней ценой лида или с CPL других каналов. Но сам по себе CPL не говорит о качестве лидов. Канал с CPL 1000 руб может давать продажи на 100k, а канал с CPL 100 руб – лиды, которые ничего не купят. Поэтому CPL всегда смотрим в паре с конверсией в продажу или с LTV.
LTV – показывает ценность аудитории. Например, с SEO пришли клиенты со средним LTV в 3 раза больше, чем с контекстной рекламы – вывод: органика приводит более “качественных” клиентов, их можно по-другому стимулировать. Но LTV нужно довольно много времени, чтобы накопился (особенно если у вас повторные покупки нечасто).
Конверсия (CR) – из визита в лид, из лида в сделку. Это базовые метрики. Канал с низкой конверсией из трафика в лиды может иметь высокий ROI, если каждый лид крупный. Но чаще низкая конверсия требует внимания – возможно, нецелевой трафик. Сквозная аналитика позволяет это видеть.

Q: Можно ли сделать всё то же самое, но в облаке и без своих серверов? A: Да, можно воспользоваться сочетанием облачных решений: например, вместо PostgreSQL – Google BigQuery (хранилище данных), вместо Python-скриптов – Google Cloud Functions или Яндекс Облако функции, вместо Power BI – Google Data Studio (Looker Studio) или Яндекс DataLens. Принцип тот же: собираем данные, складываем, объединяем, визуализируем. Выбирайте инструменты, исходя из бюджета и удобства. Наш пример показал концепцию, не привязанную жёстко к технологиям: можно заменить PostgreSQL на ClickHouse, или Python-скрипт на Apache NiFi – результат (связанные данные) будет достигнут.

Q: Сколько времени займет внедрение? A: Зависит от вашей подготовки. Если вы знакомы с Python и SQL – то первые данные можно получить в течение 1-2 дней (например, выгрузить месяц и сделать пару графиков). Настройка полной автоматизации – от 1 до 4 недель: учесть все формы, убедиться, что новые лиды с client_id пишутся, написать скрипты обновления, протестировать. Но после этого вы получаете систему без абонентской платы, которая приносит пользу постоянно. Так что игра стоит свеч!

Надеемся, этот гайд помог вам разобраться, как настроить сквозную аналитику без Roistat, используя мощь Яндекс Метрики и открытые инструменты. Немного дружбы с кодом – и вы буквально своими руками соберёте систему, которая покажет правду о ваших рекламных каналах. Больше никаких догадок, куда утекает бюджет: цифры всё расскажут. Успехов в внедрении и пусть ваши ROI будут космическими!

Источники и ссылки:

Яндекс.Метрика – Документация Logs API (сырые данные) , пример кода getClientID
Osipenkov Blog – “Настройка сквозной аналитики в Я.Метрике для Битрикс24”, “Отчет ‘Посетители и клиенты’ через Logs API”
Pro Metriki – “Как получить данные из Logs API Метрики” (шаги выгрузки)
NoBoring Finance – “Что такое сквозная аналитика” (примеры ROI, советы)
Roistat блог – материалы по сквозной аналитике и метрикам маркетинга.