Have you ever set up a report in Google Data 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 Data 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 Data 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 Data 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 Data 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 Data Studio reports!  If you have any issues getting these to work, feel free to leave a comment below or pop us an email – we’re always happy to help.