Create Calculated Fields in Blended Data Source Tables with Data Studio

Create Calculated Fields in Blended Data Source Tables with Data Studio

Welcome to our blog post kicking off ClickInsight’s 5th Annual 12 Days of Data Studio Series! Keep checking back throughout the month for more Data Studio know-how so you can wow your colleagues & stakeholders by taking your dashboards & reports to the next level.

Have you been asked questions about performance, but all the data needed to answer the question lives in different platforms? This analyst nightmare situation of yesteryears (Multi-Source Dashboards) does not have to be a long boring process requiring data manipulation anymore. With a combination of Data Studio’s data blending  & calculated fields capabilities, answers to those questions about performance can be found a lot faster.

Prior to any data blending efforts, having a good data foundation in the relevant platforms & a key attribute (join key) that connects the platforms is important. If confidence is lacking in the data foundation, a digital ecosystem analytics audit might be required for clean meaningful data.

Once clean meaningful data in the relevant data platforms is available – you can start leveraging the data blending & calculated field capabilities to begin efforts towards Digital Ecosystem Reporting via Multi-Source Dashboards. Data blending will enable you to use transactional data, revenue data, CRM data and more, and tie all that insightful information together to give a fuller picture to stakeholders on user’s behaviour.

Now that you know a little bit more about what data blending is & the benefits, let’s learn how to blend! Below we will go through an example of how to create these blended data source tables using geographic & user data from GA + transactional data from Google Sheets.

How to Create the Blended Data Source

Example:

Company A’s stakeholders want a quick overview of their transactional data. They also want to compare that across New and Total User’s for each region. However, the transactional and user data are collected on different sources as mentioned above.

You would start by connecting your underlying data set and you can go ahead and configure a chart that will include blended data.

So Company A would start by connecting the Transactional user data and start by configuring a simple chart.

Step 1: Click on the “BLEND DATA” button

Blended Data Source Table

Step 2: Add the additional data source you require and start creating your new blended data source!
Blended Data Source Table Add a Table Button

Items to keep note of:

  • You can add up to four different data sources of your choosing. Although, in order to join them, each data source must have a join key.
    • A join key is a dimension that is found in each data source.
    • You can have more than one join key dimension.
  • From there you can add any dimensions, metrics, segments or filters to each data source that you want to include in this new blended data source.
    • This new blended data source can be seen on the right most table which you can name accordingly.

      For example:

      • Company A would connect the second source which is the GA data source:
        • “Region” will be the Join Key dimension that is found in all data sources.
        • Next the dimensions and metrics needed to be showcased would be added:
          • “New Users”, “Total Users” metrics and “Medium” dimension, and the “transaction” metrics

Full Blended Data Source Table View

      • Finally a new blended data source can be created and is now named “Breakdown of Regional Transactions” as shown on the right

Step 3: Click Save!

  • Now Company A’s Stakeholders have a fuller picture of how many new users arrive from each campaign. And which regions or marketing channels generates the most transactions.

Geographic Data - Blended Data Source

 

Let’s take this a step further!

How to Create Calculated Fields

What if Company A also wants to see an average transaction per user and an average spend per transaction? To do both, two types of calculated fields must be made.

The first one will be the Avg. Transaction per User. To create this metric we need to divide total users by # of transactions. Since we have both total users and transactions in this new blended data set we made we can create calculated fields the same way it is usually done.

Step 1: Add a metric
Add a metric in table - Blended Data Source

Step 2: Create the New Field
Create a new field - Blended Data Source
In Company A’s case the following calculated field was made below.
Average Transaction per User - Blended Data Source
Average Transaction per User - Blended Data Source

And we’re done!

Next, an Avg. spend per transaction needs to be created. To create this metric you need to divide Transactions by Revenue, however, we did not bring the Revenue metric into our new Blended dataset so we can not create a calculated field like we normally did above.

Step 1: Go to edit the Blended Data Source you just created
Edit Blended Data Source

Step 2: Click on “Add metric”
Add metric

Step 3: Create the new field
Create field

In Company A’s case the following calculated field was made below.
Calculated Field

Finally the table is complete and Company A’s stakeholders now have access to a valuable report that showcases which regions have the highest Avg. Transactions per User and highest Avg. Spend per Transaction!
Complete Table - Blended Data Source

We hope that this blog will help you better understand data blending and how to use this tool to optimize your reports. Check out this link to learn more about using multiple dimensions, the limitations, how to blend a data source with itself and more!

Let us know if you have any additional tips about data blending or questions in the comments!

Other Posts in Our 2021 Data Studio Series:
  1. Create Calculated Fields in Blended Data Source Tables with Data Studio (this post)
  2. Gain Insights on Data Studio Reports in Google Analytics
  3. Understanding the Advantages of Geo Charts & Google Maps in Data Studio
  4. Enhance GA4 Reporting Functionality with Calculated Metrics in Data Studio
  5. The Quick Reference Guide to GA4 Ecommerce Data in Data Studio
  6. Key Storytelling & Visualization Elements for Meaningful Stakeholder Reports
  7. Turn Up the Dial on your Dashboard Design with New Gauge Charts
  8. Visualize Your User Journey with Horizontal Bar Charts in Data Studio
  9. Taking Advantage of Data Studio Community Connectors
  10. Time Saving Layout Features You Should Be Using in Google Data Studio
  11. Quick Introduction of the New Report Publishing Control in Data Studio
  12. Guide to Collaboration & Transfer Ownership Features in Data Studio

Stay tuned for more 12 Days of Data Studio Blog Posts throughout the month!

By |2021-12-21T13:12:17-05:00December 6th, 2021|0 Comments
Categories: Data Studio

Leave A Comment