Our Online Dialogue colleague and analytics & optimization expert Drs. Jules Stuifbergen has for Webanalists.com wrote an article on how to automatically read Google Analytics data into Google Docs.

As a web analyst, you deal with data and numbers every day. A lot of our work is automated, of course, but if you want the data from your analytics in an Excel file, for many people this is still manual cutting and pasting.

It can be much easier! Namely, by linking your Google Analytics to Google Docs. In this part, I'll explain step by step how to make this link. In the next part, I'll show what cool things you can do with it, such as analyzing and visualizing an A/B test.

What are we going to do: a summary

• installing the Google Analytics report automation (magic)  script
• linking your Google Analytics account to a Google spreadsheet
• retrieve your Google Analytics ID automatically
• Build and run an initial query

What do you need.

All you need is a Google Analytics account and a Google spreadsheet. The whole process takes about 10-20 minutes. Getting Started!

Step 1: Create an API key

Log in to your Google account and visit the Google APIs console. Choose there create new project and give it a name.

Click on Create project and your project is created. To get an API key, you must first enter the menu Services Activate the Google Analytics API. Click on Services, and slide the appropriate slider to On.

Once you've done that, the menu item is API access see your key. Select your key and copy it. You will need it later to paste into your spreadsheet.

Step 2: Create a new spreadsheet and install the script

Surf to Google Drive, and create a new spreadsheet. Under the menu Tools (or Additional, if you have the language set to Dutch), you will find the option Script Gallery. Open the gallery and search for report automation. 

If all goes well, you will find this script. Click Install and a window appears asking for authorization. Click Authorize.

Step 3: Enter the API key into the script, and test if it works

Click again on the Tools (or Additional) menu and go to the script editor. A new window opens with the script in it.

Under the menu item Resources (in Dutch: Resources), choose Use Google APIs. A popup will appear. In this screen you must 2 things to do: The Google Analytics API to On slide, and paste your key (see above, step 1) into the window.

Click OK, and importantly: save your script. (File > Save).

Now go back to your spreadsheet and reload the page. If all goes well, an additional menu item entitled Google Analytics.

To test the script, we are going to look up your ID. In the Google Analytics menu, choose the option Find profile/ids. Another authorization window may appear, requiring you to give your spreadsheet access to read your Google Analytics account. Do this.

Now you can run the script truly turning. Via Find profile/ids a screen appears where you can find your Google Analytics id. Choose the profile you want to get data from.

Copy the piece ga:nnnn. You have to paste that piece that way.

So... the link is made, the script works. Time to do the first query. Are you ready?

Step 4: Create your first query and query the data

We're almost there.... In the Google Analytics menu, choose the option Create Core report. When you do this, a tab called gaconfig. Here will be the queries you can modify.

The first column contains the variables, the second column contains the values. Fill them in.

Paste your ID into the ids line so that the correct profile is used. The rest of the values determine the type of data being requested. In this case, we will create a simple report with visitors and pageviews for the last 30 days.

The fields I fill in are metrics, dimensions, last-n-days, max-results, and the name of the output tab. This tab is created if it does not already exist.

Click in the Google Analytics menu on Get Data And the moment is there ... the data is retrieved!

Click to the tab (if this is a new tab, just click back and forth a few times) and if all goes well you will see your information there.

Congratulations, now you can get started!

Doing more?

In this example, we queried data that is also already in Google Analytics itself as a graph. Not very exciting. Fortunately, much more is possible via this API. Want to get started yourself? Then take a look at the Dimensions and Metrics overview of the API, and live it up.

In part two of this blog post, I will provide some more exciting examples, creating beautiful graphs from multiple queries and dynamic segments. Stay tuned!

Of course, you may ask your questions and show your own work in the comments. Good luck!

Originally posted on December 7, 2012 at Webanalists.com