Case Statement Google Data Studio: A Comprehensive Guide

The purpose of this article is to explore one of the most useful functions in Google Data Studio, the CASE statement. It will cover the need, syntax, and use cases of the CASE statement in detail. The CASE function is one of the most advanced and powerful functions in Google Data Studio. 

Let’s see how this blog is structured for you:

1. What is the CASE Statement in Google Data Studio?
2. How CASE Statement Google Data Studio Work
3. CASE Statement Google Data Studio: Need
4. CASE Statement Google Data Studio: Fields and Calculated Fields
5. CASE Statement Google Data Studio: Syntax
6. CASE Statement Google Data Studio: Use Case
7. Conclusion

What is the CASE Statement in Google Data Studio?

CASE function returns dimensions and metrics based on conditional expressions. The CASE statement lets you create new fields that use conditional logic to determine field values. The CASE statement can create new categories or groupings of data sets. 

This function is going to help you a lot if you are not a pro in Google Data Studio (GDS). If you are interested in knowing how to create filters in Google Data Studio, how to segment data, or how to visualize data in a better way, then it is important to know about the CASE function.

How CASE Statement Google Data Studio Work

The CASE Statement Google Data Studio works by evaluating the boolean value assigned. Whenever a user a value the CASE Statement Google Studio will evaluate the boolean value for the matched case and then return the value assigned by the user.

CASE Statement Google Data Studio: Need

When we talk about applying filters to data or perform some segmentations on data, Google Data Studio causes some serious limitations, which are hurdles in the proper analysis of data. These limitations become very prominent when we want to perform some advanced level of data analysis. The CASE function comes handy in this scenario. If you are using Google Data Studio, it is a must learned function. 

Like all other functions that Google Data Studio provides (over 50), the CASE function also has some features. The features/attributes of the CASE statement are listed below: 

• The CASE function has some purpose.
• The CASE function has a specific syntax.
• The CASE function expects one or more parameters.
• The CASE function returns a value.
• The CASE function has certain restrictions on how it should be used.

CASE Statement Google Data Studio: Fields and Calculated Fields

CASE WHEN Google Data Studio function can create new fields that use conditional logic to determine the field values. Before moving forward, it is important to know about Fields and Calculated Fields. Let’s discuss them in detail.

Fields

A field is a dimension or a metric that is used in any report in Google Data Studio. After the integration of data sources (Google Data Studio offers 65+ data sources), all the fields of data sources are available to use in the charts. Google Data Studio highlights dimensions in green and metrics in blue. 

Calculated Fields

Calculated fields allow you to create new metrics and dimensions based on existing metrics and dimensions in the data source.

CASE Statement Google Data Studio: Syntax

The CASE consists of the “WHEN” clause and two parameters that are conditional argument (X1, X2), and the value to return (Y1, Y2) when the conditions are met. Data Studio CASE WHEN contains the “END” clause too.

CASE
    WHEN condition THEN result
    [WHEN condition THEN result]
    [...]
    [ELSE result]
END

We can summarize that CASE statement has following three sections:

1. The CASE keyword to start.
2. The END keyword to end.
3. The number of sections or clauses in between CASE and END.

Let’s see what does “WHEN”, ‘THEN’ and ‘ELSE’  stands for:

• WHEN: It is used for a logical condition that needs to be evaluated. There can be multiple WHEN clauses in a single CASE statement. 
• THEN: It returns the results of the WHEN statement if the clause is true. There must be one THEN clause for each WHEN clause.
• ELSE: It is optional and comes to play when no clause condition is satisfied. The CASE returns the value of the ELSE clause, or it returns NULL if no ELSE clause is specified. 

Let’s discuss conditions on the “WHEN” and “THEN” clause and the limitations of these conditions in detail.

• WHEN Conditions
• Limitations of WHEN Conditions
• THEN Conditions
• Limitations of THEN Conditions

WHEN Conditions

Data Studio CASE WHEN conditions, as mentioned above, evaluate the data and return true if a specific condition is met, or it returns false. A valid WHEN condition can do the following things:

• Compare a dimension or metric to a literal value, using a supported operator.
• Perform a regular expression match on a dimension or metric.
• Determine whether a dimension or metric is null.
• Determine whether a dimension or metric contains a value specified in a list.
• Determine whether a dimension or metric does not contain a value specified in a list.

Limitations of WHEN Conditions

• While using the CASE WHEN Looker Studio clause, one cannot compare dimensions to dimensions or metrics to metrics.

CASE WHEN Country = "United States" ... // valid formula
CASE WHEN Amount > 100 ... // valid formula

CASE WHEN Dimension1 = Dimension2 ... //invalid formula
CASE WHEN Metric1 > Metric2 ... // invalid formula

• WHEN conditions cannot mix dimensions and metrics. 

CASE WHEN Country = "England" AND Conversions > 100 THEN ... // invalid formula

• WHEN conditions must contain either a dimension or a metric. The WHEN conditions can’t have a literal-only condition.

CASE WHEN 1 THEN ... // invalid formula

• You can’t perform calculations using WHEN conditions. It is not possible to do math or perform any calculations using WHEN conditions.

CASE WHEN metric1 + metric2 > 4 THEN ...// invalid formula

THEN Conditions

THEN clause can return the following values:

• Dimensions
• Metrics
• Calculated fields
• Literal values
• NULL

Limitations of THEN Condition

• It is not possible for THEN clauses to return a formula. 

CASE WHEN Country = "United States" THEN value1 + value2 END // invalid formula

• All the possible results in THEN clause must be of the same type. 

CASE Statement Google Data Studio: Use Case

There are various use cases of CASE statements. As you have a basic understanding of this function now, it will be easier for you to understand its practical applications. 

The following code is a script for Google Campaign Manager. The REGEXP function is used for regex expressions. Google Data Studio uses RE2-style syntax. 

CASE
WHEN REGEXP_MATCH(Campaign, '.*(EN).*') THEN 'English'
WHEN REGEXP_MATCH(Campaign, '.*(SP).*') THEN 'Spanish'
ELSE 'Other'
END

Whenever the letters EN and SP are spotted in the campaign, the text strings are replaced by “English” and “Spanish”. 

Similarly, you can point out the devices used in our campaign in Google Ads Manager. 

CASE
WHEN REGEXP_MATCH(Campaign, '.*((M|m)obile).*') THEN 'Mobile'
WHEN REGEXP_MATCH(Campaign, '.*((T|t)ablet).*') THEN 'Tablet'
WHEN REGEXP_MATCH(Campaign, '.*((C|c)omputer).*') THEN 'Desktop'
ELSE 'Other'
END

Conclusion

The CASE statement in Google Data Studio is one of the most useful and powerful statement. It can perform complex data analysis. Google Data Studio provides various other functions, but the CASE statement is the most widely used function.

Hevo is a No-code Data Pipeline. Hevo supports pre-built data integrations from 100+ data sources. You can integrate your data with the help of Hevo and then visualize it in Google Data Studio.

Give Hevo a try by signing up for a 14-day free trial today.

Share your experience of using Case statement in Google Data Studio in the comment section below.