Pipeline Forecast: weight the deals you have by the deals that actually close

Project at a glance

• Track: Data Analytics
• Difficulty: Beginner · 4/10
• Time: ~1.0 h for Part A · ~2.5 h more for the optional Part B
• You’ll use: pandas, numpy, pytest
• 💻 Local setup needed (no in-browser ▶ Run yet) — a one-time, free Python install, then you just run files. Start Here walks you through it.
• New to this? This is a beginner project — you can’t break anything. Never coded? You can still run and read every step — the writing is only the optional Part B. This one needs a one-time free setup on your computer; Start Here walks you through it.

The data in this project is synthetic — generated locally by data/make_data.py, with no external source. That is deliberate: to grade a forecast you need to know what the pipeline actually closes, and to isolate the segment-mix trap you need segments whose true conversion rates differ by a known amount — neither of which a confidential CRM export gives you. No signup, no downloads, no GPU.

Before you start

New to sales forecasting? Start here.

A sales pipeline is the set of open deals a team is working, each sitting in a stage: Lead → Qualified → Proposal → Negotiation → (closed won or lost). A deal in Negotiation is far likelier to close than one still a Lead.

The naive forecast adds up the dollar value of every open deal. That silently assumes every deal closes — a 100% win rate — so it overshoots wildly. The fix is a stage-weighted forecast: multiply each deal’s value by the historical win rate of the stage it’s in, then sum. That gives the pipeline’s expected value, not its face value.

A tiny example — four open deals, weighted by their stage’s win rate:

deal   value     stage         win rate    expected
A      $40,000   Lead          6%          $2,400
B      $30,000   Qualified     18%         $5,400
C      $50,000   Proposal      40%         $20,000
D      $20,000   Negotiation   70%         $14,000
                          naive sum $140,000   weighted $41,800

The naive number ($140k) is what you’d book if everything closed; the weighted number ($41.8k) is what you’d actually expect. Getting the win rates right — and, in Part B, realising one rate doesn’t fit every segment — is the whole job.

What you’ll learn: estimate win rates from closed history, weight the open pipeline, and check the forecast against what really closed — then discover why a single blended rate breaks when segments convert differently. Section 3 lists exactly what you’ll be able to do.

New to Python too? See Start Here — it starts from zero, and shows how to run this in your browser with nothing to install.

1. The Brief

You run RevOps and the board wants a number: how much of this pipeline will actually close? Sales says “$85M in the pipeline”; you know most of it never lands. You’ll build the stage-weighted forecast that turns face value into expected value — and then break it, by showing that one blended win rate badly mis-forecasts a business whose segments convert at very different rates.

Every sales org does some version of this, and the segment trap is a real and expensive one: a blended win rate, dominated by many small deals, systematically under-counts the few large high-converting accounts — so the forecast misses exactly the revenue that matters most. The skill is the same wherever you weight outcomes by historical base rates: forecasting, triage, risk.

2. Difficulty & time

Difficulty 4 / 10. One tool (pandas) but a real modelling judgment: estimate base rates from history, apply them as weights, and — the Part B decision — recognise when those rates must be conditioned on a second dimension. Calibrated against the ledger (G10): a peer of maverick-spend (4) and ab-test-readout (4), which likewise take a business question and add one conditional twist (a policy rule; a correction for multiple reads) rather than a pipeline or a model. It sits above the single-mechanic 3s (duplicate-invoices, cohort-retention), because the Part B segment-mix trap is a genuine design decision you can walk straight into, and below the 5s (mtouch-attribution) that require several modelling choices at once.

Time is separate from difficulty: Part A is about an hour; Part B is 2–3 hours of your own work. The scripts run in under a second — the time is comprehension.

3. What you’ll be able to do after

• Estimate win rates from closed history with a groupby and apply them as weights to an open pipeline.
• Build a stage-weighted forecast and explain why it’s the pipeline’s expected value, not its face value.
• Check a forecast against realized outcomes and quantify how far off the naive sum was.
• Diagnose the segment-mix trap — recognise when a single blended rate hides very different conversion by segment, and fix it with a segment × stage rate.

The finished result

By the end of Part A you’ll have turned a pipeline’s face value into the expected value a board can act on — three numbers side by side:

naive forecast (sum of every open deal):   $84,749,560   (4.3x what actually closed)
stage-weighted forecast (expected value):  $19,510,933   (within ~1.4% of actual)
what the pipeline actually closed:          $19,790,554

Weighting each open deal by its stage’s historical win rate turns a number that’s 4.3x too high into one within ~1.4% of what really closed.

4. Prereqs & time box

You can write a function and use a pandas DataFrame (groupby, map, a mean). No sales background needed — the primer above covers the vocabulary.

Where to write and run code

Two ways to run this project — pick either:

• In your browser (no install). If the project page shows a ▶ Run button, click it to run Part A right here — nothing to set up. Part A uses only pandas and numpy, which run in the browser.
• On your computer. The one-time setup — installing Python, downloading the code from the course site (no GitHub, no git), opening a terminal, creating a virtual environment, installing the requirements, and running Part A step by step — is covered once in Start Here. Come back here once python -c "import pandas" runs without error. Part B (writing your own solution) needs this local setup.

Time box

• Part A — Guided Build: 60–90 minutes. Hard cap.
• Part B — Your Turn: 2–4 hours. Hard cap.

5. Setup & data

The data is synthetic and generated locally — see the banner above and SOURCES.md for full provenance. One command creates both parts’ data:

python data/make_data.py

That writes four CSVs into data/ (git-ignored; regenerate anytime, byte-for-byte identical because the seed is fixed):

• Part A — history_a.csv (6,000 closed deals: stage, segment, value, won) and pipeline_a.csv (2,000 open deals: deal_id, stage, segment, value, age_days, eventual_won). The eventual_won column is the realized outcome, included so you can check the forecast.
• Part B — history_b.csv (30,000 closed) and pipeline_b.csv (6,000 open, no outcome column — the grader knows what they close to). Three segments with very different conversion.

6. Part A — Guided Build

You’ll forecast the 2,000-deal open pipeline. Run the whole thing, or (better) one # %% cell at a time — see Start Here:

python part_a/forecast.py

Prefer the browser? If this page has a ▶ Run button, click it instead of the command above — Part A runs in your browser with nothing to install. The STEP output and every checkpoint below are identical either way.

The full, commented script is in part_a/forecast.py. The spine:

Step 1 — The open pipeline. Count and total the open deals; note most are early-stage.

Checkpoint: 2,000 open deals worth ~$85M, weighted toward Lead/Qualified.

Step 2 — The naive forecast. Sum all open value.

Checkpoint: $84.7M — which assumes a 100% win rate everywhere.

Step 3 — Win rate per stage, from history. groupby("stage")["won"].mean(), then weight.

Checkpoint: rates climb ~7% (Lead) → ~74% (Negotiation); the stage-weighted forecast is $19.5M.

Step 4 — Grade against what actually closed.

Checkpoint: actual $19.8M. Naive overshoots 4.3×; the weighted forecast is within ~1.4%. Writes part_a/forecast_a.csv.

Why this and not that

• Why weight by stage at all. Face value answers “what if everything closes”; nobody’s pipeline does. The stage win rate is the cheapest possible model of “how likely is this one,” and it turns a 4×-wrong number into a board-ready one.
• Why learn rates from history, not guess them. A rep’s gut says “Proposal is basically closed”; the history says 40%. The whole point is to replace optimism with the base rate the data actually shows.
• Why check against realized outcomes. A forecast you never score is a horoscope. Comparing weighted vs actual (here, within ~1.4%) is what earns the method the right to be trusted next quarter — and it’s the discipline Part B makes you defend.

Functions you’ll meet (and where to read more)

Function	What it does here	Docs
df.groupby("stage")["won"].mean()	Win rate per stage from closed history	groupby
s.map(rate)	Attach each open deal’s stage win rate	map
(value * rate).sum()	The weighted (expected-value) forecast	sum
boolean mask df[mask]	Total the deals that actually won	boolean indexing

If something looks off

• STEP 1 and STEP 2 both print $84,749,560 — did it run twice, or is that a bug? Neither. The naive forecast is the sum of every open deal’s value, so it equals the STEP 1 pipeline total exactly. The two numbers matching is the point: assuming every deal closes just hands back the face value. STEP 3 is where the stage win rates finally discount it to the ~$19.5M you’d expect.
• A checkpoint number doesn’t match. The data is seeded, so the numbers are exact. Re-run python data/make_data.py (you may have skipped or edited it), then re-run Part A.
• Setup problems (Python not found, pip, FileNotFoundError, re-activating the venv): see the troubleshooting list in Start Here.

7. Part B — Your Turn

Part B is the optional “your turn” half — a guided fill-in-the-blank, not a blank page. You get a working starter (part_b/starter.py) that already handles the easy version; your job is to finish the # TODO it marks out. More open than Part A’s step-by-step, but you are never staring at an empty file. If you finished Part A and want to prove the skill, this is where it happens. New to Python? It’s completely fine to stop after Part A and come back later.

Same core skill, but now the business has three segments — SMB, Mid-Market, Enterprise — that convert very differently at the same stage, and Enterprise deals are far larger. Forecast the expected closed value of each (segment, stage) bucket for the 6,000-deal open pipeline. The starter (part_b/starter.py) uses Part A’s single blended stage rate and scores 0.00 — it can’t get the high-converting Enterprise segment right.

The transfer: in Part A one rate per stage was enough because segments behaved alike. Here a blended rate — dragged down by the many small SMB deals — under-forecasts Enterprise revenue by almost half. You need a win rate estimated per segment and stage.

Acceptance criteria (checkable)

Your solution writes into part_b/out/:

• forecast.csv — columns segment, stage, forecast (expected closed value per bucket), one row per (segment, stage).
• report.json — with keys buckets, total_forecast, achieved_metric (you can leave achieved_metric null; the grader scores you).

Then:

python part_b/starter.py
pytest part_b/test_solution.py -q

If pytest reports everything as skipped, it just means the output files don’t exist yet — run python part_b/starter.py first (once your solution fills in the TODOs), then re-run.

You’re done when the tests pass: at least 90% of the 12 buckets land within 5% of the true expected value (the reference reaches 1.00). The blended rate scores 0.00 — the segment × stage rate is what clears the floor.

Constraints — what must be true of your solution, not how to get there

• Estimate the win rate per (segment, stage), not per stage alone.
• Forecast each bucket as its open value summed, times that bucket’s win rate.
• Cover all 12 buckets (3 segments × 4 stages).
• Don’t fit the rate on the open pipeline — you don’t have its outcomes; learn it from history_b.csv.

Hints

Hint 1 — where the blended rate goes wrong

Compute the blended stage rate and the Enterprise-only stage rate and compare them. Enterprise converts far higher, so one blended rate under-forecasts every Enterprise bucket — and those are the biggest dollars.

Hint 2 — condition on both

history.groupby(["segment", "stage"])["won"].mean() gives a rate per (segment, stage). That’s the only change from Part A — group by two keys, not one.

Hint 3 — apply per bucket (still not the code)

Group the open pipeline by ["segment", "stage"], and for each bucket multiply its summed value by that bucket’s rate. Write one row per bucket.

8. Self-check

You don’t need the answer key. You’re done when all of these hold:

• pytest reports bucket accuracy ≥ 0.90 (aim for 1.00 — the rates are tight given the history size).
• Your Enterprise forecast is clearly higher than what the blended rate gave you (you stopped under-counting the segment that converts best).
• Re-running from python data/make_data.py reproduces the same numbers — the data is seeded.

And the tell-tale: your total forecast should rise substantially when you move from blended to segment × stage (here from ~$81M to ~$127M). If it barely moved, you’re still applying one rate to every segment.

9. Stretch

• Confidence, not just a point. Bootstrap the win rates (resample the history) and report a forecast range per segment, so the board sees the uncertainty, not just a single number.
• Age decay. A deal stuck in Negotiation for 200 days is not as live as a fresh one. Fold age_days into the weight (older deals discounted) and measure whether it improves the fit.
• (Genuinely hard) Sparse buckets. Add a fourth, tiny segment with only a handful of closed deals per stage, so its raw win rates are noisy. Shrink them toward the blended rate (partial pooling) and show the forecast is steadier than either extreme — the beginnings of a hierarchical estimate.

10. Ship it

Put this in a portfolio repo and it counts. In that repo’s README:

• State the finding in one sentence a CRO gets: “the blended forecast under-called Enterprise by 46%; segment × stage weighting puts the pipeline at ~$127M, not ~$81M.”
• Show the naive vs weighted vs actual comparison from Part A, and the blended vs segment × stage gap from Part B, in dollars.
• Include the segment × stage rate table and one paragraph on why a single blended rate hides the segment that matters most. That paragraph is the part that signals you understand base rates, not just groupby.

Keep make_data.py and the tests in the repo so anyone can reproduce your numbers from zero.

11. Sources

Rendered from SOURCES.md.

Field	Value
Dataset	Synthetic B2B sales pipeline: closed history + open snapshot (Part A 6,000/2,000; Part B 30,000/6,000 deals)
Type	Synthetic
Generated by	data/make_data.py (this repo), seeds 20260724 (Part A) and 20260725 (Part B)
Source URL	Not applicable — generated, not fetched
License	Not applicable — synthetic, no third-party rights
Access / generation date	2026-07-18
Redistribution	Permitted — synthetic; output regenerated from the seeded script, never committed
robots.txt checked	Not applicable — no scraping
ToS URL	Not applicable — no external source

Synthetic data teaches the mechanic; it does not prove a finding about the real world. The per-segment conversion gap was injected on purpose — recovering it demonstrates the technique, not a claim about any real company’s win rates.

Next up

Finished this one? Continue the Data Analytics track:

Multi-Touch Attribution: last click lies about who earned the sale →  ·  Browse all courses