Follow-Up Compliance Rate: Weekly Sales Execution KPI

Building Follow Up Compliance Rate

1. Prepare the Core Data Sets

• Work with two datasets:
  
  • An Estimates dataset containing estimate ID, estimate date and time, owner, lead source, and follow-up requirements.
    
  • A Touchpoints dataset containing estimate ID and every follow-up attempt with date and time.
    
• Ensure each estimate includes:
  
  • Estimate ID
    
  • Estimate date and time
    
  • Owner name
    
  • Follow-up required flag
    
  • Required touches within 7 days
    
  • Required touches within 14 days
    
  • First-touch SLA requirement in hours
    

2. Calculate the Week Start Date

• Extract the weekday number from the estimate date.
  
• Subtract the weekday value from the estimate date.
  
• Add 1 to return the Sunday week start date.
  
• Use this value to group activity week by week.
  
• Create a unique, sorted list of week start dates using UNIQUE and SORT.
  
• Paste values to remove formulas.
  

3. Identify First Touch Timing

• Use MINIFS on the touchpoints dataset to return the earliest follow-up date and time per estimate ID.
  
• If follow-up is not required, return a blank.
  
• Subtract the estimate date and time from the first touch date and time.
  
• Multiply the result by 24 to convert days into hours.
  
• Store this value as First Touch Hours.
  

4. Count Follow-Up Touches

• Use COUNTIFS to count follow-up attempts by estimate ID.
  
• Apply conditions to calculate:
  
  • Touches within 7 days
    
  • Touches within 14 days
    
• Use a touch-day index to determine how many days after the estimate each follow-up occurred.
  
• Compare actual touch counts against required touch counts.
  

5. Determine Follow-Up Compliance

• Create a compliance flag using conditional logic:
  
  • If follow-up is not required, return 1.
    
  • If follow-up is required:
    
    • First touch exists
      
    • First touch hours meet SLA
      
    • 7-day touches meet or exceed required count
      
    • 14-day touches meet or exceed required count
      
  • If any condition fails, return 0.
    
• This creates a binary compliance indicator per estimate.
  

6. Aggregate Weekly Compliance Metrics

• Generate a unique list of week start dates.
  
• Count follow-ups due per week using COUNTIFS.
  
• Sum compliant follow-ups per week using SUMIFS.
  
• Calculate weekly compliance rate by dividing compliant follow-ups by follow-ups due.
  
• Add a minimum acceptable compliance value as a comparison line.
  

7. Build the Weekly Compliance Chart

• Use columns to display follow-ups due per week.
  
• Add a line for actual compliance rate.
  
• Add a second line for minimum acceptable compliance.
  
• Apply a secondary axis for percentage values.
  
• Adjust line thickness and colors for readability.
  
• Avoid excessive data labels to reduce visual noise.
  

8. Create Rep-Level Compliance Views

• Generate a unique list of owner names.
  
• Generate a unique list of lead sources.
  
• Transpose lead sources across columns.
  
• Calculate compliance percentage by owner and lead source using SUMIFS.
  
• Lock cell references to allow formulas to drag correctly across rows and columns.
  
• Paste values once calculations are finalized.
  

9. Add Benchmarks and Coaching Signals

• Insert a benchmark percentage in a single cell.
  
• Apply conditional formatting to flag values below benchmark.
  
• Customize number formatting to display benchmark labels.
  
• Add total follow-up counts per rep to support coaching decisions.