5 More Calculated Fields for Data Studio

In our 5 Calculated Fields for Google Data Studio post, we introduced practical examples to get you started with calculated fields. Since then, Google Data Studio has made calculated fields increasingly powerful, with an improved formula editor, more descriptive error messaging, and new functions. 

Here are 5 more practical ways to use calculated fields in Data Studio:

1. Hyperlink to Pages, Posts, or Downloads

You can use the HYPERLINK function in a calculated field to deep link to web pages from within a table in your report. The function requires two inputs: the link URL and the link label. In the sample report below, we made the titles of our blog posts clickable. We used the CONCAT function and the Page Path dimension to build the link URL.

HYPERLINK(CONCAT('https://clickinsight.ca', Page Path), Blog Post Title)

See how it works in the report below. You could use the same technique to link to your pages, file downloads, referral sources, or any URLs recorded in your data.

2. Extracting the Start or End of a Text Field

If you ever need to extract a portion from the beginning or end of a text field, the LEFT_TEXT and RIGHT_TEXT functions will make your life easier. For example, you may need to extract the year from a date dimension formatted as MM-DD-YYYY. In this case, you can use the RIGHT_TEXT function to extract the last 4 digits as follows:

RIGHT_TEXT(Date, 4)

If Date were “12-01-2019”, this formula will return “2019” in your calculated field.

The RIGHT_TEXT function returns the specified number of characters from the end of the string. Similarly, LEFT_TEXT will extract characters from the start of the string.

3. Rounding Labels in Bar and Line Charts

Have you ever wished you could round the data labels in your bar or line charts? Data Studio allows you to adjust the decimal precision of numbers in many places, however data labels is not one of them. Until this setting is made available, you can use the ROUND function to round your data label values to the desired number of decimal places.

In the example below, we used the ROUND function to round our Percent Change labels to whole numbers as follows:

ROUND(Percent Change, 2)

Note that we are actually rounding to 2 decimal places, since our percentages are represented as decimals (i.e. 5% is 0.05).

4. Clean up Page Titles

Do your website’s page titles include your company name or other additional information? For example, the page titles on our blog usually have a suffix of “- ClickInsight”. If you are reporting on page performance in Data Studio, this added clutter can be distracting and take up valuable space. As long as your page titles have a consistent pattern or delimiter, you can use a REGEX_EXTRACT function to isolate the actual page title.

Here is an example of removing the company suffix for pages following the format “Page Title | Company”:

REGEXP_EXTRACT(Page Title, '(^[^|]+)')

This expression will retrieve anything that precedes the pipe (|) character in the page title.

For more information on using regular expressions in Data Studio see our post on Tips for Using RegEx in Data Studio.

5. Custom Content Grouping

Content Grouping can be a useful feature in Google Analytics, however it cannot be applied retroactively. In Data Studio however, you can use a calculated field to group your pages with a CASE statement. Although this won’t generate the Unique View metrics that GA would process for an actual content grouping, it is sufficient for creating a higher level grouping of your pages to aggregate other metrics.

Here’s an example of a content grouping CASE statement. You can adapt it to your own site by changing the RegEx patterns and labels for each group:

CASE
WHEN REGEXP_MATCH(Page Path, "/$") THEN "Homepage"
WHEN REGEXP_MATCH(Page Path, "/blog/.*") THEN "Blog Pages"
WHEN REGEXP_MATCH(Page Path, "/products/.*") THEN "Product Pages"
WHEN REGEXP_MATCH(Page Path, "/about/.*") THEN "About Pages"
WHEN REGEXP_MATCH(Page Path, "/contact/.*") THEN "Contact Pages"
ELSE "Other"
END

Let us know if you found these tips helpful or if there is anything else about calculated fields you would like to know!

–

Complete list of posts in our 2019 Data Studio series:
1. 5 More Calculated Fields for Data Studio
2. Using Drill-Down Charts in Data Studio
3. How to Schedule Email Delivery of Reports in Data Studio
4. 4 Scatterplot Hacks for Google Data Studio
5. How to Add Clickable Images in Data Studio
6. Calculating Ratios with Filtered Metrics in Data Studio
7. Using Optional Metrics in Data Studio
8. Using Conditional Formatting in Data Studio
9. Creating Ecommerce Funnels in Data Studio
10. How to Create a Slope Chart in Data Studio
11. Creating Radar Charts in Data Studio
12. Creating Stepped Line Charts in Data Studio