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 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.
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.
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).
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/’, ”)
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/’)
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!
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.
Hi Sreerej,
You would use something like…
REGEXP_EXTRACT(Page, ‘/abcd/collection/([^&]+)/.*’)
Thanks,
Michael
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.
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
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
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