What is cost per acquisition, and how do you measure it in Metabase?

Cost per acquisition (CPA) is total spend divided by acquisitions — paying customers, or whatever target conversion event a campaign is optimized toward. It's the efficiency metric one rung below CAC and one rung above cost per lead. Measure it in Metabase from spend synced from Google Ads, Meta Ads, and LinkedIn Ads, joined to conversions from your own backend.

What does a CPA chart look like in Metabase?

Chart blended CPA by month as a line, and read the slope as the efficiency story: a steady decline means spend is buying acquisitions more cheaply as targeting and channel mix improve. A spike like November's is usually seasonal — Q4 auctions price every advertiser up — and reads next to the calendar, not as a broken funnel.

What cost per acquisition measures

It measures how much media budget it takes to produce one unit of the outcome you actually want — a purchase, a subscription, a funded account. That makes it the natural currency for budget allocation: campaigns and channels compete on CPA against a target derived from what an acquisition is worth. Trended blended CPA also works as an early-warning signal for CAC, because media efficiency usually deteriorates before the fully loaded number does.

CPA vs. CAC

The two get used interchangeably in decks, but they answer different questions. CPA is scoped: this channel's (or this campaign's) media spend over the conversions attributed to it. CAC is fully loaded: all sales and marketing cost — salaries, agencies, tools, spend — over all new customers, attribution-free. CPA tells you where the next dollar of budget should go; CAC tells you whether acquisition is affordable at all. The scoping also means CPA depends on an attribution model while CAC mostly doesn't, so when the two trend in opposite directions, look at attribution first.

What data does it need?

• An ad_performance_daily table with channel, campaign, stat_date, and spend, as each platform reports it.
• A conversions table from your backend or CRM with converted_at, conversion_type, and attributed channel/campaign fields — deduplicated, one row per real acquisition.
• A written attribution rule (window and priority) applied consistently when stamping attributed_channel.

SQL patterns

WITH monthly_spend AS (
  SELECT
    date_trunc('month', stat_date) AS month,
    SUM(spend) AS spend
  FROM ad_performance_daily
  GROUP BY 1
),
monthly_acquisitions AS (
  SELECT
    date_trunc('month', converted_at) AS month,
    COUNT(*) AS acquisitions
  FROM conversions
  WHERE conversion_type = 'purchase'
  GROUP BY 1
)
SELECT
  s.month,
  s.spend,
  a.acquisitions,
  ROUND(s.spend / NULLIF(a.acquisitions, 0), 2) AS blended_cpa
FROM monthly_spend s
LEFT JOIN monthly_acquisitions a USING (month)
WHERE s.month >= date_trunc('month', CURRENT_DATE) - INTERVAL '12 months'
ORDER BY 1;

WITH spend AS (
  SELECT
    channel,
    campaign,
    SUM(spend) AS spend
  FROM ad_performance_daily
  WHERE stat_date >= CURRENT_DATE - INTERVAL '90 days'
  GROUP BY 1, 2
),
conv AS (
  SELECT
    attributed_channel AS channel,
    attributed_campaign AS campaign,
    COUNT(*) AS acquisitions
  FROM conversions
  WHERE conversion_type = 'purchase'
    AND converted_at >= CURRENT_DATE - INTERVAL '90 days'
  GROUP BY 1, 2
)
SELECT
  s.channel,
  s.campaign,
  s.spend,
  COALESCE(c.acquisitions, 0) AS acquisitions,
  ROUND(s.spend / NULLIF(c.acquisitions, 0), 2) AS cpa
FROM spend s
LEFT JOIN conv c USING (channel, campaign)
ORDER BY s.spend DESC;

Pitfalls

Where this metric applies

• Google Ads + Metabase — spend and conversion actions by campaign
• Meta Ads + Metabase — spend by campaign against warehouse conversions
• LinkedIn Ads + Metabase — high-CPA B2B campaigns where dedup matters most
• TikTok Ads + Metabase — spend for blended CPA across emerging channels

Related

Metrics

Dashboards

FAQ