Revenue per Lead KPI in Excel
(Sales & Marketing Performance by Month)

Revenue per Lead Reporting: Monthly Trend and Target Line

1. Identify the Required Columns in Your Dataset

• Confirm you have:
  
  • Lead Date
    
  • Revenue
    
• Each row should represent one lead.
  

2. Create Month and Year Helper Columns from Lead Date

• Add a Month column using the TEXT function to display the month name.
  
  • Use a format with four M’s to return the month name.
    
• Add a Year column using the TEXT function to display the year.
  
  • Use a format with four Y’s to return the four-digit year.
    
• If you already have these helper columns created elsewhere, you can remove any temporary columns after verifying they match.
  

3. Generate a Unique Month-Year List for Reporting

• Create a unique list of Month and Year combinations so you can summarize results by month.
  
• Use UNIQUE across the Month and Year columns.
  
• Use Ctrl + Shift + Right Arrow, then Ctrl + Shift + Down Arrow to quickly select the full range for UNIQUE.
  
• Copy and paste values using Ctrl + Shift + V to remove formulas.
  

4. Convert Your Dataset into an Excel Table

• Click inside your dataset and press Ctrl + T.
  
• Confirm the table has headers.
  
• Name the table so formulas are easier to write and read.
  
  • In the video example, the table name is “TableLeads”.
    
• Using a table name lets you reference columns directly without reselecting ranges.
  

5. Count Leads per Month Using COUNTIFS

• In your summary table, calculate the number of leads for each Month-Year row:
  
  • Use COUNTIFS because you are filtering by both Month and Year.
    
  • Criteria 1: Month equals the month in the summary row.
    
  • Criteria 2: Year equals the year in the summary row.
    
• Fill down to apply the formula to each month.
  

6. Sum Revenue per Month Using SUMIFS

• In the summary table, calculate total revenue for each Month-Year row:
  
  • Use SUMIFS with Revenue as the sum range.
    
  • Criteria 1: Month equals the month in the summary row.
    
  • Criteria 2: Year equals the year in the summary row.
    
• Format revenue as currency using Ctrl + Shift + 4.
  
• Fill down for all months.
  

7. Calculate Revenue per Lead

• Divide total revenue by total leads for each month:
  
  • Revenue per Lead = Monthly Revenue ÷ Monthly Leads
    
• Remove decimals if desired for readability.
  

8. Add a Target Column

• Add a Target value for Revenue per Lead (example shown in the video: 850).
  
• Keep it as a column so it can be plotted as a line on the chart.
  
• If targets vary by job mix or category, you can later segment targets using the same approach.
  

9. Create a Date Label for Charting

• Add a Date column in the summary table combining Month and Year (example: Jan-2025, Feb-2025).
  
• This becomes your X-axis label and keeps the timeline clean.
  

10. Build the Combo Chart

• Select:
  
  • Date
    
  • Leads
    
  • Revenue per Lead
    
  • Target
    
• Insert a Combo chart:
  
  • Leads should be a column.
    
  • Revenue per Lead should be a line.
    
  • Target should be a line.
    
• Use a secondary axis so the chart is readable because lead counts and dollar values have very different scales.
  
• In the video’s setup:
  
  • Revenue per Lead is the primary Y-axis.
    
  • Lead count is on the secondary axis.
    

11. Clean Up Chart Formatting

• Update the chart title to something like: Revenue per Lead in Dollars by Month.
  
• Adjust the target line so it is easier to read:
  
  • Reduce thickness.
    
  • Use a dashed line if desired.
    
• Add data labels to the Revenue per Lead line.
  
• Optionally add a background fill to the labels to improve visibility.
  

12. Add the Year-over-Year Summary Below the Chart

• Create a small year-level summary section (example: 2025 and 2026):
  
  • Total Revenue by Year using SUMIFS where Year equals the selected year.
    
  • Total Leads by Year using COUNTIFS where Year equals the selected year.
    
  • Revenue per Lead by Year as Total Revenue ÷ Total Leads.
    

13. Calculate Year-over-Year Growth Rates

• Create growth rate lines for:
  
  • Revenue growth: (This Year ÷ Last Year) − 1
    
  • Lead growth: (This Year ÷ Last Year) − 1
    
  • Revenue per Lead growth: (This Year ÷ Last Year) − 1
    
• This shows whether performance improved because revenue increased, lead volume changed, or lead quality improved.
  

14. Extend the Same Framework to Any Breakdown

• Once built, you can reuse the same structure to break Revenue per Lead down by:
  
  • Channel
    
  • Lead type (new vs existing)
    
  • Subtype
    
  • Location
    
  • Closed sale status
    
  • Gross profit instead of revenue
    
  • CSR, technician, or rep