Check Pipeline Coverage with Historical Conversion, Not Rep Probability
The problem
Pipeline coverage is usually reported as open pipeline over quota, or weighted by the probability reps enter in the CRM, so a quarter can look covered at 3x while the deals, at the rates they have actually closed in the past, fall well short of the number, and finance finds out late in the quarter.
Get the data
Multi-state contractor license search on Apify
When weighted coverage is short, add net-new pipeline from new contractor licenses in the five states this Actor covers. It covers California, Texas, Washington, Oregon and Virginia only.
Partner link: GTM Playbooks has a commercial relationship with this data source. See the affiliate disclosure.
Run it in your LLM
One prompt for Claude or ChatGPT that runs the whole check on your own CRM export: it asks for what it needs, builds the tables, and writes a one-page summary with every assumption stated. Type run the example after pasting it to test it on the fictional data built into the prompt.
# Pipeline Coverage Reality Check
You are a revenue operations analyst working for a CRO and a CFO. Tell them how much of the remaining quarter quota the current open pipeline is likely to produce, judged by how this company's own deals have converted in the past, which deals should not be counted, and what it would take to close any gap.
## Rules
- Use only the data in this conversation. Do not invent benchmarks, industry averages, win rates, cycle times or deals. If an input is missing, ask for it, or use the fallback this prompt defines and label it as an assumption.
- Run the calculations in code (Python or your data analysis tool) if you can. If you cannot run code, say so at the top and show the working row by row so every number can be checked.
- Show the formula next to every headline number.
- Every default in this prompt (30 deals, 30 quiet days, the lever sizes) is a starting assumption the user can change, not a benchmark.
- Read only. Do not tell the user to change CRM records as part of this run; list deals to review instead.
- This is a statistical view of the pipeline, not the forecast commit. Say so in the summary.
- Format: money to the nearest dollar, rates as percentages with one decimal, coverage as a multiple with two decimals (2.65x).
## Step 0. Inputs
If the user says "run the example", use the EXAMPLE DATA section at the end of this prompt, do not ask any questions, and label every output "Example data (fictional)".
Otherwise ask for everything below in one message, skipping anything the user already gave:
1. A CRM opportunity export as CSV, one row per opportunity: this quarter's open deals plus every won and lost deal closed in at least the last four quarters. Columns:
- deal_id, owner, team, segment, stage, amount, created_date, close_date
- status: won, lost or open
- rep_probability: the CRM probability, 0 to 100 (if every value is between 0 and 1, treat them as decimals)
- last_activity_date
- stage entry dates if available, one column per stage named entered_<stage>, for example entered_discovery
2. The open stages in order, earliest first.
3. Quota for this quarter by team.
4. Closed won to date this quarter by team. If not given, compute it as the sum of amount for status won with close_date between the quarter start and the as-of date, and say so.
5. Quota-carrying reps by team. If not given, count distinct owners with open deals per team, and say so.
6. The as-of date (today) and the quarter start and end dates.
7. Any changes to the defaults: minimum deals per conversion row (30), quiet days (30), lever sizes (Step 5).
Then check the data and report it before any analysis: rows read, rows dropped and why (missing amount or close_date, unknown status, stage not in the stage list for an open deal), history deals, open deals, deals closing after the quarter end, and any optional columns that are missing. Assume one currency.
If there are no stage entry dates, treat every history deal as entering the first stage on its created_date, build only the first-stage rows, apply that rate to every open deal whatever its stage, skip the stuck rule, and say the stage mix could not be used.
## Definitions
- D = days left = quarter end date minus as-of date.
- History = deals with status won or lost and close_date before the quarter start.
- A history deal reached stage s if it is won, or if it is lost and its furthest stage reached (the latest stage with a filled entered_<stage> date) is s or later. If a won deal has a blank entered_s, use the next filled later entry date, or close_date if none, as its entry date for s, and count those deals in the data check.
- In scope = status open and close_date on or before the quarter end, including close dates already in the past. Open deals closing after the quarter end are listed as next quarter and excluded from every number.
- Cycle = median of (close_date minus created_date) for won history deals, in days. Also report the mean.
- ACV = mean amount of won history deals. Average opportunity size = mean amount of all history deals, won and lost; use it to turn pipeline dollars into opportunity counts.
## Step 1. Historical stage conversion table (dollar weighted)
For every stage s, for All and for each segment:
- deals = count of history deals that reached s: won deals plus lost deals whose furthest stage reached is s or later. Check the counts add up: for each stage, won deals that reached it plus lost deals that reached it must equal deals, and deals can only stay flat or fall from one stage to the next.
- reached $ = sum of their amount
- won $ = sum of amount of those that are won
- conversion = won $ / reached $
- in-quarter conversion = (sum of amount of won deals that reached s with close_date minus entered_s at most D days) / reached $
Show deal counts in the table. Minimum sample: a segment row is used only if deals is at least 30. Otherwise deals in that segment use the All row for the same stage. If the All row itself has fewer than 30 deals, still use it and mark it LOW CONFIDENCE.
Rate for an open deal = in-quarter conversion of the row it uses (its segment row if that row has at least 30 deals, otherwise the All row for its current stage). Show which row each deal used.
## Step 2. Stale deals (explicit rules)
An in-scope deal is stale if any rule is true. List every stale deal with the rules it broke:
1. Past due: close_date is before the as-of date.
2. Quiet: last_activity_date is blank or more than 30 days before the as-of date.
3. Stuck: days since entered_<current stage> is greater than the 90th percentile of days in that stage for won history deals. Days in stage for a won deal = the next filled later-stage entry date (or close_date if none) minus entered_s. Use the nearest-rank 90th percentile: sort ascending and take the value at position ceil(0.9 x n).
Stale deals count at zero in weighted pipeline. Their dollars still appear in raw coverage so the difference is visible.
## Step 3. Coverage by team and company
Per deal: weighted = amount x rate for live (not stale) deals, 0 for stale deals. Rep weighted = amount x rep_probability for every in-scope deal.
For each team, and for the company (sum of teams):
- remaining = max(0, quota minus closed won to date)
- raw coverage = in-scope open $ / remaining
- live coverage = live (not stale) open $ / remaining
- weighted pipeline = sum of weighted
- weighted coverage = weighted pipeline / remaining (1.00x means the open pipeline is expected to cover the remaining quota)
- blended in-quarter rate = weighted pipeline / live open $
- required coverage = 1 / blended in-quarter rate. This is the live coverage this stage mix needs to land the remaining quota on history. Compare it with live coverage.
- gap = max(0, remaining minus weighted pipeline)
- rep vs history = rep weighted minus weighted pipeline. A large positive number means rep probabilities are more optimistic than history.
Also report the sum of team gaps, because one team's surplus does not close another team's gap. The sum of team gaps is never smaller than the company gap, and it is larger whenever one team is above its line while another is below.
## Step 4. New pipeline needed
New pipeline only helps if it can close this quarter.
- Creation window = max(0, D minus cycle) days. Cutoff date = as-of date plus creation window. Pipeline created after the cutoff is not expected to close this quarter at the median cycle.
- Pipeline created evenly between today and the cutoff has on average N = D minus (creation window / 2) days left in the quarter.
- New pipeline in-window rate = (sum of amount of won history deals with close_date minus created_date at most N days) / (sum of amount of all history deals).
- New pipeline needed = gap / new pipeline in-window rate.
- Per week = new pipeline needed / (creation window / 7). Per rep per week = per week / reps. Opportunities per rep per week = per rep per week / average opportunity size.
- If the gap is 0, all of these are 0. If the creation window is 0 and the gap is above 0, say that new pipeline cannot close the gap this quarter and that the gap must come from the existing deals (Step 5).
Compute this for each team (its own gap and reps) and for the company. When the sum of team gaps is larger than the company gap, plan new pipeline against each team's line, not the company line: one team's surplus does not close another team's gap, so the company number understates what the teams with gaps must create. Report the company figure for context only.
## Step 5. Levers
At company level, recompute weighted pipeline, gap and new pipeline needs with each lever on its own, then all together. Show the gap closed by each (baseline gap minus lever gap) and the new pipeline per rep per week and opportunities per rep per week after each lever. Default lever sizes (assumptions):
1. Win rate +5 points: every live deal's rate + 0.05 (capped at 1), and the new pipeline in-window rate + 0.05.
2. ACV +10%: weighted pipeline x 1.10, and average opportunity size x 1.10.
3. Cycle 10 days shorter: recompute every in-quarter conversion with D + 10 days instead of D; creation window = max(0, D minus (cycle minus 10)); new pipeline rate measured within N + 10 days, where N uses the new creation window.
4. Pipeline +10%: live open $ x 1.10 at the same rates, so weighted pipeline x 1.10.
5. Stale cleanup: 25% of stale dollars re-qualified, adding 0.25 x the sum of (amount x rate) over stale deals.
6. All together: 1.10 x [1.10 x sum over live deals of amount x min(1, faster rate + 0.05) + 0.25 x sum over stale deals of amount x min(1, faster rate + 0.05)], with the new pipeline rate, creation window and opportunity size from levers 1 to 3 combined.
Note that ACV +10% and pipeline +10% close the same dollars on existing pipeline; they differ only in how many new opportunities are needed. State that the levers are scenarios to size, not forecasts of what the team will achieve.
## Step 6. Output
Start with a one-page executive summary:
- Headline: "On history, open pipeline is expected to produce $X of the $R remaining (weighted coverage W vs 1.00x needed). Gap: $G."
- Three to five bullets: the team with the largest gap; stale dollars and how many deals; rep probability vs history; new pipeline per rep per week needed before the cutoff date; the lever that closes the most gap.
- If the sum of team gaps is larger than the company gap, say so in the summary and give the new pipeline target per team, not one company target, because one team's surplus does not close another team's gap.
- One line: "This is a statistical view from historical conversion, not the forecast commit."
Then these tables:
1. Data check
2. Stage conversion (stage, segment, deals, reached $, won $, conversion, in-quarter conversion, used or fallback, low confidence flag)
3. In-scope open deals (deal, owner, team, segment, stage, amount, rate row, rate, weighted, rep weighted, stale rules)
4. Stale deals to review
5. Coverage by team and company (remaining, raw, live, weighted, required, gap, rep vs history)
6. New pipeline needed (creation window, cutoff date, in-window rate, needed $, per week, per rep per week, opportunities per rep per week)
7. Levers (lever, weighted pipeline, gap, gap closed, per rep per week, opportunities per rep per week)
8. Assumptions, each labeled as an assumption: every default used and every fallback taken, plus these: rates are measured from stage entry, so deals already some days into a stage get a conservative rate; counting stale deals at zero is conservative if past stale deals sometimes won; history is assumed to describe this quarter.
If you can create files, offer the in-scope deal table as a CSV download.
## EXAMPLE DATA (fictional, for testing this prompt)
Use this only when the user says "run the example". The company, owners and deals are made up.
- As-of date: 2026-10-19
- Quarter: 2026-10-01 to 2026-12-31
- Stages in order: discovery, evaluation, proposal, negotiation
- Quota by team: East 520000, West 430000
- Closed won to date by team: East 260000, West 260000
- Quota-carrying reps by team: East 2, West 2
- Defaults: all as written above
```csv
deal_id,owner,team,segment,stage,amount,created_date,close_date,entered_discovery,entered_evaluation,entered_proposal,entered_negotiation,status,rep_probability,last_activity_date
D1001,dev,West,SMB,closed won,24000,2026-02-26,2026-04-16,2026-02-26,2026-03-11,2026-03-22,2026-04-04,won,,
D1002,dev,West,SMB,closed lost,9000,2025-10-14,2025-11-18,2025-10-14,2025-10-24,2025-11-02,2025-11-13,lost,,
D1003,ana,East,Mid-Market,closed won,61000,2025-09-13,2025-11-13,2025-09-13,2025-10-03,2025-10-18,2025-11-01,won,,
D1004,dev,West,SMB,closed lost,19500,2026-07-26,2026-08-07,2026-07-26,,,,lost,,
D1005,cara,West,SMB,closed won,16000,2026-01-19,2026-02-17,2026-01-19,2026-01-25,2026-02-01,2026-02-12,won,,
D1006,cara,West,SMB,closed lost,14000,2026-06-24,2026-07-11,2026-06-24,2026-06-29,,,lost,,
D1007,dev,West,SMB,closed won,9000,2026-04-19,2026-05-31,2026-04-19,2026-04-29,2026-05-17,2026-05-24,won,,
D1008,cara,West,Mid-Market,closed won,71000,2026-07-01,2026-09-07,2026-07-01,2026-07-20,2026-08-06,2026-08-22,won,,
D1009,dev,West,SMB,closed lost,17500,2026-06-12,2026-07-22,2026-06-12,2026-06-25,,,lost,,
D1010,ben,East,SMB,closed lost,18000,2026-03-03,2026-03-15,2026-03-03,,,,lost,,
D1011,ben,East,Mid-Market,closed won,31000,2026-06-01,2026-08-24,2026-06-01,2026-06-18,2026-07-11,2026-08-04,won,,
D1012,dev,West,SMB,closed lost,8000,2025-12-01,2026-01-01,2025-12-01,2025-12-14,,,lost,,
D1013,ben,East,SMB,closed lost,20500,2026-01-24,2026-03-15,2026-01-24,2026-02-06,2026-02-22,,lost,,
D1014,dev,West,Mid-Market,closed lost,72000,2026-09-03,2026-09-26,2026-09-03,,,,lost,,
D1015,cara,West,SMB,closed lost,8500,2025-12-02,2026-01-12,2025-12-02,2025-12-15,2025-12-29,,lost,,
D1016,ben,East,Mid-Market,closed won,58000,2025-12-23,2026-03-12,2025-12-23,2026-01-16,2026-02-05,2026-03-01,won,,
D1017,cara,West,SMB,closed lost,19000,2026-08-04,2026-09-09,2026-08-04,2026-08-13,2026-08-25,2026-09-01,lost,,
D1018,dev,West,Mid-Market,closed lost,86000,2026-05-28,2026-07-19,2026-05-28,2026-06-11,,,lost,,
D1019,ben,East,SMB,closed won,8000,2025-10-15,2025-11-21,2025-10-15,2025-10-21,2025-11-05,2025-11-14,won,,
D1020,ben,East,Mid-Market,closed won,62000,2025-08-11,2025-10-23,2025-08-11,2025-09-03,2025-09-17,2025-10-05,won,,
D1021,ana,East,SMB,closed won,18500,2026-04-24,2026-05-24,2026-04-24,2026-04-29,2026-05-10,2026-05-15,won,,
D1022,dev,West,SMB,closed won,11500,2025-12-08,2026-01-26,2025-12-08,2025-12-18,2026-01-04,2026-01-15,won,,
D1023,ben,East,SMB,closed lost,8500,2025-09-27,2025-10-17,2025-09-27,2025-10-03,,,lost,,
D1024,ben,East,Mid-Market,closed lost,34000,2025-07-27,2025-10-13,2025-07-27,2025-08-08,2025-09-02,2025-09-13,lost,,
D1025,dev,West,Mid-Market,closed won,83000,2026-02-22,2026-04-11,2026-02-22,2026-03-08,2026-03-22,2026-04-01,won,,
D1026,ana,East,SMB,closed won,21000,2026-02-03,2026-03-17,2026-02-03,2026-02-11,2026-03-01,2026-03-10,won,,
D1027,cara,West,SMB,closed lost,9000,2026-05-10,2026-05-29,2026-05-10,2026-05-15,,,lost,,
D1028,ben,East,Mid-Market,closed lost,63000,2026-07-11,2026-08-25,2026-07-11,2026-07-21,,,lost,,
D1029,cara,West,SMB,closed lost,10000,2025-12-19,2025-12-28,2025-12-19,,,,lost,,
D1030,cara,West,SMB,closed won,16000,2025-09-17,2025-11-07,2025-09-17,2025-09-30,2025-10-17,2025-10-29,won,,
D1031,ben,East,SMB,closed lost,16000,2026-03-26,2026-04-26,2026-03-26,2026-04-06,,,lost,,
D1032,dev,West,SMB,closed lost,20500,2025-10-20,2025-11-19,2025-10-20,2025-10-26,,,lost,,
D1033,dev,West,Mid-Market,closed lost,49000,2025-11-29,2026-01-28,2025-11-29,2025-12-15,,,lost,,
D1034,dev,West,Mid-Market,closed lost,73000,2026-01-01,2026-02-06,2026-01-01,,,,lost,,
D1035,dev,West,SMB,closed lost,15000,2025-11-27,2025-12-22,2025-11-27,2025-12-07,,,lost,,
D1036,dev,West,Mid-Market,closed lost,65000,2026-01-31,2026-03-12,2026-01-31,2026-02-14,,,lost,,
D1037,ana,East,SMB,closed won,12000,2026-01-05,2026-02-23,2026-01-05,2026-01-16,2026-02-03,2026-02-12,won,,
D1038,ana,East,SMB,closed lost,17500,2026-01-16,2026-01-31,2026-01-16,,,,lost,,
D1039,cara,West,SMB,closed won,10500,2025-09-26,2025-11-05,2025-09-26,2025-10-06,2025-10-24,2025-11-02,won,,
D1040,dev,West,SMB,closed won,10000,2025-12-14,2026-01-26,2025-12-14,2025-12-27,2026-01-08,2026-01-22,won,,
D1041,dev,West,SMB,closed won,8500,2026-07-24,2026-08-26,2026-07-24,2026-08-02,2026-08-11,2026-08-22,won,,
D1042,cara,West,SMB,closed lost,10500,2025-09-14,2025-10-25,2025-09-14,2025-09-28,,,lost,,
D1043,cara,West,Mid-Market,closed lost,85000,2026-01-07,2026-01-28,2026-01-07,,,,lost,,
D1044,ana,East,SMB,closed lost,12000,2026-02-23,2026-03-22,2026-02-23,2026-03-08,,,lost,,
D1045,ana,East,Mid-Market,closed won,51000,2025-09-26,2025-12-01,2025-09-26,2025-10-20,2025-11-04,2025-11-15,won,,
D1046,ana,East,SMB,closed won,20000,2025-10-06,2025-11-12,2025-10-06,2025-10-11,2025-10-26,2025-11-05,won,,
D1047,dev,West,SMB,closed lost,23500,2026-07-01,2026-08-11,2026-07-01,2026-07-12,2026-07-24,,lost,,
D1048,dev,West,Mid-Market,closed lost,74000,2026-07-15,2026-09-16,2026-07-15,2026-08-07,2026-08-27,,lost,,
D1049,ben,East,Mid-Market,closed lost,63000,2026-06-20,2026-08-19,2026-06-20,2026-07-11,,,lost,,
D1050,cara,West,SMB,closed lost,14000,2026-01-08,2026-01-25,2026-01-08,,,,lost,,
D1051,cara,West,Mid-Market,closed lost,70000,2026-01-10,2026-03-20,2026-01-10,2026-01-24,2026-02-19,,lost,,
D1052,dev,West,SMB,closed lost,8000,2026-03-21,2026-04-04,2026-03-21,,,,lost,,
D1053,ben,East,Mid-Market,closed lost,54000,2026-01-20,2026-02-16,2026-01-20,,,,lost,,
D1054,cara,West,SMB,closed won,21500,2025-11-02,2025-12-12,2025-11-02,2025-11-16,2025-11-28,2025-12-03,won,,
D1055,ana,East,Mid-Market,closed lost,55000,2025-11-25,2026-02-26,2025-11-25,2025-12-18,2026-01-09,2026-01-30,lost,,
D1056,ben,East,SMB,closed lost,15500,2026-01-23,2026-01-31,2026-01-23,,,,lost,,
D1057,cara,West,Mid-Market,closed lost,90000,2026-03-28,2026-05-16,2026-03-28,2026-04-16,,,lost,,
D1058,dev,West,SMB,closed lost,9500,2026-02-26,2026-03-09,2026-02-26,,,,lost,,
D1059,ben,East,SMB,closed lost,10500,2026-02-17,2026-03-28,2026-02-17,2026-03-01,2026-03-13,,lost,,
D1060,dev,West,Mid-Market,closed lost,62000,2025-09-12,2025-11-06,2025-09-12,2025-09-28,2025-10-22,,lost,,
D1061,cara,West,SMB,closed lost,12500,2026-01-14,2026-02-17,2026-01-14,2026-01-19,2026-01-30,,lost,,
D1062,ana,East,SMB,closed lost,8000,2025-10-20,2025-12-05,2025-10-20,2025-10-27,2025-11-14,2025-11-20,lost,,
D1063,dev,West,Mid-Market,closed lost,66000,2026-08-01,2026-09-25,2026-08-01,2026-08-20,2026-09-08,,lost,,
D1064,ana,East,Mid-Market,closed lost,45000,2026-04-05,2026-05-07,2026-04-05,,,,lost,,
D1065,ana,East,Mid-Market,evaluation,72000,2026-10-01,2026-11-14,2026-10-01,2026-10-13,,,open,60,2026-10-16
D1066,ana,East,Mid-Market,discovery,85000,2026-10-10,2026-12-16,2026-10-10,,,,open,25,2026-10-14
D1067,ana,East,SMB,proposal,18000,2026-09-22,2026-11-06,2026-09-22,2026-10-04,2026-10-15,,open,60,2026-10-15
D1068,ana,East,SMB,evaluation,21500,2026-10-01,2026-11-27,2026-10-01,2026-10-10,,,open,40,2026-10-14
D1069,ana,East,Mid-Market,evaluation,64000,2026-09-25,2026-12-18,2026-09-25,2026-10-07,,,open,40,2026-10-12
D1070,ana,East,SMB,discovery,14000,2026-10-14,2026-12-11,2026-10-14,,,,open,20,2026-10-16
D1071,ana,East,Mid-Market,proposal,58000,2026-08-29,2026-10-09,2026-08-29,2026-09-13,2026-09-29,,open,60,2026-10-02
D1072,ben,East,SMB,negotiation,23000,2026-09-06,2026-10-30,2026-09-06,2026-09-20,2026-10-02,2026-10-16,open,90,2026-10-17
D1073,ben,East,Mid-Market,proposal,81000,2026-08-31,2026-11-20,2026-08-31,2026-09-10,2026-10-10,,open,60,2026-10-13
D1074,ben,East,SMB,evaluation,16500,2026-08-11,2026-11-13,2026-08-11,2026-08-22,,,open,40,2026-10-08
D1075,ben,East,Mid-Market,discovery,47000,2026-10-11,2026-12-22,2026-10-11,,,,open,20,2026-10-15
D1076,ben,East,Mid-Market,discovery,90000,2026-10-06,2026-12-10,2026-10-06,,,,open,30,2026-10-09
D1077,ben,East,SMB,discovery,12500,2026-10-15,2027-01-15,2026-10-15,,,,open,20,2026-10-16
D1078,cara,West,SMB,negotiation,19500,2026-09-12,2026-11-02,2026-09-12,2026-09-26,2026-10-06,2026-10-14,open,80,2026-10-16
D1079,cara,West,Mid-Market,discovery,66000,2026-10-12,2026-11-25,2026-10-12,,,,open,30,2026-10-14
D1080,cara,West,Mid-Market,discovery,78000,2026-10-08,2026-12-11,2026-10-08,,,,open,25,2026-10-13
D1081,cara,West,SMB,discovery,15000,2026-10-17,2026-12-03,2026-10-17,,,,open,20,2026-10-17
D1082,cara,West,SMB,proposal,22000,2026-09-28,2026-11-18,2026-09-28,2026-10-03,2026-10-13,,open,60,2026-10-09
D1083,cara,West,Mid-Market,evaluation,55000,2026-08-24,2026-12-15,2026-08-24,2026-09-08,,,open,40,2026-09-10
D1084,dev,West,SMB,evaluation,17000,2026-10-07,2026-11-24,2026-10-07,2026-10-12,,,open,40,2026-10-15
D1085,dev,West,Mid-Market,proposal,74000,2026-09-09,2026-12-04,2026-09-09,2026-09-23,2026-10-08,,open,70,2026-10-12
D1086,dev,West,SMB,discovery,11000,2026-10-16,2026-12-09,2026-10-16,,,,open,20,2026-10-17
D1087,dev,West,Mid-Market,discovery,52000,2026-10-13,2026-12-29,2026-10-13,,,,open,20,2026-10-16
D1088,dev,West,Mid-Market,discovery,88000,2026-10-09,2026-12-17,2026-10-09,,,,open,30,2026-10-15
D1089,dev,West,Mid-Market,evaluation,69000,2026-09-20,2026-11-30,2026-09-20,2026-10-10,,,open,50,2026-10-12
```
How to run it
- Open Claude or ChatGPT with code execution on (Claude's analysis tool, ChatGPT data analysis). Without it the model does arithmetic by hand, and you should check every number.
- Paste the prompt. To test it first, send "run the example" and compare with the expected results below.
- For your quarter, attach a CRM opportunity export (open deals plus four quarters of won and lost deals) and give quota and closed won by team. The prompt lists the columns.
- Read the data check first, then the summary. Review the stale deals with managers before the forecast call.
Before you paste CRM data
Pasting an export sends it to the LLM vendor. Check your company's AI and data policy, prefer a business or enterprise plan, and drop account names and contacts: the prompt only needs deal IDs, owners, stages, dates, and amounts. It is a statistical view built on your own history, not the forecast commit.
Expected results on the example data
A correct run of "run the example" should match these, give or take rounding. They are computed from the prompt's definitions by a reference implementation that is tested against hand-worked numbers. The data is fictional.
- Days left in the quarter (D)
- 73 (as of 2026-10-19)
- History deals (closed before the quarter)
- 64
- Cycle, median of won deals
- 48 days (mean 50)
- Creation window and cutoff
- 25 days, cutoff 2026-11-13
- New pipeline in-window rate
- 13.9% (won within 60.5 days of creation)
- Average opportunity size / ACV
- $32,563 / $29,690
- Stale deals
- D1071 (past due, stuck); D1074 (stuck); D1083 (quiet, stuck): $129,500
- Excluded, closes next quarter
- D1077
- Low confidence rows
- negotiation / All, 26 deals
| Coverage | Remaining | Raw | Live | Required | Weighted | Gap | New pipeline / rep / wk |
|---|---|---|---|---|---|---|---|
| East | $260,000 | 2.27x | 1.98x | 2.59x | 0.77x | $60,931 | $61,407 |
| West | $170,000 | 3.33x | 3.01x | 2.73x | 1.10x | $0 | $0 |
| Company | $430,000 | 2.69x | 2.39x | 2.66x | 0.90x | $43,661 | $22,001 |
The company gap is $43,661, but East alone is $60,931 short: the sum of team gaps is $60,931, because one team's surplus does not close another's gap. So plan new pipeline against each team's line: East needs $61,407 per rep per week before the cutoff, not the company line's $22,001. Rep probabilities put the company at $484,550, $98,211 above history.
| Lever (company) | Weighted | Gap | Gap closed | Pipeline / rep / wk | Opps / rep / wk |
|---|---|---|---|---|---|
| Baseline | $386,339 | $43,661 | - | $22,001 | 0.68 |
| Win rate +5 points | $437,689 | $0 | $43,661 | $0 | 0.00 |
| ACV +10% | $424,973 | $5,027 | $38,634 | $2,533 | 0.07 |
| Cycle 10 days shorter | $400,421 | $29,579 | $14,083 | $8,793 | 0.27 |
| Pipeline +10% | $424,973 | $5,027 | $38,634 | $2,533 | 0.08 |
| Stale cleanup, 25% re-qualified | $401,703 | $28,297 | $15,364 | $14,259 | 0.44 |
| All together | $565,324 | $0 | $43,661 | $0 | 0.00 |
Implementation steps
- 1
Build a conversion table from history: export every won and lost opportunity closed in the last 4 to 8 quarters with stage entry dates, amount, and segment, and for each stage compute dollars won / dollars that reached the stage (deals that reached it = won deals plus lost deals whose furthest stage is that stage or later; check the counts add up), plus the in-quarter rate, the dollars won within D days of entering the stage, where D is the days left in this quarter, since a deal that wins next quarter does not cover this one; show the deal count behind every rate, and use a segment row only when it rests on at least 30 deals (an assumption you can change), otherwise the stage row
- 2
Snapshot open pipeline every week: a scheduled Zapier or n8n run on Monday morning pulls open opportunities closing this quarter (deal id, owner, team, segment, stage, amount, created date, close date, stage entry dates, rep probability, last activity date) into a new dated tab, and past snapshots are never edited
- 3
Weight each deal by history, not the rep: weighted amount = amount x the in-quarter rate for its stage and segment; mark a deal stale if its close date has passed, it has had no activity in 30 days, or it has been in its stage longer than the 90th percentile for won deals, count stale deals at zero, and list them for managers to review
- 4
Compare to quota from finance's plan, by team and for the company: remaining = quota - closed won to date; raw, live, and weighted coverage = open, non-stale, and weighted pipeline / remaining; required coverage = 1 / (weighted / live pipeline), the coverage this stage mix needs on history; gap = remaining - weighted; report the sum of team gaps too, because one team's surplus does not cover another's gap
- 5
Turn the gap into a weekly pipeline target: creation window = days left - median sales cycle, since pipeline created after that cutoff is not expected to close this quarter; new pipeline needed = gap / the historical rate at which new pipeline closes within the window; divide by the weeks in the window and by reps for pipeline and opportunities per rep per week, when the sum of team gaps is larger than the company gap, set the target per team, since one team's surplus does not close another's gap; then size levers (win rate +5 points, ACV +10%, cycle 10 days shorter, pipeline +10%, a quarter of stale dollars re-qualified) by the gap each closes
- 6
Post the result to Slack every Monday: remaining quota, raw, weighted, and required coverage, the gap and its change from last week, new pipeline per rep per week before the cutoff, stale deals, and the deals where rep probability and history disagree most; send each manager their team's lines, change no field the reps own, and state that it is a statistical view based on named quarters, not the committed forecast
- 7
Back-test monthly and recalibrate each quarter: compare what each past snapshot's weighted pipeline said with what actually closed from it by quarter end, record the error, rebuild the conversion table when the quarter closes, and keep snapshots, conversion tables, and posts as the audit trail for forecast reviews
Template outline
The structure to recreate in your own workspace. Field names and thresholds are starting points.
Workbook: Pipeline coverage FYxx Qx (one per quarter)
Tab conversion (rebuilt each quarter; finance owns it):
stage, segment, deals_reached, dollars_reached, dollars_won, conversion = dollars_won / dollars_reached,
conversion_in_quarter = dollars won within D days of entering the stage / dollars_reached (D = days left),
stage_days_p90 (won deals, nearest rank), period = "last 6 closed quarters", built_on
Rule: use the segment row only if deals_reached >= 30, else the stage row
Exclude: deals still open
Tab snapshot_YYYY-MM-DD (one per week, never edited):
deal_id, owner, team, stage, amount, close_date, rep_probability, created_date,
days_in_stage, last_activity_date
Tab weighted (formulas over the latest snapshot):
hist_rate = conversion_in_quarter for (stage, segment) in conversion
stale = close_date < today OR last_activity_date < today - 30 OR days_in_stage > stage_days_p90
in scope = close_date <= quarter end
weighted_amount = IF(stale, 0, amount x hist_rate)
rep_weighted = amount x rep_probability
Tab plan (from finance, not the CRM): team, quarter_quota
Tab coverage (company and per team):
remaining_quota = quarter_quota - closed_won_to_date
raw_coverage = open_pipeline / remaining_quota
live_coverage = non_stale_pipeline / remaining_quota
weighted_coverage = weighted_pipeline / remaining_quota
required_coverage = 1 / (weighted_pipeline / non_stale_pipeline)
gap = MAX(0, remaining_quota - weighted_pipeline)
rep_vs_history = rep_weighted - weighted_pipeline
Tab new_pipeline:
creation_window_days = MAX(0, days_left - median_cycle_days); cutoff = today + creation_window_days
new_rate = dollars won within (days_left - creation_window_days / 2) days of creation / all closed dollars
new_pipeline_needed = gap / new_rate
per_rep_per_week = new_pipeline_needed / (creation_window_days / 7) / reps
opps_per_rep_per_week = per_rep_per_week / average opportunity size
Worked example (example numbers, not a benchmark):
Quota $1,500,000, closed won $400,000 -> remaining $1,100,000
Discovery $1,200,000 x 10% = $120,000
Evaluation $900,000 x 25% = $225,000
Proposal $700,000 x 45% = $315,000
Negotiation $500,000 x 66% = $330,000
Open $3,300,000 -> raw coverage 3.0x
Weighted $990,000 -> weighted coverage 0.9x, gap $110,000
Blended rate $990,000 / $3,300,000 = 30% -> required coverage 3.33x vs 3.0x raw
Slack to #forecast every Monday:
"Pipeline coverage, week of {{date}} (rates from {{conversion_period}}):
Remaining quota {{remaining_quota}} | Closed {{closed_won}}
Raw coverage {{raw_coverage}}x | Weighted {{weighted_coverage}}x | Gap {{gap}} ({{gap_change}} vs last week)
Stale deals left out: {{stale_count}} ({{stale_amount}}) | Rep probability vs history: {{rep_vs_history}}
Sheet: {{sheet_url}}. Statistical view, not the committed forecast."
Tab backtest (monthly):
snapshot_date, weighted_pipeline_then, actual_won_from_those_deals, error, error_pct
Rebuild conversion if error_pct is outside your tolerance two quarters in a rowCost, time saved, and break-even
Tool cost here is your automation plan share, which depends on your vendor and volume, so it is an input. Every number below is an assumption; replace each with your own figure.
Inputs (all assumptions)
Replace each value with your own figure.
- TMinutes to build the weekly coverage view by hand (export, clean, weight, compare, write up)150AssumptionTime the next manual pipeline review prep from export to posted summary.
- VMinutes to review the automated sheet and post each week30AssumptionTime the first month of reviews.
- RLoaded hourly cost of the FP&A or RevOps analyst$75AssumptionSalary plus benefits, divided by working hours.
- KMonthly tool cost for the scheduled runs$50AssumptionYour Zapier or n8n plan share, from the vendor's current pricing and your invoices; Google Sheets and Slack are usually already paid for.
Formulas
- Time saved per week
(T - V) / 60 - (150 - 30) / 60 = 2 hours, the time-saved estimate on this page
- Value of time saved per month
(T - V) / 60 x 4.33 x R - 2 x 4.33 x $75 = $649.50
- Break-even hours per month
K / R - $50 / $75 = 0.67 hours, so the runs cover their tool cost once they save about 40 minutes a month
- The math counts analyst time only. A truer coverage number may change hiring, spend, or cash decisions, but the size of that depends on your business; do not add it without your own back-test results.
- The back-test in step 7 is the accuracy check: if weighted pipeline misses actual results by more than your tolerance two quarters in a row, rebuild the conversion table before trusting the gap.
- Closing the gap with more pipeline has a cost per channel. Before a lever turns into budget, check which channels earn back their acquisition cost with the CAC payback by channel playbook.
- Rerun the math after the first month with your real review time V.
Common questions
- How is this different from pipeline weighted by rep probability?
- Each open deal is weighted by the in-quarter rate its stage and segment have actually converted at in your own won and lost history, not by the probability reps enter. Stale deals, those past their close date, with no activity in 30 days, or in their stage longer than the 90th percentile for won deals, count at zero.
- What is required coverage?
- Required coverage = 1 / (weighted pipeline / live pipeline), the coverage your current stage mix needs to land the remaining quota on history. In the worked example, $990,000 weighted on $3,300,000 open is a 30% blended rate, so 3.33x is required against 3.0x raw coverage.
- Is the pipeline coverage reality check a forecast?
- No. It is a statistical view based on named quarters of history, not the committed forecast. A monthly back-test compares what each snapshot's weighted pipeline said with what actually closed, and the conversion table is rebuilt when the quarter closes.
- How much time does it save?
- The estimate is 2 hours a week: 150 minutes to build the coverage view by hand minus 30 minutes to review the automated sheet. At $75 an hour that is $649.50 a month, against an example $50 a month for the scheduled runs.
Keep going
Related playbooks
- Finance/FP&AIntermediate
Auto-Generate Monthly Revenue Forecast from Pipeline
Finance builds revenue forecasts by hand, applying historical close rates to current pipeline. It eats hours every month.
SalesforceGoogle SheetsZapierEstimate: 4 hrs/week - Finance/FP&AIntermediate
Flag Slipping Deals Before the Forecast Call
A deal whose close date has moved twice, or that has sat in the same stage for weeks, still counts in commit at full value until someone asks about it on the forecast call, so the commit and best-case numbers carry deals the history already says are slipping, and finance learns the size of the problem when the quarter closes.
HubSpot or SalesforceGoogle Sheets or ClayClaude or ChatGPT+1Estimate: 1.5 hrs/week - Finance/FP&AAdvanced
Check Sales Commissions Against Billing Before They Are Paid
Commissions are calculated from CRM closed-won data that can disagree with what was invoiced and collected, so wrong close dates, amount mismatches, split errors, and missed clawbacks surface after payroll, when they are harder to recover and explain.
HubSpot or SalesforceStripe or QuickBooksGoogle Sheets+3Estimate: 2 hrs/week - RevOpsBeginner
Sync Salesforce Pipeline to Google Sheets Dashboard
Executives ask for pipeline reports in spreadsheets, but pulling data manually creates version control chaos.
ZapierSalesforceGoogle SheetsEstimate: 4 hrs/week - ResearchAdvanced
Analyze Lost Deals with Claygent and OpenAI
Lost deal reasons in CRM are vague ("pricing", "timing"). Reps rarely document specifics.
ClayHubSpotOpenAI API+1Estimate: 5 hrs/week - Finance/FP&AIntermediate
Calculate CAC Payback by Acquisition Channel and Find the Ones That Never Pay Back
CAC is usually reported as one blended number, or per channel from ad spend alone, so a channel that costs more in SDR salaries and event fees than its customers will ever earn back in gross margin looks fine in the average, and finance only learns it when the budget is already spent.
HubSpot or SalesforceGoogle SheetsClaude or ChatGPT+1Estimate: 0.5 hrs/week - Finance/FP&AIntermediate
Compute the SaaS Magic Number so Blended CAC Stops Answering "Are We Efficient?"
Boards ask "are we efficient?" and finance answers with a blended CAC or burn multiple that hides whether last quarter's S&M actually produced enough net-new ARR to justify the spend.
HubSpot or SalesforceGoogle SheetsClaude or ChatGPT+1Estimate: 0.3 hrs/week - RevOpsIntermediate
Work Back from a New-Logo Bookings Target to the Ramped SDRs You Need
Headcount plans start from "how many SDRs do we have" instead of the quarterly new-logo bookings target, so finance and sales argue about reqs without a shared funnel from ACV, win rate, show rate, and meetings booked per ramped SDR.
HubSpot or SalesforceGoogle SheetsClaude or ChatGPT+1Estimate: 0.3 hrs/week - Finance/FP&AIntermediate
Cost per Opportunity by Channel: Spend, SQOs, and Share of ACV
Channel spend is reviewed as a blended CAC, or as cost per click, so a source that produces cheap SQOs that never close looks efficient, a source with a high win rate and ACV looks expensive, and finance and RevOps argue about the budget without a shared cost per SQO and cost per expected closed-won deal.
HubSpot or SalesforceGoogle SheetsClaude or ChatGPT+1Estimate: 0.4 hrs/week - Finance/FP&AIntermediate
Plan Sales Capacity with Ramp and Attrition, Not Headcount
Bookings targets are usually funded as headcount times quota, which assumes every rep is fully ramped all year and nobody leaves, so a plan can look covered on paper while new hires are still ramping, leavers' seats sit empty for months, and the shortfall shows up mid-year, too late to hire into.
HubSpot or SalesforceBambooHR, Rippling, or WorkdayGoogle Sheets or Clay+2Estimate: 1 hrs/week - RevOpsIntermediate
Measure Win Rate by Lead Source, Not One Blended Number
Ops leaders report one blended win rate, which hides that outbound, inbound demo requests, partners, and events convert and close very differently, so budget and SDR time keep going to the channels that look fine on average instead of the ones that turn pipeline into revenue.
HubSpot or SalesforceGoogle SheetsClaude or ChatGPTEstimate: 0.5 hrs/week - RevOpsIntermediate
Measure Sales Cycle Length by Segment and Set When Pipeline Must Be Created
Most teams plan pipeline with one blended sales cycle, so segments that close in a month and segments that take half a year get the same creation deadline, and the long-cycle segment finds out in the last month of the quarter that the pipeline it needed should have existed a quarter earlier.
HubSpot or SalesforceGoogle SheetsClaude or ChatGPT+1Estimate: 0.4 hrs/week - RevOpsIntermediate
Stage-to-Stage Conversion Rates From Your CRM, by Cohort and Segment
Most pipeline plans use one conversion rate per stage, often from a dashboard that counts open deals as losses or from a number nobody can trace, so the stage where one segment loses its deals is hidden in the average, deals that stopped moving weeks ago stay in the forecast, and the pipeline target behind the bookings number cannot be checked.
HubSpot or SalesforceGoogle SheetsClaude or ChatGPT+1Estimate: 0.5 hrs/week - Finance/FP&AIntermediate
Check Whether Quota Is Realistic From the Distribution of Rep Attainment
Quota and headcount plans usually assume every ramped rep hits 100%, and the check afterwards is one average attainment number, so nobody sees that most reps landed below quota, that a few top performers carried the average, or that the plan needed more from the typical rep than the team has delivered.
HubSpot or SalesforceGoogle SheetsClaude or ChatGPT+1Estimate: 0.4 hrs/week - RevOpsIntermediate
Measure Forecast Accuracy by Rep and Manager, and Adjust the Commit
The quarterly forecast is the sum of rep and manager commit calls, but nobody scores those calls afterwards, so the CFO cannot tell which reps sandbag and which over-call, whether managers improve on their reps, or how much of this quarter's commit history says will actually close.
HubSpot or SalesforceGoogle SheetsClaude or ChatGPT+1Estimate: 0.3 hrs/week - RevOpsIntermediate
Measure Pipeline Velocity by Segment and the Create Volume Remaining Quota Needs
Open pipeline is counted in dollars of coverage, but nobody measures how fast each segment actually turns opportunities into bookings, so a long-cycle enterprise book can look covered while it cannot produce the remaining quota in the days left, and a cycle that stretched by two weeks never shows up as a dollars-per-day problem.
HubSpot or SalesforceGoogle SheetsClaude or ChatGPT+1Estimate: 0.4 hrs/week
Need help implementing a playbook?
Our team builds custom Clay workflows and AI automations for revenue teams.
Get it built for you