It’s Magic! Automating Your Reports With Google’s API

By Susan Rust

If you work in the search marketing industry, you have to prove to your clients that they are getting results, and you don’t want the only metric to be rankings. However, that probably means there are about 10-15 other metrics you think are useful, but the client will never get as excited about. Also, pulling these stats together every week or month probably takes a lot of time.

That’s exactly what happened at Thunder, and we realized we were spending a lot of time on reporting each month. Thanks to an extensive internal time audit, we figured out just how much time was being spent gathering, analyzing and presenting data from Google Analytics alone (it was a lot). So we set out to speed things up while tackling the issue of shifting clients’ focus as well, and that’s exactly what we did. Here’s how.

Just like everything else in this industry, it all started with Google. A team of Google developers realized how difficult it was to export and work with data from Google Analytics, so they created an Analytics API for developers to use. Furthermore, Nick Mihailovski who lead the API development, created a slick way to put Analytics data into Google Spreadsheets using their own Apps Script. Put all of this together and you now have a way to automate the export of data from Analytics into Spreadsheets – it’s called Magic. No, I’m not kidding it really is called Magic, and it lives up to its name.

Google Magic Script

Instant Gratification

With the help of this new tool, Thunder was able to reduce the time of gathering data for one website from hours to instantaneously. Magic allows you to set up what metrics you want to report on going forward and then set a trigger for pulling that data via the API however frequently you’d like. This means every time you log in to your Google Spreadsheet that contains the report, the data is up to date with the latest values! We didn’t even have to open Analytics.

Mind Blown

This blew my mind.

The next best thing was the fact that since it all lived in Spreadsheets, I could create my own custom tabs with formulas to rework the data however I wanted. I could also display that data in an easy to understand chart right next to it that updated itself automatically since my formulas were referencing API data. So just to recap, I was now able to open my report and it was done for me.

Getting Everyone Excited About Key Metrics

Obviously I was excited and just about everyone at Thunder was realizing how much campaign execution time just opened up for them. Our challenge was now to figure out how we could present these metrics to our clients in a quick manner that got them thinking about more than rankings. Max went to his special thinking place and came back with a great idea – why don’t we automate the presented report too? We could link the Spreadsheet to a public Google Site, and the site could be accessed at anytime by authorized users.

Google Site Auto KPI

This great idea solved the main issue of why clients were checking rankings – they could. Think about it. If you own or run a business and you’re getting monthly reports, you probably aren’t feeling 100% in control. You have to wait for the end of the month to make future decisions. With Max’s idea, clients could now check the metrics we wanted them checking whenever they wanted! We also were able to add branding to our reports, so the Google Sites could be presented in a meeting at any time.

Ready To Automate Your Report?

I wish I could say the process of connecting the dots was easy but it really wasn’t. Max and I put a lot of time into researching Google blog posts, browsing developer forums and watching how-to videos. It took a lot of trial and error but I want to share what we’ve learned so you can get started if you’re interested.

I’m not able to show you which metrics we use exactly, but here’s a list of the exact articles we referenced when putting the reports together. If you want to learn more and get started right away, I recommend checking out all of the links below because it’s important to understand the “what” as much as the “how.” Good luck and let me know if you need any help in the comments!


Connecting the Google Analytics API to Google Spreadsheets

Using Magic to Automate Google Analytics into Google Spreadsheets

Core Reporting API Reference Guide

Analytics Dimensions & Metrics Reference Guide

Share with Your Colleagues:
Susan Rust

By Susan Rust

I believe we hear and learn to say "no, it can't be done, that's too hard" rather than say "yes, let's do it now!" I have many mottos, mine for now is "Run fast, break things."