How blends work

Understand the basic concepts behind data blending.

Blending data lets you create charts, tables, and controls based on multiple data sources.

For example, you can blend data from different BigQuery tables—say customer information and order details—and visualize that information in a single Data Studio table. As another example, you can plot combined data from your Google Ads and Analytics accounts on a time series to see a unified view of your marketing campaign performance.

Tip: Blending data can be complex. To get the most from this article, you should be familiar with:

Differences between blends and data sources

Blending data creates a resource known as a blend. Blends are similar to data sources, in that they provide data for charts and controls in your report. However, blends differ from data sources in some important ways:

  • Blends get their information from multiple data sources.
  • Blends are always embedded into the report in which they are created. You can't make a blend reusable across reports. However, if you copy the report, the blends are copied into the new report so your charts will continue to work on the blended data.
  • Metrics in the underlying data source become unaggregated numeric dimensions in the blend. See the advanced concepts article for more information.
  • Blends have no data freshness or credentials settings of their own. Instead, these settings are inherited from the underlying data sources.

How blends work

Database programmers use SQL join statements to blend data from different tables. In Data Studio, you can blend data without writing code. Instead, you use the blend editor to configure the join, as shown in the following screenshot:

Blend editor with numbered callouts.

The blend editor.

Legend:

  1. Tables
  2. Available fields
  3. Join configuration
  4. Join another table
  5. Blend name
  6. Included dimensions and metrics
  7. Save button

Tables

Blends are made up of tables. When you edit or create a blend, you'll see its tables displayed in the UI. Each table contains a set of fields extracted from the underlying data source. A blend can have up to 5 tables.

To add data to a table, open the Available Fields list by clicking Chevron right icon. .

Fields used in join conditions are shown with a link icon Link icon used to show fields in a join condition..

Join configuration

A join configuration links pairs of tables in a blend. A join configuration consists of an operator, which defines how to combine matching and non-matching records from those tables, and a condition, which is a set of fields that defines how the tables are related to each other.

For example, in the following screenshot, the Grades table joins to the Students table on the student_id field, and to the Classes table on the class_id field. Both join configurations use the left outer operator.

Example left outer join configuration linking Grades to Students on student_id.

Example join configuration, using the left outer join operator, and student_id as the join condition.

Join operators

The join operator determines how the matching and non-matching rows from the tables in the blend are joined together. Data Studio supports the following join operators:

    • Inner join - Returns only matching rows from the left and right tables.
    • Left outer join - Returns matching rows from the right table, plus non-matching rows from the left tables.
    • Right outer join - Returns matching rows from the left tables, plus non-matching rows from the right table.
    • Full outer join - Returns all matching rows from the left tables or the right table.
    • Cross join - Returns every possible combination of rows from the left and right tables.

Learn more about join operators in the BigQuery documentation.

Join conditions

A join condition is a field or fields that can be found in each table and used to link the records of those tables together. For example, in a blend of Google Analytics and Google Ads charts, if Campaign Name exists in both of the extracted tables, Data Studio can use that field to join the data.

For each table in the blend, you'll select which fields to use in the condition. Note that you don't have to use the same fields for every table, nor do they have to have the same name, as long as the data is same in field. For example, say you want to visualize customers, orders, and items in a single chart. These tables might have the following fields:

Customers table

  • customer_ID
  • customer_name

Orders table

  • cust_id
  • order_number
  • order_total

Items table

  • order_number
  • SKU

To blend these tables, you'd join Customers with Orders, using the customer_ID and cust_id fields as the join condition, and you'd join Orders with Items using order_number as the join condition.

Note: Data Studio only supports equality between the fields in the join condition. For example, you can create a join condition for Field A = Field B, but not Field AField B, Field A > Field B, Field A < Field B, and so on.

Blending examples

See these concepts in action in the classes, students, and grades blending example.

Was this helpful?
How can we improve it?
Search
Clear search
Close search
Google apps
Main menu
Search Help Center
true
102097
false
false