Expanding on Win Loss Ratio

One of the simplest reports that admins build in Salesforce is a Win/Loss Ratio report.  In its basic form, you create a report of all closed opportunities and break them up between Won vs. Lost (using the Won field or the Sales Stage itself).

I personally like to use the words Won and Lost, which may not be the exact words in Sales Stage.  So I create a bucket field called "Status" on my report that is based on the Sales Stage field.   I bucket my Won stages under "Won" and all my Lost stages under "Lost".

And with that, I can create a report with a chart based on my new bucket called Status.

This is especially useful if you have multiple sales processes or legacy data that had different stage names for Lost, e.g. Closed - Lost, Lost - Closed, Closed - Booked, Closed - Won.

Now the big question here is how useful is a raw Win/Loss Ratio for a sales manager.  It may be a mildly useful statistic that his sales team loses 50% of opportunities.  That may mean he must always have twice as much in his team's pipeline as his remaining target amount.

But if we take it a step further, a low win-loss ratio is just part of the story.  It would be useful to find out at what stage does his sales team lose your opportunities.  That may give the sales manager insight into how well his team qualifies an opportunity.

For example, losing an opportunity at an early sales stage may not necessarily be a bad thing.  It may just mean that the sales person qualified it, asked the right questions, and found that his product doesn't fit, or his customer has no budget.

Losing opportunities at a later stage may point to a different problem.  If a rep loses a deal right before the contracts were due back from the customer, it may mean he didn't qualify well enough, or he wasn't talking to the right people, or there was a change in direction at the customer.

On the flip side, winning an opportunity while it is still in an early stage could mean that you have too many stages, or the rep is not good at updating his pipeline for example.

Salesforce does not automatically capture the Sales Stage before a deal is won or lost.  While you can run reports on Opportunity History, it may be useful to create a workflow to capture the value on the Opportunity itself.  This helps you create reports based on Opportunities with Products, as Salesforce will not allow you to report on both the history table and the product object together.

Let's explore how to do it.

You will need a field to capture the Sales Stage Before Closure.  It could be a text field of 255 characters.

The workflow will be quite simple.  It should fire every time a record is updated, and the criteria would be when the Sales Stage is changed, and it is being closed.  Using a formula it would read:

AND (ISCHANGED( StageName ),  IsClosed )

This workflow will execute a field update on your custom field above to capture the previous sales stage.  To do so, you simply need the following formula:

TEXT( PRIORVALUE( StageName ))

This captures the Sales Stage before it is changed to Won or Lost.

And with this, you can create reports and charts to answer questions like: What is my Win Loss Ratio excluding those lost in Stage 1.  Or, how much is lost in Stage 4 through 7?  Who has been skipping Stage 1 to 3 and goes straight to Won?  And so on...