Customer Acquisition Cost is one of the most scrutinised numbers in any growth-stage business and also one of the most frequently miscalculated. Most teams derive it from a spreadsheet stitching together marketing invoices and a CRM export, which means their CAC figure is already weeks stale by the time anyone reads it.
Your database already has the data to calculate CAC accurately, in real time, across every channel and cohort. You just need to know which tables to touch and how to ask the right questions. This guide walks through the full process from the underlying SQL to an auto-refreshing dashboard that keeps your CAC visible without any manual work.
What Is Customer Acquisition Cost (and Why the Database Number Is Different)
The standard definition: CAC = Total Sales & Marketing Spend ÷ Number of New Customers Acquired.
Simple enough. But the spreadsheet version usually suffers from two problems:
When you calculate CAC directly from your database, you can be precise: a "new customer" is a row in your subscriptions table where status = 'active' and created_at falls within the period. You can join that to spend data stored in your ad_spend or campaigns table, or pull spend from a connected data source.
The result is a figure you trust and can drill into without waiting for someone to manually export three CSVs.
What Tables You Need to Join
Every database schema is different, but most SaaS and e-commerce businesses have the raw material in some form. Here's a typical structure:
`customers` one row per customer, with id, created_at, acquisition_channel, status
`subscriptions` (or orders) one row per billing event, with customer_id, plan, amount, started_at, status
`ad_spend` (or marketing_costs) one row per spend record, with channel, campaign, amount, spend_date
If your ad spend lives in a separate system (Google Ads, Facebook Ads), you may need to import it into a table first, or use a tool that can pull it in via API. For the purposes of this guide, assume it's in the database.
The SQL Query Behind a Reliable CAC Calculation
Here's a basic CAC query for a monthly period:
WITH new_customers AS (
SELECT COUNT(*) AS count
FROM subscriptions
WHERE status = 'active'
AND started_at >= '2026-03-01'
AND started_at < '2026-04-01'
),
total_spend AS (
SELECT SUM(amount) AS spend
FROM ad_spend
WHERE spend_date >= '2026-03-01'
AND spend_date < '2026-04-01'
)
SELECT
total_spend.spend AS marketing_spend,
new_customers.count AS new_customers,
ROUND(total_spend.spend / NULLIF(new_customers.count, 0), 2) AS cac
FROM total_spend, new_customers;The NULLIF(..., 0) prevents a division-by-zero error in months where no customers were acquired.
Want to add sales team salaries to the spend figure? Join in an employee_costs table filtered to sales and marketing roles. The schema will vary, but the pattern is the same: sum all acquisition-related costs, divide by confirmed new paying customers.
Breaking Down CAC by Channel, Campaign, or Cohort
Aggregate CAC is a starting point. The real value is knowing which channels or campaigns are producing customers most efficiently.
CAC by acquisition channel:
SELECT
c.acquisition_channel,
COUNT(DISTINCT s.customer_id) AS new_customers,
SUM(sp.amount) AS spend,
ROUND(SUM(sp.amount) / NULLIF(COUNT(DISTINCT s.customer_id), 0), 2) AS cac
FROM subscriptions s
JOIN customers c ON s.customer_id = c.id
JOIN ad_spend sp ON sp.channel = c.acquisition_channel
AND DATE_TRUNC('month', sp.spend_date) = DATE_TRUNC('month', s.started_at)
WHERE s.status = 'active'
AND s.started_at >= '2026-01-01'
GROUP BY c.acquisition_channel
ORDER BY cac ASC;This will surface channels like "organic", "paid_search", "referral" side-by-side with their respective CAC figures. If paid search is generating customers at $280 CAC and referral is at $45, that's a meaningful signal.
CAC by plan tier:
SELECT
s.plan,
COUNT(*) AS new_customers,
ROUND(SUM(sp.total_spend) / COUNT(*), 2) AS cac
FROM subscriptions s
CROSS JOIN (SELECT SUM(amount) AS total_spend FROM ad_spend
WHERE spend_date >= '2026-01-01') sp
WHERE s.status = 'active'
AND s.started_at >= '2026-01-01'
GROUP BY s.plan;This lets you compare the cost to acquire a starter-tier customer versus an enterprise-tier customer critical input for pricing and positioning decisions.
Building an Auto-Refreshing CAC Dashboard
A one-time query is useful. A dashboard that updates itself daily is actually useful.
The problem with traditional BI tools is that someone has to set up the query, maintain it when the schema changes, and make sure non-technical stakeholders can read it without needing SQL access.
With AI for Database, you can describe what you want in plain English "Show me monthly CAC by acquisition channel for the last 6 months" and the tool writes and runs the SQL, then builds a dashboard you can share with your marketing and finance teams. The dashboard refreshes on whatever schedule you set (daily, hourly, or at any interval), so the numbers are always current.
You can also ask follow-up questions directly: "Which channel had the lowest CAC last quarter?" or "How has CAC trended since we launched the referral programme?" Each answer comes back as a table or chart, without anyone writing a new query.
This is the gap that tools like Metabase or Looker don't fill well they require someone to pre-build every report. With an AI-driven interface, you can answer questions you didn't plan to ask.
Setting Alerts When CAC Exceeds Thresholds
Tracking CAC monthly is good. Getting alerted the moment it spikes is better.
You can set up a workflow that runs a CAC calculation nightly and fires a Slack message or email if CAC crosses a threshold:
Example workflow logic:
This kind of monitoring used to require a data engineer to write a stored procedure, set up a cron job, and wire it to a notification system. With AI for Database's action workflows, you define the condition in plain English and point it at a Slack channel or email address. No stored procedures, no DBA required.