Maybe you want a quick access snapshot of your referral or organic traffic. Maybe you want a custom KPI dashboard to send to your clients. Whatever the reason, pulling custom reports from Google Analytics just got easier.

Using Google Apps scripts, Google spreadsheets and Google Analytics API you can easily create automated reports and turn those reports into visualizations using charts, graphs, and gauges. Below are the steps:

Open a New Google Spreadsheet

First, you will need to navigate to Google docs and open a new spreadsheet. In the top navigation select tools and then click scripts gallery. A popup will then appear displaying all of the scripts in the Google’s library.

Next, use the search function to search the term ‘magic’ and install the first option titled ‘Google Analytics Report Automation’.  The script will then prompt you for authorization. Once you accept, the script will then be installed on the spreadsheet

Add Your Analytics Account

After you exit out of the scripts gallery, refresh your spreadsheet. After 10 seconds or so a Google Analytics menu item should appear in the horizontal navigation. 

Before you can run the script you must first authenticate your Analytics account. To do this you click the ‘tools’ menu item and then click ‘scripts editor’. This will display all of the scripts currently running on your sheet. To authenticate your GA account click ‘resources’ menu item and then ‘Use Google APIs’.

First, turn on Google Analytics API, then click the ‘Google APIs Console’ underneath to get your API key.

When clicking the link you will be redirected to a new tab and you will then select ‘Create New’ under the top drop down menu. Next, turn on the Google Analytics API and then click the ‘API Access’ tab on the left sidebar.

Your API key will be displayed which you will need to copy. After you have the key copied, paste it into the ‘API Key’ field in your previous tab. Once that is done you can exit out of that tab and your sheet is ready to roll.

Using the Script

To open a new report click the ‘Google Analytics’ menu item and then click ‘Create Core Report’. Another prompt window will open asking for access to your GA information. Once you confirm, you will have to follow the same steps again and create the core report.

Once you do that, new text will appear for you to input some different fields, metrics and dimensions. First you will need to grab the ids number for the account that you want to run the report for. To do this click the Google Analytics menu item and then ‘Find Profile ids’.

A pop up will be displayed with all of your accounts and web properties associated with your logged in email address or account. Once you navigate to the correct profile copy the ids number and paste it into the cell next to ids in your spreadsheet.

Now you are fully ready to run reports. As a sample of how the script works, run the report in the picture below. After you have the data entered select ‘Get Data’ from the Google Analytics menu item. A new sheet will will then be displayed. You can create a chart by highlighting the data set and the selecting ‘Insert’ then “Insert chart’ from the top menu.

There are tons of possibilities with the reporting and visualization tools. All of the dimension and metrics available for calls are displayed on the Google Developers site. You can also add other scripts and APIs to your spreadsheet to monitor other metrics like social counts, Yelp ratings, and correlations between online metrics and sales data. I will be posting my favorite dimensions and metric combos over the next couple of posts.

Do you have any secret weapon reports that you like to use. Please feel free to share in the comments below!