The information in this article provides more in-depth information about how blending works that can help you solve more complex use cases.
In this article:Blending tips
Blends should contain only a subset of the available data
As a best practice, you should only include the specific fields you'll want to visualize in charts based on a blend. Here's why:
- Blending can create very large data sets, which can lead to slow performance and possibly higher query costs for paid services such as BigQuery.
- Charts based on blends calculate all rows in the blend even if they are unused in the chart.
- For example, say you create a blend containing 10 fields. You then define a chart that only uses 1 of those fields. Looker Studio calculates the 10-field blend, and then queries that 1 field in the output of the blend to create the chart.
- Reaggregation only happens if your blend contains a subset of the underlying data. (More on this below.)
Blend multiple tables from the same data source
Blending multiple tables from the same data source lets you combine your data in interesting ways and allows you to work around connector limitations.
For example, the Universal Analytics connector contains metrics for 1 day active users, 7 day active users, and 28 day active users. But, due to a limitation of Analytics, you can only have one of these metrics in a chart at a time. But by joining the same Analytics data source with itself in Looker Studio, you can add each of these metrics to the blend. You can then compare each of these active users metrics in the same chart.
Advanced concepts
Table order in the blend
Looker Studio evaluates the join configurations in the blend in order, starting with the leftmost configuration. The results of each join are then applied to the next join to the right. For example, in a 3 table blend, the join configuration between table 1 (leftmost) and table 2 (middle) are evaluated, then those results are used by the join configuration between table 2 and table 3 (rightmost).
Table order in automatically created blends
When you blend a selection of charts, Looker Studio creates a table for each chart, and adds the fields in the chart to the corresponding table. The order of the tables in the blend matches the order in which you select the charts: the first chart selected becomes the first (leftmost) table, the second chart selected becomes the second table, and so on.
Looker Studio also automatically creates a join configuration for each table and uses the left outer join type.
If the default configuration isn't what you want, or if there aren't clear linkages between the tables, you can edit the blend to suit your goals.
Tables are created before the blend
The data for each table in a blend is queried before that data is joined into the final blend. This means that date ranges, filters, and calculated fields in a table are applied to the query that generates the table before any joins are performed. This can affect the data included in the blend tables and change the output of the blend.
Blending reaggregates metrics
Metrics that you include from the underlying data source become unaggregated numbers in a blend. When the blend includes less than the full set of fields from the underlying data source, these numbers are reaggregated based on the new data. This can be useful if you need to apply a different aggregation to an already aggregated field, such as calculating an average of averages.
Learn more about reaggregation.
Blends may contain more rows than the original data
You might see more data in a blended chart than you'll see in charts based on the individual data sources that make up the blend. This can depend on your data and on the join configuration chosen for the blend. For example, a left outer join includes all the records from the left hand table, as well as all the records from the tables to the right that share the same values across the join condition. When there are multiple matches for the join condition, this can result in more rows appearing in the blended data than exist in the leftmost data source.
Blends and explicit date ranges and filters
Two ways to limit the number of rows in your blends are by using a date range or applying a filter. You can do this either on charts based on a blend, or on the tables that make up the blend. To understand how this works, it's helpful to think about things being either "pre-blend" or "post-blend."
When you apply a date range or filter to a table in the blend, it takes effect before the data is joined with the other tables in the blend. Rows that are outside the date range or that are excluded by the filter aren't available for the join query to work on.
When you apply a date range or filter to a chart based on a blend, you're applying it to the data after the blend has been created ("post-blend").
This difference could have a big impact on the results you see in your charts, depending on your data and how you've configured the blend.
Blends and inherited filters
Blends inherit report, page, or group level filters as long as the filter is compatible with the pre- or post-blend data. If the filter is compatible with the underlying data source(s) used by the blend, then the filter acts on the pre-blended data. Otherwise, the filter acts on the post-blended data. If the filter isn't compatible with either the pre- or post-blend data, then the filter is ignored.
Learn more about filter inheritance.
When a chart based on a blend is subject to an inherited filter, Looker Studio processes the data in 5 steps:
(Pre-blend)
- Step 1: The data is grouped and aggregated based on the dimensions specified in the Blend Data panel.
- Step 2: Inherited dimension filters and compatible metric filters are applied to the data sources included in the Blend Data panel.
(Blend)
- Step 3: The data is blended using the specified join configuration.
(Post-blend)
- Step 4: The data is grouped and aggregated based on the dimensions in the chart.
- Step 5: Metric filters, if compatible with blended data, are applied to the chart.