In part one we covered how to track volume and conversion rates in salesforce.com, but today we are going into the more complicated velocity metric.
Funnel Velocity
To calculate funnel velocity, or how quickly records are moving from one funnel stage to another you need to determine the number of days between stage progressions. The first thing you will need is a formula field on the object you are tracking through the funnel.
If you want to calculate velocity in terms of standard days then this is a fairly straightforward formula, you would just subtract the date of the later stage by the date of the initial stage. However, in many cases people want to look at velocity in terms of weekdays. Luckily there is a clever way to do that.
I did not come up with this method, but I have used it reliably for quite some time. It can be adapted to replace any number of day calculation to weekdays. Replace “FROMDATE__c” with the initial stage date and “TODATE__c” with end stage date.
Whether you go with regular days or weekdays, the formula can be exposed on page layouts and can also be used to drive SLA policies. However, to get the funnel velocity we need to incorporate this into a report with a summary formula like we did for conversion rates.
The summary formula for average velocity will look like this:
IF(TOSTAGE_BOOLEAN__c > 0,FROMSTAGE_TOSTAGE_DAYS_c:SUM/TOSTAGE_BOOLEAN__c:SUM,NULL)
Similar to the conversion rate formula, we first check to make sure we won’t divide by a zero value, and if we do we return a null value. Note, we use null here instead of 0 because in this case a divide by zero means the calculation does not have enough data to return answer and we wouldn’t want to report that as a 0 day velocity (extremely fast).
The actual calculation takes the sum of all of the days and divides by the number of records that made it to the second stage. Note that, if TOSTAGE is false for a record that the Days formula should be null since a stage date won’t have been set. The end result is the average time in days or weekdays between two stages.
There you go, you now have your key funnel metrics natively in salesforce.com. No need to manually export data and run calculations in excel anymore!
Learn more about best practices in designing and implementing your lead lifecycle in Salesforce. Measure your lead progression with automated funnel stage tracking. Check out the Best Practices for Designing a Lead Lifecycle in Salesforce whitepaper.
