Velocity and Conversion Ratios for Any Sales Funnel
There are two key questions everyone managing a sales pipeline needs to know: what are the conversion ratios between key stages and how long does the sales process take for each stage. To start off with, I’d like to clarify that this post is not talking about a buyer journey. A sales funnel does not describe how buyers buy - it describes how sellers sell. While there are some obvious overlaps, how buyers buy and how sellers sell are two fundamentally different conversations.
Sales funnel metric will answer questions like who is closing the most opportunities per lead? Who is closing business the quickest/slowest after providing a demo? What industries have the shortest sales cycle?
Defining the Funnel
In a laboratory environment, each milestone in a sales process is represented by a stage on an opportunity. However, in the numerous Salesforce orgs I have interacted with, I have yet to see that be the case for a variety of reasons.
One common reason, the lines between marketing and sales are continually getting more blurred. The funnel has to include marketing and “top of funnel” activity in order to provide a complete picture. This means tracking information on objects other than opportunities is important, especially leads, contacts, and accounts. Each metric around this information is a key performance indicator (KPI) for the sales process.
In addition to including top of funnel metris, these KPIs (also called milestones or stages) in a sales process often combine stages with activities, such as a discovery meeting or demo.
In order to demonstrate an approach for reporting on the velocity and conversion rations of any sales funnel, I’m going to use a somewhat complex example at a fictional company to work though that measures KPIs from leads, tasks, events, and opportunities as components of the sales funnel.
The steps for measuring sales funnel velocity and conversion rations are:
Identify KPIs
Create date fields, power of one fields, and velocity fields for each KPI
Automate date field completion with process builder
Connect the Dots by populated an “Original Lead” field
Create a custom report types & reports with summary formulas to calculate our conversion ratios and average the velocities for each stage.
Add report to dashboard components for a comprehensive view of velocity and conversion.
Identify KPIs
Identifying and measuring the KPIs is imperative for sales and business leaders develop effective strategy and determine best investments of time and money. In order to support business leads as a Salesforce admin or consultant, it is important to be familiar with trends and industry standard practices. Let’s define some common milestones to use for our example sales funnel.
Lead Created (Lead)
Whether coming from a marketing campaign, outbound prospecting, or a list such as badges scanned at Dreamforce, lead creation is the start of most sales funnels.
Lead Stage: MQL (Lead)
MQLs (Marketing Qualified Leads) are typically represented by a lead stage. Salesfusion give a great concise definition: “MQLs are leads that marketing deems are qualified and ready for sales follow-up.”
Lead Stage: SQL (Lead)
Lead stages are key indicators of progress through a sales funnel, and SQL (Sales Qualified Lead) is another key stage. Again borrowing a definition from Salesfusion, “SQLs are prospects that have been vetted to determine if there’s an interest to connect them to the next stage in the buying cycle, sometimes called the Discovery or Demo stage.
Discovery Meeting/Demo Booked (Task)
In this example, our sales development team will set appointments for an Account Executive on the sales team.
Lead Converted to Opp (Lead/Opportunity)
Once the demo or discovery appointment is set, the Sales Development Rep will aslo convert the lead to an opportunity and add notes about any particular interests and pain points.
Discovery Meeting/Demo Held (Event)
Account executives sync their calendars with Salesforce and will mark an event as completed, no show, or disqualified after the scheduled meeting time.
Opp Stage: Closed Won (Opportunity Stage)
While there may be several other stages such as proposal and negotiation, our sales leadership team as determined to focus on closed won opportunities as our final KPI in the sales funnel.
Create date fields, power of one fields, and velocity fields for each KPI
Before we can start building reports to track the sales funnel velocity and conversion ratios in Salesforce
The date fields should be read-only on all page layouts as they will be updated automatically through process builder.
Existing date fields we will use include:
Lead Created Date
Opportunity Created Date
Opportunity Close Date
Here are the fields to be created for our example scenario:
Object: Lead
Field: MQL Date (date)
Field: SQL Date (date)
Object: Opportunity
Field: Demo Scheduled (date)
Field: Demo Held (date)
Object: Activity/Event
Field: Demo Meeting (checkbox)
Field: Meeting Status (Picklist – Completed, No Show, Disqualified)
Object: Activity/Task
Field: Scheduled Demo For (date)
Create Formula Fields for Velocity Tracking
The formula fields will simply subtract the 2nd date from the first.
Lead
Days from lead creation to MQL
Days from MQL to SQL
Days from SQL to Lead Converted
Opportunity
Days from Lead Converted/Opp Created to Demo Scheduled
Days from Meeting Scheduled to Demo Held
Days from Demo Held to Opportunity Closed (Won)
Create Power Of One Fields for Calculating Conversion Ratios
For each date field, create a “Power of One” formula that returns 1 if the date field is filled in and 0 if not.
Automate date field completion with process builder
Create one process for three objects (lead, task, event) using Flow. This step is optional and reps could manually enter the dates for each stage if preferred.
Lead Process Builder
When Lead Status = MQL & MQL Date is blank, set MQL Date to TODAY.
When Lead Status = SQL & SQL Date is blank, set SQL Date to TODAY.
Task Process Builder
If Scheduled Demo For date is not blank & what ID begins with “006” (ie is an opportunity) & Opportunity Meeting Scheduled Date is blank, then update Opportunity Meeting Scheduled Date with the Scheduled Demo For date from the task.
Event Process Builder
If what ID begins with “006” (ie is an opportunity) & Opportunity Meeting Scheduled Date is blank & Event Status = Completed & Demo Meeting = True, then update Opportunity Meeting Scheduled Date with the event date.
Connecting the Dots
In order to tie the complete sales funnel together and get all the ratios and velocity metrics into a single reports, create a lookup field on the Opportunity Object to Leads. Create a process builder on the lead object that populates the lookup field on the opportunity with the ID of the lead when the lead is converted.
A side benefit of adding a lookup field to the original lead on opportunities is the ability to automatically update the opportunity record type based on the lead record type.
I also recommend adding an equivalent lookup field to the original lead on accounts.
Create a custom report types & reports with summary formulas to calculate our conversion ratios and average the velocities for each stage.
The main custom report type needed is going to be Leads with or without opportunities. Since we created a lookup field on Opportunities to leads, we have this relationship available now. It’s also possible to add all the fields to a leads with converted information report, but that is a lot more tedious than creating a leads with opportunities report.
A leads with or without accounts report will also give insight into how many leads does it take to get one client. (Hint, adding a filter where account status = current or former customer helps.)
Add report to dashboard components for a comprehensive view of velocity and conversion.
Now that all of our numbers (days between stages) and number of leads/opps that reached each stage are in the same report, we can use summary formulas to calculate the average velocities and conversion ratios and display them in two simple, yet powerful dashboard components that are guaranteed to add value to anyone managing a sales pipeline.
