Day Close Rate (%): Identify Revenue Leakage and Improve Same-Day Conversions

Build the Day Close Rate Table and Chart

1. Copy the core fields you need into a working area

• In your “Estimates Run” dataset, confirm you have these fields available:
  
  • Week Start
    
  • Lead Source
    
  • Sales Rep
    
  • Cancel Flag, No Show Flag, Rescheduled Flag
    
  • Same Day Close Flag (1 if estimate run date equals sold date, else 0)
    
• Create a new working section (or tab) where you’ll build the KPI table and chart.
  

2. Create a unique list of Week Start dates

• Use the UNIQUE function on the Week Start column from the table (make sure you click the table column, not a filtered column outside the table).
  
• Convert the results to a short date format.
  
• Copy and paste values using Ctrl + Shift + V to remove the formula.
  
• Keep the list sorted chronologically.
  

3. Count weekly Estimates Run using COUNTIFS

• Create a column for total estimates run per week using COUNTIFS:
  
  • Criteria 1: Week Start equals the week in your summary table
    
• Add criteria to exclude bad outcomes:
  
  • Cancel Flag = 0
    
  • No Show Flag = 0
    
  • Rescheduled Flag = 0
    
• You will get a count of valid opportunities per week.
  

4. Create a single “Check Flag” to simplify formulas

• Add a helper column in the dataset called Check Flag:
  
  • Check Flag = Cancel Flag + No Show Flag + Rescheduled Flag
    
• Now update the weekly COUNTIFS formula to use:
  
  • Check Flag = 0
    
• This prevents you from repeating three separate “must equal zero” criteria in every formula.
  

5. Count weekly Same Day Closes

• Create a second weekly COUNTIFS column for same-day closes:
  
  • Criteria 1: Week Start equals the week in your summary table
    
  • Criteria 2: Check Flag = 0
    
  • Criteria 3: Same Day Close Flag = 1
    
• This returns the numerator for the rate.
  

6. Calculate Day Close Rate percentage

• Create the rate column:
  
  • Day Close Rate (%) = Same Day Closes ÷ Valid Estimates Run
    
• Format as percent (Ctrl + Shift + 5).
  
• Copy down for all weeks.
  

7. Add a benchmark line that stays dynamic

• Enter a benchmark value (example: 45%) in a benchmark cell.
  
• In a “Benchmark” column aligned with your weeks, reference that benchmark cell so it repeats down the table.
  
  • This makes the benchmark line dynamic if you change the benchmark later.
    

8. Build the weekly chart with a secondary axis

• Highlight Week Start plus:
  
  • Estimates Run count (the denominator column)
    
  • Day Close Rate (%)
    
  • Benchmark (%)
    
• Insert chart:
  
  • Insert → Recommended Charts (or All Charts → Combo)
    
• Configure chart series:
  
  • Estimates Run stays on the primary axis
    
  • Day Close Rate (%) goes on the secondary axis
    
  • Benchmark (%) stays with the rate on the secondary axis
    
• Rename the chart title to: Day Close Rate by Week
  

9. Add the benchmark line to the chart using the copy-paste trick

• Copy the Benchmark column values (including the header).
  
• Click the chart and paste (Ctrl + V) to add the line series.
  
• Format the benchmark line to be visible but subtle (example: dashed line style).
  

10. Build the Rep x Lead Source tables for deeper drilldowns

• Create a unique list of Sales Reps:
  
  • Use SORT(UNIQUE(Sales Rep)) to get an A to Z list
    
  • Copy and paste values (Ctrl + Shift + V)
    
• Create a unique list of Lead Sources:
  
  • Use SORT(UNIQUE(Lead Source))
    
  • Copy and paste values
    
• Transpose the lead sources across the top of the table:
  
  • Copy → Paste Special → Transpose
    

11. Count opportunities by Rep and Lead Source

• In the bottom table, calculate the denominator first (opportunities):
  
  • COUNTIFS with criteria:
    
    • Sales Rep = row header rep
      
    • Lead Source = column header lead source
      
    • Check Flag = 0
      
• Lock references correctly using absolute referencing:
  
  • Lock the Sales Rep column reference so it does not shift when dragging across
    
  • Lock the Lead Source row reference so it does not shift when dragging down
    
• Copy across and down to fill the matrix.
  

12. Calculate Day Close Rate by Rep and Lead Source

• Copy the denominator COUNTIFS formula to the top table (rate table), then modify it:
  
  • Add criteria: Same Day Close Flag = 1 for the numerator
    
  • Divide numerator ÷ denominator
    
• Format as percent and fill across/down.
  

13. Hide low-sample-size cells using an IF threshold

• Wrap the rate formula in an IF statement:
  
  • If denominator is less than 10, return a dash
    
  • Otherwise return the percentage formula
    
• This prevents overreacting to tiny sample sizes.
  

14. Add conditional formatting triggers

• Add a cell that holds your benchmark percent (example: 45%).
  
• Apply conditional formatting to the rate matrix:
  
  • Highlight cells less than the benchmark (Home → Conditional Formatting → Highlight Cells Rules → Less Than)
    
• Apply a second conditional formatting rule:
  
  • Highlight cells that contain a dash
    
  • Set the fill color to gray so low-sample cells are visibly “disabled”
    

15. Format a benchmark label like “Target: 45%”

• If you want the benchmark to display as text while still being numeric:
  
  • Click the benchmark cell → Ctrl + 1 (Format Cells)
    
  • Custom format
    
  • Add a custom notation that displays “Target: ” before the percent value
    

This produces a clean weekly trend chart plus a rep and lead source drilldown grid that flags underperformance and hides unreliable sample sizes.