Cost per Booked Appointment (CPBA)
by Lead Source in Excel

Cost per Booked Appointment by Lead Source by Month

1. Create Month and Year helper columns

• Add a Month column by converting the timestamp into a month label using TEXT.
• Add a Year column by converting the timestamp into a year label using TEXT.
• Keep both fields in your dataset so you can filter and group results by month and year.

2. Build the month list for the left side of the table

• Create a sequential list of months (and the associated year if your dataset spans multiple years).
• This becomes the row structure for your month-by-month CPBA table.

3. Generate the list of marketing channels

• Pull a unique list of lead sources using UNIQUE.
• Sort the list alphabetically using SORT.
• Move the list across the top of the table using Transpose (Paste Special → Transpose).
• Convert formulas into static values using Copy → Paste Values.

4. Calculate total spend by channel, month, and year

• Use SUMIFS to sum Lead Cost based on:
  • the selected lead source
  • the selected month
  • the selected year

5. Count booked appointments by channel, month, and year

• Use COUNTIFS to count rows where:
  • the lead source matches the column header
  • the month and year match the row values
  • Appointment Booked equals “Yes”

6. Compute CPBA for each cell

• Divide total spend by booked appointment count inside each month-channel cell.
• Format as currency using Excel number formatting.

7. Make the grid copy correctly across and down

• Use absolute and relative referencing (dollar-sign locking) so:
  • Month and Year references stay aligned as you copy across columns
  • Channel references stay aligned as you copy down rows
• Copy and paste across the grid instead of dragging if structured table references shift.

8. Remove non-spend channels and clean up zeros

• Identify channels that naturally have no cost (like referrals).
• Remove or blank those columns if they create misleading zero values.

9. Add an appointment volume table below the CPBA table

• Copy the grid below the main table.
• Convert it into a bookings-only view by keeping the COUNTIFS logic.
• This helps validate whether CPBA is based on a meaningful sample size.

10. Create an overall CPBA by channel

• Copy the CPBA logic into an “Overall” row.
• Remove month and year filtering so the result is calculated across the full dataset.
• Keep the lead source logic so it remains channel-specific.

11. Optional: Hide CPBA when the sample size is too small

• Use an IF rule to display a dash when bookings are below a threshold (example: fewer than 4).
• This prevents overreacting to small sample months.

12. Flag high CPBA values with conditional formatting

• Create a target CPBA cell (example: $150).
• Apply Conditional Formatting using a “Greater Than” rule to highlight cells above target.

13. Prevent dashes from triggering formatting rules

• Add a second Conditional Formatting rule using “Text that contains” to detect the dash.
• Apply a neutral format so only true numeric CPBA issues are emphasized.