Congratulations ???? ????, you have stumbled into one of the most advanced features of Looker Studio, some will say conditional logic, formatting, or Regex extraction… I am here to tell you Case() is THE MOST ADVANCED feature in Looker Studio.

Case Statements work by creating conditions, when met, then execute an action. The Case() function does this by three handlers; When, Then, Else, End.

For example, when {revenue} > $1000, then return the name of the highest sold product, else 0. End. Its truly amazing and I will show you just how to use it.

I am going to try and keep this ULTRA ACTIONABLE for you, using examples that you can copy & paste without too much effort. However, for the casual Looker Studio User, you might stop halfway through this article and circle back at a different time.

Case Statement Handler

Let’s first get to know the pieces of our case statement ????

Just so we all know what on earth a case statement is… it’s this ????????????

case
when Event Category = "Ecommerce" Then regexp_extract(Event Label, '.*([0-9])')
when Event Category = "Purchase" Then regexp_extract(Event Action, '.*([0-9])')
else "Oops"
End

For all the boring people who just want to find Google’s Documentation – Here is Googles Documentation on the Case Statement

Now, we are going to hop into the absolute basics of identifying how to use a case statment.

Case (This Let’s Google Know We Are About To Do Some Epic ????)

To start us off, we need “CASE”, this identifies that we are about to do some epic shit.

Case 
     When {{Something}} Then {{Something Else}}
     When {{Something}} Then {{Something Else}}
     When {{Something}} Then {{Something Else}}
  Else {{Fall Back Something}}  
End 
#you need to end the statement

When (something happens).

The ‘When’ is the following line after case and starts your statement.

You can use any of Looker Studio’s Functions to Identify the ‘When’ criteria.

or

As simple as when something = something else

When page = “LookerStudio.VIP/” then “Homepage”

For Example:

when REGEXP_CONTAINS( LEFT_TEXT(Event Label,5),’look.at.this’

This says, ‘When’ the left 5 characters of your event label contain the Regex criteria of ‘look.at.this’

Take a deep breath, It can be a lot. ???? ????

The point is that you can keep it simple or get advanced really quickly.

Start with something like “When Event Category = “Join” then move from there.

Then (Do Something )

Here I am changing the name of the country to a more identifiable trait:

Now we need to dive into the action of the case statement.

After your Case, ‘When’ , we need a ‘then’.

The same rules apply, you can use any function within Looker Studio here as well, but we are going to keep it simple to start.

Example:

When the homepage is / then it’s the home page.

Then we want to create a new group of terms for pages grouped “Home Page” (Why?? Maybe we want to identify key content types & its a good example)

case 
When page = "LookerStudio.VIP/" then "Homepage"
else "Definitely Not The Homepage"
end

When Nothing Fits, Use ‘Else’

The last handler is ‘Else’.

It’s the catch all, if it doesn’t fit any of the above, then do this.

It’s really great!

If the above conditions are not met, it then executes what the ‘Else’ was.

We wanted to take a look at the regions offered, but we only had the states. Solution…. Case statement

case 
When text_contains(region,'Washington|California|Oregon') then "West Coast USA"
When text_contains(region,'Maine|New York|Deleware') then "East Coast USA"
else "No Services Offered"
end

Nearly Unlimited When Statements Can Be Used?

The next thing you need to know is that you can keep stacking your ‘When’ lines

For example:

When DATE = “Sunday” Then Concat(DATE, “Happy”) 

When DATE = “Monday” Then Concat(DATE, “Sad”) 

When DATE = “Tuesday” Then Concat(DATE, “BONUS”) 

When DATE = “Wednesday” Then Concat(DATE, “Hump”) 

When DATE = “Thursday” Then Concat(DATE, “Slog”) 

When DATE = “Friday” Then Concat(DATE, “Frisky”) 

When DATE = “Saturday” Then Concat(DATE, “Stoked”)

We have one client over on visionlabs.com who is a global franchise. We are using 1 data Source in Big Query and using nearly 100 ‘When’ lines to differentiate which franchise it is. (So, I guess I’ve personally tested it to 109 lines)

Real World Examples Using Case Statements In Looker Studio

Everyone hates theory, let’s get into some juicy stuff and give you some ideas on how to improve and rock some SEXY data in your dashboards.

Grouping Content By URL Descriptors

We want to group all of our blog posts by their url keywords.

When a Blog URL has a word ‘how’ we want to group it in the “How To Do Something”

When a Blog URL has a word ‘what’ we want to group it in the “What to do”

When a Blog URL has a word ‘compared’ or ‘vs’ or ‘better’ we want to group it in the “ Comparison Articles”

First, we want to start off with our ‘Case’, then ‘Whens’

Case #You can see we are using And to identify two criteria 
When CONTAINS_TEXT(Page, ‘blog’) and CONTAINS_TEXT(Page, ‘how’) then “How To Do It Article” 
When CONTAINS_TEXT(Page, ‘blog’) and CONTAINS_TEXT(Page, ‘what’) then “Does It Work? Article” 
When CONTAINS_TEXT(Page, ‘blog’) and REGEXP_CONTAINS(Page, ‘compared|vs|better’) then “ Comparison Article” 
Else Page 
End 

The output will be something along the lines of

Join Your OLD pages

If you follow the “Best Practices of Google Analytics” you most likely have joined your Host Name & URI to show the full url in your ‘Page’ dimension

But you now have a bunch of duplicates in Google Analytics ☹️

We can fix that in Looker Studio

case 
When regexp_match(Page,'^LookerStudio.VIP.*') then Page
else concat(Hostname, Page)
end

Pretty Sweet? Right?!

Conclusions to CASE Statements

There are nearly unlimited ways to use and abuse the case statements in Looker Studio. Seriously, if you can think of it, most likely you can reiterate that exact thing if you have some knowledge of Regex & Case statements.

Remember, there are about a hundred other functions that you can use in Looker Studio and if you know case well, you can really leverage them to your advantage