CMO Revenue Dashboard

-- Blended Customer Acquisition Cost (CAC)
-- Sums total marketing spend across all channels and divides by total customers acquired
WITH total_spend AS (
  SELECT SUM(spend_usd) as usd_spent
  FROM marketing_spend
  WHERE date BETWEEN :start_date AND :end_date
),
new_customers AS (
  SELECT COUNT(DISTINCT customer_id) as customer_count
  FROM customers
  WHERE start_date BETWEEN :start_date AND :end_date
)
SELECT
  COALESCE(usd_spent, 0) / NULLIF(customer_count, 0) as blended_cac
FROM total_spend, new_customers;

-- Customer Lifetime Value (LTV)
-- Formula: (ARPU * Gross Margin %) / Monthly Churn Rate
-- Gross Margin is hardcoded to 82% based on audited SaaS financials
WITH arpu_calc AS (
  SELECT AVG(mrr) as avg_mrr
  FROM customers
  WHERE start_date <= :end_date
),
churn_calc AS (
  SELECT
    (SELECT COUNT(DISTINCT customer_id) FROM customers WHERE status = 'churned' AND end_date BETWEEN :start_date AND :end_date)::float /
    NULLIF((SELECT COUNT(DISTINCT customer_id) FROM customers WHERE start_date < :start_date), 0) as monthly_churn
)
SELECT
  (avg_mrr * 0.82) / NULLIF(monthly_churn, 0) as customer_ltv
FROM arpu_calc, churn_calc;

-- LTV:CAC Ratio
-- Computes the ratio of Customer Lifetime Value to Customer Acquisition Cost
WITH cac_calc AS (
  SELECT SUM(spend_usd) / NULLIF(COUNT(DISTINCT c.customer_id), 0) as cac
  FROM marketing_spend s
  JOIN customers c ON c.start_date = s.date
  WHERE s.date BETWEEN :start_date AND :end_date
),
ltv_calc AS (
  SELECT
    (AVG(mrr) * 0.82) / NULLIF(
      (SELECT COUNT(DISTINCT customer_id) FROM customers WHERE status = 'churned' AND end_date BETWEEN :start_date AND :end_date)::float /
      NULLIF((SELECT COUNT(DISTINCT customer_id) FROM customers WHERE start_date < :start_date), 0), 0
    ) as ltv
  FROM customers
  WHERE start_date <= :end_date
)
SELECT ltv / NULLIF(cac, 0) as ltv_to_cac_ratio
FROM ltv_calc, cac_calc;

-- CAC Payback Period (in Months)
-- Formula: CAC / (Monthly ARPU * Gross Margin %)
WITH cac_calc AS (
  SELECT SUM(spend_usd) / NULLIF(COUNT(DISTINCT c.customer_id), 0) as cac
  FROM marketing_spend s
  JOIN customers c ON c.start_date = s.date
  WHERE s.date BETWEEN :start_date AND :end_date
),
arpu_calc AS (
  SELECT AVG(mrr) as avg_mrr
  FROM customers
  WHERE start_date <= :end_date
)
SELECT cac / NULLIF((avg_mrr * 0.82), 0) as payback_months
FROM cac_calc, arpu_calc;