Consider 5 important questions before creating your google analytics KPI dashboards
You have been given the task of creating one or more dashboards for the online marketing team using Google Analytics as their primary data source. You’ll also need to compare different Google Analytics Views (Websites) in one comprehensive report? To perform this task, you may have the following tools at your disposal: Excel, Tableau, Power BI, Looker or any other business intelligence tool. For example, you decide to create your reporting from Google Analytics in Excel first and then, after successful internal reconciliation, implement your logic from excel in an online dashboard. The following questions play an important role and should, therefore, be carefully considered before each dashboard creation:
- What data from Google Analytics do I need as a starting point for my reports?
- How do I import the data from Google Analytics into my Excel, Tableau or Power BI project?
- How do I aggregate or calculate my most important Google Analytics Management KPIs?
- How do I continuously enter the data without creating additional manual effort for dashboard maintenance?
- How do I organize my dashboards and for which user groups should I create them?
- Some general information about Google Analytics in advance
Google Analytics is the most commonly used web analytics tool to track and analyze user data on a website. In general, Google Analytics is free of charge for websites with small to medium traffic. For websites with e.g. more than 100.000 sessions per day it can cost a lot. 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. In addition, they should definitely pay 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 output table. Google divides the data into metrics and dimensions. This is briefly explained by a small example: Thomas‘ dog wears a GPS collar which transmits data about the current location and other information to Thomas‘ smartphone every second. Let’s assume that Thomas wants to receive a message when his dog moves more than 1,000 meters from the property. The collar transmits in second intervals the current GPS coordinates and the absolute distance to the property. Now the current distance can be regarded as the measuring point. The coordinates and the current time are dimensions that describe a measuring point. A metric or measuring point is determined by its dimensions such as time and location. Using the Google Analytics Metrics Explorer: To determine your output table, use the following tool, the Google Analytics Metrics Explorer. Activate the field „Only show allowed in segments“ to select only dimensions and metrics that are valid for 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 check box to display all other values that can be combined in the same query. Also, switch to UI Names mode to get the same naming as in Google Analytics. Next, you can select your metrics and dimensions and directly 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 on how to increase this number. Read more 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
Google Analytics dimensions and metrics explained:
Dimensions
- ga:date = Date with the format YYYY-MM-DD
- ga:landingPagePath = The URL of the starting page in a user web session on your website. For example, the first page a user has entered on the website e.g. via a Google search.
- ga:fullReferrer = The exact referring URL, including the parameters of an external source which a user used to get to his own page. Extract the source of your traffic here.
- ga:deviceCategory = To which user device the session was conducted. The subdivision is Mobile, Tablet or Desktop.
- ga:channelGrouping = Traffic sources from Google’s fullReferrer are already pre-categorized and grouped here. Here you can find: Organic Search Engine, Direct, Paid Search Engine, Referral or Social.
- ga:medium = The UTM medium describes the type of source itself, e.g. organic, pay-per-click (paid ads), etc.
Metrics
- ga:users = The number of unique users identified by a cookie in the browser. Problems can arise if the users use several devices, i.e. it is actually one and the same user. However, this can only be differentiated in a complex way. In a later article, we will resolve this topic in more detail.
- ga:sessions = The number of sessions on the website. A session begins when a user enters a home page of the Website. If the user does not perform an action within the next 30 minutes, it expires and is counted as a new session again. A session can be equated with a visit. The customer enters the store and leaves it after a certain period of time after viewing the product range.
- ga:bounces = The number of direct jumps after entering or starting a session without an action having been performed beforehand. The user enters the website and leaves it without performing any action beforehand. E.g. click on a website element or follow another subpage.
- ga:timeOnPage = The time a user was active on the site, i.e. when he or she conducted 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 goal completions on your website. These can be defined in advance in Google Analytics.
Question 2: How do I import 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 all tools, you should pay attention to the sampling level. More about this in the following article: Avoiding Google Analytics Data Sampling in Reporting So that no data sampling occurs, I recommend using 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. This CSV document can be used as an Excel data source. You can also use plugins like Next Analytics to load the data directly into your Excel sheet.
Question 3: How do I aggregate or calculate my most important Google Analytics Management KPIs?
So that you can distinguish different domains or properties, I recommend adding another column with the web page URLs or property IDs to your Excel sheet. Then you can aggregate all data from the different accounts. Once you have loaded your data into Excel or another tool, you can calculate your most important KPIs. In Excel, it is best to use „Calculated Metrics“ in the Pivot section. The following KPIs can be calculated from the above data set.
- Bounce Rate [percent] = ga:bounces / ga:sessions
The ratio of direct jumps and sessions indicates whether a user interacts with their content after entering the site or not. This KPI can provide information about the targeting of users and the attractiveness of the content. A good bounce rate can be between 10 – 30%. If this is well below 10%, you should check whether the tracking has been validated at this point. Average session duration [minutes] = ga:timeOnPage / ga:sessions / 60 This Google Analytics KPI shows the average duration a user runs 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 from certain entry pages.
- Average page views per session [decimal] = ga:pageviews / ga:sessions
Also called page depth. A user navigates through different numbers of pages until he successfully makes a purchase or otherwise converts.
- Conversionrate [percent] = ga:goalCompletionsAll / ga:sessions
Shows the percentage of users who have successfully completed sessions. Normally this rate is between 2% – 5% in some shops even higher, sometimes even 17% – 20%. The conversion rate depends among other things strongly on the quality of the shop, in addition, the industry and the product portfolio.
- Percentage of new visitors to sessions [percent] = ga:newUsers / ga:sessions
A KPI to evaluate how high the proportion of new visitor sessions is.
- Sessions per user = ga:sessions / ga:users
Helpful to determine whether users are more likely to make a single or multiple purchase or deal 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 be the case if you filter the data in a certain level of detail.
Question 4: How do I continually enter the data without creating additional manual effort for maintaining the dashboards?
It is best to create a kind of import process of your downloaded CSV file. This can be easily created in Excel with Power Pivot. Most BI tools also offer an automated update. At this point try to keep the steps as small as possible and if possible work with an ETL tool. For example, if you need the calendar weeks, months and years in addition to the date. This has already been integrated automatically in one of the newer Excel versions. If not, simply create a kind of help table and link it to the date. It is best to create a Power Pivot data model for this.
Question 5: How do I organize my dashboards and for which user groups should I create them?
You already know very well your dashboard users and the needs of the department or the 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 gives you an overview of the performance of traffic sources.
- Device Report: With this report, you can analyze the performance of your users‘ 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 the important information about your online sales, leads, and deals.
The whole thing sounds pretty cumbersome and time-consuming. I agree with you, but there are alternatives where this effort can be saved 100%. Because if you don’t feel like doing all these steps manually, you can simply take a close look at our ABIS software solution. We offer a Cloud Business Intelligence platform, which makes it possible to install reporting apps very easily and to receive finished dashboards with your data directly without additional work.
