AdWords
4.5K members online now
4.5K members online now
Use AdWords conversion tracking and reporting to measure your results. Have a question about Google Analytics? Post it here, on the Google Analytics Community!
Guide Me
star_border
Reply

Data Studio Create Calculated Field - Regexp Extract Error

Visitor ✭ ✭ ✭
# 1
Visitor ✭ ✭ ✭

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,

1 Expert replyverified_user

Re: Data Studio Create Calculated Field - Regexp Extract Error

Top Contributor
# 2
Top Contributor

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

 

petebardo -- Deadhead doing AdWords

Data Studio Create Calculated Field - Regexp Extract Error

Visitor ✭ ✭ ✭
# 3
Visitor ✭ ✭ ✭

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

 

Marked as Best Answer.
Solution
Accepted by topic author Adrian H
March 2017

Data Studio Create Calculated Field - Regexp Extract Error

Top Contributor
# 4
Top Contributor

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

 

petebardo -- Deadhead doing AdWords

Data Studio Create Calculated Field - Regexp Extract Error

Visitor ✭ ✭ ✭
# 5
Visitor ✭ ✭ ✭

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