Getting more out of Google Analytics with Google Sheets

The good thing about August is that there is generally a little more time available to look around and investigate new things.

It was in this vein that I took the opportunity to take a deeper dive into the capabilities of Google Analytics and, in particular, what you can do if you link it into Google Sheets.

Google Analytics is, of course, a very powerful tool in its own right and there is most of the information you need right there in the application.  If the standard menu doesn’t provide what you want, there is the option of creating customised reports.

So, what more could you possibly need?

Pleasing all the people, all the time

We monitor and optimise our clients’ campaigns via a dashboard of key performance indicators, an important part of which is measuring performance against baseline data and agreed targets.

To examine different elements of a campaign, we can find ourselves jumping around within Analytics and outputting the information into a spreadsheet or PowerPoint, both to perform trend analysis and for presentation purposes. Everybody wants the ‘one-pager’ with everything they need to know in one place – the thing is, everybody wants to know something different!

Pulling the raw data into Google Sheets (which is Google’s version of Excel), allows us to manipulate and present the information as we want to, with the added benefit that the data is linked directly into Google Analytics itself via the Google API. As such, updating the reports once they’ve been created is simply a matter of scheduling them to run at pre-set intervals or selecting ‘run report’ to do it ad-hoc – no more pulling the data from multiple sources each time we need to create a new management summary.

So, what do you need to unlock the power of this wonder tool?

1. A Google account
2. A decent understanding of what is in Google Analytics (principally, the metrics and dimensions)
3. A decent understanding of how to create and use formulas in Excel (the same formulas work in Google sheets).

Fortunately, I just about qualify on all three counts, so it allowed me to start experimenting. As with anything new, there is no real substitute for just diving in and learning by trial and error. In an effort to short cut the errors for you, here’s what I’ve discovered so far.

Getting started

You’ll need to add the Google Analytics ‘add on’ into Google Sheets, which is simply a case of clicking on the ‘+’ button to install it.

Google Analytics will now appear in the Add-ons submenu in your Google Sheets navigation bar.

While you’re at it, you might also want to add the Table Styles ‘add on’ as this will help with the presentation of your dashboard later on.

You’re now good to go.

Creating reports

To use the tool, navigate to ‘create new report’ in the Add-ons submenu and a wizard will pop up. This allows you to select the Analytics account you want to report on and the information you want to extract.

Google talks in terms of ‘metrics’ and ‘dimensions’, which you might think about as ‘what do I want to measure?’ and ‘how do I want to slice it?’

There is a drop down for each, and you will find every element that Google Analytics has to offer in there. In my trial phase, I kept to something simple and created a basic traffic report using sessions, bounce rate and page-views per session as my metrics, with the medium and the source as my dimensions. 

When you’ve finished making your selections, click ‘create report’ and the report information will appear in a sheet named ‘report configuration’. You will note that there are a number of other options in the report configuration, but you can leave these blank for now.  They are there to allow you to further refine the outputs – I prefer to learn to walk first.

To create additional reports on the same property, you simply repeat the process.

(Note, I haven’t been able to find how to edit a report configuration using the wizard once it has been created. The only way to do this seems to be to edit it manually via the cells. If you want to add metrics and dimensions to an existing report, there is a library of them here.

Running reports

Here’s the fun bit. Add the start date and end date that you want to report on (or the last N days), go back up to your Add-ons submenu and select ‘run report’.  You will now find out if it has worked or not! Hopefully, you will get a message that the report has run OK. If you get an error message, it should point you to what you need to correct.

Creating your dashboard

You will find the output of your reports has been placed under new tabs which match the titles you gave them in the report configuration. Unfortunately, it’s just ugly lines of data at this stage that can’t be interpreted by anybody, let alone the busy management team that you’re reporting to.

This is where your Excel skills need to come into play.

Create a new tab and call it ‘dashboard’. Here you can start to tabulate the information and format it in a way that will be useful to those that need it.

You will need to use formulas to extract the information you need. So far, in addition to the normal SUM and = formulas, I have found SUMIF/SUMIFs, AverageIF/AverageIFs, most useful. I am sure there is whole level of additional power that can be unleashed with other formulas like ARRAYFORMULA. I’m not going to go into how to use these here, but hopefully there is enough for you to be able to look them up online and follow one of the many tutorials.

With your initial tabulated information in place, you can then start to add additional value to it by calculating the trends or using the chart options to make the information more visual. Again, basic Excel skills will stand you in good stead to do this. (If video is more your thing and you’ve got 15 mins to spare, I recommend you view this: https://youtu.be/N_Ok0rJwj2U)

From here, it’s really over to you to decide what information you need and how you want to present it. 

Give it a go

As I said earlier, the only true way of finding out what Google Analytics into Google sheets can do for you is by giving it a go and taking yourself on a journey of discovery. You will undoubtedly make a few mistakes, but perseverance should be rewarded and, once you’ve got a report that you’re happy with, it should make your life much easier from here on in.

If you need any help with the content of this post or setting up reporting dashboards, feel free to contact us.