How to Calculate ROI by Lead Source in Excel

How to Calculate ROI by Lead Source in Excel

This tutorial explains how to calculate Return on Investment (ROI) in Excel by lead source, helping you identify which channels generate the highest profit relative to their acquisition cost.

1. Create a Unique List of Lead Sources

• Use the UNIQUE() function on your Lead Source column to remove duplicates.
  
• If a zero appears due to blanks, add a period (.) inside the formula to remove it dynamically.
  
• Copy the results and paste values using Ctrl + Shift + V to remove the formula.
  
• Optionally sort alphabetically.
• This creates a clean list of unique lead sources like Facebook Ads, Referrals, Email Campaigns, etc.
  

2. Calculate Total Operating Income by Lead Source

Use the SUMIFS() function to calculate total operating income per lead source.
     Example:
     =SUMIFS(OperatingIncomeRange, LeadSourceRange, LeadSource)

• Copy the results and paste them as values.
  
• Format as currency using Ctrl + Shift + 4.
• This step gives you the total operating income generated by each marketing channel.
  

3. Calculate Total Acquisition Cost

Repeat the same process, but reference your Acquisition Cost column instead (e.g., Column S).
     Example:
     =SUMIFS(AcquisitionCostRange, LeadSourceRange, LeadSource)

• Format the results as currency and align them using Alt + H + A + C for a clean layout.
• You now have the two main components needed for your ROI formula.
  

4. Calculate ROI for Each Lead Source

In a new column, divide Operating Income by Acquisition Cost.
     Example:
     =OperatingIncome ÷ AcquisitionCost

• Format as a percentage using Ctrl + Shift + 5.
• This shows the return on every dollar spent for each lead source. For example, an ROI of 350% means you earn $3.50 for every $1 spent.
  

5. Create an ROI Chart by Lead Source

• Select your Lead Source column and ROI column.
  
• Go to Insert → Recommended Charts → Bar Chart.
  
• Add Data Labels by clicking the chart, selecting the “+” icon, and enabling labels.
  
• Resize labels or increase font size for visibility.
  
• To sort highest ROI at the top, sort the ROI column from smallest to largest before charting.
• This visual immediately shows which lead sources drive the best performance.
  

6. Format and Customize the Chart

• Title the chart “ROI by Lead Source.”
  
• Apply consistent colors (e.g., blue bars, gray text).
  
• Optionally remove gridlines and adjust bar spacing for a cleaner look.
• You can now compare ROI values across all acquisition channels at a glance.
  

7. Interpret the Results

• Identify which channels yield the highest ROI (e.g., Referrals or SEO).
  
• Understand how much operating income is generated per dollar of acquisition cost.
  
• Use this data to optimize marketing spend by focusing on high-performing sources.
• Example: Referrals may deliver the best ROI, but also factor in churn and customer lifetime value (LTV) for a complete view.
  

8. Expand the Analysis

• Add monthly breakdowns to track ROI trends over time.
  
• Include additional metrics such as Lifetime Value (LTV) or Renewal Rates.
  
• Combine this ROI analysis with your Acquisition Cost and Margin models for deeper insights.
  

Result

You now have an Excel model that calculates and visualizes ROI by lead source, showing where each marketing dollar produces the greatest return. This approach helps you identify the most profitable channels and guides smarter marketing investments.