Как посчитать ROI в SQL

Зачем это нужно аналитику

ROI — универсальная метрика «стоит ли вкладываться». Маркетингу важно, окупаются ли каналы. Продукту — окупился ли трёхмесячный релиз. CFO — сколько возвращает аналитический стек за 10 000 $ в год. Все эти вопросы — ROI в разных одеждах.

Главное не путать его с ROAS. ROAS = Revenue / Spend — это «сколько рубля выручки приносит рубль рекламы». ROI учитывает маржу и все расходы:

ROI = (Profit − Investment) / Investment × 100%

ROAS 3 при марже 25% даёт прибыль 0,75 ₽ на 1 ₽ расхода — то есть ROI отрицательный. Правило быстрой проверки:

breakeven ROAS = 1 / margin

При 25% марже break-even ROAS равен 4 — всё ниже убыточно.

Дальше — готовые SQL-шаблоны под частые задачи: ROI кампании и канала, feature ROI, ROI c учётом LTV, сравнение ROI и ROAS, ROI бизнес-решения.

1. ROI маркетинговой кампании

WITH campaign_revenue AS (
    SELECT
        o.attribution_campaign      AS campaign,
        SUM(o.total) * 0.25         AS gross_profit   -- 25% gross margin
    FROM orders o
    WHERE o.attribution_campaign = 'spring_sale'
      AND o.status               = 'paid'
      AND o.created_at >= '2026-04-01'
    GROUP BY 1
),
campaign_spend AS (
    SELECT
        campaign,
        SUM(spend) AS total_spend
    FROM ad_spend
    WHERE campaign = 'spring_sale'
      AND day >= '2026-04-01'
    GROUP BY 1
)
SELECT
    cr.campaign,
    cs.total_spend                                                        AS investment,
    cr.gross_profit                                                       AS profit,
    cr.gross_profit - cs.total_spend                                      AS net_profit,
    100.0 * (cr.gross_profit - cs.total_spend) / NULLIF(cs.total_spend, 0) AS roi_pct
FROM campaign_revenue cr
JOIN campaign_spend   cs USING (campaign);

Коэффициент маржи 0,25 нужно заменить на реальный. Лучше вынести его в CTE или параметр dbt-модели, чтобы маржа не была «магическим числом» в теле запроса.

2. ROI по каналам

Собираем выручку и спенд отдельно, потом сопоставляем:

WITH revenue_by_channel AS (
    SELECT
        u.attribution_channel AS channel,
        SUM(o.total) * 0.25   AS gross_profit
    FROM orders o
    JOIN users  u ON u.user_id = o.user_id
    WHERE o.status = 'paid'
      AND o.created_at >= '2026-01-01'
    GROUP BY 1
),
spend_by_channel AS (
    SELECT
        channel,
        SUM(spend) AS total_spend
    FROM ad_spend
    WHERE day >= '2026-01-01'
    GROUP BY 1
)
SELECT
    COALESCE(r.channel, s.channel)                                  AS channel,
    s.total_spend                                                   AS spend,
    r.gross_profit                                                  AS profit,
    100.0 * (r.gross_profit - s.total_spend)
          / NULLIF(s.total_spend, 0)                                AS roi_pct
FROM revenue_by_channel r
FULL OUTER JOIN spend_by_channel s USING (channel)
WHERE s.total_spend > 0
ORDER BY roi_pct DESC;

FULL OUTER JOIN + COALESCE позволяют не потерять каналы, у которых есть спенд, но нет выручки (это как раз самые интересные для разбора).

3. ROI по кампаниям с MoM-динамикой

WITH monthly AS (
    SELECT
        DATE_TRUNC('month', day)::DATE AS month,
        campaign,
        SUM(spend)                     AS spend
    FROM ad_spend
    GROUP BY 1, 2
),
monthly_rev AS (
    SELECT
        DATE_TRUNC('month', created_at)::DATE AS month,
        attribution_campaign                   AS campaign,
        SUM(total) * 0.25                      AS profit
    FROM orders
    WHERE status = 'paid'
    GROUP BY 1, 2
)
SELECT
    m.month,
    m.campaign,
    m.spend,
    mr.profit,
    100.0 * (mr.profit - m.spend) / NULLIF(m.spend, 0) AS roi_pct
FROM monthly m
LEFT JOIN monthly_rev mr USING (month, campaign)
ORDER BY m.month, roi_pct DESC;

4. ROI с учётом LTV

Краткосрочный ROI одного месяца может быть отрицательным, а канал всё равно окупается за счёт долгой жизни клиента. Учитываем LTV:

WITH cohort_ltv AS (
    SELECT
        u.attribution_channel                              AS channel,
        DATE_TRUNC('month', u.registered_at)::DATE          AS cohort_month,
        AVG(user_rev.lifetime_revenue * 0.25)               AS avg_profit_per_user
    FROM users u
    LEFT JOIN (
        SELECT user_id, SUM(total) AS lifetime_revenue
        FROM orders
        WHERE status = 'paid'
        GROUP BY user_id
    ) user_rev ON user_rev.user_id = u.user_id
    GROUP BY 1, 2
),
users_by_month AS (
    SELECT
        attribution_channel                    AS channel,
        DATE_TRUNC('month', registered_at)::DATE AS cohort_month,
        COUNT(*)                               AS acquired
    FROM users
    GROUP BY 1, 2
),
spend_by_month AS (
    SELECT
        channel,
        DATE_TRUNC('month', day)::DATE AS cohort_month,
        SUM(spend)                      AS spend
    FROM ad_spend
    GROUP BY 1, 2
)
SELECT
    cl.cohort_month,
    cl.channel,
    ua.acquired,
    cl.avg_profit_per_user * ua.acquired         AS cohort_profit,
    sm.spend                                     AS investment,
    100.0 * (cl.avg_profit_per_user * ua.acquired - sm.spend)
          / NULLIF(sm.spend, 0)                  AS ltv_roi_pct
FROM cohort_ltv       cl
JOIN users_by_month   ua USING (channel, cohort_month)
JOIN spend_by_month   sm USING (channel, cohort_month);

Это всё ещё observational-модель: она не различает, сколько дохода принёс бы канал без рекламы. Для настоящей оценки «incremental ROI» нужен geo- или holdout-эксперимент.

5. Feature ROI

Оценка окупаемости продуктовой фичи. Здесь осторожно: это не причинно-следственный эффект, а приближение на тех пользователях, которые реально использовали фичу.

WITH feature_users AS (
    SELECT DISTINCT user_id
    FROM feature_usage
    WHERE feature_name = 'new_checkout'
),
feature_profit AS (
    SELECT SUM(o.total) * 0.25 AS profit
    FROM orders o
    JOIN feature_users fu USING (user_id)
    WHERE o.status = 'paid'
      AND o.created_at >= '2026-04-01'
),
feature_investment AS (
    SELECT 40 * 2500 * 2.5 AS dev_cost   -- 40 часов × 2500 ₽/час × 2.5 месяца
)
SELECT
    fp.profit,
    fi.dev_cost                                                  AS investment,
    100.0 * (fp.profit - fi.dev_cost) / NULLIF(fi.dev_cost, 0)  AS feature_roi_pct
FROM feature_profit     fp,
     feature_investment fi;

Чтобы получить честный incremental-эффект, нужен A/B-тест: показывать фичу не всем, а случайной половине и сравнивать метрики.

6. ROI vs ROAS рядом

SELECT
    campaign,
    SUM(revenue)                                                       AS revenue,
    SUM(spend)                                                         AS spend,
    SUM(revenue) / NULLIF(SUM(spend), 0)                               AS roas,
    100.0 * (SUM(revenue) * 0.25 - SUM(spend)) / NULLIF(SUM(spend), 0) AS roi_pct
FROM campaign_stats
GROUP BY campaign
ORDER BY roi_pct DESC;

Две цифры рядом быстро показывают, где ROAS формально зелёный, а ROI — уже красный.

7. ROI бизнес-решения

Пример: стоит ли нанять Customer Success-менеджера для enterprise-сегмента. Моделируем ожидаемое снижение churn и считаем возврат:

WITH assumptions AS (
    SELECT
        150        AS enterprise_customers,
        0.05       AS current_monthly_churn,
        0.03       AS expected_monthly_churn,
        50000      AS avg_mrr_per_customer,
        1500000    AS csm_annual_cost
)
SELECT
    enterprise_customers * avg_mrr_per_customer
        * (current_monthly_churn - expected_monthly_churn) * 12       AS annual_saved_revenue,
    csm_annual_cost                                                   AS investment,
    100.0 * (enterprise_customers * avg_mrr_per_customer
             * (current_monthly_churn - expected_monthly_churn) * 12
             - csm_annual_cost)
          / NULLIF(csm_annual_cost, 0)                                AS roi_pct
FROM assumptions;

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

Частые ошибки

1. Путать ROI и ROAS

ROAS работает с выручкой, ROI — с прибылью. При одной и той же ROAS одна команда может быть в плюсе, другая — в нуле, третья — в убытке. Всегда держите в голове маржу.

2. Считать без LTV

Краткосрочный ROI кампании может быть отрицательным, а канал стоит того, если приводит лояльных платящих на долгий срок. Всегда сверяйте с LTV.

3. Присваивать всю выручку рекламе

Часть пользователей купили бы и без рекламы. «Ад-хок» ROI почти всегда завышен — честную оценку даёт incrementality-тест.

4. Игнорировать накладные расходы

Кроме прямых расходов есть зарплаты команды, инструменты, комиссии платёжных систем. Fully-loaded ROI — ближе к правде.

5. Магические числа в SQL

0.25 в теле запроса — это «где-то в документах есть такая цифра». Выносите margin, курсы валют и ставки в отдельные таблицы или параметры dbt — меньше шанс ошибиться и проще обновлять.

Связанные темы

• Что такое ROI простыми словами
• ROAS vs ROI
• Как посчитать CAC в SQL
• Кейс: ROAS упал

FAQ

ROI в процентах или в долях?

Чаще всего в процентах. 0,5 = 50% — одно и то же число.

Margin неизвестен, что считать?

Без маржи корректнее говорить о ROAS. Если нужно именно ROI — уточняйте маржу у финансовой команды, она обычно есть хотя бы в среднем по категории.

Когда ROI отрицательный — закрывать канал?

Не всегда. Если вы смотрите только первый месяц, а LTV-окупаемость на горизонте 12 месяцев — канал ещё может выйти в плюс. Сверяйтесь с long-term-метриками.

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

Сложно: эффект от бренда размывается во времени и каналы взаимодействия с ним трудно атрибутировать. Обычно ROI считают для performance-кампаний, а бренд оценивают по awareness / searches / direct-трафику.