CASE WHEN function in Data Studio

One of the most used functions in calculated fields: the famous CASE function! You can use it to segment all types of data.

CASE function

Use

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

Applications

Segment URLs with the CASE function and the REGEXP_MATCH function

Calculate field:

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

Calculate field:

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!