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
| Content Type | Users | # of Articles | Users per article |
|---|---|---|---|
| How To Do It Article | 4201 | 12 | 350 |
| Does It Work? Article | 9239 | 42 | 220 |
| Comparison Article | 300 | 8 | 37.5 |
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
