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

Utilizing wildcards for custom dimensions/case statements

Visitor ✭ ✭ ✭
# 1
Visitor ✭ ✭ ✭

Hey guys,

 

I am wondering if there is a support (or a workaround) to utilize wildcards when creating custom dimensions within data sets.

 

For example, 

 

CASE when FIELDNAME1 like 'apples%' then 'CATEGORY1'

when FIELDNAME like 'oranges%' then 'CATEGORY2'

else 'CATEGORY3'

 

I get the impression it only supports basic operators from the documentation but am wondering if there is a workaround to solve this kind of problem.

 

Thanks for the help!

1 Expert replyverified_user

Re: Utilizing wildcards for custom dimensions/case statements

[ Edited ]
Visitor ✭ ✭ ✭
# 2
Visitor ✭ ✭ ✭

What I've had to do is one of the following (note the spacing wherein Data Studio will auto-detect dimensions):

 

1. Use the Case statement for each known instance of the Dimensions you're trying to categorize. This works best with marketing campaigns that are similarly-named. 

 

CASE 
WHEN FIELDNAME1 = 'apples' THEN 'Apples' 
WHEN FIELDNAME1 = 'apples_2' THEN 'Apples' 
WHEN FIELDNAME1 = 'oranges_3' THEN 'Oranges' 
ELSE '*' 
END

 

2. Use regular expressions to match dimension values - but be aware, I have not found regular expressions in Data Studio to function the same way they do in Tableau (for example) or Google Analytics. I honestly can't say I've had basic expressions ever work like expected so far (beta!). 

 

CASE 
WHEN REGEXP_MATCH(FIELDNAME1, '^(apple).*') THEN 'Apples' 
WHEN REGEXP_MATCH(FIELDNAME1, '^(orange).*') THEN 'Oranges' 
ELSE '*' 
END

 

Again, keep in mind the spaces at the end of each line, and that the documentation so far for regular expressions in Data Studio is still "beta". Your best bet is to start typing "=REGEXP" in the Calculated Field formula field and go from there with the tooltips. 

 

Hope this helps!

Re: Utilizing wildcards for custom dimensions/case statements

Google Employee
# 3
Google Employee
Hi Chris,

Can you provide some examples of how REGEXP_MATCH doesn't function as expected?

Re: Utilizing wildcards for custom dimensions/case statements

[ Edited ]
Visitor ✭ ✭ ✭
# 4
Visitor ✭ ✭ ✭

Final edit: Turns out Google Data Studio uses the RE2 regular expression syntax, which I can't say one way or another how similar/dissimilar it is than the one used in Google Analytics (POSIX? ERE?). While it is strange and ultimately extremely disappointing that a Google data reporting tool meant to be used in close conjunction with Google Analytics uses a different regular expression syntax and does not allow the ability to switch syntax, I suppose that's just the way it is. 

 

Be aware of this change if you are planning on investing time and resources into building complex reports in Data Studio. I'll post additional "conversions" from GA syntax to RE2 as I hear back from the Google rep I'm in contact with. 

Utilizing wildcards for custom dimensions/case statements

Visitor ✭ ✭ ✭
# 5
Visitor ✭ ✭ ✭

Hi @Rick E

as you asked for examples I guess I have one (I'm a rookie so might be also my lack of understanding) - 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 "Id [Courses]", ".*8.*") THEN "8_XYZ" ELSE "Others" END

 

For my understanding these expressions should extract the 8 (or 88,...) 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...

 

My solution works properly here and I got a hint that works also properly here (even a better solution because only the 8 and not 88 is extracted)

 

Kind regards

Utilizing wildcards for custom dimensions/case statements

Visitor ✭ ✭ ✭
# 6
Visitor ✭ ✭ ✭

Hi @Rick E

 

Here are several differences between REGEXP_MATCH in DataStudios and similar functions in Google Analytics and Google Sheets:

 

1) in DataStudios the escape character is a double \\.  In Google Sheets and Google Analytics it's \. 

2) In DataStudios REGEXP_MATCH is a full match.  In Google Analytics, "regex" is a contains match.  In Google Sheets, "REGEXMATCH()" is also a contains match.  In Google Sheets and Google Analytics, the regex functions will match 

 

These things weren't impossible to figure out, but it took a while.  I'd guess most DataStudios users will be coming from other Google products.