Sales Qualified Leads (SQL)

-- Calculate Sales Qualified Lead metrics and conversion rates
WITH sql_progression AS (
  SELECT 
    l.lead_id,
    l.email,
    l.created_date as lead_date,
    l.source,
    l.campaign_id,
    -- MQL qualification
    mql.mql_date,
    mql.mql_score,
    mql.is_mql,
    EXTRACT(days FROM AGE(mql.mql_date, l.created_date)) as days_to_mql,
    -- SQL qualification  
    o.opportunity_id,
    o.created_date as sql_date,
    o.owner_id as sales_rep_id,
    o.stage as current_stage,
    o.amount as deal_value,
    EXTRACT(days FROM AGE(o.created_date, mql.mql_date)) as days_mql_to_sql,
    EXTRACT(days FROM AGE(o.created_date, l.created_date)) as days_lead_to_sql,
    -- SQL qualification criteria scoring
    sq.budget_score,
    sq.authority_score,
    sq.need_score,
    sq.timeline_score,
    sq.total_qualification_score,
    sq.qualification_date,
    sq.qualification_notes,
    -- Outcome tracking
    CASE 
      WHEN o.opportunity_id IS NOT NULL THEN 1 
      ELSE 0 
    END as became_sql,
    CASE 
      WHEN o.stage = 'Closed Won' THEN 1 
      ELSE 0 
    END as became_customer,
    o.close_date,
    DATE_TRUNC('month', l.created_date) as lead_month,
    DATE_TRUNC('month', mql.mql_date) as mql_month,
    DATE_TRUNC('month', o.created_date) as sql_month
  FROM leads l
  LEFT JOIN mql_qualification mql ON l.lead_id = mql.lead_id
  LEFT JOIN opportunities o ON l.lead_id = o.lead_id
  LEFT JOIN sql_qualification sq ON o.opportunity_id = sq.opportunity_id
  WHERE l.created_date >= CURRENT_DATE - INTERVAL '12 months'
),
sql_metrics AS (
  SELECT 
    source,
    sql_month,
    -- Volume metrics
    COUNT(DISTINCT CASE WHEN is_mql = 1 THEN lead_id END) as total_mqls,
    COUNT(DISTINCT CASE WHEN became_sql = 1 THEN lead_id END) as total_sqls,
    COUNT(DISTINCT CASE WHEN became_customer = 1 THEN lead_id END) as total_customers,
    -- Conversion rates
    ROUND(100.0 * COUNT(DISTINCT CASE WHEN became_sql = 1 THEN lead_id END) / 
          NULLIF(COUNT(DISTINCT CASE WHEN is_mql = 1 THEN lead_id END), 0), 1) as mql_to_sql_rate,
    ROUND(100.0 * COUNT(DISTINCT CASE WHEN became_customer = 1 THEN lead_id END) / 
          NULLIF(COUNT(DISTINCT CASE WHEN became_sql = 1 THEN lead_id END), 0), 1) as sql_to_customer_rate,
    ROUND(100.0 * COUNT(DISTINCT CASE WHEN became_customer = 1 THEN lead_id END) / 
          NULLIF(COUNT(DISTINCT CASE WHEN is_mql = 1 THEN lead_id END), 0), 1) as mql_to_customer_rate,
    -- Velocity metrics
    ROUND(AVG(days_mql_to_sql), 1) as avg_days_mql_to_sql,
    ROUND(AVG(days_lead_to_sql), 1) as avg_days_lead_to_sql,
    -- Qualification scoring
    ROUND(AVG(total_qualification_score), 1) as avg_qualification_score,
    ROUND(AVG(CASE WHEN became_customer = 1 THEN total_qualification_score END), 1) as avg_winning_score,
    -- Revenue metrics
    ROUND(AVG(deal_value), 0) as avg_deal_value,
    SUM(CASE WHEN became_customer = 1 THEN deal_value ELSE 0 END) as total_revenue,
    ROUND(SUM(CASE WHEN became_customer = 1 THEN deal_value ELSE 0 END) / 
          NULLIF(COUNT(DISTINCT CASE WHEN became_sql = 1 THEN lead_id END), 0), 0) as revenue_per_sql
  FROM sql_progression
  WHERE sql_month IS NOT NULL  -- Focus on periods with SQL activity
  GROUP BY source, sql_month
),
sql_trends AS (
  SELECT 
    sm.*,
    LAG(mql_to_sql_rate) OVER (PARTITION BY source ORDER BY sql_month) as prev_month_conversion,
    LAG(avg_days_mql_to_sql) OVER (PARTITION BY source ORDER BY sql_month) as prev_month_velocity,
    RANK() OVER (PARTITION BY sql_month ORDER BY mql_to_sql_rate DESC) as conversion_rank,
    RANK() OVER (PARTITION BY sql_month ORDER BY revenue_per_sql DESC) as revenue_rank
  FROM sql_metrics sm
)
SELECT 
  source,
  sql_month,
  total_mqls,
  total_sqls,
  total_customers,
  mql_to_sql_rate,
  sql_to_customer_rate,
  mql_to_customer_rate,
  avg_days_mql_to_sql,
  avg_qualification_score,
  avg_winning_score,
  avg_deal_value,
  revenue_per_sql,
  total_revenue,
  ROUND(mql_to_sql_rate - COALESCE(prev_month_conversion, 0), 1) as conversion_change,
  ROUND(avg_days_mql_to_sql - COALESCE(prev_month_velocity, 0), 1) as velocity_change,
  conversion_rank,
  revenue_rank
FROM sql_trends
WHERE total_mqls >= 10  -- Minimum sample size
ORDER BY sql_month DESC, mql_to_sql_rate DESC;

-- Analyze SQL qualification criteria effectiveness
WITH qualification_scoring AS (
  SELECT 
    sp.lead_id,
    sp.opportunity_id,
    sp.source,
    sp.sql_date,
    sp.deal_value,
    sp.became_customer,
    sp.current_stage,
    -- BANT scoring components
    sq.budget_score,
    sq.authority_score, 
    sq.need_score,
    sq.timeline_score,
    sq.total_qualification_score,
    -- Score categorization
    CASE 
      WHEN sq.budget_score >= 8 THEN 'Strong Budget'
      WHEN sq.budget_score >= 6 THEN 'Moderate Budget'
      WHEN sq.budget_score >= 4 THEN 'Weak Budget'
      ELSE 'No Budget Confirmed'
    END as budget_category,
    CASE 
      WHEN sq.authority_score >= 8 THEN 'Decision Maker'
      WHEN sq.authority_score >= 6 THEN 'Strong Influence'
      WHEN sq.authority_score >= 4 THEN 'Some Influence'
      ELSE 'Limited Authority'
    END as authority_category,
    CASE 
      WHEN sq.need_score >= 8 THEN 'Critical Need'
      WHEN sq.need_score >= 6 THEN 'Important Need'
      WHEN sq.need_score >= 4 THEN 'Nice to Have'
      ELSE 'Unclear Need'
    END as need_category,
    CASE 
      WHEN sq.timeline_score >= 8 THEN 'Immediate (0-3 months)'
      WHEN sq.timeline_score >= 6 THEN 'Near-term (3-6 months)'
      WHEN sq.timeline_score >= 4 THEN 'Medium-term (6-12 months)'
      ELSE 'Long-term (12+ months)'
    END as timeline_category
  FROM sql_progression sp
  JOIN sql_qualification sq ON sp.opportunity_id = sq.opportunity_id
  WHERE sp.became_sql = 1
),
criteria_performance AS (
  SELECT 
    budget_category,
    authority_category,
    need_category,
    timeline_category,
    COUNT(*) as sql_count,
    ROUND(100.0 * SUM(became_customer) / COUNT(*), 1) as win_rate,
    ROUND(AVG(deal_value), 0) as avg_deal_value,
    ROUND(AVG(total_qualification_score), 1) as avg_total_score,
    SUM(CASE WHEN became_customer = 1 THEN deal_value ELSE 0 END) as total_revenue
  FROM qualification_scoring
  GROUP BY budget_category, authority_category, need_category, timeline_category
  HAVING COUNT(*) >= 5  -- Minimum sample size
),
score_effectiveness AS (
  SELECT 
    CASE 
      WHEN total_qualification_score >= 32 THEN 'Excellent (32-40)'
      WHEN total_qualification_score >= 28 THEN 'Very Good (28-31)'
      WHEN total_qualification_score >= 24 THEN 'Good (24-27)'
      WHEN total_qualification_score >= 20 THEN 'Fair (20-23)'
      ELSE 'Poor (< 20)'
    END as score_range,
    COUNT(*) as sql_count,
    ROUND(100.0 * SUM(became_customer) / COUNT(*), 1) as win_rate,
    ROUND(AVG(deal_value), 0) as avg_deal_value,
    ROUND(SUM(CASE WHEN became_customer = 1 THEN deal_value ELSE 0 END) / COUNT(*), 0) as revenue_per_sql
  FROM qualification_scoring
  GROUP BY 1
  ORDER BY MIN(total_qualification_score) DESC
),
rep_performance AS (
  SELECT 
    sp.sales_rep_id,
    u.rep_name,
    COUNT(*) as total_sqls,
    ROUND(100.0 * SUM(sp.became_customer) / COUNT(*), 1) as win_rate,
    ROUND(AVG(qs.total_qualification_score), 1) as avg_qualification_score,
    ROUND(AVG(sp.deal_value), 0) as avg_deal_value,
    SUM(CASE WHEN sp.became_customer = 1 THEN sp.deal_value ELSE 0 END) as total_revenue,
    -- Score distribution
    ROUND(AVG(qs.budget_score), 1) as avg_budget_score,
    ROUND(AVG(qs.authority_score), 1) as avg_authority_score,
    ROUND(AVG(qs.need_score), 1) as avg_need_score,
    ROUND(AVG(qs.timeline_score), 1) as avg_timeline_score
  FROM sql_progression sp
  JOIN qualification_scoring qs ON sp.opportunity_id = qs.opportunity_id
  JOIN users u ON sp.sales_rep_id = u.user_id
  WHERE sp.became_sql = 1
  GROUP BY sp.sales_rep_id, u.rep_name
  HAVING COUNT(*) >= 10  -- Minimum sample size
  ORDER BY win_rate DESC
)
SELECT 
  'Score Range Analysis' as analysis_type,
  score_range as category,
  sql_count,
  win_rate,
  avg_deal_value,
  revenue_per_sql as metric_value
FROM score_effectiveness
UNION ALL
SELECT 
  'Rep Performance' as analysis_type,
  CONCAT(rep_name, ' (', total_sqls, ' SQLs)') as category,
  total_sqls as sql_count,
  win_rate,
  avg_deal_value,
  total_revenue as metric_value
FROM rep_performance
ORDER BY analysis_type, win_rate DESC;

-- Analyze SQL pipeline progression and stage velocity
WITH pipeline_stages AS (
  SELECT 
    sp.opportunity_id,
    sp.lead_id,
    sp.source,
    sp.sql_date,
    sp.deal_value,
    sp.current_stage,
    sp.became_customer,
    sp.close_date,
    -- Stage progression tracking
    sh.stage_name,
    sh.stage_entered_date,
    sh.stage_exited_date,
    sh.days_in_stage,
    sh.stage_order,
    -- Calculate cumulative time
    SUM(sh.days_in_stage) OVER (
      PARTITION BY sp.opportunity_id 
      ORDER BY sh.stage_order
    ) as cumulative_days,
    -- Identify where deals get stuck
    CASE 
      WHEN sh.days_in_stage > 30 AND sh.stage_name != 'Closed Won' AND sh.stage_name != 'Closed Lost' THEN 1
      ELSE 0
    END as stuck_in_stage
  FROM sql_progression sp
  JOIN stage_history sh ON sp.opportunity_id = sh.opportunity_id
  WHERE sp.became_sql = 1
    AND sh.stage_entered_date >= sp.sql_date
),
stage_analysis AS (
  SELECT 
    stage_name,
    COUNT(DISTINCT opportunity_id) as opportunities_reached,
    ROUND(AVG(days_in_stage), 1) as avg_days_in_stage,
    ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY days_in_stage), 1) as median_days_in_stage,
    ROUND(PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY days_in_stage), 1) as p90_days_in_stage,
    COUNT(CASE WHEN stuck_in_stage = 1 THEN 1 END) as stuck_opportunities,
    ROUND(100.0 * COUNT(CASE WHEN stuck_in_stage = 1 THEN 1 END) / COUNT(*), 1) as stuck_rate,
    -- Conversion from this stage
    SUM(CASE WHEN stage_name != 'Closed Lost' AND 
               EXISTS(SELECT 1 FROM pipeline_stages ps2 
                     WHERE ps2.opportunity_id = pipeline_stages.opportunity_id 
                     AND ps2.stage_order > pipeline_stages.stage_order) THEN 1 ELSE 0 END) as progressed_count,
    ROUND(100.0 * SUM(CASE WHEN stage_name != 'Closed Lost' AND 
                             EXISTS(SELECT 1 FROM pipeline_stages ps2 
                                   WHERE ps2.opportunity_id = pipeline_stages.opportunity_id 
                                   AND ps2.stage_order > pipeline_stages.stage_order) THEN 1 ELSE 0 END) / 
          COUNT(DISTINCT opportunity_id), 1) as stage_conversion_rate
  FROM pipeline_stages
  WHERE stage_name NOT IN ('Closed Won', 'Closed Lost')
  GROUP BY stage_name, stage_order
  ORDER BY stage_order
),
sql_cohort_analysis AS (
  SELECT 
    source,
    DATE_TRUNC('month', sql_date) as sql_cohort,
    COUNT(DISTINCT opportunity_id) as cohort_size,
    -- 30-day outcomes
    SUM(CASE WHEN close_date <= sql_date + INTERVAL '30 days' AND became_customer = 1 THEN 1 ELSE 0 END) as month_1_wins,
    SUM(CASE WHEN current_stage IN ('Proposal', 'Negotiation', 'Closed Won') AND 
                  CURRENT_DATE <= sql_date + INTERVAL '30 days' THEN 1 ELSE 0 END) as month_1_active,
    -- 90-day outcomes  
    SUM(CASE WHEN close_date <= sql_date + INTERVAL '90 days' AND became_customer = 1 THEN 1 ELSE 0 END) as month_3_wins,
    SUM(CASE WHEN current_stage IN ('Proposal', 'Negotiation', 'Closed Won') AND 
                  CURRENT_DATE <= sql_date + INTERVAL '90 days' THEN 1 ELSE 0 END) as month_3_active,
    -- 180-day outcomes
    SUM(CASE WHEN close_date <= sql_date + INTERVAL '180 days' AND became_customer = 1 THEN 1 ELSE 0 END) as month_6_wins,
    SUM(CASE WHEN current_stage IN ('Proposal', 'Negotiation', 'Closed Won') AND 
                  CURRENT_DATE <= sql_date + INTERVAL '180 days' THEN 1 ELSE 0 END) as month_6_active,
    -- Revenue outcomes
    SUM(CASE WHEN became_customer = 1 THEN deal_value ELSE 0 END) as total_revenue,
    ROUND(AVG(deal_value), 0) as avg_deal_value
  FROM sql_progression
  WHERE became_sql = 1
    AND sql_date >= CURRENT_DATE - INTERVAL '12 months'
  GROUP BY source, DATE_TRUNC('month', sql_date)
  HAVING COUNT(DISTINCT opportunity_id) >= 5  -- Minimum cohort size
)
SELECT 
  source,
  sql_cohort,
  cohort_size,
  month_1_wins,
  month_3_wins,
  month_6_wins,
  ROUND(100.0 * month_1_wins / cohort_size, 1) as month_1_win_rate,
  ROUND(100.0 * month_3_wins / cohort_size, 1) as month_3_win_rate,
  ROUND(100.0 * month_6_wins / cohort_size, 1) as month_6_win_rate,
  ROUND(total_revenue / cohort_size, 0) as revenue_per_sql,
  avg_deal_value
FROM sql_cohort_analysis
ORDER BY sql_cohort DESC, month_6_win_rate DESC;