Recently, I’ve had the opportunity to use the Google Analytics Core Reporting API, Nick Mihailovsk’s Google Analytics Report Automation (Magic) script, and Google Spreadsheets to automate a few of our clients’ monthly reports. Wow. What a time saver. Sure, the initial setup and configuration of the queries takes a little time, but it’s time that is recouped instantly when you go to run the report for the first time, and subsequent times, thereafter.
In this blog tutorial, I’m going to cover the configuration of the Google Analytics Core API Report Automation Script, and how to run a query using it. In my next blog post, I’ll show you different tricks to clean up the data and automate your Google Analytics reports.
Configuring Google Analytics Core API Report Automation (Magic) Script
If you’re new to words like API, scripts and spreadsheets, fear not! If you can write an email, you can use the Google Analytics Core Reporting API, Google Analytics Report Automation (Magic) script and Google Spreadsheets.
Let’s start with Google Spreadsheets as that’s where we are going to be pulling in our Google Analytics data. Start by creating a new spreadsheet and going to Tools > Script Gallery. From here you’ll search for the term “Google Analytics” and look for the Google Analytics Report Automation (Magic) script, and install.
EDIT: The way of creating a new api project has changed and I’ve updated the post to reflect those changes.
Once you’ve install the script, you’ll want to configure it. To do this, let’s go back up to the Tools > Script Editor, and once the Script Editor has loaded, let’s go to Resources >
Use Google APIs Advanced Google Services (Experimental). Scroll down and find the Google Analytics API; turn it on.
Before you click OK and continue, click the
Google APIs Console Google Developers Console link. This will direct you to the Google APIs Dashboard where you can create a new project and generate a new API key. Let’s create a new project by clicking the APIs & auth dropdown and selecting Create APIs item. Once you selected a name for your project From here, you’ll be directed to the Google API services page, and from here, Toggle on the Google Analytics API. Once you’ve toggled it on, click on the API Access item in the left column and copy the API key.
Go back to the Google APIs Services window and paste the API key into the API Key field and click OK. *Note: you can close the Google Script Editor and Google API console windows. Viola! You’re now ready to use the Google Core Reporting API.
Building your Google Analytics Core API Report
You’ve successfully installed and configured the Google Analytics (Magic) Script. Now what?
Let’s get some data. I’m assuming you’re back at the Google Spreadsheet where you started. Refresh your browser or open a new worksheet. For some reason this is needed to trigger the new Google Analytics menu item. You should now see the new menu item: Google Analytics. Schweet, there it is.
Before I create a new report, I create a master sheet. This is where I will reference the different report (queries) sheets and format the report so it’s not just raw data. The master sheet is also where I setup the data range from where I want to pull the data from in GA (I’ll come back to this point).
Account ID (*required)
So let’s click on the new Google Analytics menu and ‘Create Core Report’. This creates your core report underneath the gaconfig spreadsheet. The first thing you’re going to need is an account ID. This is how the API knows which account to grab data from. Luckily, the (Magic) script comes with a ‘Find Profile/IDs’ option underneath the Google Analytics menu. Once your ID is in place, you’re ready to start fleshing out the rest of the report.
Start Date/End Date (*required)
PRO TIP: For both start-date and end-date, I reference the date range cells on the master sheet. (Thanks Mike!) This will save you from having to go to the gaconfig sheet and editing each query start-date and end-date individually. And believe me, once you have 30 queries, you’ll want it to be as easy as possible to edit your data.
For metrics and dimension, I found the Google Analytics Dimensions & Reference guide an absolute necessity. For this example, let’s start with Visits, or ga:visitors, in Google API terms. Just about every metric in Google Analytics has a corresponding API metric, so reference the Google Analytics Dimensions & Reference frequently.
Now, if we wanted to run the report, we could do so by going up to the Google Analytics tab and clicking Get Data, and this would return the number of visits for the date range. Kinda boring. So let’s add some dimensions to the data we’d like to pull. I’ve gone and added Country, or ga:country, in Google API terms.
By default, after we run the report, the data return is returned and sorted alphabetically. However, say we don’t want it alphabetically, we want it to sort by ascending value. Let’s add -ga:visitors to the sort query, and run the report. Note: If we wanted to sort descending we’d put ga:visitors into the sort query.
Nice. We’re getting the data we want and it’s sorted ascending. However, we’re seeing the value of (not set). We don’t want this. So let’s filter out (not set). We can do this by setting the filter to ga:country!=(not set). What this is telling GA is, that we’d like all countries that are not (not set). There are a lot of ways you can filter your metrics and dimensions outlined in Google’s Core Reporting API.
I tend to use RegEx to filter through data. However, one issue I’ve run into is the 128 character limit, so you’ll have to get creative when you write your RegEx.
So we’re getting our countries without the (not set) value. However, we’re seeing 127 countries returned. In this instance, 127 rows isn’t a lot; however, other queries can return up to 1000 rows, by default, and thus would slow down the time it takes to run the query. So for this example, let’s say we just want the top 10 countries.
Because a lot of the queries I have had to run have been relatively simple, I’ve only had to use the segment query once, and I was unable to get the segment work. I knew I was entering in the segment ID correctly, however when I went to get the data, it returned an error “(403) User does not have permission to perform operation for this advanced segment”. After talking with some coworkers, I realized that I was trying to access a custom segment that was built by somebody else. So remember, that when you’re trying to access an advanced segment, make sure you have permissions.
So, continuing on with Segments, there are two ways to query segments. The first way is to use the numeric ID for that custom segment. I use the awesome Google Analytics Query Explorer 2 tool to get the ID. The second way to segment is to use a dynamic parameter query, which allows you to use expressions and operators to segment the data. For our example, let’s say we only want to get direct traffic, or gaid::-7 in Google Analytics API terms.
This is another query I haven’t had to use quite yet. However, it would prove useful if you were looking to grab data for a specific range. For this example, let’s say we’d like to grab the range after the top ten counties i.e. eleven through twenty. *Note: The index does not start at zero, it starts at one. I started our start-index at eleven.
Once we have our queries, we can run our report. To do that, go up to the Google Analytics tab and click Get Data. What this does is goes to GA and gets your data. After the data has been return, you’ll see a message window like this:
This window just shows you that your query has been successful and that the script is done running. However, sometimes you’ll overlook a query, and you’ll see an error being returned in the report. For example:
The script doesn’t give you the best information for debugging your query, so what I normally do is to check that all of the metrics and dimensions are spelled correctly, or that I am actually using the correct metric. For example, when I was writing this post, I used ga:visit instead of ga:visitors. Usually the most troublesome query I have problems with is the filter using RegEx. So it’s good to test the RegEx, either in GA or using regexpal. Also, using Google Analytics Query Explorer 2 to test the query before you begin writing it does wonders for saving time.
So to recap, I’ve gone over how to configure Google Analytics Core API Report Automation (Magic) Script via Google Spreadsheet, and how to run your first Google Analytics Core Reporting query. In my following post next month, I’ll show you different Google Spreadsheet formulas to clean up your data and automate your Google Analytics reports.