In our marketing analytics projects we are often faced with the question how to automate access to Google Analytics and provide more interactive and easier to use reporting options. Normally the approach is an integration via the Google API that typically involves developing more or less complex ETL processes into the desired data model architecture (e.g. Jedox, MS Analysis Services etc.)
In this article I will describe a very easy and simple process that involves a great free add-in for Excel and using PowerPivot an analysis component that is part of Excel since version 2010.
As a first step we download the free “Excellent Analytics” add-in from http://excellentanalytics.com/. Once this is installed you will have a new Ribbon in Excel:
On that Ribbon just click on account and login with your Google Analytics account. Following that you can just create a new query selecting the dimensions, metrics, related filters and date range:
After you have selected the measures of your query just click “Execute” which will paste the results in your spreadsheet:
Now select the range that includes the entire table (including the column header BUT NOT the query definition on top of it. Now click on “Add to Data Model” in the PowerPivot Ribbon (if that is not there enable it in File>Options>Add-in):
(You might have to change the range manually as Excel seems to want to include the query e.g. Row 1 in the above example as well.)
This step has defined your PowerPivot model. Now you can either create an interactive dashboard from the PowerPivot Manager:
And create your desired dashboard analysis elements by dragging dimensions and measures into the respective pivotcharts:
Or if you have Excel 2013 create a PowerView report that enables you to integrate easily georeferenced data:
By clicking on the query definition at the top of your query results and clicking on “Update Query” in the Excellent Analytics Ribbon you can at any time update the query definition. The update results will immediately reflect on your dashboards.
I hope you find this approachhelpful which really enables you to create interactive analytics just using standard Excel functionality. Please feel free to contact me for any questions. If you like to know more about advanced business intelligence with Excel check our related seminar series: Advanced BI with Excel.