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

Зачем CPA

CPA = Cost Per Acquisition. «Acquisition» = defined action (signup, purchase, lead). Bid system платформы оптимизируется под target CPA. Каноническая performance metric.

Формула

CPA = total_spend / acquisitions

«Acquisition» определяется заранее — signup, paid purchase, demo booking, etc.

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

SELECT
    DATE_TRUNC('day', DATE) AS day,
    SUM(spend) AS spend,
    SUM(acquisitions) AS acquisitions,
    SUM(spend)::NUMERIC / NULLIF(SUM(acquisitions), 0) AS cpa
FROM ad_performance
WHERE DATE >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY 1
ORDER BY 1;

По каналам

SELECT
    channel,
    campaign_id,
    SUM(spend) AS spend,
    SUM(acquisitions) AS acquisitions,
    SUM(spend)::NUMERIC / NULLIF(SUM(acquisitions), 0) AS cpa,
    SUM(clicks)::NUMERIC * 100 / NULLIF(SUM(impressions), 0) AS ctr_pct,
    SUM(acquisitions)::NUMERIC * 100 / NULLIF(SUM(clicks), 0) AS conv_rate_pct
FROM ad_performance
WHERE DATE >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY channel, campaign_id
HAVING SUM(acquisitions) >= 10
ORDER BY cpa;

CPA vs CAC

CPA — per-campaign. CAC — company-wide (включая salaries marketing team).

-- CPA per campaign
SELECT campaign_id, SUM(spend) / SUM(acquisitions) AS cpa
FROM ad_performance ...

-- CAC company-wide
WITH spend AS (
    SELECT SUM(amount) AS total_marketing_spend
    FROM marketing_expenses
    WHERE month = '2026-04-01'
),
new_users AS (
    SELECT COUNT(*) AS new_customers
    FROM users
    WHERE first_paid_at BETWEEN '2026-04-01' AND '2026-05-01'
)
SELECT total_marketing_spend / new_customers AS cac
FROM spend, new_users;

Target CPA bid strategy

Многие платформы (Google Ads) bid optimize for target CPA:

-- Target CPA = LTV × Margin / Payback Period
WITH ltv AS (
    SELECT 300 AS avg_ltv  -- placeholder
),
margin AS (
    SELECT 0.4 AS gross_margin  -- placeholder
)
SELECT
    avg_ltv * gross_margin AS contribution_per_customer,
    (avg_ltv * gross_margin) / 3 AS target_cpa_3month_payback
FROM ltv, margin;

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

Ошибка 1. Acquisition definition drift. «Acquisition» changed semantics (от signup → paid). Track over time.

Ошибка 2. Last-click attribution. CPA по last-click ≠ true. Multi-touch attribution.

Ошибка 3. Lookback window. Acquired при click в Jan, paid в Mar. Match to Jan spend or Mar?

Ошибка 4. CPA vs CAC mixed up. CPA — channel. CAC — overall. Не путать в reports.

Ошибка 5. View-through conversions. Saw ad, didn't click, later purchased. Counts? Channel-specific policy.

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

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

FAQ

CPA vs CAC?

CPA — per-campaign or per-channel. CAC — overall (incl. salaries, tools).

Какой CPA ok?

CPA < LTV × Margin / Payback Period. SaaS: payback < 12 months стандарт.

Target CPA — что бид?

Bid that gives acceptable LTV/CPA ratio. ≥3 — good. ≥5 — excellent.

CPA per stage?

Yes! CPL (lead), CPSU (signup), CPP (paid) — separate measurements.

CPA optimized — что значит?

Platform автоматически adjusts bids чтобы CPA target hit. Reduce manual work.