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

Website conversion rate – the number of people that come to the website and make a purchase. This data is available in Google Analytics. Average revenue per sale – the average order value. 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. Monthly management fee – if you’re doing this for a clients, enter the fee you charge for monthly account management.

Services

Website conversion rate / lead rate – the number of people that come to the website and submit a form or call. Lead conversion rate – how many of those leads turn into paying customers / clients. Average revenue per sale – the average client value. 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. 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).

Sorry, the locker you asked for is either removed or never existed.

Enjoy!