“Our ability to use APIs, scrapers, multiple tools is going to be super critical.” — Avinash Kaushik
Have you ever wanted to combine Google Analytics data into an existing spreadsheet? Or create a dashboard outside of GA that updated automatically? Or distribute the information to a wide audience without the need to email pdf files?
Recently Google Analytics made it easier to automate Google Analytics reporting using Google Apps Script — you can pull information about your website, put it into a Google Spreadsheet and then publish the information.
This integration lets you export data for further analysis, create dashboards, or share simple prototypes.
Nick Mihailovski from Google has recorded a video to show you how. He demonstrates the steps to pull data from Google Analytics, put it into a Google Spreadsheet, then pull it into a simple website. He covers the details thoroughly in his demo, so I’ll simply summarize the sequence here.
Install and configure “Magic Script”
First you need to configure your environment so you can request data from Google Spreadsheets to Google Analytics. To do that you simply create a new Google Spreadsheet and install the Magic Script from the Google Script Gallery.
Once the script is installed and configured, you’ll see a new menu item called “Google Analytics” in your spreadsheet. You’ll also see at the bottom a spreadsheet tab called “gaconfig.”
You are now ready to use the Google Spreadsheet to start requesting information from one of your Google Analytics Profiles.
Create your first report
The “gaconfig tab” is like a template that contains all the parameters to make a request to Google Analytics.
To create a report, go to the new Google Analytics menu item and select “Create Core Report.” In the gaconfig tab you will see a list of the parameters you can use to populate your report. Some of these parameters are required, many are optional.
You can find the Analytics Profile ID for the “ids” field by selecting “Find Profile / ids” from the Google Analytics menu.
In the table below is a brief description of the parameters. For more information, go to the Core Reporting API Reference Guide.
Once you have entered the parameter fields, select “Get Data” from the Google Analytics menu on your spreadsheet. The Magic script will run, fetch the data from GA, and populate your report in a new sheet.
Then, as Nick says, “boom, there’s all your data.”
Automate your report
Now it’s easy to visualize your data by creating a chart in the spreadsheet.
Nick then shows you how to set triggers for your reports so that the data is refreshed automatically. Your data will now always be fresh.
Build a dashboard
Finally, you can use Google Sites to create a simple website dashboard to share your reports and charts with others.
Go to Google Sites and create a new project. Once you are in your new website, click on the pencil icon at the top of the page to edit the page. Then select Insert from the menu and insert a chart. Google Sites will ask you for the name of the spreadsheet you want to use. It will display the charts in that spreadsheet; select the one you want and Google Sites will display it in the website page.
Since it is a website, you can add as many pages as you want, put other information, pictures, explanations on the pages, and it all becomes part of this new website.
The charts you inserted with the Google Analytics data will update automatically. So whoever you give permission to view the website will always see current information in the charts.
Ok, I don’t know about you, but I think this is pretty amazing. I can think of lots of situations where it will be handy to pull information out of GA, mix it with additional information and numerical data, and then share it with others. No one else has to have access to GA or know how to use it.