Why is a Holistic View of Your Lead Funnel Important?
One of the common challenges our Salesforce clients face is getting a holistic picture of their lead funnel. And this is no small problem. Seeing your data accurately and understanding how it works together allows you to optimize your sales and marketing efforts, identify your strengths and weaknesses, and improve your company’s ROI. When leveraged effectively, Salesforce can, for example, show you the exact part of a sales process where leads fall off and your sales team struggles to move to the next step. This allows management to address those areas. Marketing teams can glean information about which lead sources are the most effective and shift around their budget and effort accordingly, boosting ROI.
There are dozens of ways to tweak and customize Salesforce to give you the information your business needs. Today, though, I’ll be focusing on sharing how to track lead status history and conversion rates on the platform.
Regardless of the volume of leads that your organization receives, the following tips will help you report on the following:
- Lead status–to–status progressions
- Lead conversion %
Salesforce Lead History Reports
Most Salesforce admins are aware that you can enable Lead History on the Status field to create history reports. Here is a quick reminder on how to do that:
Click on the Setup -> Object Manager -> Leads ->Fields and Relationships -> Set History Tracking
The history reports will allow you to see changes to the Status field as well as when and who made those changes. However, due to the high volume of data pulled into reports, they will not only test your patience, but they won’t give you a true picture of the lead conversion and status-to-status progression.
Getting a Full Picture While Tracking Status History
For this article, we will look into a slightly different yet simple approach to tracking status history. We will implement the following:
- Creating a custom object to store historical changes)
- Creating a flow to create records that store historical changes)
- Creating a conversion report to report on the changes
Let’s dive in. First, we need to create a custom object of where we plan on storing our Lead Status history. Let’s call it simply “Lead Status History.”
Click on the Setup -> Object Manager -> Create-> Create Custom Object
Add the following fields or the fields of your choice:
- Lead Status (picklist)
- Status Change Date (date)
- Lead (lookup to Lead object)
Second, we now need something that would help us populate data into the custom object. We will be creating a simple flow to create a record in our custom object every time the lead status changes.
Click on the Setup -> Home -> Search for Flow-> Create new flow-> Record Triggered Flow
In our conditions, we will look for Status change:
And since we will be creating related records to the Lead, we will select Actions and related Records:
Now, let’s map the fields we will populate when the Lead History Record is created:
Now, the flow is ready to go! Activate and let the data roll!
Creating a Report on Lead Progress and Status Conversion Rate
Lastly, let’s create a report to see how our leads progress and convert from status to status. We want to know the following about the leads we have in the “Open – not contacted” status:
- How many progressed into “Working – contacted”
- How many were “Closed – not converted”
- How many were converted
We will create our report using a new custom object. Of course, you may need to wait some time for the data to be collected before you will be able to report on it.
The report will be using Lead Status History with Lead type:
We will create two summary formula fields:
- Progression Rate
- Conversion Rate
Progression rate takes the total number of leads per group and divides it by the number of leads in each previous Status grouping:
RowCount/PREVGROUPVAL(RowCount,Lead_Status_History__c.Lead_Status__c)
For example, there are nine leads in Open – not Contacted. Out of those nine, we have eight that moved to Working – Contacted, which gives us 89% (rounding up).
Out of the 8 in Working – Contact, six moved to Closed – Not Converted, which gives us 75% (6/8 =0.75)
And because we are creating this report using the Lead Status History object, we can say with certainty that these leads were at some points in those specific statuses without skipping them!
Conversion Rate is much more straightforward. We need to take the total of converted leads and divide it by ALL leads.
And if you display the results by grouping, then you will see something like this:
This tells us that out of 9 leads in Open – not contacted status, four were converted, which resulted in 44% (4/9=0.44)
There are many other ways to slice and dice Lead conversion data:
- By the owner
- By the time period
- By other lead information
If you need help with creating KPI reports, our team will be ready to assist.
Next Steps in Your Salesforce Implementation
Our team helps companies optimize Salesforce in hundreds of ways. This is just one of them. If you have any questions about how to further customize your platform functionality, please contact our team to talk with one of our senior consultants. 
Happy reporting!
