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.Screenshot of a calculated field

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:

REGEX_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

 

Leave a Reply

Your email address will not be published. Required fields are marked *