If you’ve ever struggled with how to pull quick reports comparing multiple properties  or how to customize and automate dashboards with your data, the Google Sheets Add-On is worth looking into. It’s free, it’s simple and it allows you manipulate your data outside of the Google Analytics interface. The introduction is here to get started in case you haven’t used it before. If you have completed that quick-start guide, read on for the second guide to using segments and filters.

Filters

The Filters field in the report set-up is similar to other fields. If you only need one dimension to filter on, it is constructed the same way as the other fields. For example, to look at only blog pages I would add the following to the field:

ga:pagePath=~/blog/

‘ga:pagePath’ is the dimension, ‘=~’ is the operator for ‘matches’ and ‘/blog/’ is the subdirectory that I want to filter on.

To add more than one filter, we need to first think about how we want to filter the data. Should it be an ‘and’ or an ‘or’? In terms of the reporting API, just remember the following:

Or = Comma

And = Semicolon

If we want to create a filter showing blog pages or contact pages, I would use a comma and it may look like this- ga:pagePath=~/blog/,ga:pagePath==/contact. However, if we want to filter data to look at only blog pages and only users from an .edu network it would look like this- ga:pagePath=~/blog/;ga:networkDomain=~.edu.

The syntax can be tricky, so there’s a handy guide for Filters with the API to help you.

Segments

Subsetting your data into specific segments of sessions or users is one of the most valuable tools as an analyst. To use advanced segments with the Google Sheets Add-On, you have a couple of options.

Option #1

The first option is appropriate if you have already created your advanced segments in Google Analytics. For example, let’s say I have a simple advanced segment that looks at users from an .edu network domain (showing that they may be on a campus. The set-up in the Google Analytics interface would look like below:

If it is already created, one way to get the segment’s ID is to use the Query Explorer. In the segment dropdown menu, the ID will show automatically and you can copy-and-paste it right from the page to Google Sheets.

There is also a trick to getting the ID without using the Exporer or the API. In Google Analytics, apply your segment so that it is the only segment. In the URL, you should see something like below at the end:

/%3F_u.date00%3D20151001%26_u.date01%3D20151031%26_.useg%3DuserhSiQKwf1RVqrtiVO54rx7Q/

The last part after ‘user’ is the segment ID.

Option #2

Segments can also be added while you are setting up your report in Google Sheets. The syntax is slightly different that the regular reporting fields. Using the same example, if I wanted to only look at sessions from an .edu network, I would go to the Segment field and build it like below:

users::condition::ga:networkDomain=~.edu

The important thing to remember is to specify the scope of the segment (users or sessions). And again, there’s documentation on Segments with the API to help you out!

The ease of adding segments and filters in the Google Sheets makes the add-on one of the quickest ways to use the Core Reporting API. For all the dimensions and metrics available with the API, check the reference here.