Using Data Blends and Calculated Metrics in Data Studio
If you are using Google Analytics (GA) to track user engagement and traffic to your site, then at one point or another you have probably dabbled with Google’s data viz tool – Data Studio. Google has supplied an incredibly straightforward process to visualize data directly from GA in Data Studio via the GA connector. Within minutes you can amass a collection of tables, time series plots, bar charts, and more to visualize site data. However, once you begin fielding more challenging questions and tinkering with visualizations of increasing complexity, you will likely find yourself needing custom user-defined metrics; this happens to be a point of contention for most users. Below we will walk through a standard business question, which introduces some of these roadblocks, and provide some relatively simple solutions to save you a few headaches.
A marketing executive asks about the performance of a specific call-to-action (CTA) on your website. She wants a self-service tool that will give her visibility into the total number of impressed users and the percentage of users who clicked on the CTA. You decide to build a basic report in Data Studio containing a table (see below) with total impressed users, the number of users who clicked on the CTA, and the click-through-rate (CTR) on the CTA.
While this appears to be a seemingly simple ask, there are a few complications introduced by the Data Studio framework:
- User-defined metrics and dimensions can reference either dimensions or metrics in their definition, never both
- User-defined metrics and dimensions cannot reference other user-defined metrics or dimensions
- CTA Users and Total Users are based on the same out-of-the-box GA metric – users
- To calculate CTA CTR the final table/object must have access to both the Total Users and CTA Users metrics simultaneously
Creating a calculated metric for CTA Users would require the use of a dimension (i.e. event action and/or promotion name) to identify users who clicked on the CTA, as well as a metric to indicate which metric to sum when the specified dimension was present. However, the first limitation means no sophisticated CASE WHEN or IF THEN logic will allow for the calculation of CTA Users. Through the user of a filter you can calculate CTA Users, but this filter applies to all metrics in the table; since both CTA Users and Total Users are based on the same out-of-the-box GA user metric, both metrics would show the same number of users (6033). Even if there were a way to calculate CTA Users from a calculated field, according to limitation #2 there would be no way to reference this field when trying to calculate CTA CTR. Without the ability to create two variants of users in a single table or define CTA users via a filter and then reference it in a different object, how do you solve the problem? Two words for you – data blend.
Start off by adding two tables, both of which contain only a single metric – users.
Rename the metrics Total Users and CTA Users to make it easier to distinguish them later.
Next, select the CTA Users table and apply a filter to limit only to users who have clicked on the CTA.
The CTA Users metrics should now reflect the subset of users who clicked on the CTA.
To calculate the CTA CTR, you must first combine the two tables using a data blend. Ctrl + click both tables and right-click to open the options menu. Select Blend data.
Data Studio will create a new table, which combines the data from both source tables using logic resembling an inner join. If you see the following symbol on your newly created table, don’t worry, you are not alone!
By default, Data Studio rarely joins the two tables as intended, especially when there are dimensions involved, so you typically will need to edit the blended data source you created by clicking on the pencil icon. You can also remove the invalid dimension, which should resolve the error, since there are no dimensions in either of the two source tables. Note that you cannot bring any other dimensions or metrics into the blended data set by simply adding a dimension or metric in the final table. If for instance you wanted to see the CTR broken out by the geographic location where the user accessed the site, then you would have to add this dimension to one of the source tables used to create the resulting table.
After opening the edit dialogue, you can rename the blended data source and change dimensions, metrics, data sources and filters for the two underlying tables. If a dimension was going to be displayed in the final table, then you would be required to have a join key (Date is usually a pretty good option here). Since there is no dimension in this case you can simply rename the blended data source and select Save before closing.
When you blend data, GA creates a new data source with its own unique dimensions and metrics. As a result, CTA Users is no longer viewed as a calculated field in the newly created data source and can be referenced within a calculated field. Add a new metric called CTA CTR equal to the sum of CTA Users over the sum of Total Users. Change the number type to percent.
With the table now successfully deployed, your marketing executive can keep tabs on the CTA performance. By adding a date range at the top she can select a rolling 30-day window or alter the range to look at particular dates of interest – perhaps after the launch of a marketing campaign.
This represents one simple use case where data blends can help you combine metrics into a single table and successfully navigate the use of calculated fields. However, this strategy can be employed to tackle many other challenges, including the creation of powerful dual axes time series plots and just about any other visual you can think of!
