This is the last lesson from my Google Sheets for Marketers mini-course. In this lesson you’ll build an automatically updating reporting dashboard connected to your Google Analytics account.
Importing Google Analytics Data into Sheets will allow you some more flexibility for your data analysis and reporting (compared to viewing the data directly in Google Analytics). E.g. you can query and report data from multiple views. Schedule automated data updates. You’ll be able to manipulate the data with custom calculations. You can create visualizations and embed them on third party website. And last but not least you can control who sees your data and visualizations with Google Sheets’ sharing options.
The later one is especially helpful when you are working with other stakeholders or if you work in an agency and want to share something with your clients. E.g. you could pull only the most important metrics from Google Analytics and share them in a chart or other data visualization.
What you’ll learn:
Connecting Google Sheets to Google Analytics, Building an automatically updating dashboard
Google Analytics Spreadsheet Add-on
As for the other lessons you’ll find a solution for every step in this guide in the Google Sheets for Marketers workbook and I highly recommend to make a copy and to work along.
Before we start you have to install the Google Analytics Spreadsheet Add-on. This little plugin will let you pull all your Google Analytics data into a Google Sheet. As such you’ll need a Google Analytics account. If you don’t have a Google Analytics account you can just read along for now and work with the sheet Report Raw Data in the above mentioned Google Sheets for Marketers workbook.
After you have installed the add-on open a new spreadsheet (or open above workbook).
In the top menu choose Add-ons → Google Analytics → Create a New Report. This will open a sidebar on the right side which will allow you to configure the data, which you want to pull into your spreadsheet. For our example we’ll choose the following settings:
Name: Report Raw Data
View: Whatever you prefer for your account
Metrics: Users, Goal Completions
Dimensions: Source, Medium, Country, Date
Segments: All Users
Afterwards click on the Create Report button. A new sheet called “Report Configuration” will be created with the details you just entered. You can change those details manually by changing the cells before you run the actual report. E.g write 90daysAgo into cell B4 to get data from the last 90 days. The sidebar assistant we used before is actually quite superficial in what it allows you to configure. You can have a look at the reference to see what else you can configure, but we’ll leave it for now.
If you want to create additional reports, just repeat the steps above. Every additional report configuration will appear in a new column to the right of the previous report configuration.
Before we run our report for the first time, we’ll set it to run automatically so our dashboard gets updated automatically. In the top menu click on Add-ons → Google Analytics → Schedule Reports. Check Enable reports to run automatically and click on Save.
Now it’s finally time to run the report. Click on Add-ons → Google Analytics → Run Reports to pull your Google Analytics Data into the designated sheet.
Preparing the data
The following will describe the basis and first steps on how to build a dashboard. The dashboard itself we are building won’t be too sophisticated. The following guide rather provides basics so you know how to work with the data to build dashboards yourself.
If you don’t know which metrics you should be reporting for your business or clients check out Kaushik’s great DMMM framework. Or if you work in an ecommerce business my guide on ecommerce marketing KPIs.
You have to do some data preparation first to build the dashboard. However it’s nothing too complicated. First open a new sheet. You can’t work directly in the Report Raw Data as it will get updated automatically and everything in it gets overwritten. In above solution workbook we will be using the Solution – Dashboard sheet.
We are building an interactive dashboard, which you can control via dropdown menus. As such we are going to need the options you can choose in each dropdown menu. You will use the UNIQUE formula for this. The UNIQUE formula pulls all uniques values from a specified range.
Type in the following:
Medium in cell N3
Country in cell O3
Month in cell P4
=UNIQUE(‘Report Raw Data’!B16:B) in CellN4
=UNIQUE(‘Report Raw Data’!C16:C) in CellO4
=UNIQUE(‘Report Raw Data’!E16:E) in CellP4
Next we’ll add the drop down menus via data validation. Click on cell D4 and then choose Data → Data Validation in the top menu. In the box next to “List from a range” write Solution – Dashboard’!O4:O. Do the same for cell E4, however write ‘Solution – Dashboard’!N4:N into the box.
This gives us two nice dropdown menus to control the dashboard later on.
The charts of the dashboard won’t be based on the actual data from the Report Raw Data sheet, but on staged data, which is easier to manipulate with the dropdowns. Type =date(,P4,1) into cell R4 and copy it down until R7. The formula pull the months number and converts it into a data format. Format cells R4 to R7 show the month only via Format → Number → More Formats → More time and date formats.
Next write =D$4 in S4 and copy it until T7. This will pull the data from the drop down menus. Last we will use SUMIFS formulas to only pull the users, which fit to the month, country and medium in that row. As such write
=SUMIFS(‘Report Raw Data’!F$16:F,’Report Raw Data’!C$16:C,S4,’Report Raw Data’!E$16:E,P4,’Report Raw Data’!B$16:B,T4
into cell U4 and copy down until U7. If you need a refresher on SUMIFS, check out the second lesson of the course!
Put =UNIQUE(‘Report Raw Data’!B16:B836) into cell W4 to get all the mediums and =SUMIF(‘Report Raw Data’!B$16:B$836,W4,’Report Raw Data’!F$16:F$836) into X4. Copy it down until you have reached the last row with a medium in it.
Last make the text colour of columns N to X white.
Building an interactive dashboard
This is actually the easy part (have a look at lesson three about data visualization, if you are not sure what to do here). First select cells W4 to X12 and insert a pie chart to show the medium split.
Next insert a line chart in the sheet in cell C13. As X-Axis choose R4:R and as Series U3:U (If you are not sure how that works, check out lesson 3). In cell C8 type =”User from “&D4&” acquired via “&E4 to create an interactive headline for the chart.
Now try playing around with the dropdown menus to see how the headline and the chart changes. Obviously you can make the whole thing a lot prettier by styling it a little bit (for some inspiration you can take a look at what I did in the workbook).
Last you might want to share the dashboard with somebody. You can do that either via the green share part in the upper right corner or you can actually even publish it publicly via File → Publish to the web.
Obviously this is a very simple use case on what you can do when pulling Google Analytics Data into Google Sheets. I.e. you could build a lot more advanced dashboards (even though I would recommend Google DataStudio for that) or do more in-depth analysis with the data itself. Either way it is important to know how to pull the data in the first place, which is shown in the above first part.