Installing the Google Analytics add-on
The Google Analytics add-on is a great solution for getting all the data you need into Google Sheets. Once mastered, this tool will remove the need to manually navigate Google Analytics to retrieve the data you need for report generation.
Getting started
To get started with the Google Analytics add-on, you will need to log in to your Google account and create a blank Google docs spreadsheet (just click blank here). You will also need to have access to a Google Analytics account via the same Google account you’re using with Google sheets.
To download the add-on, open your spreadsheet and navigate to: ‘Add-ons’ > ‘Get add-ons’:
Once you’ve found and installed the Google Analytics add-on, you’ll be prompted to authorise access to a Google Analytics profile. Once authorised, simply navigate to ‘Add-ons’ > ‘Google Analytics’ > ‘Create Report’ to get started on your first report.
Within the ‘Create Report’ table above, you will be able to specify which property and view you would like to report on. Here you can choose which metrics and dimensions to use for your report, however it’s worth mentioning that this can be customised further AFTER you click create report if required.
After clicking ‘Create Report’ at the bottom of the rectangular dialogue box, your workbook will have a new sheet called ‘Report Configuration‘, which is where you will be able to tweak the settings of each report you’d like to create.
Here’s an example of what the ‘Report Configuration‘ sheet looks like when used to generate multiple reports (separated by columns):
As you can see in the screen grab above, every report will be generated via a new sheet. You may also notice that I have added a sheet to act as a dashboard (which I’ll cover later in this post) that references the raw report data in each sheet.
Within the Report Configuration sheet, there is no option to use drop-down menus to select the metrics and dimensions you would like to report on. For this reason, you might find it useful to have the reference guide open to identify which dimensions and metrics you need to use when creating a new report.
Creating a basic report
To give you a better idea of how to create and customise reports within the Report Configuration sheet, let’s take a look at a basic example.
To generate a new report, you’ll need to edit the values within each row of the report configuration sheet. To do this, you’ll firstly need to understand which rows need to be edited in order to generate a basic report, and which are optional (for advanced use cases).
Mandatory fields
- Report name – what you would like your report to be called
- Type – this does not need to be changed
- View (profile) – this references the Google Analytics property you originally selected via add-ons > Google Analytics > create new report
- Start / end date – the date range you would like to report on
- Metrics – the metrics you would like to include in your report – see reference guide for details
- Dimensions – the dimensions you would like to use for your report – see reference guide for details
Optional fields
- Last N Days – number of days previous to report on (this will override start/end dates if present)
- Sort – specifies an order to return your data by, can be used to organise data before generating a report
- Filters – allows you to filter the data included in your report based on any dimension (not just those in the report)
- Segment – allows you to use segments from the main reporting interface
- Sampling Level – specify the level of sampling for the data you’re pulling. Using the add-on, you can increase the precision of sampling by using the command “HIGHER_PRECISION”
- Start Index – for use with Max Results, when you want to retrieve a set of data (e.g if you’re getting 1,000 results, and want to get results 500 – 1,000)
- Max Results – default is 1,000, max is 10,000
- Spreadsheet URL – option to send your report data to another spreadsheet / URL
Example report: organic sessions (YOY + MOM comparison)
Let’s say we wanted to generate a report to monitor organic traffic with a rolling MOM (month on month) comparison for the past 12 months, and a YOY (year on year) comparison for the last complete month.
To do this, we would start by configuring our report as follows:
Start and end dates
For the start and end dates for each report, you can either write dates manually, or enter formulas into the report configuration so they update automatically by using various DATE formulas.
In this example, we are looking to create a report that includes data for the last complete month vs. the same month a year previous (and each month in between). Thus, we need to ensure that the start date is always set to collect data 13 months previous, and that the end date is set to the last day of the last full month.
We can achieve this by using the following formulas:
- In the End date column, we would set the last day of the most recent full month. This formula is based on an “if” condition to determine if today’s date is the end of the month and, if it is, to use that date, otherwise to go back to the previous full month. Here’s the formula in full:
=if(today()=eomonth(today(),0),today(),eomonth(today(),-1)) - In the Start date column, we need to ensure the date automatically updates to 13 months prior to the date referenced in the End date column, so that we can do a year on year comparison for the most recent full month. To do this, we can use the EDATE function, which returns a date a specified number of months before or after another date. In order to set the start date 13 months prior to the end date, we would use the following formula (where the ‘end date’ cell = B6):
EDATE(B6, -13)
Using filters
The filters field allows you to drill down into specific dimensions and metrics. As we only wish to report on organic sessions (vs. all sessions regardless of source) we would need to input the following filter:
ga:medium=~organic
‘ga:medium’ is the dimension, ‘=~’ is the operator for ‘matches’ and ‘organic’ is the only medium we want to report on.
To add more than one filter to a report, you need to consider whether the filter should be an ‘and’ or an ‘or’.
To do this, just remember the following:
- Or = Comma (,)
- And = Semicolon (;)
For example, if we want to create a filter to show only blog visits that have been referred via organic search, we would use a semicolon in the filters field:
ga:pagePath=~/blog/;ga:medium=~organic
For help with the syntax for these fields, check out this guide for Filters with the API.
Generating the report
Once you click generate, the report will appear on a new tab with a name coinciding with the title of your report on the report configuration sheet.
Here’s an example of the report we’ve just built and generated:
As you can see, this is raw data devoid of any formatting, so the next step is to create a ‘dashboard’ tab to better visualise this data.
Setting up a dashboard
To create a basic dashboard to display data from multiple reports in Google sheets, you’ll first need to create a new sheet.
On this sheet, you should create an empty table with row headings that correspond to the headings in your individual reports OR sections of the reports you’d like to use on your dashboard.
For example, we could create a table like this to reference the data in the organic traffic report we’ve just created:
In the above example, I have created a new row for each month and used the following formula originally provided in this post by Ben Collins:
=text(today(),”MMMM”)
Then I used the following formula to create the month prior to the current one, as another row heading:
=text(date(year(today()-1),month(today())-1,1),”MMMM”)
To complete the months, I kept “rolling” the months back, by changing the “-1” to “-2” for two months prior, then “-3”, “-4” all the way back to “-13”, to give the current month and 13 preceding months in an empty table.
The yearly column in the table is created with the same formulas listed above, and replacing “MMMM” with “YYYY”.
Once you have your date rows set up, you can simply use a formula to map the data from the raw analytics reporting sheets over to your newly created table. In the vast majority of cases, it’s enough to use a simple link to the cell that has the corresponding data.
Of course, creating a comparison table in your dashboard is just the beginning. You can move on from here to create and format multiple reports within your dashboard sheet, before adding charts and sparklines to visualise the data included in your dashboard tables.
Once you’ve mastered this, a Google sheets dashboard is a powerful way of displaying bespoke data to clients and/or peers, and a viable alternative to custom GA reports or the use of third party reporting tools.
Automating reporting
Once you’ve created a set of reports and customised your dashboard, you can automate the generation of your reports using the add-on. This will ensure your reports are always up to date, and is particularly useful if you used formulas in your start/end dates within the report configuration sheet as we outlined earlier.
To automate the generation of your reports, simply navigate to: ‘Add-ons’ > ‘Google Analytics’ and schedule your reports to run as and when you require:
Sharing reports
Sharing reports within Google sheets can be particularly useful for agencies tasked with generating multiple client reports, and indeed in-house marketers who need to regularly report data to their peers.
You can share a Google sheet in just the same way you would any Google Drive file. Simply click the ‘share’ button and input the email addresses of anyone you’d like to grant access to:
Whoever you share your reports with will be able to view the report. However, only individuals with Google accounts that have access to the corresponding analytics account will be able to edit and run reports.
Conclusion
The Google Analytics add-on is a great option for marketers looking to customise Google Analytics reports to suit their specific requirements, removing the need to trawl through the interface on a regular basis. It probably isn’t the perfect reporting solution for everyone, but I’ve found it versatile enough to add value to most web reporting processes, without the need for in-depth technical fluency.
Once you’ve taken the time to understand how to retrieve data properly from the Analytics API, you’ll be free to organise your reports as you like and will be capable of building a totally automatic and self-updating dashboard specific to your needs.
