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.6K members online now
4.6K members online now
Ask questions about Data Studio
Guide Me
star_border
Reply

Regex Help - Aggregating Campaign Data

Visitor ✭ ✭ ✭
# 1
Visitor ✭ ✭ ✭

Hey studio community. I'm new to regex and I'm trying to aggregate multiple campaigns into one for reporting purposes.

 

For example I'd like all the data for any campaign that contains the word "Search" to combine into an aggregate campaign named "Search". Same goes for all the data for any campaign containing "GDN". When i tried using the vertical bar (OR function) and exact campaign names I ran out of characters, and I was unsuccessful at my attempt to use wildcards.

 

The long version (shortened here) that I tried but ran out characters in the process looked like this...

CASE
WHEN Campaign, "(2017 - Search - Neighborhoods|2017 - Search - Branded Campaign)" THEN "Search" 

WHEN Campaign, "(2017 - GDN - Food Category / Shortcut Campaign|2017 - GDN - Lead Acquisition - Food Category / Shortcut Campaign)" THEN "GDN" ELSE "Other" END

 

Here are the campaigns I'm trying to aggregate. Any help would be greatly appreciated!!!
Screen Shot 2017-02-06 at 9.57.59 PM.png

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Marked as Best Answer.
Solution
Accepted by topic author Anthony A
February 2017

Regex Help - Aggregating Campaign Data

Follower ✭ ☆ ☆
# 2
Follower ✭ ☆ ☆

Hi Anthony,

You need to use a RegEx function within the CASE statement.

 

CASE
WHEN REGEXP_MATCH(Campaign,".*Search.*") THEN "Search"

WHEN REGEXP_MATCH(Campaign,".*GDN.*") THEN "GDN"

ELSE "Other"

END

 

View solution in original post

Marked as Best Answer.
Solution
Accepted by topic author Anthony A
February 2017

Regex Help - Aggregating Campaign Data

Follower ✭ ☆ ☆
# 2
Follower ✭ ☆ ☆

Hi Anthony,

You need to use a RegEx function within the CASE statement.

 

CASE
WHEN REGEXP_MATCH(Campaign,".*Search.*") THEN "Search"

WHEN REGEXP_MATCH(Campaign,".*GDN.*") THEN "GDN"

ELSE "Other"

END

 

Regex Help - Aggregating Campaign Data

Visitor ✭ ✭ ✭
# 3
Visitor ✭ ✭ ✭

Thank you! The usage of .* wasn't immediately clear to me, and I wasn't able to implement that correctly.