How to Count the Number of Opportunity Close Date Changes
Anyone that runs a sales team needs to be able to quickly identify dud pipeline deals. Those opportunities that have little chance of ever closing successfully but which are inflating the size of the pipeline. The ones that give you a false sense of future sales revenue.
The dashboard table on the left gives you this information. It ranks the 10 Opportunities on which the Close Date has been changed the most number of times. And shows the Created Date for each Opportunity.
Look at the Opportunity at the top of the table for example. It was created in June 2013. That’s 9 months ago. And the Close Date has been changed 13 times. If your typical sales cycle is (say) 3 to 4 months, is that a solid pipeline deal that you can rely on? Or is it consistently being moved along in order to maintain the size of the sales pipeline?
Either way, this powerful table gives busy Executives the information they need to drill into specific areas of the sales pipeline in more detail.
The detailed instructions on how to configure Salesforce to create the underlying report and dashboard table are contained in the rest of this blog. If it’s a management perspective you’re after then review our list of other blog posts on the effective use of Salesforce dashboards.
Like all Salesforce dashboard charts and tables this one is based on an underlying report. Here’s the report that drives this table. It’s a matrix report that lists all open Opportunities by Opportunity Owner and displays the Created Date across the top.
There’s one figure within the report for each Opportunity. That’s the number of times the Close Date has changed. We’ve added some conditional highlighting to make it easier for the eye to pick out the higher numbers.
Create the metric that counts Close Date changes
The field that’s driving the report is on the Opportunity. It’s the “# times Close Date has changed”.
Here’s how to make the field update automatically every time the Close Date is changed.
First create your field. The Field Type needs to be Number. The essential thing about this field is to set the Default Value to zero.
Then you need to create a Workflow Rule. Here are the three key things to know about this workflow rule.
- Set the Evaluation Criteria to “Created, and every time it’s edited”. We need this option because we want the workflow rule to be evaluated every time the Opportunity is updated.
- Select the “Formula evaluates to true” as the Rule Criteria.
- Use the formula ISCHANGED (Close Date) as the formula entry. In other words, the workflow rule should fire every time the Close Date is changed.
Then create a Field Update that is triggered whenever the workflow rule is fired.
In the field update use a formula to specify the new field value. The formula is the name of your custom field + 1. Save the field update and then go ahead and activate your workflow rule.
Remember that by default, your new field will only work for new Opportunities. This is because it’s only those Opportunities that will have the starting value of zero in the field you created. On the existing Opportunities the field will be blank.
So firstly test that your new field is working correctly by creating a new Opportunity. Change the Close Date and check that the field advances to 1. Tip: if you’ve followed the above instructions carefully and your field doesn’t update, chances are it’s because you haven’t activated the workflow rule. Activate the rule and create a new Opportunity to re-test the process.
Now you can address the issue of the existing Opportunities. The workflow rule and field update can’t fire retrospectively – in other words you can’t count Close Date changes that have already taken place. But you can count changes from now on.
To do this you need to set the value of your new field to be zero on the existing Opportunities. The Data Loader is one way to do this in bulk. Alternatively, if you’re not familiar with that tool then create a List View on the Opportunity Tab and mass update your new field. Tip: if you’re using Opportunity Record Types you need to include the Record Type as a filter in the List View criteria.
And that (almost) is it. Now you can go ahead and create your report.
Create the dashboard table
One final tip. To display the dashboard table in the way that we’ve shown, add a chart to the report. Format the chart in the way we’ve shown below, not forgetting to group by the Created Date. Then go ahead and create your dashboard table.
And that IS it! We’ve created a YouTube video that shows you exactly how to set up the field, workflow rule and field update. Don’t forget to read our other blog posts on getting the best from Salesforce dashboards.
Our 27 page eBook shows you the 12 killer
Sales Charts for your dashboard.
Don’t have time to read the entire Blog Post right now?
No problem.
You can download the entire “Your Sales Forecast Is Probably Wrong” eBook for free by completing the form below!