Conversion Rate

-- Calculate conversion rates by traffic source
WITH daily_metrics AS (
  SELECT 
    date,
    traffic_source,
    page_type,
    SUM(visitors) as total_visitors,
    SUM(conversions) as total_conversions,
    SUM(revenue) as total_revenue
  FROM web_analytics
  WHERE date >= '2024-01-01'
  GROUP BY 1, 2, 3
)
SELECT 
  traffic_source,
  page_type,
  SUM(total_visitors) as visitors,
  SUM(total_conversions) as conversions,
  ROUND(100.0 * SUM(total_conversions) / NULLIF(SUM(total_visitors), 0), 2) as conversion_rate,
  ROUND(SUM(total_revenue) / NULLIF(SUM(total_conversions), 0), 2) as revenue_per_conversion
FROM daily_metrics
GROUP BY 1, 2
HAVING SUM(total_visitors) >= 100  -- Minimum traffic for statistical significance
ORDER BY conversion_rate DESC;

-- Analyze conversion funnel step-by-step
WITH funnel_steps AS (
  SELECT 
    user_id,
    session_id,
    MAX(CASE WHEN event_name = 'page_view' THEN 1 ELSE 0 END) as viewed_page,
    MAX(CASE WHEN event_name = 'add_to_cart' THEN 1 ELSE 0 END) as added_to_cart,
    MAX(CASE WHEN event_name = 'checkout_started' THEN 1 ELSE 0 END) as started_checkout,
    MAX(CASE WHEN event_name = 'purchase' THEN 1 ELSE 0 END) as completed_purchase
  FROM user_events
  WHERE event_date >= '2024-01-01'
  GROUP BY 1, 2
),
funnel_summary AS (
  SELECT 
    COUNT(*) as total_sessions,
    SUM(viewed_page) as page_views,
    SUM(added_to_cart) as cart_additions,
    SUM(started_checkout) as checkout_starts,
    SUM(completed_purchase) as purchases
  FROM funnel_steps
  WHERE viewed_page = 1
)
SELECT 
  'Page View' as step,
  page_views as count,
  100.0 as conversion_rate,
  0.0 as drop_off_rate
FROM funnel_summary

UNION ALL

SELECT 
  'Add to Cart' as step,
  cart_additions as count,
  ROUND(100.0 * cart_additions / page_views, 2) as conversion_rate,
  ROUND(100.0 * (page_views - cart_additions) / page_views, 2) as drop_off_rate
FROM funnel_summary

UNION ALL

SELECT 
  'Checkout Started' as step,
  checkout_starts as count,
  ROUND(100.0 * checkout_starts / page_views, 2) as conversion_rate,
  ROUND(100.0 * (cart_additions - checkout_starts) / cart_additions, 2) as drop_off_rate
FROM funnel_summary

UNION ALL

SELECT 
  'Purchase' as step,
  purchases as count,
  ROUND(100.0 * purchases / page_views, 2) as conversion_rate,
  ROUND(100.0 * (checkout_starts - purchases) / checkout_starts, 2) as drop_off_rate
FROM funnel_summary;

-- Track conversion rate trends over time
WITH daily_conversions AS (
  SELECT 
    DATE_TRUNC('day', event_date) as date,
    COUNT(DISTINCT session_id) as unique_sessions,
    COUNT(DISTINCT CASE WHEN event_name = 'conversion' THEN session_id END) as converting_sessions
  FROM user_events
  WHERE event_date >= CURRENT_DATE - INTERVAL '90 days'
  GROUP BY 1
),
conversion_trends AS (
  SELECT 
    date,
    unique_sessions,
    converting_sessions,
    ROUND(100.0 * converting_sessions / unique_sessions, 2) as daily_conversion_rate,
    AVG(100.0 * converting_sessions / unique_sessions) OVER (
      ORDER BY date 
      ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) as seven_day_avg_rate
  FROM daily_conversions
)
SELECT 
  date,
  unique_sessions,
  converting_sessions,
  daily_conversion_rate,
  ROUND(seven_day_avg_rate, 2) as seven_day_avg_rate,
  ROUND(daily_conversion_rate - LAG(daily_conversion_rate) OVER (ORDER BY date), 2) as day_over_day_change
FROM conversion_trends
ORDER BY date DESC;