Learn how you can automate data quality audits in Google Analytics using the official Google Analytics Google Sheets add on.
Disclaimer: when I say audit, I really mean a health check.
The point of Google Analytics is to guide future decisions. To increase your chance of making the right decision, you need to ensure that the data is not unexpectedly impacted by your implementation.
There are common configuration issues that can do this, which are easily fixed. However, you do not want to spend a significant amount of time pulling data to check and re-check this every time you start or update your implementation. As an analyst, you should spend your time guiding decisions, rather than pulling data.
You can automate the process of doing basic health checks on your Google Analytics data using Google Sheets and the official Google Analytics add on.
Do not use this as a plug and play solution. You will find completely arbitrary logic within the “Audit Summary” sheet I have created.
The point of this article is to:
I’m not going to spend time explaining how these work. The best way to learn is to Google, or YouTube the concept. It will be hard to understand, modify and add new checks to the audit without this knowledge. I recommend you learn how to use:
Option | Explanation | Example |
---|---|---|
Report name | Gives your report a name. This determines what the sheet is called when it is created. | hostname |
View ID | Your view ID you want to pull data from. This can be found under Admin > View > View Settings in the Google Analytics UI. | 172758398 |
Start Date | Start date from which you want to pull data from. This is in “YYYY-MM-DD” format or there are some strings you can use like “yesterday”. | yesterday |
End Date | End date from which you want to pull data from. | 2018-12-23 |
Metrics | The API names of the metrics you want to include in your reports. To look up the API names, go to this link. | ga:sessions |
Dimensions | The API names of the metrics you want to include in your reports. | ga:eventCategory, ga:eventAction, ga:eventLabel |
Order | Specifies the order in which data is returned. | -ga:sessions |
Filters | Adds filters to your request. Note: more complicated filters will need to follow the API’s filter object formatting. | Review the documentation for an example |
Segments | Allows you to apply segments to reports just like you would in the Google Analytics UI. | Review the example in the documentation. |
Limit | Allows you to specify how many results are returned. You should always specify a limit as some of your reports may be bigger than the row limit in Google Sheets. | 50 |
POST https://analyticsreporting.googleapis.com/v4/reports:batchGet { "reportRequests": [ { "viewId": "172758398", "dateRanges": [{"startDate": "yesterday", "endDate": "2018-12-23"}], "metrics": [{"expression": "ga:sessions"}], "dimensions":[{"name":"ga:eventCategory"}, {"name":"ga:eventAction"}, {"name":"ga:eventLabel"}], "orderBys":[ {"fieldName": "ga:sessions", "sortOrder":"DESCENDING"} ] //doesn't include filter, limit etc from above } ] }
Config Options | Config |
---|---|
Report Name | hostname |
View ID | {{your view ID}} |
Start Date | 2018-12-23 |
End Date | 2019-01-22 |
Metrics | ga:sessions |
Dimensions | ga:hostname |
Order | -ga:sessions |
Limit | 500 |
=IF(COUNTA(hostname!A16:A)=1,"Passed","Requires attention")
=IF('goal completions'!A12>0,"Passed","Needs attention")
=IF(COUNTA(hostname!A16:A)=1,"Passed","Requires attention")
=IF(ABS('google ads link'!B12-'google ads link'!C12)/('google ads link'!B12+'google ads link'!C12)<0.5,"Passed","Requires attention")
=IF(AND('pii events'!D12=0,'pii pageviews'!B12=0), "Passed","Requires attention")
=IF(OR(COUNTIFS('source/medium'!A14:A,"*"&"(not set)"&"*"),COUNTUNIQUE(ARRAYFORMULA(LOWER('source/medium'!A16:A))),COUNTUNIQUE('source/medium'!A16:A))=TRUE,"Requires attention","Passed")
=IF(OR('bounce rate'!A12<0.05,'bounce rate'!A12>0.95),"Requires attention","Passed")
=IF(COUNTIFS('internal referrals'!A16:A,"*"&B2&"*")>0,"Requires attention","Passed")
Comments
There are no comments on this entry.