Cost per Lead (CPL) by Channel:
Find What’s Actually Working

Build a Cost per Lead (CPL) Report in Excel

1. Prepare the dataset and add Month and Year

• Confirm your dataset includes fields like lead date, channel, lead cost, revenue, gross margin %, and gross profit.
• If you only have a date field, create two helper columns:
  • Month: =TEXT([Lead Date],”mmmm”)
  • Year: =TEXT([Lead Date],”yyyy”)
• These two fields are used to build the time-based CPL table.

2. Create a unique list of Month and Year

• Use UNIQUE() on the Month/Year field you created (or on your existing Month/Year column).
• Copy the results and paste as values using Ctrl + Shift + V.

3. Create the marketing channels across the top

• Build a unique, alphabetized, horizontal list of marketing channels using three functions:
  • UNIQUE() to get the list of channels from your table (example: TableLeads[Channel])
  • SORT() to alphabetize the unique list
  • TRANSPOSE() to place channels across columns instead of down rows
• Copy and paste as values (Ctrl + Shift + V) and center-align the headers.

4. Calculate CPL for a single month, year, and channel

• CPL is calculated as:
  • Sum of Lead Cost for the channel and time period
  • Divided by the count of leads for the same channel and time period
• In the first CPL cell, start with a SUMIFS() that sums Lead Cost with criteria:
  • Month equals the Month row label
  • Year equals the Year row label
  • Channel equals the Channel column header
• Then divide that result by a COUNTIFS() using the same criteria:
  • Month equals the Month row label
  • Year equals the Year row label
  • Channel equals the Channel column header

5. Fix the references so you can copy across and down

• Update your references using dollar signs so the formula copies correctly:
  • Lock the Month column reference so it does not shift when copying left/right
  • Lock the Year column reference the same way
  • Lock the Channel row reference so it does not shift when copying up/down
• Use F4 to cycle through absolute and mixed references until:
  • Month and Year keep pointing to the correct row labels
  • Channel keeps pointing to the correct column header

6. Fill the entire CPL matrix

• Copy the corrected formula across all channels and down all Month/Year rows.
• Some channels may show zero or blanks if there was no lead cost (organic, referrals, email, direct).
• Remove or clear those zeros if you want a cleaner view.

7. Create an overall CPL by channel

• Copy any existing CPL formula and simplify it to remove the Month and Year criteria.
• Keep only the Channel criteria so the result becomes “overall CPL” for each channel across the full dataset.

8. Add conditional formatting to flag high CPL

• Select the CPL matrix and also select the overall CPL row/column.
• Go to Home → Conditional Formatting → Highlight Cells Rules → Greater Than
• Enter a threshold (example: 150) to highlight expensive lead sources.
• Test different thresholds (100, 150, etc.) to see which channels consistently exceed your target.

9. Add Ad Spend, Gross Profit, and Return on Ad Spend

• Create summary fields for each channel:
  • Ad Spend: use SUMIFS() to sum Lead Cost by Channel (no Month/Year for the total view)
  • Gross Profit: use SUMIFS() to sum Gross Profit by Channel
• Calculate Return on Ad Spend using gross profit:
  • Gross Profit ÷ Ad Spend
• Copy across channels to compare which sources produce the most gross profit per dollar spent.

10. Add a dynamic “Threshold” label without converting it to text

• Type the threshold number in a cell (example: 100).
• Format it so it displays as text but remains numeric:
  • Press Ctrl + 1 → Number → Custom
  • Use a custom format like: “Threshold: “0
• Now changing 50, 60, 75, 100 updates the threshold value used in your analysis while still behaving like a number.