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.

43 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!

  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.

  22. Darren
    Reply

    Hi Ahmed,
    Great article – thanks a lot! I’m trying to create a calculated field within Google Data Studio to combine versions of pages with and without trailing slashes. Would you be able to advise how best to go about this please?
    Thanks,
    Darren

  23. Joey
    Reply

    Hello Ahmed,

    I’m checking your website and it is super helpful. I’m working on REGEXP_EXTRACT and having difficulty creating the correct regexp for my ad names. Let say I have

    let’s says I have 4 ad names with the following labels:
    RT_AP_Photo_AfterPay
    RT_DPA_Text1
    PP_AP_Photo_AfterPay
    PP_Photo_AfterMay

    so I’d like to display only ‘Photo_AfterPay ’, ‘DPA_Text1 ’, ‘Photo_AfterPay ’ & ‘Photo_AfterMay’
    what would be the formula to remove ‘RT_ | RT_AP_ | PP_ | PP_AP_’ from my report?
    thank you in advance!

  24. Sofie
    Reply

    Hi Ahmed! Great article!

    I am quite new to Data studio and i need to extract the last 8 characters from a string. The string looks like this:
    CV_LKI/3490_lki_xxxxxx_yxyxyxyxxy/92703940

    The part before the last / is almost never the same so i need to count 8 characters from the right or all characters after the second /.

    I thinks this should be simple but i can’t manage to solve it.

    Thank you! /Sofie

  25. John Benfield
    Reply

    Hi Ahmed

    Many thanks for the excellent article, it has already helped me massively, however I am having problems producing a value based on combining values…

    I am trying to create a field in a GDS table that pulls images depending on the combination of filter states. I have an initial calculated field called CALC_FIELD_A that is based on a GA standard Page value with two variants.

    CALC_FIELD_B then creates a value I use to create a value used to create a URL (CALC_FIELD_C).
    _________________
    CALC_FIELD_B:

    CASE
    WHEN REGEXP_MATCH(CALC_FIELD_A, “STATE1”) THEN “state1”
    WHEN REGEXP_MATCH(CALC_FIELD_A, “STATE2”) THEN “state2”
    ELSE “Other”
    END
    _________________
    CALC_FIELD_C:

    IMAGE(CONCAT(‘http://www.abc.com/’, CALC_FIELD_C, ‘.jpg’))
    _________________

    In my GDS table this succssfully returns two image2: state1.jpg and state2.jpg

    I then have a filter that uses CALC_FIELD_A to control the display of state1.jpg and state2.jpg – this works fine – I can toggle off either image successfully.

    However, I want to have a 3rd state image that is called if CALC_FIELD_A is both STATE1 and STATE2, so when in the GDS filter I select both, I get the third image, state3.jpg.

    I have tried as many alternative combinations of CASE statement I can, but nothing I’ve tried calls the third state. My most promising version so far for defining CALC_FIELD_B is below:
    _________________
    CASE
    WHEN REGEXP_MATCH(CALC_FIELD_A, “STATE1”) THEN “state1”
    WHEN REGEXP_MATCH(CALC_FIELD_A, “STATE2”) THEN “state2”
    WHEN REGEXP_MATCH(CALC_FIELD_A, “STATE1”) AND REGEXP_MATCH(CALC_FIELD_A, “STATE2”) THEN “state3”
    ELSE “Other”
    END
    _________________
    …but this still only returns the first two images.

    Can you advise what might be going wrong?

    Thanks in advance.

  26. John
    Reply

    Hi, thanks for the effort creating this post and answering to the questions made.

    Can you please help with the above?

    All of my ad groups end with one of the below:

    – [Exact]
    – [Broad]
    [Exact]
    [Broad]
    _[Exact]
    _[Broad]

    I would like to remove the above endings from my ad groups.

    I have created the below dimension in DS using the below code but it not works:

    REGEXP_REPLACE(Ad group, “( – [Exact]$)”, “”),
    REGEXP_REPLACE(Ad group, “( – [Broad]$)”, “”),
    REGEXP_REPLACE(Ad group, “( [Exact]$)”, “”),
    REGEXP_REPLACE(Ad group, “( [Broad]$)”, “”),
    REGEXP_REPLACE(Ad group, “(_[Exact]$)”, “”),
    REGEXP_REPLACE(Ad group, “(_[Broad]$)”, “”)

    Am I missing something here? Thanks!

  28. Darpan Nikam
    Reply

    Hi Ali,

    I need to create a custom filter which whelp me to select paid/organic traffic.

    My paid segment is
    ad content match regx (havas|countrypaid|nativelift|grouppaid) AND default channel grouping match regx (Video Advertising|Paid Search)

    This is what I have created

    CASE
    WHEN REGEXP_MATCH(Ad Content,”(havas|countrypaid|nativelift|grouppaid)”) AND REGEXP_MATCH(Default Channel Grouping, “(Video Advertising|Paid Search”) THEN “Paid Traffic”
    ELSE “Organic Traffic”
    END

    Thanks!
    Darpan Nikam

  29. florencia
    Reply

    ¿How can I include a negative condition on the case form?

    Example :
    CASE
    When(Medio != “Email|email” AND IS NOT “LANDING”) then “Newsletter”

    END

  30. John
    Reply

    Hi. I have quarter date filter.
    Hi Guys. It is possible to reflect the selected filter in data studio?
    I have a filter (Quarter Filter) date. If I select Q1 2019, Q2 2019, Q3 2019, Q4 2019 the output should be 2019 same as in 2018 but if I selected 1 quater the output should be the selected quarter itself. Thanks

  31. neelabh Srivastava
    Reply

    Hello, I want sum of the columns based on another column. For Example- Column A contains: Fruits name and column B contains Quantity of fruits.
    Result need – Sum of quantity (Col:B) where Fruits = Apple and Orange.

    Can you please help me.

  32. James
    Reply

    Hi,
    Is it possible to do something like this in Data Studio

    CASE
    WHEN REGEXP_MATCH(Page,’.*insight-and-expertise.*’) THEN “Insight”
    WHEN REGEXP_MATCH(Page,’^.*about.*’) AND NOT REGEXP_MATCH(Page,’^.*careers-at.*’) THEN “About Us (Excl. Careers)”
    ELSE “Other”
    END

  33. Benedikt
    Reply

    Hello there Ahmed, thank you for yourdetailed and helpfull blog-post!
    I have a question regarding string-categorys in Google Data Studio. I used ‘CASE WHEN REGEXP_MATCH’ to aggregate strings into categories. My keywords can live in different categories and thats something I can not manage with ‘CASE WHEN REGEXP_MATCH’ because every string is sorted into one category only. Is there a way I can different categories like “labels” to a keyword?

    Best reagrds!
    Benedikt

  34. Carlos Lluberes
    Reply

    Hi Ahmed,

    Thanks for the tutorial, it was very helpful. I just used it to group Search Queries from Search Console Data. I’ve already filtered this data using Filters, but with that scenario, I was able to use Include AND|OR Exclude rules.

    Is there any way to use a explicit “Exclude” with REGEXP_MATCH?

    In the meantime: What I have learned is that the order of the rules can be useful to get something like Exclude, if you combine it with AND. For example:

    WHEN REGEXP_MATCH(Query,”^.*(shirt|top|blouse).*”) AND REGEXP_MATCH(Query,”^.*(blue|navy|teal).*”) THEN “Blue Shirts”
    WHEN REGEXP_MATCH(Query,”^.*(shirt|top|blouse).*”) THEN “Unspecefied / Other Color Shirts”

Leave a Reply

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