Salesforce: Another Use for ISCHANGED

In an earlier post,we used ISCHANGED to track when Next Step was changed.  This formula can be used in many situations: logging when a Lead Status was changed, when a Close Date is changed, or when a Sales Stage is changed.  The last scenario is useful if you need to show it on a page.  For reporting purposes, the standard "Age Duration" is available on standard Opportunity report types.

But what if we need to measure how long each stage is taking?  This will help the management see where the sales teams take long, and maybe provide help to move them along faster.  It can also help rationalize if the sales process is accurate.

First, the requirements: We need to log when an opportunity reaches a certain stage.  We also need to clear out any time stamps for subsequent stages (in case the Stage went backwards).  And then we need to measure the duration between stages.

In this scenario, let's assume we only have one Sales Process, that is, one set of Sales Stages.  First, let's create a time stamp field for each stage. These can be pure Date or Date/Time fields, depending on how granular you want to measure your sales cycle.  They should all be read only to all profiles, as they will only be updated via workflow email updates.

Now we need the workflow and field update.  The first time stamp is a little different than the others. We want to capture the time stamp of the first stage upon creation.   ISCHANGED isn't actually True when a record is new.  If all the workflows were just based on ISCHANGED, the time stamp of the first sales stage upon creation will not be captured.

Assuming that we allow the sales team to create opportunities above the first stage, we must create one workflow for each stage that we want to capture, that fires only when the record is created, and has a single criteria that evaluates the Sales Stage.  For example, to capture the time stamp if it was created with "Prospecting" as the stage, we will need this workflow:

 Note the Evaluation Criteria and the lone Rule Criteria.

We will need one of this for each stage in your sales process.  And this takes care of capturing the time stamp when it is created.

The field update is relatively simple.  We will use NOW() if it is a Date/Time field, or TODAY() if it is just a date, and set the Stage 1 Timestamp or Prospecting Timestamp field.

Now, to capture the same time stamp when the stage is changed,  we will use the following formula:

AND ( 
  ISCHANGED ( StageName ),
  ISPICKVAL ( StageName, 'Prospecting')
)

We will use the same field update that we used in the earlier workflow to  capture NOW() or TODAY().  And again, we will create one workflow for each stage, adjusting the value in ISPICKVAL as appropriate, and changing the Field Update.

We also need additional field updates.  Say for example, our organization uses the standard Salesforce stages, and a sales person moves his opportunity back from Id. Decision Makers to Value Proposition.  The time stamp for Id. Decision Makers is now invalid.  We will need to clear this out, and all the timestamps for the subsequent stages.  So for each of these workflows, there will be more than one field update.  One to set the time stamp of the current stage, and one or more to clear the time stamp of subsequent stages.

The above steps take care of setting the time stamp for each stage when that stage is reached, either when created new or when the opportunity stage is updated.

The next step is to create formulas to get the duration.  We need a formula field, Stage 1 Duration for example, that evaluates to a number, and the formula will be something like this:

IF ( ISPICKVAL( StageName , 'Prospecting'),
  NOW() -   Stage_1_Timestamp__c  ,
  Stage_1_Timestamp__c - Stage_2_Timestamp__c
)
    
If it is currently Prospecting (and Prospecting is the first stage), then the duration for Prospecting is the current date and time minus the time stamp when it was set to Stage 1.  If it is not, then it is the difference between Stage 1 and Stage 2.

Note that the above formula will cause issues if the opportunity jumps from Stage 1 to Stage 3.  The formula can be modified to check if Stage 2 Timestamp is not null before performing the subtraction, and making the result -1 to signify that it is unavailable.

The alternative is to modify the workflows from the previous step to set any previous stage time stamp to TODAY or NOW, the same as the current stage, effectively making the duration zero.  An administrator can discover the pros and cons of each and reach a decision on the best way forward for his own organization.

Once the workflows have been added, and the field updates are done, it is possible to populate the new fields with data from the Opportunity Field History table.  This requires some Excel work and the use of Data Loader.  Afterwards, reports can be created to show average duration of each stage.

To summarize, we have created fields, workflows, and field updates that will allow us to measure Stage Duration for each Stage and gain useful insight about the current Sales Process.