Blog Navigation

Blog Updates Via RSS

« Excluding Internal Traffic the Easy Way | Main | Funny Adwords Contest: Round 2 »

November 26, 2007

If you have used the ROI Calculator Spreadsheet tool that Shawn came out with before, you know that it takes several steps to get to the point where you can start entering in cost data and seeing that ROI. If your need for ROI data is only occasional, then this works great. In addition, if you click on the appropriate link below, you can download the latest version of this spreadsheet, which eliminates step 4 from Shawn's procedure.

Microsoft Excel 2007 Version
Microsoft Excel 97-2003 Version

If, however, you find yourself running through these steps again and again on a frequent basis with no shortcut in sight, there is hope!

Introducing...the ROI Calculation Macro.

Imagine...you log in to Google Analytics, download the file into Excel, click a button and you're done. All that remains is to fill in the appropriate cost data.

Now again, since the setup of this will take some time, this will be most useful for daily or weekly use. The instructions are for MS Excel 2007. If there are enough comments expressing interest in seeing a 97-2003 version of the instructions, we will come out with this in a later post.

You'll need to download this file (Note: You may need to right click and choose "Save link as".):

ROI Calculation Macro for MS Excel 2007

Make sure to save it in a location where you can find it again later.

STEP #1 Open Excel.

STEP #2 Click "View" tab.

STEP #3 In the "Window" box click "Unhide".
If you see PERSONAL in the Unhide dialog box, close Unhide dialog box and proceed to step 4. Do not unhide PERSONAL as this will cause problems later. If you don't see PERSONAL in the Unhide dialog box or if Unhide is unclickable, most likely you do not yet have a personal workbook to store macros in. Go ahead and close the Unhide dialog box.

Follow these instructions to create a personal workbook:

STEP #3a Click the "View" tab.

STEP #3b In the "Macros" box click "Macros", then choose "Record Macro". (Note: if the "Macro" window pops up instead, close it, and try clicking a little lower so that it gives you the option to choose "Record Macro".)

STEP #3c Under "Store macro in:" select "Personal Macro Workbook".

STEP #3d Click the "OK" button.

STEP #3e In the "Macros" box click "Macros", then choose "Stop Recording".

STEP #4 If you have a "Developer" tab, click on it and proceed to step 5. If there is no "Developer" tab, do the following:

STEP #4a Click the Office Button.

STEP #4b Click Excel Options.

STEP #4c Check the box for "Show Developer tab in the Ribbon".

STEP #4d Click the "OK" button.

STEP #5 In the "Code" box Click "Visual Basic".

STEP #6 Right click VBAProject(PERSONAL.XLSB). Note: Do not click VBAProject(Book1).

STEP #7 Select Import File.

STEP #8 Find and Select Conversion.bas (It will be where you saved it in the beginning) and click "Open".

STEP #9 Close Microsoft Visual Basic.

STEP #10 Click the Office Button.

STEP #11 Click Excel Options.

STEP #12 Click the "Customize" tab.

STEP #13 Under "Choose commands from:" select Macros.

STEP #14 Select "PERSONAL.XLSB!Conversion" and click the "Add > >" button.

STEP #15 Click "Modify".

STEP #16 Select a symbol for the macro button and click "OK".

STEP #17 Click "OK".

Congratulations! You have finished setting up your ROI Calculation Macro.

To Use this macro:

1. Follow steps 2-3 in Shawn's Procedure.

2. Click macro button located in the upper left on the quick access toolbar. It should look like the symbol that you chose in step 16.
3. Fill in the cost data. Excellent...

There you have it. If you found this macro useful or if you need any additional help implementing it, please let me know.

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:

@Aaron:

I can't take credit for this particular macro. Although I came up with the original spreadsheet, our new Google Analytics support team member Jeremy Aube came up with this macro and cleaned up my original workbook. He's an Excel wizard. Thanks for the compliments nonetheless, and thanks for reading!

November 27, 2007 2:42 PM

Jeremy Aube, Google Analytics Support Tech said:

@ Mariusz: I currently don't have access to MS Excel 2003 or else I would have posted instructions for that as well. I believe in 2003 you go to tools::macro::record new macro. Make sure you record it in a personal workbook (I'm assuming it will still give you that option in 2003). Then stop recording. The go to tools::macro::visual basic editor. You should be able to follow the excel 2007 instructions from there till you get to setting up the macro button. Then click here for instructions on setting up a toolbar button for the macro in 2003 (You should skip the first 5 steps).

March 3, 2008 9:47 AM

Post Your Comments

© Copyright 2009 ROI Revolution. All Rights Reserved.