Are you Interested in creating data groups and applying conditions to your data to let it make more sense?
Welcome to the Google Looker Studio CASE WHEN function. It enables users to transform their data and create custom reports best representing their business.
Are you interested in learning more about the statement and how you can use it?
Read our blog to find out.
What is Google Looker Studio Case When Statement?
Case in Looker Studio is a conditional function. When the input (data) meets a given set of pre-defined criteria, it automatically returns a result.
Simply put, it is an If This, Then That logical statement.
It allows you to evaluate your data and perform relevant actions automatically. Eventually, it saves you tons of manual labor.
Businesses find this super valuable.
A simple statement can help them create custom categories and groups of data and run unique analyses to produce actionable results.
If you have any queries about our analytics services, get in touch with us to let our team of highly professional experts take care of your concerns.
How Does a Case Statement Work in Google Looker Studio
The statement starts with the keyword CASE, has some clauses in between, and finishes with the keyword END.
The clauses can be one of three types.
- When - the condition or set of conditions against which it evaluates the data.
- Then - the result returned by the statement, whether true or false, after assessing your data.
- Else - an optional clause that can specify the response when none of the WHEN clauses are true.
WHEN condition
The first part of the statement, where you set up a criteria.
A boolean expression that assesses the given data as true or false against a criteria.
If the data meets the condition, it will return true; otherwise, it gives false.
THEN result
For every WHEN statement, there has to be a THEN statement.
If the statement returns true for the WHEN condition, the CASE statement clearly outlines what action applies to it with the WHEN clause.
For example, if the WHEN condition is WHEN Gender = “Female” THEN Title = “Ms.”
In our example, where there can be multiple WHEN conditions for Gender, you can have a series of different WHEN conditions in the statement.
It will analyze the data for the conditions, one by one, and return the response with the first true.
So, if the first condition is WHEN Gender = “Male” THEN Title = “Mr.”, and the first name in the data is Jonathan with Gender = Male, it would return Mr. as the title without moving to the second or third condition.
ELSE result
The users may include an ELSE clause in their WHEN statement.
If the data does not have a single true, it will either return a NULL result or if there is an ELSE condition, it will return the response specified in this condition.
For example, if WHEN “Conversions” = Null THEN “0”
ELSE Conversions
Syntax
As previously pointed out, a CASE statement begins with CASE, has the conditions in the middle, and ends with END.
A typical statement looks like the one below.
CASE input_expression
WHEN expression_to_match THEN result
[WHEN expression_to_match THEN result]
[...]
[ELSE else_result]
END
To set the conditions of your CASE WHEN statement, you can use a few operators, find some examples shared below.
- = (equal to)
- != (is not equal)
- < (greater than)
- > (less than)
- | (and)
- AND logic
- OR logic
- REGEX_Match (matching regular expression)
How to Apply the CASE WHEN function In Google Looker Studio?
Now that we have established the statement, it is time to show you how you can write one in Looker Studio.
Use the following steps to write your function.
- Go to Google Looker Studio > (desired) Report > Manage added data sources.
- You will find your data sources; click Edit on the relevant one to make changes.
- To write the CASE WHEN function, click the Add a Field button.
- In the opening interface, write your function in the Formula box. Review it for errors, click Save, and make any improvements if desired.
- Once implemented, you can publish the newly created dimension on your Google Looker Studio Report as a chart, table, control, and scorecard or apply it as a filter.
|
Remember CASE statement evaluates the data in the same order as the WHEN clauses. Ensure you prioritize them correctly because even if the data meets the criteria for your priority clause, it will not bother evaluating it for a statement at the bottom if it met a true clause earlier. |
Use Cases for Looker Studio Case When Function
The CASE WHEN function can organize & analyze data in ways that make sense for individual businesses.
Below, we share some ways you can employ the CASE WHEN statement in Google Looker Studio to transform your data meaningfully.
1. Create Customer Segments
Use the CASE WHEN function to create one custom segment for your customers instead of reviewing different reports.
You can combine their demographic data sets with the marketing channels (used to visit your site), interests, and more.
CASE
WHEN REGEXP_MATCH (Session default channel group, ‘(.Paid.|.Aggregator.|.Paid Social.|.Paid Search.|.Display.|.Affiliate.)’)
THEN ‘Paid Channels’
ELSE ‘Owned Channels’
END
Notice that the formula uses the | to combine different data sets and then relies on REGEX (matching regular expressions) to create the desired patterns.
2. Differentiate Non-branded Traffic from Branded Traffic
You can use the CASE WHEN function to differentiate between the branded and non-branded queries on your Google Search Console data.
CASE
WHEN REGEXP_MATCH (Query, '^(?i).*brandterm1.*') THEN "Branded"
WHEN REGEXP_MATCH (Query, '^(?i).*brandterm2.*') THEN "Branded"
WHEN REGEXP_MATCH (Query, '^(?i).*brandterm3.*') THEN "Branded"
ELSE "Non-Branded"
END
In the formula above, replace the “brandterm1. / brandterm2. /brandterm3.” with the brands most frequently searched on your website.
You can find the data by going to Google Search Console > Search Results and scrolling down to Queries.
3. Differentiate Paid Traffic from Unpaid Traffic
Another meaningful way to look at your data could be to organize traffic from ads and other sources.
The other sources should signify the organic traffic earned through SEO efforts.
CASE
WHEN REGEXP_MATCH(Default Channel Grouping, "Paid Search|Display") THEN "Paid Traffic"
ELSE "Earned Traffic"
END
The code calculates the paid traffic by combining the “Display” Default Channel Grouping and “Paid Traffic.”
4. Differentiate Between Campaigns
You can use the CASE WHEN statement to group your campaign data for comparative analysis. Some ways to do so include grouping them according to their ad type, time of the year, location, budget, etc.
CASE
WHEN Campaign='Campaign A' THEN "Group 1"
WHEN Campaign='Campaign B' THEN "Group 1"
WHEN Campaign='Campaign C' THEN "Group 1"
WHEN Campaign='Campaign D' THEN "Group 2"
WHEN Campaign='Campaign E' THEN "Group 2"
WHEN Campaign='Campaign F' THEN "Group 3"
ELSE "Other Campaigns"
END
Replace the Campaign titles with your actual campaign titles and replace the Group title with one suited to your grouping criteria.
5. Identify Trends
Another option for customized data analysis in Google Looker Studio with the CASE WHEN function is to identify your trending products or services.
CASE
WHEN Average Position < 4 THEN “Top 3” THEN 0
WHEN Average Position < 11 THEN “Top 10”
WHEN Average Position < 21 THEN “Top 20”
ELSE “Rest”
END
This code groups the keywords on your website according to popularity. The information could help modify the marketing strategy and improve returns.
You can tweak the same function to identify the most popular products on your e-commerce site.
You may use the output to reallocate your marketing resources and efforts, maintain stocks, and make other data-driven decisions.
CASE
WHEN item revenue >= 1000 THEN “High Revenue”
WHEN item revenue >= 500 and item revenue < 1000 THEN “Mid Revenue”
WHEN item revenue < 500 THEN “Low Revenue”
END
Conclusion
The Google Looker Studio CASE WHEN function allows users to organize, segment, and analyze their data in a customized manner.
It is an If This, Then That conditional statement incorporating several other logical operators to group data.
The first part of the statement deals with the WHEN clause. You write your condition to evaluate the data.
The second part is the THEN clause. This clause explains what happens to the data if it returns true for the WHEN condition.
Finally, ELSE is an optional condition where if the data is not returned true for any of the WHEN conditions, you specify a response instead of returning NULL.
To write the function in the Looker Studio, go to the Report, click Manage added data sources, Edit the relevant data, click Add a Field button, and write and Save in the Formula box.
Examples of the use cases for the Google Looker Studio CASE WHEN Function include creating customer segments.
You may group the branded and non-branding traffic & paid traffic, and unpaid traffic.
Other uses include creating campaign groups and identifying the trending services, products, or keywords.
Like what you read? Learn more about digital analytics on our blog here.