Identify Stuck Sales Deals

Learning Objectives

After completing this unit, you’ll be able to:

• Explain why it’s important to spot opportunities not moving through the sales cycle.
• Create reports and build dashboards to help identify stuck deals.

Introduction

Sales teams are always looking for ways to find new leads and close deals fast. We know that stagnating opportunities typically account for about 10 to 15 percent of the average sales pipeline. These stalled deals can upset your sales team’s performance. But with a few easy steps, you can help your team get insight into why these sales deals aren’t moving—and get them back on track.

In this module, you’ll:

• Build a custom report to identify stalled opportunities.
• Add a dashboard component to get visibility into stuck deals.

Let’s Get Started

If you’ve earned other Trailhead badges, you’re used to getting hands-on in your personal Trailhead Playground. For this module, we’re taking a slightly different approach. 

We recommend you use your company’s production org. Since you will just be building out reports and dashboards, it’s perfectly fine for you to follow along in your company’s org as you build out the new report and dashboard component. You can keep the report in a private folder until you’re ready to deploy it to your users.

You can use your free Trailhead Playground to follow along if you prefer.

Preparation

Before you start the step-by-step instructions, there is one important thing you need to do first.  We build reports around a field on the Opportunity called Stage Duration. This field tracks how long an opportunity has been in the current stage of the sales cycle. Many companies have rules about acceptable stage durations as part of their sales process. Stage durations can vary widely—some organizations have very long and complex sales processes and others have a more streamlined sales cycle. Work with your sales leadership to determine suitable stage durations for your business before tackling the next step.

Build a Stuck Opportunities Report

The first thing on our checklist for identifying stuck deals is to create a custom report. Here’s how.

1. Click  and select the Sales app.
2. Click Reports in the navigation bar.
3. Click New Report.
4. Enter opportunities in the text box, and select Opportunities in the report type selection popup.
5. Click Continue.
6. Once the report preview loads, click Outline. Let’s get rid of a few columns in the report that we don’t need. Click the X next to the following fields in the list to remove them from this report.
   • Owner Role
   • Fiscal Period
   • Expected Revenue
   • Probability
   • Created Date
   • Lead Source
   • Type
7. Let’s add a new column for the Stage Duration field. Click Add column... and type duration in the text box. Select Stage Duration to add it to the report.
8. In the report preview pane, scroll to the far right and click next to the Stage Duration column heading. Select Summarize and then deselect Sum.
9. In the report preview pane, look for the Amount column and click next to the Amount column heading. Select Summarize and then select Sum.
10. Next click Add Group just below GROUP ROWS. Type owner in the text box, and select Opportunity Owner to group the report.
11. Add a second group by clicking Add Group just below GROUP ROWS again. Type stage in the text box, and select Stage to group the report.
12. Click Filters.
13. Click Close Date and update the range picklist to All Time. Click Apply.

Next we’re going to build filters around the stage duration field. For this example, we’re going to use the following table of stage duration thresholds.

1. Click Add Filter. Type Stage into the text box and select it.
2. Keep the operator set to equals, and then select Qualification for value. Click Apply.
3. Click Add Filter and type Stage into the text box. Select Stage Duration.
4. Set the filter operator to greater than, and enter 30 for the value. Click Apply.
5. Repeat steps 1–4 for each of your opportunity stages and discount thresholds (see above).

If you're following along in your production org, your stage names may be different than the values in this example.

For our example above, here’s how this looks when complete.

Now, let’s add custom filter logic as follows.

1. Click  next to Filters and select Add Filter Logic.
2. In the edit filter logic text box, update the default values of 1 AND 2 AND 3 AND 4 AND 5 AND 6 AND 7 AND 8 to the following.

(1 AND 2) OR (3 AND 4) OR (5 AND 6)OR (7 AND 8)

1. Click Apply.
2. Click Save & Run.
3. Name the report Stuck Opportunities and store the report in your private reports folder for now.

The filter logic example above is for exactly four different opportunity stage values. If you are configuring a different amount of stage values, you need to adjust the filter logic accordingly. The general pattern above should work—just add or subtract additional conditions to the filter logic as needed.

If you have just one opportunity stage duration threshold for all opportunity stages, this extra filter logic isn’t needed. Just create a single filter for Stage Duration greater than your company’s threshold.

Next let’s add a chart to this report and visualize the amount of stuck opportunities per sales rep. Here’s how.

1. With the Stuck Opportunities report open, click Edit.
2. Click Add Chart.
3. Click  to open chart properties. Select the Stacked Bar chart type.
4. Verify that the X-axis is set to Sum of Amount, and Stack By is set to stage.
5. Click  to close the chart editor.
6. Click Save & Run.

Here’s an example chart from this report. Yours can look different depending on the opportunity data in the org you’re using and the stage duration thresholds you configured.

Based on the thresholds we defined, there are $4.5 million worth of stalled opportunities. Ouch! This sales team has some work to do.

Once you’ve got this basic report configured, review with sales management and get their input. Are the stage thresholds appropriate? Is anything missing? Incorporate their feedback and adjust the report if necessary. When the report is ready to deploy, you need to move it to a folder that your sales team can access. Here’s how.

1. Click Edit on the report and click  next to the Save button. Select Properties.
2. Click Select Folder, and then select a new folder to store the Stuck Opportunities report.  Make sure to select a folder that your sales team can access.
3. Click Save.

Resources

Build a Report in Lightning Experience—Salesforce Help Article

Reports & Dashboards for Lightning Experience—Trailhead Module