Google Analytics provides an incredible amount of insight into your data but how can you view that data in a way that makes sense to you and your business? Custom Dashboards through the interface are good but they are pretty limited. The Google Analytics add-on for Google Sheets allows users to visualize and share all this data and it works across multiple views & profiles. It’s easy to get your head around and a great tool for anyone wanting to easily analyse their GA data.
The set-up has already been explained and outlined very well by Google with these tutorial videos (taken from Google’s own resources). Watch the videos and with a bit of playing around, you’ll soon get the hang of it.
Set up
Building Custom Dashboard and graphs
What I think the resources lack is more examples of the different query parameters specifically for the add-on for the non-developer types among us. Below I have listed all of the Report Configuration names, a quick summary just like you can find on the Google help page but this time with some specific examples alongside.
Report Name
Simple enough, whatever you want your report to be called. If left blank the report will not attempt to run
Type
Default value is ‘core’ for Core Reporting API but you can also have ‘mcf’ for the Multi-Channel Funnels Reporting API instead
View (Profile) ID/ids
Your Google Analytics view (profile) ID. You can get this easily by pressing create report in the analytics add-on and select your view. As soon as you know the ID you can copy and paste for other reports if needed
Start Date
Has to be in a date format, ‘today’, ‘yesterday’ or ‘NdaysAgo’ (N must be a number). We often use formulas so that the date is always current and doesn’t need to be manually changed
Example Formulas
- 1st day of the Last month =date(year(today()),month(today())-1,1)
- 1st day of the Last month Last Year =date(year(today())-1,month(today())-1,1)
- 1st day of past 3 previous months =date(year(today()),month(today())-4,1)
- 1st Day of this month =date(year(today()),month(today()),1)
End Date
Requires same formats as start date. Here are a few example formulas of end dates:
- Last day of the Last month =date(year(today()),month(today()),1-1)
- Last day of the Last month Last Year =date(year(today())-1,month(today()),1-1)
Last N Days
This is instead of a start and end date and is pretty self-explanatory. Pulls the data for the last number of full days of that you choose. You just have to enter a positive number just as 30, 14, 7 etc
Metrics
List of the metrics you want pulled. Can get name of metrics by creating a new report and selecting the different metrics you want. List of the valid metrics can be found here and multiple metrics can be separated by a comma or a new line.
- Example for amount of Users, Transactions, Goal 3 Completions & Revenue: ga:users,ga:transactions,ga:goal3Completions,ga:transactionRevenue
Dimensions
List of the dimensions you want pulled. You can get the name of the dimensions the same way as you do with the metrics (full list again here ) and the multiple dimensions can still be separated by a comma or new line.
- Example for amount for Source/Medium and Month of Year: ga:sourceMedium,ga:yearMonth
Sort
Allows you to sort (great when utilised with ‘Max Results’) by your chosen metric or dimension but whichever you choose must be present in the metrics or dimensions fields. Sorting directions defaults to ascending but you can change this to descending by using a minus sign at the start of the field
- Example to sort by bounce % (Highest to Lowest): -ga:bounceRate
- Example to sort by bounce % (Lowest to Highest): ga:bounceRate
Filters
Dimension or metric filters that restrict the data that you want returned.
You do not have to have your chosen filtered dimensions or metrics also listed in the dimension & metric fields of the report configuration.
The examples on Google Developers for filters aren’t specific for the analytics add-on sheet so I have re-written the operators along with specific examples.
|
METRIC FILTERS
|
|
Operator
|
Descriptions
|
Google Sheets Analytics Add-on examples
|
|
==
|
Equals
|
Return results where the time on the page is exactly ten seconds:
|
|
ga:timeOnPage==10
|
|
!=
|
Does not equal
|
Return results where the time on the page is not ten seconds:
|
|
ga:timeOnPage!=10
|
|
>
|
Greater than
|
Return results where time on page is strictly greater than ten seconds:
|
|
ga:timeOnPage>10
|
|
<
|
Less than
|
Return results where time on page is strictly less than ten seconds:
|
|
ga:timeOnPage<10
|
|
>=
|
Greater than or equal
to
|
Return results where the time on the page is ten seconds or more:
|
|
ga:timeOnPage>=10
|
|
<=
|
Less than or equal to
|
Return results where the time on the page is ten seconds or less:
|
|
ga:timeOnPage<=10
|
|
DIMENSION FILTERS
|
|
Operator
|
Descriptions
|
Google Sheets Analytics Add-on examples
|
|
==
|
Exactly matches
|
Return results where country is United Kingdom:
|
|
ga:country==United Kingdom
|
|
!=
|
Does not equal
|
Return results where country is not United Kingdom:
|
|
ga:country!=United Kingdom
|
|
=@
|
Contains substring
|
Return results where campaign name contains 'Remarketing':
|
|
ga:campaign=@remarketing
|
|
!@
|
Does not contain substring
|
Return results where campaign name does not contain 'Remarketing':
|
|
ga:campaign!@remarketing
|
|
=~
|
Contains a match for the regular expression
|
Return results where campaign name starts with 'UK' and contains 'Test':
|
|
ga:campaign=~UK.*test
|
|
!~
|
Does not contain a match for the regular expression
|
Return results for all campaigns that do not start with 'UK':
|
|
ga:campaign!~UK.*
|
If you need more than one filter then that's fine you can just use the OR function with a comma (,) or the AND function using a semi-colon (;).
You can combine two or more dimension/metric filters as well as combining both metric and dimension filters (a few examples below).
Filter to return results for all CPC traffic and only the remarketing campaigns:
ga:medium==cpc;ga:campaign=@remarketing
Filter used to return results for CPC campaigns not containg ‘Remarketing’ and where average session duration is greater than 10 seconds:
ga:medium==cpc;ga:campaign!@remarketing;ga:avgSessionDuration>10
Personally I prefer to use the regular expression operators for dimension filtering, especially when filtering by Source/Medium (you can test your RegEx here). This negates the need to know the exact name of what you want to search and allows you to include more than one condition without using multiple filters. We often use this filter to only pull CPC data for Google OR Bing:
ga:sourceMedium=~(google|bing).*cpc
Segment
Segments the data returned for your request. It took me a while to work out how to set up segments and there aren’t many resources that show you how.
To set up segments you start by outlining whether you want the Google Analytics reporting API to segment by ‘session’ level or ‘user’ level, follow this with double colons ‘::’ and indicate there is a ‘condition’, again follow this with double colons before entering what that condition is à your usual dimension or metric operators & names (same as the operators used for filtering). Examples below
To segment by sessions that came from any device category containing ‘Desktop’:
sessions::condition::ga:deviceCategory=@desktop
Sampling Level
Your desired sampling level: ‘DEFAULT’, ‘FASTER’ or ‘HIGHER_PRECISION’
Usually the default option will be fine but see below a description of the others
DEFAULT - Returns response with a sample size that balances speed and accuracy.
FASTER - Returns a fast response with a smaller sample size.
HIGHER_PRECISION - Returns a more accurate response using a large sample size, but this may result in the response being slower
Start Index
Only show results starting from this index. If left blank this defaults to 1 which is the first results (row 1).
You can use this along with max results if you want to drill down into different sections of your data for example you want to split your 120 campaigns into top 40, middle 40 & bottom 40 is terms of session volume so that you can allocate specific optimisation work to be carried out for each group. Top and bottom 40 you can get using the ‘Sort’ field by either sessions ascending or descending and select max results to 40 but to get the middle section you choose start index of 40 and then also max results by 40.
Max Results
The maximum number of results you want to see. The default is 1,000 but maximum is 10,000 (if you need more than 10,000 results to can but you’ll have to pull another report and using ‘start index’ of 10,001)
Spreadsheet URL
This allows you to send your report data to another spreadsheet. You just need to enter the spreadsheet URL (provided you have edit permissions for that sheet).
So there you have some more field examples for the Google Spread Sheet Analytics Add On and below I’ve just outlined a few of the popular charts/graphs we create from the data that this add-on provides whenever it is scheduled to run (or when run manually).
YoY stats
Easily keep an eye on your performance compared to previous year & helps you visualise a seasonal pattern.
Results by source
Helps you measure & test the relationship impact between sources.
Simple but useful multi-profile performance tables
Saves time downloading or looking at reports across different profile views
There are many more useful charts and graphs depending on your business and this tool means you just have to set up the configuration once and then you will easily be able to view and share the charts and data important to your business.
