In our earlier post on Sales Manager dashboards, I brought up the topic of trend reporting for the sales pipeline.  For many sales managers they feel a strong need to have visibility into whether the pipeline is growing or shrinking over time, and to get a more granular view on how that trend is playing out for each sales rep and/or product.

Dynamics CRM out of the box only maintains a current copy of the data so trending is not possible without some additional configuration, or through the use of core SQL Server tools like SQL Integration Services.  Let's take a look at the alternatives -

 1.  CRM Workflow - There are a range of options available here.  With Dynamics 4.0 workflow can be used to trap key events and write out the changes that occurred.  These events could then be reported on with standard Dynamics CRM rpeorting tools.  I believe this option is likely the easiest to setup, but it can be quite limiting in terms of trapping all of the relevant data attributes, and likely will become unwieldy as the system grows and changes.

 2.  CRM Development - The CRM application itself can be enhanced by developing code that will write out to an audit table or audit entity on all key event changes to an opportunity record.  This approach is the most all encompasing because it will allow for users to have visibility into the audit history, but it is also likely the most expensive and time consuming to implement.

3.  Integration Services Trending - A simple solution that can allow sales managers visiblity into pipeline trends is to create an Integration Services package that will extract all key data from the CRM opportunity table in an automated fashion at a pre-defined interval.  This will allow for sales managers to look at the progression in the pipeline over time, and to pivot or drill down to view these changes by product, rep, territory, or any other key attribute.  Setting up this type of trending requires answering the following key questions about the business -

• How frequently should the trending extract run?  Many organizations assume that the more frequently this is run the better.  That may be the case for a subset of organizations, but I've found that most can work very effectively with weekly snapshots of the pipeline.  The benefit of a weekly snapshot is that data volumes remain reasonable and managers can still look at trending from a quarterly, monthly, weekly perspective without getting consumed by the potential noise captured in day to day changes. 
• What data attributes do you care about trending on?  Perhaps the most important question to answer is which columns of data related to the opportunity should you be storing in the trending table.  A personal opinion is that organizations should think about this question broadly and pull in all attributes that have relevance today as well as those that may be relevant tomorrow.  Some key fields include - sales stage, estimated amount, deal owner, territory, country, state, city, postal code, and currency.
• How granularly should the data be stored? I believe it is absolutely critical to store the data at the transactional level so that the trending table has one row of data for each opportunity extracted on a given date.  This will allow the end users to track directly back to the record involved if there are ever questions about the validity of the data and it allows for easier pivoting and slicing of the data as users drill into reports.

If you're interested in starter code that creates a trending table, and populates that table with CRM data, feel free to email jefff@madronasg.com