Data Studio Create Calculated Field - Regexp Extract Error

[#1]

Hi,

I'm trying to create a calculated field separating brand and non brand campaigns by using regexp_extract function to search for the substring "BKWS" or "SKWS" inside the campaign field. But I kept on getting an error. Would anyone know how to debug this?

My formula as follows:

CASE WHEN REGEXP_extract(Campaign, '(\W|^)(BKWS)(\w|$)') THEN "Brand" WHEN REGEXP_extract(Campaign, '(\W|^)(SKWS)(\W|$)') THEN "Non Brand" ELSE 'n/a' END

Thanks,

[#2]

Hi Adrian H,

What error are you getting? Documentation shows syntax like this:

REGEXP_EXTRACT(field_expression, regexp)

My first guess, lacking any other information, would be the case of the function call. Can't say for certain, but your expression looks to be more complicated than it needs to be unless you are using the search results and variables produced by the grouping ($1, $2, $3). If all you are doing is identifying the brand and non-brand campaigns, this should suffice:

REGEXP_EXTRACT(Campaign,  '(SKWS)'))

Best of Luck!

Pete

[#3]

Hi Pete, 

Thanks for the swift response!

I tried the formula you have posted and it works, but my attempt is to use this as a filter option on DataStudio and be able to select BKWS and SKWS. What your formula only allows is to shows the option SKWS and null for the others (Does not contain "SWKS"). Hence I used a case statement at the first place to show the logic sequence, but it is still not letting me parse even if I do this:

CASE WHEN REGEXP_EXTRACT(Campaign, '(BKWS)') THEN "Brand" WHEN REGEXP_EXTRACT(Campaign, '(SKWS)') THEN "Non Brand" ELSE 'n/a' END

Wonder if you would know a work around for that?

Cheers,

Adrian

[#4 · Accepted solution]

Hi Adrian,

That looks right to me for what you want to do. I was only addressing the syntax for REGEXP_EXTRACT.

You might be able to optimize a little by making one call to REGEXP_EXTRACT, then analyze the result. Again, this is based on Javascript and Regular expressions. I don't do AdWords scripts and haven't tried Data Studio yet. I am assuming REGEXP_EXTRACT works much that same as in Perl in that results from expressions surrounded by parenthesis are available as $1, $2, etc. Try it out, I don't think it will make much difference in performance. Something like this:

var result = REGEXP_EXTRACT(Campaign, '(BKWS|SKWS)') ;

CASE WHEN $1 =='BKWS' THEN "Brand"

WHEN $1 == 'SKWS' THEN "Non Brand" ELSE 'n/a' END

Best of Luck!

Pete

[#5]

If you're using a Case Statement then it might work better using regexp_contains or regexp_match