How to calculate CPA in SQL

What CPA is and why it matters

Cost Per Acquisition is the working language of every paid marketing team. Your growth lead pings you on a Tuesday afternoon: "Pull CPA by channel for the last 30 days, and tell me which campaigns to kill before the budget review on Thursday." That request sounds simple, but the answer depends on three definitions you must lock down before writing any SQL — what counts as an "acquisition," which spend rows you include, and which attribution window you trust. Get any of those wrong and the dashboard quietly lies to the team for a quarter.

The acronym itself is just a ratio: dollars spent divided by acquisitions produced. The acquisition event is whatever the team has agreed to optimize for — a paid signup at Stripe, a first booking at Airbnb, a paid plan at Notion, a completed test drive at Tesla. Every ad platform from Google to Meta to TikTok exposes a bidding mode tied to a target CPA, so when leadership talks about "performance marketing," they are usually talking about whether your reported CPA is below the value you can extract from a customer.

CPA is also the metric most often confused with CAC in interviews. If a recruiter at Snowflake or DoorDash asks you to write the query and you reach for total marketing spend over total new customers, you have answered the wrong question. CPA is granular and campaign-level; CAC is a company-wide number that includes salaries and tools. Most marketing analyst loops at Meta, Airbnb, and Vercel will probe whether you can articulate that distinction in code, not just in words.

The SQL formula

The core ratio fits on one line, but a useful query computes it per day so the team can spot drift and per campaign so the team can act:

-- Daily CPA over the last 30 days
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;

Two details matter here. First, the NULLIF guard returns NULL when a day has zero acquisitions instead of throwing a divide-by-zero error — that single token has saved more dashboards than any other piece of SQL in performance analytics. Second, the explicit ::NUMERIC cast prevents Postgres from doing integer division on the numerator and silently truncating CPA to whole dollars, a classic interview trap at Stripe and Linear data screens.

If the platform tables you read from store spend in micro-currency (Google Ads exports cost_micros divided by one million), divide first, aggregate second. Otherwise you accumulate rounding error and miss the budget by a few percent every week.

CPA by channel and campaign

The query the growth lead actually needs joins channel metadata so the killable campaigns sort to the top:

-- CPA by channel and campaign, with funnel context
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 DESC;

The HAVING SUM(acquisitions) >= 10 clause is the most important line in this entire post. Without it, a campaign with one accidental conversion and twenty dollars of spend shows up as a CPA of twenty — looking like a hero when it is statistical noise. Most teams at Meta, Airbnb, and Uber set the floor between ten and thirty acquisitions before they let a CPA number into a report.

Sorting by cpa DESC puts the most expensive customers at the top, which is what you want when the question is "what should I cut?" If the question were "where should I scale?" you would sort ascending and require a larger acquisition floor, because the next dollar of spend at a small campaign is rarely as efficient as the marginal dollar at a proven one.

CPA vs CAC

CPA is the per-campaign, paid-channel-only number. CAC is the company-wide cost of acquiring a customer, which includes the marketing team's salaries, the marketing tooling stack, agency fees, and organic costs. When a finance partner at Snowflake or Databricks compares to a benchmark, they almost always mean CAC. When a paid media buyer talks about a bidding strategy, they always mean CPA.

-- CPA per campaign
SELECT
    campaign_id,
    SUM(spend) / NULLIF(SUM(acquisitions), 0) AS cpa
FROM ad_performance
WHERE DATE >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 month')
  AND DATE <  DATE_TRUNC('month', CURRENT_DATE)
GROUP BY campaign_id;

-- CAC company-wide for the same month
WITH spend AS (
    SELECT SUM(amount) AS total_marketing_spend
    FROM marketing_expenses
    WHERE month = DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 month')
),
new_users AS (
    SELECT COUNT(*) AS new_customers
    FROM users
    WHERE first_paid_at >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 month')
      AND first_paid_at <  DATE_TRUNC('month', CURRENT_DATE)
)
SELECT
    total_marketing_spend,
    new_customers,
    total_marketing_spend::NUMERIC / NULLIF(new_customers, 0) AS cac
FROM spend, new_users;

The two numbers almost never match, and they should not. CAC is usually one and a half to three times higher than the blended CPA across paid channels, because the denominator counts organic customers too while the numerator counts non-media costs. The further the gap, the heavier the team is on people and tooling relative to media spend.

Target CPA bid strategy

Most platforms let you set a target CPA and let the auction optimize bids to hit it. The number you plug in is not a guess — it comes from unit economics:

-- Target CPA = LTV * Gross Margin / Payback Period (months)
WITH unit_econ AS (
    SELECT
        300.00 AS avg_ltv_24mo,   -- replace WITH cohort LTV query
        0.42   AS gross_margin,   -- replace WITH finance number
        6      AS payback_months  -- board-approved threshold
)
SELECT
    avg_ltv_24mo                                       AS ltv,
    gross_margin                                       AS margin,
    avg_ltv_24mo * gross_margin                        AS contribution_per_customer,
    (avg_ltv_24mo * gross_margin) / payback_months     AS target_cpa
FROM unit_econ;

For a consumer subscription product at thirty dollars a month with twelve-month average tenure, the contribution is about one hundred and fifty dollars after margin. A six-month payback target then puts your ceiling CPA at twenty-five dollars. Bidding above that wins more auctions but burns cash; bidding below it leaves growth on the table.

This is the calculation that gets you hired at Uber, DoorDash, or Airbnb growth teams. Recruiters do not want the formula memorized — they want to see you ask about gross margin and payback before writing any SQL, because those two assumptions move the answer by a factor of three.

Common pitfalls

The first trap is acquisition definition drift. A campaign that optimized for "free signup" in January and "paid conversion" in April will show a CPA jump that has nothing to do with media efficiency. Lock the definition in a dimension table with an effective date range, join it into every CPA query, and surface the change date as an annotation on every chart. Otherwise you spend a week debugging a "spike" that was a product decision.

The second trap is last-click attribution treated as truth. A user at Meta who saw your Instagram ad three times and then converted on a branded Google search will be credited entirely to Google in a last-click model. CPA per channel is fine for ranking similar channels, but it overstates the efficiency of bottom-of-funnel ones like brand search and understates top-of-funnel ones like display. Run a parallel data-driven or first-touch attribution view before you cut a channel based on CPA alone.

The third trap is the lookback window. A click in January that converts in March can be attributed to January spend (true cost of that click) or March spend (recognized timing). Either choice is defensible, but they cannot coexist in the same dashboard. Pick one, document it in the data dictionary, and align the finance and marketing reports to the same convention — the alternative is a quarterly meeting where two leaders argue about a number neither of them computed.

The fourth trap is mixing CPA and CAC in board-level reports. Once the CFO at a Series C company sees a "CPA" number below "LTV/CAC" target, the assumption is the business is healthy. In reality CPA can be at target while CAC is twice as high because the marketing team is heavy on full-time salaries. Always present them side by side, with the blended CPA, the channel CPA, and the all-in CAC clearly labeled.

The fifth trap is view-through conversions, where a user saw an ad but did not click and later purchased anyway. Most platforms count these and inflate acquisitions; some teams strip them out before computing CPA. Whichever you do, apply it consistently and reconcile against in-product signup data. A CPA query that disagrees with the product analytics by more than five percent usually points to a view-through configuration nobody documented.

Optimization tips

If ad_performance is partitioned by date, the thirty-day filter prunes most of the scan automatically — make sure your warehouse is using the partition pruning by checking the query plan in Snowflake's profile view or BigQuery's execution graph. On a daily-grain table at scale, partition by date and cluster by channel to keep channel-level rollups cheap.

Aggregate first, then join. Joining ad_performance to a campaign-metadata dimension before the GROUP BY will balloon the intermediate row count. Materialize a small CTE with SUM(spend), SUM(acquisitions) grouped by campaign_id, then join to the dimension table for names and channel labels at the end.

For dashboards that re-run every fifteen minutes, build a daily aggregate as a materialized view or dbt incremental model keyed on (date, channel, campaign_id). The CPA query then reads from a table with thousands of rows instead of billions. The trade-off is one extra DAG step and roughly an hour of staleness — acceptable for paid marketing decisions, not acceptable for real-time bidding.

Related reading

• How to calculate CAC in SQL
• How to calculate CAC by channel in SQL
• How to calculate blended CAC in SQL
• How to calculate CAC payback in SQL
• How to calculate ARPU in SQL
• SQL window functions interview questions

If you want to drill marketing-analyst SQL questions like this one every day, NAILDD is launching with hundreds of SQL problems that mirror exactly this interview pattern.

FAQ

What is the difference between CPA and CAC?

CPA is per-campaign or per-channel and only counts media spend in the numerator. CAC is company-wide and includes marketing salaries, tools, agencies, and organic acquisition costs in the numerator while counting all new customers — organic and paid — in the denominator. CAC is the number a board or CFO cares about; CPA is the number a media buyer optimizes.

What CPA is acceptable for my product?

The honest answer is "CPA below LTV times gross margin divided by your payback period." For a SaaS product targeting twelve-month payback, the ceiling is the twelve-month gross profit per customer. For consumer apps with thinner margins and faster churn, payback windows of six months or less are typical. There is no universal CPA target — anyone who quotes one without asking about LTV and margin is guessing.

Should I count view-through conversions?

It depends on the channel. For brand-safety partners and high-funnel display, view-through gives credit where last-click misses real impact, but the inflation is real. The defensible move is to compute both — a click-only CPA and a click-plus-view CPA — and label them clearly. Then make budget decisions on the click-only number while reporting the view-through one for context.

How is target CPA bidding different from manual CPC?

Target CPA bidding lets the platform vary the per-click bid in real time to hit your desired cost per acquisition on average. Manual CPC fixes the click price and lets CPA float with conversion rate. Target CPA usually wins on mature campaigns with enough conversion volume — most platforms need at least thirty acquisitions per month before the model is stable. Below that floor, manual bidding gives you predictable spend pacing.

Can I compute CPA per funnel stage?

Yes, and you should. Cost per lead, cost per signup, and cost per paid conversion are three different ratios with three different denominators, and a healthy paid funnel sees each one within an acceptable band. A campaign with cheap leads but expensive paid conversions usually has a targeting or landing-page mismatch — the SQL is the same query repeated three times with different acquisition definitions.

How do I avoid the divide-by-zero error in production?

Always wrap the denominator in NULLIF(x, 0) so the result is NULL instead of an exception. In dashboarding tools, render NULL as a dash rather than zero — a CPA of zero is a meaningful claim (free customers), while NULL correctly says "we did not acquire anyone today." This single convention prevents the most common production page from analytics platforms.