Сквозная аналитика без Roistat: считаем ROI по каналам на чистом SQL

Ты построил атрибуцию и знаешь, какой канал привёл клиента. Отлично. Но CMO приходит с вопросом, от которого нельзя отмахнуться: «Мы потратили 2 миллиона на рекламу. Сколько вернулось?»

Атрибуция без расходов — это полкартины. Ты знаешь, что Яндекс.Директ привёл 40% клиентов, но не знаешь, обошлось это в 200 тысяч или в 2 миллиона. А без выручки по сделкам — даже CPA не посчитаешь.

Сквозная аналитика — это когда расходы, клики, лиды, сделки и выручка живут в одной таблице и ты видишь ROI по каждому каналу и кампании. В этой статье собираем её с нуля на SQL, без Roistat.

Зачем платить 30k/мес, если можно SELECT

В одной компании 8 месяцев платили за Roistat 40k/мес. Когда я спросил, какие отчёты реально смотрят — оказалось, один: «ROI по каналам за месяц». Один SELECT. Финдиректор, когда узнал цену этого SELECT’а, сделал лицо, которое я запомнил надолго.

Roistat делает 4 вещи:

1. Собирает расходы из рекламных кабинетов (API Директа, Google Ads, VK)
2. Склеивает визиты с лидами и сделками (через client_id, roistat_visit)
3. Атрибутирует конверсии каналам
4. Строит отчёт ROI/ROAS по каналам и кампаниям

Всё это можно повторить на SQL. Разница — в скорости старта и стоимости поддержки:

Архитектура сквозной аналитики: 5 слоёв от клика до рубля

Сквозная аналитика — это не один волшебный запрос. Это пайплайн из 5 слоёв данных, каждый из которых отвечает за свой кусок реальности.

Слой 1 — Рекламные расходы

Откуда: API Яндекс.Директ, VK Ads API, Google Ads API (или ручной CSV-экспорт). Зачем: без расходов ROI не посчитаешь — это числитель формулы.

Слой 2 — Визиты и UTM

Откуда: Яндекс.Метрика (Logs API) или GA4 (BigQuery export). Зачем: UTM-метки связывают клик по рекламе с визитом на сайте. Подробнее о выборе между Метрикой и GA4. Если у вас ecommerce — данные о покупках уже можно собирать в Метрике.

Слой 3 — Лиды

Откуда: CRM (amoCRM, Битрикс24, RetailCRM, или свой). Зачем: лиды — это мост между анонимным визитом и живым клиентом. Тут происходит главная склейка: client_id → лид.

Слой 4 — Сделки и выручка

Откуда: тот же CRM. Зачем: сделки — это деньги. Статус (won/lost), сумма, дата закрытия. Без этого слоя у вас CPA, но не ROI.

Слой 5 — Склейка

Тут всё сходится: расходы + визиты + лиды + сделки = ROI по каналам. Один JOIN, который связывает клик в рекламном кабинете с рублём на счёте.

Схема данных: 4 SQL-таблицы для сквозной аналитики

Никакой магии. Плоские таблицы, понятные поля. Адаптируй под свою CRM — структура остаётся.

-- Расходы: ежедневные, по кампаниям
CREATE TABLE ad_costs (
    dt          TEXT,       -- дата (YYYY-MM-DD)
    source      TEXT,       -- yandex_direct, vk_ads, google_ads
    medium      TEXT,       -- cpc, cpm, social_paid
    campaign    TEXT,       -- название кампании
    channel     TEXT,       -- нормализованный канал (paid_search, paid_social, paid_display)
    cost        REAL,       -- расход в рублях (НДС включён, валюта сконвертирована)
    impressions INT,
    clicks      INT
);

-- Визиты: каждый визит с UTM
CREATE TABLE visits (
    visit_id    INT,
    client_id   TEXT,       -- _ym_uid из Метрики или GA client_id
    visit_dt    TEXT,
    utm_source  TEXT,
    utm_medium  TEXT,
    utm_campaign TEXT,
    channel     TEXT        -- нормализованный канал
);

-- Лиды: заявки из CRM
CREATE TABLE leads (
    lead_id     INT,
    client_id   TEXT,       -- прокинутый из визита через hidden field
    created_dt  TEXT,
    lead_type   TEXT,       -- form, callback, chat
    phone       TEXT,
    email       TEXT
);

-- Сделки: закрытые и в работе
CREATE TABLE deals (
    deal_id     INT,
    lead_id     INT,        -- связь с лидом
    status      TEXT,       -- won, lost, in_progress
    revenue     REAL,       -- сумма сделки
    closed_dt   TEXT,
    product     TEXT
);

Обрати внимание на данные:

• uid_001: пришёл из Яндекс.Директ, вернулся из органики, потом Direct → лид → сделка 180k
• uid_003: пришёл из Google Ads, вернулся из органики → лид → сделка 210k
• uid_004: два визита из VK Ads → лид → сделка проиграна. VK потратил деньги, но не заработал
• uid_006: лид без визита (позвонил по телефону с рекламы) — нет client_id в visits

Шаг 1 — Собираем расходы из рекламных кабинетов

Это самый нудный, но обязательный шаг. Без расходов ROI не существует — это просто буква R.

Агрегируем расходы по каналам для дальнейшего JOIN’а с выручкой:

-- Агрегация расходов по каналам за период
SELECT
    channel,
    SUM(cost)        AS total_cost,
    SUM(clicks)      AS total_clicks,
    SUM(impressions)  AS total_impressions,
    ROUND(SUM(cost) / NULLIF(SUM(clicks), 0), 2) AS avg_cpc
FROM ad_costs
WHERE dt BETWEEN '2026-02-01' AND '2026-02-28'
GROUP BY channel
ORDER BY total_cost DESC;

-- Агрегация расходов по каналам за период
SELECT
    channel,
    sum(cost)        AS total_cost,
    sum(clicks)      AS total_clicks,
    sum(impressions)  AS total_impressions,
    round(sum(cost) / nullIf(sum(clicks), 0), 2) AS avg_cpc
FROM ad_costs
WHERE dt BETWEEN '2026-02-01' AND '2026-02-28'
GROUP BY channel
ORDER BY total_cost DESC;

Google Ads отдаёт расходы в долларах. Яндекс — в рублях с НДС. VK — в рублях без НДС. Если у вас все три — добро пожаловать в валютный ад. Решение: конвертация и НДС при загрузке, в ad_costs всё хранится в рублях с НДС.

Шаг 2 — Связываем визиты с лидами

Это самый болезненный шаг. Здесь ломается 80% самописных систем. Визит — анонимный (client_id из cookie). Лид — живой человек (email, телефон). Как их связать?

Техника hidden field

Самый надёжный способ — прокинуть client_id из веб-аналитики в CRM через скрытое поле формы:

1. JavaScript на сайте достаёт _ym_uid из cookie Метрики (или _ga для GA4)
2. Записывает значение в hidden input формы заявки
3. Форма отправляется в CRM — лид уже содержит client_id
4. В SQL: JOIN visits ON visits.client_id = leads.client_id

Fallback: матчинг по email и телефону

Не у всех лидов есть client_id (звонки, оффлайн, кривые формы). Fallback — матчинг по email или телефону из посещённых визитов. Работает хуже, но лучше, чем ничего.

На одном проекте CRM-менеджеры вручную вбивали email. Через месяц я обнаружил: 12% email’ов содержат опечатки, 3% — вымышленные (test@test.com, asdf@asdf.ru), а один креативный менеджер записал «клиент отказался» прямо в поле email. Матчинг сломался так красиво, что отладка заняла два дня.

Реальный match rate: 70-85%. Не 100%. Никогда. Звонки без client_id, заблокированные cookie, мобайл → десктоп — всё это рвёт связку. 85% — отличный результат. 70% — рабочий. Ниже 60% — пересмотрите сбор данных.

-- Связываем визиты с лидами:
-- 1) по client_id (точный матч)
-- 2) fallback по email (если client_id нет)
WITH attributed_visits AS (
    SELECT
        v.client_id,
        v.visit_dt,
        v.channel,
        v.utm_campaign,
        ROW_NUMBER() OVER (
            PARTITION BY v.client_id
            ORDER BY
                CASE
                    WHEN v.channel IN ('paid_search','paid_social','paid_display') THEN 1
                    WHEN v.channel IN ('organic_search','smm_organic') THEN 2
                    ELSE 3
                END,
                v.visit_dt DESC
        ) AS rn
    FROM visits v
    WHERE v.client_id IS NOT NULL
)
SELECT
    l.lead_id,
    l.client_id,
    l.created_dt,
    av.channel     AS attributed_channel,
    av.utm_campaign AS attributed_campaign
FROM leads l
LEFT JOIN attributed_visits av
    ON av.client_id = l.client_id AND av.rn = 1;

-- Связываем визиты с лидами:
-- 1) по client_id (точный матч)
-- 2) fallback по email (если client_id нет)
WITH attributed_visits AS (
    SELECT
        v.client_id,
        v.visit_dt,
        v.channel,
        v.utm_campaign,
        ROW_NUMBER() OVER (
            PARTITION BY v.client_id
            ORDER BY
                multiIf(
                    v.channel IN ('paid_search','paid_social','paid_display'), 1,
                    v.channel IN ('organic_search','smm_organic'), 2,
                    3
                ),
                v.visit_dt DESC
        ) AS rn
    FROM visits v
    WHERE v.client_id != ''
)
SELECT
    l.lead_id,
    l.client_id,
    l.created_dt,
    av.channel     AS attributed_channel,
    av.utm_campaign AS attributed_campaign
FROM leads l
LEFT JOIN attributed_visits av
    ON av.client_id = l.client_id AND av.rn = 1;

Обрати внимание: внутри ROW_NUMBER — LPC-атрибуция с приоритетами: платные каналы побеждают бесплатные, даже если бесплатный визит был позже. Это тот же CASE WHEN channel IN (...) THEN 1/2/3, что мы разбирали в статье про атрибуцию, только встроенный в JOIN.

Шаг 3 — Прокидываем атрибуцию до сделки

Теперь у каждого лида есть атрибутированный канал. Осталось протянуть эту цепочку до сделки с выручкой:

-- Полная цепочка: визит → лид → сделка
WITH attributed_leads AS (
    SELECT
        l.lead_id,
        l.client_id,
        COALESCE(
            -- Приоритет 1: LPC по client_id
            (SELECT v.channel FROM visits v
             WHERE v.client_id = l.client_id
             ORDER BY
                CASE
                    WHEN v.channel IN ('paid_search','paid_social','paid_display') THEN 1
                    WHEN v.channel IN ('organic_search','smm_organic') THEN 2
                    ELSE 3
                END,
                v.visit_dt DESC
             LIMIT 1),
            -- Приоритет 2: нет визитов → direct
            'direct'
        ) AS channel
    FROM leads l
)
SELECT
    al.channel,
    COUNT(DISTINCT d.deal_id)                     AS deals,
    COUNT(DISTINCT CASE WHEN d.status = 'won'
          THEN d.deal_id END)                      AS won_deals,
    COALESCE(SUM(CASE WHEN d.status = 'won'
          THEN d.revenue END), 0)                  AS revenue
FROM attributed_leads al
LEFT JOIN deals d ON d.lead_id = al.lead_id
GROUP BY al.channel
ORDER BY revenue DESC;

-- Полная цепочка: визит → лид → сделка
WITH attributed_leads AS (
    SELECT
        l.lead_id,
        l.client_id,
        ifNull(
            (SELECT v.channel FROM visits v
             WHERE v.client_id = l.client_id
             ORDER BY
                multiIf(
                    v.channel IN ('paid_search','paid_social','paid_display'), 1,
                    v.channel IN ('organic_search','smm_organic'), 2,
                    3
                ),
                v.visit_dt DESC
             LIMIT 1),
            'direct'
        ) AS channel
    FROM leads l
)
SELECT
    al.channel,
    countDistinct(d.deal_id)                       AS deals,
    countDistinct(if(d.status = 'won', d.deal_id, NULL)) AS won_deals,
    coalesce(sumIf(d.revenue, d.status = 'won'), 0) AS revenue
FROM attributed_leads al
LEFT JOIN deals d ON d.lead_id = al.lead_id
GROUP BY al.channel
ORDER BY revenue DESC;

Результат:

Paid_search забрал 4 выигранные сделки (uid_001, uid_002, uid_003, uid_005 — LPC атрибутировал их к платному поиску, потому что у каждого был визит из Директа или Google Ads). Paid_social (VK Ads) — одна проигранная сделка, 0 выручки. Direct — лид без визитов (uid_006).

Шаг 4 — Считаем ROI и ROAS по каналам

Вот он — финальный запрос. Ради этого всё затевалось.

Формулы:

• ROI = (Revenue − Cost) / Cost × 100% — сколько процентов заработали/потеряли на каждый вложенный рубль
• ROAS = Revenue / Cost — сколько рублей выручки на рубль расходов
• CPA = Cost / Won deals — стоимость привлечения клиента
• CPL = Cost / Leads — стоимость лида

-- Финальный отчёт: ROI по каналам
WITH attributed_leads AS (
    SELECT l.lead_id, l.client_id,
        COALESCE(
            (SELECT v.channel FROM visits v
             WHERE v.client_id = l.client_id
             ORDER BY
                CASE WHEN v.channel IN ('paid_search','paid_social','paid_display') THEN 1
                     WHEN v.channel IN ('organic_search','smm_organic') THEN 2
                     ELSE 3 END,
                v.visit_dt DESC
             LIMIT 1),
            'direct'
        ) AS channel
    FROM leads l
),
channel_revenue AS (
    SELECT
        al.channel,
        COUNT(DISTINCT al.lead_id)                              AS leads,
        COUNT(DISTINCT CASE WHEN d.status = 'won'
              THEN d.deal_id END)                                AS won_deals,
        COALESCE(SUM(CASE WHEN d.status = 'won'
              THEN d.revenue END), 0)                            AS revenue
    FROM attributed_leads al
    LEFT JOIN deals d ON d.lead_id = al.lead_id
    GROUP BY al.channel
),
channel_costs AS (
    SELECT channel, SUM(cost) AS total_cost, SUM(clicks) AS total_clicks
    FROM ad_costs
    GROUP BY channel
)
SELECT
    COALESCE(cr.channel, cc.channel)               AS channel,
    COALESCE(cc.total_cost, 0)                     AS cost,
    cc.total_clicks                                 AS clicks,
    cr.leads,
    cr.won_deals,
    cr.revenue,
    ROUND(COALESCE(cc.total_cost, 0)
        / NULLIF(cr.leads, 0), 0)                  AS cpl,
    ROUND(COALESCE(cc.total_cost, 0)
        / NULLIF(cr.won_deals, 0), 0)              AS cpa,
    ROUND(cr.revenue::NUMERIC
        / NULLIF(cc.total_cost, 0), 2)             AS roas,
    ROUND((cr.revenue - COALESCE(cc.total_cost, 0))
        / NULLIF(cc.total_cost, 0) * 100, 1)       AS roi_pct
FROM channel_revenue cr
FULL OUTER JOIN channel_costs cc ON cc.channel = cr.channel
ORDER BY cr.revenue DESC NULLS LAST;

-- Финальный отчёт: ROI по каналам
WITH attributed_leads AS (
    SELECT l.lead_id, l.client_id,
        ifNull(
            (SELECT v.channel FROM visits v
             WHERE v.client_id = l.client_id
             ORDER BY
                multiIf(
                    v.channel IN ('paid_search','paid_social','paid_display'), 1,
                    v.channel IN ('organic_search','smm_organic'), 2,
                    3),
                v.visit_dt DESC
             LIMIT 1),
            'direct'
        ) AS channel
    FROM leads l
),
channel_revenue AS (
    SELECT
        al.channel,
        countDistinct(al.lead_id)                               AS leads,
        countDistinct(if(d.status = 'won', d.deal_id, NULL))    AS won_deals,
        coalesce(sumIf(d.revenue, d.status = 'won'), 0)         AS revenue
    FROM attributed_leads al
    LEFT JOIN deals d ON d.lead_id = al.lead_id
    GROUP BY al.channel
),
channel_costs AS (
    SELECT channel, sum(cost) AS total_cost, sum(clicks) AS total_clicks
    FROM ad_costs
    GROUP BY channel
)
SELECT
    coalesce(cr.channel, cc.channel)                AS channel,
    coalesce(cc.total_cost, 0)                      AS cost,
    cc.total_clicks                                  AS clicks,
    cr.leads,
    cr.won_deals,
    cr.revenue,
    round(coalesce(cc.total_cost, 0)
        / nullIf(cr.leads, 0))                      AS cpl,
    round(coalesce(cc.total_cost, 0)
        / nullIf(cr.won_deals, 0))                  AS cpa,
    round(cr.revenue
        / nullIf(cc.total_cost, 0), 2)              AS roas,
    round((cr.revenue - coalesce(cc.total_cost, 0))
        / nullIf(cc.total_cost, 0) * 100, 1)        AS roi_pct
FROM channel_revenue cr
FULL OUTER JOIN channel_costs cc ON cc.channel = cr.channel
ORDER BY cr.revenue DESC;

Итоговый результат — ради него всё затевалось:

Что мы видим:

• Paid search (Яндекс.Директ + Google Ads) — ROAS 3.79, ROI +279%. Каждый вложенный рубль вернул почти 4. Работает и работает отлично.
• Paid social (VK Ads) — потратили 115k, получили 0 выручки. ROI −100%. Единственная сделка — проиграна.
• Paid display — 12k расходов, 0 лидов. Возможно, работает на awareness (First Click это покажет), но по LPC — дыра в бюджете.

ROI без атрибуции — число, которое врёт в удобную сторону. uid_002 пришёл из VK Ads, но позже вернулся через Яндекс.Директ. Last Click засчитал бы конверсию в Direct (последний визит). LPC — в paid_search (последний платный). А без атрибуции вы бы спорили, чей это клиент, до вечера.

Атрибуция отвечает «кто привёл?», воронки — «где теряем?», а сквозная аналитика — «сколько это стоит?». Три инструмента, которые работают вместе. Для сравнения периодов используйте оконные функции — LAG и SUM OVER покажут динамику ROI по месяцам.

Дашборд для CMO: куда вывести ROI-отчёт

SQL-таблица — это хорошо для аналитика. Для CMO нужен дашборд. Три варианта:

1. Yandex DataLens — бесплатен, подключается к PostgreSQL и ClickHouse напрямую. Для российского стека — оптимальный выбор. Подробнее — в гайде по DataLens.
2. Metabase — open source, self-hosted. Красивее DataLens, сложнее в настройке.
3. Google Looker Studio — бесплатен, но нужен BigQuery или Sheets как источник.

Грабли и пограничные случаи

Можно написать идеальный SQL, но реальность найдёт, как его сломать. Вот грабли, на которые я наступал — и которые наступят на вас:

Roistat vs самописная: когда строить, когда покупать

Я не скажу, что самописная аналитика «лучше Roistat». Она гибче, дешевле и прозрачнее. Но требует аналитика, который понимает SQL и не уволится через три месяца. Если такого нет — Roistat честнее, чем самописный Excel-монстр, который никто не может поддерживать.

Ты собрал сквозную аналитику на SQL. Расходы, визиты, лиды, сделки — всё в одном месте. ROI по каналам считается, VK Ads разоблачён, Яндекс.Директ оправдан.

Следующий шаг — автоматизировать обновление: cron-скрипт для загрузки расходов, инкрементальная загрузка визитов, webhook из CRM. Но это уже инженерная задача, а не аналитическая. Подпишись на Telegram-канал, чтобы не пропустить.

FAQ

Сколько стоит собрать сквозную аналитику самому vs Roistat?

Roistat — от 30k ₽/мес + настройка (разово 30-100k). Самописная: VPS 1-3k ₽/мес + время аналитика (2-4 недели на запуск, потом 2-4 часа/неделю на поддержку). Break-even через 2-3 месяца. Но если аналитик уходит — вы остаётесь с SQL, который некому поддерживать. Учитывайте этот риск.

Какие данные нужны для минимально рабочей сквозной аналитики?

Минимум: расходы по кампаниям (CSV-экспорт из кабинетов) + визиты с UTM (Метрика Logs API) + сделки из CRM с суммой и статусом. Даже без полноценной CRM-интеграции: расходы + достижение целей в Метрике уже дают CPA по каналам. Не идеально, но лучше, чем ничего.

Как часто нужно обновлять данные?

Ежедневно — стандарт. Расходы из API приходят с лагом 1-2 дня. Статусы сделок в CRM меняются (in_progress → won/lost) — обновляйте. Хороший паттерн: ежедневный инкрементальный апдейт + еженедельный full refresh. Real-time — overkill для 99% бизнесов.

Что делать, если CRM не отдаёт данные по API?

CSV-экспорт раз в неделю → скрипт загрузки в PostgreSQL. Большинство CRM (Битрикс24, amoCRM) имеют API, но если ваша — не из их числа, еженедельный CSV вполне рабочий вариант. Не позволяйте идеальному быть врагом работающего.

Подходит ли этот подход для мобильных приложений?

Веб-часть (визиты → лиды → сделки) работает. Мобильная атрибуция — другой мир: там AppsFlyer, Adjust, SKAdNetwork, и данные приходят в совершенно ином формате. Объединение web + app требует единого user_id и отдельного пайплайна. Совет: стабилизируйте веб-часть, потом добавляйте мобайл.