Customer acquisition cost (CAC): formula and example.

WITH mkt AS (
  SELECT date_trunc('year', month) AS year, SUM(amount) AS cost
  FROM marketing_costs GROUP BY 1),
sales AS (
  SELECT date_trunc('year', p.period_month) AS year, SUM(p.gross_cost) AS cost
  FROM payroll p JOIN employees e USING (employee_id)
  WHERE e.department = 'Sales' GROUP BY 1),
new_customers AS (
  SELECT date_trunc('year', first_order) AS year, COUNT(*) AS n
  FROM (SELECT customer_id, MIN(order_date) AS first_order FROM orders GROUP BY 1) f
  GROUP BY 1)
SELECT year, m.cost + COALESCE(s.cost, 0) AS acquisition_cost, n.n AS new_customers,
       ROUND((m.cost + COALESCE(s.cost, 0)) / NULLIF(n.n, 0), 0) AS cac
FROM mkt m LEFT JOIN sales s USING (year) JOIN new_customers n USING (year)
ORDER BY year;

New Customers =
COUNTROWS(
    FILTER(
        VALUES(Customers[CustomerId]),
        VAR FirstOrder = CALCULATE(MIN(Sales[OrderDate]), ALL('Date'))
        RETURN FirstOrder >= MIN('Date'[Date]) && FirstOrder <= MAX('Date'[Date])
    )
)

CAC =
DIVIDE(
    SUM(MarketingCosts[Amount]) + CALCULATE(SUM(Payroll[GrossCost]), Employees[Department] = "Sales"),
    [New Customers]
)