How To Track Key Funnel Metrics Within Salesforce, Part 1
- AUTHOR Luke Duncan
- August 16, 2013
- No Comments
If your in marketing operations you’ve probably been asked to provide 3 key metrics on funnel performance. Ensuring you have your data setup to provide funnel volume, conversion rates, and velocity within salesforce.com can save you a ton of time manipulating data in excel.
The first step is making sure you have all the required data in the correct place. For each stage in your funnel you need to track whether or not the stage has been achieved and more crucially when it was achieved. Make sure whichever object you are tracking in your funnel has a boolean field and a date field for each stage. Based on your business logic and how you have defined your funnel stages populate the fields using workflow. For example if a lead reaches a score threshold you may consider that an MQL and use a workflow rule to make the boolean field as true and set the date to today. With that in place you are ready to start reporting.
Funnel Volume by Stage
This is probably the easiest of the three, but some people may not be aware that you can use boolean fields to calculate totals in reports.
Just create a Summary report with whatever grouping and dimensions you are concerned with, and add all of the stage boolean fields to your report. Summarize the columns using “Sum”, and you will be able to see the volume of records that achieved each stage.
Funnel Conversion Rates
To calculate the conversion from one funnel stage to another you can use a summary formula. This approach is flexible because it allows you to filter and group your report to see your conversion rates by different dimensions.
The summary formula should look like this:IF(FROMSTAGE_BOOLEAN__c:SUM > 0,TOSTAGE_BOOLEAN__c:SUM /FROMSTAGE_BOOLEAN__c:SUM,0)
If you are calculating the conversion rate from MQL to SAL, you would replace “FROMSTAGE_BOOLEAN__c” with your MQL boolean and “TOSTAGE_BOOLEAN__c” with your SAL boolean field. This formula takes advantage of the fact that Boolean fields evaluate to 1 or 0, and therefor the sum of a boolean field within a grouping will give you the total number of records in the grouping where the boolean is true.
The summary formula will first check to see if there are any records within the grouping that have reached starting stage, if there are none it will evaluate to 0.00%–Thus preventing division by 0 errors. When there are records in the from stage, it will calculate the conversion rate by dividing the number that made it to the next stage by the number in the starting stage.