One of the most used functions in calculated fields: the famous CASE function! You can use it to segment all types of data.
The CASE function allows related data to be grouped together under certain conditions.
The syntax of the function is as follows:
CASE WHEN ... THEN "..." WHEN ... THEN "..." ELSE "..." END
- WHEN is a condition. It accepts many functions like the 3 REGEXP functions and logical operators.
- THEN is the expected result if the data respects the associated condition (this will be your data group)
- ELSE is the mandatory fallback that will collect all the data that do not respect the conditions defined by WHEN
- END is mandatory
Note that the system reads CASE statements from top to bottom. If a data item respects 2 WHEN conditions, then it will only be captured by the 1st one in the reading order from top to bottom.
In the following case, all “URL” data are captured by the first condition i.e. Group 1. Group 2 and Group 3 will be empty (null value on Data Studio)
CASE WHEN REGEXP_MATCH(URL,"https://www.my-site.com/." THEN "Group 1" WHEN REGEXP_MATCH(URL,"https://www.my-site.com/categorie.") THEN "Group 2" ELSE "Group 3" END
Segment URLs with the CASE function and the REGEXP_MATCH function
CASE WHEN REGEXP_MATCH(URL,".*/tag/.*" THEN "Tags" WHEN REGEXP_MATCH(URL,"(.*/product/.*|.*\\.html$)") THEN "Product pages" ELSE "Autre pages" END
Useful with data sources:
- Google Analytics – Dimension “Pages
- URLs de la Search Console – Dimension “Landing page”
- Google Ads – Dimension “Landing page”
Create average position segments
CASE WHEN Position = 1 THEN "Top 1" WHEN Position < 4 THEN "Top 3" // so from 1,0x to 3,9x WHEN Position < 10 THEN "Top 10" // so from 4,0x to 9,9x ELSE "10+" END
Note that I start with the smallest position since the syntax is read from top to bottom.
Useful with the Search Console data source!