Pipeline Probability

Deal probability (win likelihood percentage per stage) is used in revenue forecast reports. This page documents how it works and what to build when a spec requires it.

Does a probability column exist?

The deal_stages table may or may not have a probability column depending on whether it was added via migration. Always check before writing queries:

SELECT column_name
FROM information_schema.columns
WHERE table_name = 'deal_stages'
  AND column_name = 'probability';
-- If 0 rows returned, column does not exist

If it exists, the value is an integer 0–100 representing the estimated win probability for deals in that stage.

If probability does not exist — add it

ALTER TABLE deal_stages
ADD COLUMN IF NOT EXISTS probability INT DEFAULT 0
  CHECK (probability >= 0 AND probability <= 100);

Then seed reasonable defaults:

-- Example defaults — adjust based on actual stage names
UPDATE deal_stages SET probability = 10
  WHERE name ILIKE '%new%' OR name ILIKE '%lead%';
UPDATE deal_stages SET probability = 30
  WHERE name ILIKE '%contact%' OR name ILIKE '%reach%';
UPDATE deal_stages SET probability = 60
  WHERE name ILIKE '%proposal%' OR name ILIKE '%quote%';
UPDATE deal_stages SET probability = 90
  WHERE name ILIKE '%negotiat%' OR name ILIKE '%review%';
UPDATE deal_stages SET probability = 100
  WHERE is_won = true;
UPDATE deal_stages SET probability = 0
  WHERE is_lost = true;

Revenue forecast query

Once probability exists:

SELECT
  ds.name AS stage,
  ds.probability,
  COUNT(d.id) AS deal_count,
  SUM(d.value) AS total_value,
  ROUND(SUM(d.value) * ds.probability / 100.0, 2) AS weighted_value
FROM deals d
JOIN deal_stages ds ON ds.id = d.id_stage
WHERE d.id_company = :id_company
  AND d.deleted_at IS NULL
  AND d.status = 'open'
GROUP BY ds.id, ds.name, ds.probability, ds.sort_order
ORDER BY ds.sort_order;

The weighted_value column is the forecast contribution from each stage — sum it for the total weighted pipeline forecast.

Important constraints

  • Probability values must be 0–100 integers — enforce this at both the DB constraint level and in any UI that edits stages
  • is_won = true stages must have probability = 100
  • is_lost = true stages must have probability = 0
  • Never use probability to filter deals — only use it for calculation. A deal in a 60% stage is still open, not 60% won.