Practical Guide to Regex in Google Data Studio

Analyze

Regular expressions (regex or regexp) can help save time and effort when working with Google Analytics, Google Tag Manager and now Google Data Studio.  They add extra flexibility to the way you create definitions or include & exclude data from your reports.

You can think of regex as specific sequences of characters that broadly or narrowly match patterns in your data. And to build a pattern you need learn the regex metacharacters. In this post, I will be covering most of the metacharacters along with practical examples from Google Data Studio.

Before we start, I recommend you have a look and understand the formulas in the article below, because all of our regex examples are based on these formulas.

Create Advanced Dimensions in Google Data Studio via these 6 Formulas

Caret ^

It matches the start of a string. In other words, it means something begins with.
^social matches social media and social network, but not online social.

Practical Use in Google Data Studio

Let’s say you have 3 paid social sources in your Google Analytics (facebook / paid-social, instagram/paid-social, twitter/paid-social). And you want to group any medium that starts with the string paid under a channel grouping called “Paid” and anything else under “Organic”.

CASE WHEN REGEXP_MATCH(meduim,"^paid") THEN "Paid"  ELSE "Organic" End

Dot .

It matches any single character.

go.gle matches google, goagle and gosgle but not gogle.

The power of using the dot lies in combining it together with other characters.

Practical Use in Google Data Studio

Google Data Studio by default has a formula to convert all text to uppercase, but what if you want to capitalize only the first letter of your traffic sources in Google Analytics and create a report of the same.

This can be done in three steps using the caret and the dot together ^. (which means select the first letter):
1- Extract the first letter using REGEXP_EXTRACT() with the regular expression ^. which captures the first letter.

2- Capitalize the extracted letter using UPPER().

3- Replace the first letter in Source with nothing using REGEXP_REPLACE() and concatenate it with the extracted CAP letter using CONCAT().

So if we put all the formulas altogether, the final syntax will be as below:

CONCAT(UPPER(REGEXP_EXTRACT(Source, "(^.)")), REGEXP_REPLACE(Source, "^.", ""))

 

Asterisk *

It matches the preceding character 0 or more times.

go*gle matches with google, gooogle, gooooogle and gogle, but not goegle.

One of the most powerful combinations of asterisk is with the dot (.*) It  actually matches everything,

go.* matches with google, goooogle, goosssf and goelge.

Practical Use in Google Data Studio

You have search and display campaigns running within the same AdWords account, you want to review and compare the performance of each channel separately. Your campaigns naming includes the acronym SN for search and CN for display.

^.* means text starts with anything, then contains (SN, CN) and finally ends with any string.

So basically, the regex below means search for text contains my acronyms (SN, CN) in the campaign name.

CASE WHEN REGEXP_MATCH(Campaign,"^.*(SN).*") THEN "Search"
WHEN REGEXP_MATCH(Campaign,"^.*(CN).*") THEN "Display" 
ELSE "Undefined" End

Pipe |

It means OR.

men|women match with men clothes or clothes for women.

Practical Use in Google Data Studio

You can run a performance report of your landing pages grouped into unified dimensions. For example, you’re running a fashion website that sells dresses, shirts, boots, sandals..etc. You want to compare the performance of your website categories and review which category has the most sessions or transactions.

CASE WHEN REGEXP_MATCH(Landing Page,"^.*(shirt|dress|jeans") THEN "Clothes" 
WHEN REGEXP_MATCH(Landing Page,"^.*(boot|sandal|sneaker") THEN "Shoes" 
ELSE "Undefined"
End

 

Dollar Sign $

It means text ends with.

color$ matches red color and green color, but not color yellow.

Practical Use in Google Data Studio

You want to run a URL performance report of your website, but some of your links end with .php and you want to standardize all the URLs by removing .php only from the end.

REGEXP_REPLACE(Landing Page, "(.php$)", "")

 

Question Mark ?

It matches the preceding character zero or one time. In other words, it means the previous character is optional.

colo?r matches colr and color, but not colour.

Practical Use in Google Data Studio

You want to run a keywords performance report of your branded and non-branded terms.

Optimization ?up matches both; optimization up with space and optimizationup without space.

CASE WHEN REGEXP_MATCH(Keyword,".*(optimization ?up).*$") THEN "Paid"  ELSE "Organic" End

Square brackets []

Matches the enclosed characters in any order anywhere in a string.

col[oau]r matches color, colar and colur, but not coloaur.

Square brackets are very powerful when they are combined with dashes. Dashes create a range of characters between the brackets.

[a-z] matches any lowercase letter between a to z.

[0-9] matches any number from 0 to 9.

Practical Use in Google Data Studio

Let’s assume you sell cars and you want to create a performance report for all the sold car models between 2010 and 2017. You can create a filter for your query using the regular expression below.

regex in google data studio

Parentheses ()

Matches the enclosed characters in exact order anywhere in a string. It also uses to group characters or string together.

g(oo)gle matches only google.

The power of parentheses lies in combing it with different metacharacters.

Practical Use in Google Data Studio

Let’s say you want to run a report of the categories included in your campaigns names. Your campaign naming looks like below:

SN-EN_Category:Cars_Exact

SN-EN_Category:Bikes_BMM

To extract the category name from the campaign name, we can write something as follows:

REGEXP_EXTRACT(Campaign, "Category:(.*?[^_]*)")

.*? means capture everything after Category: The ? is added to make .* non-greedy by matching little data.

[^_]* to stop at the _ underscore. Please note when the caret comes between the square brackets [^], it means doesn’t contain.

mm
Ahmed Ali
Ahmed launched his first digital campaign in 2013 through participating in Google Online Marketing Challenge. He competed with over 12,000 students from 80 countries to win the first place in Africa & MENA region and one of the best five campaigns in the world. Today, he works as an Associate Digital Director at Keyade Middle East.

29 Comments

  1. Patrick Siki
    Reply

    Hey Ahmed,

    Thanks for your article that helped me fine up my Data Studio report 🙂

    I have a question :

    In your last example you illustrate with an formula that allows to extract everything AFTER some specific text.

    What would be the formula to extract everything BEFORE some specific text?

    For example in ‘SN-EN_Category:Cars_Exact’ I’d like to keep ‘SN-EN_Category:Cars’ and remove ‘_Exact’?

    Thank you in advance for your help!

    • mm Ahmed Ali
      Reply

      Hi Patrick

      Thanks for your comment.

      I would use something like this REGEXP_EXTRACT(Campaign, “(.*?[^_]*)”) to extract everything before the underscore _

      Let me know if you need anything else.

      Best,
      Ahmed

  4. Ian Feavearyear
    Reply

    I want to group 3 landing pages to return a single value (there are 3 URLs that are all the home page) but I want to return the actual Landing Page value in all other cases. I tried:

    CASE
    WHEN REGEXP_MATCH(Landing Page,”^\/(en-us((\/index)?\.html)|index\.html)”) THEN “Home Page”
    ELSE (Landing Page)
    End

    However, it doesn’t seem to like my ELSE statement. Any ideas?

    • mm Ahmed Ali
      Reply

      Hello,

      Add the false condition before ELSE and it should work:

      CASE
      WHEN REGEXP_MATCH(Landing Page,”^\/(en-us((\/index)?\.html)|index\.html)”) THEN “Home Page”
      WHEN REGEXP_MATCH(Landing Page,”^.*[^index].*”) THEN (Landing Page)
      ELSE “Undefined”
      End

      Let me know hot it goes.

      Best,
      Ahmed

  5. TonyYa
    Reply

    Thanks for sharing! I have a question – maybe you or someone here might help me to figure out whats the best way to manage it with data studio. I have a field that could contain one ID or even an aggregation of IDs in the data source but I want to separate them. So if the value is “1” it should count to “abc”, if the value is “2” to “xyz” and so on…. same with the value “1,2,3”. That works so far but if I manage the separation with a case statement the “1,2,3” does of course count for “abc” but not for “xyz” anymore. Furtheron “2,1,3” doesn’t count for “abc”. I’m thankful for any help!

  6. Xeo
    Reply

    Hi,

    I’d like to create custom dimension where I;ve got 3 types of campaign:
    1. Search
    2. GDN
    3. APP
    All campaigns have a prefix:
    1. Search starts [Search]…
    2. GDN starts [GDN]…
    3. APP starts [APP]…

    I create regex:

    CASE
    WHEN REGEXP_MATCH(Campaign, “^.*(Search).*”) THEN “Search”
    WHEN REGEXP_MATCH(Campaign type, “^.*(GDN).*”) THEN “GDN”
    WHEN REGEXP_MATCH(Campaign type, “^.*(APP).*”) THEN “APP”
    ELSE “Other” END

    But there is error
    Unable to aggregate ratio metrics in the request. Please select another metric.
    Error ID: 068984b8

    In standard regex I’d use “\” to negation brace but in DC it’s not working :/
    So I use .* to work around the problem with brackets.
    Any idea what I working wrong?

    • Caro
      Reply

      Hi Xeo, Do you find the solution for your Regex please?

      I try to make the same but It doesn’t work. For exemple :

      I’d like to create custom dimension where I;ve got 3 types of campaign:
      1. Brand (contain Brand and Brand + produit)
      2. Shopping
      3. Generique
      All campaigns have a prefix:
      1. Brand starts : FR – brand and FR – brand – produit
      2. Shopping starts : FR – Shopping
      3. Generique starts : FR – generique and UK – generique

      I create regex:

      CASE
      WHEN REGEXP_MATCH(Campaign, “^.*(FR – brand|FR – brand – produit).*”) THEN “Brand”
      WHEN REGEXP_MATCH(Campaign type, “^.*(FR – Shopping).*”) THEN “Shopping”
      WHEN REGEXP_MATCH(Campaign type, “^.*(FR – generique|UK – generique).*”) THEN “Generique”
      ELSE “Other” END

      Any idea what I working wrong?

  7. TonyYa
    Reply

    Hi @Ahmed Ali,

    thank you for your reply. Here my problem with an example – hope that makes things clearer…

    I try to extract different numbers out of a row of numbers.

    E.g.: I try to extract 8 and use

    CASE WHEN REGEXP_MATCH(Text “Source”, “^.*8.*”) THEN “Hello” ELSE “Others” END
    or maybe just CASE WHEN REGEXP_MATCH(Text “Id [Courses]”, “.*8.*”) THEN “Hello” ELSE “Others” END

    For my understanding these expressions should extract the 8 out of every series but it doesn’t… I have the following options with 8 in the series:
    39x(7,8) + 3x(8) + 2x(8,1) + 3x(8,9) + 1x(11,8) + 3x(11,7,8) + 11x(11,7,8,9) + 31x(11,7,8,9,10) + 1x(11,7,8,9,10,12) + 2x(7,8,10) + 224x(7,8,9) + 13x(7,8,9,10) = 334 times the 8 in a series.
    But my result is 48 so my assumption is that only (7,8), (8), (8,1), (8,9) and (11,8) are detected…

    In the data studio forum I got a reply to try this:
    CASE
    WHEN REGEXP_MATCH(Text “Source”, “x\\([^8\s]*8[^8\s]*\\)”) THEN “Hello”
    ELSE “0”
    END
    but unfortunetly data studio is not parsing this function.

    I also want to detect the othere numbersfrom above(7,9,11,…) so if there is a good solution to make this I’ll be gratefull

    Do you or does any body know why? :/ Thank you for any advice.

  11. Tom Mc
    Reply

    Hi Ahmed,

    I’m looking at getting data studio to pick up the contents of a landing page URL and if it matches to then label it.
    It seems to return the sessions of just that specific page, any tips on it including anything ‘Containing’ that url?

    CASE WHEN REGEXP_MATCH(Landing Page, “/scotch-whisky/single-malts/speyside/”) THEN “Speyside” WHEN REGEXP_MATCH(Landing Page, “/scotch-whisky/single-malts/highland/”) THEN “Highland” ELSE “Other” END

    Thanks,

    Tom

  12. dilman yasin
    Reply

    Great info!

    I am having trouble with creating a dimension. I am very new to Data Studio so please bare with me. I have 9 pieces of the pie that I want to aggregate into 4. This is regarding WIFI usage.
    I’m a visitor
    I’m a visitor (interior)
    I’m a patient
    I’m a patient (interior)
    I’m an employee
    I’m an employee (interior)
    I’m a patient
    I’m a patient (interior)
    I’m a provider
    I’m a provider ( interior)
    What type of formula will help me with this?

    Thank you,

    Dilman

  13. Julia
    Reply

    Hi-

    I have a question that I’ve been working on for a while and haven’t found the solution: how do you account for spaces in DataStudio RegEx?

    We apply a filter to our Google Analytics data to clean up the information displayed in our client reports. However, I can’t figure out how to account for spaces when using the RegExp REPLACE. Two examples :

    1. We have videos on our website, and the videos have an ID number preceding the actual title. We only want the title to be displayed in the report. The naming conventions of the video is as follows: “00 – Title”
    I’m using this expression to get rid of the ID:
    REGEXP_REPLACE(Event Label, “^([0-9]{1,2}.[0-9A-Z]*)”, “”)

    This gets rid of any ID numbers before the title, but doesn’t get rid of the ‘–‘

    I’ve tried using ‘\\s’ for the spaces but that doesn’t work. Any ideas?

    Thanks!

  14. Sohail
    Reply

    Hi Ahmed,

    I need to create custom dimension out of the main dimension, for example I need to extract first two characters of placement value which is “EN” in this case. Can you please help?

    Dimension name: Placement
    Value: EN_Destination_Creative-name_All_Richmedia

    Regards,
    Sohail

  15. Mark
    Reply

    Note that in your first example:

    CASE WHEN REGEXP_MATCH(meduim,”^paid”) THEN “Paid” ELSE “Organic” End

    Medium is misspelled as meduim – totally not a huge deal, unless someone copies and pastes into their setup and it doesn’t work….

  16. Viraj Shah
    Reply

    Hi Ahmed,

    Thanks for this article! This is really very helpful.

    I tried to use your last example of Regex Extract in my case but it turns out with no output. Can you please confirm and help me with the regex?

    In my case, the campaign name contains city names. Example: Campaign Name: Engagement|Search|Los Angeles|Geo and we want to extract the value of city from Campaign name field so, I tried out using: REGEXP_EXTRACT(Campaign, “|Search|(.*?[^|Geo]*)”) based on your last example on regex extract. And, it gave no output. Screenshot: https://image.prntscr.com/image/M2Qg5XN2RhqsKxPRUwPipQ.png

  17. Ricardo Abreu
    Reply

    Hi Patrick

    I have the following string and I need to extract only the number after the string “LIO-MSW-total=”. How can I do that? I´m tryng to use the space but [^ ] but is not working

    Computation{ CEPA=145;ICPA=90;CPPPA=476;PCPMA=240;PACPSA=82;PPSCA=336;IPCA=210;CDBD=157;GOCA=53;CDA=48;PPCA=17640;IPBD=64;FCA=1367;AA=80;NCBD=0;SCBD=833;CIDMD=0;RCA=34;CBD=0;FTFD=2;LIO-MSW-overlapping-total=21857;LIO-MSW-total=793195 } User{ PCPMA=839;PACPSA=655;IPCA=6999;CDA=2072;AA=753600;USER-MSW-overlapping-total=764165;USER-MSW-total=792591 }

  19. Brian
    Reply

    Hey Ahmed,

    Thanks for the great tutorial. Could you help me with a REGEXP_EXTRACT?

    I’m trying to extract only two events from a list – the “MBA Multi-School Events” and “S7” events. Here is a sample list of events…

    Attend: MBA Multi-School Event – 09/09/2015
    Attend: S7 – 07/18/2013
    Attend: Info Session – 05/05/2012
    Attend: Consultation – 02/03/2011
    No Show: Assessment – 06/05/2016
    Waitlist: Campus Visit – 05/07/2017

    Here is my regex which extracts all events with “Attend:” My question is how do I group only the Multi-School and S7 events from the list?

    REGEXP_EXTRACT(Events – Comma Separated, ‘Attend:(.*)’)

    Thank you,
    Brian

  20. Goncalo Pinto
    Reply

    Hi @Ahmed Ali,
    Thank you so much for all this. It helped me so much!

    How do you make a regex extract to get the string between something? for example:

    AAA_field1_field2_field3_blablabla
    BBB_field1_field2_field3_blablabla

    how would you get for example field1? how about for field2?

    what if you wanted field1 if AAA or field2 if BBB?

    Thank you so much once again.

Leave a Reply

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