Lead to Contact Rate (%): Track Response Speed and Lead Follow-Up Performance

Build Lead to Contact Rate in Excel

1. Set up the dataset fields used in the video

• Lead timestamp date and time
  
• Sales rep assigned
  
• Lead source
  
• First contact timestamp
  
• Optional columns you can slice later: lead type, campaign, ad source, etc.
  

2. Create a unique list of Lead Sources

• In a blank area, use the UNIQUE function on the Lead Source column from your table
  
• When selecting the table column, click so the cursor becomes a down arrow and the whole sheet column is not highlighted
  
• Copy the results and paste values: Ctrl + C, then Ctrl + Shift + V
  

3. Add the summary table headers

Create four columns next to your Lead Source list:

• Leads
  
• Contacted
  
• Not Contacted
  
• Lead to Contact Rate
  

4. Calculate Leads per Lead Source with COUNTIF

• In the Leads column, use COUNTIF on the table Lead Source column
  
• Criteria should reference the Lead Source cell for that row
  
• Center the results if you want the same look as the video
  

5. Add a Contacted helper column inside the dataset

• Create a new column called Contacted
  
• Use an IF formula:
  
  • If First Contact Timestamp is blank, return 0
    
  • Otherwise return 1
    
• Fill the formula down the column
  

6. Add a Time to Contact helper column in minutes

• Create a new column called Time to Contact
  
• Use an IF formula:
  
  • If Contacted equals 0, return blank
    
  • Otherwise subtract Lead Timestamp from First Contact Timestamp
    
• Convert Excel time to minutes by multiplying inside the IF:
  
  • (FirstContactTimestamp – LeadTimestamp) * 24 * 60
    
• Fill the formula down
  
• If you see errors, make sure the multiplication is inside the IF so blanks are not multiplied
  

7. Create a Time to Contact Bucket column

• Create a new column that labels each row based on the time to contact
  
• Build it as a nested IF that evaluates in this order:
  
  • If First Contact Timestamp is blank, label Not contacted
    
  • Else if minutes <= 5, label 0 to 5
    
  • Else if minutes <= 10, label 6 to 10
    
  • Else if minutes <= 15, label 11 to 15
    
  • Continue the pattern for your remaining buckets like 16 to 30, 31 to 60, and Greater than 60
    
• This works because the logic is sequential and stops at the first match
  

8. Calculate Contacted per Lead Source with COUNTIFS

• In the Contacted column of your summary table, use COUNTIFS with:
  
  • Contacted equals 1
    
  • Lead Source equals the Lead Source cell in the summary row
    
• Fill down for all lead sources
  

9. Calculate Not Contacted and Lead to Contact Rate

• Not Contacted:
  
  • Leads minus Contacted
    
  • Or use COUNTIFS with Contacted equals 0
    
• Lead to Contact Rate:
  
  • Contacted divided by Leads
    
• Format as percent with Ctrl + Shift + 5
  
• Fill down the column
  

10. Build the Lead to Contact Rate combo chart

• Highlight Lead Source, Leads, and Lead to Contact Rate
  
• Hold Ctrl to select non-adjacent columns when needed
  
• Insert, then Recommended Charts
  
• If the chart is reversed, switch to:
  
  • All Charts, then Combo
    
  • Lead to Contact Rate as Clustered Column
    
  • Leads as Line
    
  • Set Leads to Secondary Axis
    
• Rename the chart title to Lead to Contact Rate by Lead Source
  

11. Build the response time distribution table by Lead Source

• Copy your Lead Source list into a new section for the time bucket table
  
• Create the bucket headers across the top:
  
  • Use UNIQUE on the Bucket column
    
  • Copy and paste values
    
  • Rearrange into the order you want
    
  • If they are already in the correct vertical order, copy and Paste Special Transpose to place them across the top
    

12. Calculate percent of leads in each time bucket

For each lead source row and each bucket column:

• Numerator uses COUNTIFS with:
  
  • Lead Source equals the row lead source
    
  • Bucket equals the column bucket label
    
• Denominator is total leads for that lead source:
  
  • Use COUNTIF or COUNTIFS for lead source only
    
• Divide numerator by denominator and format as percent
  
• When copying across and down, use absolute references with dollar signs so:
  
  • Lead source stays tied to the row as you copy across
    
  • Bucket header stays tied to the column as you copy down
    
• If table references behave oddly when dragged, copy and paste the formula across instead of dragging
  

13. Apply conditional formatting to create a heat map

• Highlight the table range you want to visualize
  
• Conditional Formatting, Color Scales
  
• If the full-table scale looks confusing, apply it row-by-row instead:
  
  • Apply the scale to one row
    
  • Double click Format Painter
    
  • Click each row to apply the same formatting
    

14. Optional build shown at the end: replicate by Sales Rep

• Repeat the same exact method, but slice on Sales Rep instead of Lead Source
  
• Unique list of reps, leads, contacted, lead to contact rate
  
• Optional response time bucket distribution by rep