How to use CASE statements in Google Data Studio

A regular expression REGEXP_MATCH formula using a case statement

In this post, we are going to explore one of the most useful functions in Google Data Studio, the CASE. I personally think that knowing how to write a CASE is not only helpful in Data Studio, but also it allows you to understand how computer logic works.

What is the Data Studio CASE function?

The CASE function returns dimensions and metrics based on conditional expressions; it is often used to create new groupings of data and sort them in categories. Let’s imagine the following scenario. In the table below, you have a paid digital marketing campaign that includes “EN” and “FR” in the campaign name. However, you want to see the table below only per “Language”. This is where you can use the CASE function to write a specific statement.

google data studio campaign data table with a column highlighted

The trick with the CASE function is that you often have to find a unique common denominator to parse the data correctly. In the statement below, I am opening the CASE with a WHEN, specifying the first condition. Then adding IN, which instructs Data Studio that I will be listing text strings. Finally, I go ahead and choose my campaign name(s) and specify with THEN what I want to name the result of my WHEN condition. I know…. this is a bit of a tongue twister.

google data studio case statement using in

The result is exactly what we were looking for. Now if we want to see the campaign simply filtered by the “Language” dimension, we can even exclude “Campaign”.

table with language column highlighted

How to use REGEXP_MATCH function within CASE?

Now, what happens if things get more complicated. For example, in the “Complex Campaign” dimension, we have a lot of different and unique campaign iterations. If we were to use IN, then the list in the CASE function is going to grow substantially and we are more likely to make a mistake while writing it. This is why we are going to try out the REGEXP_MATCH function, also referred to as regex.

table with campaign data

Quick background: Regular expressions are a notation for describing sets of character in a specific word or any text string. Google Data Studio (and most Google products) uses the RE2-style syntax.

We start the WHEN statement the same way, without much difference. However, the modified dimension i.e. “Campaign” is inside brackets, unlike IN where it is outside. All we are doing with the regular expression is telling Data Studio that every time the capital letters “EN” or “FR” are spotted in the “Complex Campaign” dimension, the text strings should be named “English” and “French”, respectively. In a little bit more detail:

google data studio case statement using regex match

Data Studio CASE examples from the Media Agency world:

These are few of the most common CASE statements across the Google Marketing Platform (GMP), ready to be copy/pasted!

Google Campaign Manager LogoGoogle Campaign Manager (CM) – Language

CASE
WHEN REGEXP_MATCH(Campaign, '.*(EN).*') THEN 'English'
WHEN REGEXP_MATCH(Campaign, '.*(FR).*') THEN 'French'
ELSE 'Other Language'
END

display and video 360Display and Video 360 – Language

CASE
WHEN REGEXP_MATCH(Insertion Order, '.*(EN).*') THEN 'English'
WHEN REGEXP_MATCH(Insertion Order, '.*(FR).*') THEN 'French'
ELSE 'Other Language'
END

display and video 360Display and Video 360 – Standard Media Tactics

CASE
WHEN REGEXP_MATCH(Line Item, '.*(Remarketing).*') THEN 'Remarketing'
WHEN REGEXP_MATCH(Line Item, '.*(Lookalikes).*') THEN 'Lookalikes'
WHEN REGEXP_MATCH(Line Item, '.*(Prospecting).*') THEN 'Prospecting'
ELSE 'Other Tactic'
END
CASE
WHEN REGEXP_MATCH(Campaign, '.*((M|m)obile).*') THEN 'Mobile'
WHEN REGEXP_MATCH(Campaign, '.*((T|t)ablet).*') THEN 'Tablet'
WHEN REGEXP_MATCH(Campaign, '.*((C|c)omputer).*') THEN 'Desktop'
ELSE 'Other'
END

CASE
WHEN Age IN ('Undetermined') THEN 'Unknown'
WHEN Age IN ('18to24') THEN 'A18-24'
WHEN Age IN ('25to34') THEN 'A25-34'
WHEN Age IN ('35to44') THEN 'A35-44'
WHEN Age IN ('45to54') THEN 'A45-54'
WHEN Age IN ('55to64') THEN 'A55-64'
WHEN Age IN ('gt64') THEN 'A65+'
ELSE 'N/A'
END

I hope this was helpful! If you have any questions, feel free to DM me on LinkedIn or subscribe to my newsletter for more updates 🙂

14 comments
  1. Hey there,

    Super useful article!

    I’m using this to segment items within a table: CASE
    WHEN REGEXP_MATCH(Campaign, ‘.*(Brand).*’) THEN ‘Brand’
    WHEN REGEXP_MATCH(Campaign, ‘.*(Generic).*’) THEN ‘Generic’
    WHEN REGEXP_MATCH(Campaign, ‘.*(Competitor).*’) THEN ‘Competitor’
    ELSE ‘Other’
    END

    However, I can only see this segmentation when I keep a Dimension for “Campaign” active. As soon as I remove Campaign, the whole thing becomes broken.

    You mentioned above “Now if we want to see the campaign simply filtered by the “Language” dimension, we can even exclude “Campaign”.” … how can I exclude?

    Thank you!!!

    1. Hey Valentina,

      Thanks for reaching out and sorry for the delay. You should be able to remove the source dimension and replace it with the newly created CASE one. Normally, you just have to remove it from the dimensions menu on the side. Also, did you create the CASE at a report-level or data source-level (Resources -> Manage added data sources -> …)?

      Regards,
      Lachezar

  2. Hey there

    How do I use the case function when trying to get data between two dates.
    for example. When the country is Kenya and the dates are between 1st March and 30th April give me 26 and when the country is Kenya and the dates are between 1 May and 30th June give me 70.

    1. Hey Reginah,

      Thanks for reaching out! Here is what you can do to activate your logic within a CASE statement. I have used the “[Sample] Google Analytics Data” in Data Studio to re-create the example:

      CASE
      WHEN Country ISO Code = “US” AND Date >= “20200301” AND Date <= "20200430" THEN "26" WHEN Country ISO Code = "US" AND Date >= “20200101” AND Date <= "20200229" THEN "70" ELSE "Other" END
      I have put together a dashboard too – https://datastudio.google.com/reporting/6058186c-5d15-42d7-8306-51ebc5063f79

      Let me know if this works for you 🙂

      Thanks,
      Lachezar

      1. Hey Lachezar,

        I have tried using this example but I keep getting this error “Failed to create field. Please try again later.”. I am using a Heroku database for my report.

        Regards,
        Reginah

        1. Hey Reginah,

          This is interesting. I would recommend you to take a look at how your “Date” is formatted, as that might be the reason why you are getting the error. If possible, my recommendation would be to do the transformations in Heroku and then push the final table in Data Studio to avoid the additional rendering and limitations.

          Thanks,
          Lacho

        1. Hey Pooya,

          Are you referring to the same issue Reginah is experiencing or another section in the article?

          Thanks,
          Lacho

  3. Hi Lachezar,
    Can Data studio allow a table containing columns of text data type to filter a pivot table based on the number of unique or non-unique rows in 1 field. For example.
    Region Country Campaign Status
    Europe. England. ABC. Delayed
    Europe. France ABC. Delayed
    Europe. Germany ABC. Delayed
    In Data studio, we want to show only 1 row if the status is the same of all countries and collapse the country row. Essentially its a form of de-duplication when the status is the same.

    1. Hey Marshal, apologies for the delay in my reply. This is a great question! You can achieve the breakdown that you described by using the “Exapnd-Collapse” in a pivot table. I have sent you an e-mail with a sample Data Studio dashboard. Let me know if that is what you were looking for.

  4. Per your answer to Reginah Kanyi Date format. Does the regex syntax ever change? I have previously written CASE statements that worked when I wrote it, but now am unable to edit them. When I click on the calculated field to edit, it shows a red X, where when I saved it it was ok showing a green checkmark.

    CASE 
    WHEN ( Date >= ‘20170401’ AND Date = ‘20180401’ AND Date = ‘20190401’ AND Date <= '20200331') THEN '2019 to 2020'
    END

Leave a Reply

Your email address will not be published. Required fields are marked *