Report Durations of Key Events & Stages in Dynamics CRM

In this article I will demonstrate how workflows and calculated fields can effectively be used to track the duration of events and stages in CRM, allowing you to report on how much business time is taken at each stage. One of the Key Reasons to Implement a CRM system is for organisations looking to continuously improve processes and reduce bottlenecks in the customer journey. This is particularly useful when you want to track how long it takes to process an opportunity or customer on boarding etc. Reporting on Business Processes is vital for any organisations looking to reduce waste and become more efficient in delivering value & customer experience.

The Audit Feature in CRM is great for Compliance and tracking user level changes, it’s not so simple to Report on in CRM Online as they are stored in Separate Logs that would need to be exported to a Staging database to make it reportable for MI.

However I will show you how you can use Dynamics CRM Workflows and Calculated fields in Dynamics crm to allow you to easily track your historical stages and durations for key events, allowing you to identify bottlenecks in your pipeline so you can take action on improving your processes and reducing Time and Cost and most importantly customer experience.

 1)    First of all Create a new Entity for ‘Stage History’: This will be used to log historical Actions such as the Stage Names, Process Stage, Status Reason etc and corresponding Durations for Stages or Key Event, Your Primary entity such as Opportunity will have a one to many Relationship with the Stage History Entity, and this entity will log the Stage or Key Event and the Duration for it. This will be used to Report on.

1. Create the following Fields in the new entity to be used by our workflow to calculate the Duration of each stage;

·        A ‘Parent Stage’ lookup field to link the current stage History record to the previous stage, allowing us to create a link between the 2 stages. This will allow us to set the Parent Stages ‘CompletedDate’ with the new stage history records ‘createdon’ date to calculate the duration between stages.

·        Lookup to the Parent Entity, Since Workflows can only use N:1 Lookups to update records this will allow is to update the Parent Primary Pipeline Record from the Child Stage History record.

·        DateStageCompleted – a workflow we create later will set  to the Createdon date from the new stage history record when it is created, allowing us to calculate the duration between the two dates

·       Create the following Calculated Fields ‘Diff’ function to Calculate Durations for the metrics you require, Diff Function available are ;

a.      DiffinMinutes(createdon, new_datestagecompleted)

b.      DiffInHours(createdon, new_datestagecompleted)

c.      DiffInDays(createdon, new_datestagecompleted)

2. Create a Lookup on the Primary Entity to the Stage History Entity called ‘Last Stage History’. This will allow you to store the Last Stage History Record before it Changes to the Next, and will serve as reference whenever a New Stage Is created so that we can use it to calculate the Durations from the Last Stage and Current Stage. We will create a Workflow step to update this lookup, allowing us to reference the last stage to perform our calculations.

Once all the Attributes are created the following 2 Workflows will be required to Create the Stage History Records and set the Values for Corresponding Stages, Date Completed and Last History Stage Lookups so that the Duration for each Stage history can be calculated.

Workflow A: Create a Workflow on the Main Parent Entity to Create a Stage History Record Every time a Stage Change is triggered, The Stage Change can be change manually or you can have another series of workflows that updates the stage based on conditions. On change of the Stage your Workflow will perform Create Record Action  for  a Stage History, Setting the Values for the following Stage History Lookup from the corresponding values on the on the Primary ‘Pipeline’ Entity using the Workflow Create Record Action and will allow us to update the Stage history with the second workflow;

·        Primary Entity lookup – updated from the Primary Record – This lookup will allow us to retrieve values from the Parent record via the Workflow editor

·        Stage  ( to log the Stage Value on the Primary Record for the Stage History)

·        Parent Stage (Updated with the ‘Last Stage History’ Lookup on the Parent Record)

Now Workflow A that will create a New Stage History Record every time your pass a milestone in your pipeline, as mentioned before you can use the a Stage Attribute or Business process Flow Stage, and add Field Change triggers on the Workflow for the Attributes that store your Key Events. However we need to prevent the Stage Value from being updated if it is already the same value, as this may cause duplicate Stage History records to be created. So you will need to add check conditions in the beginning of the workflow to prevent this from occurring.

Workflow B: Now Create a Second Workflow that will fire on Create of a Stage History entity, this will update the remaining fields required for calculating the duration from when the last stage was created and the new stage was created.

1)      Update Primary Entitys  ‘Last Stage History’ lookup with which will serve as a reference for the new Stage History record being created

2)      Update Stage History Entity’s ‘Parent Stage’ Lookup with the Value Set by the ‘Last Stage History’ in the last step.

3)      Update Primary Entity / Last Stage Lookup: Set DateCompleted on the ‘Last  Stage’ Lookup with the Createdon date for new Stage History Record, which will update the Duration calculated fields

4)       Set the Primary Entity /Last Stage History’ lookup with the new Stage Record

The Above method works perfectly for creating history logs and calculating actual Durations for each pipeline event, but not for calculating the actual working days so my next blog will include a step to calculate the duration in business days also. Happy CRMing J