Как посчитать Customer Acquisition Rate в SQL

Зачем Customer Acquisition Rate

CAR — сколько новых клиентов привлекли за период. Отличается от CAC (cost). CAR показывает velocity роста customer base, не efficiency.

Формула

Customer Acquisition Rate = New Customers / Period
Customer Acquisition % = New Customers / Total Visitors × 100%

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

SELECT
    DATE_TRUNC('month', signup_date) AS month,
    COUNT(*) AS new_customers
FROM users
WHERE signup_date >= '2026-01-01'
GROUP BY 1
ORDER BY 1;

По channels

SELECT
    DATE_TRUNC('month', signup_date) AS month,
    acquisition_channel,
    COUNT(*) AS new_customers
FROM users
WHERE signup_date >= '2026-01-01'
GROUP BY 1, 2
ORDER BY 1, new_customers DESC;

Customer Acquisition Rate vs Visitors:

WITH visitors AS (
    SELECT
        DATE_TRUNC('month', session_date) AS month,
        COUNT(DISTINCT visitor_id) AS unique_visitors
    FROM sessions
    GROUP BY 1
),
new_users AS (
    SELECT
        DATE_TRUNC('month', signup_date) AS month,
        COUNT(*) AS new_users
    FROM users
    GROUP BY 1
)
SELECT
    v.month,
    v.unique_visitors,
    n.new_users,
    n.new_users::NUMERIC * 100 / NULLIF(v.unique_visitors, 0) AS conversion_to_signup_pct
FROM visitors v
LEFT JOIN new_users n ON n.month = v.month
ORDER BY v.month;

Динамика

WITH monthly AS (
    SELECT
        DATE_TRUNC('month', signup_date) AS month,
        COUNT(*) AS new_users
    FROM users
    WHERE signup_date >= '2024-01-01'
    GROUP BY 1
)
SELECT
    month,
    new_users,
    LAG(new_users) OVER (ORDER BY month) AS prev_month,
    (new_users::NUMERIC / NULLIF(LAG(new_users) OVER (ORDER BY month), 0) - 1) * 100 AS mom_growth_pct
FROM monthly
ORDER BY month;

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

Ошибка 1. Repeated signups. Юзер забыл pass и зарегился снова с другой почтой. Counts as new. Dedupe.

Ошибка 2. Bot signups. Без фильтрации overestimate. Implement captcha + bot detection.

Ошибка 3. Time zone. Signup в 23:50 UTC = next day МСК. Зафиксируйте.

Ошибка 4. Channel attribution. Юзер пришёл через ads, затем organic — какой attribute? UTM tracking важен.

Ошибка 5. Сравнение acquisition rate без context. Маркетинг увеличил spend → CAR вырос. Это efficiency или просто money?

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

• Как посчитать CAC в SQL
• Как посчитать new users в SQL
• Как посчитать ROAS в SQL
• Как посчитать конверсию в SQL

FAQ

CAR vs CAC?

CAR — count of new customers. CAC — cost per customer.

Какой CAR considered ok?

Зависит от стадии. Growth-стартап — +20% MoM. Зрелый — +5-10% MoM.

CAR падает — что делать?

1. Acquisition channels analysis. 2) Conversion funnel decomposition. 3) Budget allocation review.

CAR vs growth rate?

CAR — absolute. Growth rate — relative.

CAR на cohort?

Можно: «Cohort growth» — сколько cohort выросла quarter-over-quarter. Используется реже.