Pipeline Velocity

-- Calculate comprehensive pipeline velocity metrics
WITH opportunity_metrics AS (
  SELECT 
    o.opportunity_id,
    o.created_date,
    o.close_date,
    o.amount,
    o.stage,
    o.probability,
    o.owner_id,
    o.source,
    a.industry,
    a.company_size,
    CASE 
      WHEN o.stage = 'Closed Won' THEN 1 
      WHEN o.stage = 'Closed Lost' THEN 0 
      ELSE NULL 
    END as is_won,
    CASE 
      WHEN o.stage IN ('Closed Won', 'Closed Lost') THEN 
        EXTRACT(days FROM AGE(o.close_date, o.created_date))
      ELSE NULL 
    END as cycle_length_days,
    DATE_TRUNC('month', o.created_date) as created_month,
    DATE_TRUNC('quarter', o.created_date) as created_quarter
  FROM opportunities o
  JOIN accounts a ON o.account_id = a.account_id
  WHERE o.created_date >= CURRENT_DATE - INTERVAL '12 months'
),
monthly_velocity AS (
  SELECT 
    created_month,
    COUNT(*) as total_opportunities,
    COUNT(CASE WHEN stage NOT IN ('Closed Won', 'Closed Lost') THEN 1 END) as open_opportunities,
    COUNT(CASE WHEN stage IN ('Closed Won', 'Closed Lost') THEN 1 END) as closed_opportunities,
    COUNT(CASE WHEN is_won = 1 THEN 1 END) as won_opportunities,
    ROUND(AVG(CASE WHEN is_won = 1 THEN amount END), 0) as avg_won_deal_size,
    ROUND(AVG(amount), 0) as avg_opportunity_size,
    ROUND(100.0 * COUNT(CASE WHEN is_won = 1 THEN 1 END) / 
      NULLIF(COUNT(CASE WHEN stage IN ('Closed Won', 'Closed Lost') THEN 1 END), 0), 1) as win_rate_percent,
    ROUND(AVG(cycle_length_days), 1) as avg_cycle_length_days,
    SUM(CASE WHEN is_won = 1 THEN amount ELSE 0 END) as won_revenue
  FROM opportunity_metrics
  GROUP BY created_month
),
velocity_calculation AS (
  SELECT 
    created_month,
    total_opportunities,
    open_opportunities,
    closed_opportunities,
    won_opportunities,
    avg_won_deal_size,
    avg_opportunity_size,
    win_rate_percent,
    avg_cycle_length_days,
    won_revenue,
    -- Pipeline Velocity = (Opportunities × Deal Size × Win Rate) ÷ Cycle Length
    CASE 
      WHEN avg_cycle_length_days > 0 AND win_rate_percent > 0 THEN
        ROUND((total_opportunities * avg_opportunity_size * (win_rate_percent / 100.0)) / avg_cycle_length_days, 0)
      ELSE 0 
    END as pipeline_velocity_per_day,
    CASE 
      WHEN avg_cycle_length_days > 0 AND win_rate_percent > 0 THEN
        ROUND((total_opportunities * avg_opportunity_size * (win_rate_percent / 100.0)) / (avg_cycle_length_days / 30.0), 0)
      ELSE 0 
    END as pipeline_velocity_per_month
  FROM monthly_velocity
  WHERE avg_cycle_length_days IS NOT NULL
)
SELECT 
  created_month,
  total_opportunities,
  won_opportunities,
  avg_won_deal_size,
  win_rate_percent,
  avg_cycle_length_days,
  pipeline_velocity_per_day,
  pipeline_velocity_per_month,
  won_revenue,
  LAG(pipeline_velocity_per_month) OVER (ORDER BY created_month) as prev_month_velocity,
  ROUND(((pipeline_velocity_per_month - LAG(pipeline_velocity_per_month) OVER (ORDER BY created_month)) / 
    NULLIF(LAG(pipeline_velocity_per_month) OVER (ORDER BY created_month), 0)) * 100, 1) as velocity_growth_percent,
  RANK() OVER (ORDER BY pipeline_velocity_per_month DESC) as velocity_rank
FROM velocity_calculation
WHERE created_month >= CURRENT_DATE - INTERVAL '12 months'
ORDER BY created_month DESC;

-- Analyze pipeline velocity by individual sales representatives
WITH rep_performance AS (
  SELECT 
    u.user_id,
    u.name as rep_name,
    u.team,
    DATE_TRUNC('quarter', om.created_date) as quarter,
    COUNT(*) as total_opportunities,
    COUNT(CASE WHEN om.is_won = 1 THEN 1 END) as won_opportunities,
    ROUND(AVG(CASE WHEN om.is_won = 1 THEN om.amount END), 0) as avg_won_deal_size,
    ROUND(AVG(om.amount), 0) as avg_opportunity_size,
    ROUND(100.0 * COUNT(CASE WHEN om.is_won = 1 THEN 1 END) / 
      NULLIF(COUNT(CASE WHEN om.stage IN ('Closed Won', 'Closed Lost') THEN 1 END), 0), 1) as win_rate,
    ROUND(AVG(om.cycle_length_days), 1) as avg_cycle_length,
    SUM(CASE WHEN om.is_won = 1 THEN om.amount ELSE 0 END) as total_revenue,
    COUNT(CASE WHEN om.stage NOT IN ('Closed Won', 'Closed Lost') THEN 1 END) as active_pipeline_count,
    SUM(CASE WHEN om.stage NOT IN ('Closed Won', 'Closed Lost') THEN om.amount ELSE 0 END) as active_pipeline_value
  FROM opportunity_metrics om
  JOIN users u ON om.owner_id = u.user_id
  WHERE om.created_date >= CURRENT_DATE - INTERVAL '12 months'
    AND u.role = 'Sales Rep'
  GROUP BY 1, 2, 3, 4
),
rep_velocity AS (
  SELECT 
    *,
    CASE 
      WHEN avg_cycle_length > 0 AND win_rate > 0 THEN
        ROUND((total_opportunities * avg_opportunity_size * (win_rate / 100.0)) / (avg_cycle_length / 30.0), 0)
      ELSE 0 
    END as monthly_velocity,
    CASE 
      WHEN avg_cycle_length > 0 AND win_rate > 0 THEN
        ROUND((active_pipeline_count * avg_opportunity_size * (win_rate / 100.0)) / (avg_cycle_length / 30.0), 0)
      ELSE 0 
    END as projected_monthly_velocity
  FROM rep_performance
  WHERE avg_cycle_length IS NOT NULL
),
team_benchmarks AS (
  SELECT 
    team,
    quarter,
    ROUND(AVG(monthly_velocity), 0) as team_avg_velocity,
    ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY monthly_velocity), 0) as team_median_velocity,
    ROUND(STDDEV(monthly_velocity), 0) as team_velocity_stddev,
    COUNT(*) as team_size
  FROM rep_velocity
  GROUP BY 1, 2
)
SELECT 
  rv.quarter,
  rv.rep_name,
  rv.team,
  rv.total_opportunities,
  rv.won_opportunities,
  rv.avg_won_deal_size,
  rv.win_rate,
  rv.avg_cycle_length,
  rv.monthly_velocity,
  rv.projected_monthly_velocity,
  rv.total_revenue,
  tb.team_avg_velocity,
  tb.team_median_velocity,
  ROUND(rv.monthly_velocity - tb.team_avg_velocity, 0) as velocity_vs_team_avg,
  CASE 
    WHEN rv.monthly_velocity >= tb.team_avg_velocity + tb.team_velocity_stddev THEN 'Above Average'
    WHEN rv.monthly_velocity <= tb.team_avg_velocity - tb.team_velocity_stddev THEN 'Below Average'
    ELSE 'Average'
  END as performance_tier,
  RANK() OVER (PARTITION BY rv.team, rv.quarter ORDER BY rv.monthly_velocity DESC) as team_rank,
  RANK() OVER (PARTITION BY rv.quarter ORDER BY rv.monthly_velocity DESC) as company_rank
FROM rep_velocity rv
JOIN team_benchmarks tb ON rv.team = tb.team AND rv.quarter = tb.quarter
WHERE rv.quarter >= DATE_TRUNC('quarter', CURRENT_DATE - INTERVAL '12 months')
ORDER BY rv.quarter DESC, rv.monthly_velocity DESC;

-- Analyze individual components contributing to velocity changes
WITH component_trends AS (
  SELECT 
    created_month,
    total_opportunities,
    avg_opportunity_size,
    win_rate_percent,
    avg_cycle_length_days,
    pipeline_velocity_per_month,
    LAG(total_opportunities) OVER (ORDER BY created_month) as prev_opportunities,
    LAG(avg_opportunity_size) OVER (ORDER BY created_month) as prev_deal_size,
    LAG(win_rate_percent) OVER (ORDER BY created_month) as prev_win_rate,
    LAG(avg_cycle_length_days) OVER (ORDER BY created_month) as prev_cycle_length,
    LAG(pipeline_velocity_per_month) OVER (ORDER BY created_month) as prev_velocity
  FROM velocity_calculation
  WHERE created_month >= CURRENT_DATE - INTERVAL '12 months'
),
component_impact AS (
  SELECT 
    created_month,
    total_opportunities,
    avg_opportunity_size,
    win_rate_percent,
    avg_cycle_length_days,
    pipeline_velocity_per_month,
    prev_velocity,
    pipeline_velocity_per_month - prev_velocity as velocity_change,
    ROUND(((total_opportunities - prev_opportunities) / NULLIF(prev_opportunities, 0)) * 100, 1) as opportunities_change_pct,
    ROUND(((avg_opportunity_size - prev_deal_size) / NULLIF(prev_deal_size, 0)) * 100, 1) as deal_size_change_pct,
    ROUND(win_rate_percent - prev_win_rate, 1) as win_rate_change_points,
    ROUND(((avg_cycle_length_days - prev_cycle_length) / NULLIF(prev_cycle_length, 0)) * 100, 1) as cycle_length_change_pct,
    -- Calculate impact of each component on velocity (holding others constant)
    CASE 
      WHEN prev_cycle_length > 0 AND prev_win_rate > 0 THEN
        ROUND(((total_opportunities * prev_deal_size * (prev_win_rate / 100.0)) / (prev_cycle_length / 30.0)) - prev_velocity, 0)
      ELSE 0 
    END as opportunities_impact,
    CASE 
      WHEN prev_cycle_length > 0 AND prev_win_rate > 0 THEN
        ROUND(((prev_opportunities * avg_opportunity_size * (prev_win_rate / 100.0)) / (prev_cycle_length / 30.0)) - prev_velocity, 0)
      ELSE 0 
    END as deal_size_impact,
    CASE 
      WHEN prev_cycle_length > 0 AND win_rate_percent > 0 THEN
        ROUND(((prev_opportunities * prev_deal_size * (win_rate_percent / 100.0)) / (prev_cycle_length / 30.0)) - prev_velocity, 0)
      ELSE 0 
    END as win_rate_impact,
    CASE 
      WHEN avg_cycle_length_days > 0 AND prev_win_rate > 0 THEN
        ROUND(((prev_opportunities * prev_deal_size * (prev_win_rate / 100.0)) / (avg_cycle_length_days / 30.0)) - prev_velocity, 0)
      ELSE 0 
    END as cycle_length_impact
  FROM component_trends
  WHERE prev_velocity IS NOT NULL
),
segment_velocity AS (
  SELECT 
    om.industry,
    om.company_size,
    om.source,
    COUNT(*) as opportunities,
    ROUND(AVG(om.amount), 0) as avg_deal_size,
    ROUND(100.0 * COUNT(CASE WHEN om.is_won = 1 THEN 1 END) / 
      NULLIF(COUNT(CASE WHEN om.stage IN ('Closed Won', 'Closed Lost') THEN 1 END), 0), 1) as win_rate,
    ROUND(AVG(om.cycle_length_days), 1) as avg_cycle_length,
    CASE 
      WHEN AVG(om.cycle_length_days) > 0 AND 
           COUNT(CASE WHEN om.is_won = 1 THEN 1 END) / 
           NULLIF(COUNT(CASE WHEN om.stage IN ('Closed Won', 'Closed Lost') THEN 1 END), 0) > 0 THEN
        ROUND((COUNT(*) * AVG(om.amount) * 
               (COUNT(CASE WHEN om.is_won = 1 THEN 1 END) / 
                NULLIF(COUNT(CASE WHEN om.stage IN ('Closed Won', 'Closed Lost') THEN 1 END), 0))) / 
               (AVG(om.cycle_length_days) / 30.0), 0)
      ELSE 0 
    END as segment_velocity
  FROM opportunity_metrics om
  WHERE om.created_date >= CURRENT_DATE - INTERVAL '6 months'
    AND om.stage IN ('Closed Won', 'Closed Lost')
  GROUP BY 1, 2, 3
  HAVING COUNT(*) >= 10  -- Minimum sample size
)
SELECT 
  industry,
  company_size,
  source,
  opportunities,
  avg_deal_size,
  win_rate,
  avg_cycle_length,
  segment_velocity,
  RANK() OVER (ORDER BY segment_velocity DESC) as velocity_rank,
  CASE 
    WHEN segment_velocity >= (SELECT PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY segment_velocity) FROM segment_velocity) THEN 'High Velocity'
    WHEN segment_velocity >= (SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY segment_velocity) FROM segment_velocity) THEN 'Medium Velocity'
    ELSE 'Low Velocity'
  END as velocity_tier
FROM segment_velocity
ORDER BY segment_velocity DESC;