How to Use the Data Studio Case Statement

IFTTT—If This, Then That. If you are using the Data Studio, then you should learn how to use the Case statement.

Now, functions are a very important part within Google Data Studio to create calculated fields. And the Case statement is probably the most advanced but most powerful function you can learn in Data Studio. It works just like If This, Then That!

In today’s lesson, I’ll show you how we can use the Case statement in building our dashboards and create calculated fields.

An overview of what we’ll cover:

What are Functions in Data Studio?

To understand functions, it helps to know about calculated fields. Calculated fields allow you to set up reports using the metrics and dimensions in your data. It also enables you to tweak the data based on the customization that your report will need to show using different code techniques. 

Functions are among the best features of calculated fields. Functions use branching logic to evaluate data, manipulate geographic information, text, and dates, and perform relevant arithmetic.

There are many useful functions in Data Studio that you should get to know. However, for this tutorial, we’ll focus specifically on the Case function. (To learn more about calculated fields and some other functions, check out our Google Data Studio Calculated Fields Guide.)

The Case function is probably the most advanced and most powerful function you can learn. This function allows for the creation of new categories as well as groupings of data. It uses conditional logic in order to identify the calculated field’s value.

This  tutorial will explain the case statement and demonstrate how to use it in our Data Studio dashboards. 

The Case Statement Explained

Today, I’m going to show you how to use Case When, a statement in Google Data Studio to create calculated fields. The Case When statement is one of the most powerful formulas available in Google Data Studio which you can use in many situations. 

The Case When function is an if this, then that type of formula which can be used for more complex conditional expressions. It can help greatly with cleaning up groupings in marketing programs like Google Analytics by segregating values and data with the use of proper conditions.

Use Case: Showing Traffic & Conversions by Weekdays vs. Weekend

But for the purpose of this tutorial, we’re going to use it to build a table that shows traffic and conversions from Google Analytics by Weekday versus Weekend. And you know, that’s not a dimension that is usually available in Google Analytics.

NOTE: This is a rather advanced feature of Google Data Studio. Therefore, you should already know the basics, like how to build reports and visualize data using charts. 

Open the report you’d like to use. For this tutorial, we will be using the Google Analytics demo account Google Merchandise Store as a data source. If you’re totally new to Case functions, I’d recommend that you use the same data source so that you can follow along with our steps exactly while you get familiar with the technique.

Our aim today is to create a table and categorize days of the week into two groups: Weekdays and the Weekend. We want to be able to visualize and analyze sessions, conversion rates, etc, per Weekday versus Weekend. 

For this, we first need to table. So let’s add one by clicking on the Add a chart button and clicking on the Table option.

The Add a chart and Table buttons being clicked on Google Data Studio

After that, the chart will be added to the report. 

Next, let’s add our metrics. In this case, we’ll input Sessions into the Available Fields search box and drag our desired field into the Metric section.

The chart being added to the report, the Sessions metric being input in the Available Fields section, and the Sessions field being dragged under the Metric section

We’ll also add the E-commerce Conversion Rate metric and drag it under the Metric section as well.

The E-commerce Conversion Rate metric being input in the Available Fields section, and the E-commerce Conversion Rate field being dragged under the Metric section

And we also want to add the Average Order Value. We’ll search it under the Available Fields section and once it’s reflected in the results, we’ll drag it under the Metric section.

The Average Order Value metric being input in the Available Fields section, and the Average Order Value field being dragged under the Metric section

This chart seems a bit squished, doesn’t it? Remember that you can always make the chart a bit bigger to see all the data by dragging the edges on either side.

The chart being dragged on opposite ends to make the data clearer

🚨 Note: If you double click on one of the chart’s columns, it will resize all the columns automatically to show you all the data.

The columns on the chart being double clicked in order to resize them

For the dimension, we don’t want Medium. We want something closer to be able to see the name of the day of the week. This is something that is available in Google Analytics by default. 

To do this, we will input the Day of Week Name on the Available Fields section and select the correct dimension accordingly.

The Day of Week Name being input and selected in the Available Fields section

We just want to use the Day of Week Name as a starting point, so we’ll drag it under the Dimensions section.

The Day of Week Name dimension being dragged under the Dimensions section

After setting the correct dimension, we now have our weekdays at the Day of Week Name, Sessions, Page views, E-commerce Conversion Rate, and Average Order Value.

The chart displaying data for Day of Week Name, Sessions, Page views, E-commerce Conversion Rate, and Average Order Value.

Now, this is quite close to what we want, but not exactly. Instead of seeing the name of each day separately on its own row, we’d like to have only two rows, the first being Weekday and the second Weekend. However, this is not a dimension that is already available in Google Analytics.

So to do this, we need to create a calculated field. Calculated fields are fields whose values are evaluated based on some calculations on other fields values. 

To create one, we need to go to the Resources menu and click on Manage added data sources to see our data source.

The Resource button and the Manage added data sources button below it being clicked

Then, click on Edit.

The EDIT button being clicked on the Data Source options

When we edit our Data Source, we can add new fields. To do this, we’ll click on the Add a Field button.

We will then arrive at this interface which allows us to give our new field a name. I’m going to name it Day Type

And then in the formula section, I’m going to use and introduce you to Case When Statement.

Before I start typing in the formula, let’s recall what we want to achieve in the table above. 

First, we want to write Weekday if it’s a Monday, Tuesday, Wednesday, Thursday, or Friday.

The Field Name and Formula sections being filled in with a name and codes respectively

But if it’s a Saturday or a Sunday, they should be classified as a Weekend. 

Google Data Studio will validate the formula and show a green checkmark once the formula is seen as valid.

Codes for Saturday and Sunday being added and a green checkmark appearing to signify that the code is valid

The Syntax of a Case When Statement

Let’s go over the syntax so that we can write this logic with a Case When formula.

Let’s type in CASE WHEN. I use uppercase here, but you can write in lowercase as well.

After typing WHEN, it’s going to say it’s invalid, but it’s okay. I’m just showing you it doesn’t allow me to input sample texts here. This will be fixed when we finish our function.

On the second line of code, we’ll type in a code that says when a certain condition is met, it will return a result. The formula for this is WHEN Condition = THEN Result. (Note that this is just a template or formula for how you can implement a variety of Case When functions—you will need to use real conditions from your data and describe the result that you want to return in your use case.)

And when another condition is true, we want to return another result. Remember that we always end our case with the word END. So, the formula for this next part is WHEN Condition 2 = THEN Result 2 END .

The Case When Statement Syntax being types into the code window

Writing the Use Case Statement

So how do we apply this to our use case of weekdays and weekends?

Now in this case, we do not need anything else because we only have seven values for each day of the week. And we already know the return string that we want for each condition. So, let’s start writing.

We’ll write WHEN Day of Week Name = Monday THEN “Weekday”. This tells Data Studio that when the day of week name is Monday, then it is a Weekday.

We’ll do the same for days Tuesday through Friday.

And now, we can Save it.

The SAVE button being clicked on the Data Sources window

Then, we’ll click on Finished.

The FINISHED button being clicked on the Data Sources window

Let’s add our newly-created dimension which is Day Type.

The Date Type dimension being clicked on and dragged under the Dimensions section

For each day of the week that is a weekday, the Day Type is now Weekday. And for Sunday and Saturday, it is Weekend

The Day of Week and Day Type reflecting correct coding results.

But this still isn’t quite what we wanted. We’d actually like two rows, one for Weekdays and one for Weekends. 

So, we’ll get rid of their Day of Week Name as a field in the Dimension list.

The Day of Week Name dimension being removed from the Dimension list

That’s better. On our table, we can now see our Sessions, Page Views, E-commerce Conversion Rate, and Average Order Value per Weekday versus Weekend.

The chart reflecting the changes made to the Day of Week Name dimension

We can already see some interesting data in this table.

If I were the owner of this shop, I would rather advertise and promote my products on Weekdays because the Average Order Value is five times bigger than the Average Order Value on Weekends. 

The Day Type, Sessions, Page View, E-commerce Conversion Rate, and Average Order Value details of the Weekday being highlighted

The Case When Formula

While I’d like to keep this tutorial short and sweet, I want to return briefly to the Case When formula to show you some interesting things about it. 

Again, let’s go to Resource and then click on Manage added data sources

The Resources and the Manage added data resources buttons being clicked on

Then, we’ll click on the Edit button on the view of our data source.

The EDIT button  being clicked on the Master View Data Source section

We’ll type in day to find the specific field we need.

The day field being input into a search box

Once we’ve found our field, we’ll click on fx (function).

The fx (function) button being clicked on inside the Master View Data Source

Then, displayed in the code window is our Formula.

The Formula in the code window being highlighted

You can troubleshoot and edit any of your formulas for any of your metrics using this tool. 

Note that if you want to do calculations with metrics themselves, this requires a slightly different approach. To learn more, check out our guide to Calculating a Conversion Rate between Two Metrics.

Other Case Functions

Logical Operators

There are some other useful functions that I want to describe briefly. The first is logical operators, which you can use in the conditions section of the Case When statement. 

Instead of using the equal sign, we can use an exclamation mark to say “does not equal to.”

An exclamation mark being added in between the Field Name and the equal sign on the code window

Another thing that we can do is to raise another condition using OR. For example, here we’ll include the field name, which is the Day of Week Name = Tuesday THEN Weekday .

Although we’ve placed Monday and Tuesday in a single line, we can’t bring all of these days into one line. However, there is still a better solution to remedy this.

The code, “Monday” OR Day of Week Name = “Tuesday” THEN “Weekday” being input in the code window

To do that, we’ll remove lines three to line six because I want to bring all the Weekdays into one line.

Lines three to six being deleted from the code window

IN Case Statement

For this, I’m going to use the IN Case Statement. The IN Case Statement can group several different conditions inside parentheses. To do that, we’ll input the following into the code window:

IN( ”Monday” ,“Tuesday”,“Wednesday” →  ,“Thursday”,“Friday”)THEN“Weekday”.

The result is the same but we achieved the logic that we wanted. 

A new in case statement code being added into the code window

Regular Expressions

To combine Saturday and Sunday, we’re going to use a regular expression.

If you do not know what a regular expression is, then you can use the IN Case Statement like we did for the other weekdays.

We should first delete the fourth line of code for Sunday because we’ll be combining  it with Saturday.

The 4th line of code for Sunday being deleted

We can use a regular expression, in a Case When statement which is powerful. It brings the flexibility of this formula to a whole next level. 

It’s easier to write the code in seven lines, one for each day. However, I would like to encourage you to use RegEx as it’s so much more powerful than the IN Case Statement. It’s also useful in a wide variety of situations, so it keeps you flexible.

To implement the regular expression, we’ll type in REGEXP_MATCH ( Day of Week Name“Saturday | Sunday” )THEN“Weekend”

The pipe in between Saturday and Sunday signifies “or.” So the code reads as when the day of the week is either Saturday or Sunday. 

A new regular expression code being input into the code window

Now that the formula is valid, let’s Update it.

The UPDATE button being clicked on

Then, we’ll click on the Finished button to save it.

The FINISHED button being clicked on

And now we have the same table which has two lines of code for both the Weekday and Weekend Day Types.

The Weekday and Weekend Day Types being highlighted on the Google Data Studio chart

Case Else Statement

We have one more option, which is Case Else. Which means if none of these conditions are met, then return another result.

Sometimes you want to use Else as a catchall to just include anything that’s not one or two specific conditions that you’ve already included in a function. You can also use it to make sure that data with any errors still gets counted.

For example, if the Day of Week Name field for a data point was populated with something other than a day of the week (i.e. “basketball” or “February”), then your function will give a separate return for these data points.

To use this, we’ll write ELSE“Result 3” .

The CASE ELSE statement being written in the code window

Summary

Using the Case When statement in Google Data Studio is very valuable especially for creating accurate calculated fields. Through this tutorial, you were able to learn about the importance of the Case function and its applications along with the logical operators, IN Case statements, and regular expressions which are the conditions you can use to optimize your use of the Case When statement.

If you’re curious to about other functions you can do in Data Studio besides the Case statement, check out our 2020 updated guide to Data Studio functions, or get to know more about different visualizations in our guide to Data Studio charts.

Or if you’re interested to explore all the other features of Google Data Studio, you can learn more about it when you join our Google Data Studio for Beginners Free Course.

Do you have any other use cases for Case Functions? Let us know down below in the comments!

JOIN US!

Master Data & Analytics with Measuremasters

Exclusive Courses & Workshops | Ongoing Troubleshooting | Support Resources, Tools & much more
guest
0 Comments
Inline Feedbacks
View all comments

Blog Categories

Join 30,000+ marketers getting exclusive resources and our latest content!

now it's time to

Start measuring like a master

Itching to jump into the world of MeasureMasters? This is what you have to look forward to.