Google Analytics and Google Sheets – Automate your Google Analytics reports with Google Sheets.
Automation is a hot topic these days, saving marketers and other professionals time and effort. Google Analytics does offer the ability to schedule automated email reports and dashboards, however, it can be difficult and tedious to combine all the data into one complete report. I used to spend countless hours pulling data from several reports and adding to one master report, leaving plenty of room for human error. As a marketer myself, you can imagine my excitement when I learned that you could automate reports from Google Analytics right into Google Sheets. NO MORE MANUALLY INPUTTING DATA!
To be fair, it’s not quite as easy as clicking a button, and then suddenly “magic” happens. It takes a day or two to learn and get comfortable building the reports, but in the grand scheme of things it’s saving hours of data entry, and it’s always correct (assuming it’s configured properly). It’s also worth noting that I wouldn’t rate myself as an advanced Google Sheets or Excel user, so if you’re already really comfortable building multi-sheet formulas this should be a breeze for you.
In this blog I’m going to walk you through how to automate a report in Google Sheets that reports on:
- Total amount of inbound calls by source
- Goals completed for inbound calls
- Combining those two metrics above into a single, automated report
Before we create a report, let’s review the basics of the Google Analytics Add-on in Google Sheets.
Overview of how to create a report:
- Create a new Google Spreadsheet.
- Navigate to the add-ons tab in the main toolbar (between Tools & Help).
- Select “Get Add-ons”.
- You can either scroll to find Google Analytics or type it into the search bar.
- Install the Google Analytics Add-on.
- Navigate back to the add-ons section and select Google Analytics > Create new report.
- A pop-up will appear asking you to:
- Name your report – *TIP- I would recommend keeping this short, to the point, and in all lowercase, as you will need to be referencing it in your formulas.
- Account – This will list all the Analytics accounts you have access to- select the one you’d like to generate reports for.
- Property – This lists all the property in a selected account.
- View (Profile) – Lists all the views in the property.
- Lastly, you’ll have to select your metrics and dimensions for the report.
- Select “Create Report”.
Creating the Report:
First, let’s create a report for the total number of events by Category, Action and with their Source & Medium, by default it will report on the last seven days.
- Create a new report.
- Name it “call events”.
- Select the appropriate Account, Property & View.
- The Metric should be: “total events”. You can type into the box for the metric you are looking for, the metrics will mirror the metrics in Analytics.
- The Dimensions should be: event category, event action, and source/medium.
- Click “Create Report”. This will bring you to the master sheet called the “Report configuration”. Navigate back to the Add-ons>Google Analytics and click “Run Report”. This will create a new sheet with the report we just configured. You likely have several different types of events reporting here. To fine-tune it to only report on call events, navigate back to the Report Configuration sheet. From here, there is filter row which will allow us to apply a filter to the report- this does require basic regular expression knowledge. We know we want to filter the events to only the category of “Calls”, which would look like this: “ga:eventCategory==Calls”. Run report again. This time, it should only report on the events with the category of “Calls”.
- Next, we are going to create a second report by heading back to the Add-ons>Google Analytics>New Report. For this report, we are going to set the metrics to “goalXXcompletions” and click “Create Report”.
- Next, you’ll need to go into your analytics and find the goal #. This can be found in the Admin Panel>Goals. Replace the XX with the goal number you’d like to report on. See here how to create a Calls goal in Analytics.
- Navigate back to the Add-ons and run report.
- Now you should have two reports, one reporting on the total number of inbound calls and the other reporting on the goal completions for those calls.
- To combine the reports, create a new sheet and use the formula of =’call events’ !A15. This will pull the data from the other sheet and report it in the new sheet. Do the same thing for the goal completions and BAM, you’ve combined the reports. This new sheet will automatically update every time the report is run.
- In order to automate the reports, navigate to the Add-ons>Google Analytics>Schedule Reports. From here, you can schedule the reports to automatically run in time frames, like hourly, or on a select day at a select time.
This is a basic example of how to use the Google Analytics add-on in Google Sheets. The platform also offers the ability to build out very robust reports with charts and graphs that can be published for real time viewing. I hope you found this demonstration informative and exciting, as I did. There are several other resources available for building reports in sheets, and if you’re looking for more in-depth information, I would suggest Googling it.