conversion_funnel.sql

-- USED BY: automations/05-forecasting-suite (calibrates stage_rates) and 07-reporting-automation/weekly_business_review.py
-- Stage-to-stage conversion rates by segment, computed from the OpportunityHistory table.
-- Inputs: 12 months of opportunity history. Output: P(reach stage N+1 | reached stage N).

WITH stage_entries AS (
  SELECT
    oh.opportunity_id,
    o.account_segment,
    oh.new_stage_name AS stage,
    MIN(oh.created_date) AS entered_at
  FROM opportunity_history oh
  JOIN opportunities o ON o.id = oh.opportunity_id
  WHERE oh.created_date >= CURRENT_DATE - INTERVAL '365 days'
  GROUP BY oh.opportunity_id, o.account_segment, oh.new_stage_name
),
stage_pairs AS (
  SELECT
    se1.account_segment,
    se1.stage           AS from_stage,
    se2.stage           AS to_stage,
    COUNT(DISTINCT se1.opportunity_id) AS opps_in_from,
    COUNT(DISTINCT se2.opportunity_id) AS opps_in_to
  FROM stage_entries se1
  LEFT JOIN stage_entries se2
    ON se1.opportunity_id = se2.opportunity_id
   AND se2.entered_at > se1.entered_at
  GROUP BY 1, 2, 3
)
SELECT
  account_segment,
  from_stage,
  to_stage,
  opps_in_from,
  opps_in_to,
  ROUND(opps_in_to::numeric / NULLIF(opps_in_from, 0), 3) AS conversion_rate
FROM stage_pairs
WHERE from_stage IS NOT NULL AND to_stage IS NOT NULL
ORDER BY account_segment, from_stage, conversion_rate DESC;