Schema Theory

Description

Customer Acquisition Cost (CAC) measures the average cost of acquiring a new customer over a defined period. It includes all sales and marketing expenses associated with customer acquisition, divided by the number of new customers acquired during that time.

Visual Example

DAX Formula

CAC = 
DIVIDE(
    SUM(MarketingSpend[Cost]),
    DISTINCTCOUNT(CustomerAcquisition[CustomerID])
)          

SQL Statement

SELECT
  d.Month,
  d.MonthID,
  SUM(ms.Cost) AS TotalMarketingSpend,
  COUNT(DISTINCT ca.CustomerID) AS CustomerAcquisitions,
  SUM(ms.Cost) * 1.0 / NULLIF(COUNT(DISTINCT ca.CustomerID), 0) AS CAC
FROM
  DimDate d
LEFT JOIN MarketingSpend ms
  ON ms.SpendDate = d.Date
LEFT JOIN CustomerAcquisition ca
  ON ca.AcquisitionDate = d.Date
GROUP BY
  d.Month,
  d.MonthID
ORDER BY
  d.MonthID  

Usage Notes

• Ensure that marketing costs and customer acquisition counts are filtered over the same time period.
• Use a shared Date table to allow time intelligence functions like monthly or quarterly CAC.
• Pair CAC with CLV (Customer Lifetime Value) to assess marketing ROI.

Downloads