Lead Conversion Rate

-- Calculate lead conversion rates by source
WITH lead_summary AS (
  SELECT 
    lead_source,
    acquisition_channel,
    DATE_TRUNC('month', created_date) as month,
    COUNT(*) as total_leads,
    COUNT(CASE WHEN status = 'customer' THEN 1 END) as converted_customers,
    COUNT(CASE WHEN status IN ('qualified', 'opportunity', 'customer') THEN 1 END) as qualified_leads,
    AVG(EXTRACT(days FROM AGE(COALESCE(converted_date, CURRENT_DATE), created_date))) as avg_lead_age_days
  FROM leads
  WHERE created_date >= '2024-01-01'
  GROUP BY 1, 2, 3
)
SELECT 
  lead_source,
  acquisition_channel,
  month,
  total_leads,
  qualified_leads,
  converted_customers,
  ROUND(100.0 * qualified_leads / total_leads, 2) as qualification_rate,
  ROUND(100.0 * converted_customers / total_leads, 2) as conversion_rate,
  ROUND(100.0 * converted_customers / NULLIF(qualified_leads, 0), 2) as qualified_to_customer_rate,
  ROUND(avg_lead_age_days, 1) as avg_conversion_time_days
FROM lead_summary
WHERE total_leads >= 10  -- Minimum volume for meaningful analysis
ORDER BY month DESC, conversion_rate DESC;

-- Analyze conversion rates at each funnel stage
WITH funnel_progression AS (
  SELECT 
    lead_id,
    lead_source,
    created_date,
    MAX(CASE WHEN stage = 'lead' THEN 1 ELSE 0 END) as reached_lead,
    MAX(CASE WHEN stage = 'mql' THEN 1 ELSE 0 END) as reached_mql,
    MAX(CASE WHEN stage = 'sql' THEN 1 ELSE 0 END) as reached_sql,
    MAX(CASE WHEN stage = 'opportunity' THEN 1 ELSE 0 END) as reached_opportunity,
    MAX(CASE WHEN stage = 'customer' THEN 1 ELSE 0 END) as reached_customer
  FROM lead_stage_history
  WHERE created_date >= CURRENT_DATE - INTERVAL '6 months'
  GROUP BY 1, 2, 3
),
stage_summary AS (
  SELECT 
    lead_source,
    COUNT(*) as total_leads,
    SUM(reached_lead) as leads,
    SUM(reached_mql) as mqls,
    SUM(reached_sql) as sqls,
    SUM(reached_opportunity) as opportunities,
    SUM(reached_customer) as customers
  FROM funnel_progression
  GROUP BY 1
)
SELECT 
  lead_source,
  total_leads,
  leads,
  mqls,
  sqls,
  opportunities,
  customers,
  ROUND(100.0 * mqls / leads, 1) as lead_to_mql_rate,
  ROUND(100.0 * sqls / mqls, 1) as mql_to_sql_rate,
  ROUND(100.0 * opportunities / sqls, 1) as sql_to_opp_rate,
  ROUND(100.0 * customers / opportunities, 1) as opp_to_customer_rate,
  ROUND(100.0 * customers / leads, 1) as overall_conversion_rate
FROM stage_summary
WHERE total_leads >= 20
ORDER BY overall_conversion_rate DESC;

-- Analyze lead conversion velocity by source
WITH lead_conversions AS (
  SELECT 
    lead_id,
    lead_source,
    created_date,
    converted_date,
    EXTRACT(days FROM AGE(converted_date, created_date)) as days_to_convert,
    deal_value
  FROM leads
  WHERE status = 'customer'
    AND converted_date >= CURRENT_DATE - INTERVAL '12 months'
    AND converted_date IS NOT NULL
),
conversion_metrics AS (
  SELECT 
    lead_source,
    COUNT(*) as converted_leads,
    ROUND(AVG(days_to_convert), 1) as avg_days_to_convert,
    ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY days_to_convert), 1) as median_days_to_convert,
    ROUND(AVG(deal_value), 2) as avg_deal_value,
    MIN(days_to_convert) as fastest_conversion,
    MAX(days_to_convert) as slowest_conversion
  FROM lead_conversions
  GROUP BY 1
)
SELECT 
  lead_source,
  converted_leads,
  avg_days_to_convert,
  median_days_to_convert,
  avg_deal_value,
  fastest_conversion,
  slowest_conversion,
  CASE 
    WHEN avg_days_to_convert <= 30 THEN 'Fast'
    WHEN avg_days_to_convert <= 90 THEN 'Medium'
    ELSE 'Slow'
  END as conversion_speed_category
FROM conversion_metrics
WHERE converted_leads >= 5
ORDER BY avg_days_to_convert;