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

Custom channel groupings with calculated fields

[ Edited ]
Follower ✭ ✭ ☆
# 1
Follower ✭ ✭ ☆

Hi there

 

Since Data Studio is not able to use my Custom Channel Groupings from GA, I am building it myself with a Calculated Field. However, I am having trouble getting the formula to work. Can anyone spot the problem? Data Studio stops recognising my code after (...) AND Source = "google" (...), after this bit, the "THEN" does not change color as it should if it was working, and the rest of the formula has the same problem.

 

My code currently looks like this

CASE
WHEN Default Channel Grouping = “Direct” THEN “Direct”
WHEN Medium = “cpc” AND Source = "google" THEN “Paid Search”
WHEN Default Channel Grouping = "Organic Search" THEN "Organic Search"
WHEN REGEXP_MATCH(Source, “.*(ClickDimensions|Directmail|mail).*”) OR Medium = "email" THEN "Email"
WHEN REGEXP_MATCH(Source, ".*(facebook|instagram).*" AND REGEXP_MATCH(Medium, ".*(referral|opslag).*") THEN "Organic Social"
WHEN REGEXP_MATCH(source, ".*(facebook|instagram).*" AND REGEXP_MATCH(Medium, ".*(annonce|post|ppc|cpc).*" THEN "Paid Social"
WHEN Default Channel Grouping = “Referral” THEN “Referral”
ELSE “Other”
END

 

Hope someone can help

1 Expert replyverified_user
1 ACCEPTED SOLUTION

Accepted Solutions
Marked as Best Answer.
Solution
Accepted by topic author Anton H
May 2017

Custom channel groupings with calculated fields

Follower ✭ ✭ ☆
# 8
Follower ✭ ✭ ☆

Hi Jennifer.

 

As mentioned above, I did get it to work, by not using logical operators such as AND OR. 

My working solution, might need some tinkering, since some of the things I added are specific for my case, but you can have it for inspiration.

 

CASE

WHEN Default Channel Grouping = 'Direct' THEN 'Direct'
WHEN Default Channel Grouping = 'Paid Search' THEN 'Paid Search'
WHEN Default Channel Grouping = 'Organic Search' THEN 'Organic Search'
WHEN Default Channel Grouping = 'Referral' THEN 'Referral'

WHEN REGEXP_MATCH(Source, '.*(ClickDimensions|Directmail|mail|Nyhedsbrev).*') THEN 'Email'
WHEN Medium = 'email' THEN 'Email'
WHEN REGEXP_MATCH(Source / Medium, '.*(Facebook|facebook|instagram).*(referral|opslag).*') THEN 'Organic Social'
WHEN REGEXP_MATCH(Source / Medium, '.*(Facebook|facebook|instagram).*(Annonce|annonce|post|ppc|cpc).*') THEN 'Paid Social'
WHEN REGEXP_MATCH(Source / Medium, '.*(google).*(cpc).*') THEN 'Paid Search'
WHEN Medium = 'referral' THEN 'Referral'

ELSE 'Other'
END

View solution in original post

Custom channel groupings with calculated fields

Explorer ✭ ✭ ✭
# 2
Explorer ✭ ✭ ✭

Are you using cost, impressions or click data in your table(s)? If so, the default channel groupings is not compatible with those metrics. I would recommend sticking to rules that only use medium, source, campaign or Keyword. 

If the formula  still does not work, I would try changing the rule for the paid search channel so you do not have to use AND, something like:

WHEN REGEXP_MATCH(campaign, '.*Search.*') THEN 'Paid Search'

 

If it works after using only single, standard  dimension rules, then you test using AND in the rules again but try tweaking the syntax. For example:

WHEN (medium = 'cpc' AND source = 'google') THEN...

I'm not sure if that's correct but you probably just need parentheses or something else to make CASE formulas work properly with AND in there. 

 

ALSO, remember to manually refresh the data source after making changes to custom dimensions! Sometimes changes do not take affect until you do a refresh. 

Custom channel groupings with calculated fields

Follower ✭ ✭ ☆
# 3
Follower ✭ ✭ ☆

Thank you for your reply. 

 

I tried the suggestions you made, but without any luck. It seems that no matter which arguments I put into the formula, it will not accept it and I keep getting "Invalid Formula". It seems I am forced to abandon this for now and come up with another solution. Thank you for your response.

 

//Anton

Custom channel groupings with calculated fields

Follower ✭ ☆ ☆
# 4
Follower ✭ ☆ ☆

Hi Anton,

I've also been struggling with the Data Studio formulas, but with luck.

I don't know if you are still waiting for an answer but what I've learned is that you should avoid the AND and OR statements within the formula. I don't know why, but these statements are often the cause for a parse error.
Data Studio applies the rules in the order they are written,  you can use this to your advantage to keep out the statements.

Also when trying to escape characters with an \ ,  you should also add an extra \ for an unknown reason to make it work.

 

Good luck.

 

 

 

 

Custom channel groupings with calculated fields

Follower ✭ ✭ ☆
# 5
Follower ✭ ✭ ☆

Hi Peter

 

Yes, I found the logical operators to be the problem as well, but never got around to updating on i there. But you are right, AND & OR breakes the parse, even though the Help-center says they are supported. 

Thank you for you reply.

Custom channel groupings with calculated fields

Visitor ✭ ✭ ✭
# 6
Visitor ✭ ✭ ✭

You could try to put the AND statement in parentheses: (Medium = “cpc” AND Source = "google")

 

Sometimes the function does work even if the editor doesn't show proper styling.

Custom channel groupings with calculated fields

Visitor ✭ ✭ ✭
# 7
Visitor ✭ ✭ ✭

Did you find a working solution for this and do you like to share it with us?

Marked as Best Answer.
Solution
Accepted by topic author Anton H
May 2017

Custom channel groupings with calculated fields

Follower ✭ ✭ ☆
# 8
Follower ✭ ✭ ☆

Hi Jennifer.

 

As mentioned above, I did get it to work, by not using logical operators such as AND OR. 

My working solution, might need some tinkering, since some of the things I added are specific for my case, but you can have it for inspiration.

 

CASE

WHEN Default Channel Grouping = 'Direct' THEN 'Direct'
WHEN Default Channel Grouping = 'Paid Search' THEN 'Paid Search'
WHEN Default Channel Grouping = 'Organic Search' THEN 'Organic Search'
WHEN Default Channel Grouping = 'Referral' THEN 'Referral'

WHEN REGEXP_MATCH(Source, '.*(ClickDimensions|Directmail|mail|Nyhedsbrev).*') THEN 'Email'
WHEN Medium = 'email' THEN 'Email'
WHEN REGEXP_MATCH(Source / Medium, '.*(Facebook|facebook|instagram).*(referral|opslag).*') THEN 'Organic Social'
WHEN REGEXP_MATCH(Source / Medium, '.*(Facebook|facebook|instagram).*(Annonce|annonce|post|ppc|cpc).*') THEN 'Paid Social'
WHEN REGEXP_MATCH(Source / Medium, '.*(google).*(cpc).*') THEN 'Paid Search'
WHEN Medium = 'referral' THEN 'Referral'

ELSE 'Other'
END

Custom channel groupings with calculated fields

Visitor ✭ ✭ ✭
# 9
Visitor ✭ ✭ ✭

Hey, thanks for sharing this. i've come quite far with modifying this but now i want to EXCLUDE a string (trying to find a way to split branded channels (with criterion Keyword X) vs non-branded channels (exclude keyword X). i am not a regex champion and i think there is some really stupid error in my syntax:

REGEXP_MATCH(Keyword, '(!example).*') 

 

any suggestions? Smiley Happy

 

Custom channel groupings with calculated fields

Follower ✭ ✭ ☆
# 10
Follower ✭ ✭ ☆

Hi there.

 

The CASE function is hierarchical. Which means which ever grouping traffic is assigned to first in the function is what goes. So just make sure you have the branded keyword RegEx first, then all other keywords can be assigned as non-brand.

 

Hope that made sense