This post is for all you eCommerce marketing managers or business owners out there who are constantly looking for deeper insight into how your online advertising is performing. What I’m going to show you is how to easily pull data from your Google Analytics account into a custom Google spreadsheet.

This report will not only show you the revenue generated by a specific product but the keywords that lead users to that product and the resulting revenue.

Our report goes beyond just creating an auto updated dashboard with the Google Analytics API. It allows us to side step some limitations of the UI in creating custom reports (i.e. the number of dimensions you can have available at a time in a single tab).

finished keyword revenue report

From this report we can see that our modified broad match keyword +buy +fishing +tackle has driven sales of 20,000 Green Spinner Baits for a total revenue of $159,800.

 

What you’ll need in order for this report to be accurate and functional:

Setting Up the Keyword Revenue Report

Open up your Google Drive and create a new spreadsheet.

create google spreadhseet

 

Under the Tools menu select Scrip Gallery.

script gallery

 

Type “google analytics” into the search field and find the Google Analytics Report Automation (magic) plugin. Then select install.

 

install analytics magic plugin

 

Authorize the plugin.

authorize google doc plugin

Refresh the page in your browser and the Google Analytics tab should be available.

Under the tools menu select Script Editor.

Google Docs Tools Menu to Script Editor

Under Resources on the Script Editor Menu select: Use Google APIs…

Use Google APIs

 

In the Google APIs Services window turn on Google Analytics API then select: Google APIs Console.

Google Analytics API On

 

Navigate to Google API Console

 

Select “API Access” in the blue menu on the left.

API Console API Access

 

Copy the API key from the API Access menu and paste it into the API Key field in the Google APIs Services window and press OK.

Google API Access Key

 

Enter API Access Key

 

Then under Google Analytics on the document’s menu select “Find Profile / ids, press OK, then “Grant Access” to authorize the script access your Google Analytics data. (You’ll need to select “Find Profile / ids” again after you grant access).

Find Profile and IDS

Then select the desired account, web property, and profile. You will then need to enter the ids number into the “gaconfig” tab in the cell next to “ids”.

Enter Profile IDS

 

The next step is to enter the desired date range for the data you’ll want to view. The date format to enter is month/day/year (i.e. 5/6/2013). For this example we’ll pull data for the last 30 days.

Last 30 Days Data

 

Under Google Analytics on the Main Menu select: Get Data. A status window will appear that will show whether or not the script executed successfully. Exit out of this window and navigate to the Product Revenue by Keyword tab.

Get PPC Data

Script Write Success

Final PPC Report Results

If everything worked as it should you should see your keyword and product revenue data.

What to do with this keyword data

With the stats in these columns you’ll be able to tell what product sales and revenue are driven by which keywords. This will give you a good idea of how effective your keywords are. It will also give you a good indication to whether or not the keywords are driving revenue for the correct products and an indication to whether or not these keywords are driving traffic to the right product pages.