Close Dates In The Past Wreck Pipeline Reports | 5 Ways To Stop It Happening

Colin Parish, VP of Sales at Moderna, read our blog post, 12 Must-Have Charts On Your Salesforce Dashboard.

“That’s the dashboard for me”, thought Colin. “Especially the Pipeline by Stage and Month.”

So Colin had his system administrator install the dashboard from the AppExchange.

But there was a problem.

A big problem.

Colin’s graph didn’t look like the beautiful example of the most important dashboard chart in our blog post.

Colin’s was, well, to put it frankly, a mess.

It was full of deals with opportunity close dates in the past.

“It looked more like a bedraggled washing line”, said Colin.

Sales Dashboard by GSP

Superb Pipeline Visibility and Sales
Performance Metrics from this free Dashboard.

The result?

Colin didn’t get the pipeline visibility he craved.

What is the Opportunity Close Date?

The Opportunity Close Date is the date the salesperson believes the deal is likely to be completed.

That’s a judgement, based on imperfect information. Of course, it means the Close Date can legitimately move during the deal life-cycle.

Nevertheless, accurate Close Dates are foundation for a reliable pipeline forecast.

Colin’s Opportunity Close Dates

Unfortunately, close dates in the past destroyed the benefits the chart brings. Because unless you have a time turner, an opportunity isn’t going to close successfully in the past.

Consequently, Colin couldn’t tell which deals were still alive and which had been lost.

What’s more, he had no sensible view of the pipeline or the sales forecast.

So Colin called us up. Asked what he should do. Here’s what we said.

We explained to Colin that there are two sides to the problem.

Here’s the first: Existing deals with opportunity close dates in the past.

In other words, Colin needs to sort out the existing opportunities with a close date in the past. We explained the five ways to do this.

Second, Colin needs to stop the ‘opportunity close dates in the past problem’ from recurring. Again, there are several ways to do this.

So, here’s what Colin did to solve the problem. And what he’s doing now to stop it happening again.

If your pipeline chart looks like a bedraggled washing line, you can easily do the same as Colin.

Fix the immediate ‘Close Dates in the past’ problem

There are five ways to deal with the problem of opportunities with close dates in the past.

  1. Go through the opportunities one by one yourself.
  2. Mass update all out-of-date opportunities to Closed Won or Closed Lost.
  3. Have salespeople update their own opportunities.
  4. Mass update all Close Dates in the past, to a future date.
  5. Sweep the problem under the carpet.

Yes, you read that last one right.

Bottom line:

To fully solve the close dates in the past problem you need to apply several of these steps.

Now I’ll explain EXACTLY what each of these strategies with details of what actions to take.

1. Go through the opportunities one by one yourself

With this approach, you personally update the Close Date on each opportunity.

Consequently, you’re likely in for a late night. Unfortunately sometimes its the only option.

Whilst you’re at it, update the Opportunity Stage for deals that should no longer be in the sales pipeline. For example, change the Opportunity Stage to Closed Won or Closed Lost.

Tip: Create an Opportunity List View to make the editing and updating process easier. If your salesforce implementation uses Opportunity Record Types, you’ll need to filter for each record type in order to update directly in the list view.

Here’s when step 1 is appropriate:

  • There’s a relatively small number of opportunities.
  • Updating each opportunity with an accurate close date is important.
  • You’re prepared to do the work yourself (or it’s not realistic to get anyone else do it).

Good luck.

2. Mass update opportunities to Closed Won or Closed Lost

This is the broad-brush approach.

Here’s what you do:

Set all opportunities with a close date in the past to Closed Won or Closed Lost.

On the one hand, that lacks subtlety.

However, you can be a little more sophisticated.

For example, you might decide to take this action for all opportunities where the close date is more than one year in the past.

As with step 1, you can use a List View to update many opportunities at the same time.

Alternatively, do the update using the Data Loader. If you’re unsure how to do this, get in touch. I’ll show you.

Here’s when step 2 is appropriate:

  • The accuracy of opportunities with close dates in the past doesn’t matter too much. Or at least, it doesn’t matter for opportunities more than a year old.
  • There are far too many opportunities to go through one by one.
  • You are prepared to sacrifice the accuracy of historic sales performance reports in order to get an up to date pipeline forecast.

3. Have salespeople update their own opportunities

This is a variation of option 1.

Have the Opportunity Owners to do their own dirty work.

It means you get them to go through their own opportunities and update the Close Dates and (where appropriate) the Opportunity Stage.

It does beg one question: what to about opportunities owned by salespeople that are no longer with the company?

You might end up having to do these yourself.

This step is appropriate when:

  • The accuracy of reports and charts that track historic sales performance is important.
  • There are viable deals that have close dates in the past. Salespeople can pick these up as they go through the opportunities.
  • It is a worthwhile investment in time for salespeople to review out of date opportunities.

Needless to say, you can combine step three with steps one and two.

4. Mass update all Close Dates in the past to a future date

This means taking all out-of-date opportunities that are still open, and giving them a close date in the future.

Then you – or the sales team – take time to update each opportunity individually.

Consequently, its a variation on the earlier steps.

This approach is appropriate when:

  • There are live or viable opportunities with close dates in the past. You probably want to take a fresh look at these.
  • No-one has the time to sort out the out-of-date opportunities right now. Move them all to the future and wade through the deals when you have time.

Bear in mind: with this step you need to accept that the pipeline chart will contain lost or dormant deals until the opportunities are updated.

5. Sweep the problem under the carpet

This step means you modify reports that underpin the dashboard chart.

Specifically, change the Close Date ‘From’ value on the report so that it only includes opportunities where the close date is greater than a specific point in time.

For example, filter the report to show opportunities with a Close Date ‘From’ the first day of this month.

Consequently, there will only be a small number of opportunities on the report with close dates in the past.

This approach is appropriate when:

  • It is unlikely anyone will get around to updating out-of-date opportunities.
  • The pipeline chart will be based only on opportunities with close dates greater than the date you have chosen – and you are prepared to accept this.
  • Your system administrator acknowledges that all dashboard pipeline reports will need to incorporate the fixed ‘From’ date.

Take a multi-step approach

Of course, it makes sense to combine some of these options.

For example, you might do a mass that sets opportunities with a close date of more than one year ago, to Closed Lost.

Then, update the remainder so they have a Close Date in the future. Have salespeople go through these deals one by one to pick out the viable deals.

Stop the ‘close dates in the past’ problem from recurring

If the pipeline chart contains deals with Close Dates in the past then you lack clear pipeline visibility.

That means you can’t get an accurate revenue forecast. And it is impossible to know whether you have enough pipeline, to meet future sales targets.

So, here are four ways you stop the problem happening again.

1. Avoid sloppy management

Proactive sales management means being on top of the pipeline. In that case, there shouldn’t be any deals with close dates in the past.

Simple as that.

Good sales management means the sales pipeline is well maintained. It gives sales managers the key information they need to conduct funnel reviews at all times.

2. Coach salespeople to self-manage their pipeline

Sloppy sales management is only part of the story.

Effective salespeople don’t allow their pipelines to become out of date.

Salespeople need to understand the importance of keeping the Close Dates and Opportunity Stages accurate. That means each person has an accurate view of his or her pipeline.

3. Create an alert when the Close Date is today

Use workflow to create an email alert when an Opportunity is due to close today. This draws the salesperson’s attention to the deal so that they update it.

Optionally, trigger the alert when the Close Date is tomorrow.

This is a useful technique when you need to emphasize the importance of keeping deals up to date. Ideally, salespeople self-manage their pipeline and using dashboard charts tailored to their needs.

However, if you want to draw more attention to deals that need to be updated, then this is one way to do it.

4. Use a validation rule

A validation rule kicks-in when a salesperson makes a change to an opportunity. If the close date is in the past, this prevents the opportunity saving.

Effectively, it means the salesperson has to update the close date in order to make any change.

This solution is often implemented by companies that have a problem with close dates. However, I’m not the greatest fan.

The validation rule approach doesn’t actually prevent the problem from occurring. If the opportunity is not updated (which, given that the close date is in the past suggests is the case) then it won’t prevent close dates from drifting into the past.

The most effective approach is to apply good sales management practice and have salespeople take pride in the accuracy of their individual funnels.

How Colin solved his close dates in the past problem

Colin had several hundred opportunities with close dates in the past.

Here’s what he did.

  1. Colin used an Opportunity List View to quickly identify deals he knew for sure had been won. He updated them on the salesperson’s behalf to Closed Won.
  2. Then he set all deals more than a year old to Closed Lost. Some of these deals were probably won. However, as the opportunity was out of date, it’s likely many were lost. Colin accepted the risk of inaccuracy in historic reports.
  3. He assigned two hours one Friday afternoon. Each salesperson reviewed and updated their own opportunities during this time. A number of dormant opportunities were re-energized as a result of this focused review.
  4. Colin explained to his team managers the importance of good pipeline management.
  5. He had everyone read our blog post about the Open Opportunities by Stage and Month.
  6. Colin played this video at his team meeting. The video and blog post gave managers valuable insight into how to use the dashboard chart to manage the pipeline effectively.
  7. Colin had every sales manager explain the importance to salespeople at local sales team meetings.
  8. He mandated a review of the Open Opportunities by Stage dashboard chart at every sales meeting.
  9. Colin got his system administrator to create a second version of the sales dashboard. This runs on ‘My Opportunities’. The sales managers educated each salesperson on how to use the dashboard to analyze their own pipeline and sales performance.

The result?

Colin gets a robust view of the company sales pipeline. Now, he accurately identifies the action sales people and managers need to take to boost revenue. And it means Colin is confident of making is quota.

“Now, this truly is the dashboard chart for me”, says Colin.

12 Must-Have Dashboard Charts

Our 27 page eBook shows you the 12 killer
Sales Charts for your dashboard.

Popular eBook


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!