9.4. Data Blending: The Simple Blend
Let's dive into Looker Studio and explore the simplest form of data blending. Imagine we have sales data from a Google Sheet and another data point such as the number of clicks, impressions, or cost from another data source like a GA demo account. Although these are not tables, they're simple numbers that can be blended together to provide valuable insights.
The Need for Blending Metrics
We want to access both of these metrics in a single data source. But why do we need sales and clicks in a single data source? The answer is simple: advantage conversion. We may want to calculate conversion rates, ratios, or even add or subtract these metrics from each other. However, we can't do that because they exist in separate data sources, and one chart cannot be connected to more than one data source.
Blending Data in Looker Studio
To blend these data points, we'll select the two scorecards from the different data sources that we want to combine. By right-clicking and selecting "blend data," a new scorecard will appear with the blended data. This new data source is called "blended data number one."
In the blended data source, we only have access to two fields: clicks and sales. Looker Studio tries to guess what kind of calculations we want to make, such as clicks divided by sales. However, we may want to divide sales by clicks instead. We can click on the metric to either show the number of clicks directly or create a field to do the opposite calculation, such as the sum of sales divided by the sum of clicks.
Remember, when blending data, it's essential to ensure that the join keys in both data sources have a one-to-one relationship, meaning that each key appears only once in each data source.
Simple Self Blend
Sometimes, you might want to blend two subsets of metrics from the same data source. This process is called a simple self blend. In this lesson, we'll learn how to blend two metrics from the same data source with different filters, segments, or date ranges applied.
Working with Subsets of Metrics
Imagine we have Google Analytics as a data source, and we want to blend two subsets of the total events metric with different filters applied. For example, we want to calculate the conversion rate of a contact form by dividing the number of submissions by the number of impressions.
However, we cannot use a subset of a metric with a specific filter applied in a calculation directly. Instead, we can blend the data source with itself to create a self blend.
Blending Subsets of Metrics
To blend the two subsets of metrics, we'll select the two scorecards with the specific filters applied. In our example, both scorecards show the total events metric but with different filters applied for contact form submissions and contact form impressions.
When we blend the data, Looker Studio automatically tries to figure out the calculation we want to perform. In our example, it divides the number of contact form submissions by the number of contact form impressions.
Data blending allows us to define subsets of metrics, either by segmenting, filtering, or applying a specific date range as a single metric, and then use them in calculations.
Applying Date Ranges
When blending data with different date ranges, you might need to reapply the date ranges in the blended data source to ensure the calculation is accurate. In our example, we had to reapply the date ranges for contact form submissions in 2021 and 2022 to calculate the correct conversion rate.
Use Cases for Simple Self Blends
Simple self blends can be particularly useful for funnel tracking. If you have a funnel with multiple steps, you can filter page views or events by each step in the funnel and blend them together. This process allows you to report on the number of users across the funnel and use the segmented metrics in calculations.
📩 Receive my weekly Looker Studio tips
🎥 Subscribe to my YouTube channel
🖇 Connect with me on LinkedIn