Isabela Class
To explore
01SproutingWork in progress

When will this deal close?

Understanding the when of a deal is paramount to building an accurate forecast. Misjudging the expected time to close does not make the revenue disappear; it pushes it into the next month and, in some cases, into the next quarter, jeopardizing the target. This study was born to build a model that makes revenue predictability tangible: a monthly revenue forecast, tested against what actually happened.

Here, closing means winning: the deal becomes revenue. When it leaves the pipeline, won or lost, I say it was resolved.

  • 8,800 deals
  • Public synthetic dataset (Maven CRM)
  • Python · discrete-time hazard model

In short

  1. Time to win has two groups: 42% of wins come within 20 days and 54% take 45 to 138. The average describes almost nobody.
  2. Out of every 100 deals that enter negotiation, about 51 are won, 32 lost and 16 stay stuck forever. After 138 days open, none was won.
  3. What decides the when is deal age and month of the quarter. Product, sales rep, region and account size did not improve the forecast.
  4. Across seven tests, the monthly forecast missed by 13% on average. The classic pipeline × win rate forecast predicted three times the actual.
  5. A simple alarm found a region that tripled its pipeline in three weeks with deals that got stuck.

I started with three hypotheses

Before looking at the data, I wrote down what I expected, from my experience in Sales Ops. Writing it down first is what lets me say, later, what the data confirmed and what it overturned.

Not confirmed

Deals that move fast through the pipeline convert more.

Among resolved deals, those that leave the pipeline within 20 days are won 57% of the time; slower ones, 68%. Moving fast more often means losing fast. At the extreme the idea holds: after 138 days, no deal was won.

True for product, not for size

High tickets drive more revenue than many small deals.

The three products priced above 3,000 are 44% of deals and 84% of revenue, with the same win rate and timing as the cheap ones. Large accounts bring more because they open more deals: the ticket barely changes, and timing is the same at every size.

The data supports it

Since conversion is hard to move, volume drives revenue.

Conversion varies little across reps (55% to 70%) while volume varies a lot (79 to 553 deals). About 60% of the revenue gap between them comes from volume, 40% from ticket and almost none from conversion. It compares reps; it does not prove cause.

The average lies

The obvious way to measure how long a deal takes to be won is to average the wins. That gives 52 days, but almost no win happens anywhere near that.

There are two groups: 42% of wins come within 20 days and 54% take 45 to 138. In between, only 4%. A forecast built on the average gets both wrong.

0400800average: 52 days0204590138days between entering negotiation and the win

Three outcomes, counting open deals

Looking only at wins forgets the lost deals and the ones still open, and many of those will never move. It is like measuring wait time by looking only at people who were served.

Counting everyone, the question changes from "how long does it take" to "what happens to every 100 deals that come in".

Deals already won, by days since entry
0%25%50%75%100%138among wins onlyof every deal that came in · 51.4%0306090120150days since entering negotiation

The dataset only records resolutions between March and December 2017. To keep the curve honest, I used only deals that entered between 2017-03-01 and 2017-08-15, which have the full 138 days of observation, and left out the unusual block shown further down.

Explore: a deal open for X days

The question that helps whoever manages a pipeline the most. Pick how many days the deal has been in negotiation and which month of the quarter it came in.

Came in during the
Is won in the next 30 days
19%
Is ever won
53%
Stays stuck forever
23%
Chance of being won in the next 30 days, by age
0%25%50%03570105140

Based on 1,061 historical deals that were open at this age.

From chance to revenue

A 4,000 deal with a 30% chance of being won in November counts as 1,200 in the November forecast. Adding that up for every open deal, and for the deals still to come that month, gives the expected revenue.

The chance comes from history: among deals that were open at the same age, in the same month of the quarter, what share was won each day. I tested which features deserved a place. Only age and the calendar improved the forecast; product, rep, manager, region and account size did not.

The honest test

I went back to 2017-09-30 and asked each model, using only what was known that day, how much revenue the open deals would bring in the next quarter. Then I compared it with what happened.

  • 57% of the quarter’s revenue came from deals that did not exist on 09/30. The full forecast adds that part, projected from the entry rate of the previous 90 days (2% error).
  • Across seven cut-off dates, forecasting the next month, the average error was 35% with age only, 18% with age and calendar, and 13% with the alarm.

The inflated pipeline alarm

The biggest remaining error came from a block of deals that got stuck. It was not the whole company: it was the Central region, which usually opens about 62 deals a week and opened 540 in three weeks. Of those, 355 were never won or lost.

flagged weeks0100200CentralEastWestMay 29Jun 26Jul 24Aug 21Sep 18new deals per week

The rule flags a week when two things happen together, using only what is known on the forecast date: the region’s new deals reach 1.5 times its normal level, and the share resolved within 20 days falls well below the other regions in the same week. The estimated excess is removed from the forecast.

Without the first condition, the West region was flagged five times with no spike at all. With both, only the three Central weeks show up.

What did not work

  • Logistic regression of won vs. lost. Trained on resolved deals, it separated wins from losses no better than a coin flip (AUC 0.49 to 0.53). In this dataset, winning or losing depends largely on when the deal is resolved.
  • More features in a tree model. With product, rep, manager and region, it improved on one test and got worse on the other: it memorized noise.
  • The empty account field. Every deal without an account is still open, so the field gives away the outcome. Using it in the test would mean using the future, so it stayed out.

Limits and next steps

  • The dataset is synthetic. The method holds; the numbers belong to this fictional company.
  • There is no activity history. With it, "days since last contact" would likely be the strongest feature.
  • Seven tests are too few to state the accuracy with confidence.

Next steps: deliver the forecast as a range, with how confident we are that the actual falls inside it, and explain in plain language why each deal is at risk.

The full notebook, with code and outputs, is on GitHub.