Flag Slipping Deals Before the Forecast Call
The problem
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.
Run it in your LLM
One prompt for Claude or ChatGPT that runs the whole check on your own opportunity history export: it counts pushes and days in stage, flags deals by the rules you set, prices the commit and best case at risk, and writes the owner questions, with every assumption stated. Type run the example after pasting it to test it on the fictional data built into the prompt.
# Deal Slippage Forecast Check
You are a revenue operations analyst preparing the weekly forecast call. Find the open deals in commit and best case whose close date keeps moving or whose stage has stopped moving, show how much of the commit and best-case numbers sits on those deals, and write the list each owner should answer before the call.
## Rules
- Use only the data in this conversation. Do not invent slip rates, win rates, benchmarks, 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 deal by deal.
- Show the formula next to every dollar figure and share.
- Every default in this prompt (2 pushes, 30 days in stage) is a starting assumption the user can change, not a benchmark.
- Read only. Do not tell the user to change close dates, stages, or forecast categories as part of this run; list the questions for each owner instead.
- "At risk" means the deal carries a slippage flag. It is not a prediction that the deal is lost. Say so in the summary.
- Format: money to the nearest dollar, shares as percentages with one decimal.
## 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. An opportunity history export as CSV, one row per change, for every open opportunity, with these columns (ask the user to map theirs):
- opportunity_id, owner, forecast_category (Commit, Best Case, Pipeline, or the user's names for them), stage, amount, close_date, created_date: the opportunity's current values, repeated on each row
- field: close_date, stage, or created (one row per opportunity, so deals with no changes still appear)
- old_value, new_value, changed_at
Where to get it: in Salesforce, the Opportunity History report, or an Opportunity Field History report if field history tracking is on for Close Date and Stage. In HubSpot, the property history of Close date and Deal stage on each deal, or the "Date entered" stage properties for days in stage. If the export lists only the new value on each change, sort each opportunity's rows by date and take the old value from the row before. If there is no history at all, ask for two or more dated weekly snapshots of open deals and compare close dates and stages between them.
2. The as-of date (today) and the last day of the quarter.
3. Closed won to date this quarter, to build the commit and best-case numbers. If not given, show at-risk dollars only and say so.
4. Any changes to the defaults: pushes to flag (2) and days in stage to flag (30).
Then report a data check before any analysis: rows read, rows dropped and why (no opportunity_id, unknown field, unreadable date), open opportunities, deals closing after the quarter end (excluded and listed), and forecast categories found.
## Step 1. Pushes and days in stage
For each open opportunity:
- Pushes = count of close_date rows where new_value is later than old_value. Pull-ins (new date earlier) do not count. Count over the deal's whole history.
- Days in stage = as-of date minus the latest stage row's changed_at. If the deal has no stage row, use created_date.
## Step 2. Flags
- In scope = open deals with close_date on or before the quarter end, including close dates already in the past.
- pushed = pushes of 2 or more.
- stalled = days in stage of more than 30.
- Flagged = pushed or stalled. A deal can carry both.
- Commit and best-case deals are the call list. Flagged pipeline deals are listed separately and do not count below.
## Step 3. Money at risk
- Commit open = sum of amount on in-scope commit deals. Commit number = closed won to date + commit open.
- Best case open = sum of amount on in-scope best-case deals. Best-case number = commit number + best case open.
- Commit at risk = sum of amount on flagged commit deals. Share = commit at risk / commit number.
- Best case at risk = commit at risk + sum of amount on flagged best-case deals. Share = best case at risk / best-case number.
- If the user's team defines the commit or best-case number differently, use their definition and say so.
## Step 4. Output
Start with a five-line summary:
- Headline: "{n} commit and best-case deals carry a slippage flag: {commit at risk} of the {commit number} commit ({share}) and {best case at risk} of the {best-case number} best case ({share})."
- The largest flagged deal and why.
- The owner with the most flagged dollars.
- Deals excluded because they now close after the quarter end.
- One line: "Flagged means the close date keeps moving or the stage has stopped; it is not a forecast that the deal is lost."
Then these tables:
1. Data check
2. Flagged deals, largest first (deal, owner, category, stage, amount, close date, pushes, days in stage, flags)
3. Money at risk (commit and best case: open, flagged, number, at risk, share, each with its formula)
4. By owner (owner, flagged deals, flagged amount)
5. Owner questions: one short message per owner listing their flagged deals with the reason, asking for the next step and its date, or a close date or category change before the call, ready to paste into Slack
6. Flagged pipeline deals and excluded next-quarter deals
7. Assumptions, each labeled as an assumption
If you can create files, offer the flagged deals 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-25
- Quarter end: 2026-12-31
- Closed won to date this quarter: $210,000 (example input)
- Defaults: all as written above
```csv
opportunity_id,owner,forecast_category,stage,amount,close_date,created_date,field,old_value,new_value,changed_at
O-101,maya,Commit,negotiation,40000,2026-11-20,2026-06-15,created,,,2026-06-15
O-101,maya,Commit,negotiation,40000,2026-11-20,2026-06-15,close_date,2026-09-30,2026-10-31,2026-09-25
O-101,maya,Commit,negotiation,40000,2026-11-20,2026-06-15,stage,proposal,negotiation,2026-10-05
O-101,maya,Commit,negotiation,40000,2026-11-20,2026-06-15,close_date,2026-10-31,2026-11-20,2026-10-20
O-102,omar,Commit,proposal,30000,2026-11-15,2026-07-20,created,,,2026-07-20
O-102,omar,Commit,proposal,30000,2026-11-15,2026-07-20,stage,evaluation,proposal,2026-09-10
O-102,omar,Commit,proposal,30000,2026-11-15,2026-07-20,close_date,2026-10-31,2026-11-15,2026-10-02
O-103,lena,Commit,negotiation,85000,2026-12-10,2026-08-03,created,,,2026-08-03
O-103,lena,Commit,negotiation,85000,2026-12-10,2026-08-03,stage,proposal,negotiation,2026-10-15
O-104,maya,Commit,proposal,70000,2026-12-15,2026-07-01,created,,,2026-07-01
O-104,maya,Commit,proposal,70000,2026-12-15,2026-07-01,close_date,2026-10-31,2026-11-30,2026-08-20
O-104,maya,Commit,proposal,70000,2026-12-15,2026-07-01,stage,evaluation,proposal,2026-08-28
O-104,maya,Commit,proposal,70000,2026-12-15,2026-07-01,close_date,2026-11-30,2026-11-15,2026-09-15
O-104,maya,Commit,proposal,70000,2026-12-15,2026-07-01,close_date,2026-11-15,2026-12-01,2026-10-01
O-104,maya,Commit,proposal,70000,2026-12-15,2026-07-01,close_date,2026-12-01,2026-12-15,2026-10-21
O-105,jules,Commit,negotiation,65000,2026-11-05,2026-08-11,created,,,2026-08-11
O-105,jules,Commit,negotiation,65000,2026-11-05,2026-08-11,stage,proposal,negotiation,2026-10-20
O-106,omar,Best Case,evaluation,90000,2026-12-20,2026-07-28,created,,,2026-07-28
O-106,omar,Best Case,evaluation,90000,2026-12-20,2026-07-28,close_date,2026-11-15,2026-11-30,2026-09-08
O-106,omar,Best Case,evaluation,90000,2026-12-20,2026-07-28,stage,discovery,evaluation,2026-10-10
O-106,omar,Best Case,evaluation,90000,2026-12-20,2026-07-28,close_date,2026-11-30,2026-12-20,2026-10-14
O-107,lena,Best Case,proposal,50000,2026-11-30,2026-06-30,created,,,2026-06-30
O-107,lena,Best Case,proposal,50000,2026-11-30,2026-06-30,stage,evaluation,proposal,2026-09-01
O-107,lena,Best Case,proposal,50000,2026-11-30,2026-06-30,close_date,2026-10-31,2026-11-30,2026-10-06
O-108,maya,Best Case,evaluation,40000,2026-12-05,2026-09-14,created,,,2026-09-14
O-108,maya,Best Case,evaluation,40000,2026-12-05,2026-09-14,stage,discovery,evaluation,2026-10-12
O-109,jules,Best Case,proposal,70000,2026-12-18,2026-08-19,created,,,2026-08-19
O-109,jules,Best Case,proposal,70000,2026-12-18,2026-08-19,stage,evaluation,proposal,2026-09-25
O-110,omar,Pipeline,discovery,30000,2026-12-30,2026-08-01,created,,,2026-08-01
O-110,omar,Pipeline,discovery,30000,2026-12-30,2026-08-01,close_date,2026-10-31,2026-11-30,2026-09-20
O-110,omar,Pipeline,discovery,30000,2026-12-30,2026-08-01,close_date,2026-11-30,2026-12-30,2026-10-18
O-111,lena,Commit,negotiation,25000,2027-01-15,2026-07-07,created,,,2026-07-07
O-111,lena,Commit,negotiation,25000,2027-01-15,2026-07-07,stage,proposal,negotiation,2026-10-01
O-111,lena,Commit,negotiation,25000,2027-01-15,2026-07-07,close_date,2026-12-20,2027-01-15,2026-10-19
O-112,maya,Best Case,proposal,55000,2026-11-25,2026-10-01,created,,,2026-10-01
```
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.
- Attach an opportunity history export with close date and stage changes for every open deal (Salesforce Opportunity History or field history, HubSpot property history), and give the quarter end and closed won to date. The prompt lists the columns.
- Read the data check first, then post each owner their questions in Slack a day before the forecast call so answers come back before the number is set.
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, forecast categories, stages, amounts, and dates. It reads the export and changes nothing in your CRM.
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.
- Commit at risk
- $140,000 of $500,000 ($210,000 closed won + $290,000 open commit), 28.0%
- Best case at risk
- $280,000 of $805,000 (commit number + $305,000 open best case), 34.8%
- Flagged commit and best-case deals
- 5
- Owner with the most flagged dollars
- omar, 2 deals, $120,000
- Flagged pipeline deals (listed, not counted)
- O-110 $30,000
- Excluded, now closing after quarter end
- O-111
| Deal | Owner | Category | Flags | Amount | Pushes | Days in stage |
|---|---|---|---|---|---|---|
| O-106 | omar | best case | pushed | $90,000 | 2 | 15 |
| O-104 | maya | commit | pushed, stalled | $70,000 | 3 | 58 |
| O-107 | lena | best case | stalled | $50,000 | 1 | 54 |
| O-101 | maya | commit | pushed | $40,000 | 2 | 20 |
| O-102 | omar | commit | stalled | $30,000 | 1 | 45 |
O-109 has sat in proposal for exactly 30 days, so it is not flagged: the rule is more than 30. O-104 moved its close date four times, but one move was a pull-in, so it counts as three pushes.
Enter the flagged share of open pipeline as stale, then check whether the rest still covers the remaining quota at your win rate, and how much new pipeline each rep needs per week. Open the Pipeline coverage calculator.
Implementation steps
- 1
Export the change history for every open opportunity: in Salesforce, the Opportunity History report, or an Opportunity Field History report if field history tracking is on for Close Date and Stage; in HubSpot, the property history of Close date and Deal stage, or the "Date entered" stage properties for days in stage; you need one row per change with opportunity ID, owner, forecast category, stage, amount, close date, created date, field, old value, new value, and changed date
- 2
If history is not available, start keeping it: snapshot open deals every week (ID, owner, forecast category, stage, amount, close date) into a new dated tab in Google Sheets or a Clay table, never edit past snapshots, and compare close dates and stages between snapshots; for Clay, its pricing page lists CRM integrations on Growth and above
- 3
Count pushes per deal: a push is a close date change where the new date is later than the old one; pull-ins do not count, and count over the deal's whole history so a deal that slipped last quarter still shows it
- 4
Compute days in stage: as-of date minus the date of the latest stage change, or minus the created date if the stage has never changed
- 5
Flag with written rules: pushed if the close date has moved later 2 or more times, stalled if the deal has been in its stage for more than 30 days (both are assumptions you set; a long enterprise cycle may need a higher stage limit); only open deals closing on or before the quarter end are in scope, and deals pushed into next quarter are listed separately
- 6
Price the risk: commit number = closed won to date + open commit; commit at risk = flagged commit amount, and its share = commit at risk / commit number; best-case number = commit number + open best case, and best case at risk = flagged commit + flagged best case; run it with the copy-paste prompt on this page in Claude or ChatGPT, or with formulas in the template
- 7
Post to Slack a day before the forecast call: the at-risk amounts with their formulas, then one message per owner listing their flagged deals and the reason, asking for the next step and its date, or a close date or category change before the call; change no field the reps own
- 8
Back-test each quarter: for every deal flagged during the quarter, record whether it closed won in the quarter, slipped, or was lost, compare with deals that were not flagged, and raise or lower the push and stage limits if the flags do not separate the two groups in your own data
Template outline
The structure to recreate in your own workspace. Field names and thresholds are starting points.
Google Sheet: Deal slippage check FYxx Qx (RevOps prepares it; the forecast owner reviews it)
Tab history (one row per change, from the CRM export or weekly snapshots):
opportunity_id, owner, forecast_category, stage, amount, close_date, created_date,
field (created, stage, close_date), old_value, new_value, changed_at
Tab deals (one row per open opportunity, formulas):
pushes = COUNTIFS(history.opportunity_id, id, history.field, "close_date", later_than_old, TRUE)
last_stage_change = MAXIFS(history.changed_at, history.opportunity_id, id, history.field, "stage"),
or created_date if blank
days_in_stage = as_of - last_stage_change
in_scope = close_date <= quarter_end
pushed = pushes >= 2; stalled = days_in_stage > 30; flagged = OR(pushed, stalled)
Tab at_risk:
commit_number = closed_won_to_date + SUMIFS(amount, category, "Commit", in_scope, TRUE)
commit_at_risk = SUMIFS(amount, category, "Commit", in_scope, TRUE, flagged, TRUE)
commit_share = commit_at_risk / commit_number
best_case_number = commit_number + SUMIFS(amount, category, "Best Case", in_scope, TRUE)
best_case_at_risk = commit_at_risk + SUMIFS(amount, category, "Best Case", in_scope, TRUE, flagged, TRUE)
best_case_share = best_case_at_risk / best_case_number
Worked example (fictional data in the prompt, as of 2026-10-25, quarter end 2026-12-31):
Closed won to date $210,000 (example input)
Commit open $290,000 -> commit number $500,000; flagged O-104 $70,000 (3 pushes, 58 days),
O-101 $40,000 (2 pushes), O-102 $30,000 (45 days) -> $140,000 at risk, 28.0%
Best case open $305,000 -> best-case number $805,000; flagged O-106 $90,000 (2 pushes),
O-107 $50,000 (54 days) -> $140,000 + $140,000 = $280,000 at risk, 34.8%
O-109 at exactly 30 days in stage is not flagged; O-111 now closes in January and is excluded
Slack to #forecast the day before the call:
"Slippage check, week of {{date}}: {{flagged_count}} commit and best-case deals flagged.
Commit at risk {{commit_at_risk}} of {{commit_number}} ({{commit_share}}) | Best case at risk {{best_case_at_risk}} of {{best_case_number}} ({{best_case_share}})
Flagged = close date moved later {{min_pushes}}+ times or more than {{stall_days}} days in stage. Not a forecast that the deal is lost.
Sheet: {{sheet_url}}"
Message to each owner:
"{{owner}}, before {{call_date}}: {{deal}} ({{amount}}, {{reason}}). What is the next step and its date,
or should the close date or category change?"
Tab backtest (each quarter):
deal, flagged_on, flag, outcome (won in quarter, slipped, lost), amountCost, time saved, and break-even
Tool cost here is your share of an LLM plan or the time you spend on formulas, which depends on your vendor, so it is an input. Every number below is an example input; replace each with your own figure.
Inputs (all assumptions)
Replace each value with your own figure.
- DOpen commit and best-case deals reviewed each week40AssumptionCount them in this week's forecast view.
- TMinutes per deal to check close date and stage history by hand3AssumptionTime ten deals in the CRM before the next forecast call.
- VHours per week to export, run the prompt or sheet, and post0.5AssumptionTime the first month of runs.
- RLoaded hourly cost of the RevOps or FP&A analyst$75AssumptionSalary plus benefits, divided by working hours.
- KMonthly tool cost for the LLM plan$20AssumptionYour share of the Claude or ChatGPT plan, from current vendor pricing and your invoices; HubSpot or Salesforce, Google Sheets, and Slack are usually already paid for.
Formulas
- Hours by hand per week
D x T / 60 - 40 x 3 / 60 = 2 hours
- Time saved per week
D x T / 60 - V - 2 - 0.5 = 1.5 hours, the time-saved estimate on this page
- Value of time saved per month
(D x T / 60 - V) x 4.33 x R - 1.5 x 4.33 x $75 = $487.13
- Break-even hours per month
K / R - $20 / $75 = 0.27 hours, so the run covers its tool cost once it saves about 16 minutes a month
- Commit at risk share
flagged commit / (closed won to date + open commit) - $140,000 / ($210,000 + $290,000) = 28.0% in the fictional example
- At risk means the deal carries a slippage flag, not that it will be lost. Do not cut the forecast by the at-risk amount; ask the owners, then let the forecast owner decide.
- The math counts prep time only. Catching a slipping deal earlier may change what finance commits, but the size of that depends on your business; do not add it without your own back-test results.
- Rerun the math after the first month with your real deal count D and run time V.
Common questions
- What counts as a slipping deal?
- An open commit or best-case deal closing this quarter whose close date has moved later 2 or more times, or that has been in its current stage for more than 30 days. Pull-ins do not count as pushes. Both limits are assumptions you set, and the quarterly back-test is how you tune them on your own deals.
- How is the commit at risk calculated?
- Commit number = closed won to date + open commit. Commit at risk = the amount on flagged commit deals, and its share = commit at risk / commit number. In the fictional example, $140,000 of a $500,000 commit number is flagged, 28.0%. Best case at risk adds flagged best-case deals to the flagged commit.
- Where does the close date push history come from?
- In Salesforce, the Opportunity History report, or a field history report if tracking is on for Close Date and Stage. In HubSpot, the property history of Close date and Deal stage, or the "Date entered" stage properties. With no history, keep weekly snapshots of open deals and compare them.
- Does a flagged deal mean it will be lost?
- No. A flag means the close date keeps moving or the stage has stopped, so the owner should confirm the next step before the call. The page counts prep time saved only and claims no forecast accuracy gain; track flagged deals against outcomes each quarter before relying on the flags.
Keep going
Related playbooks
- Finance/FP&AIntermediate
Check Pipeline Coverage with Historical Conversion, Not Rep Probability
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.
HubSpot or SalesforceGoogle SheetsZapier or n8n+1Estimate: 2 hrs/week - 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 - RevOpsBeginner
Post Real-Time Deal Alerts to Slack by Stage
Leadership wants to celebrate wins and coach deals in real time, but they live in Slack, not the CRM.
HubSpotZapierSlackEstimate: 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 - 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 - 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
Need help implementing a playbook?
Our team builds custom Clay workflows and AI automations for revenue teams.
Get it built for you