How to Accurately Track the Last Stage Change Date in Salesforce

We all love Salesforce, it’s an amazing platform that makes it easy to capture important data and manage the sales cycle. There are, however, some limitations with its base functionality. That’s when Salesforce growth experts like ourselves step in and provide guidance on how to overcome these challenges. Let’s look at how to accurately track the last stage change date in Salesforce.

Problem

One limitation encountered with out-of-the-box Salesforce reporting is that there is no easy way to report on how much time an opportunity stays at each stage. Perhaps it would be useful to track the last stage change date in Salesforce by the average number of days for the following reasons:

Compare the behavior of won opportunities against lost opportunities;
Flag open opportunities that have been spending too much time in a given stage;
Create automation (using process builders, workflow rules, flows, etc.) if opportunities spend too much time in specific stages;
Etc.

There are two solutions we can use, a simpler, more limited option, and another more complicated, but more efficient, The simple solution involves of creating date fields on the opportunity object. Alternatively, the more complex option involves flagging the opportunity changes in a custom object attached to the opportunity. We’ll present each option below.

For reference, we will be using the default values in Salesforce opportunity stages:

Solution 1: Date fields on the opportunity object

Let’s outline the first way to accurately track Salesforce stage changes. The idea behind this option is to provide a set of fields on the opportunity for each stage:

A start date field – Date field
A duration field – Formula field

And a process to update the start date fields.

Follow these steps:

1) CREATE DATE FIELDS FOR ALL THE OPEN STAGES, AND ONE FOR CLOSED STAGES COMBINED (WON AND LOST – NO NEED TO HAVE A FIELD FOR EACH):

Tracking Stage Changes in Salesforce: Picklist Values

Note that field “Closed Date” might be too similar in name to the standard field “Close Date”, feel free to rename it to something else

2) CREATE FORMULA FIELDS TO CALCULATE THE DURATION AN OPPORTUNITY HAS SPENT AT EACH STAGE (ONLY FOR THE ACTIVE STAGES, WE ARE NOT INTERESTED IN THE NUMBER OF DAYS AN OPPORTUNITY HAS BEEN SET TO CLOSED-WON OR CLOSED LOST):

Here’s an example of one of those formula fields, specifically “Prospecting – Duration”. I’m using colors here to split the formula into steps to better explain the logic:

IF( ISBLANK( Prospecting_Start_Date__c ), NULL,
IF( NOT(ISBLANK( Qualification_Start_Date__c)) , Qualification_Start_Date__c – Prospecting_Start_Date__c,
IF( NOT(ISBLANK( Needs_Analysis_Duration__c )) , Needs_Analysis_Duration__c – Prospecting_Start_Date__c,
IF( NOT(ISBLANK( Value_Proposition_Start_Date__c )) , Value_Proposition_Start_Date__c – Prospecting_Start_Date__c,
IF( NOT(ISBLANK( Id_Decision_Makers_Start_Date__c )) , Id_Decision_Makers_Start_Date__c – Prospecting_Start_Date__c,
IF( NOT(ISBLANK( Perception_Analysis_Start_Date__c )) , Perception_Analysis_Start_Date__c – Prospecting_Start_Date__c,
IF( NOT(ISBLANK( Proposal_Price_Quote_Start_Date__c )) , Proposal_Price_Quote_Start_Date__c – Prospecting_Start_Date__c,
IF( NOT(ISBLANK( Negotiation_Review_Start_Date__c )) , Negotiation_Review_Start_Date__c – Prospecting_Start_Date__c,
IF( IsClosed, Closed_Date__c – Prospecting_Start_Date__c,
Today() – Prospecting_Start_Date__c )))))))))

To explain, we look at the following conditions, in this order:

If field “Prospecting Start Date” is blank, then field is null – This makes sense if an opportunity has not been at that stage yet
If not, then if field “Qualification Start Date” is not blank, then we count the number of days between Qualification Start Date and Prospecting Start Date (Prospecting being the next stage in order) – This would apply to an opportunity that has moved on to the next stage
If not, then we successively look at each subsequent stages using the same logic, to account for opportunities skipping stages
If not, then if the opportunity is closed (won or lost), we compare the number of days between Closed Date and Prospecting Start Date – In case an opportunity skips straight to closed won or lost
If not, then we compare today’s date – That would imply this is the current stage. This also means that in this situation, the value will increase by one each day that it is the current stage, making it a dynamic value that will tend to increase over time.

Note also that this will give you the value in days, so a value of “14” would mean 14 days.

You can put these fields, along with the Start Date fields, on the page layout, but it’s recommended that the Start Date fields be read-only.

3) CREATE A PROCESS BUILDER TO UPDATE THE START DATE FIELDS:

We decided to use only one node, where all the field updates are contained in this example. This is to prevent the process builder from being too big, and make it leaner. You could also make one node per stage.

Note, also, that we want to make sure this process runs when field stage is changed, but also on record creation.

As for the actions, see example below with stage Prospecting:

As you can see, we have two filters to verify that our opportunity is at the right stage, and that the start date field for that stage is null. If so, we update the start date field with today’s date.

For field Closed Date, the filters are slightly different:

Limitations:

This process is simple and easy to implement, but comes with a few limitations:

1) Duration fields cannot be used to trigger processes because they are formula fields;

2) Adding/removing stages means reworking several parts of the process – creating new fields, editing duration formula fields, modifying the process builder;

3) Opportunities going back and forth in stages are not well represented. With the current setup, we only put the first date an opportunity goes to a specific stage. We could also make it so it always updates the date field when an opportunity goes back to a stage, but we would not have the overall number of days an opportunity spent in any given stage. This is the major limitation that we will address in the second, more complicated solution.

Solution 2: custom object to track opportunity stage changes

This second strategy to more accurately track the last stage change date in Salesforce will involve a bit more work, but will let us address some of the limitations mentioned above.

1) FIRST, WE NEED TO CREATE A CUSTOM OBJECT, LET’S CALL IT “OPPORTUNITY STAGE CHANGE” WITH A FEW FIELDS:

Note that the record Name field has been changed to Auto Number for convenience.

Here’s the “Duration” formula field content:

IF( ISBLANK( End_Date__c ), Today() – Start_Date__c, End_Date__c – Start_Date__c )

Similarly to the duration fields in solution #1, we compare the number of days between the start and end date, or, if there is no end date, between today and the start date.

2) A FEW CUSTOM FIELDS ON THE OPPORTUNITY OBJECT:

a) Prospecting – Duration: Number field
b) Qualification – Duration: Number field
c) Needs Analysis – Duration: Number field
d) Value Proposition – Duration: Number field
e) Id. Decision Makers – Duration: Number field
f) Perception Analysis – Duration: Number field
g) Proposal/Price Quote – Duration : Number field
h) Negotiation/Review – Duration: Number field
i) Current Opportunity Stage Change: Lookup field to object Opportunity Stage change

The number fields have the same name as the formula fields in the first solution, but the type of field is different. Remember, mark these fields as being read-only if these fields are to be shown on page layouts.

The lookup field is used in a process to update the end date on the current stage (see step #4).

3) CREATE A PROCESS THAT CREATES/UPDATES OPPORTUNITY STAGE CHANGE RECORDS WHEN AN OPPORTUNITY IS CREATED/STAGE IS CHANGED. THIS PROCESS WORKS IN TWO STEPS:

a) CONDITION: When an opportunity’s stage field is changed, and it currently has a value on the Current Opportunity Stage Change field:

ACTION: Update the end date on that Opportunity Stage change record:

b) CONDITION`: When the following happens:

1) An opportunity is created OR the stage is changed

AND

2) The opportunity is closed OR it was closed or reopened

ACTION: Create a new Opportunity stage change record:

c) Create a process builder that assigns a newly created Opportunity stage change record with no end date to the opportunity:

CONDITION: An opportunity stage change record is created with no end date:

ACTION: Update field Current opportunity name change with that record:

RESULT

The automation is ready now, but we still need to roll up those numbers to the opportunity. We can’t use Salesforce’s roll-up summary fields since we want to roll up the duration fields, which are formulas. Besides, the limit on roll-up summary fields is often an issue with opportunities, as we often come across orgs where plenty of roll-up summaries are present.

We will instead rely on a free app called “Declarative Lookup Rollup Summaries” that will enable us to do that. Follow the instructions to install and set up.

Once installed and ready, create the roll-up summaries. Here’s an example for the first stage:

Note that, if it is your first rollup, you cannot check the “Active” box before you save the rollup and go through the Manage Child Trigger process.

Once active, the rollups calculate and update the duration fields on the opportunity any time an Opportunity Stage Change record is created or edited. However, we also want to take into account that overnight, the current stage duration goes up by one, so we will need to run a daily calculation. Click on “Schedule Calculate”.

It is recommended at this point that, for this calculate job, we limit the scope only to the open opportunities, so you can schedule the roll-up as below:

Make sure you create rollup calculations for all the duration fields you have!

This solution is more complicated than the first one, but, as you can see, adding or removing stages is much easier, as we do not need to revise the whole process. Simply add/remove duration fields and roll-up summaries, the rest will work as intended.

LIMITATIONS:

This process will, over time, create a lot of records for the Opportunity Stage Change object, which depending on the size of your database, might become an issue. It’s important to keep an eye on the data storage every once in a while to make sure we are not hitting storage limits.

Final Thoughts:

We have now outlined two ways you can more accurately track the last stage change date in Salesforce and how to put each into action. Depending on your situation and how comfortable you are with the process, one of these may be more appropriate than another. If you have any questions or would like to discuss this further feel free to email me and I’d be happy to connect.

If you’d like to improve your own Salesforce skillset, our founder David Carnes, one of the most knowledgeable instructors in the industry, is training around Salesforce administration and configuration. It’s a great way to improve the way you use Salesforce and reinforce best practices.

We all love Salesforce, it’s an amazing platform that makes it easy to capture important data and manage the sales cycle. There are, however, some limitations with its base functionality. That’s when Salesforce growth experts like ourselves step in and provide guidance on how to overcome these challenges. Let’s look at how to accurately track the last stage change date in Salesforce.