It’s 3:00 AM. Do You Know What Your Sales Pipeline Velocity Is?

Of all the things sales managers lose sleep over, pipeline velocity may be one of the least well defined.

Out of the box, most CRM applications are pretty good at pipeline snapshots: what’s expected to close this month, next month, and so forth. But they usually aren’t so good at telling you how fast deals move through the sales pipeline. And if you don’t know how quickly your deals close (velocity), you’ll never know if your pipeline velocity is increasing or decreasing (acceleration).

Fortunately, if you’re using Microsoft Dynamics CRM, you’re only a few steps away from serviceable measures of pipeline velocity and acceleration. The good news is, the data may already be there, just waiting for you to discover what it has to tell you. And one of the things it may tell you, as the case study I’m about to relate makes clear, is that higher velocity isn’t necessarily a good thing.

Measuring Pipeline Velocity in Microsoft Dynamics CRM

If you use Microsoft Dynamics CRM for sales management, chances are good you use the Opportunity record type to represent potential sales. Opportunities are created, get pushed through the stages of a sales process, and eventually get closed out. With just the right combination of sales voodoo (pricing, value proposition, persistence, selling skills, luck…) they get closed as Won. Sadly, inevitably, a few get closed as Lost. But either way, certain immutable Microsoft Dynamics CRM laws apply:

• Every opportunity record has a Created On date field.
• Every closed opportunity record has an Actual Close Date field.
• Every opportunity closed as Won has an Actual Revenue field, filled by default with the value of the Est. Revenue field.

Together, these mean that you can easily calculate your sales pipeline velocity, and measure how it’s changing over time. For example, consider the following figure, which shows an Excel table based on an exported view of Microsoft Dynamics CRM opportunity records, exported from the default Won Opportunities view:

Columns D, F and G are straight from Microsoft Dynamics CRM. These are the three fields I mentioned above, and the Actual Revenue field will contain whatever values the won opportunities were closed with.

Column H – what I refer to as Sales Days --  represents how long each deal took to close. Format the column as a number and subtract column F from G: =[@[Actual Close Date]]-[@[Created On]]. The higher the average sales days, the slower your pipeline velocity.

Column I – Revenue per Sales Day – is simply actual revenue divided by Sales Days: =[@[Actual Revenue]]/[@[Sales Days]]. So for each opportunity record, this gives us the realized revenue for each day it took to sell the deal. I think of this as representing the benefit (in terms of revenue) per unit of cost (in terms of sales days)

Most people of course could eyeball data like those and intuitively calculate velocity and acceleration, but for me this is a job for pivot tables. For example, on the data set partially shown above, you can create a pivot table like this one:

This shows the average, by month and year, of the Sales Days column.  It’s analogous to the standard “days outstanding” measure for accounts receivable, and you can think of it as the inverse of “pipeline velocity”: the more days the average deal takes to close, the slower your pipeline velocity. In this data set, the average sales days per deal has increased from 30.7 for 2011, to 42.86 so far in 2012. That’s a pretty alarming slowdown in pipeline velocity, and might be worth losing some sleep over.

Velocity by Sales Stage

One problem with this measure is it’s a broad brush: a lot can happen between the creation and closing of an opportunity and a measure like this one, although better than nothing, doesn’t provide insight on why velocity’s changing. Organizations with well-defined staged sales processes can take analysis a step further and examine how long deals stay in each stage. A client of mine with a 4-stage sales process uses a “stage aging” chart to identify bottlenecks in its sales process. Here’s an example:

Charts like this one show the average days spent in each stage across all deals in the filtered data set. In this example, the data set includes all opportunities (“deals”, in this client’s parlance) closed as won, YTD for 2012. You can imagine comparing this to different time periods to see how stage aging has changed over time. But analysis like this allows you to identify bottlenecks across different dimensions, as well. For example, you might want to filter the data set by deal type:

This makes it obvious that type 1 deals get bogged down in the underwriting stage. Filtering for type 2 makes it clear they tend to languish in the proposal stage:

I like this kind of analysis since it’s actionable: type 1 deals need process improvements in the underwriting stage, type 2 deals in proposal.  And you can extend this to pretty much any information you track on your opportunity records, comparing stage aging across dimensions like these:

• Owners, teams, business units
• Products or product groups
• Time periods
• Won deals v. lost deals

The Problem with Velocity

Pipeline velocity matters, but it’s not all that matters. Returning to the sample data set we started out with, suppose you added a couple more fields to the pivot table:

All else equal, changes in pipeline velocity are important…but all else generally isn’t equal, as this example points out: average revenue per sales day has skyrocketed. The following pivot chart focuses in on just Average Sales Days and Average Revenue per Sales Day measures, adds trend lines for both, and gives us a pretty clear picture of what’s happening:

The days it takes to close a deal have increased, but the revenue yield per day of selling time has increased a lot more. This is invariably driven by bigger deals: they take longer to close, but the payoff is probably worth it. And what this really tells us is that, if the only thing you know is your pipeline velocity, you really don’t know if you should be worried or not. So you might as well get some sleep.

If you need guidance or implementation assistance with Microsoft Dynamics CRM, contact Chicago CRM partner, Magenium Solutions - we can help!

By Richard Knudson, Magenium Solutions Microsoft Dynamics CRM practice VP, author of Building Business with CRM: Using Processes in Microsoft Dynamics CRM 2011.