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).
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:
- An active Adwords Account
- A Google Analytics account with eCommerce tracking setup and working
- Your Adwords and Google Analytics accounts are connected
- This Excel template
Setting Up the Keyword Revenue Report
Open up your Google Drive and create a new spreadsheet.
Under the Tools menu select Scrip Gallery.
Type “google analytics” into the search field and find the Google Analytics Report Automation (magic) plugin. Then select install.
Authorize the plugin.
Refresh the page in your browser and the Google Analytics tab should be available.
Under the tools menu select Script Editor.
Under Resources on the Script Editor Menu select: Use Google APIs…
In the Google APIs Services window turn on Google Analytics API then select: Google APIs Console.
Select “API Access” in the blue menu on the left.
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.
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).
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”.
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.
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.
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.