How to Automate Google Analytics Report with Google Sheet & Looker Studio

how to automate google analytics report

Prerequisites

In order to build a Google Analytics monthly report, you will need the following:

Google Sheets – This is free from Google and is an alternative to Excel. We will be pulling data into Google Sheets.

Google Analytics Add-On – This tool allows you to connect your Google Analytics account to Google Sheets. To make things a lot easier, there is a wizard that comes along with the tool.

Google Data Studio – This is a data visualizer that is still in the beta stage. When you link your Google Sheet to the Data Studio, you can create reports that are easy to parse when looking for a number of KPIs.

Starting Your Automation

The first thing you need to do is to define what you need in the final report. There are many templates that you can start using. The reason is that without a template, you will feel overwhelmed.

understanding month over month performance

Setting Up Your Google Analytics Monthly Report

Now, install the Google Analytics add-on and give it permission to access your analytics data. In order to create a basic report, you can use a basic wizard that allows you to set basic report parameters.

You can also employ the text search to identify metrics or dimensions. You will also find that the sections of the wizard are quite self-explanatory. 

Metrics and Dimensions

There are many metrics and dimensions for Google Analytics. For this report, however, let us look at a few that apply to both blogs and corporate websites.

Session Level Dimensions

Ga:month – Session level reports only deal with metrics. However, the initial set up requires a dimension. This is why it is important to set up a time-based dimension.

If the report is monthly, the month is treated as the dimension. 

The metrics include sessions, users, percentNewSessions, avgSessionDuration, pageviews, pageviewsPerSession and goalXXcompletions.

Top Pages Dimensions

ga:pageTitle – This dimension can be substituted for another if necessary. However, the pageTitle dimension will get the meta title for each page, allowing you to determine how specific posts and landing pages are doing.

The metrics include sessions, users, percentNewSessions, avgSessionDuration, pageviews, pageviewsPerSession, uniquePageviews, avgTimeOnPage and goalCompletionsAll.

Create Summaries and Schedules

You really don’t have to do this step. Doing it, however, makes the report collation a lot easier. It is possible to create compiled reports using Data Studio although this can be messy.

For top-performing pages, create a table that places the dimension ga:pageTitle in column A using the formula =(‘**REPORT TITLE**’!A’).

If instead, you are doing time-based calculations, you want to convert the returned numbers into standard time in hours, minutes and seconds. You can use the formula =sum(‘**REPORT TITLE**’!C-/86400’). Then choose hh:mm:ss as the cell display.

Import Data into Google Data Studio

Now you can finally visualize the report for your audience’s consumption. When you open Data Studio, choose “New Report,” give it a name, create a data source by clicking “Create New Data Source.” Select Google Sheets and navigate to the saved list. Click Connect.

Now, select the chart type and sketch out the size of the chart you want to be created. Choose your dimensions and metrics to create different reports in no time. 

Conclusion

Even though the process of setting up Google Analytics Monthly Report can be a lot at the beginning, it pays dividends once set up. Use this article as a guide through this process.

Geeks of Digital is an analytics-driven online marketing and content production agency. We are experts when it comes to Google Analytics. Get in touch so we can use Analytics to take your business to the next level.