Sales Pipeline Trend Analysis

In today's volatile business environment, it's more important than ever that you understand what's happening in your sales pipeline.  Your sales pipeline affects everything from resource planning, and operations, to cash flow and headcount.  Using Microsoft SQL Server Reporting Services and Microsoft Dynamics CRM you can produce some valuable reports that will give you a great picture of how the pipeline looks today.  The problem is today's data doesn't tell you very much about the long term health of your business if you can't produce a trend report or a comparative analysis.  You need to put today's numbers into context so you can compare apples to apples.  For example, let's say you wanted to compare the estimated value of your pipeline in Q1 2008 to Q1 2007.  You not going to get there until you start capturing the pipeline data in a slightly different way.

 

Most sales pipelines are moving targets with estimated values and close dates that change daily.   Today's pipeline value can look very different from tomorrow's value.  So how do you analyze changes in the estimated value over time?  Is the value increasing or decreasing compared to last month or last quarter?  One way to get answers to those questions is to begin taking daily snapshots of your opportunities.  Keep in mind the longer you wait to begin collecting the snapshot data, the more time it will take to get to the analysis.  It will take some time to build that history in order to produce meaningful reports.  Companies just beginning their Microsoft Dynamics CRM journey are in a great position to begin capturing this data from day one.  The bottom line is the sooner you begin taking snapshots of your opportunity data, the sooner you'll be able to produce trend reports and perform a comparative analysis.

 

The way we address this issue at Custom Effective is with an ETL layer that extracts data from our CRM database on a daily basis and store it in a relational database with conformed dimensions (ROLAP) .  Once the data is in the ROLAP database we have a number of reporting options.  We can build reports directly from the ROLAP database or we can quickly process cubes in Microsoft SQL Server 2005 Analysis Services for consumption via Excel or SSRS.  Also, as we expand our own BI initiative we now have the flexibility of easily adding more facts and dimensions to the ROLAP in a controlled way.  Additionally, reporting against a separate ROLAP database (as opposed to keeping snapshot tables in the CRM database) reduces the load on the CRM solution which helps with performance.

 

Another area where the value of a daily pipeline snapshot can be demonstrated is in salesperson management.  Let's say you're a salesperson manager and you perform a quick check of the 6 month pipeline on your team's dashboard and find that….."SURPRISE!"…..the estimated value is 10% less than it was the last week.  But who changed what this week?  Assuming you don't have customized audit trail functionality, there's no way good way to go through all opportunities and identify which one(s) fell out this week or which estimated values were changed.  However, if your were able to go through the pipeline starting with the last day you checked it and proceed one day a time you would know exactly who changed what….making follow up with the opportunity's owner much easier.

 

Note:

As of this blog post, there's no other solution for a sales pipeline trend report or a period over period comparative analysis with an out of the box installation of Microsoft Dynamics CRM.  I am, however, anxiously awaiting the "R2" release of the Analytics CRM Accelerator for Microsoft Dynamics CRM 4.0 release which is promising to deliver trending for the sales pipeline.  As a part of that release, I do hope the authors will give some guidance as to how to apply the Analytics Accelerator to any CRM environment. 

Comments

[Jeff Paul Big League Players Club]

Hi, Great post! I felt great reading your blog post. I’m working with my friend in a small internet marketing business as a web developer. When I’m free I go around for some IT info