Calculated Fields in Looker Studio - How to Use Text Functions | GlowMetrics

Jan 8th 2019

Digital Analytics Insights

3 min read

Posted by Michael Wilson

Calculated Fields in Looker Studio – How to Use Text Functions

Have you ever set up a report in Looker Studio and realised your data isn’t as clean as you’d like it to be? Maybe you’ve used inconsistent naming conventions in...

GlowMetrics Social Link GlowMetrics Social Link GlowMetrics Social Link GlowMetrics Social Link GlowMetrics Social Link

Have you ever set up a report in Looker Studio and realised your data isn’t as clean as you’d like it to be?

Maybe you’ve used inconsistent naming conventions in some of your campaigns – some are uppercase, and others are lowercase? Perhaps some of your URLs have parameters appended and others don’t? Or maybe you’ve changed your URLs and removed a sub-directory? Or you’ve renamed a sub-directory?

Luckily, we can fix all these issues by creating new Calculated Fields making use of Data Studio’s Text Functions!

Using Text Functions in Calculated Fields

If you have a mixture of uppercase and lowercase within the same dimension, you’ll have fragmented data. If we force all the fields to be uppercase or lowercase, we can consolidate your data and clean it up. Whether you want to go UPPERCASE or lowercase is your call – but lowercase is probably easier on the eyes.

Converting Text to Uppercase

Data Studio has a handy ‘UPPER’ function which allows us to do this. In the screenshot below, you can replace ‘Location’ with whichever Dimension you would like to force to uppercase.

Google Data Studio - Forcing Text to Uppercase

Converting Text to Lowercase

Same idea here – this time we use Looker Studio’s ‘LOWER’ function. Again, you put the Dimension you’d like to be lowercase where we’ve used ‘Location’ in the example below.

Google Data Studio - Forcing Text to Lowercase

Removing Parameters from URLs

This one is a little more complicated as we’re going to use Looker Studio’s REGEXP_REPLACE function. Regular Expressions can be hard to wrap your head around but don’t worry, we’ve got this.

REGEXP_REPLACE(Page, ‘\?.+’, ”)

There are three parts to the REGEXP_REPLACE function:

– the first part is the Dimension we’re going to use: Page

…because we want to remove Query Parameters from our Page URLs

– the second part is the Regular Expression: ‘\?.+’

This RegEx looks for ‘?’ and anything after it in your Page URL

– the final part is our replacement string: ”

In this case, it’s a blank string because all we want to do is strip the parameters.

Essentially we’re using the REGEXP_REPLACE function to replace something (our parameters) with nothing (a blank string).

Google Data Studio - Stripping Parameters from URLs

Removing a Text String

This works in exactly the same way as above – we again use Looker Studio’s REGEXP_REPLACE function to remove a string of text. In the example below, we use this function to remove the subdirectory ‘category/’ from a URL string.

REGEXP_REPLACE(Page, ‘category/’, ”)

Google Data Studio - Stripping Partial Text from a String

Replacing a Text String

We can also use the REGEXP_REPLACE function in Looker Studio to swap out one string of text for another. It works exactly the same as the previous two examples above, except this time, we’re defining a replacement string in the third part of the function, rather than leaving it as a blank string.

In the example below, we switch out ‘category/’ in our Page URL and replace it with ‘features/’

REGEXP_REPLACE(Page, ‘category/’, ‘features/’)

Google Data Studio - Replacing one Text String with Another

Hopefully, this guide to using Text Functions in your Calculated Fields allows you to clean up the data in your Looker Studio reports!

Want to Learn More About Looker Studio?

For more on Looker Studio, check out our Guide To Using Filters and Using Comparison Metrics. If you have any questions or there’s anything else we can help with on Looker Studio, please leave a comment below, or contact us at contact@glowmetrics.com.

You can also stay up-to-date by following GlowMetrics on Twitter or LinkedIn or by subscribing to our newsletter (link below) for the latest tips and news in the world of digital analytics and marketing.

Don’t forget to sign up for a complimentary account on our Resource Hub to access lots of valuable resources, including exclusive Looker Studio how-to videos and insights!


GlowMetrics Social Link GlowMetrics Social Link GlowMetrics Social Link GlowMetrics Social Link GlowMetrics Social Link

6 responses to “Calculated Fields in Looker Studio – How to Use Text Functions”

  1. Sreerej says:

    how to extract value between two texts in page field in data studio calculated field

    Example : **abcd/collection/collection-name1/**

    I want to extract only “collection-name1” from it.

  2. Alex says:

    Hi, do you know, if there is a chance to use a parameter instead the direct regex-string?
    For example

    REGEXP_REPLACE(Page, Parameter_1)

    I get Parameter_1 from a input-field.

    I don´t get any results so far, if I use text in the parameter, although there is no error recognized. If I use numeric values for the parameter, it is working.

    • Conor Ross says:

      Hi Alex, thanks for getting in touch.

      You should be able to use your Parameter in a Regex function. You might be missing an element in your function though, as the REGEXP_REPLACE function requires three parameters to operate.

      REGEXP_REPLACE(Field Or Expr, Replace regex, Replacement string)

      So in this example, it sounds like you might be missing the “Replace regex” parameter, in other words, the string you’d like to replace with your Parameter_1 value. I’ve run a quick test on this and it looks to work okay. Don’t forget that the “Replace regex” will need to be in quotation marks if it’s a text string, which it’s likely to be if it’s from the Page dimension.

      Hopefully this solves the issue you’re encountering, but if you’re still faced with errors then please don’t hesitate to get in touch. This resource might also be useful: https://support.google.com/datastudio/answer/9002005?hl=en#use-parameters&zippy=%2Cin-this-article%2Ccalculated-field-with-parameter-example

      Thanks, Conor

  3. Andrea says:

    Hi there!

    Do you have a recommendation how to handle the following?
    I need to catch everything that lies underneath /about-us EXCEPT for one specific page, e.g. /about-us/news/podcasts

    How do I adjust my current setup correctly?
    WHEN (REGEXP_MATCH(Page path, ‘.*/about-us.*’)) THEN “About Us”

    Please not: This is a just a snippet of the whole calculated field but basically it’s about assigning multiple pages to content groups and the podcasts page should not be assigned to “About Us” but to a different content group and therefore, needs to be excluded here

    I tried
    WHEN (REGEXP_MATCH(Page path, ‘.*/about-us(?!/news/podcasts).*’)) THEN “About Us”
    but I get a System Error from Looker Studio when trying to use the dimension inside a chart

    Happy about any tips 🙂

    Best regards
    Andrea

    • Michael Wilson says:

      Hey Andrea,

      You can’t use negative lookahead in Looker Studio Regex which is likely why you’re getting the System Error…

      I’d approach this by starting with a line to group the Podcast page first, then following with the rest – because the pages are assigned to groups depending on the order you put the lines in your Case Statement…

      WHEN (REGEXP_MATCH(Page path, ‘.*/podcasts.*’)) THEN “Podcasts”
      WHEN (REGEXP_MATCH(Page path, ‘.*/about-us.*’)) THEN “About Us”

      As long as the Podcasts line is included before the About Us line, that should do the trick.

      Thanks,

      Michael

Leave a Reply

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

GlowMetrics

Posted by
Michael Wilson

Michael is the Director of Strategy at GlowMetrics, implementing complex tracking for our largest clients via Google Tag Manager while steering our wider work across the Google Marketing Platform.
Read more from Michael Wilson

Browse by Category



GlowMetrics
Optimise your website and marketing campaign performance with Ireland’s leading digital analytics agency
Sign up to our newsletter