Return on Ad Spend (ROAS)

-- Calculate ROAS across channels and campaigns
WITH campaign_performance AS (
  SELECT 
    campaign_id,
    campaign_name,
    channel,
    DATE_TRUNC('month', ad_date) as month,
    SUM(ad_spend) as total_spend,
    SUM(impressions) as total_impressions,
    SUM(clicks) as total_clicks,
    SUM(conversions) as total_conversions
  FROM advertising_data
  WHERE ad_date >= '2024-01-01'
  GROUP BY 1, 2, 3, 4
),
attributed_revenue AS (
  SELECT 
    ar.campaign_id,
    ar.channel,
    DATE_TRUNC('month', ar.conversion_date) as month,
    SUM(ar.revenue) as attributed_revenue,
    COUNT(DISTINCT ar.customer_id) as unique_customers,
    AVG(ar.revenue) as avg_revenue_per_conversion
  FROM ad_revenue_attribution ar
  WHERE ar.conversion_date >= '2024-01-01'
    AND ar.attribution_window <= 30  -- 30-day attribution window
  GROUP BY 1, 2, 3
),
roas_analysis AS (
  SELECT 
    cp.month,
    cp.channel,
    cp.campaign_name,
    cp.total_spend,
    cp.total_impressions,
    cp.total_clicks,
    cp.total_conversions,
    COALESCE(ar.attributed_revenue, 0) as attributed_revenue,
    COALESCE(ar.unique_customers, 0) as customers_acquired,
    ROUND(cp.total_spend / NULLIF(cp.total_clicks, 0), 2) as cost_per_click,
    ROUND(cp.total_spend / NULLIF(cp.total_conversions, 0), 2) as cost_per_conversion,
    ROUND(COALESCE(ar.attributed_revenue, 0) / NULLIF(cp.total_spend, 0), 2) as roas,
    ROUND(100.0 * cp.total_clicks / NULLIF(cp.total_impressions, 0), 2) as ctr,
    ROUND(100.0 * cp.total_conversions / NULLIF(cp.total_clicks, 0), 2) as conversion_rate
  FROM campaign_performance cp
  LEFT JOIN attributed_revenue ar ON cp.campaign_id = ar.campaign_id 
    AND cp.channel = ar.channel 
    AND cp.month = ar.month
)
SELECT 
  month,
  channel,
  campaign_name,
  total_spend,
  attributed_revenue,
  customers_acquired,
  roas,
  cost_per_click,
  cost_per_conversion,
  ctr,
  conversion_rate,
  RANK() OVER (PARTITION BY month ORDER BY roas DESC) as roas_rank
FROM roas_analysis
WHERE total_spend > 0
ORDER BY month DESC, roas DESC;

-- Calculate ROAS with multi-touch attribution
WITH customer_journeys AS (
  SELECT 
    customer_id,
    conversion_date,
    total_revenue,
    STRING_AGG(
      CONCAT(channel, ':', campaign_id), 
      ' -> ' 
      ORDER BY touchpoint_date
    ) as journey_path,
    COUNT(*) as touchpoint_count
  FROM customer_attribution_path
  WHERE conversion_date >= '2024-01-01'
  GROUP BY 1, 2, 3
),
touchpoint_attribution AS (
  SELECT 
    cap.campaign_id,
    cap.channel,
    cap.customer_id,
    cap.conversion_date,
    cj.total_revenue,
    cj.touchpoint_count,
    -- Linear attribution: equal weight to all touchpoints
    cj.total_revenue / cj.touchpoint_count as linear_attributed_revenue,
    -- Time decay: more weight to recent touchpoints
    cj.total_revenue * POWER(0.7, cj.touchpoint_count - cap.touchpoint_position) as time_decay_attributed_revenue,
    -- First-touch attribution
    CASE WHEN cap.touchpoint_position = 1 THEN cj.total_revenue ELSE 0 END as first_touch_attributed_revenue,
    -- Last-touch attribution
    CASE WHEN cap.touchpoint_position = cj.touchpoint_count THEN cj.total_revenue ELSE 0 END as last_touch_attributed_revenue
  FROM customer_attribution_path cap
  JOIN customer_journeys cj ON cap.customer_id = cj.customer_id 
    AND cap.conversion_date = cj.conversion_date
),
attribution_roas AS (
  SELECT 
    ta.channel,
    ta.campaign_id,
    DATE_TRUNC('month', ta.conversion_date) as month,
    SUM(ta.linear_attributed_revenue) as linear_revenue,
    SUM(ta.time_decay_attributed_revenue) as time_decay_revenue,
    SUM(ta.first_touch_attributed_revenue) as first_touch_revenue,
    SUM(ta.last_touch_attributed_revenue) as last_touch_revenue,
    COUNT(DISTINCT ta.customer_id) as attributed_customers
  FROM touchpoint_attribution ta
  GROUP BY 1, 2, 3
),
campaign_costs AS (
  SELECT 
    campaign_id,
    channel,
    DATE_TRUNC('month', ad_date) as month,
    SUM(ad_spend) as total_spend
  FROM advertising_data
  WHERE ad_date >= '2024-01-01'
  GROUP BY 1, 2, 3
)
SELECT 
  cc.month,
  cc.channel,
  cc.campaign_id,
  cc.total_spend,
  ar.attributed_customers,
  ROUND(ar.linear_revenue / NULLIF(cc.total_spend, 0), 2) as linear_roas,
  ROUND(ar.time_decay_revenue / NULLIF(cc.total_spend, 0), 2) as time_decay_roas,
  ROUND(ar.first_touch_revenue / NULLIF(cc.total_spend, 0), 2) as first_touch_roas,
  ROUND(ar.last_touch_revenue / NULLIF(cc.total_spend, 0), 2) as last_touch_roas,
  ROUND(cc.total_spend / NULLIF(ar.attributed_customers, 0), 2) as customer_acquisition_cost
FROM campaign_costs cc
JOIN attribution_roas ar ON cc.campaign_id = ar.campaign_id 
  AND cc.channel = ar.channel 
  AND cc.month = ar.month
ORDER BY cc.month DESC, linear_roas DESC;

-- Calculate ROAS including customer lifetime value
WITH customer_ltv AS (
  SELECT 
    customer_id,
    first_purchase_date,
    acquisition_channel,
    acquisition_campaign_id,
    SUM(revenue) as total_revenue,
    COUNT(DISTINCT order_date) as order_count,
    MAX(order_date) as last_purchase_date,
    EXTRACT(days FROM AGE(MAX(order_date), first_purchase_date)) as customer_lifespan_days
  FROM customer_orders
  WHERE first_purchase_date >= '2023-01-01'
  GROUP BY 1, 2, 3, 4
),
predicted_ltv AS (
  SELECT 
    customer_id,
    total_revenue,
    customer_lifespan_days,
    -- Simple LTV prediction based on current behavior
    CASE 
      WHEN customer_lifespan_days > 0 THEN 
        total_revenue * (365.0 / customer_lifespan_days) * 2  -- Assume 2-year lifetime
      ELSE total_revenue 
    END as predicted_lifetime_value
  FROM customer_ltv
),
ltv_roas_analysis AS (
  SELECT 
    cltv.acquisition_channel,
    cltv.acquisition_campaign_id,
    DATE_TRUNC('month', cltv.first_purchase_date) as acquisition_month,
    COUNT(DISTINCT cltv.customer_id) as customers_acquired,
    SUM(cltv.total_revenue) as actual_revenue_to_date,
    SUM(pltv.predicted_lifetime_value) as predicted_total_ltv,
    AVG(cltv.total_revenue) as avg_revenue_per_customer,
    AVG(pltv.predicted_lifetime_value) as avg_predicted_ltv
  FROM customer_ltv cltv
  JOIN predicted_ltv pltv ON cltv.customer_id = pltv.customer_id
  GROUP BY 1, 2, 3
),
acquisition_costs AS (
  SELECT 
    channel,
    campaign_id,
    DATE_TRUNC('month', ad_date) as month,
    SUM(ad_spend) as total_spend
  FROM advertising_data
  WHERE ad_date >= '2023-01-01'
  GROUP BY 1, 2, 3
)
SELECT 
  lra.acquisition_month,
  lra.acquisition_channel,
  lra.acquisition_campaign_id,
  ac.total_spend,
  lra.customers_acquired,
  lra.actual_revenue_to_date,
  lra.predicted_total_ltv,
  ROUND(ac.total_spend / NULLIF(lra.customers_acquired, 0), 2) as cac,
  ROUND(lra.actual_revenue_to_date / NULLIF(ac.total_spend, 0), 2) as current_roas,
  ROUND(lra.predicted_total_ltv / NULLIF(ac.total_spend, 0), 2) as predicted_ltv_roas,
  ROUND(lra.avg_predicted_ltv / NULLIF(ac.total_spend / lra.customers_acquired, 0), 2) as ltv_to_cac_ratio
FROM ltv_roas_analysis lra
JOIN acquisition_costs ac ON lra.acquisition_channel = ac.channel 
  AND lra.acquisition_campaign_id = ac.campaign_id 
  AND lra.acquisition_month = ac.month
ORDER BY lra.acquisition_month DESC, predicted_ltv_roas DESC;