tobiaswillmann.de

The 10 things I always had to look-up using Google Analytics Core Reporting API in Google Sheets

2020-04-01

could be extended. List for now in random skill / difficulty level…

1) How to use Custom Dimensions

e.g.

ga:dimension12==blick_web_desktop

2) INDIRECT()

It’s probably my #1 function to type less while collecting information out of countless reports. If your Report Configuration looks like this

You can get pageview numbers using

='bigbeachspringbreak.com LastYear'!B12

But if you don’t want to type or select the sheet use

=INDIRECT("'"&A3&"'!B12")

and have the sheet name ‘bigbeachspringbreak.com LastYear’ in A3

INDIRCT in VLOOKUP 🙌

could be done like this

=SVERWEIS(B5;INDIREKT("'"&E$2&"'!"&"A:D");2;FALSCH)

3) The No1 “waiting time”-saver

is to put TRUE to Skip Report if you are done

4) Operators

Most of the time I’m looking for these:

=@Contains substring.

!@Does not contain substring

=~Contains a match for regular expression.

!~Does not contain a match for regular expression.

but here are all of them:

https://developers.google.com/analytics/devguides/reporting/core/v3/segments#operators

5) Segments

e.g. like this:

users::condition::ga:sessions>=2;ga:sessions<=29

6) How to filter and regex to get an ID

ga:pagePath=~.*id[0–9]+.html

7) Sampling OFF

These two setting make data more accurate

  • Sampling Level: LARGE
  • Use Resource Quotas: YES

8) Dates

can be done like this

  • 13/10/2019
  • yesterday
  • 31daysAgo
  • ENDOFMONTH(<dates>)
  • TODAY()

other date functions in Sheets are useful too

9) Dimensions & Metrics

Most of the time im looking for

Dimensions

ga:channelGrouping, ga:source, ga:medium, ga:pagePath, ga:landingPagePath, ga:landingScreenName ,ga:browser

Metics

ga:pageviews, ga:screenviews, ga:entrances, ga:sessions, ga:users

Here are all

https://developers.google.com/analytics/devguides/reporting/core/v3/segments#operators

10) AND (i.e., ‘;’) and OR (i.e., ‘,’)

https://developers.google.com/analytics/devguides/reporting/core/v3/segments#operators

e.g. used like this:

ga:pagePath=~.*id[0–9]{6}.html;ga:pageviews<=10;ga:pagePath!@=

What are the top notes on YOUR cheat sheet for Google Analytics Core Reporting API in Google Sheets? Please comment 👇👇👇

© 2020 Tobias Willmann