Ondigital
About usCase StudiesLoginSign upTalk to us
Article

Looker Studio CASE WHEN Statement Guide

March 19, 2025

· Timmy Bontegard

Analys
Analysis
Articles
Blog
Blogg
Ecommerce
E-handel
Inlägg
Rapportering
Reporting

CASE statements let you reorganize data inside Looker Studio without editing the underlying source. You can group product types, standardize country names, or map several advertising platforms into one reporting channel.

For ecommerce teams, this is useful when source systems use different naming conventions or when a dashboard needs categories that match how finance, marketing, or purchasing reviews performance.

Looker Studio report showing a CASE statement example

What is a CASE statement?

A CASE statement checks one or more conditions and returns a value when a condition is met. In practice, it works like a series of if/then rules.

For example, you could tell Looker Studio:

  • If the product category is Shoes, return Footwear.
  • If the product category is Boots, return Footwear.
  • For every other category, return Other Category.

The calculated field does not rewrite your source data. It creates a new field that can be used in tables, charts, filters, and controls.

Calculated fields marked as dimensions in Looker Studio

The green fields in the screenshot indicate dimensions rather than pre-aggregated metrics. Dimension-based CASE fields are usually the simplest option because Looker Studio can classify each value before calculating totals.

CASE syntax in Looker Studio

A basic CASE statement follows this structure:

CASE
WHEN Product_Category = "Shoes" THEN "Footwear"
WHEN Product_Category = "Boots" THEN "Footwear"
ELSE "Other Category"
END

Looker Studio evaluates the conditions from top to bottom. It returns the result for the first matching condition, then stops checking that row.

The ELSE clause handles values that do not match any condition. Without it, unmatched records may return NULL, which can produce blank categories in a chart.

Order specific rules first

If two conditions could match the same value, place the narrower condition above the broader one. CASE returns the first match, so rule order can change the result.

Dimensions, metrics, and aggregation

CASE statements are commonly used with dimensions such as product name, country, campaign, or source. These fields describe each row and make natural inputs for classification.

Metrics require more care. Looker Studio distinguishes between:

  • Unaggregated fields, such as row-level revenue or quantity
  • Aggregated fields, such as SUM(Revenue) or COUNT(Order_ID)

A calculated field cannot freely mix aggregated and unaggregated expressions. For example, comparing SUM(Revenue) in one condition while returning a row-level field elsewhere can trigger an aggregation error.

The practical rule is to keep the expressions at the same aggregation level. If the goal is to regroup categories and then compare revenue, build the CASE field from the category dimension. Looker Studio can aggregate revenue under the resulting labels.

Reporting task
CASE input
Result
Combine shoes and boots
Product category
A new Footwear dimension
Standardize country names
Shipping country
A consistent country dimension
Group paid social platforms
Source or channel
A custom marketing channel
Classify aggregated revenue totals
Aggregated metric expression
Possible, but all expressions must use compatible aggregation

How CASE affects ecommerce data

Picture a source table with these fields:

  • Order ID
  • Product category
  • Transaction channel
  • Revenue

A CASE field labels each row according to its product category. If both Shoes and Boots return Footwear, a chart using the new field will place those records in the same group.

Looker Studio then calculates the selected metric for that group. Revenue from shoe and boot orders is summed under Footwear, assuming the chart uses SUM(Revenue).

Original category
CASE result
Revenue
Shoes
Footwear
120
Boots
Footwear
180
Accessories
Other Category
40

In a chart grouped by the CASE field, the first two rows contribute to a Footwear total of 300. The CASE statement changes the reporting label, while the chart controls how revenue is aggregated.

Looker Studio chart using regrouped ecommerce data

Common ecommerce uses

Group product categories

Several detailed categories can be rolled into a reporting category used by finance or purchasing. For example, Shoes, Boots, and Sandals could become Footwear.

Keep the original category available. Teams often need both the grouped view and the underlying detail when investigating a change in sales or margin.

Create custom channel groupings

Source data may use separate values for Facebook Ads and Instagram Ads. A CASE field can group both under Paid Social, while Google Ads remains Paid Search.

This is especially useful when the dashboard’s channel definitions differ from the default groupings supplied by an advertising or analytics platform.

Clean up inconsistent values

CASE can standardize known variations, such as mapping both USA and United States to US.

This works well for a manageable list of reporting fixes. If the mapping contains hundreds of values or changes frequently, clean the data upstream or maintain a separate mapping table instead. A long CASE statement becomes difficult to test and update.

Combine low-volume values

Products or channels with little activity can be grouped into Other to make charts easier to read. Check the detailed data before doing this. A small category may still matter if it has unusually high margin, return rates, or advertising costs.

Create a CASE field in Looker Studio

  1. Open the Looker Studio report and select Edit.
  2. In the data panel, choose Add a field.
  3. Give the calculated field a clear name, such as Product Group.
  4. Enter the CASE statement in the formula box.
  5. Check that the formula is valid.
  6. Select Apply, then save the field.
  7. Add the new field to a table or chart as a dimension.
  8. Compare the result with the original field before using it across the report.

Test unmatched values as well as the categories you expect to match. New products, renamed campaigns, and blank source values can otherwise fall into Other without anyone noticing.

Check connector field types

Field behavior can vary by connector and data source. If a formula produces an aggregation error, inspect whether each referenced field is a dimension, an unaggregated metric, or an aggregated expression.

Keep CASE fields maintainable

Use descriptive field names and format longer statements with one condition per line. Add an explicit ELSE result so dashboard users can see which records were not classified.

For business-critical mappings, document the owner and the source of the rules. A channel grouping maintained by marketing may differ from the grouping finance uses for spend reconciliation.

CASE is a good fit for report-level classification. When the same logic is repeated across many reports, move it into the connector, warehouse, or another shared data layer. That reduces conflicting definitions and gives the team one place to update the mapping.

Need help cleaning up a Looker Studio report?

Talk to us about calculated fields, ecommerce data connectors, and dashboard structure.

Talk to us
← All posts