Last updated for your viewing pleasure on April 12, 2022

Most Useful Guide to Using Regex In DataStudio – 5 min

Written by: JJ Reynolds
Lover of both data & having a good time. I spend my days creating bombdiggity reports🤘🏽 that knock the socks off our clients and help them take action faster than ever before through dynamic Looker Studio Dashboards!

Architect The Perfect Dashboard Every Time!

Publish better dashboards every time by following these 8 criteria to WOW your Boss, clients, & self!

Regex is a language used to find patterns from strings of text or numbers. You can use Regex within Looker Studio to uncover and clean any piece of information. From a simple string of text to complicated patterns, Regex can handle it.

Where You Will Use Regex Within Looker Studio

Looker Studio has 4 Regex functions. These 4 functions account for about 80% of the functions I use in the day-to-day of Looker Studio.

But before we hop into that, I need to give you the 80/20 of regex.

There are only 4 regex functions in GDS Here is Google’s Documentation on this.

My Quick Explanation

  • Regex Contains – Show me true when regex is matched
  • Regex Extract – Show me ONLY what matches in this field
  • Regex Match – Show me everything that exactly matches this regex
  • Regex Replace – Replace this regex with something else (could also be regex)

The Most Useful Regex Handlers in Looker Studio

This is just to get your mouth wet with anticipation. There are countless other websites that offer in-depth Regex guides. But here is 90% of the handles I use in Looker Studio

. – Matches anything or nothing

* – Matches the previous selector an unlimited number of times

.* – Matches anything

[A-Za-z] – Matches all Alpha characters (The alphabet for us #NonCoders)

[A-Za-z0-9] – Matches all Alpha Numeric characters (Numbers and letters for us #NonCoders)

\s Matches a space

| or use this to give a “Match this or that”

( ) Group things together

[ ] a list of options for a single character

More In-depth Regex Trainings

Regex101.com – Where you can test everything before you deploy it

https://regexr.com/ – Test all of your Regex in one place

https://regexone.com/ – Regex practice tests

Now let’s hop into how to actually use these random regex gibberish names!

REGEXP_REPLACE

This does two things.

  1. Finds your Regex pattern
  2. It replaces that pattern with your new pattern.

This is the most advanced Regex statement as you can use it to do just about anything depending on how creative you get.

✅ Clean up incorrect UTMs: Regex_Replace({{Medium}}, ‘PPC’, “CPC”)
✅ Group Custom Sales Regions: Regex_Replace({{Region}}, ‘region1|region2|region3|region4’, “Custom Region”)
✅ Remove Unwanted Strings: Regex_Replace({{page}}, ‘?(.*)’, “”)

Regex_Replace Example – Removing Spaces (h4)

You want to remove every space within a string! “super cool string” and you want that to look like “super cool string”.

You do that using Regex replace.

Regex_Replace({{Your_Field}}, r‘\s’, “”)

This looks at {{Your_Field}} finds every space. Spaces are defined as \s within Regex then replaces it with nothing. Pretty sweet right?

View Example In GDS (who doesn’t want to see a stripping chicken?)

Regex_Replace Example – Changing PPC to CPC

Sometimes you have a new Facebook Agency who decides to use a custom UTM Tagging System. Instead of using CPC for their Medium, they use PPC.

In GDS, we can use Regex replace to fix this.

REGEXP_replace(Source,’.ads.‘,”cpc”)

Obviously, not fully utilizing the extent of Regex, but we will get to that later.

REGEX_EXTRACT

Regex_Extract does the opposite of Regex replace. This is looking for a specific string then returns that value.

Regex Extract Example – Returning the pixels from a value

Let’s imagine you stored a value in an event of “Page Height 4028 Pixels” and you wanted to average the heights of all the webpages.

You will need to extract the numbers from that value.

In GDS, we can use Regex to extract this.

Regex_Extract({{Your_Field}}, ‘[0-9]*’)

This will return every numeric value from that string of text.

Now you can add, average, multiply, or do anything with your new metric.

REGEX_MATCH

So if you want a true / False (boolean) returned from your Regex pattern.

For example, if the user ID contains “PugsRule” then true otherwise false.

Regex_match({{my_field}}, ‘.PugsRule.’)

I really don’t use this very often

REGEX_CONTAINS

Pretty much the same thing as Regex_Match. Not 100% sure why they have to confuse us with Regex_Contains as the others would have been fine.

I know, I know, I know. There are use cases. All I am saying is that I personally have never found a usecase for it.

I don’t use this either, but it exists for you to use

Final Thoughts

Overall Regexp_replace() & Regexp_extract() are the only two that I use every day.

My regex skills are not amazing, but they get me where I need to go and you can use sites like regex101.com to really text if you are doing what you would like to do.

Hope this helps!

-JJ