AdWords is now Google Ads. Our new name reflects the full range of advertising options we offer across Search, Display, YouTube, and more. Learn more

Data Studio
4.3K members online now
4.3K members online now
Ask questions about Data Studio
Guide Me
star_border
Reply

REGEXP_REPLACE - Multiple Variables/Output

[ Edited ]
Visitor ✭ ✭ ✭
# 1
Visitor ✭ ✭ ✭

I am setting up a new data studio report, and one of the items that will be delivered is browser.  The data that the report pulls from lists out specific versions of the browsers - i.e. Chrome 47, Chrome 48, Firefox 41, Firefox 42, etc

 

For this particular report, I would just like to report all of the Chrome results as one and Firefox as one.

 

I have gone in and added a custom dimension and can replace one or the other using the REGEXP_REPLACE formula of:

 

REGEXP_REPLACE(Browser_Type, '(Chrome [0-9]+)', 'Chrome')

 

When I use that and view the report, it is lumping all of the Chrome results as one (which I want).  My problem is how to also add in the Firefox part to the formula. I've been looking online and so far have struck out on how to include an "and" statement in this or an alternate way to handle so am hoping someone on this forum will have a solution.

 

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Marked as Best Answer.
Solution
Accepted by topic author Jeremy B
March 2017

REGEXP_REPLACE - Multiple Variables/Output

Follower ✭ ✭ ✭
# 2
Follower ✭ ✭ ✭

 

 

 

Due to the restriction on CASE statement which only allow text and number, you will not be able to do the following:

 

CASE WHEN REGEXP_MATCH(Browser_Type, '(Chrome [0-9]+)') THEN 'Chrome' WHEN REGEXP_MATCH(Browser_Type, '(Firefox [0-9]+)') THEN 'Firefox' ELSE Browser_Type END

 

With the Else using a non text and number, the formula will fail.

 

You can try something like this:

 

CASE WHEN REGEXP_MATCH(Browser_Type, '(Chrome [0-9]+)') THEN 'Chrome' WHEN REGEXP_MATCH(Browser_Type, '(Firefox [0-9]+)') THEN 'Firefox' WHEN REGEXP_MATCH(Browser_Type, '(IE 10)') THEN 'IE 10' WHEN REGEXP_MATCH(Browser_Type, '(IE 9)') THEN 'IE 9' WHEN REGEXP_MATCH(Browser_Type, '(IE [6-8]+)') THEN 'IE <9' ELSE 'Others' END

 

Hope this helps.

 

 

 

 

View solution in original post

Marked as Best Answer.
Solution
Accepted by topic author Jeremy B
March 2017

REGEXP_REPLACE - Multiple Variables/Output

Follower ✭ ✭ ✭
# 2
Follower ✭ ✭ ✭

 

 

 

Due to the restriction on CASE statement which only allow text and number, you will not be able to do the following:

 

CASE WHEN REGEXP_MATCH(Browser_Type, '(Chrome [0-9]+)') THEN 'Chrome' WHEN REGEXP_MATCH(Browser_Type, '(Firefox [0-9]+)') THEN 'Firefox' ELSE Browser_Type END

 

With the Else using a non text and number, the formula will fail.

 

You can try something like this:

 

CASE WHEN REGEXP_MATCH(Browser_Type, '(Chrome [0-9]+)') THEN 'Chrome' WHEN REGEXP_MATCH(Browser_Type, '(Firefox [0-9]+)') THEN 'Firefox' WHEN REGEXP_MATCH(Browser_Type, '(IE 10)') THEN 'IE 10' WHEN REGEXP_MATCH(Browser_Type, '(IE 9)') THEN 'IE 9' WHEN REGEXP_MATCH(Browser_Type, '(IE [6-8]+)') THEN 'IE <9' ELSE 'Others' END

 

Hope this helps.

 

 

 

 

REGEXP_REPLACE - Multiple Variables/Output

Visitor ✭ ✭ ✭
# 3
Visitor ✭ ✭ ✭

Thanks Chun - that did the trick.  One further question if you don't mind.  I'm going to apply this same solution to the operating system field but have another question:  The source data for the OS has them entered like:  Android 5.0, Android, 5.1, iOS 9, iOS 9.1

 

The formula such as  '(Android [0-9]+)' doesn't locate anything, I assume that is due to the decimal.  Is there something specific that needs to be done to find the decimal values?

 

Thank you again.

Re: REGEXP_REPLACE - Multiple Variables/Output

Follower ✭ ✭ ✭
# 4
Follower ✭ ✭ ✭

Hi,

 

Yes, you need to modify the regex to handle the decimal place.

 

The following should works:

(Android [0-9]+\.*[0-9]*)

(iOS [0-9]+\.*[0-9]*)

 

There is many ways to do the same things, including using \d instead but some of the regex syntax might not be supported in Data Studio yet.

 

If you are not concern about the version behind the OS name, you could do the following instead:

(Android .*)

(iOS.*)

 

I would suggest you bookmark this page, which I find it very useful when dealing with regex -> https://regex101.com/

 

Hope this helps.

REGEXP_REPLACE - Multiple Variables/Output

Visitor ✭ ✭ ✭
# 5
Visitor ✭ ✭ ✭

Perfect - I was close with some of my attempts but the (Android .*) seems to work perfect.  Thanks for the link - I don't think I'd come across that site in my searching - I'll be sure to bookmark.

 

Thanks again!

REGEXP_REPLACE - Multiple Variables/Output

Visitor ✭ ✭ ✭
# 6
Visitor ✭ ✭ ✭

Thank you so much for this solution! Saved me a ton of time.