Read this. Execute it.

GROW.

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

  • 9
  • 5.00
  • RATE THIS
    How to Determine the ROI of a PPC Campaign [FREE Template]
    5(100%)4votes

Ryan Stewart

I have an unhealthy obsession with being considered the world's BEST internet marketer. This blog is one of the channels I use to demonstrate that. I'm highly active on social media and love a good debate (bring it internet trolls). Click the icons below and fire away.

Ryan Stewart

I have an unhealthy obsession with being considered the world's BEST internet marketer. This blog is one of the channels I use to demonstrate that. I'm highly active on social media and love a good debate (bring it internet trolls). Click the icons below and fire away.

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. 

 

How to use the ROI 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

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

Enjoy!

9 Comments

  1. Dude, this is money.

    Thanks for going to the effort for sharing some of the recipes to your secret sauce.

  2. Wow nice share Ryan. Thanks for this great little tool :)

  3. 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

  4. 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

  5. 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.

  6. Oops please delete my comment, I understand now.

  7. Thank you Ryan for sharing your ppc results with us. Nice video with clear explanations!

  8. 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?

Leave a Reply

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