6.11. CASE Function: Aggregating Results

In this lesson we will see how we can aggregate the output of a CASE function by wrapping it on another function in Looker Studio in order to create a segment of an entity based on the criteria we set.

You can aggregate results of a CASE function in Looker Studio using another aggregation function, such as SUM, AVERAGE, COUNT, etc. This allows you to perform complex operations on a segment of data without using a filter.

Example

Imagine you want to find the total number of page views for search URLs and the number of search URLs. You can use a CASE function to achieve this:

CASE
  WHEN CONTAINS_TEXT(page_url, 'q=') THEN 1
  ELSE 0
END

This function returns 1 for search URLs and 0 for non-search URLs by using CONTAIN_TEXT() function in Looker Studio. You can then wrap this CASE function with the SUM function to aggregate the results:

SUM(
  CASE
    WHEN CONTAINS_TEXT(page_url, 'q=') THEN 1
    ELSE 0
  END
)

Using the SUM function in this way calculates the total number of search URLs. The same CASE function can be used to aggregate other metrics as well, such as page views, revenue, or goals.

Discussion

It is important to understand that the COUNT function wouldn't work in this example because it would count all values returned by the CASE function, including the zeros. Instead, the SUM function is used to aggregate the results, as zeros do not contribute to the total sum.

You can also achieve the same result by applying a filter to count distinct values of page URLs that match the search criteria. However, using a CASE function allows you to include the non-search URLs in your calculations for other purposes.

Enroll in Looker Studio Masterclass →

📩 Receive my weekly Looker Studio tips

🖇 Connect with me on LinkedIn