Probability for a deal to win

Discussion

Hi all,

I currently have a large dataset of deals that are either 'open', 'lost', or 'won'. Most deals that are won will take approximately 2-3 months.

I want to essentially forecast the subtotal of won deals in the next few months, but I'm having trouble setting up my model. Namely, I'm having trouble incorporating the 2-3 month average time for deals to be won. Because the probability for a deal to win increases with time, before it hits around 3 months and then it starts decreasing with time.

​

Does anyone have any ideas? I managed to implement a super basic model within my BI tool that follows a normal distribution, but I'm looking for something more robust. Thanks in advance!

Posted Sep 30, 2021, 8:10 PM UTC

214
tea_horse•

Have you tried fitting any models yet? Other than the BI tool one

The thing is, you seem to be basing this model off of time as your only parameter? I'm not convinced you'll ever get a good output from this alone. Sales deals are subject to a lot of stochastic events that make them incredibly difficult to predict (maybe your top sales rep got a better offer and jumped ship, taking the guts of the team with her)

You'll want to include other predictors here. Not sure what those would be, maybe performance of a sales team, win rate for a particular customer or product, addressable market etc

Also going back to the business question, what value does prediction give you here? Ok so you hit in and around the predicted value each month and the model works well...so what?

Perhaps instead of predictive models you should be looking at inference models, such models that would highlight opportunities that are more(or less) likely to win, allowing the teams to try focus on the opportunities with a greater win probability, which opportunities to send to put on the backburner etc

3
FishcorkOP•

Thanks for the reply! I should have been more specific, but yes I'm including predictors (win rates based on country, agents, markets, etc). The point of the prediction is to predict inventory stock, the plan is to use the probability to get some idea on what our demand will be like for certain materials for our products. That's why I want some kind of tangible 'expected quantity' from my model.

The thing that jumps to mind is a logistic regression, but I'm still having trouble applying this 'probability distribution' I have, where deals only begin to have a high P(win) a few months after it gets entered in our system.

2
yoi12321•

Maybe I'm misunderstanding but I think this is a classic hazard model? Basically instead of treating each deal as an observation you want to treat each deal+month (or whatever time unit) as an observation. Your model will then predict given all your other inputs as well as number of months since opened the probability the deal will be won, lost, or stay open this month. You can then use this model to calculate whatever you're interested in from probabilities of deals eventually winning, mean/median times to win, or even running monte carlo simulations over all deals and getting probability distributions if win/loss counts, number open at a time, whatever else you care about.

2
[deleted]•

[deleted]

1
playawhile•

The trick here is to create a list of unique "reference dates" from which to look ahead. Then you Cartesian join to a unique list of your deals. Now that you have every combination ... Delete rows where the reference date falls before the deal opened, and after it closed.

Now... Outer join with a range join to a list of deals and their close dates, and flag if the close date was within your timeframe from the reference date.

When you build your model, you'll have to decide between using group partition so that all the rows for a deal exist in either training or validation (random would split them up.. can overfit ) or if you have lots and lots of deals then you can use time based partition so you can do backtesting and validation based on date.

This is the trick to setting up your data with a deal, reference date, and target.

Now it gets difficult, but you join to your database and add attributes about the deal but always base them on the reference date so that your data doesn't contain data which occurred after the reference date. I do this kind of model all the time and the biggest mistake is adding a field like "deal health" which can be updated and change over time... So you can't have a reference date which is 3 days after the deal opened, and a "deal health" of green when the sales rep didn't actually set it to green until many weeks later.

Data setup is key to this type of model.

1
playawhile•

Oh I totally misread your problem. Didnt realize you were aggregating and forecasting... But I'll leave the comment here in case it helps someone else!

I think the answer to your challenge may include the frame of reference method as well.

Sorry!

1
FishcorkOP•

Thanks for the reply! In fact, this is exactly what I've done in my BI tool - I created reference dates up to a whole year, and I calculated probabilities in each reference date. I then multiplied each probability with the quantity of the deal, giving me an 'expected quantity' in each reference point. This actually works well when I'm predicting inventory levels.

The limitation of this is that I'm stuck with a normal distribution (and also.. because it's in a BI tool and it's very slow ), and it's why I want to carry it out in Python. Because I have a wider range of tools, I figured that this subreddit might have some suggestions to this.

Your suggestion to use time based partition for backtesting and validation based on date is a great idea - the lack of backtesting was also one of the limitations I am currently having right now. Thanks a lot!

1
playawhile•

To assemble the dataset, I prefer to use SQL but that's because I'm very familiar at SQL, and databases are very fast at this work.

Python would work, and you can even use some libraries to use the SQL language there... But I find python to be slow at this. But maybe that's your only choice.

Once you have a dataset, whether at the desk level predicting yes or no for whether it will close within the window... Or of you're aggregating by the reference date and then forecasting the close percentage over time... That modeling work is always done in python (or of your company pays for a fancy Auto ML tool)

1
playawhile•

Not sure what you mean about being stuck with a normal distribution??

1
FishcorkOP•

Do you mind me asking how you set up and carry out models like this (specifically, tools or resources that you may have)? I've never really come across models like this and although my model in the BI tool works, it's not very robust. I've also made the same mistake in adding a 'deal health' because the sales reps are not very good at updating their information :P

1
playawhile•

To build the models, get started with learning some python. Most popular is to install Anaconda and use Jupyter notebook. The packages you'll want to learn are pandas and sklearn. Sklearn has random Forest which is a great algorithm to learn as your first one.

1
playawhile•

Now that I've read your problem more closely, I think what you have here is a problem best addressed by a time series model. These are not for the faint of heart because time series problems on general can be quite tricky to setup and think about.

I think you're mixing probabilities and forecasting, which are two separate projects.

To forecast several time periods ahead, setup your data with a list of evenly spaced reference dates like we talked about before... And then for each date, join to your data and calculate how many deals were won (or whatever you want to predict). If you want to do this by "groups" or product types, this is what is called a multiseries problem because each product type would have it's own set of dates. But I will explain as a single series from here on.

Now that you have a list of reference points and the thing you want to forecast, your intuition of adding more variables is spot on. Try adding variables like, [number of open deals] and [number of deals opened] next to each date as well. Maybe [number of open deals of type ___] and so forth.

Now this is your base dataset. You might try to forecast in python with prophet which is one of the easiest packages to get started, but I've not found it to be the best on many cases.

There are so many different ways to do the modeling that I can't explain it all here, but assembling your data is the first challenge. If you want to talk in detail about the modeling part later, you can always DM me and I'd be happy to help you out on zoom or something.

1
FishcorkOP•

Sorry, I have been incorrectly using these terms interchangeably. I'm essentially just looking for monthly probabilities of all deals winning. So for example, if I had a deal that was created today, I want to assign it a probability in each of the next few months. (for example, if a deal was created today, it would have a 5% chance of winning this month, 10% the next month, 50% two months from now, 20% three months from now.) My goal now is to find a way to determine & apply the distribution of this probability. Does that explain this a bit more? I'd also like to apply different distributions to different customer groups if possible.

The reason why I (incorrectly) mention forecasting is because I eventually use these probabilities to calculate expected quantities of inventory stock. Management in my company calls this forecasting :P

I've played around with timeseries briefly using FBProphet (it was really just playing around with a cool toy) but I'm not sure if it's what Im looking for, because my predictions are based on deals created (quotes), instead of trends over time. This is really racking my brain, because I feel like I've learned something in school that applies to this problem perfectly, and yet I can't seem to recall it.

1
playawhile•

Ah, I get it now. Timeseries and forecasting is not what you want, then. I think what you're looking for is a multiclass problem setup - for each deal predict whether it would close in either 1mo, 2mo, 3mo, or not close (4 classes). Then, each probability would add up to 100%.

The challenge here is that most multiclass algorithms will work under the assumption that the classes are independent of each other and there is no order implied (like Red, Green, Blue). In your case it does.

This link gives you an idea of how you can setup the data: https://towardsdatascience.com/simple-trick-to-train-an-ordinal-regression-with-any-classifier-6911183d2a3c

2
FishcorkOP•

Thanks a lot man, I appreciate your help. I’ll consider this and see how it goes :)

1