Customer Acquisition Cost (CAC) in Excel:
Track It by Month and Marketing Channel

How to Build Customer Acquisition Cost by Month and Channel

1. Pull the Month from the Date

• Open the data tab and locate the date field (example shown: cell B2).
  
• If the dataset does not already include a month column, create one using:
  
  • =TEXT(B2,”mmmm”)
    
• Fill the formula down to generate the month name for every row.
  

2. Build the Month Table Layout

• In a new area, type a header like Month.
  
• List January and drag down through December.
  
• Add three column headers next to the months:
  
  • Spend
    
  • Jobs Sold
    
  • Customer Acquisition Cost
    

3. Sum Monthly Spend with SUMIFS

• In the Spend column, use SUMIFS to sum ad spend for each month:
  
  • Sum range: the Spend column in the dataset (example referenced in the video as Column G)
    
  • Criteria range: the Month column you created (or existing month field)
    
  • Criteria: the month name in your table (example referenced as B3)
    
• Format as currency using Ctrl + Shift + 4.
  
• Double click to fill down through December.
  

4. Sum Monthly Jobs Sold with SUMIFS

• In the Jobs Sold column, repeat the same SUMIFS structure:
  
  • Sum range: the Jobs Sold column in the dataset (example referenced as Column I)
    
  • Criteria range: Month
    
  • Criteria: the month name in the table
    
• Fill down for all months.
  

5. Calculate CAC by Month

• In the Customer Acquisition Cost column, divide:
  
  • Spend ÷ Jobs Sold
    
• Fill down to calculate CAC for every month.
  
• Optional: apply conditional formatting to visualize higher CAC:
  
  • Home → Conditional Formatting → Color Scales
    
  • Choose a red scale where higher values stand out.
    

6. Create a Line Chart for CAC by Month

• Select the Month column, then hold Ctrl and select the CAC column.
  
• Insert a line chart:
  
  • Insert → Recommended Charts
    
  • Choose the line chart option
    
• Rename the chart title to something like CAC by Month.
  
• Optional chart formatting shown in the video:
  
  • Remove decimals (either from the ribbon or Format Axis → Number → set decimals to 0)
    
  • Add data labels: click chart → plus icon → Data Labels
    

7. Copy the Table Structure for Marketing Channel

• Copy the month table and paste it below or beside it to reuse the structure.
  
• Replace the Month list with a Marketing Channel list.
  

8. Create a Unique List of Marketing Channels

• Use the UNIQUE() function on the Marketing Channel column in the dataset.
  
• Quick-select the full column range using:
  
  • Click first value → Ctrl + Shift + Down Arrow
    
• Press Enter to generate the unique list.
  
• Copy and paste values (Ctrl + C, then Ctrl + Shift + V) to remove the formula.
  
• Sort the channels alphabetically if desired.
  

9. Sum Spend by Marketing Channel

• Use the same SUMIFS formula logic, but change the criteria:
  
  • Instead of criteria range = Month, use criteria range = Marketing Channel
    
  • Sum range still = Spend
    
• If you accidentally reference the wrong column, correct the criteria range (the video shows fixing a wrong column reference).

10. Sum Jobs Sold by Marketing Channel

• Use SUMIFS for Jobs Sold by channel:
  
  • Sum range must be Jobs Sold (not a count of rows)
    
• The video highlights a common mistake:
  
  • Counting rows underestimates jobs when a channel produced multiple jobs
    
  • Fix by summing the Jobs Sold column instead of using a count-style approach

11. Calculate CAC by Marketing Channel

• Divide:
  
  • Spend ÷ Jobs Sold
    
• Fill down for all marketing channels.
  
• You can optionally apply conditional formatting to CAC values like you did for the monthly table.

12. Create a Column Chart for CAC by Channel

• Highlight Marketing Channel and Customer Acquisition Cost.
  
• Insert a bar/column chart:
  
  • Insert → Recommended Charts, or choose Clustered Bar
    
• Sort channels to display smallest to largest CAC:
  
  • In bar charts, sorting can feel backwards
    
  • You may need to sort the data largest to smallest to get the visual order you want
    
• If sorting causes formulas to behave oddly, re-check the filled formulas and drag down again to confirm values.

13. Adjust the Chart Axis for Better Readability

• Click the axis values and format the axis bounds.
  
• Example shown:
  
  • Set a max like 300 to compress the view and make differences easier to compare.
    
• Add labels if desired so each bar shows its CAC value.