Reactivated Lead Close Rate: How to Win More Deals From Your Existing Pipeline

Reactivated Lead Close Rate (%)

1. Add the required reactivation and close fields to your dataset

• In your dataset, make sure you have reactivation columns (starting around column AG in the video).
  
• Minimum fields needed:
  
  • Reactivation date and time
    
  • Reactivation channel
    
  • Reactivation reason
    
  • Close status (won or lost)
    
  • Close date and time
    
  • Close reason
    

2. Create helper columns that power the KPI calculations

• Create a “Reactivated Lead Source” helper column:
  
  • If Reactivation Date/Time is blank, return blank
    
  • Otherwise return the original Lead Source
    
• Create a “Reactivated Subsource” helper column the same way (optional in the video, but shown)
  
• Create a “Reactivation Month” helper column:
  
  • If Reactivation Date/Time is blank, return blank
    
  • Otherwise extract the month name using TEXT(date_cell,”mmmm”)
    
• Create a “Was Reactivated” flag column (0/1):
  
  • If Reactivation Date/Time is blank, return 0
    
  • Otherwise return 1
    
• Create a “Reactivated Won” flag column (0/1):
  
  • If Was Reactivated = 1 and Final Status = Closed, return 1
    
  • Otherwise return 0
    

3. Build the monthly summary table

• Create a Month column:
  
  • Type January and drag down through December
    
• Create “Reactivated” count by month:
  
  • Use COUNTIFS() to count rows where:
    
    • Reactivation Month = the month in your summary table
      
    • Was Reactivated flag = 1
      
• Create “Won” count by month:
  
  • Use SUMIFS() to sum the Reactivated Won flag where:
    
    • Reactivation Month = the month in your summary table
      
  • Because it is a 0/1 column, sum gives you the count of wins
    

4. Calculate the Reactivated Lead Close Rate (%)

• In the monthly summary table:
  
  • Close Rate = Won ÷ Reactivated
    
• Format as a percent using Ctrl + Shift + 5
  
• Copy the formula down for all months
  

5. Calculate the true 3-month moving average

• Do not average the last 3 close rate percentages
  
• Instead calculate:
  
  • Sum of Won for the last 3 months ÷ Sum of Reactivated for the last 3 months
    
• Drag down so the 3-month window rolls forward each month
  
• This prevents “average of averages” errors when monthly volumes differ
  

6. Create the combo chart with a secondary axis

• Select the monthly columns you want charted:
  
  • Reactivated count
    
  • Won count
    
  • Close Rate percent
    
  • 3-month moving average percent
    
• Insert a chart:
  
  • Insert → Recommended Charts
    
  • Then switch to All Charts → Combo
    
• Set:
  
  • Reactivated and Won as columns on the primary axis
    
  • Close Rate and 3-month average as lines on the secondary axis
    
• Title suggestion used in the video:
  
  • “Reactivated Close Rate by Month”
    
• Optional labeling:
  
  • Add data labels to just one line series to avoid clutter
    
  • Resize or reposition labels if they overlap
    

7. Build the lead source by reactivation channel heat map

• Create a unique list of Lead Sources:
  
  • Use UNIQUE() on the lead source field from your table
    
  • Copy → Paste Values (Ctrl + Shift + V)
    
• Create a unique list of Reactivation Channels:
  
  • Use UNIQUE() on the reactivation channel field
    
  • Copy → Paste Values
    
  • Sort A to Z if desired
    
• Transpose the reactivation channels so they run across the top:
  
  • Copy → Paste Special → Transpose (or the shortcut shown in the video)
    

8. Populate the heat map denominator

• For each Lead Source x Reactivation Channel cell:
  
  • Use COUNTIFS() to count reactivated leads where:
    
    • Was Reactivated flag = 1
      
    • Reactivation Channel = the column header
      
    • Reactivated Lead Source = the row label
      
• Lock cell references correctly so you can copy across and down:
  
  • Lock the row for the header reference
    
  • Lock the column for the lead source reference
    
• If dragging causes table column references to shift incorrectly:
  
  • Copy and paste formulas instead of dragging
    

9. Calculate the heat map close rate with IFERROR

• Close Rate = Won Reactivations ÷ Reactivations
  
• The numerator uses the same base logic but adds a “Closed” requirement
  
• Wrap the full formula in IFERROR():
  
  • If divide-by-zero occurs, return a dash “-”
    
• Copy across and down using copy/paste (not drag) for table stability
  
• Key interpretation shown in the video:
  
  • Dash means no denominator (no reactivations happened)
    
  • Zero means reactivations happened but none closed
    

10. Add a target close rate and conditional formatting

• Create a target cell (example used: 40%)
  
• Conditional format the heat map:
  
  • Highlight cells less than the target:
    
    • Home → Conditional Formatting → Highlight Cells Rules → Less Than
      
    • Reference the target cell
      
• Format dashes separately so they do not look like failures:
  
  • Conditional Formatting → Text that contains → “-”
    
  • Apply a neutral fill (gray) to visually separate “no data” from “below target”
    

11. Display “Target-40%” without breaking the numeric value

• Keep the cell as a numeric percent
  
• Use custom number formatting:
  
  • Ctrl + 1 → Number → Custom
    
  • Add text in quotes before the percent format, like:
    
    • “Target-“0%
      
• This displays the label while keeping the value usable in rules and formulas