How To Blend Data in Google Data Studio For Better Data Analysis by@lukecalton

How To Blend Data in Google Data Studio For Better Data Analysis

How To Blend Data in Google Data Studio For Better Data Analysis: Luke Calton. This is written for you if you’re aware of the basics of Google’s Data Studio. In this post, I’m going to cover some of the more advanced things you can do, like blending data, creating custom calculated fields, using metric level filters, and creating combination charts. The best visualization for this is a combination chart that compares sessions where users have interacted with features and the completion rate.
Luke Calton Hacker Noon profile picture

Luke Calton

Product guy. Learns stuff and writes about it.

Using data to inform decisions is essential to product management, or anything really. And thankfully, we aren’t short of it. Any online application generates an abundance of data and it’s up to us to collect it and then make sense of it.

Google Data Studio helps us understand the meaning behind data, enabling us to build beautiful visualizations and dashboards that transform data into stories. If it wasn’t already, data literacy is as much a fundamental skill as learning to read or write. Or it certainly will be.

Nothing is more powerful than data democracy, where anyone in your organization can regularly make decisions informed with data. As part of enabling this, we need to be able to visualize data in a way that brings it to life and makes it more accessible. I’ve recently been learning how to do this and wanted to share some of the cool ways you can do this in Google Data Studio.

Whether it’s automating some of the routine, manual analysis you might do, you will find some great use cases that help you understand how users use your digital service. This is written for you if you’re aware of Google Data Studio and might be asking yourself whether it’s worth the effort involved in learning more. You may think that if you get some out-of-the-box analysis already from some of your existing data sources (e.g. other Google products) why invest more time? What more value can you really get? Basically, I’m writing for me, 6 months ago.

So, I will presume you know the basics (like connecting to a data source), but if you don’t, there’s a great tutorial here. In this post, I’m going to cover some of the more advanced things you can do, like blending data, creating custom calculated fields, using metric level filters, and creating combination charts, all using a fake dataset I created based on Nike’s website.

Working through a Nike eCommerce example

A common use case for a product manager is to compare a group of users who complete a certain experience as a percentage of total users. These are called ratio metrics. For example, let’s say you worked for Nike as a designer and wanted to understand how many users use the “design your own” trainer feature you spent ages designing, to understand how many users complete all of the customization steps.


You would generally have Google Events fire every time a user interacts with parts of your website. You would have an event for a user entering the customization flow, events for everything in between, and then one final event for a user finishing the flow. You can create a ratio metric manually by the following calculation:

ends flow event / enters flow event= completion rate (new ratio metric)

In Data Studio, you can create this with the following steps:

Insert a scorecard with unique events as the metric and then create a filter that specifies the event label you’re interested in (e.g. enters flow). Insert a second scorecard and do the same as step 1, creating a second filter with the second event label you’re interested in (e.g. ends flow). Blend the two by clicking on both of the scorecards and clicking “blend data”.

If you’re a more visual learner, here is a video showing you how this works:

This is really useful to get an overarching update on what you’ve recently designed. But in order to optimize this, you want to see how it trends over a period of time. You can calculate this manually each time, but having the metric that you’re trying to optimize for automatically calculate will be really useful and save you a tonne of time in the future.

The best visualization for this is a combination chart. Which compares sessions where users have interacted with the feature and the completion rate (the custom metric you just created through blending data) that you’re trying to optimize for.

The below video will show you each step of the way how will we achieve this. In general, it encompasses these four areas:

  1. Blending data amongst the same data source, using a JOIN KEY (a shared dimension among each data source) which will be the Month (but could be any time dimension depending on how detailed you want the chart to be).
  2. Creating two individual metrics that only contain the unique events of the event label you’re interested in. This will be achieved by using metric level filters that filter for the event labels you’re interested in (e.g. a user entering the flow / a user finishing the flow).
  3. Selecting your dimension, metric #1 (sessions), and creating a new field that is a calculation to generate metric #2.
  4. Styling the chart so it looks like a combination chart and involves two data series being shown on the left and right axis.

Here’s a video that shows you how this is done:

Once we have added some styling elements to it and a title, we have a finished visualization that looks like this:


With the data clearly visualized like this, the data has been brought to life and you have a much clearer picture of what’s happening with this feature. You can immediately see that the trend is going downwards as the year goes on. This can help you understand if you have made product changes that have influenced the completion rate (e.g. extra steps or more complicated steps in the flow). If you wanted an even more accurate picture, you could create a Google Analytics segment that only includes sessions where users have had the opportunity to customize their trainers (as not all trainers have the option). This won’t change your completion rate but might reduce the overall sessions count.

I use the Nike example because this is hopefully relatable to most people. But there are lots of other eCommerce use cases for this. For example:

  • What percentage of people use certain filters on AirBnB?
  • What percentage of sessions do users add your product to their basket?
  • What percentage of users land on your checkout page and then decide not to buy something?

Visualizing important metrics in a dashboard means you are better placed to optimize them and easily measure the success of anything you’re building.

I hope you found this useful. I’m not a Google Data Studio expert, but I’ve spent lots of time in the last 6 months learning things. And once I’ve learned it, I’m keen to share it.

If you want to see the end visualization and how it is configured, here is the data studio report, and here is the Google Sheet which contains some fake data based on the Nike website.

If you found this interesting, check out some of the other things I’ve written about building apps without writing code or kicking off projects with a kick-off document.

Photo by Markus Spiske on Unsplash


Join Hacker Noon

Create your free account to unlock your custom reading experience.