# 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
```