Following on from my previous article where I talk about the idea of a Bayesian filter for CRM, I did a bit of digging on the interwebs and found this Excel Bayesian Classifier. Being open source it is easy to review the VBA to see how it works (which I plan to do at some point and see if I can make a workflow version or similar).
The default example is about movie ratings but it is easy to convert this to be more ‘CRM friendly’. Here is one I prepared earlier:
In this example, I use three variables (one more than the example provided out of the box):
- Value Range (this is the range an estimate for the value of the opportunity falls in to. For example, ‘Low’ may be deals worth $0-$10,000)
- Company Type (Private or Government)
- Relationship (How good our relationship is with the prospect)
and the results of these ‘historical opportunities’ are shown in the fourth column. We can think of this as the Closed Opportunities view entered into the spreadsheet.
The discerning eye will be able to work out the kind of deals we succeed at but, if it is not obvious (and if you are dealing with many opportunity examples it will not be) this is where our Bayesian Classifier comes in.
Here are the statistical predicted outcomes for the variables in the absence of all other information.
In other words, we do well at the smaller deals, deals with private organisations and deals where we have a close relationship with the prospect.
We can now dump our Open Opportunities view into the first three columns and the spreadsheet will predict which ones we will win, based on the information gained from the historical ones.
While, in this case, the result is a prediction of whether the opportunity will be won or lost, it is also possible to return a confidence level (percentage) on the likelihood of the outcome. This would be used as our probability percentage for winning the deal.
What I really like about this spreadsheet is that it appears to allow for any number of input variables. In my case I used three but you could used more.
Strictly speaking, this is what is called a ‘naive’ classifier which means it assumes there is no relationship between the three variables. If it was such that we only went for large government deals, this assumption would not be correct and this dependence would affect the system’s ability to accurately predict the result.
So Why Are We Going To All This Effort?
I briefly outlined the reasons in my last blog but Phil Richardson, former heavy-hitter on the Microsoft CRM development team and college friend, who has recently moved into the position of Head of Baby Roasting at salesforce (not his real title), wrote an article a few years ago talking about the problems of pipeline revenue projection using a human-estimated probability. Some of the problems he references are:
- Probability estimate usually either guessed or blindly refers to the sales stage
- Historical success not considered
- Historical opportunity characteristics not considered
- Does not consider the historical success of the salesperson’s estimations
The use of a classifier addresses all of these issues in one hit.
Another option would be to use a ‘scorecard’ to score an opportunity based on predicted ‘good values’. The main problem with a scorecard system is you must determine what the good values are and you must work out how many points to assign each value. The Bayesian classifier does this automatically through the statistical calculations.
If you have a large volume of some kind of record in your CRM system (opportunities, leads, cases etc.) and you think that certain characteristics may govern the fate of these records (win/lose, conversion to an opportunity, routing to a specific queue etc.) download the spreadsheet, feed it some historical data and see how it goes at predicting either other historical outcomes or keep a tally on how it goes with existing records.