Friday, September 25, 2015

Salesforce: Opportunity Stage Aging

Background: sales operation would like to analyze how long a stage stay in an Opportunity.

Solution: use Opportunity History report.

Read this blog to refresh what is Opportunity History report. Let us take a sample of Opportunity History, although this is not a great sample because Stage has change to close, back to open again, and finally close again.

Above screenshot taken from Opportunity related list Stage History, let us analyze changes happened in that Opportunity:
  1. Opportunity created with stage = Prospecting 
  2. Amount change to $600.00
  3. Stage change to Closed Won and Amount to $610.00
  4. Amount change to $48,000.00
  5. Stage change from Closed Won to Qualification
  6. Stage change from Qualification to Needs Analysis
  7. Close Date changed
  8. Stage change to Close Won and Close Date also changed
  9. Close Date changed again
Now, how it looks in the Opportunity History report:

It show exact the same value from related list to report, except there are more information in the report:
  1. From Stage
  2. To Stage
  3. Stage Change
  4. Stage Duration

We can use those fields as filter as required. If we look back to initial requirements, we can use Opportunity History with filters below:

This report will only show row with stage change and not upon opportunity creation. Using Stage Duration, it will auto calculate how many days that opportunity stay at that Stage.