Blog Navigation

Blog Updates Via RSS

« Why Google Analytics is critical to the Pay Per Click (PPC) process | Main | Adwords For Dummies: Who's the Dummy Now? »

September 7, 2007

Due to the new Google Analytics interface, some changes had to be made to our old ROI Calculator Spreadsheet tool. Unfortunately, there's now an extra step involved before you can use the tool to figure out your ROI (See Step 4 below). The good news, however, is that the new method is a lot more versatile, and you can use it for many kinds of reports, including your non-AdWords campaign and keyword reports.

Read on to learn more:

You can find the files by clicking on the appropriate link below:

Microsoft Excel 2007 Version
Microsoft Excel 97-2003 Version

Here's a step-by-step guide (complete with pictures) detailing how you can use the tool to calculate your ROI for non-AdWords traffic sources:

Step 1: Download the appropriate file above.

Step 2: Find the right report to use. In my example I'll use the All Traffic Sources report.

Click Here to see the report I used

Step 3: Export the report as a CSV file. You'll find the Export button at the top of the report, like so:

Select 'CSV' as the file type:

Open or save the file:

Here's what the report should look like

Step 4: Delete any data before the data table information. In my example, that means deleting everything before the 'Source/Medium' line. You can do this by selecting the rows you wish to delete, right-clicking, and then selecting Delete.

Click here to see what this looks like

Step 5: Copy the modified data from the exported spreadsheet. You can do this by right-clicking in the top left corner and selecting Copy

Click here to see an example

Step 6: Open up the ROI Calculator Tool

Step 7: Paste the data into the "GA_Data" tab in the tool. You can see the tab on the bottom of the workbook. Again, you can paste by right clicking on the top-left corner and selecting Paste:

Click here for an example

Step 8: Click on the "ROI Calculator" tab in the tool, and get your ROI calculated!

First, click on the ROI tab on the bottom, and you should see something like this.

Then, enter in cost data for a value, and the ROI will be calculated, like this.

Here is what the final report looks like:

I know there are a number of steps here, but it can be worth it if you're trying to calculate the ROI for thousands of keywords. Hopefully Google will hear our pleas and integrate this in some way to make our lives easier. Please let me know if you have any comments/suggestions about the tool.

Interested in learning more about Google Analytics?
Attend our LIVE Google Analytics Seminars for Success training in Atlanta, GA Wednesday, April 14th, 2010 and Thursday, April 15th, 2010 or get the latest tips and tricks sent to you via our free, twice-monthly Google Analytics newsletter.

Comments

Shawn Purtell, Google Analytics Support Tech said:

@JS: The tool is indeed designed to work with revenue data (which suggests ecommerce), but there are a few ways to close the loop associated with lead generation models. They're all pretty advanced, and would take entire posts to explain properly. None of the methods, however, will allow you to use this particular tool.

September 19, 2007 9:42 AM

Shawn Purtell, Google Analytics Support Tech said:

@ Nicole,

I agree, it's crude - but that's why we've come up with the macro that can be found Here.

I'm be no means an Excel wizard, and am pressed to find time right now, but Jeremy is extremely proficient, and has made this much nicer. I think you'd find his macro helpful.

Thanks for the comments!

December 10, 2007 11:01 AM

Shawn Purtell, Google Analytics Support Tech said:

@Mariusz:

These instructions should work for all versions from '97-2007, you just need to make sure you've downloaded the right spreadsheet. One is for '97-2003, and one is for 2007 only. Please let me know if you can't get this to work.

January 23, 2008 9:08 AM

Post Your Comments

© Copyright 2009 ROI Revolution. All Rights Reserved.