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

  • 17
  • 4.43
  • Rating:
    How to Determine the ROI of a PPC Campaign [FREE Template]
    4.43(88.57%)7votes

Ryan Stewart

Marketing expert

I have an unhealthy obsession with being considered the world's BEST internet marketer. I'm highly active on social media and love a good debate.

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.

roi-of-pay-per-click

 

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.

 

adwords-data-roi

 

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

download-adwords-data

  • 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

 

insert-data

 

  • 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

 

vlookup

 

  • 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

 

roi-results

 

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)

final-roi

 

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 tempalte for yourself (Google Sheets file).

Enjoy!

About Ryan Stewart

I have an unhealthy obsession with being considered the world's BEST internet marketer. I'm highly active on social media and love a good debate.

17 Comments

  1. Great resource for PPC ROI stats. We use Adwords and figuring out your ROI is so key. We only market for local business owners so we do it differently but this resource is awesome.

    Thanks for sharing on Google+…that is how I found it. I look forward to learning more from you.

    Sincerely,

    Garret

    0
  2. Hi Ryan
    Happy new year and a great big thank you for sharing your ROI template. Not only is the template a fantastic tool but the video and explanation is just as valuable

    0
  3. Wonderful resource Ryan, but I’m a newbie. What does “Website lead rate” mean?

    Thanks and stay awesome. Been on a bit of a binge of your videos.

    0
  4. Hey Ryan, great tool. I’m trying it out now to get a grasp of the profitability of a future Adwords campaign.
    I’m wondering though why you’re using revenue per sale instead of net income per sale for the e-commerce part. Now it doesn’t factor in the costs of goods sold. Which can make a campaign seem profitable which in reality isn’t.
    Or am I missing something here?

    0
  5. where to download the template?

    i am unable to find any link or button in this page to download the excel template.

    Can you email me pls

    0

Leave a comment

Your email address will not be published. Required fields are marked *

subscribe

No sales pitches, just marketing tips.