CASE statements in Google Data Studio

CASE statements let you to create new dimensions in Google Data Studio based on existing ones. Syntax of CASE statement reminds SQL a lot:

CASE
    WHEN condition THEN result
    WHEN condition THEN result
    ELSE result 
END

To create a new calculated field – open your Data Studio report, and click “Create new field”:

Give your new dimension a name, write the CASE statement in “Formuła” field and save it:

That is an example of CASE statement for Channel Grouping in Google Analytics:

CASE
WHEN ((Source="direct" AND Medium="(not set)") OR Medium="(none)") THEN "Direct"
WHEN Medium="organic" THEN "Organic Search"
WHEN (Social Source Referral="Yes" OR REGEXP_MATCH(Medium,"^(social|social-network|social-media|sm|social network|social media)$")) THEN "Social"  
WHEN Medium="email" THEN "Email"
WHEN Medium="affiliate" THEN "Affiliates"
WHEN Medium="referral" THEN "Referral"
WHEN (REGEXP_MATCH(Medium,"^(cpc|ppc|paidsearch)$") AND Ad Distribution Network!="Content") THEN "Paid Search"
WHEN REGEXP_MATCH(Medium," ^(cpv|cpa|cpp|content-text)$") THEN "Other Advertising"
WHEN (REGEXP_MATCH(Medium,"^(display|cpm|banner)$") OR Ad Distribution Network="Content") THEN "Display" ELSE "(Other)"
END

An example of another CASE statement I used to create a new dimension for filtering purpose, based on Google Ads campaign name:

CASE
WHEN REGEXP_MATCH(Campaign, "brand.*") THEN "Brand" 
WHEN REGEXP_MATCH(Campaign, "generic.*") THEN "Generic"
WHEN REGEXP_MATCH(Campaign, "gdn.*") THEN "GDN"
ELSE "other"
END

Leave a Reply

Your email address will not be published. Required fields are marked *