First-Time Fix Rate Dashboard in Excel
(By Month + Technician)

Build First Time Fix Rate by Month and Technician

1. Prepare the Source Data as an Excel Table

• Start with your exported job dataset (example: ServiceTitan export).
  
• Convert the dataset into a table so formulas use structured references:
  
  • Select any cell in the dataset
    
  • Press Ctrl + T
    
• This makes it easier to reference fields like Service Date, Visit Type, Job Status, Return Visit Flag, and Technician.

2. Create a Month Column from Service Date

• Add a Month column to your dataset.
  
• Use the TEXT function to convert Service Date into month name:
  
  • Use TEXT([@[Service Date]],”mmmm”)
    
• This creates values like January, February, etc., used later in COUNTIFS.

3. Create the First Time Eligible Flag

• Add a column that labels whether a job is eligible to be considered for first-time fix.
  
• Build an IF statement using AND logic so it returns:
  
  • “Y” when the job meets all criteria
    
  • “N” otherwise
    
• Criteria used in the video:
  
  • Visit Type equals Initial
    
  • Call Type equals Repair or Emergency
    
  • Job Status equals Completed
    
  • Return Visit Flag equals No
    

Result: you now have a simple Yes or No flag that filters out jobs that should not count in the KPI.

4. Create the Success Flag

• Add a Success Flag column that returns:
  
  • 1 for success
    
  • 0 for not success
    
• Logic used in the video:
  
  • If the Original Job ID indicates a secondary visit, it is not a first-time success
    
  • If the Return Visit Flag indicates a return visit, it is not a first-time success
    
  • If neither of those conditions are true, mark it as a success (1)
    

This converts the outcome into a clean binary field you can count.

5. Build the Monthly Summary Table

• On a new area of the sheet, create the month list:
  
  • Type “Month”
    
  • Enter January and drag down to December
    
• Add these column headers:
  
  • First Time Fix
    
  • Required Return Visit
    
  • First Time Fix Rate
    
  • Benchmark

6. Count First Time Fix Jobs by Month

• In the First Time Fix column, use COUNTIFS with three criteria:
  
  • Eligible Flag equals “Y”
    
  • Success Flag equals 1
    
  • Month equals the month in your summary row (January, February, etc.)
    

This gives the count of successful first-time fixes per month.

7. Count Required Return Visit Jobs by Month

• Copy the prior COUNTIFS formula and change only one condition:
  
  • Success Flag equals 0 instead of 1
    

This gives the count of eligible jobs that required a return visit.

8. Calculate First Time Fix Rate Percentage

• First Time Fix Rate equals:
  
  • First Time Fix divided by the sum of First Time Fix plus Required Return Visit
    
• Format as a percentage:
  
  • Press Ctrl + Shift + 5
    
• Drag the formula down to calculate all months.

9. Add a Dynamic Benchmark Column

• Enter a benchmark value (example used: 95%).
  
• Make it dynamic by referencing the benchmark cell above so the full benchmark column updates automatically.
  
• This allows your benchmark line on the chart to move up or down when you change a single input.

10. Create the First Time Fix Rate by Month Line Chart

• Highlight the First Time Fix Rate column and insert a line chart:
  
  • Insert → Recommended Charts or Insert → Line Chart
    
• Add the benchmark line using a quick trick:
  
  • Copy the Benchmark column
    
  • Click the chart
    
  • Press Ctrl + V to paste it as a second series
    
• Update the chart title to:
  
  • First Time Fix Rate by Month
    
• Optional formatting shown:
  
  • Remove decimal places from labels
    
  • Make the benchmark line thinner and dashed
    
  • Adjust the Y-axis minimum if desired (example: start at 80%)

11. Build the Technician-by-Month Table for the Heat Map

• Create a unique list of technicians:
  
  • Use UNIQUE() on the Technician column
    
  • Copy and paste values (Ctrl + C, then Ctrl + Shift + V)
    
• Add month headers across the top (January through December), left to right.

12. Calculate First Time Fix Rate by Technician and Month

• Use COUNTIFS to build the numerator:
  
  • Eligible Flag equals “Y”
    
  • Success Flag equals 1
    
  • Month equals the column header month
    
  • Technician equals the row technician name
    
• For the denominator, you need both success and not success.
  
• The trick used in the video:
  
  • Use a curly-brace array {0,1} inside COUNTIFS to return counts for both outcomes
    
  • Wrap that in SUM to total them into one denominator
    
• Final structure:
  
  • Numerator count divided by SUM of denominator counts
    
• Apply correct absolute and relative references so:
  
  • Month changes as you drag left to right
    
  • Technician changes as you drag top to bottom
    
  • The data ranges stay fixed

13. Prevent Divide-by-Zero Errors with IFERROR

• When a technician has no jobs in a month, you will get divide-by-zero errors.
  
• Wrap the formula with IFERROR:
  
  • Return blank instead of errors
    
• This keeps the heat map clean and avoids clutter.

14. Add Conditional Formatting with a Threshold

• Enter a benchmark threshold (example used: 75%).
  
• Highlight the technician-month percentage grid.
  
• Apply conditional formatting:
  
  • Home → Conditional Formatting → Highlight Cell Rules → Less Than
    
  • Select the benchmark cell
    
• Result:
  
  • Any value below the benchmark is highlighted (red in the example).

15. Optional: Show “Benchmark: 75%” as Text Without Breaking the Number

• If you want the benchmark cell to display text like “Benchmark: 75%” but still behave as a number:
  
  • Press Ctrl + 1
    
  • Go to Custom format
    
  • Add a custom format that includes the word Benchmark while keeping a numeric percent value