September 7, 2007
New Google Analytics ROI Caculation Spreadsheet

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
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:
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.
Find The Secret To Solid, Sustainable Improvements To Your Website
Learn the insider secrets to making more money from your website than ever before – Guaranteed! Get started right away with the Google Analytics Training Course.




