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

Зачем Marketing ROI

ROAS показывает revenue per ad spend. Marketing ROI — учитывает marketing cost (creative, agency, не только media). И учитывает margin. ROI 50% = on $1 spend, profit $1.50.

Формула

Marketing ROI = (Revenue Generated × Gross Margin - Marketing Cost) / Marketing Cost × 100%

ROMI (Return on Marketing Investment) = (Marketing Profit - Marketing Cost) / Marketing Cost

Базовый расчёт

WITH attribution AS (
    SELECT
        channel,
        SUM(amount) AS attributed_revenue
    FROM conversions
    GROUP BY channel
),
spend AS (
    SELECT
        channel,
        SUM(spend) AS total_spend
    FROM marketing_spend
    WHERE month >= CURRENT_DATE - INTERVAL '90 days'
    GROUP BY channel
)
SELECT
    a.channel,
    a.attributed_revenue,
    s.total_spend,
    (a.attributed_revenue - s.total_spend) / NULLIF(s.total_spend, 0) * 100 AS roas_minus_one_pct,
    (a.attributed_revenue * 0.4 - s.total_spend) / NULLIF(s.total_spend, 0) * 100 AS marketing_roi_with_margin
FROM attribution a
JOIN spend s ON s.channel = a.channel
ORDER BY marketing_roi_with_margin DESC;

0.4 — gross margin assumption.

ROMI vs ROAS

ROAS 4.0 при margin 20% → ROMI = (4 × 0.2 - 1) / 1 = -20%. Технически loss.

Time-to-payback

Когда marketing investment окупается:

WITH customer_cohort AS (
    SELECT
        u.user_id,
        u.acquired_at,
        u.acquisition_cost
    FROM users u
    WHERE u.acquired_at >= CURRENT_DATE - INTERVAL '12 months'
),
cumulative_revenue AS (
    SELECT
        c.user_id,
        c.acquired_at,
        c.acquisition_cost,
        EXTRACT(EPOCH FROM (o.created_at - c.acquired_at)) / 86400 AS days_after_acq,
        SUM(o.total) OVER (
            PARTITION BY c.user_id ORDER BY o.created_at
        ) AS cum_revenue
    FROM customer_cohort c
    JOIN orders o ON o.user_id = c.user_id
    WHERE o.status = 'paid'
)
SELECT
    user_id,
    acquisition_cost,
    MIN(days_after_acq) FILTER (WHERE cum_revenue * 0.4 >= acquisition_cost) AS days_to_payback
FROM cumulative_revenue
GROUP BY user_id, acquisition_cost;

× 0.4 — margin. Payback = когда cumulative profit >= acquisition cost.

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

Ошибка 1. ROAS as ROI. ROAS doesn't include margin. ROI does.

Ошибка 2. Marketing cost ≠ ad spend. Add: agency fees, content production, salaries marketing team. Real marketing cost.

Ошибка 3. Attribution windows. ROI depends на attribution model. Different models = different ROI numbers.

Ошибка 4. Brand marketing. Brand campaigns have long-term effect. ROI на короткий window underestimates.

Ошибка 5. Halo effect ignored. Marketing brought juzer who told friend → halo revenue. Hard to attribute.

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

• Как посчитать ROAS в SQL
• Как посчитать CAC в SQL
• Как посчитать LTV в SQL
• Как посчитать CAC Payback в SQL

FAQ

Какой Marketing ROI ok?

100%+ — отлично. 30-50% — норма. <0% — investing for future / brand.

ROI положительный, но < 100% — что значит?

Each $1 spend returns $1-2 in profit. Break-even к profitable.

ROAS 5, but ROI -10% — почему?

Low margin (20%). $5 revenue × 20% = $1 profit. Costs $1. ROI = 0.

Multi-touch ROI?

Use multi-touch attribution + margin formula by channel.

Brand marketing ROI?

Длинный horizon. Brand awareness studies + LTV analysis.