LinkedIn
Twitter
Hey I build custom channel grouping using calculated fields with this code:
CASE
WHEN LENGTH(Page referrer) = 0
AND NOT CONTAINS_TEXT(Full page URL, "utm_")
THEN "Direct"
WHEN (
CONTAINS_TEXT(Page referrer, "google.com")
OR CONTAINS_TEXT(Page referrer, "bing.com")
OR CONTAINS_TEXT(Page referrer, "yahoo.com")
OR CONTAINS_TEXT(Page referrer, "duckduckgo.com")
OR CONTAINS_TEXT(Page referrer, "baidu.com")
OR CONTAINS_TEXT(Page referrer, "yandex.com")
OR CONTAINS_TEXT(Page referrer, "naver.com")
)
THEN "Organic Search"
WHEN (
(CONTAINS_TEXT(Full page URL, "utm_medium=cpc") OR CONTAINS_TEXT(Full page URL, "utm_medium=paid"))
OR (CONTAINS_TEXT(Full page URL, "gclid=") OR CONTAINS_TEXT(Full page URL, "msclkid=") OR CONTAINS_TEXT(Full page URL, "fbclid="))
)
THEN "Paid Search"
WHEN (
CONTAINS_TEXT(Page referrer, "facebook.com")
OR CONTAINS_TEXT(Page referrer, "instagram.com")
OR CONTAINS_TEXT(Page referrer, "twitter.com")
OR CONTAINS_TEXT(Page referrer, "linkedin.com")
OR CONTAINS_TEXT(Page referrer, "pinterest.com")
OR CONTAINS_TEXT(Page referrer, "tiktok.com")
OR CONTAINS_TEXT(Page referrer, "reddit.com")
OR CONTAINS_TEXT(Full page URL, "utm_medium=social")
)
THEN "Social"
WHEN (
CONTAINS_TEXT(Full page URL, "utm_medium=email")
OR CONTAINS_TEXT(Page referrer, "mail.google.com")
OR CONTAINS_TEXT(Page referrer, "outlook.live.com")
OR CONTAINS_TEXT(Page referrer, "mail.yahoo.com")
)
THEN "Email"
WHEN (
CONTAINS_TEXT(Full page URL, "utm_medium=affiliate")
OR CONTAINS_TEXT(Page referrer, "affiliate.")
)
THEN "Affiliate"
WHEN LENGTH(Page referrer) > 0
AND NOT (
CONTAINS_TEXT(Page referrer, "google.com")
OR CONTAINS_TEXT(Page referrer, "facebook.com")
OR CONTAINS_TEXT(Page referrer, "utm_medium")
)
THEN "Referral"
ELSE "Other"
END
the problem is the grand total number is not the same, looks like there is a rule overlap, how should I fix this?
