How to calculate Customer Acquisition Cost (CAC)

Formula

CAC = (marketing spend + sales spend) in a period ÷ new customers won in that period

SQL query

This example assumes: spend(month, channel, amount), customers(id, first_order_at). It is written in PostgreSQL syntax; date functions differ slightly in MySQL, BigQuery and SQLite, and your column names will differ from these.

WITH cost AS (
  SELECT DATE_TRUNC('month', month) AS month, SUM(amount) AS total_spend
  FROM spend GROUP BY 1
), won AS (
  SELECT DATE_TRUNC('month', first_order_at) AS month, COUNT(*) AS new_customers
  FROM customers GROUP BY 1
)
SELECT c.month, c.total_spend, w.new_customers,
       c.total_spend / NULLIF(w.new_customers, 0) AS cac
FROM cost c
JOIN won w USING (month)
ORDER BY c.month;

Common mistakes that make Customer Acquisition Cost (CAC) wrong

• Counting only ad spend (not salaries, tools, agencies) gives a flattering 'blended paid' number; say which one you report.
• Spend and customers from the same month ignore the lag between spend and conversion; compare a lagged window if your cycle is long.
• Existing customers returning (reactivations) are not new customers.

Ask for Customer Acquisition Cost (CAC) 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 was our customer acquisition cost by month?
• Which channel has the lowest cost per new customer?