5 Calculated Fields for Google Data Studio

5 Calculated Fields for Google Data Studio

Calculated fields are one of the most powerful features of Google Data Studio. Based on the existing fields in your data source, calculated fields allow you to create new dimensions and metrics to augment your reporting and analysis.

Data Studio offers a variety of formulas that can be used to calculate ratios and rates, extract and reformat text, and apply logical comparisons.

Here are 5 useful examples to get you started with calculated fields in Data Studio:

Tip: Copy & paste any of the formulas below into the calculated field editor in Data Studio. Even though the editor is single-line, you can paste in multi-line expressions as well.

1. Combine hostname and page path

If you have multiple domains or subdomains within your website, it can be helpful to see the full URL of the page in your reports. A simple concatenation function will let you combine the Hostname and Page dimensions:

CONCAT(Hostname, Page)

2. Extract query parameter from URL

Query parameters are often used to trigger dynamic content, label traffic sources, or identify unique users. If you haven’t already moved these parameters into custom dimensions via your analytics setup, you can create a new dimension in Data Studio with a regular expression extract:

REGEXP_EXTRACT(Page, 'mode=([^&]+)')

Replace mode with the name of the desired query parameter.

3. Clean up inconsistent campaign naming

Ideally, you should establish consistent campaign tagging across your organization. Using common naming conventions between marketing teams and agency partners will make the reporting and analysis process much more efficient. However, if you need to clean up historical data or a rogue campaign, Data Studio will be your friend:

The most common inconsistency is usually the use of different case, e.g. “email”, “Email”, and “EMAIL”. This is easily fixed by forcing all campaign dimensions to lowercase.

LOWER(Medium)

Similarly, you can use LOWER(Campaign) and LOWER (Source).

When you use your calculated dimension, Data Studio will then aggregate the inconsistent values for you.

4. Create custom geographic regions

Google Analytics provides you with the standard geo dimensions of City, Region, and Country. But what if your organization uses your own regional grouping? Perhaps your sales team splits the country into West, Central, and East, for example.

Here’s a conditional function that you can use to define your own custom regions, based on the standard dimensions:

CASE 
WHEN REGEXP_MATCH(Region, "British Columbia|Alberta|Saskatchewan|Manitoba") THEN "West" 
WHEN REGEXP_MATCH(Region, "Ontario|Quebec") THEN "Central"
WHEN REGEXP_MATCH(Region, "Nova Scotia|New Brunswick|Prince Edward Island|Newfoundland and Labrador") THEN "East"
ELSE "Other" 
END

Note that Data Studio doesn’t support IF statements, so you must use the CASE function instead (which ends up being cleaner than nested IFs).

The general format is: CASE WHEN X THEN Y1 ELSE Y2 END, where X is a boolean expression and Y1 and Y2 are text, number, or boolean values. Refer to this CASE statement article in the Data Studio help centre for more details.

5. Create a custom channel grouping

Any custom channel groupings that you create in Google Analytics won’t be available in Data Studio. However, you can recreate your channel definitions in Data Studio using a CASE construct with a series of WHEN clauses.

Here is the Google Analytics default channel grouping represented as a CASE statement. Use this as a base for creating your own custom channel definitions:

CASE 
WHEN ((Source="direct" AND Medium="(not set)") OR Medium="(none)") THEN "Direct" 
WHEN Medium="organic" THEN "Organic Search" 
WHEN (Social Source Referral="Yes" OR REGEXP_MATCH(Medium,"^(social|social-network|social-media|sm|social network|social media)$")) THEN "Social" 
WHEN Medium="email" THEN "Email" 
WHEN Medium="affiliate" THEN "Affiliates" 
WHEN Medium="referral" THEN "Referral" 
WHEN (REGEXP_MATCH(Medium,"^(cpc|ppc|paidsearch)$") AND Ad Distribution Network!="Content") THEN "Paid Search" 
WHEN REGEXP_MATCH(Medium," ^(cpv|cpa|cpp|content-text)$") THEN "Other Advertising" 
WHEN (REGEXP_MATCH(Medium,"^(display|cpm|banner)$") OR Ad Distribution Network="Content") THEN "Display" 
ELSE "(Other)" 
END

Using calculated fields is a skill that every Data Studio user should master. It will make your data cleaner, make your reporting more relevant, and ultimately make your life easier.

Interested in learning more about Data Studio? Check out 7 Reasons to Use Data Studio with Google Analytics.

Let us know if there are any other specific aspects of Data Studio you would be interested in learning more about!

——

Looking for hands-on training in Data Studio? Come to our 1-day Data Studio course!

Related post: 7 Reasons to Use Data Studio with Google Analytics

 

By | 2018-02-20T09:11:10+00:00 February 14th, 2017|4 Comments

4 Comments

  1. Skyler December 5, 2017 at 12:07 pm - Reply

    I am tracking scroll depth (25%, 50%, 75%, 90%) and want to create an aggregate view of total events per URL. I also want to create a % of the total events per URL at each scroll depth interval. For example, I want to know the % of the total events for a URL at each interval. Is this possible in GDS?

    • Marc Soares December 7, 2017 at 3:18 pm

      Hi Skyler,

      You can definitely accomplish this, but not with calculated fields. Data Studio will aggregate data based on the dimensions and metrics that you include in your chart. So, if you want to see an aggregate of total scroll depth events per URL, you could create a table with Page and Total Events and filter for scroll events only.

      To get a “percent of total” metric, you can edit a metric in your report and apply the Percent of Total analytical function. Take a look at the help centre page here: https://support.google.com/datastudio/answer/7512288.

      Hope that helps!

  2. Ondra February 20, 2018 at 5:22 am - Reply

    Hi, thanks for the article. You have wrong function name in 2. Extract query parameter from URL part it is REGEXP_EXTRACT. Have a nice day

    • Jonathan Iudica February 20, 2018 at 9:12 am

      Hello Ondra,

      Thanks for letting us know! We have updated the post.

      Jonathan

Leave A Comment