6.1. Grouping Data with IF() in Looker Studio
In this chapter we will discuss how we can use the CASE() and IF() conditional functions to group and categorize data so we can attain how we want to present and visualize our data in our reports.
We will discuss the most common and useful applications of the CASE() function especially when working with web tracking data.
In this first lesson we will tackle how we can nest the IF() function to text functions to categorize metrics or dimensions into two categories.
Grouping Data with IF()
In our data modeling journey, we've covered data sources, aggregation methods, mathematical formulas, custom field scopes, text processing, and regular expressions. Now, it's time to dive into grouping and categorizing data using conditional functions.
In this lesson, we'll explore how to group data similarly to how Google Analytics groups channels, breaking them down into categories we understand based on our business logic. We'll be using the IF() function to achieve this.
Tables and Calculated Fields
Remember that everything in Looker Studio is a table. We're always working with tables, and when we're happy with the results, we connect them to charts. While grouping and categorizing data, we'll be creating custom fields or groups.
There are two main conditional functions in Looker Studio to help us group and categorize data: the IF() function and the CASE function. We'll use the IF() function for simple use cases with only two options or groups, while the CASE function will be used for more complex scenarios.
The IF() Function
Let's say we previously used the REPLACE function to replace the top-level domain in a hostname and extract the subdomain. Instead of just returning the value of the subdomain, such as "shop" or "www," we want to report the actual value in the reporting interface.
For example, if the website uses "s" for "shop" or "a," "b," "c" for departments, we want to report the actual names. In this case, we can use the IF() function to return the desired result based on a condition.
The IF() function takes three parameters:
- Condition: Any expression that returns true or false
- Result if true
- Result if false
Here's an example of how to use the IF() function:
IF(LEFT(x,4) = "shop", "Shop Section", "Main Website")In this example, we check if the first five characters of the hostname are "shop." If they are, we return "Shop Section"; otherwise, we return "Main Website."
The IF() function is most effective when we only have two categories and want to group all our dimension categories into just two different categories.
Note: It's important to note that the IF() function in Looker Studio is case-sensitive.
📩 Receive my weekly Looker Studio tips
🎥 Subscribe to my YouTube channel
🖇 Connect with me on LinkedIn