5 important questions before you create your Google Analytics KPI Dashboard
You have been given the task of creating one or more dashboards for the online marketing team using Google Analytics as your primary data source. The following tools may be available to perform this task: Excel, Tableau, Power BI. or any other business intelligence tool. You decide to create your individual departmental reporting from Google Analytics in Excel and replace it as a dashboard in Tableau after successful internal reconciliation. The following questions play an important role and should be carefully considered before every dashboard creation:
- What data from Google Analytics do I need as a starting point for my reports?
- How do I get the data from Google Analytics into my Excel, Tableau or Power BI project?
- How do I calculate my most important Google Analytics Management KPIs?
- How do I maintain the data on an ongoing basis without creating additional manual effort to maintain the dashboards?
- How do I organize my dashboards and for which user groups should I create them?
Preview some general information about Google Analytics
Google Analytics is the most frequently used web analytics tool to track and analyze user data of a website. As a rule, Google Analytics is free of charge for websites with low to medium traffic. For websites with e. g. more than 100,000 sessions per day, it can also cost something. Google calculates so-called hits, i. e. every time a user performs an action on the website, an event is transmitted to Google Analytics Server. They should also pay close attention to data sampling in Google Analytics. Further details and how to fix it can be found in our article: Avoid Google Analytics data sampling in reporting
Question 1: What data from Google Analytics do I need as a starting point for my reports?
To answer this question, you should first think about the structure of your source table. Google divides the data into metrics and dimensions. Which is briefly explained in the following example:
The dog of Thomas wears a GPS collar which transmits data about the current location and other information to the smartphone of Thomas every second. Let’s assume that Thomas wants to get a message when his dog moves more than 1,000 meters from the property. The collar thus transmits the current GPS coordinates and the absolute distance to the property every second. The current distance can now be regarded as a measuring point. The coordinates and the current time are dimensions which describe a measuring point. A metric or measuring point is determined by its dimensions such as time or location.
Use of the Google Analytics Metrics Explorer:
So that you can now determine your source table, use the following tool, which Google Analytics Metrics Explorer uses. HERE
To begin, activate the “Only show allowed in segments” field so that you only select dimensions and metrics that are valid from the data model. Background: Valid combinations – Not all dimensions and metrics can be queried together. Only certain dimensions and metrics can be used together to create valid combinations. Select a dimension or metric checkbox to display all other values that can be combined in the same query.
Also, switch to the UI Names mode to get the same naming as in Google Analytics. Next, you can select your metrics and dimensions and check if they are valid.
Limit for a Google Analytics query result:
A maximum of 7 dimensions and 10 metrics can be used in one query. There are a few tricks you can do to increase this number. More about this in one of our later articles.
In our example we use the following query
Dimensions: ga:date, ga:landingPagePath, ga:fullReferrer, ga:deviceCategory, ga:channelGrouping, ga:medium
Metrics: ga:users, ga:sessions, ga:bounces, ga:timeOnPage, ga:newUsers, ga:pageviews, ga:goalCompletionsAll
ga: date = date in YYYYY-MM-MM-DD format
ga: landingPagePath = The URL of the start page of a web session on the website. So say the first page a user has entered on the website e. g. via a Google search.
ga: fullReferrer = The exact path as URL including parameters of an external source page via which a user got to his own page. Here the source of the traffic can be read out.
ga: deviceCategory = To which user device the session was executed. The subdivision is: Mobile, Tablet or Desktop.
ga: channelGrouping = Here traffic sources from the fullReferrer of Google are already pre-categorized and grouped. Here you will find for example: Organic Search Engine, Direct or Paid Search Engine etc.
ga: medium = The UTM medium describes the type of source in itself, for example organic, ppc, etc.
ga: users = The number of unique users that are identified by a cookie in the browser. Problems can arise if users use more than one device, so it is actually the same user. However, this can only be differentiated with great effort. In a later article we will discuss this topic in more detail.
ga: sessions = The number of sessions on the website. A session begins with the entry of a user on a start page of the website. If the user does not perform any action within the next 30 minutes, it expires and is counted as a new session. A session can be treated as a visit. The customer enters and leaves the shop after a certain period of time after having looked at the product range.
ga: bounces = The number of direct jumps after entering or starting a session without an action having been executed before. The user enters and leaves the website without taking any action. For example, click on a web page element or follow another subpage.
ga: timeOnPage = The time in which a user was active on the page, i. e. he/she performed a session.
ga: newUsers = New visitor sessions within 30 days or after deleting the cookie in the browser. Only new sessions are counted.
ga: pageviews = The number of all page views during a session.
ga: goalCompletionsAll = All target trades. These can be defined beforehand in Google Analytics.
Question 2: How do I get my data from Google Analytics into my Excel, Tableau or Power BI project?
When you are ready and know exactly which dimensions and metrics you want to load from Google Analytics, you can go to the step and load the data into your Analytics tool. Many business intelligence tools offer ready-to-use interface editors for this purpose. For all tools you should pay attention to the sampling level. Read more in the following article: Avoid Google Analytics data sampling in reporting
To avoid data sampling I recommend to use the Google Analytics Custom Reports and create a table there. This can then be downloaded as an “Unsampled Report” in the form of a CSV. You can use this CSV document again as an Excel data source.
Question 3: How do I calculate my most important Google Analytics Management KPIs?
Once you have loaded your data into Excel or another tool, you can calculate your most important key figures (KPIs). In Excel you should use “Calculated Metrics” in the Pivot section. The following KPIs can be calculated from the above-mentioned data set.
Bounce Rate [percent] = ga: bounces / ga: session
The ratio of direct jumps and sessions indicates whether or not a user interacts with their content after entering the site. These KPIs can draw conclusions about user targeting and the attractiveness of content. A good bouncerate could be between 10 – 30%. If this is clearly less than 10% you should check if the tracking has been validated at this point.
Average session time [minutes] = ga: timeOnPage / ga: session / 60
This Google Analytics KPI shows the average time a user spends an active session on the site. Determine the average session duration for your shop or website that correlates with a high conversion rate. This session duration can now serve as a benchmark for further sessions of specific entry pages.
Average page views per session [decimal] = ga: pageviews / ga: session
Also called side depth. A user navigates through different number of pages until he successfully makes a purchase or converts in any other way.
Conversion rate [percent] = ga: goalCompletionsAll / ga: sessions
Shows how many percent of the user sessions have been successfully completed. Usually this rate is between 2% – 5% in some shops even higher, sometimes between 17% – 20%. The conversion rate depends, among other things, on the quality of the shop but also on the industry and the product portfolio.
Share of new visitors to sessions [percent] = ga: newUsers / ga: sessions
A KPI to evaluate how high the percentage of new visitor sessions is.
Sessions per user [decimal] = ga: sessions / ga: users
Helpful to find out if users make a single or multiple purchase or purchase on the site. This KPI also depends on the product range and the industry.
Note: Make sure that there are no zero values in the denominator. This can happen when you filter the data in a certain level of detail.
Question 4: How do I maintain the data continuously without creating additional manual effort for maintaining the dashboards?
It’s best to create some kind of import process for your downloaded CSV file. This can be easily created in Excel with Power Pivot, for example. Most BI tools also offer automated updating. Try to keep the steps as small as possible at this point and try to work with an ETL tool if possible. For example, if you need not only the date but also the calendar weeks, months and years. This has already been automatically integrated in one of the newer Excel versions. If not, simply create some sort of auxiliary table that links it to the date. The best way to do this is to create a Power Pivot data model.
Question 5: How do I organize my dashboards and for which user groups should I create them?
Do you already know your dashboard users and the needs of your department or teams? All the better, then this task is easy for them. The best way to organize your target groups is to define exactly one Google Analytics query per group, see step 1.
The following groups might be interesting for you:
Traffic Channel Report: This web report provides you with an overview of the performance of traffic sources.
Device Report: This report allows you to analyze the performance of your user devices such as mobile, desktop and tablet.
Landingpage Report: Get an overview of the most important key figures for all your subpages.
Conversion Report: Here you get all important information about your online sales, leads and deals.
The whole thing sounds awkward and time-consuming. I agree with you, but there are alternatives where this effort can be saved 100%. If you do not want to carry out all these steps manually, you can also take a closer look at our software solution ABIS. We offer a cloud business intelligence platform that allows you to easily install reporting apps and get ready-made dashboards with your data without any additional work.
For example, here you will find a Google Analytics Traffic Reporting App with turnkey report templates: HERE.