If you’re used to creating Segments and Advanced Filters in Google Analytics, and you’re using Google Data Studio to visualize your data, this post is for you.
There are serious limitations to Google Data Studio’s ability to understand, interpret, and apply your filters and segments from Google Analytics. Of course these features are helpful for basic users of GDS, but if you want to take your analysis and visualization a step further, then keep reading. I’ll be walking through the CASE statement in Data Studio, and how it can be used to create new fields for better analysis of your data from the 75+ data sources GDS currently connects to.
If you’re new to Data Studio, this post is more advanced than we’d recommend starting out with. Instead, read through my Beginner’s Guide to Google Data Studio, or our alumni’s post on what GDS is before diving deeper into specific ways of manipulating the data you bring into the platform.
- A ‘FIELD’ is a dimension or a metric which is used in your report. After you’ve added a data source, all of the fields within it will be available to use in your charts. Google Data Studio highlights dimensions in green and metrics in blue.
- CALCULATED FIELDS (or custom fields) allow you to create new metrics and dimensions based on existing metrics and dimensions available from within your data source.
CASE: Returns dimensions and metrics based on conditional expressions. The CASE statement lets you create new fields that use conditional logic to determine the field values. CASE is most often used to create new categories or groupings of data. Learn more with this Help article from Google.
Syntax
CASE consists of the WHEN clause, two parameters; the conditional argument (X1, X2) and the value to return (Y1,Y2) when the conditions are met. And finally, the END clause.
How CASE statements work in Data Studio
CASE works by evaluating boolean values, and returning the value you specify. For example, if you’re as sick of GA’s limited social analytics functionalities as we are on the Seer Analytics team, then use Data Studio to create a CASE statement that groups common social sources into single fields. See ‘Social Source’ and more CASE WHEN examples below.
Creating ‘Social Source’
CASE WHEN Source IN ( “facebook” , “m.facebook.com” , “l.facebook.com” , “facebook.com” , “lm.facebook.com” ) THEN “Facebook”
WHEN Source IN ( “l.instagram.com” , “instagram.com” , “instagram” ) THEN “Instagram”
WHEN Source IN ( “t.co” , “twitter.com” , “twitter” ) THEN “Twitter”
when Source IN ( “pinterest.com” , “pinterest” , “pinterest.ca” , “pinterest.co.uk” , “pinterest.fr” , “pinterest.jp” , “b.pinterest.com” , “pl.pinterest.com” , “pinterest.com.mx” , “pinterest.cl” , “id.pinterest.com” , “ru.pinterest.com” , “br.pinterest.com” , “pinterest.com.au” ) THEN “Pinterest”
WHEN Source IN (“youtube.com”) THEN “Youtube”
WHEN Source IN (“houzz.com”) THEN “Houzz”
ELSE “Other Source”
END
Creating ‘Social Medium’
CASE WHEN Medium IN (“social-ad”) THEN “Paid Social”
WHEN Medium IN (“social”) THEN “Organic Social”
ELSE “Social Referral”
END
Creating ‘Engagement’
CASE WHEN Page Depth in (“1″,”2″,”3”) THEN “Less than 4”
WHEN Page Depth in (“7″,”4”) THEN “4-7”
WHEN Page Depth in (“11”) THEN “10 or more”
ELSE “Other”
END
Creating ‘Channel Type’
CASE WHEN REGEXP_MATCH(medium,”^(cpc|ppc|cpm|paid)$”) THEN “Paid”
WHEN REGEXP_MATCH(medium,”organic” THEN “Organic”
ELSE “Other Channels”
END
Creating ‘Page Groups’
CASE WHEN REGEXP_MATCH(Page, “((?i).*^/$|^/\?.*).*”) THEN “Homepage”
WHEN REGEXP_MATCH(Page, “((?i).*.*/about.*).*”) THEN “About Us”
WHEN REGEXP_MATCH(Page, “((?i).*.*/contact$).*”) THEN “Contact Us”
WHEN REGEXP_MATCH(Page, “((?i).*^/services$).*”) THEN “Services”
WHEN REGEXP_MATCH(Page, “((?i).*^/blog$).*”) THEN “Blog Page”
WHEN REGEXP_MATCH(Page, “((?i).*.*/blog/.*).*”) THEN “Blog Posts”
WHEN REGEXP_MATCH(Page, “((?i).*.*/blog/category/.*).*”) THEN “Blog Category Page”
WHEN REGEXP_MATCH(Page, “((?i).*^/products/.*).*”) THEN “Product Pages”
WHEN REGEXP_MATCH(Page, “((?i).*^/solutions/.*).*”) THEN “Solutions Pages”
WHEN REGEXP_MATCH(Page, “((?i).*^/contact/.*).*”) THEN “Thank You – Contact”
ELSE “_Other”
END
- Clean up your inconsistent campaign tagging with LOWER (Campaign)
- Group leads and other high value users you’re tracking with COUNT (User ID)
- Combine fields for more granular data with CONCAT:
- (User Type , Device Category)
- (Landing Page , Count of Sessions)
- (Channel , Count of Sessions)
- ((Event Category , Event Action , Event Label)(“ | ” , Page))
- Replace full URL page names with page paths using REGEXP_REPLACE (Page,’https://www.seerinteractive.com’,’’)
- Create groups of pages for higher level analysis using CASE WHEN statements.
Let me know if these are helpful in the comments below, and make sure to share with the #measure community on social.