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

Что такое CAC

CAC (Customer Acquisition Cost) — стоимость привлечения одного клиента.

CAC = Marketing spend / New customers acquired

Варианты:

• Blended CAC — весь маркетинг / все новые клиенты
• Paid CAC — только платный трафик
• Fully-loaded CAC — включает зарплаты команды, tools, etc.

Схема данных

Предположим, есть таблицы:

ad_spend        (day, channel, spend)
users           (user_id, signup_at, attribution_channel)
first_orders    (user_id, first_order_at)  -- из orders

1. Простой blended CAC

Весь маркетинговый spend за период / все новые клиенты за тот же период.

WITH spend_total AS (
    SELECT SUM(spend) AS total_spend
    FROM ad_spend
    WHERE day BETWEEN '2026-01-01' AND '2026-03-31'
),
new_users AS (
    SELECT COUNT(*) AS new_users_cnt
    FROM users
    WHERE signup_at BETWEEN '2026-01-01' AND '2026-03-31'
)
SELECT
    st.total_spend,
    nu.new_users_cnt,
    st.total_spend / nu.new_users_cnt AS blended_cac
FROM spend_total st, new_users nu;

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

WITH spend_by_channel AS (
    SELECT channel, SUM(spend) AS total_spend
    FROM ad_spend
    WHERE day BETWEEN '2026-01-01' AND '2026-03-31'
    GROUP BY channel
),
users_by_channel AS (
    SELECT attribution_channel AS channel, COUNT(*) AS new_users
    FROM users
    WHERE signup_at BETWEEN '2026-01-01' AND '2026-03-31'
    GROUP BY attribution_channel
)
SELECT
    sbc.channel,
    sbc.total_spend,
    ubc.new_users,
    sbc.total_spend / NULLIF(ubc.new_users, 0) AS cac
FROM spend_by_channel sbc
LEFT JOIN users_by_channel ubc USING (channel)
ORDER BY cac;

3. CAC по когортам

Когортный CAC — точнее, чем общий.

WITH cohorts AS (
    SELECT
        DATE_TRUNC('month', signup_at) AS cohort_month,
        attribution_channel,
        COUNT(*) AS new_users
    FROM users
    GROUP BY 1, 2
),
spend AS (
    SELECT
        DATE_TRUNC('month', day) AS month,
        channel,
        SUM(spend) AS spend
    FROM ad_spend
    GROUP BY 1, 2
)
SELECT
    c.cohort_month,
    c.attribution_channel,
    c.new_users,
    s.spend,
    s.spend / NULLIF(c.new_users, 0) AS cac
FROM cohorts c
LEFT JOIN spend s
    ON s.month = c.cohort_month
   AND s.channel = c.attribution_channel
ORDER BY c.cohort_month, c.attribution_channel;

4. Paid-only CAC

Только paid-каналы, без organic:

WITH paid_spend AS (
    SELECT SUM(spend) AS paid_spend
    FROM ad_spend
    WHERE channel IN ('google_ads', 'yandex_direct', 'vk_ads', 'facebook_ads')
      AND day BETWEEN '2026-01-01' AND '2026-03-31'
),
paid_users AS (
    SELECT COUNT(*) AS paid_users
    FROM users
    WHERE attribution_channel IN ('google_ads', 'yandex_direct', 'vk_ads', 'facebook_ads')
      AND signup_at BETWEEN '2026-01-01' AND '2026-03-31'
)
SELECT
    paid_spend.paid_spend / paid_users.paid_users AS paid_cac
FROM paid_spend, paid_users;

5. CAC по факту первой покупки (не регистрации)

Более строгий CAC: не «зарегистрировался», а «купил».

WITH spend AS (
    SELECT
        channel,
        DATE_TRUNC('month', day) AS month,
        SUM(spend) AS spend
    FROM ad_spend
    GROUP BY 1, 2
),
first_buyers AS (
    SELECT
        u.attribution_channel AS channel,
        DATE_TRUNC('month', fo.first_order_at) AS month,
        COUNT(*) AS new_buyers
    FROM first_orders fo
    JOIN users u ON u.user_id = fo.user_id
    GROUP BY 1, 2
)
SELECT
    s.channel,
    s.month,
    s.spend,
    fb.new_buyers,
    s.spend / NULLIF(fb.new_buyers, 0) AS cac_per_buyer
FROM spend s
LEFT JOIN first_buyers fb
    ON fb.channel = s.channel AND fb.month = s.month
ORDER BY s.month, cac_per_buyer;

6. CAC vs LTV per channel

Главное сравнение для маркетинга:

WITH cac_channel AS (
    SELECT
        channel,
        SUM(spend) / COUNT(DISTINCT user_id) AS cac
    FROM ad_spend a
    LEFT JOIN users u
        ON u.attribution_channel = a.channel
       AND DATE_TRUNC('month', u.signup_at) = DATE_TRUNC('month', a.day)
    GROUP BY channel
),
ltv_channel AS (
    SELECT
        u.attribution_channel AS channel,
        AVG(user_ltv.total_revenue) AS avg_ltv
    FROM users u
    JOIN (
        SELECT user_id, SUM(total) AS total_revenue
        FROM orders WHERE status = 'paid'
        GROUP BY user_id
    ) user_ltv ON user_ltv.user_id = u.user_id
    GROUP BY u.attribution_channel
)
SELECT
    c.channel,
    c.cac,
    l.avg_ltv,
    l.avg_ltv / NULLIF(c.cac, 0) AS ltv_cac_ratio
FROM cac_channel c
JOIN ltv_channel l USING (channel)
ORDER BY ltv_cac_ratio DESC;

7. Fully-loaded CAC

Включает не только рекламу, но и зарплаты маркетинга:

WITH all_marketing_cost AS (
    -- ad spend
    SELECT SUM(spend) AS cost FROM ad_spend WHERE day BETWEEN ... AND ...
    UNION ALL
    -- зарплаты маркетинг-команды (из таблицы marketing_payroll)
    SELECT SUM(monthly_salary) FROM marketing_payroll WHERE month BETWEEN ...
    UNION ALL
    -- SaaS-инструменты
    SELECT SUM(cost) FROM marketing_tools WHERE month BETWEEN ...
)
SELECT SUM(cost) AS total_marketing_cost FROM all_marketing_cost;

Дальше — как обычно.

8. CAC с учётом атрибуции (multi-touch)

Last-click — самый простой, но не лучший. Linear attribution в SQL:

WITH touches AS (
    SELECT
        user_id,
        channel,
        touched_at,
        COUNT(*) OVER (PARTITION BY user_id) AS total_touches
    FROM marketing_touches
    WHERE touched_at <= signup_at
),
attributed AS (
    SELECT
        user_id,
        channel,
        1.0 / total_touches AS attribution_weight
    FROM touches
)
SELECT
    channel,
    SUM(attribution_weight) AS attributed_users
FROM attributed
GROUP BY channel;

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

Ошибка 1. Перемешивать blended и paid CAC

Blended ≠ paid. Если 50% пользователей organic, blended CAC = paid CAC / 2 — обманчиво хорошо.

Ошибка 2. CAC без margin

Сравнение CAC с LTV должно быть через margin. 1000₽ LTV при 30% маржа = 300₽ profit — его сравнивать с CAC.

Ошибка 3. NULLIF забыли

spend / new_users  -- деление на 0 крашнет запрос
spend / NULLIF(new_users, 0)  -- безопасно, вернёт NULL

Ошибка 4. Смешение регистраций и покупок

CAC per signup ≠ CAC per paying customer. Для SaaS обычно нужно именно «paying».

Ошибка 5. Игнорировать attribution window

Если пользователь кликнул рекламу в январе, а купил в марте — кому засчитать? 7 дней / 28 дней / forever — стандарты разные.

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

• CAC что это
• CAC payback period
• Unit-экономика простыми словами
• Как посчитать LTV в SQL

FAQ

Blended или paid CAC — что важнее?

Paid — для принятия решений по рекламным бюджетам. Blended — для overall unit-экономики компании.

Нужен ли fully-loaded CAC?

Для малого бизнеса — обычно хватает paid + немного overhead. Для enterprise и инвесторов — обязательно.

Как учитывать long attribution window?

Ставить разумный default (28 дней) и держать несколько моделей атрибуции для сравнения.

CAC должен быть меньше LTV?

LTV / CAC ≥ 3:1 — здоровая экономика. < 3 — проблема или в CAC, или в LTV.

---

Тренируйте SQL — откройте тренажёр с 1500+ вопросами для собесов.