SELECT 
  campaign_name,
  SUM(impressions) as impressions,
  SUM(clicks) as clicks,
  ROUND(100.0 * SUM(clicks) / NULLIF(SUM(impressions), 0), 2) as ctr,
  SUM(conversions) as conversions,
  ROUND(100.0 * SUM(conversions) / NULLIF(SUM(clicks), 0), 2) as conversion_rate,
  SUM(spend) as spend,
  SUM(revenue) as revenue,
  ROUND(SUM(revenue) / NULLIF(SUM(spend), 0), 2) as roas
 FROM campaign_metrics
 WHERE date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
 GROUP BY campaign_name
 ORDER BY revenue DESC;

WITH first_touch AS (
  SELECT 
  customer_id,
  MIN(touchpoint_date) as first_touch_date
  FROM marketing_touchpoints
  GROUP BY customer_id
 )
 SELECT 
  t.channel,
  t.campaign,
  COUNT(DISTINCT c.customer_id) as conversions,
  SUM(c.revenue) as attributed_revenue
 FROM first_touch ft
 INNER JOIN marketing_touchpoints t ON ft.customer_id = t.customer_id 
  AND ft.first_touch_date = t.touchpoint_date
 INNER JOIN conversions c ON ft.customer_id = c.customer_id
 GROUP BY t.channel, t.campaign
 ORDER BY conversions DESC;

WITH funnel_data AS (
  SELECT 
  user_id,
  MAX(CASE WHEN event = 'page_view' THEN 1 ELSE 0 END) as visited,
  MAX(CASE WHEN event = 'signup' THEN 1 ELSE 0 END) as signed_up,
  MAX(CASE WHEN event = 'trial_start' THEN 1 ELSE 0 END) as started_trial,
  MAX(CASE WHEN event = 'purchase' THEN 1 ELSE 0 END) as purchased
  FROM events
  WHERE event_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
  GROUP BY user_id
 )
 SELECT 
  SUM(visited) as visitors,
  SUM(signed_up) as signups,
  ROUND(100.0 * SUM(signed_up) / NULLIF(SUM(visited), 0), 1) as signup_rate,
  SUM(started_trial) as trials,
  ROUND(100.0 * SUM(started_trial) / NULLIF(SUM(signed_up), 0), 1) as trial_rate,
  SUM(purchased) as purchases,
  ROUND(100.0 * SUM(purchased) / NULLIF(SUM(started_trial), 0), 1) as purchase_rate
 FROM funnel_data;

SELECT 
  campaign_name,
  segment,
  COUNT(*) as sent,
  SUM(opened) as opens,
  ROUND(100.0 * SUM(opened) / COUNT(*), 2) as open_rate,
  SUM(clicked) as clicks,
  ROUND(100.0 * SUM(clicked) / NULLIF(SUM(opened), 0), 2) as click_to_open_rate,
  SUM(converted) as conversions,
  SUM(revenue) as revenue
 FROM email_sends
 WHERE send_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
 GROUP BY campaign_name, segment
 ORDER BY campaign_name, open_rate DESC;