Using Google Analytics API to automate reports
If you work in an agency or even, in some cases, in-house, you will be all too familiar with how long reporting can take, but what if I said that you can cut that time by more than half? You’d think I’m mad, but wait, hear me out!
If you have a very Google Analytics-heavy reporting style/template, then this solution may be exactly what you’ve been looking. Most of the time taken when pulling data from Analytics is in having to navigate through different pages, waiting for all the data to load and constantly having to modify custom segments and filters; it all adds up.
Have you ever heard of an Application Program Interface, or API for short? No? Essentially, an API is a set of routines, protocols and tools used to build software. In short the definition is that it’s a way of one computer program communicating with another.
Why is this of interest to you? Well, Google Analytics allows you to access their Core Reporting API, meaning you can fetch all the data from your analytics profiles and display it in Excel in a way that you want.
Manipulating this data isn’t particularly easy, however, and it requires a lot of work through the Visual Basic Editor, which is where SuperMetrics comes in. They have done all the legwork for you by developing a tool that allows you to access the API parameters through their own functions by simply downloading their .xls document (however, keep in mind that you need to have macros enabled for this to work). Unfortunately, SuperMetrics costs money, (although there is a trial version) but if you have quite a few client reports that you need to do on a weekly/monthly basis, it pays for itself!
Once you open the SuperMetrics document, navigate to the “Analytics” sheet. If you’re not a developer, you might take one look at this spreadsheet and immediately become worried, but don’t be. When you begin to get the hang of it and understand what each of the sections are for, then it’s really easy to get to grips with.
Firstly, enter the token given to you by SuperMetrics into the blue box. Once you have done so, you will notice that it has automatically begun to populate your profile IDs, Segments (which you can also change to “goals” to show your goals), Pageviews on the first profile and it will automatically fill out the “A More Complex Data Query” section.
The part you want to be focusing on the most is the “A More Complex Data Query” section, as this is where you will primarily be fetching your data. Before I break down what each of the boxes highlighted in blue do, it is probably worth explaining that the information you see below is in an Excel array, so it is limited as to the data you can initially see, but you can expand this further. Learn how here.
Understanding the formula it is calling is quite an essential process as well, just in case you want to tinker with this manually and not display the data box above. It also helps to understand the data you are calling. The example formula you are given is:
Broken down into a more readable format, essentially what this formula entails is:
=Supermetrics(Token, Analytics Profile ID, Metrics, Start Date, End Date, Dimensions, Pivot Dimensions, Filters, Segments, Sorting Format, Settings, Max Results, Max Categories)
Now to get to the good stuff, I’ve written a step-by-step guide explaining each of the sections with a breakdown so you can understand what they mean and how to get the information you want displaying below.
1. Firstly, you will see a box in cell Z13 named Profile ID, which is where you enter the profile you wish to pull data from – you can find a list of these ID’s from cell M26 downwards. If you’re struggling to find the ID, you can do so by the URL of the analytics page, as the ID can be found within this. Here is an example
2. Navigating to the next cell, you will see it is labelled as Metrics and in the field; it has preselected “visits”. In this field, much like how it is displayed in Analytics, you can enter a variety of different frontline data such as newUsers, NewSessions, Users, Bounces, Goal1Completions and more. The tool I found most useful for this was the GA Query Explorer; it gives you all the available metrics you have for the profile ID you have selected in the format of ga:metric. All you need to do is copy what follows the ga: and place it in cell Z12.
3. Now you have to define your Start Date / End Date. This is fairly straightforward, but what you need to understand is that SuperMetrics doesn’t just show you one single statistic, which is the reason the results are displayed in an array.
Depending what you select as your dimensions, you can filter the results to display in days, weeks, months and even years within the specific date range you define. The format of the dates is as it displays in the example. However, instead of having the end date defined as “yesterday”, I much prefer to input a date format (e.g. 01/01/2015).
4. The Dimension section is also fairly easy to get to grips with; it’s the format in which you want the data displayed. For the majority of the time, I stick with “yearmonth”, as reporting tends to be monthly but, alternatively, you can choose “day”, “year” or “month”, but you have to adapt your selected date range accordingly to display the right data.
5. Pivot Dimensions is slightly trickier. Essentially, in the Query Explorer tool, this is displayed under the Dimensions tab. The way I understood what this section was trying to acquire data from was by relating the given dimensions by the query explorer with Analytics and trying to marry up what section it was referring to. What I found was that, a lot of the time, the ID given was similar to the titles in the left-hand side navigation.
For example, if I want to retrieve something from the Source / Medium, the ID would be sourcemedium, or if I wanted to see what devices under Mobile where getting a certain amount of traffic, I would input devicecategory. Once you understand the link between what the Query Explorer is showing you and Analytics, it’s much easier to understand the data you are retrieving. Here are a few common Pivot Dimensions I use:
- sourcemedium (which can also be just source or just medium)
You can also leave this section blank if you just want a top line figure for visits, sessions, goals or anything along those lines.
6. The next part of the equation is Filters. This is potentially the trickiest bit to understand as it isn’t displayed within the Query Explorer, but it can make data much easier to manipulate as you can manage what information you want to be displaying. As the name suggests, this is the same as the filter on Analytics; it essentially adds an extra layer to your Pivot Dimensions command.
Here is an example of how it works:
PivotDimension==Secondary dimensions displayed on the Analytics page once you’ve clicked your primary dimension
So, for example, if I wanted to fetch the results for Google Organic sessions that month:
Sourcemedium==google / organic
This would be the solution I was looking for, but keep in mind that you have to type the second part of the equation exactly as it is displayed in Analytics, or it will display an error.
7. Advanced Segments are also relatively straightforward. These are simply just the segments that you have set up yourselves or are already in Analytics. You can find the IDs to these segments in cell Q26 downwards, but if all of your segments aren’t being displayed or you’re having trouble with it, you can also find a full list on the Query Explorer.
- You only get two options for the Sort section: TRUE and FALSE. TRUE sorts the metric in descending order of the highest number, but most of the time, FALSE is the preferred option, as it just sorts them alphabetically, meaning the date range is in order, which I find much easier to gather information from.
- The Setting function is great for inputting special queries; all your variations of this are quite clearly displayed in the SuperMetrics example document. The ones I tend to use the most are SAMPLING_NOTE, which displays whether your data has been sampled or whether it’s un-sampled and AVOID_SAMPLING.
However, you have to be very careful with the latter, as it can take a long time to retrieve the data and slow down significantly or even crash your Excel, which can cause problems.
- Max Results is pretty self-explanatory, as it simply limits the amount of queries that you can run, keeping a limit on this is good for maintaining a fast spreadsheet.
- Max Categories is ever so slightly different. It defines how many of your Pivot Dimensions that you want to display. Again, keeping this as low as possible will maintain your spreadsheet calculation speed.
Once you’ve understood grabbing basic data, you can also combine Metrics, Pivot Dimensions and Filters by simply splitting them with “,”. This is slightly more advanced and there are far too many variations to give examples, but by adding various IDs together, you can collect more drilled-down data.
Having this data displayed in a format that you want means that you can copy and paste the figures straight into your reports. Alternatively, you can feature SuperMetrics in a hidden sheet so the data can get pulled through automatically through lookups, but you need to make sure you protect the sheet and ensure that no one can see your token!