How to calculate Cost per Acquisition (CPA)

Formula

CPA = campaign spend ÷ number of conversions (actions)

SQL query

This example assumes: ad_stats(day date, campaign text, spend numeric, conversions bigint). It is written in PostgreSQL syntax; date functions differ slightly in MySQL, BigQuery and SQLite, and your column names will differ from these.

SELECT campaign,
       SUM(spend) AS spend,
       SUM(conversions) AS conversions,
       SUM(spend) / NULLIF(SUM(conversions), 0) AS cpa
FROM ad_stats
WHERE day >= DATE_TRUNC('month', CURRENT_DATE)
GROUP BY campaign
ORDER BY cpa;

Common mistakes that make Cost per Acquisition (CPA) wrong

• CPA counts any conversion you define; customer acquisition cost counts only new paying customers, so they differ.
• Conversions attributed by the ad platform and conversions in your own database rarely match.
• Campaigns with zero conversions have an undefined CPA; list them rather than hiding them.

Ask for Cost per Acquisition (CPA) in plain English

Instead of writing the query yourself, you can type the question. SchemaChat turns it into SQL against your connected database or uploaded file, runs it, and shows the SQL and the figures it used so you can check the answer before you share it. It does not replace your judgement about the definition: check that the query matches the formula your business uses.

• What is the cost per acquisition for each campaign this month?
• Which campaigns spent money and produced no conversions?