Stage Duration Report in Salesforce

What is stage duration?

Stage Duration is a count of the number of days an opportunity is in a particular sales stage, specifically from the date when opportunity changes to current stage until today. To determine stage duration of sales opportunities in Salesforce, an Opportunity History Report can be run in Salesforce.

Why use a stage duration report?

A Stage Duration Report is often used to understand how long a sales representative is taking to progress through sales stages. It is an important pointer of the health and velocity of a pipeline or an opportunity.

To maintain a healthy business, it is important to understand the duration of time required for several types of opportunities to close and how much time is spent in each stage (for instance, doing due diligence vs. getting approvals from all stakeholders vs. negotiating final price). With this information, sales management can do more accurate forecasting and optimize stages that tend to take longer than what’s reasonable or expected.

What are the challenges with stage duration reporting in Salesforce?

Building a Stage Duration Report in Salesforce is based on the sales duration field, which as discussed above, displays the count of days that an opportunity has spent in that stage. However, this field comes with certain challenges –

• One cannot show the stage duration field on a page layout. The field will be missing when one looks through all the standard fields on the opportunity object.
• The data is only available through standard reports. As stated above, if one builds a custom report, the Stage Duration field would not be available.
• Unless a validation rule is set, moving through Sales Stages is not subsequent. For example, if I move from ‘Qualification’ to Closed Won’ after 3 days, missing, say 6 stages, the stage duration will be 3 days for Qualification with no data for any consequent stages.

How to build a stage duration report

We will create a sample Stage Duration Report in Salesforce. For this example, we will generate an Opportunity History report to view Sales Duration data.

1. Go to the Reports tab and click on New Report.

2. Select the Report Type as Opportunities. Expand it and select Opportunity History from the drop down list. Click on Create.

3. In the Fields pane, use Quick Find to search for Stage Duration.

4. Drag Stage Duration into the Column on Preview Pane. This report shows Opportunities, From Stage, To Stage and Stage Duration. Additionally, we can calculate averages the following way…

5. Click on the drop down menu on Stage Duration and select Summarize this field.

6. In the Summarize pop-up box, select Average and click Apply.

7. Change the report format to Summary Format.

8. Group the report by To Stage. Drag To Stage from the Fields pane to Preview pane.

9. If you don’t want to see all opportunities details, click on Show and deselect Details. Click Run Report.

Stage Duration use case

As discussed earlier, the stage duration field can be incorporated in many custom reports. Let’s look at a specific example which is slightly more complex to configure. In this scenario, we will create an Opportunity Report that lists the opportunities you need to know about. This is helpful in terms of what opportunities should be on the top of mind with the Executive team, when they discuss health and velocity of pipeline with the Sales team. This custom report can be helpful, as part of a Salesforce dashboard, for founders and startup leaders.

1. Create a new Opportunities Report.

2. Show All Opportunities with an Open Status and set Probability as All and set Close Date to Current and Next FQ.

3. Group the information by Stage.

4. Group the information by Type.

5. Add Filters in the following order –
   1. Set Close Date as less or equal to This Week.
   2. Set Stage Duration is greater than 45. (Note – This field only accepts number. The figure is considered as days by default.)
   3. Set Stage equals (choose your organization’s low level stages, such as Prospect)
   4. Set Stage equals (choose your organization’s high level stages, such as Closing Prep)
   5. Set Stage Duration is greater than 20.
   6. Set Has Products equals False.
   7. Set Stage as not equal to Closed Lost, Qualified Out, No Purchase.
   8. Set Age is less than 30.
6. Add Filter Logic as ((1 OR (2 AND 3) OR (4 AND 5) OR (6 AND 7)) OR 8). Click Ok and then Run Report.

This report tracks stuck opportunities, but with additional details such as deals that are set to close in the next week and deals that need to speed up the process. This is particularly useful for a weekly sales meeting, and also to quickly identify trends that the sales leadership team should be aware of.