Google Analytics Core Reporting API in Google Sheets — Cheat Sheet

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:

Core Reporting API - Segments | Analytics Core Reporting API

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

Dimensions & Metrics Explorer

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

Core Reporting API — Segments | Analytics Core Reporting API

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 👇👇👇