How to use CASE statements in Looker Studio for smarter data segmentation

by

When building insightful reports in Looker Studio, one of the most powerful tools at your disposal is the humble CASE statement. Whether you’re categorising users, grouping campaign sources, or splitting branded and non-branded search traffic, CASE statements let you add logic-driven fields that bring clarity and nuance to your data.

Let’s walk through what they are, how they work, and a few ways you might use them to enrich your reports.

What Is a CASE Statement?

Think of a CASE statement as Looker Studio’s version of the IF-THEN-ELSE logic. It evaluates a list of conditions in order and returns a result based on the first one that’s true. You can use them to create new calculated fields directly in your data source (no code or connectors required).

Here’s the structure:

CASE  
  WHEN condition1 THEN result1  
  WHEN condition2 THEN result2  
  ...  
  ELSE resultN  
END

Each WHEN checks a condition. If it’s met, the corresponding THEN result is returned. If no conditions are met, the ELSE provides a fallback (which is optional but highly recommended).

Practical ways to use CASE statements

Here are a few real-world examples I often use in Looker Studio reports.

1. Categorising customers by purchase value

Need to segment customers by how much they’ve spent? This CASE statement creates a simple tier system:

CASE  
  WHEN Total_Purchase < 100 THEN "Low Spender"  
  WHEN Total_Purchase BETWEEN 100 AND 500 THEN "Medium Spender"  
  WHEN Total_Purchase > 500 THEN "High Spender"  
  ELSE "Unclassified"  
END

It’s a great way to tailor your marketing or identify opportunities to upsell.

2. Grouping traffic sources

To simplify your source/medium data, you can bundle similar traffic channels together:

CASE  
  WHEN Campaign_Source IN ("Google Ads", "Bing Ads") THEN "Paid Search"  
  WHEN Campaign_Source IN ("Facebook", "Twitter") THEN "Social Media"  
  ELSE "Other"  
END

This helps you understand your top-level marketing mix without getting lost in the weeds.

3. Identifying recent sign-ups

Want to highlight users who joined recently?

CASE  
  WHEN DATE_DIFF(CURRENT_DATE(), SignUp_Date) <= 30 THEN "New User"  
  ELSE "Existing User"  
END

This sort of logic is perfect for tracking onboarding engagement or cohort behaviour.

4. Distinguishing branded vs. non-branded queries

This one’s especially helpful if you’re working with Google Search Console data. By using the CONTAINS_TEXT function, you can split queries into branded and non-branded buckets:

CASE  
  WHEN CONTAINS_TEXT(Query, "Two Octobers") THEN "Branded"  
  WHEN CONTAINS_TEXT(Query, "TwoOctobers") THEN "Branded"  
  ELSE "Non-Branded"  
END

You can adapt this to your own brand name, accounting for spaces, capitalisation, or common misspellings. Once set up, this new field lets you track SEO performance with much more precision.

How to add a CASE statement in Looker Studio

  1. Open your report and go to Resource → Manage added data sources.
  2. Click Edit under your data source’s actions
  3. Click Add a field → Add calculated field to create a new calculated field.
  4. Paste your CASE logic into the formula editor.
  5. Name the field, something clear and useful like “Customer Segment” or “Query Type”.
  6. Save it, and the new dimension will be available to use in any chart or table.

You will also see an Add group button in your data source, which offers a similar categorisation tool, but I find CASE statements give more control and flexibility.

Best practices

  • Keep data types consistent: All your THEN and ELSE results should return the same type (text, numbers, or dates) otherwise the formula won’t validate.
  • Put specific conditions first: Since CASE returns the first true match, make sure the most specific conditions are at the top.
  • Always test: Use a sample table or chart to make sure your logic holds up. This helps you catch anything that might fall through the cracks into the ELSE.

Final thoughts

Once you get comfortable with CASE statements in Looker Studio, you’ll wonder how you managed without them. They’re simple to write but incredibly versatile, helping you segment, group, and customise your data exactly how you need it.

Next time you’re looking at a report and thinking “I wish I could group this differently”, consider whether a CASE statement might be the answer.

Leave a Comment