I recently collaborated with a co-worker to create a solution for tracking week-over-week changes to specific metrics on Opportunities. The client wants to see several specific changes and what the count was for changes either up or down. In addition to these specific metrics that required a custom solution, they also wanted to track a number of other metrics.
The Challenge
There are six specific metrics that the client asked us to track:
- How many Opportunities moved forward
- How many Opportunities moved backward
- How many Opportunities have a higher Estimated ARR
- How many Opportunities have a lower Estimated ARR
- How many Opportunities are scheduled to close sooner
- How many Opportunities are scheduled to close later
In addition to those stats, they also wanted to see:
- How many new Opportunities were created this week
- How many Opporties were lost this week
- What was the sum of the Estimated ARR last week
- What is the sum of the Estimated ARR this week
- What is the sum of the Estimated ARR on just the new Opportunities this week
- What is the sum of the Estimated ARR on lost Opportunities this week
- What is the sum of the Estimated ARR on won Opportunities this week
The Solution, Part 1
We discussed the possibility of using various standard reports and reporting snapshots to solve for the first set of requirements, but ultimately we decided on a custom set of fields and two Flows. Everything in the second part of the requirements was easily solved with standard reports set up with the right fields and filters. There’s only one of those reports that I want to show later.
In order to create the six data points, we needed to know what the information was on the record at the close of the week last week in order to compare it to this week (today). We added the first three fields to store the probability for tracking changes to the stage, the Estimated ARR, and the Close Date. We then created checkboxes to keep track of the changes.
The Solution, Part 2
In order to get the first three values, we created a Scheduled-Triggered Flow that will run early morning, every Sunday. This will store the new values for comparison each week. All this flow does is copy the record’s values for the Close Date, Estimated ARR, and Probability into the “Last Week” fields.
The Solution, Part 3
Now that we have the initial values to compare each week, we need the ability to track those changes. We built a second Record-Triggered Flow to watch for changes. By comparing to the previous week’s value, we’re able to determine which way the values have changed without needing complex formulas. Each decision element checks for a Great Than or Less Than value and then checks the correct box. If True is currently checked and the record is updated, it will be checked again. As long as the value is still greater than last week, the checkbox isn’t changed. But if the value drops below the prior value, the Down box is checked and the Up box is unchecked. This allows for the values to fluctuate throughout the week while still properly comparing the current value to last week.
The Solution, Part 4
All of this gets pulled together into a single report. These values can then be shown as a Metric component on the Dashboard, which ensures a clean presentation of the data in the correct format.
The Solution, Part 5
There is one other custom field called “Last Stage Change Date” that keeps track of the last time the Opportunity Stage is changed. This field ensures that we can filter for records where the stage was changed in the current week so that our pipeline changes are accurate.