Google analytics has a great interface and there are numerous reports that you can generate as a business as well as an analytics expert. But are you getting exactly what you want to see? There are many businesses that have highly unique data analysis needs that are not met by the interface presented by Google analytics. Besides, instead of just viewing the data, they would like to manipulate it, and use it to generate their own graphs and reports.
This is where it makes sense to use Google analytics data in Microsoft Excel. Excel has a great repository of pre-defined functions that you can use to twist the analytics data in any form and create business intelligence magic.
Why would you use Google analytics with MS Excel?
As already mentioned above, you want to do much more than what the Google analytics interface currently provides. For instance:
- Use an interface that you are already familiar with
- You can combine data from multiple data sources to create highly complex reports and graphs
- Use Excel formulas, charts and pivot tables to get a deeper insight of the Google analytics data
- Share the worksheets with other Excel users without giving them access to your Google analytics account
- Build custom VBA queries and macros and save them within the worksheets for repeat use.
Using Google analytics API to connect it with Microsoft Excel
According to this article on Search Engine Land that aims to teach you how to make Google analytics interact with your Excel spreadsheet, in plain English, there are many plug-ins available that eliminate the various complexities involved in such ventures. Just like any web service these days, Google analytics also provides an API that enthusiastic software developers can use to fetch information from the service and present it to the client application. One of such plug-ins is, appropriately named, Excellent Analytics.
Quit Microsoft Excel if it is running right now.
Once you have downloaded the installation files you will need to run it. It will ask you to check whether .Net etc. is installed or not, but we assume that it is installed. Once the installation is done, you can launch Microsoft Excel. When you launch it for the first time after installing Excellent Analytics it warns you against installing an unauthorized and unverified application. It is safe to click “Install”.
Once installed, you get a new “Excellent Analytics” tab with its unique buttons.
It goes without saying that in order to use this plug-in you need to have a pre-existing Google analytics account. If you don’t have, then there is a long procedure of creating one, associating websites and creating various profiles. But if you already have a functional Google analytics account, you can enter your credentials by first clicking the “Account” (extreme left on your Excel window under the Excellent Analytics tab)
Once you have verified yourself you are automatically taken to the window that lets you create a query. It has various interface components that you can use to create a query. Once you have selected what you want to see, you can click “Execute”
A good thing about the “Execute” button is that if you have chosen insufficient parameters, a tooltip appears to tell you that.
Of course if you haven’t had much experience using various analytics interfaces it may seem a bit confusing but eventually you get used to creating queries that you really want.
Here is a nice blog post on SEOMoz that explains, step-by-step, how to create a functional query in Microsoft Excel using the Google analytics API plug-in Excellent Analytics.