Leads by Channel: Track Demand Mix
Month by Month in Excel

How to Build the Leads by Channel Report

1. Confirm the minimum data needed

• Make sure your dataset includes:
  • Lead Date (the date the lead came in)
  • Lead Source or Channel (where the lead came from)
• Optional fields can be used later for deeper slicing (campaign, location, zip, job type, customer type, SDR, etc.)

2. Extract month and year from the lead date

• Create a Month field from the Lead Date using the TEXT function.
• Use a format that returns Month-Year (for example, January 2025).
• If your lead date includes a timestamp, you can still reference that same date cell.

3. Convert your dataset into an Excel Table

• If your data is not already a table:
  • Click anywhere inside the dataset
  • Press Ctrl + T
  • Confirm “My table has headers”
• Name the table (example used in the video: TableLeads)
• This enables auto-fill formulas and structured references.

4. Create a unique list of Month-Year values in chronological order

• Use UNIQUE() on your Month-Year column to generate the list.
• Copy the results and paste values using Ctrl + Shift + V so you keep the list static.
• Add a Date label next to the list if you want a clean header.

5. Create a unique list of channels and place them across columns

• Build the channel headers using three functions together:
  • UNIQUE() to get the channel list
  • SORT() to alphabetize it
  • TRANSPOSE() to place the list across the top as column headers
• Copy and paste values using Ctrl + Shift + V so the headers do not change.

6. Calculate leads by channel for each month using COUNTIFS

• In the first data cell of your matrix, count leads where:
  • Channel equals the channel header
  • Month equals the month label
  • Year equals the year value (if you split year separately)
• Use COUNTIFS() since you are matching multiple criteria.
• This gives you the channel lead count for that month (the numerator).

7. Convert counts into percent of total leads for the month

• For the denominator, count total leads for that month and year only.
• Divide:
  • Channel count for month (numerator)
  • Total leads for month (denominator)
• Format as percent using Ctrl + Shift + 5.

8. Fix the formula with absolute references before filling across the grid

• Adjust references so:
  • The channel header stays fixed as you drag down
  • The month label stays fixed as you drag across
• Add dollar signs to lock rows or columns as needed.
• If dragging causes table references to shift unexpectedly, use copy and paste instead of dragging.

9. Add a Total Leads series for the secondary axis

• Create a Total Leads row or column by reusing the denominator formula you built earlier.
• This produces the total lead volume per month, which you will plot as a line.

10. Build the combo chart

• Highlight the full table (percent breakdown by channel plus Total Leads).
• Insert chart, then choose a combo layout:
  • Channel percentages as a Stacked Column
  • Total Leads as a Line
  • Set Total Leads to Secondary Axis
• This prevents the lead-count line from flattening the percentage view.

11. Reduce chart noise and improve readability

• Limit the chart range to fewer months (example: show only 2026) if the full timeline is too busy.
• Adjust series colors if needed.
• Add data labels to the Total Leads line (not the stacked columns) so the chart stays readable.
• Increase label font size and apply a background fill to labels if numbers are hard to see.
• Use hover tooltips to inspect percentage breakdowns without cluttering the visual.

12. Save the output as a reusable template

• Once built, you can reuse the same structure for other breakdowns by swapping the criteria field:
  • Job type, campaign, location, SDR, customer type, etc.