How to get Google Analytics data in Excel
Every website owner and web analyst reaches a point where there’s a need to get Google Analytics data in Excel. While the Google Analytics web interface is very user friendly and easy to work with, it quickly becomes an obstacle when doing more advanced analysis. Very often, we also want to do the same analysis from month to month for reporting purposes. Or maybe we need to merge Google Analytics data with other data - and Excel then gives us more tools than the Google Analytics interface. In this post, I’ll teach you how to import data from Google Analytics directly into Excel - without needing to build and export reports in Google Analytics. You’ll need access to your Google Analytics account (duh!) and you’ll need Microsoft Excel 2007 or later running on a Windows PC.
Get a Google Analytics plugin for Excel
It’s possible to write your own Visual Basic scripts in Google Analytics, that’ll let you import data into Excel via the Google Analytics Core Reporting API. But that’s for developers, and the result will very likely not be very flexible, and it’ll probably be slow. The solution is to use a plugin (or add-in as Microsoft calls it) for Excel. A plugin is simply an application that will extend the functionalities in Excel. Specifically, I recommend that you start by downloading the free version of Analytics Edge. This should cover the most needs of most people. Just follow the installation wizard. Once installed, open up Excel - you’ll notice that you now have an extra tab in the ribbon called Analytics Edge. This is your brand new plugin, which we’ll use to get Google Analytics data in Excel:
Connect Excel to Google Analytics
Before you can begin importing data, you need to allow Excel (or Analytics Edge) to access your Google Analytics account. Do this by clicking on the Analytics Edge tab, expand the Free Google Analytics menu and select the Accounts option, which should result in this dialogue appearing:
Enter a description of your Google Account in the Reference name field and click on the Add Account button and follow the wizard. This will connect your Analytics Edge installation to your Google Analytics account. You can always remove the access by visiting the Apps Settings page on your Google account. You’re now ready to import data into Excel. Analytics Edge calls it to ‘query’ - because, underneath, you’re actually querying the Google Analytics API for data. So by using the Analytics Edge wizard you can create one or more queries. And you can create multiple queries within a single sheet or on different sheets. Some of the power in Analytics Edge is that it’s possible to use dynamic date ranges based on e.g. a date value in a cell. And you can update those queries automatically with a single click on a button. That’s great for reporting purposes or for doing the same analysis on multiple Analytics properties.
Create your first query
Let’s do a simple query: How many unique pageviews did our frontpage have over the last two months? And how were those divided by device category (i.e. desktop, tablet or mobile)? Now, make sure that you still have the Analytics Edge tab open on the Excel ribbon, and follow these steps:
- Select Analytics Reporting in the Free Google Analytics menu option
- In the Login dropdown, select the account you just created
- Find the view with your data in the Select View list and click it
- On the Fields tab, find and doubleclick the following fields in the list:
- Device Category
- Unique Pageviews
- On the Filters tab, do the following:
- Select Page in the list
- Select Exact Match in the Comparison and Value dropdown
- Enter “/” without the quotes in the input field below the dropdown
- Click Add
- On the Dates tab
- Select Yesterday in the End of dropdown
- Check the Duration field
- Enter “2” without the quotes in the input field
- Select Months in the dropdown
- Click the Finishbutton
In a matter of seconds, Analytics Edge will query the Google Analytics API and retrieve all the data you’ve requested. The result of the query described above should look something like this:
This is a major time saver. Specially for those cases where you have to update numbers and reports on a regular basis, and for those cases where you need to combine Analytics data in ways that the Analytics interface simply won’t let you.
Learn more about Google Analytics data in Excel
Now, this was just a simple introduction - and it’s an example with a great plugin for Excel. And I don’t even get a commission for recommending it! But trust me, it’s invaluable. Everybody should know how to get Google Analytics data in Excel. For one, it saves time, and secondly, the power of Excel makes it possible to do much better analysis than what is possible inside the Analytics interface. If you’d like to learn more about how to use Analytics Edge, then Mike Sullivan (who made the plugin) has a tonne of articles and free sample reports that are ready to use. Just go to his Analytics Edge basic add-in help site.