How to Determine the ROI of a PPC Campaign [FREE Template]

Thanksgiving is my favorite holiday so I’m really feeling the love today.

I’m going to give you the exact data modeling template I use to determine the return on investment (ROI) of paid search (PPC) campaigns.

All you need to do is enter a few cells of information and the entire thing populates for you.

It’s an incredibly powerful tool to pitch clients or determine the profitability of keywords for your own internal campaigns.

Using the model…

It’s a 4 tab Excel file that calculates itself with a little input on your end.

I’ve explained how to use it via video (below) and screenshot guide (below).

Tab 1: Enter your information

We need to put in some quick information points to get a better picture of the ROI.

Tab 2: Dump in your AdWords data 

• Navigate to Google’s Keyword Planner
• Enter between 5 – 10 planned keywords into the search field
• Click search, navigate to “Keyword Ideas” tab
• We want to make sure there’s sufficient search volume for the keywords we’re targeting
• If there isn’t use the keyword suggestions, add them to the search field, search again
• Once you’ve found sufficient volume, click the “Download” button

Tab 2: Dump in your AdWords data 

• Navigate to Google’s Keyword Planner
• Enter between 5 – 10 planned keywords into the search field
• Click search, navigate to “Keyword Ideas” tab
• We want to make sure there’s sufficient search volume for the keywords we’re targeting
• If there isn’t use the keyword suggestions, add them to the search field, search again
• Once you’ve found sufficient volume, click the “Download” button

We need to differentiate the difference between traffic for eCommerce and lead generations sites. This models classifies each as the following:

• eCommerce – any website that sells directly on the site (clothing, retailers, etc)
• Services – any website that sells based on leads generated (attorneys, agencies, real estate, etc).

eCommerce

1. Website conversion rate – the number of people that come to the website and make a purchase. This data is available in Google Analytics.
2. Average revenue per sale – the average order value.
3. Projected ad click through rate (CTR) – you will have to guesstimate this, it gets easier with experience. Generally, between .5 – 5% depending on the competition of the keywords.
4. Monthly management fee – if you’re doing this for a clients, enter the fee you charge for monthly account management.

Services

1. Website conversion rate / lead rate – the number of people that come to the website and submit a form or call.
2. Lead conversion rate – how many of those leads turn into paying customers / clients.
3. Average revenue per sale – the average client value.
4. Projected ad click through rate (CTR) – you will have to guesstimate this, it gets easier with experience. Generally, between .5 – 5% depending on the competition of the keywords.
5. Monthly management fee – if you’re doing this for a clients, enter the fee you charge for monthly account management.

• For ‘File format’, select “AdWords Editor CSV”
• For ‘Destination’, select “Save to Google Drive”
• Click Download

• Open up the downloaded data
• Click the upper right hand corner to select all
• Copy all of the cells
• Navigate over to my template
• In the tab that says “Paste AdWords Export Here”, click the upper right hand corner to select all
• Paste in the data from AdWords

• Open up the downloaded data
• Click the upper right hand corner to select all
• Copy all of the cells
• Navigate over to my template
• In the tab that says “Paste AdWords Export Here”, click the upper right hand corner to select all
• Paste in the data from AdWords
• That’s it! Move on to the next tab

Tab 3: Analyze your results in auto calculator

All of the work is done – the Spreadsheet auto calculates everything else!

Let me run you through it so you have an understand of what you’re looking at.

• The data in the top chart pulls through using a =VLOOKUP formula
• This tells the model to look up “seed keywords” only (i.e. the ones you entered, NOT suggested ones) and pull through their associated search volume and CPC
• To add more keywords, simply insert lines and drag the formula down

• Under that chart, the ROI calculations begin
• Projected CTR x total keyword search volume = project number of traffic
• Projected traffic x website lead rate = number of leads we can expect
• Expected leads x lead close rate = projected sales
• Projected sales x cost per sale = total projected revenue
• Projected revenue – costs = net revenue

Tab 4: A picture of projected ROI

I added this tab to clean up the presentation of the ROI, as most clients don’t give a shit about all that stuff, they just want to see the results.

ROI is calculated as: (Costs – Revenue) / (Costs)

Wrapping it up

It seems like a lot of work, but honestly, I’m handing you the keys to a Maserti here. I did all th eheavy leg work for you, all you have to do is change a few cells and you’ve got an accurate model to predict the ROI of a paid search campaign.

Click the button above to grab the template for yourself (Google Sheets file).

Enjoy!