Wednesday, February 28, 2018

Lead perfomance and tracking of Status, Owners and Activities

Lead Status and Owner Tracking

Quite often requires tracking changes of Lead Status and Owners and Lead Activities.
To store Lead changes, time between changes, and Activities we used Lead Tracking custom object:

When Lead Status or Owner changed, will be inserted new Lead Tracking record. In related list show "New Status", "Previous Status"(if End Status is empty - current Status), "Start" Date, "End" Date and "Elapsed Time". If Lead Status again to change, record with empty "New Status" field will be updated: will write down a new Lead Status and date of the change.
If Lead Owner changed, Lead Tracking inserts similar like when changed Status, but "Field" will be "Onwer", "Previous" and "New" a new Owner and Previous Owner. Trigger handler:

   public Map getNewOwnerName(Set ownIds){
        Map ownerMap = new Map();  
        List groups =  [SELECT Id, Name FROM Group WHERE Id IN :ownIds];
        List users = [SELECT Id, Name FROM User WHERE Id IN :ownIds];
        for(Group g : groups){
            ownerMap.put(g.Id, g.Name);
        }
        for(User u: users){
            ownerMap.put(u.Id, u.Name);
        }
        return ownerMap;
    }
    public void afterInsert( List newleads){
        List toInsert = new List();       
        Set ownIds = new Set ();
        for(Lead l : newleads){
            ownIds.add(l.OwnerId);
        }
        Map ownerMap = getNewOwnerName(ownIds);   
        for(Lead l : newleads){
            Lead_Tracking__c tr = new Lead_Tracking__c();
            tr.Start__c = l.CreatedDate;
            tr.Start_Value__c = l.Status;
            tr.Lead__c = l.Id; 
            tr.Changed_by__c = UserInfo.getUserId();
            tr.Field__c = 'Status';           
            toInsert.Add(tr);
            Lead_Tracking__c tr1 = new Lead_Tracking__c();
            tr1.Start__c = l.CreatedDate;
            tr1.Start_Value__c = ownerMap.get(l.OwnerId);
            tr1.Lead__c = l.Id; 
            tr1.Changed_by__c = UserInfo.getUserId();
            tr1.Field__c = 'Owner';
            tr1.Owner_Type__c = (String.valueOf(l.OwnerId).StartsWith('005')? 'User' : 'Queue');

            toInsert.Add(tr1);    
        }
        insert toInsert;

    }
    public void afterUpdate(List newLeads, map oldMap){
        Map newValMap = new map();
        Set ownerIds = new Set();
        for(Lead l: newLeads){
            if(l.Status != oldMap.get(l.Id).Status || l.OwnerID != oldMap.get(l.Id).OwnerID){
                newValMap.put(l.Id, l);
                ownerIds.add(l.OwnerId);
            }           
        }
        Map  newOwnerMap = getNewOwnerName(ownerIds);
        List toUpdList = new List();
        toUpdList = [SELECT Id, End__c, End_Value__c, Lead__c,  Start__c, Start_Value__c, Changed_by__c, Lead__r.OwnerId, Field__c 
                      FROM Lead_Tracking__c
                       WHERE Lead__c IN: newValMap.keySet() AND End__c = null AND End_Value__c =null ];
        ListtoInsert = new List();
        set existLeads = new set();
        for(Lead_Tracking__c tr: toUpdList){
            existLeads.add(tr.Lead__c);
            if(tr.Field__c == 'Status' && tr.Start_Value__c == oldMap.get(tr.Lead__c).Status &&  oldMap.get(tr.Lead__c).Status != newValMap.get(tr.Lead__c).Status ){
                tr.End_Value__c = newValMap.get(tr.Lead__c).Status;
                tr.End__c = newValMap.get(tr.Lead__c).LastModifiedDate;
                tr.Changed_by__c = UserInfo.getUserId(); 
                Lead_Tracking__c new_tr = new Lead_Tracking__c();
                new_tr.Lead__c = tr.Lead__c;
                new_tr.Start__c = newValMap.get(tr.Lead__c).LastModifiedDate;
                new_tr.Start_Value__c = newValMap.get(tr.Lead__c).Status;
                new_tr.Changed_by__c = UserInfo.getUserId(); 
                new_tr.Field__c = 'Status';
                toInsert.add(new_tr);
            }
        
            if(tr.Field__c == 'Owner' && oldMap.get(tr.Lead__c).OwnerId != newValMap.get(tr.Lead__c).OwnerId ){
                tr.End_Value__c = newOwnerMap.get(tr.Lead__r.OwnerId);
                tr.End__c = newValMap.get(tr.Lead__c).LastModifiedDate;
                tr.Changed_by__c = UserInfo.getUserId();
                Lead_Tracking__c new_tr = new Lead_Tracking__c();
                new_tr.Lead__c = tr.Lead__c;
                new_tr.Start__c = newValMap.get(tr.Lead__c).LastModifiedDate;
                new_tr.Start_Value__c = newOwnerMap.get(tr.Lead__r.OwnerId);
                new_tr.Changed_by__c = UserInfo.getUserId();
                new_tr.Field__c = 'Owner';
                new_tr.Owner_Type__c = (String.valueOf(tr.Lead__r.OwnerId).StartsWith('005')? 'User' : 'Queue');
                toInsert.add(new_tr);
            }
        } 
        for(Lead l : newLeads){
            if(!existLeads.contains(l.id) && (l.Status != oldMap.get(l.Id).Status || l.OwnerID != oldMap.get(l.Id).OwnerID)){
                Lead_Tracking__c tr = new Lead_Tracking__c();
                tr.Start__c = system.now();
                tr.Start_Value__c = l.Status;
                tr.Lead__c = l.Id; 
                tr.Changed_by__c = UserInfo.getUserId();
                tr.Field__c = 'Status';           
                toInsert.Add(tr);
                Lead_Tracking__c tr1 = new Lead_Tracking__c();
                tr1.Start__c = system.now();
                tr1.Start_Value__c = newOwnerMap.get(l.OwnerId);
                tr1.Lead__c = l.Id; 
                tr1.Changed_by__c = UserInfo.getUserId();
                tr1.Field__c = 'Owner';
                tr1.Owner_Type__c = (String.valueOf(l.OwnerId).StartsWith('005')? 'User' : 'Queue');
    
                toInsert.Add(tr1);
            }    
        }
        insert toInsert;
        update toUpdList;
    }
    public void beforeDelete(map oldMap){
        List  toDelete = [SELECT Id FROM Lead_Tracking__c WHERE Lead__c IN: oldMap.keySet()];       
        delete toDelete;
    }

Elapsed time before first Activity

When the first Activity of certain Lead Owner created, the trigger on Activity writes downtime between lead assignment and creation of activity.

 public void afterInsert(List callList){
        Set leadIds = new Set();
        Map trackMapUpd = new Map();
        List toUpd = new List();
        for(Task t : callList){
            System.debug(t);
            if(t.Subject.contains('Call')){
                leadIds.add(t.WhoId);
            }
        }

        for(Lead_Tracking__c tr : [SELECT Id, Lead__c,  Elapsed_time_before_first_activity__c, Start__c, CreatedDate, End_Value__c FROM Lead_Tracking__c WHERE Lead__c IN: leadIds AND Field__c = 'Owner' AND End_Value__c = null  ORDER BY Start__c ASC]){
            if(tr.Elapsed_time_before_first_activity__c == null){
                trackMapUpd.put(tr.Lead__c, tr);
            }
        }

        if(trackMapUpd.size()>0){
            for(Task t : callList){           
               trackMapUpd.get(t.WhoId).Elapsed_time_before_first_activity__c = GetElapsedTime(trackMapUpd.get(t.WhoId).CreatedDate, System.now());
               toUpd.add(trackMapUpd.get(t.WhoId));            
            }
            update toUpd;
        }
    }

    public static Decimal GetElapsedTime(DateTime startTime, DateTime endTime){
        if(startTime == null || endTime == null){
            return null;
        }
        return ((endTime.getTime())/1000/60) - ((startTime.getTime())/1000/60);
    }
Lead Tracking: