Posts

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-onsGoogle 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-onsGoogle 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-onsGoogle 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 FormatNumber 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.

This is the fourth lesson of my Google Sheets for Marketers mini-course. A typical task every marketer has to face at one point or the other is to forecast things such as sales, revenue, conversions or something similar. Often this comes up when you have to create a robust marketing plan. Or when you have to present forecasts to superiors and stakeholders to secure budgets.
 
One challenge that usually comes up during the forecasting process is how you can predict the numbers as accurate as possible without using too complicated and time intensive methods.
 
This tutorial describes how to use simple linear regression (no worries, no math needed. There is a simple formula in Google Sheets for that) and formulas to forecast marketing channel revenue. As usual I’ll go through everything step-by-step and explain it all. But I recommend having open the mini-course workbook (please make a copy) to work along to really understand everything.

 
What you’ll learn:
The basics of the Google Sheets QUERY function, FORECAST formula.

 

The task

In the past lessons of this course we analyzed the data from three advertising channel and learned that Twitter Ads are performing quite well. You think it would be a good idea to raise the budget of those ads to improve revenue. However you first have to convince your stakeholders and superiors of this to secure the budget. Part of this you have to forecast the potential additional revenue to prove the impact of more Twitter Ads budget. As such the task is the following:
 
If we rise our investments by 20% compared to last year, what will be the revenue from Twitter Ads on a monthly basis for the next year?
 
There are two implications from this question:
 
  • It won’t be enough to give a total estimate for the next year, but we have to give a forecast for each individual month.
  • We have to forecast at least 12 months into the future.

Preparing the data

Again I highly recommend to work along in the workbook, you’ll also find a solution sheet to this lesson there. As a first step we want to get the data we are going to work with into our worksheet. In our case the Twitter Ads data.
 
Of course we could simply copy the data from the Raw Data sheet and delete everything besides the Twitter Ads data. However this approach would get messy very fast, when working with large Data Sets. Instead we are going to work with the Google Sheets Query function. This function is probably one of the most powerful functions overall. It is kind of a pseudo SQL, which let’s you query, manipulate and analyze very large data sets in Google Sheets.
 
We won’t go into depth here and you’ll only learn a simple case on how to use the formula. However believe me, knowing this function will probably make you stand out among 97% of all marketers and it will seem like magic for those who don’t know it.
 
Put the following formula into cell A1 in your new sheet:
 
=QUERY(‘Worksheet – Raw Data’!A1:J118, “SELECT A, B, H, I WHERE A = ‘Twitter Ads'”,1)
The function =QUERY() has three input parameters. The first one ‘Worksheet – Raw Data’!A1:J118 defines the dataset you want to work with and which you want to query. In our case all the raw data. “SELECT A, B, H, I WHERE A = ‘Twitter Ads'” is the actual query. What you are doing here is you are selecting columns A, B,H and I. The WHERE statement introduces a condition and lets you only select those rows, where the cell in column A equals Twitter Ads. The last parameter 1 simply indicates that there is a header row, which we want to display as well.
 
As such we build a simple very fast filter to only select the data we need. Feel free to play around a little bit with the Query function. You can add more conditions with an AND behind the WHAT the statement. E.g. SELECT A, B, H, I WHERE A = ‘Twitter Ads’ AND H >1000 would give you the same as above, however only those rows, which have a revenue higher than $1000.
 
We are almost done with preparing the sheet. Just add Cost Forecast and Revenue Forecast as headers in cell E1 and F1. Write Twitter Ads in A41 and =B40+31 in B41. Copy both down until row 52.
 

The actual forecasting

We will have to do two things here. Plug-in the future potential budgets and forecast the resulting revenues.
 
The first one is quite easy. As the task states we will have a 20% higher budget compared to the last 12 months. So we will simply multiply the costs of each of the last 12 months with 1.2 in order to get the investments for the future 12 months. As such write the following into E41 and drag it down for the other months:
 
=D29*1.2
 
Next we’ll do the revenue forecasting itself with linear regression. It is easier than you might expect as there is a formula in Sheets exactly for that. Write this into F41 and again drag it down for the other future months:
 
=FORECAST(E41,C$2:C$40,D$2:D$40)
 
What the FORECAST formula does is it predicts through linear regression a future value by using existing values.
 
In our case the predicted value is revenue (y-value) for a given cost (x-value). The existing revenue values (y-values) are in cells C2:C40 and existing cost values (y-values ) in cells D2:D40.
 
I added $ dollar signs in above formula in order create an absolute references. So when you drag the formula down it won’t change the referenced existing values.
 

Visualization

You’re done! Or at least almost. Technically you are done already – you predicted revenues for Twitter Ads for the next 12 months. However nobody wants to read a boring table and drawing insights from that table is very difficult. So the last step to take is to build a chart on top of your analysis.
 
Insert a chart from the menu: Insert > Chart and change the chart type to Linear chart (Need a refresher on charts? Check out lesson 3):
 
Add the following two series:
 
Series 1 – C1:C52
 
Series 2 – F1:F52
 
and as X-axis: B1:B52.
 
You’ll probably notice now that there is an ugly and confusing gap between the lines of the historic data and the forecast. In order to get rid of that add the following to cells F40:
 
=C40
 
This will level the start of the forecasts to the actual revenue.
 
That’s it! Obviously you can change the colors of the lines (which I did in above screenshot and the example sheet) to make it look prettier and more organized.
 

Limitations

Certainly above is a very simple solution for forecasting revenue as there are a lot more advanced techniques such as the moving average forecasting method. We are also not counting in any other influencing factors such as seasonality, competition etc..
 
Nonetheless above is a valid demonstration on how a simple data science technique (linear regression in this case) can be applied to real life marketing challenges by everyone. You could use above methods for all kind of marketing forecasting, e.g.:
 
  • How will the the next days outside temperature affect my sales?
  • How will the number of blog posts affect my website traffic?
  • Will conversions go up or down during winter months?
 
In the last lesson of this mini-course we will build a dynamic reporting dashboard, which pulls its data automatically from Google Analytics. So be sure to don’t miss out lesson number 5.