A step-by-step guide to creating Google Analytics reports in Google Sheets

Posted on Posted in Google Analytics

So you’ve delved into Google Analytics and have a decent understanding of all the data that’s available to you. Awesome!

You then come to realise that the GA user interface and dashboard functionality is actually pretty limited. You want to access a variety of dimensions and metrics within a dashboard that has unlimited interactive graphs and tables. You need flexibility, complexity and automation. For free.

Well, Google Sheets and the Google Analytics add-on is your answer.

Here we’ll give you a step-by-step guide to integrating GA with Google Sheets, including how to build automated reports and create those flexible dashboards.

Sections for our guide include:

So unless you’re jumping ahead, let’s get started.


Step One: Installation

First of all, you’ll need to get the Google Analytics add-on.

Using the same login details as you use for GA, sign into Google Sheets and open up a new spreadsheet. Then, just click on the ‘Add-ons’ tab at the top and select ‘Get add-ons’.




A small window should pop up, in which you can search for Google Analytics and select the relevant add-on (it should be the first one). You’ll then need to give Sheets permission to access your GA data.


Step Two: Creating your first report

Once the add-on is installed, go to the top menu and click Add-ons > Google Analytics > Create new report. Once you’ve done that, a sidebar will appear to the right of your screen.




At the top you can name your report – this should be relevant and unique so that you don’t get confused later on! Then you need to select the GA Account, Property and View you want to use, which Sheets should have automatically pulled through into the dropdown.

You can then choose from a range of metrics and dimensions, all of which you might recognise from reports in GA.
Once you’re happy, click ‘Create Report’ and your report should be generated into a new tab called ‘Report Configuration’.

Et voila, your first report! You’ll probably need to do some extra customising (see the next step), but if you’re ready to go, just click Add-ons > Google Analytics > Run reports. A new tab will appear with all your data!


Step Three: Customising your report

Once you clicked the ‘Create Report’ button, you’ll have seen some new options appeared for your report. This is where the ultra fun customisation begins!


Dates

Firstly, you’ll see your report is automatically created for the last 7 days. Unless you’re happy with this, delete the 7 so that cell is blank and fill in the ‘Start date’ and ‘End date’ cells (mm/dd/yyyy).

We can get a bit clever here too. While you can just put in the relevant date, you can also specify relative days (such as the first day of the year, or the last day of last month). This is really helpful if you want to automate your reporting and you need the dates to update themselves rather than having to do it manually every month.

Here are some formulas that might be useful – just put them straight into the relevant date cell:

Formula Result
=TODAY() Today
=TODAY()-1 Yesterday
=TODAY()-4 4 days ago (you get the idea)
=TODAY()-WEEKDAY(TODAY(),1)-5 First day of last week
=TODAY()-WEEKDAY(TODAY(),1)-6 Last day of last week
=EOMONTH(TODAY(),-1)+1 First day of this month
=EOMONTH(TODAY(),0) Last day of this month
=EOMONTH(TODAY(),-2)+1 First day of last month
=EOMONTH(TODAY(),-1) Last day of last month
=EOMONTH(TODAY(),-3)+1 First day of month before last
=EOMONTH(TODAY(),-2) Last day of month before last
=date(year(today())-1,month(today()),1) Same month but a year earlier



Dimensions and metrics

When we first created the report, you had a chance to input all of your metrics and dimensions. But you can still do this by editing the relevant cells.

You can see that the metrics and dimensions you originally put in now have ‘ga:’ in front of them (e.g. ga:uniquePageviews).




So if you want to add more, just add them into the cell with a single space in between each one.
You can find a list of all the possible metrics and dimensions on this Google Developers page.


Sorting your report

You then get the option of sorting your report, either by date, metric or dimension. Just type the relevant ‘ga’ formula (e.g. ga:date, ga:pageviews, ga:sourceMedium) in to ‘sort’ cell.

Automatically it will sort numbers from 1 upwards, but for metrics like Pageviews you might want to see the highest number first. To do this, just put a minus symbol before the ‘ga’ formula (e.g. -ga:pageviews).

If you want to sort by multiple criteria (for example by month and then by pageviews), you can put both formulas into the cell separated by a space (e.g. ga:month -ga:pageviews).


Filters

If you want to exclude some data from your report, you can do this in the ‘filter’ cell. This gets a little bit more complicated, but it’s easy once you get into the swing of it!

First, here are some operators you’ll need to know:

Metric operator Result
== Equals
!= Does not equal
> Greater than
< Less than
>= Greater than or equal to
<= Less than or equal to
Dimension operator Result
== Exact match
!= Does not match
=@ Contains substring
!@ Does not contain substring
=~ Contains match for the regex
!~ Does not match regex













In the ‘filter’ cell, use these formulas to filter your data. For instance:

  • ga:pagePath=@/contact-us (Only includes URLs containing the string /contact-us)
  • ga:sourceMedium!@organic (Excludes all organic traffic)
  • ga: deviceCategory==mobile (Only includes mobile traffic)
  • ga:pageviews>5000 (Only includes pages with pageviews over 5000)

Of course, you can filter by multiple conditions too. To filter by Condition 1 AND Condition 2, separate with a semi-colon:

  • ga:pagePath=@/contact-us;ga:medium==organic (will return organic traffic for the Contact Us page)

To filter by Condition 1 OR Condition 2, just separate them all with a comma:

  • ga:fullReferrer=@twitter.com,ga:fullReferrer=@facebook.com (will return data for users coming from Twitter or Facebook).

You can also combine the two:

  • ga:medium=@organic;ga:pagePath=@contact-us,ga:pagePath=@about-us (will return data for all organic traffic visiting the ‘Contact us’ page or the ‘About us’ page).



Segments

Just like in GA, segments give you much more flexibility to your filtering. Using segments, you can return results based on certain conditions (e.g. for sessions that took place on Google Chrome), or based on specific sequences (e.g. users who visited your Homepage and then later visited your Contact page).

To do this, the first thing you have to decide is whether you want to segment by User or by Session. Note, the downside of segmenting by User is that you can only get data for the previous 90 days, whereas with sessions you can go back much further. You then decide whether you want to filter by condition or sequence.

The formula for conditional segments is laid out like this:

  • users::condition:: ga:deviceCategory==desktop
  • users::condition::ga:userGender==Male
  • sessions::condition::ga:country=@united kingdom
  • sessions::condition::ga:browser==Chrome

For multiple segments, just separate the formulas with a semi-colon (AND) or comma (OR).

For sequence segments, you’ll need to know a couple of extra operators:

Operator Result
;–>> Followed by
;–> Immediately followed by



Here’s how the formula is structured:

  • users::sequence::!ga:deviceCategory==desktop;->ga:deviceCategory==tablet
  • sessions::sequence::ga:pagePath=@/contact-us;->>ga:pagePath=@/thank-you



Sampling

The next option you get is to add in a sampling level. Google Sheets may automatically sample your data. You can see how much a report is sampled when you run the report – the report will open in a new tab and at the top you’ll see a sampling level.

If you don’t want a particular report to be sampled, just type ‘HIGHER_PRECISION’ into the ‘Sampling level’ cell in the Report Configuration tab. However, this won’t always prevent sampling so you’ll either have to continue with the sampled data or reduce the volume of data that you’re requesting from GA


Start index

By default, your report will start from the first result (1). If you’d like it to start from a different position, just type in the row number you’d like to start from.


Max results

By default, each report will return a max of 1,000 results. If you’d like more than then just type in the number you of rows you need. Bear in mind that Google Sheets has a limit of 400,000 cells per sheet and so this may run out if you’re producing lots of big reports.


Example reports

If you’ve reached this point, then you should be well on the way to becoming a Sheets expert – congratulations!

We’ve created a few basic reports to show what you can do with Google Sheets. If you want to copy the templates and edit them yourself, just open them up and click on File > Make a copy.












Leave a Reply