How to Calculate Customer Acquisition Cost (CAC) Across All Channels

-- Basic Customer Acquisition Cost by Channel
WITH marketing_costs AS (
  SELECT 
    DATE_TRUNC('month', cost_date) AS month,
    channel,
    SUM(ad_spend) AS direct_ad_spend,
    SUM(tool_costs) AS tool_costs,
    SUM(personnel_costs) AS personnel_costs,
    SUM(overhead_allocation) AS overhead_costs
  FROM marketing_expenses
  WHERE cost_date >= '2024-01-01'
  GROUP BY month, channel
),

customers_acquired AS (
  SELECT 
    DATE_TRUNC('month', c.created_date) AS month,
    c.acquisition_channel AS channel,
    COUNT(DISTINCT c.customer_id) AS new_customers,
    SUM(c.initial_deal_value) AS total_revenue
  FROM customers c
  WHERE c.created_date >= '2024-01-01'
    AND c.customer_status = 'active'
  GROUP BY month, channel
),

cac_calculation AS (
  SELECT 
    mc.month,
    mc.channel,
    mc.direct_ad_spend + mc.tool_costs + mc.personnel_costs + mc.overhead_costs AS total_costs,
    ca.new_customers,
    ca.total_revenue,
    
    -- CAC Calculation
    CASE 
      WHEN ca.new_customers > 0 
      THEN (mc.direct_ad_spend + mc.tool_costs + mc.personnel_costs + mc.overhead_costs) / ca.new_customers
      ELSE NULL 
    END AS cac,
    
    -- Blended CAC (includes all channels)
    SUM(mc.direct_ad_spend + mc.tool_costs + mc.personnel_costs + mc.overhead_costs) OVER (PARTITION BY mc.month) / 
    SUM(ca.new_customers) OVER (PARTITION BY mc.month) AS blended_cac,
    
    -- Revenue per customer
    CASE 
      WHEN ca.new_customers > 0 
      THEN ca.total_revenue / ca.new_customers
      ELSE NULL 
    END AS revenue_per_customer
    
  FROM marketing_costs mc
  LEFT JOIN customers_acquired ca ON mc.month = ca.month AND mc.channel = ca.channel
)

SELECT 
  month,
  channel,
  total_costs,
  new_customers,
  ROUND(cac, 2) AS channel_cac,
  ROUND(blended_cac, 2) AS blended_cac,
  ROUND(revenue_per_customer, 2) AS revenue_per_customer,
  ROUND(revenue_per_customer / NULLIF(cac, 0), 2) AS ltv_cac_ratio_estimate
FROM cac_calculation
WHERE new_customers > 0
ORDER BY month DESC, cac ASC;

-- Multi-Touch Attribution CAC Calculation
WITH customer_touchpoints AS (
  SELECT 
    t.customer_id,
    t.touchpoint_date,
    t.channel,
    t.campaign,
    c.conversion_date,
    c.deal_value,
    -- Calculate days between touchpoint and conversion
    EXTRACT(days FROM c.conversion_date - t.touchpoint_date) AS days_to_conversion,
    -- Time decay weight (more recent touchpoints get more credit)
    EXP(-0.1 * EXTRACT(days FROM c.conversion_date - t.touchpoint_date)) AS time_decay_weight,
    -- Linear attribution (equal weight to all touchpoints)
    1.0 / COUNT(*) OVER (PARTITION BY t.customer_id) AS linear_weight
  FROM touchpoints t
  JOIN customers c ON t.customer_id = c.customer_id
  WHERE t.touchpoint_date <= c.conversion_date
    AND c.conversion_date >= '2024-01-01'
),

attributed_revenue AS (
  SELECT 
    DATE_TRUNC('month', conversion_date) AS month,
    channel,
    campaign,
    
    -- First-touch attribution
    SUM(CASE WHEN ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY touchpoint_date) = 1 
             THEN deal_value ELSE 0 END) AS first_touch_revenue,
    
    -- Last-touch attribution  
    SUM(CASE WHEN ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY touchpoint_date DESC) = 1 
             THEN deal_value ELSE 0 END) AS last_touch_revenue,
    
    -- Linear multi-touch attribution
    SUM(deal_value * linear_weight) AS linear_attribution_revenue,
    
    -- Time-decay attribution
    SUM(deal_value * time_decay_weight / SUM(time_decay_weight) OVER (PARTITION BY customer_id)) AS time_decay_revenue,
    
    -- Customer counts for each attribution model
    COUNT(DISTINCT CASE WHEN ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY touchpoint_date) = 1 
                       THEN customer_id END) AS first_touch_customers,
    COUNT(DISTINCT CASE WHEN ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY touchpoint_date DESC) = 1 
                       THEN customer_id END) AS last_touch_customers,
    COUNT(DISTINCT customer_id) AS total_influenced_customers
    
  FROM customer_touchpoints
  GROUP BY month, channel, campaign
),

monthly_costs AS (
  SELECT 
    DATE_TRUNC('month', cost_date) AS month,
    channel,
    campaign,
    SUM(total_cost) AS total_spend
  FROM marketing_expenses
  WHERE cost_date >= '2024-01-01'
  GROUP BY month, channel, campaign
)

SELECT 
  c.month,
  c.channel,
  c.campaign,
  c.total_spend,
  
  -- CAC by attribution model
  ROUND(c.total_spend / NULLIF(r.first_touch_customers, 0), 2) AS first_touch_cac,
  ROUND(c.total_spend / NULLIF(r.last_touch_customers, 0), 2) AS last_touch_cac,
  ROUND(c.total_spend / NULLIF(r.total_influenced_customers, 0), 2) AS linear_attribution_cac,
  
  -- Revenue attribution comparison
  ROUND(r.first_touch_revenue, 2) AS first_touch_revenue,
  ROUND(r.last_touch_revenue, 2) AS last_touch_revenue,
  ROUND(r.linear_attribution_revenue, 2) AS linear_attribution_revenue,
  ROUND(r.time_decay_revenue, 2) AS time_decay_revenue,
  
  -- ROI by attribution model
  ROUND((r.first_touch_revenue - c.total_spend) / NULLIF(c.total_spend, 0) * 100, 1) AS first_touch_roi_pct,
  ROUND((r.linear_attribution_revenue - c.total_spend) / NULLIF(c.total_spend, 0) * 100, 1) AS linear_roi_pct

FROM monthly_costs c
LEFT JOIN attributed_revenue r ON c.month = r.month 
                              AND c.channel = r.channel 
                              AND c.campaign = r.campaign
WHERE r.first_touch_customers > 0 OR r.last_touch_customers > 0
ORDER BY c.month DESC, first_touch_cac ASC;