In every Salesforce Admin’s life, there comes a moment when somebody asks, “Who changed that Opportunity stage?”, “When did they change it?”, or “How long were Opportunities in each stage?” At some point, most admins need to look at historical changes to certain fields or possibly duration. However, they may wonder which report to use. While most objects have a history report available, why do Opportunities have two reports that look almost the same? So, what does it all mean?
Well, fear not because this post will cover everything you need to know about these two seemingly similar report types. You’ll find out what the differences are and how you can use both to get a thorough understanding of what is happening in your Opportunities.
Identify Separate Report Types
To begin with, we’re going to discuss history reports in general and the Opportunity Field History report that appears similar to all the others but has a slightly different name. Ignore the Opportunity History report for now – we’ll come back to that later.
Opportunity Field History Reports
History reports are available for objects where field history tracking is enabled. In Setup, navigate to the object you want to review, go to Fields & Relationships, and Set History Tracking. If you’re in a brand new org, you’ll need to do this pretty much everywhere. If you’re in an existing org, you can look at what fields already have history tracking enabled.
A few things to note about history tracking on fields (out of the box):
- You can track history for up to 20 fields per object.
- Long text fields can only show an edit, not what the change was (no old and new values).
- History tracking can be enabled or disabled. Historical changes won’t disappear if you disable history tracking for a given field.
- Similarly, history tracking works only from the moment you enable it for a field (you won’t see anything older from before you enabled tracking).
And if you’ve ever looked at the history section on a page layout, Field History is what is controlling that.
In this example, I have enabled historical tracking for Account Name. As a result, when I changed the Account Name, a log was created – showing the date and time of the change, the field that was modified, my name, the old value, and the new value.
This sort of tracking is priceless for important fields, like status and stage values, owner fields, source fields, and contact information fields, like email, phone, or opt out. Setting up history tracking in this way allows you to use those history reports for all the objects where it is set up.
The most important thing to remember about these history reports is to enable tracking and select which fields to track. Otherwise, your history reports will show nothing.
Opportunity History Report
Now, let’s go back to Opportunities and why history is special and different for them. Hopefully, you noticed that we skipped one of the report types earlier. Above, we only looked at Opportunity Field History, which works like all the other reports that contain “History” in their names. We also know that those history reports only work when the tracking is set up.
Opportunities are special because they are the only objects with out-of-the-box tracking for certain fields that admins do not have to set up and track additional information that’s not in the standard history report. The report type is Opportunity History – it’s little used but very powerful.
- Opportunity History report does not need to be set up, and field tracking does not need to be enabled.
- You cannot select which fields are tracked.
However, the most useful thing this report does is to track stage duration for every single stage change that happened on an Opportunity. This is a huge deal! Even a normal Opportunity report only shows the duration for the current stage – not all historical changes! Looking at this example org, we can see that I have not enabled any field history tracking on the Opportunity object:
But even though I have not done this, the Opportunity History report still shows the changes that have been made! In this example, I have changed several important things on this Opportunity, such as the name, amount, and stage:
There is a lot to notice in this report. First, you’ll see the columns “Last Modified” and “Last Modified By”. These refer to the ‘change record’ and not the Opportunity record, which is why they are not called “Edited Date” and “Edited By” like in the other report.
You’ll also see “From Stage” and “To Stage”, but sometimes there’s no change at all there. That’s because I made additional changes besides the stage (like the amount, name, or close date). Additionally, notice the “Stage Change” checkbox – this is what you can filter on if you only want to look at stage changes, but not the other changes.
Lastly, the “Stage Duration” – sometimes stage changes will happen on the same day, so their duration is blank. But if there is any time between stage changes, the number of days will appear in the “Stage Duration” column.
Stage Duration
This report shows information about one Opportunity – including each stage it went through, who it was modified by, and the number of days spent in that stage:
By grouping the report by “From Stage” and “To Stage”, as well as using the Average Stage Duration, we can then get this awesome report:
There is a lot to look at here, and you might notice a diagonal ‘stairs’ effect happening:
On the left, we can see the “From Stage” column and the “To Stage” column across the top. An Opportunity cannot move from the current stage to the same stage, so those were all zeros and have been grayed out in this picture.
The green outlined boxes represent progress to the next stage – for instance, moving from Stage 1 to Stage 2, or Stage 4 to Stage 5. In this example, it takes, on average, 42 days to get from Stage 2 to Stage 3. The red boxes represent an Opportunity moving into the prior stage. So, when an Opportunity moves from Stage 3 to Stage 2, backwards a step that took, on average, 22.4 days.
Using this data, we can get a simplified average total lifecycle for Opportunities. This gives you a lot more detail than the regular “Age” field on an Opportunity!
If, at this point, your brain has been scrambled by reading the words “history” and “tracking”, as well as the things that seem kind of the same but aren’t the same – here’s a handy infographic to explain what the difference is:
Summary
On a personal note, the Opportunity History report is one of my all-time favorite things to do in Salesforce. And when laid out in groups and columns for the stair effect, it’s a powerful visual for determining your sales lifecycle, where the slowdowns are, or where any problem areas might be.
I know this is a lot to absorb, especially if you’re new to reporting on historical data! These two report types can be confusing, but with a little practice, you can learn how to make the best use of the functionalities available in both report types, and impress your whole team with your historical Opportunity analysis.
Comments: