6.6. Cleaning Data with CASE Function

The CASE function can be utilized to clean inconsistent or messy data. In this example, we'll explore how to use the CASE function to correct inconsistencies in source medium data. We'll merge some sources, lower case their values, and replace certain values with others.

Example

Suppose you have source medium data with the following issues:

• "direct" and "none" are represented as "not set."
• The values have mixed casing, and you want everything to be lowercase.
• There are multiple instances of "Google" in different formats, and you want to report them all as simply "Google."

Here's how you can use the CASE function to clean this data:

CASE
  WHEN (source = 'not set' AND medium = 'not set') THEN 'direct none'
  WHEN (source CONTAINS 'Google') THEN CONCAT('google ', medium)
  ELSE LOWER(source, medium)
END

This function performs the following actions:

1. When the source is "not set" and the medium is "not set," it returns "direct none."
2. When the source contains the word "Google," it concatenates "google" with the value of the medium.
3. Otherwise, it lowercases the source and medium values.

By using the CASE function, you can clean up your data, merge sources, and modify values as needed to create a cleaner and more useful dataset for your viewers or clients.

In this chapter:

→ 6.7. Order of WHEN Clauses in CASE Function

→ 6.8. Returning Expressions in CASE Functions

→ 6.9. Using CASE to Extract URL Query Parameters

→ 6.10. Mixing Dimension & Metrics in CASE

→ 6.11. CASE Function: Aggregating Results

→ 6.12. Misc Functions: IMAGE() & HYPERLINK()

Enroll in the updated & extended Looker Studio Masterclass 2024 →

📩 Receive my weekly Looker Studio tips

🎥  Subscribe to my YouTube channel

🖇 Connect with me on LinkedIn