Microsoft Dynamics CRM gives me great capabilities out of the box such as the ability to manage my pipeline, create and track goals and targets. It also gives me great dashboards that provide insights into the deal process whilst giving me control of my pipeline.

A few months ago I wrote about Power View with CRM 2011 and CRM Online, but this time I wanted to do something a little bit different. I wanted to know:

  • How many days on average does it take to close a deal
  • How many appointments or interactions does it take to close a deal
  • How many days and appointment’s does it take at each stage of the deal so that I could identify efficiencies and bottlenecks.

I then wanted to slice and dice that data by:

  • Owner
  • Deal / Opportunity Stage
  • And any other CRM data

I wanted to do all this without writing a single line of code! I started to create some workflows but I quickly realised that it would be easier to use North52’s Formula manager. For those of you who have read this blog before you’ll know I use this extensively.

This is one the dashboards I was able to build:

OppAnalytics

With this I can now answer questions like:

  • How long is the sales cycle for each employee
  • At what stage do opportunities get stalled in your sales cycle
  • The length of their sales cycle in terms of deals won or lost
  • At what stage do opportunities typically fall off.
  • What percentage of first calls\meetings lead to second calls meetings
  • What’s the average sales cycle time per sales stage
  • Over time, what is the total value of the funnel at each stage
  • Number of times you contact each prospect before you close the sale

If  you want to do it yourself you can follow the steps below. John from North52 contacted me recently to tell me he has now turned this into an accelerator. You can download the accelerator here along with installation instructions. Alternatively here are the files:

Dashboard Design

I started with the outcome I wanted. I knew the insights I wanted gain and so I designed the dashboard(s) that would give me those insights. I came up with something very close to the above example. Resist the temptation to start building things, I know it is very easy, but the ability and ease that Dynamics CRM gives you to customise things can also be a projects greatest weakness.  Think, design and then do.

Having designed the dashboard I could see I needed to make a few modifications:

  1. A needed a new entity to store the number of days and the number of appointments at each stage of the opportunity. I called this entity Opportunity Stage. I added the following fields to the Opportuntiy Stage entity:
    • Start Date
    • End Date (date and time)
    • No of appointments (Whole number)
    • No of days (Decimal number)
    • Pipeline Phase (Single line of text)
    • OpportunityID (Lookup) – this is not a field but a relationship to the parent opportunity
  2. I then needed to add 2 fields to the Opportunity entity:
    1. Total no of appointments (Whole number)
    2. Total no of days (Decimal number)
  3. I then tidied a few things up by hiding the Opportunity Stage from the Opportunity form and I placed the relevant fields on the forms for testing purposes etc.

The next thing I needed to do was to create the calculations. To do this I needed:

  1. 1 Schedule:
    • The schedule would run nightly and would calculate the number of  days an appointment was open for each stage of an opportunity
  2. 6 formulas:
    1. Opportunity Stage – Update Opportunity – Total Appointments
    2. Opportunity Stage -Update Opportunities- Total Days
    3. Opportunity Stages – Save – To Current Record
    4. Appointment – Update Tot No of Appointments on Opportunity Stages
    5. Opportunity Stages – Save – To Current Record
    6. Opportunity Stages – Nightly Batch Microsoft

Schedule – 

sched1

sched2

1. Opportunity Stage – Update Opportunity – Total Appointments

Create the Formula below

formuma1

Formula
Sum(‘SumOpportunityStageNoAppointments.fin_noofappointments’)

Description
‘SumOpportunityStageNoAppointments’ is a formula detail record which contains a fetchxml query to retrieve the sum of all the appointments on the opportunity stages entity. Behind the scenes the system is filtering the fetchxml below by the current opportunity & using the SUM fetchxml aggregation operator.

<fetch version=”1.0″ output-format=”xml-platform” mapping=”logical” distinct=”false”>
<entity name=”fin_opportunitystages”
<attribute name=”fin_noofappointments” />
</entity>
</fetch>

Create the formula detail

formuma1b

2. Opportunity Stage -Update Opportunities- Total Days

Create the Fomula below

formuma2

Formula
Sum(‘SumOpportunityStageNoDays.fin_noofdays’)

Description
‘SumOpportunityStageNoDays’ is a formula detail record which contains a fetchxml query to retrieve the sum of all the numberof days on the opportunity stages entity. Behind the scenes the system is filtering the fetchxml below by the current opportunity & using the SUM fetchxml aggregation operator.

<fetch version=”1.0″ output-format=”xml-platform” mapping=”logical” distinct=”false”>
<entity name=”fin_opportunitystages”
<attribute name=”fin_noofdays” />
</entity>
</fetch>

Create the formula detail

formuma2b

3. Opportunity Stages – Save – To Current Record

formuma3

Formula
DateDiff([fin_opportunitystage.fin_startdate], [fin_opportunitystage.fin_enddate], ‘d’)

Description
This formula uses the datediff() function to calculate the number of days between 2 dates & store the value on the opportunity stages entity.

4. Appointment – Update Tot No of Appointments on Opportunity Stages

formuma4

Formula
if(ContainsData([appointment.actualend]) and ContainsData([appointment.regardingobjectid]), FirstRecord(‘GetCurrentAppointmentCount.fin_noofappointments’) + 1 , ‘NoOp’)

Description
This formula first checks to see if the actualend field is set on the appointment (i.e. it’s being marked as completed) & it checks to see if the regarding field has some data. If these conditions are true then the formula will execute the fetchxml query associated with’ GetCurrentAppointmentCount’.

When the formula has calculated the number of appointments its then executes the fetchxml query associated with ‘UpdateOpportunityStage’. This fetchxml query will return a single row which the formula will then set to the calculated number of appointments.

Create Formula detail – UpdateOpportunityStage

formuma4b

Create formula detail – GetCurrentAppointmentCount

formuma4a

Opportunity Stages – Save – To Current Record

formuma5

Formula
[Opportunity.modifiedon]

Description
Whenever the pipeline stage of an opportunity changes then the modifiedon date of the opportunity is saved to the current open opportunity stage record. This record is found by executing the fetchxml that is associated with the ‘SetEmptyOpportunityStageEndDate’ formula detail record.

Create formula detail – SetEmptyOpportunityStageEndDates

formuma5b


Opportunity Stages – Nightly Batch Microsoft

formula6

Formula
DateDiff([fin_opportunitystage.createdon], [fin_opportunitystage.modifiedon], ‘d’)

Description
When the nightly schedule executes it calls this formula which uses the DateDiff() function to calculate the number of days the opportunity has been opened at this stage & places the value in the No. of days field on the associated opportunity stage entity.

This posting is provided “AS IS” with no warranties, and confers no rights.

Next time on Mark Margolis’s BlogCRM Working Lunch – Financial Services