Measure Sales Cycle Length by Segment and Set When Pipeline Must Be Created
The problem
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.
Run it in your LLM
One prompt for Claude or ChatGPT that runs the whole analysis on your own opportunity export: it keeps only won deals closed in the period, builds the cycle table by segment next to the blended row, and turns each segment's cycle into create-by dates for the quarter you are building pipeline for. Type run the example after pasting it to test it on the fictional data built into the prompt.
# Sales Cycle Length by Segment
You are a revenue operations analyst reporting to the CRO and the CFO. Measure how many days won deals took from opportunity created to closed won in each segment (or lead source), and turn those cycle lengths into the latest dates pipeline can be created and still close by a quarter end, with every number traceable to the export.
## Rules
- Use only the data in this conversation. Do not invent deals, dates, benchmarks, or industry averages. If something 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 segment by segment.
- Show the formula next to every headline number.
- Every default in this prompt (5 won deals per group, the four quarters, the 75th percentile) is a starting assumption the user can change, not a benchmark.
- Read only. Do not tell the user to edit CRM dates as part of this run; list the records that look wrong instead.
- Cycle lengths here come from won deals only. Say so in the summary: lost and open deals have no won cycle, and deals that are still open are often the slow ones.
- Format: days as whole numbers (a median between two days keeps its .5), dates as YYYY-MM-DD, percentages with one decimal, money to the nearest dollar.
## 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 CSV export of opportunities, one row per opportunity, with these columns (ask the user to map theirs):
- opportunity_id, segment, lead_source, stage, amount, created_date, close_date
Where to get it: in Salesforce, an Opportunities report with Created Date, Close Date, Stage, Amount, Lead Source, and the field the team uses for segment (for example a custom field or employee-count band on the account). In HubSpot, a deals export with Create Date, Close Date, Deal Stage, Amount, and the segment and source properties. Ask which stage names mean closed won and closed lost.
2. The period: by default won deals with a close date in the last four full quarters before today.
3. The as-of date (today by default), the end of the quarter in progress, and the end of the quarter the user is building pipeline for (by default the next one).
4. Whether to group by segment (default) or lead source.
5. Any change to the default minimum of 5 won deals per group.
Then report a data check before any analysis: rows read, rows dropped and why (closed lost, open, closed outside the period, close date before created date, no amount), the group values found, any values that look like the same group spelled differently, and won deals with a created date that looks like a placeholder (for example many deals created on the same day as a CRM migration). Ask the user to confirm a mapping for messy values before going on, unless they said "run the example".
## Step 1. Cycle table
For each group, and for all won deals together (the blended row):
- Cycle days = close_date - created_date, for each won deal in the period
- Won deals, won amount
- Median days (for an even count, the average of the two middle values)
- 75th percentile days, nearest rank: sort ascending and take the value at position ceil(0.75 x n)
- Dollar-weighted median days: sort by cycle days, add up won amount, and report the cycle days of the deal where the running total first reaches half of the group's won amount. It answers how long the deals carrying half the dollars took.
- Days left in the current quarter = quarter end - as-of date. Share of won deals, and of won dollars, with cycle days at or below that number: the share of pipeline created today that history says could still close this quarter.
- Enough data = won deals is 5 or more.
## Step 2. Pipeline creation timing
For each group with enough data, for the quarter being built:
- Create-by date at the median = quarter end - median days (round the median up to whole days). Pipeline created after this date closed in time in fewer than half of past wins.
- Create-by date at the 75th percentile = quarter end - 75th percentile days. Pipeline created by this date had time to close in at least three of four past wins.
- Days from the as-of date to each create-by date. A negative number means the date has passed: say so plainly.
- Compare each group's create-by date with the one the blended median gives, and state the difference in days. This is the main finding: one blended cycle sets the wrong deadline for every segment that is faster or slower than average.
Groups under the minimum get "Too few won deals to time" and no create-by date. Say how many more won deals they need.
Do not forecast how many deals will close or what a change in process would do to cycle length. This is history, not a forecast; for how much pipeline to create, point the user to their coverage math.
## Step 3. Output
Start with a five-line summary:
- Headline: "Median days to close won: {group} {median}, ..., blended {median}; the blended number sets the wrong deadline for {groups}."
- For the quarter being built: each group's create-by date at the median and the 75th percentile, and which have passed.
- Days left in the current quarter and the share of won dollars that historically closed within that many days, by group.
- Groups with too few won deals.
- One line: "Cycle lengths come from {n} won deals closed {period}; lost and open deals are not in them."
Then these tables:
1. Data check
2. Cycle table (group, won deals, won amount, median days, 75th percentile days, dollar-weighted median days, share of deals and of dollars within the days left this quarter, enough data), blended row last
3. Pipeline creation timing (group, create-by at the median, days from as-of, create-by at the 75th percentile, days from as-of, difference from the blended create-by)
4. Records to check (close date before created date, suspicious created dates, group values to map)
5. Assumptions, each labeled as an assumption, including: created date is when the opportunity was opened in the CRM, which may be later than first contact; cycle lengths are from won deals in the period; the minimum deal count and the 75th percentile are defaults.
If you can create files, offer the cycle table and the timing table as CSV downloads.
## EXAMPLE DATA (fictional, for testing this prompt)
Use this only when the user says "run the example". The company and deals are made up.
- Period: won deals closed 2025-10-01 to 2026-09-30
- As-of date: 2026-10-01
- Quarter in progress ends: 2026-12-31
- Quarter being built ends: 2027-03-31
- Group by: segment
- Stage names: "Closed Won" and "Closed Lost"; every other stage is open
- Defaults: all as written above
```csv
opportunity_id,segment,lead_source,stage,amount,created_date,close_date
O-001,SMB,Inbound demo request,Closed Won,6000,2025-09-25,2025-10-09
O-002,SMB,Outbound,Closed Won,8000,2026-02-04,2026-02-22
O-003,SMB,Partner,Closed Won,8000,2026-06-17,2026-07-08
O-004,SMB,Event,Closed Won,10000,2025-11-04,2025-11-29
O-005,SMB,Inbound demo request,Closed Won,10000,2026-03-15,2026-04-14
O-006,SMB,Outbound,Closed Won,12000,2026-07-26,2026-08-28
O-007,SMB,Partner,Closed Won,12000,2025-12-12,2026-01-19
O-008,SMB,Event,Closed Won,14000,2026-04-20,2026-06-04
O-009,SMB,Inbound demo request,Closed Won,20000,2025-08-27,2025-10-26
O-010,Mid-market,Outbound,Closed Won,20000,2026-01-25,2026-03-11
O-011,Mid-market,Partner,Closed Won,25000,2026-06-03,2026-07-25
O-012,Mid-market,Event,Closed Won,30000,2025-10-17,2025-12-16
O-013,Mid-market,Inbound demo request,Closed Won,30000,2026-02-22,2026-05-01
O-014,Mid-market,Outbound,Closed Won,35000,2026-07-01,2026-09-14
O-015,Mid-market,Partner,Closed Won,40000,2025-11-07,2026-02-05
O-016,Mid-market,Event,Closed Won,60000,2026-03-03,2026-06-21
O-017,Enterprise,Inbound demo request,Closed Won,60000,2025-07-25,2025-11-12
O-018,Enterprise,Outbound,Closed Won,80000,2025-11-13,2026-03-28
O-019,Enterprise,Partner,Closed Won,100000,2026-03-04,2026-08-11
O-020,Enterprise,Event,Closed Won,150000,2025-06-26,2026-01-02
O-021,Enterprise,Inbound demo request,Closed Won,110000,2025-09-20,2026-05-18
O-022,SMB,Outbound,Closed Lost,9000,2026-02-02,2026-03-20
O-023,Mid-market,Event,Closed Lost,28000,2025-12-01,2026-04-10
O-024,Enterprise,Partner,Closed Lost,120000,2025-11-03,2026-06-30
O-025,Enterprise,Outbound,Proposal,140000,2026-05-12,2026-11-30
O-026,SMB,Inbound demo request,Discovery,7000,2026-09-08,2026-10-20
O-027,Mid-market,Partner,Closed Won,32000,2025-07-01,2025-09-18
O-028,SMB,Inbound demo request,Closed Won,9000,2026-04-20,2026-04-02
```
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 a CSV of opportunities with segment, lead source, stage, amount, created date, and close date (a Salesforce Opportunities report or a HubSpot deals export), say which stages mean closed won and closed lost, and give today's date and the quarter you are building pipeline for. The prompt lists the columns.
- Read the data check first and confirm how messy segment values map, then take the timing table to the pipeline planning meeting: a create-by date that has already passed is the first thing to discuss.
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 opportunity IDs, segments, sources, stages, amounts, and dates. It reads the data 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.
- Median days to close won
- SMB 30, Mid-market 68, Enterprise 160, All segments 60
- Days left in the quarter
- 2026-10-01 to 2026-12-31 = 91 days
- Won within that many days, blended
- 71.4% of deals but 33.3% of dollars
- Create-by dates for the quarter ending 2027-03-31
- SMB 2027-03-01 (median), 2027-02-21 (75th percentile); Mid-market 2027-01-22 (median), 2026-12-31 (75th percentile); Enterprise 2026-10-22 (median), 2026-09-22 (75th percentile); All segments 2027-01-30 (median), 2026-12-11 (75th percentile)
- Already passed
- Enterprise at the 75th percentile, 9 days before 2026-10-01
- Rows excluded
- 7: O-022 (closed lost, no won cycle), O-023 (closed lost, no won cycle), O-024 (closed lost, no won cycle), O-025 (open, no result yet), O-026 (open, no result yet), O-027 (closed outside the period), O-028 (close date before created date)
| Segment | Won deals | Won amount | Median days | 75th pct days | Dollar median days | Dollars within 91 days | Create by (median) | Create by (75th pct) |
|---|---|---|---|---|---|---|---|---|
| SMB | 9 | $100,000 | 30 | 38 | 33 | 100.0% | 2027-03-01 | 2027-02-21 |
| Mid-market | 7 | $240,000 | 68 | 90 | 75 | 75.0% | 2027-01-22 | 2026-12-31 |
| Enterprise | 5 | $500,000 | 160 | 190 | 190 | 0.0% | 2026-10-22 | 2026-09-22 |
| All segments | 21 | $840,000 | 60 | 110 | 135 | 33.3% | 2027-01-30 | 2026-12-11 |
The blended median of 60 days says pipeline for the quarter ending 2027-03-31 can wait until 2027-01-30. Enterprise deals took a median of 160 days, so its pipeline needed to exist by 2026-10-22, 100 days earlier, and its 75th percentile date of 2026-09-22 had already passed on the as-of date.
Pipeline creation dates from your cycle lengths
Enter your own medians and 75th percentiles from the prompt or the template. Prefilled with the fictional example. Create by = quarter end - cycle days, rounded up to whole days.
| Segment or source | Median days | 75th pct days | Create by (median) | Create by (75th pct) | Vs blended | Remove |
|---|---|---|---|---|---|---|
2027-03-01 in 151 days | 2027-02-21 in 143 days | 30 days later | ||||
2027-01-22 in 113 days | 2026-12-31 in 91 days | 8 days earlier | ||||
2026-10-22 in 21 days | 2026-09-22 passed 9 days ago | 100 days earlier | ||||
2027-01-30 in 121 days | 2026-12-11 in 71 days | baseline |
Name a row All segments to compare every other row with it. Cycle lengths come from your own won deals; this is history, not a forecast.
Implementation steps
- 1
Write the definitions down before pulling data: cycle days = close date - created date of the opportunity; only won deals have a cycle; the segment is one agreed field (for example an employee-count band on the account) mapped to a short list; the period is won deals with a close date in the last four full quarters. Note that created date is when the opportunity was opened in the CRM, which can be weeks after first contact
- 2
Export every opportunity from HubSpot or Salesforce with ID, segment, lead source, stage, amount, created date, and close date; drop closed lost and open deals, won deals closed outside the period, and rows whose close date is before the created date, and list each dropped row with the reason; flag created dates that look like placeholders, such as many deals created on the day of a CRM migration
- 3
Compute per segment, and for all won deals together: won deals and won amount, the median days, the 75th percentile days by nearest rank (sort ascending and take position ceil(0.75 x n)), and the dollar-weighted median (sort by days, add up amount, and take the days where the running total first reaches half of won dollars); show the deal count next to every number, and give no timing to a segment with fewer than 5 won deals (an assumption you can change)
- 4
Check what can still close this quarter: days left = quarter end - today; share of won deals, and of won dollars, whose cycle was at or under that many days. A segment where almost no won dollars closed that fast cannot be saved by pipeline created now, however much of it there is
- 5
Turn cycles into creation deadlines for the quarter you are building: create by = quarter end - median days (rounded up), and quarter end - 75th percentile days for the date that gave three of four past wins enough time; show each segment next to the date the blended median gives, and say plainly when a date has already passed
- 6
Feed the dates into pipeline planning: set each segment's pipeline creation target to land before its create-by date, and use the segment cycle, not the blended one, for the creation window in your coverage math; post the timing table to the revenue leadership channel in Slack at the start of each quarter
- 7
Run it with the copy-paste prompt on this page in Claude or ChatGPT, or with formulas in the template, check every number against the export, and use the calculator on this page to try other quarter ends
- 8
Repeat each quarter on the same definitions and keep each quarter's table: a median that moves by more than a couple of weeks is worth explaining (a new segment definition, a pricing change, a process change), and the share of won deals with suspicious created dates is a data quality number to track
Template outline
The structure to recreate in your own workspace. Field names and thresholds are starting points.
Google Sheet: Sales cycle by segment FYxx Qx (RevOps owns it)
Tab inputs: period start and end (won deals by close date), as_of, current_quarter_end, next_quarter_end, min_won (default 5)
Tab opps (CRM export): opportunity_id, segment, lead_source, stage, amount, created_date, close_date
keep = stage is Closed Won AND close_date in the period AND close_date >= created_date AND amount > 0
cycle_days = close_date - created_date
segment_mapped = lookup in segment_map
Tab segment_map: raw_value, segment (one row per spelling found in the export)
Tab by_segment (one row per segment plus a blended row):
won = COUNTIFS(segment_mapped, s, keep, TRUE); won_amount = SUMIFS(amount, segment_mapped, s, keep, TRUE)
median_days = MEDIAN(FILTER(cycle_days, segment_mapped = s, keep))
p75_days = INDEX(SORT(FILTER(cycle_days, segment_mapped = s, keep)), ROUNDUP(0.75 x won))
dollar_median_days = cycle days where the running won amount, sorted by days, first reaches won_amount / 2
days_left = current_quarter_end - as_of
share_within = COUNTIFS(..., cycle_days, "<=" & days_left) / won; same with amount for dollars
create_by_median = next_quarter_end - ROUNDUP(median_days); create_by_p75 = next_quarter_end - p75_days
days_to_create_by = create_by - as_of (negative: passed)
enough = won >= min_won
Worked example (example data, fictional):
Won deals closed 2025-10-01 to 2026-09-30. 28 rows; 7 dropped: O-022 to O-024 closed lost, O-025 and O-026 open,
O-027 closed 2025-09-18 (before the period), O-028 closed 2026-04-02 before it was created 2026-04-20.
Example cycle: O-020 created 2025-06-26, closed 2026-01-02 = 4 + 31 + 31 + 30 + 31 + 30 + 31 + 2 = 190 days
SMB, 9 won: 14, 18, 21, 25, 30, 33, 38, 45, 60 days
Median = 5th value = 30. 75th percentile = position ceil(0.75 x 9) = ceil(6.75) = 7th = 38
Won $100,000; half = $50,000; running $6,000, $14,000, $22,000, $32,000, $42,000, $54,000 -> 33 days
Mid-market, 7 won: 45, 52, 60, 68, 75, 90, 110
Median = 4th = 68. 75th percentile = ceil(5.25) = 6th = 90
Won $240,000; half $120,000; running $20,000, $45,000, $75,000, $105,000, $140,000 -> 75 days
Enterprise, 5 won: 110, 135, 160, 190, 240
Median = 3rd = 160. 75th percentile = ceil(3.75) = 4th = 190
Won $500,000; half $250,000; running $60,000, $140,000, $240,000, $390,000 -> 190 days
All segments, 21 won: median = 11th = 60; 75th percentile = ceil(15.75) = 16th = 110; dollar median 135
Average for comparison: (284 + 500 + 835) / 21 = 1,619 / 21 = 77.1 days
As of 2026-10-01, days left to 2026-12-31 = 30 + 30 + 31 = 91
Won within 91 days: SMB 9 of 9 (100.0% of dollars); Mid-market 6 of 7, $180,000 / $240,000 = 75.0% of dollars;
Enterprise 0 of 5 (0.0%); blended 15 of 21 = 71.4% of deals but $280,000 / $840,000 = 33.3% of dollars
Create by, for the quarter ending 2027-03-31:
Segment Median Create by From 2026-10-01 75th pct Create by From 2026-10-01
SMB 30 2027-03-01 in 151 days 38 2027-02-21 in 143 days
Mid-market 68 2027-01-22 in 113 days 90 2026-12-31 in 91 days
Enterprise 160 2026-10-22 in 21 days 190 2026-09-22 passed 9 days ago
Blended 60 2027-01-30 in 121 days 110 2026-12-11 in 71 days
Enterprise's median date is 160 - 60 = 100 days earlier than the blended date; SMB's is 30 days later.
Slack to #revenue-leadership at the start of each quarter:
"Pipeline timing for the quarter ending {{quarter_end}} (won deals {{period}}): create by {{segment_dates}}.
Passed already: {{passed_list}}. Too few won deals to time: {{too_few_list}}. Table: {{sheet_url}}"
Tab history: quarter, segment, won, median_days, p75_days, dollar_median_days, suspicious_created_shareCost, time saved, and break-even
Tool cost here is your share of an LLM plan, 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.
- THours a quarter to build the cycle and timing table by hand (export, cleanup, percentiles, dates, write-up)6AssumptionTime the next quarterly pipeline planning prep.
- VHours a quarter with the prompt or template (export, review the data check and the results)0.8AssumptionTime the first two quarterly runs.
- RLoaded hourly cost of the RevOps 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; the CRM, Google Sheets, and Slack are usually already paid for.
Formulas
- Time saved per quarter
T - V - 6 - 0.8 = 5.2 hours
- Time saved per week
(T - V) / 13 - 5.2 / 13 = 0.4 hours
- Value of time saved per quarter
(T - V) x R - 5.2 x $75 = $390
- Tool cost per quarter
K x 3 - $20 x 3 = $60
- Break-even hours per quarter
K x 3 / R - $60 / $75 = 0.8 hours, so the run covers its tool cost once it saves 48 minutes a quarter
- Enterprise create-by date (fictional example data)
quarter end - median cycle days - 2027-03-31 - 160 days = 2026-10-22, against 2027-03-31 - 60 days = 2027-01-30 for the blended median
- The math counts analyst time only. Creating pipeline on time may change what closes in a quarter, but by how much depends on your business, so it is not added here.
- Cycle lengths are history, not a forecast: a pricing change, a new segment definition, or a new sales motion can move them. Recheck the medians each quarter before relying on the dates.
- Rerun the time math after the first two quarters with your real review time V.
Common questions
- Why use the median sales cycle instead of the average?
- A few very long deals pull the average up, so it describes almost no real deal. In the fictional example, the average across 21 won deals is 1,619 / 21 = 77.1 days while the median is 60, and the 75th percentile of 110 shows the slow end directly.
- Why does one blended sales cycle mistime pipeline creation?
- Create-by date = quarter end - cycle days, so a segment slower than the blend needs its pipeline earlier than the blend says. In the fictional example, the blended median of 60 days gives 2027-01-30 for the quarter ending 2027-03-31, while Enterprise at 160 days needed its pipeline by 2026-10-22, 100 days earlier.
- What is the dollar-weighted median cycle?
- Sort won deals by cycle days, add up their amounts, and take the days at which the running total first reaches half of won dollars. It shows how long the deals carrying half the revenue took: in the fictional example, 135 days against a median of 60 by deal count.
- Why measure only won deals, selected by close date?
- Lost and open deals have no won cycle. Selecting won deals by close date keeps slow deals that were created long ago; selecting by created date would drop deals still open, which are often the slow ones, and make recent cycles look shorter than they are.
- How much time does it save?
- With the example inputs, building the table by hand takes 6 hours a quarter and reviewing the prompt or template run takes 0.8, so 5.2 hours a quarter are saved, about 0.4 hours a week. That is worth $390 a quarter at $75 an hour against an example $60 a quarter LLM plan share.
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 - 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 - 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&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
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 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