Report on Activities by Historic Opportunity Stage

Report on Activities by Opportunity Stage

One regularly asked question that I've seen on the Salesforce Success Community and other Salesforce forums is: 
"How do I report on activities made at different stages of the Sales process?"
You would be forgiven for thinking that there was a simple out of the box report that would show this. However, the standard Activity reports in Salesforce all have a look up to the Object they are reporting on. That means that as the Opportunity object progresses through the sales stages, so does the reference on the Activity.

For example - 

  1. on the 1st April I made a call whilst Opportunity was in "Discovery"
  2. on the 2nd April I made another call whilst the Opportunity was "Closed Won"
A standard Opportunity with Activity report in Salesforce will show you 2 Activities related to an Opportunity with "Closed Won" as the status for BOTH activities.

So, how do you solve this?



Taking a Snapshot

One way of approaching this problem is to take a copy or snapshot of the Opportunity Stage at the time the activity was recorded and then report on that. 

Then, rather than referencing the current Opportunity Stage on your report you would have all required information on the Task Object.

Text field added to Activity Custom Fields
To capture the Opportunity Stage at the point an activity is logged you will need a new field in Activity Custom Fields. I called my field "Opportunity Stage" which resulted in an API name of "Opportunity_Stage__c" (it's very important to get the API field name correct when you are creating Triggers).

Once I had created my custom field I added it to the related list layout on the Opportunity Page Layout.

Storing the Opportunity Stage at the time of the Activity
There is a new tool from Salesforce called Process Builder which would give you another way to achieve this copying of the current status without a custom Apex Trigger. However, in this instance I went with the Apex code below.

I would be interested to hear from anyone who decides to set this up in Process Builder. Please let me know in the comments if you found any advantages/disadvantages in doing it that way.

The Results

Before I show you the code for this I just wanted to share the output. 

Instead of all activities referencing the current Opportunity Stage, you can now report on Activity at each Sales stage.

The Apex Code itself is at the bottom of the page.

Grouped graph by Activity Stage
Activities broken down by Stage for an Opportunity that is now Closed/Won

The Trigger

    
trigger OpportunityStage on Task (after insert) {

/*
* For each Task added - identify whether related to Opportunity
* If it is - store current Opportunity Stage on Task - for reporting purposes
*/    
    
    Map<id string=""> oppStage = new Map<id string="">();
    Set<id> opportunityIds = new Set<id>();    
    Set<id> taskIds = new Set<id>();
    
    
    for(Task myTask : trigger.new) {
        
        System.debug('ID is ' + myTask.Id + 'WhoID is ' + myTask.WhoId + ' WhatID is ' + myTask.WhatId);
        
        String whatID = myTask.WhatId; 
        
        if (WhatID != Null &amp;&amp; whatID.startsWith('006')) {
            System.debug('Task is related to Opportunity - add ID to set');
            taskIds.add(myTask.Id);
            opportunityIds.add(whatID);
        }
        
    }
    
    System.debug('SELECT ID, StageName FROM Opportunity WHERE id = :' + opportunityIds);
    
    List<opportunity> myOpportunities = [SELECT ID, StageName FROM Opportunity WHERE id = :opportunityIds];   
    for(Opportunity myOpp : myOpportunities) {
        //Build the map
        oppStage.put(myOpp.id, myOpp.StageName);
    }
    
    System.debug('oppStage map is ' + oppStage.size());
    System.debug('myOpportunities size is ' + myOpportunities.size());
    
    System.debug('SELECT ID, Opportunity_Stage__c FROM Task WHERE id = :' + taskIds);
    
    List<task> tasksToUpdate = [SELECT ID, WhatID, Opportunity_Stage__c FROM Task WHERE id = :taskIds];   
    for(Task a : tasksToUpdate) {
        a.Opportunity_Stage__c = oppStage.get(a.WhatId);
        System.debug('new values ' + oppStage.get(a.id));
    }
    System.debug('tasksToUpdate size is ' + tasksToUpdate.size());
    
    update tasksToUpdate;
}



Thanks for reading everyone.

Icon from Vecteezy at Iconfinder

Comments