GA Performance Reporting Dashboard

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.

Google Sheets charts for visualizing marketing data Header
This is the third lesson from the Google Sheets for marketers mini course. In this chapter you’ll learn how to implement and customize charts to visualize your insights. As the famous saying goes “a picture is worth a thousand words.” Visualizing data will be useful in itself as it makes finding actionable insights in huge data sets a lot easier. Furthermore it is essential when you want to define and communicate next step recommendations based on the data to other stakeholders or your clients.

 

What you’ll learn:

Regular charts, combo charts with two axis, chart trendlines, SPARKLINE, custom change indicators, interactive charts.
 

 

Visualizing Budget Utilization

As for the other lessons this workbook contains all exercise data (please make a copy to work a long).
As an easy start you are going to visualize the 2018 budgets vs actual costs for the three advertising channels: Facebook Ads, Google Ads and Twitter Ads.
Select cells A1 to C4 in sheet Solution – Charts. Now choose Insert → Chart in the top menu.
 
 
You’ll get a simple column chart comparing budget utilization for the different channels. If you want to you can customize it further by clicking on the tab Customize on the right side in the Chart editor. E.g. click on one of the red bars and choose another color than red in the dropdown menu in the chart editor on the right side.
 
This is basically how it works for all elements of the chart. Click on it and customize the style on the right side. However we won’t focus on these fundamentals in the following, but will rather do some little bit more interesting stuff in this lesson (if you want to learn the fundamentals, check out Google Sheets help).
Btw. if you ever wondered, which chart you should choose in general have a look at this awesome infographic from A. Abela.

 

Average Order Value vs. Conversions

As we learned in the past lessons the Facebook Ads performance has been quite bad due to low Average Order Values and the low number of Conversion. As such we want to visualize this to show the development over time and to potentially communicate this to other stakeholders. You are going to use a so called combination chart for this. In a combinations chart you can display two series using different formats (e.g. bar chart and line) as well as two axis.
 
First insert a pivot table by clicking into any cell in the Worksheet – Raw Data tab and choosing Data → Pivot table from the top menu. Select Existing Sheet and write ‘Solution – Charts’!A21 into the empty box before clicking Create.
 
Choose the following settings in the pivot table editor:
Rows: Month (Deselect Show totals)
Values: Conversions, Average Order Value
Filters: Channel and only select Facebook Ads
 
(If you are not sure what to do here or need a recap check out my first lesson on pivot tables)
 
Select cells A21 to C60 and insert a chart via the top menu (Insert Chart). Now on the right side in the first dropdown menu of the chart editor under Chart type choose Combo chart. It is the third item in the first row.
 
 
Delete Month as a series by clicking on the three dots next to Month on the right side and choosing Remove. Instead choose Month as the X-Axis by clicking in Add X-axis above Series and selecting the range A21:A60. Click OK.
 
 
You have your first combo chart! However Average Order Value is a little bit hard to read as the left axis shows a number (not currency) for both Conversion and Average Order Value.
 
As such click on the red line. On the right side below Axis choose Right axis instead of Left axis. This will add another vertical axis on the right side showing currency values.
 
 
While it’s quite clear to see that there is a strong drop in Average Order Value, it’s unfortunately not as clear for Conversions.
As such click on one of the blue bars add a trendline by checking the box next to Trendline on the right side to visualize it better.
 
 
You now have a ready chart, which you could use to visualize your insight that Facebook Ad performance is going down. As such it is ready to be copied into a slide deck, email or wherever you need it.

 

Comparing Facebook Ads year-over-year performance

 
Sometimes you don’t want to build a full chart, but rather want a quick visualization of the data in Google Sheets itself. E.g. in our case we quickly want to compare the year-over-year performance of the Facebook Ads. Write the following in the respective cells:
 
2016 in cell A14
2017 in cell A15
2018 in cell A16
2019 in cell A17
Seasonality in B13
 
In cells B14 to B17 we will use the SPARKLINE formula to visualize number of Conversions over the months in the respective years. Sparklines are mini-charts which exist in a cell itself. They are perfect to visualize trends or seasonality. Write the following formulas:
 
=SPARKLINE(B22:B32) in cell B14
=SPARKLINE(B33:B44) in cell B15
=SPARKLINE(B45:B56) in cell B16
=SPARKLINE(B57:B60) in cell B17
 
 
We also want to visualize the trend in year over year growth in the most recent month April. As such write the following in the respective cells to calculate year over growth for April of each month:
 
YoY April in C13
=B36/B24-1 in C15
=B48/B36-1 in C16
=B60/B48-1 in C17
 
Select cells C15 to C17 and in the top menu click on FormatNumberMore Formats Custom number formats. In the popup paste the following into the box and click Apply afterwards:
 
[color50]0% ▲;[red]-0% ▼;[color40]0% ▬
 
 
What this does is it will apply the colour 50 (=green) and the symbol ▲ to all cells with a positive value, the colour red and the symbol ▼ to all cells with a negative value and finally the colour 40 (=yellow) and the symbol ▬ to all cells with 0% as value. Those symbols are actually interchangeable with whatever symbols you like. E.g. you could also use ↑ , ↓ and ↔ instead. Same goes for the colours.

 

Building an interactive chart

 
In the last lesson you built a small tool for comparing the performance metrics for two advertising channels. We’ll use that to build a dynamically controllable chart.
 
Write the following formulas in the respective cells:
 
=B8 in cell B10
=C8 in cell C10
=B9 in cell B11
=C9 in cell C11
=A9&” for “&B8&” is “&round((B9/C9),1)&” times the “&A9&” for “&C8 in cell A11
 
 
Even though it looks fancy the last formula does nothing else besides linking different cells and text (everything between two quote marks is considered text) with each other. Select cells A11 to C11 and change the Text color to white.
 
Next select cells A10 to C11 and insert a chart via the top menu. Switch rows / columns by checking the box next to it in on the right side in the chart editor.
 
 
Now go to the Customize tab on the right side of the Chart editor and open Legend. Choose Top in the drop down menu for Position.
 
 
Now try choosing something different in cells A9, B8 and C8. The chart including the legend will update instantly and dynamically! Obviously styling and formatting could be improved. However you get the idea and can imagine how interactive dashboards could be build with these techniques.
 
We will actually use some of these to build an interactive dynamic performance reporting dashboard (which you can share directly with clients or other stakeholders) at the end of this course in lesson 5.
 

Ecommerce KPI Hero

Imagine you just started a new job and are leading marketing for a small or newer ecommerce business (or alternatively you are the marketing analyst of the department). Before you start your marketing efforts ƒyou are tasked with defining a few ecommerce KPIs for tracking the success of your marketing campaigns. The only tracking tool you are using is Google Analytics.

Sounds scary right? With no starting points to begin with this sounds like a huge and complex venture as there are so many potential KPIs to choose. In addition you are probably scared that as you don’t know an immediate answer, people will soon start asking how somebody like you was hired to lead marketing (or be an analyst respectively). And as such not long until you get fired or replaced by somebody else.

So what if you had a concrete KPIs framework to follow for ecommerce marketing. You would know exactly which marketing efforts work and what to focus on.  All leading to skyrocketing sales.

That’s what this guide is for. Step-by-step it will show you how to systematically develop a ecommerce KPIs framework directly derived from your business objectives. No other tool besides Google Analytics will be necessary for this. At the end of this article you’ll also find a done-for-you custom report template, which you can instantly add to Google Analytics in order to use your new KPI framework.

Getting started

This guide and framework relies heavily on the work from Avinash Kaushik and is adjusted for an ecommerce context. Be sure to check out his blog, if you haven’t.

One of the reasons why setting up a marketing ecommerce KPIs framework can be so overwhelming is that there are hundreds of metrics to choose from. E.g. should you focus on bounce rate or clicks? Are conversions the only metric to look at? Or are others equally important? In this guide you will reduce the large number of metrics you could look at, while focusing only on those, which are really important for your ecommerce business.

In general when setting up a marketing KPIs framework you should always make sure to track and cover all of the following areas of the consumer journey with your KPIs:

Acquisition: How will users be acquired?

Behavior: What should the users do on the website?

Outcome: What’s the final desired result you want to achieve?

These are the guiding questions you should alway keep in mind when designing your new ecommerce KPIs framework. The setup of the framework itself then follows five steps:

  • Step one is to identify the business objectives of your company. Why does it actually exist?
  • Step two is to identify goals for each business objective. Think of goals as steps to take to reach the objectives.
  • Step three is to define the key performance indicators. So basically metrics that help you understand how you are doing.
  • Step four is to identify targets for each KPI, indicating, if you are succeeding or failing.
  • Step five is to identify segments of people / behavior / outcomes that can be analyzed further.

Step 1 – Define the business objectives.

Most ecommerce businesses have three objectives in common: They want to grow and as such increase their sales revenue. They want their brand to become more well known and doing so build brand awareness. And last but not least they to want build an audience or more specifically an email list, which they can nurture.

Obviously not all of those objectives must be relevant for you and you can decide to use only part of below metrics. However those objectives are usually the common denominator for the majority of smaller or newer ecommerce businesses.

ecommerce kpis - business objectives

Step 2 – Identify goals for each objective

Goals are basically the specific levers you have to pull to accomplish your business objectives.

Business Objective 1: Increase sales revenue

As you want to increase the absolute number of sales as well as the efficiency of your sales process we will work with three different goals here.

As a first goal we want to increase the number of orders, second we want to improve your conversion rate in order to convert the website traffic more efficiently and as a last we want to increase your average order value in order to rise customer value.

Business Objective 2: Create brand awareness

We want to get your brand more well known and more people visiting your store. As such we will set two goals here. First we want to get more visitors overall to your website and second more specifically we want to increase the number of new website visitors.

Business Objective 3: Generate leads

Last but not least we want to build an engaged audience with an email list that you can nurture. Therefore your last goal here is to increase newsletter sign-ups through the website blog.

ecommerce kpi goals

Step 3 – Define ecommerce KPIs for marketing

For each objective we will need metrics, which tell us how you are doing against each objective and goals respectively. Those metrics are called key performance indicators (KPIs).

Business Objective 1: Increase sales revenue

As we want to increase number of sales, sale efficiency and average order value we will also define your KPI’s accordingly. Therefore the metrics to focus on here are Transactions, Avg. Order Value and Ecommerce Conversion Rate.

Business Objective 2: Create brand awareness

Not only do we want more visitors coming to your website, which is why we track number of total Users as one KPI but we specifically want new visitors. Ergo the second KPI is number of New Users.

Business Objective 3: Generate leads

As your goal is to build an audience or email list, we will be tracking the number of newsletter Sign-Ups as a KPI here.

ecommerce kpi kpi

Step 4 – Set targets

It is important to set targets for each KPIs upfront in order to have indicators of success or failure. Failing to set those will make it impossible to determine, if your marketing campaign was successful or not. It is not possible to generalize what the targets should be for you business specifically. E.g. increasing sales by 100 orders could be huge marketing success for an online store just starting out but a marketing campaign failure for a store, which makes millions in revenue already.

There are three main options to determine targets. Either you look at historical performance, e.g. “we had 100 orders at this point last year, which is why we want to have 120 this year”.

Or, if you don’t have historical data you can look at ways to substitute that data. E.g. is there a competitor, which sales you can estimate? Or numbers from a similar product you have been selling before?

Last option is to ask internal or external people. E.g. internally you could ask people, who have been working there before you such as management or the founders. If those don’t exist or aren’t available, try to find external sources as for example your clients (for validation), consultants or other ecommerce people to approximate sales and other metrics.

I know this step might seem intimidating. But setting any target is better than no target at all. If you still don’t have a clue how to set your targets, try to set a number, that if when reached won’t make you feel too bad. The more experience you get the better your targets well be. As such just set a number to start with and revise it as time goes by and you get more data to base it on.

ecommerce kpi target

Step 5 – Create segments to analyze

There are probably a few people, who will stop thinking about their marketing analytics framework after step 3 and as such not set any targets. But I bet that the majority will wrap it up after step 4 the latest and will not define any meaningful segments.

Segments can be anything from the traffic sources, your user behavior onsite, the kind of users attracted. Basically anything you can segment your KPI metric reports with and which will tell you more on how to achieve your business objectives.

Segmentation is the only way to give your KPIs a meaningful sense and make them actionable. E.g. it is nice to know that 30% of your visitors are new users, however this insight becomes truly actionable, when you know that 100% of those come from PPC advertising.

We will define the following segments for your ecommerce store:

Source – Where did the users come from? Social Media, referral, organic, etc.? This segment is relevant for all your KPIs. As such you could ask, which sources drive the most sales or where most new users come from. As somebody working in marketing this will probably one of the most important segmentations for you in order to decide, which marketing channels to focus on.

User Type – Should you focus on new or existing users? E.g. you can track which group accounts for most revenue or which source refers the most new users. You can easily focus your marketing efforts accordingly on either group by looking at this segmentation.

Page Title – Segmenting by pages allows you to see which content performs best. As such you can answer questions, as for example which blog post had the most newsletter sign-ups or which article had the best performance in terms of user acquisition.

As you can see segmentation is the final step to make your KPIs actionable. You will know exactly what type of traffic source, content, etc performs the best and where you should focus. Feel free to add your own segments, depending on what makes most sense for you.

That’s it! We went through a structured approach to determine which KPIs to use, derived directly from the business objectives. We kept in mind the acquisition of customers (user metrics), the behavior they should show on the site (newsletter signups, conversion rate) as well as the outcome we want (conversions and transaction value), while having a relatively low overall number of metrics to track.

ecommerce kpi target

Resources

As promised above is the custom report, which you can add directly to your Google Analytics account in order to track above six metrics. Please keep in mind that you have to set up ecommerce tracking correctly to use it and make sure you are tracking newsletter sign-ups via event tracking (I used Goal 3 in the template).

Furthermore this is a Google Sheets template, which you can use to track actuals vs. target numbers for your KPIs for the next months.