Automating Google Analytics reporting via Google sheets
I’m often shocked at how much time some marketers spend trawling through the Google Analytics interface to pull stats together for reporting purposes. By manually extracting data from Google Analytics on a regular basis, you’re using up valuable time that would surely be much better spent on non-menial tasks.
As a digital agency, we have recently delivered huge time savings by automating our client reporting via the Google Analytics add-on for Google sheets, which has allowed us to set up highly customisable analytics dashboards for each of our clients, providing them with a concise overview of their performance at all times.
In this post, I explain how you too can automate the generation of custom Google Analytics reports within Google sheets to display key SEO, PPC, ecommerce, social media and email marketing metrics and projections for your clients or direct reports.
Step 1: Download the add-on
To start pulling data into Google Sheets, you’ll firstly need to download the GA add-on. To do this, open Google sheets and navigate to Add-ons > Get add-ons:
Note that in order to use this add on you must have access to the Google Analytics profile that you wish to report on via the Google account you’re using with Google Sheets.
Once you’ve downloaded the add-on and ensured you have access to the GA profile in question, you’re ready to start creating your first report.
Step 2: Define metrics and dimensions
The first thing you’ll notice after downloading the add-on is the ‘create a new report’ table:
Here you will be able to specify which Analytics property and view you would like to report on and choose which metrics and dimensions to use for your report. This can be further customised after clicking create report, so don’t spend too much time customising your metrics and dimensions at this early stage.
Once you’ve clicked ‘Create Report’, a new sheet will be created 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):
The report configuration sheet doesn’t have drop downs or help options for beginners, so I would strongly advise having the Analytics API reference guide open to identify which dimensions and metrics you need to use in this sheet when creating a new report.
Within the report configuration sheet, there are 6 mandatory fields:
- Report name – what you would like each report to be called
- Type – leave this as is, ‘core’ is the default
- View (profile) – this references the Google Analytics property you originally selected via add-ons > Google Analytics > create new report
- Start / end date – the date ranges 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
In addition to these fields, there are options available to sort the data, apply filters, use pre-defined segments from your GA account, specify sampling levels and limit the number of results returned in each report.
Step 3: Customise and generate reports
To generate a report in Google sheets you must firstly think about what you want to display in your final report. For example, a basic SEO report can be generated to display the following information:
Broken down, the SEO report above displays:
- Data from the last full month vs. YOY. Metrics reported include sessions, % new users, bounce rate, page views per session, average session duration, transactions, revenue and conversion rate
- Top landing pages from the last full month vs. YOY. Metrics reported include sessions, bounce rate, conversions and revenue
- Data from the last 12 months with a month on month comparison and trend line. Metrics reported include sessions, bounce rate, conversions and revenue
To generate a set of reports that will allow you to build the SEO dashboard shown above, you would need to configure your report configuration sheet by setting up the 3 reports displayed below:
The report settings in column E are designed to pull together SEO metrics for our YOY reports, while the settings in columns F and G will provide us with data on the top landing pages for the last full month (column F) and the same month a year previous (column G).
Now, because you will be automating the generation of these reports, it is important that your start and end date settings update automatically. To do this, we would need to enter formulas into the report configuration so they update automatically by using various DATE formulas.
In the example displayed in column E above, 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). To capture all of this data, we need to ensure that the end date automatically updates to the last day of the last full month, and the start date is set 13 months prior to the end date column.
We can achieve this by using the following formula:
- In the End date column, we would set the last day of the most recent 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. To do this, we can use the EDATE function, which returns a date a specified number of months before or after another date. To do this, we would use the following formula (where the ‘end date’ cell = E6): EDATE(E6, -13)
To report on just the previous month’s data (as displayed in column F), you would instead need to use the following formula:
- For the end date, we want to display the last day of the last full month: =if(today()=eomonth(today(),0),today(),eomonth(today(),-1))
- For the start date, we want to display the 1st day of the last full month, which can be achieved by referencing the end date column in the following formula =eomonth(F6,-1)+1
And to compare this to the previous year (as displayed in column G), you would need to replicate that formula, minus a year:
- To do this, we would use the following formula to return a date a specified number of months previous to another date. So for the end date, F6 would refer to the last day of the most recent month: EDATE(F6, -12)
- And for the start date, F5 would refer to the first day of the most recent full month: EDATE(F5, -12)
You will note on the example report configuration screen that I have used a filter on all 3 reports to specify that I only want to report on traffic classed as organic.
This is achieved via use of the following formula in the ‘filter’ row:
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 help with the syntax for these fields, check out this guide for Filters with the API. The filters field allows you to drill down into specific dimensions and metrics.
Once you’ve finished configuring your reports via the report configuration sheet, navigate to the add-on and click ‘run reports’:
Each report you create will now be displayed on a new sheet, however these individual sheets can be hidden once you set up a dashboard to reference this data. This is simply raw data devoid of any formatting, so the next step is to create a ‘dashboard’ tab to better visualise this data.
Step 4: Set up your 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 / 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 (13 rows in total) and used the following formula to automate the text in the table to reference the month prior to the current one, as the final row heading:
To complete the months, we need to roll backwards from the most recent month 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 the example above,
The ‘Year’ column in the table is simply created with the same formulas listed above, but replacing “MMMM” with “YYYY”.
Once you have your date rows set up, you can simply use a cell reference to map the data over to your raw analytics reporting sheets over to your newly created table.
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 and start adding charts and sparklines to visualise the data included in your dashboard tables.
Step 5: Visualise the data
Google sheets allows you to create a wide variety of charts. For example, you could create a chart to display the split of website traffic by source:
You can also create line charts to compare metrics YOY, as we have done to compare all website sessions vs. YOY:
To create charts in Google sheets, I would recommend creating a new sheet (which can later be hidden) purely for the purpose of referencing the data you want to include in your charts. This will allow you to use attractive headings for your metrics rather than the standard ‘ga:sessions’ headings in the individual report sheets.
For example, to create the pie chart displaying the split of traffic in the last full month, I created the following table within a charts sheet and referenced the data from a report created to measure sessions split by channel:
In addition to creating basic charts, you can use the charts function to map projections vs. YOY data. For example, we have started adding projections into some of our reports by using the AVERAGE function to estimate data for the year ahead based on average YTD performance:
Step 6: Automate
Once you’ve created a set of reports and customised your dashboard, you can automate the generation of your reports using the Google Analytics add-on settings. 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, navigate to: Add-ons > Google Analytics and schedule your reports to run as and when you require:
You can then share this 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:
Bonus step: Explore other APIs
It is no secret that Google sheets is a great tool for collating data from multiple sources using APIs. Major keyword tracking providers such as Authority Labs and STAT provide access to their APIs, so there is no reason you can’t attempt to collate all relevant SEO data into a single Google sheet for your clients or bosses.
As a digital agency, we have delivered huge time savings by moving to this method for our monthly reporting, and at the same time have been able to provide each of our clients with customisable analytics dashboards which are accessible via a single Google sheets URL at all times.
Working client-side, I often found that we had to tailor any reports delivered by an agency into an ‘internal’ format, so bonus points for any agencies who work closely enough with their clients to deliver a dashboard they’re happy to use to display all the digital marketing metrics they need to review.
Of course, this reporting format is not solely for agency use. If you are an in-house marketer there’s no reason you can’t build a customised dashboard and share it with key stakeholders within your business who are interested in reviewing certain metrics within GA.