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

How to split campaign names by "_" to make more columns out of one.

Visitor ✭ ✭ ✭
# 1
Visitor ✭ ✭ ✭

Hi all!

In my agency we have naming conventions that we use to generate different filters in the dashboards we make.

 

For example, in Adwords campaigns, we could have sth like this:

AlwaysOn_Search_Brand_Product_321231

We separete this fields so we can filter them then in the dashboard. Using tableau this was pretty simple, but I couldn't find the way to do that in Data Studio. The only thing that came to me was to use regex formulas to exctract each field into a new column.

 

In this example, the expected output would be:

(one field per column)

AlwaysOn      Search      Brand      Product

 

Does anybody know how to do this with regular expressions?

Or Any other way?

 

Help!

 

thanks a lot,

 

César

1 ACCEPTED SOLUTION

Accepted Solutions
Marked as Best Answer.
Solution
Accepted by topic author César R
March 2017

How to split campaign names by "_" to make more columns out of one.

[ Edited ]
Visitor ✭ ✭ ✭
# 3
Visitor ✭ ✭ ✭

Hi!

 

I finally figured it out yesterday in stackoverflow (spanish): http://es.stackoverflow.com/questions/55362/c%C3%B3mo-separo-texto-por-guiones-bajos-de-una-celda-en...

 

It was simple enough after all...

 

To get each column just use this regex extract function: 

1st column: REGEXP_EXTRACT(Campaña, '^(?:[^_]*_){0}([^_]*)_')

2nd column: REGEXP_EXTRACT(Campaña, '^(?:[^_]*_){1}([^_]*)_')

3rd column: REGEXP_EXTRACT(Campaña, '^(?:[^_]*_){2}([^_]*)_')

etc...

The only thing that has to be changed in the formula to switch columns is the numer inside {}, (column number - 1).

If you do not have the final number, just don't put the last "_".

 

Lastly, remember to do all the calculated fields again, because (for example) it gets an error with CPC, CTR and other Adwords metrics that are calculated automatically.

 

Hope it helps!

 

View solution in original post

How to split campaign names by "_" to make more columns out of one.

Follower ✭ ✭ ✭
# 2
Follower ✭ ✭ ✭

Hi,

 

I was trying to do something similar yesterday.

 

Someone correct me if I'm wrong but as Dimension cannot be passed as parameter for formula so you can not do what you are trying to do easily unless each part of the string is a fixed length.

 

To match a string for example the 1st of the 5 parts, you could do something like this:

 

 

 

REGEXP_EXTRACT(Campaign, '(([\\D]|[[\\d]|)+)')

 

The formula should return the string without the "_".

 

The tricky part comes after that. 

 

Assume the 1st part is call "Tactics", I would have done something like:

 

REGEXP_EXTRACT(SUBSTR(Campaign, (LENGTH(Tactics) +1), (LENGTH(Campaign) - LENGTH(Tactics))), '(([\\D]|[[\\d]|)+)_')

 

Basically the above code should substring the "Campaign" field to the character after tactics till end of string and use it as the input for the regex extraction. The similar pattern would have give you the string for the 2nd part which I call it "Channel"

 

However, it seems there is no way to chain formula so you have to use the min length of the previous field(s) + number of separator to approx the start of the substring and use a very large integer for the length of the substring

 

I tried to pass another calculated field as parameter but again I got error message trying to do it.

 

If anyone have a better way or if Google can fix the chaining issue I would love to find out too.

 

Hope it helps...

 

Marked as Best Answer.
Solution
Accepted by topic author César R
March 2017

How to split campaign names by "_" to make more columns out of one.

[ Edited ]
Visitor ✭ ✭ ✭
# 3
Visitor ✭ ✭ ✭

Hi!

 

I finally figured it out yesterday in stackoverflow (spanish): http://es.stackoverflow.com/questions/55362/c%C3%B3mo-separo-texto-por-guiones-bajos-de-una-celda-en...

 

It was simple enough after all...

 

To get each column just use this regex extract function: 

1st column: REGEXP_EXTRACT(Campaña, '^(?:[^_]*_){0}([^_]*)_')

2nd column: REGEXP_EXTRACT(Campaña, '^(?:[^_]*_){1}([^_]*)_')

3rd column: REGEXP_EXTRACT(Campaña, '^(?:[^_]*_){2}([^_]*)_')

etc...

The only thing that has to be changed in the formula to switch columns is the numer inside {}, (column number - 1).

If you do not have the final number, just don't put the last "_".

 

Lastly, remember to do all the calculated fields again, because (for example) it gets an error with CPC, CTR and other Adwords metrics that are calculated automatically.

 

Hope it helps!