Median Speed to First Contact (Minutes):
Track Lead Response Time

Build Median Speed to First Contact in Excel

1. Convert your dataset into an Excel Table

• Click anywhere inside your dataset and press Ctrl + T.
  
• Confirm the table has headers so formulas expand automatically as you add rows.
  

2. Create a Contacted flag column

• Add a new column named something like Contacted.
  
• Use an IF formula that checks whether the First Contact Timestamp is blank.
  
  • If it is blank, return 0.
    
  • If it is not blank, return 1.
    
• Fill the formula down the entire table column.
  

3. Create Minutes to First Contact

• Add a new column named Minutes to Contact.
  
• Use an IF formula tied to your Contacted flag:
  
  • If Contacted equals 0, return blank.
    
  • Otherwise subtract Lead Created Timestamp from First Contact Timestamp.
    
• Convert the timestamp difference into minutes:
  
  • Wrap the subtraction in parentheses, then multiply by 24 and then by 60.
    
• Fill the formula down the column so every row calculates minutes.
  

4. Optional: Create time buckets for response time

• Add a column like Response Time Bucket.
  
• Use nested IF logic based on Minutes to Contact:
  
  • If blank, label it Not contacted.
    
  • If less than or equal to 5, label 0 to 5 minutes.
    
  • If less than or equal to 10, label 6 to 10 minutes.
    
  • Continue as needed, then use a catch-all for 60+ minutes.
    
• This is optional, but it helps you group response time into ranges for reporting.
  

5. Generate a unique list of Sales Reps

• In a reporting section, type Sales Rep as the header.
  
• Use the UNIQUE function to pull a unique list of sales reps from the table.
  
• Make sure you reference the table column, not the worksheet column.
  
• Copy the results and paste values using Ctrl + Shift + V if you want a static list.
  

6. Calculate Average Minutes to Contact by Sales Rep

• Add a column header: Average Minutes to Contact.
  
• Use AVERAGEIFS:
  
  • Average range: Minutes to Contact column
    
  • Criteria range: Sales Rep column
    
  • Criteria: the sales rep name in your unique list
    
• Format as a number using Ctrl + Shift + 1.
  
• Copy the formula down for every rep.
  

7. Calculate Median Minutes to Contact by Sales Rep

• Add a column header: Median Minutes to Contact.
  
• Since there is no MEDIANIF, recreate it using FILTER:
  
  • Use FILTER to return only Minutes to Contact values where Sales Rep equals the rep name.
    
  • Wrap the FILTER result inside MEDIAN.
    
• Copy the formula down for every rep.
  

8. Optional: Calculate Not Contacted count and percent by Sales Rep

• Add a column header: Not Contacted.
  
• Use COUNTIFS:
  
  • Contacted equals 0
    
  • Sales Rep equals the rep name
    
• For a percentage, divide Not Contacted by the total leads for that rep:
  
  • Use another COUNTIFS (or COUNTIF) for total leads by rep
    
  • Format as percent
    

9. Create the Sales Rep chart using Median Minutes

• Hold Ctrl and select:
  
  • Sales Rep names
    
  • Median Minutes to Contact
    
• Go to Insert → Recommended Charts and select a Clustered Bar chart.
  
• Update the chart title to something like “Median Minutes to Contact by Sales Rep”.
  

10. Repeat the same analysis for Lead Source

• Copy the Sales Rep summary block and paste it to the right or below.
  
• Change the header to Lead Source.
  
• Use UNIQUE on the Lead Source column to generate the list.
  
• Update formulas by replacing the Sales Rep criteria range with Lead Source:
  
  • AverageIFS: swap Sales Rep column for Lead Source column
    
  • Median formula: swap the FILTER condition to Lead Source equals the selected lead source
    
  • Not contacted: swap Sales Rep criteria for Lead Source criteria
    

11. Build the Lead Source chart and add data labels if desired

• Create a chart from:
  
  • Lead Source names
    
  • Median Minutes to Contact
    
• Insert a chart type of your choice:
  
  • Line chart if you want a trend-like visual
    
  • Column or bar chart if you want cleaner comparisons
    
• To show numbers on the chart:
  
  • Click the chart
    
  • Turn on Data Labels
    
  • Adjust label formatting and styling however you want