Knowledge
BANK
Home > Knowledge Bank > Articles > Easy Google Analytics Api For Excel 2

Easy Google Analytics API for Excel

4

EDIT: Don’t want to read? Just download the Google API Sheet from the bottom and enter your details into the cells.

What is the Google Analytics API? 

The Google Analytics API allows you to pull analytics information directly from the back end of Google Analytics, letting you skip the often cumbersome Google Analytics interface.  The API lets you grab a huge amount of data without having to worry about your browser crashing or inconsistent parameters being set report to report.

Why should you be using the API instead of the normal Analytics dashboard?

Once set up, using the API will streamline not only your reports, but how you deal with data from Google Analytics as a whole. It is less clunky than the GA interface as it allows you to look at exactly the data you want without having to navigate anywhere online.

It allows you to directly import data into customised reports, automatically populating tables and updating visuals so that monthly reports take seconds instead of hours.

The API lets you pull numerous dimensions at one time including keyword, country, city, date, etc. allowing you to gain maximum insight with minimal effort.

How this guide is different to others

Most other guides out there offer one of two ways to pull data from the GA API:

Directly into a Google Docs sheet, usually using the Magic script or something similar.

Or

Into Excel via a third party plugin such as Excellent Analytics.

Often it may be easier or more useful to circumnavigate both of these options and just pull the data directly from the API itself, which is what this tutorial aims to explain with a nifty bit of Excel code written by the incredibly smart Mikael Thuneberg over at Supermetrics. Be sure to check them out as they have this and a host of other quality time savers.

How to set up a simple Google Analytics dashboard in Excel

The very first thing you will need to do is enable macros in Excel by going file > Options > Trust Center > Trust Center Settings > Macro Settings and then you need to unmark “Disable all macros without notification” and “Disable all macros except digitally signed macros,” this will allow you to still filter out most dangerous code that might try to run in your Excel from other non-Google programs.*

trust centre

 

A yellow box may appear in the top left hand corner of your document after this asking you if you want to enable macros, click yes.

Next, you will want to define your date ranges. If you want monthly data a really useful tid bit is the End of Month formula which requires you to set up the first date of any month in one cell and then reference that in a second cell with the End of Month Formula: =EOMONTH(the cell with your start date in it, 0). This will always calculate the date correctly as Excel is smart enough to know how long every month is and can even take into account February during leap years.

It is also handy to include the name of your client or GA profile along with the GA Profile Number somewhere on the sheet for easy reference (information on where to find your GA profile number is coming up).

Your resulting sheet should look like this:

verve

 

You will then need to decide on what information you would like to pull. Some basic data to pull is:

  • Organic Traffic
  • Direct Traffic
  • Referral Traffic
  • PPC Traffic
  • Organic Brand Visits
  • Organic Non Brand Visits
  • Organic Not Provided Visits
  • Bounce Rate
  • Pages Viewed

Set these up in rows which will eventually reference your date ranges already set up to pull your data. The resulting sheet should now look something like this:

to&from

 

The sheet is now ready for the GetGAData string to be inserted to pull the data for all of these. The string requires several parts, most of which are mandatory but some are optional

The GetGAData formula: =getgadata(getgaauthenticationtoken

The login details for your Google Analytics account: (“email@gmail.com”, “password”),

The profile number for your specific Google Analytics Profile you want to pull the data from: 55555555,

Note that this will change for every GA profile that you have and can be found in the URL of the dashboard for the specific profile you want. It is the string of numbers after the letter “p” as can be seen here:

urld

 

The metric you want (visits, bounce rate, time on site, etc.): “visits”,

The start and end dates you want to pull data for (in this case we are referencing the cells with the dates we set up earlier, but you can put specific dates in here if you want): C$7, C$8,

And the segment/medium. For whatever reason you need to use a double equals sign here for this to work: “medium==organic”)

Throw all of those parts of the formula together and you will get this:

=getgadata(getgaauthenticationtoken(“email@gmail.com”, “password”), 55555555, “visits”, C$7, C$8, “medium==organic”)

That will pull organic visits to your website during the month you specified. Now all you need to do is adjust the formula for the different metrics and mediums that you want:

Direct Traffic:

=getgadata(getgaauthenticationtoken(“email@gmail.com”, “password”), 55555555, “visits”, C$7, C$8, “medium==(none)”)

Referral Traffic:

=getgadata(getgaauthenticationtoken(“email@gmail.com”, “password”), 55555555, “visits”, C$7, C$8, “medium==referral”)

PPC Traffic:

=getgadata(getgaauthenticationtoken(“email@gmail.com”, “password”), 55555555, “visits”, C$7, C$8, “medium==ppc”)

Total Traffic:

=getgadata(getgaauthenticationtoken(“email@gmail.com”, “password”), 55555555, “visits”, C$7, C$8)

Bounce Rate:

=getgadata(getgaauthenticationtoken(“email@gmail.com”, “password”), 55555555, “visitBounceRate”, C$7, C$8, “medium==organic”),2)

Pages Viewed:

=getgadata(getgaauthenticationtoken(“email@gmail.com”, “password”), 55555555, “pageviews”, C$7, C$8)

To pull the brand/non brand visits you will need to use regular expressions

Don’t less this scare you, regex is just a fancy way to set up filters, telling the API “I want all keywords that include my branded terms, including common spelling mistakes.” The way you do this is by using the “keyword” and a modifier, followed by the terms you want to include or exclude separated by pipes at the end of your string. To include certain terms you use “keyword=~” and to exclude terms you use “keyword!~”

So your formulas will look like this the following:

SEO Brand Visits:

=getgadata(getgaauthenticationtoken(“email@gmail.com”, “password”), 55555555, “visits”, C$7, C$8, “medium==organic”, “”, “keyword=~verve|varve|lisa|myers”)

SEO Non Brand Visits:

=getgadata(getgaauthenticationtoken(“email@gmail.com”, “password”), 55555555, “visits”, C$7, C$8, “medium==organic”, “”, “keyword!~verve|varve|lisa|myers”)

After you enter all of these formulas into the corresponding cells you will have a sheet that looks like this:final

This sheet will automatically repopulate when you change the dates.

It looks complicated

It’s not. Seriously, just copy and paste the above formulas into an excel sheet, replace the logins with your own, make sure they are referencing a specific date range, and you will be amazed at how easy this is. Better yet, download the sheet from below and replace the details with you own. It is linked under “Google API Sheet”

There are a host of other mediums, metrics, segments, filters, and dimensions that you can use. Check in the Google Analytics Query Explorer if you want to get an idea about other sets of data you can pull.

Things to remember

This is technically referencing an old version of the GA API so it may expire at some time, but it currently works perfectly.

If you get stuck, use the Query Explorer as a guide.  As previously stated, this is an old version of the API so the queries that you use to call data are not like-for-like, but they are incredibly similar. Instead of using “ga:visits” as it says in the Query Explorer, you would just use “visits” and instead of using “gaid::-1” to pull All Visits, you would use “All Visits”

googledelevloper

The strings you are entering into excel work the same as any other formula so they need to match up exactly otherwise they won’t be able to call the data correctly.

If you are really stuck you can always ask a question here or get more information from the Google Analytics Developers site.

 

*This is in Microsoft Excel 2010. You may need to search around for how to do this in other versions.
Google API Sheet