Google Data Studio Case Function

Introduction

Functions are a very important part of Google Data Studio to create calculated fields. Among them, the case function is probably the most advanced and useful function you can learn.

In this tutorial, I am going to show you how to use the CASE WHEN Statement in Google Data Studio to create calculated fields. It is one of the most powerful formulas available in Google Data Studio. For the purpose of this tutorial, I’m going to use it to build a table that shows traffic and conversions from Google Analytics by weekdays versus weekends.

This is a rather advanced feature of Google Data Studio, so I assume you already know the basics like how to create reports, connect to different data sources and visualize data using charts.

1. Creating a New Report

Let’s start by creating a blank report.

Click CREATE A NEW DATA SOURCE.

Connect it to the Google Analytics demo account data source.

If you don’t have access to Google Analytics Demo Account, follow the steps in this link.

Choose Master View and click CONNECT.

Here we can see a list of our dimensions and metrics. Add this data source to the report by clicking ADD TO REPORT.

2. Adding a Table to the Report

We want to create a table and categorize days of the week into two groups of weekdays versus weekends. Therefore, we first need a table. 

Add a table by clicking Add a chart.

Add Sessions, Page Views, E-commerce Conversion Rate, and Avg. Order Value as metrics.

We can resize the table by dragging the edges and double-clicking on the edges of the columns.

For the dimension, let’s choose Day of Week Name which is one of the default options available in Google Analytics.

Now we have the days of the week on our table.

This table 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: Weekdays and Weekends.

3. Creating a Calculated Field

Weekday vs Weekend as a field is not available in Google Analytics as a default dimension, so we need to create a calculated field. The values of calculated fields are evaluated based on calculations on other field values.

To make a calculated field, click on the Resource tab and select Manage added data sources to select the data source and edit it.

Add a new field by clicking ADD A FIELD.

We can assign a name to our new field. In the Formula box, let’s enter the following code which is the general outline of CASE WHEN Statement.

We need this code to return Weekday for each of the weekdays, and Weekend for Saturday and Sunday. 

So, let's change the previous values with the exact values we need for the report.

‍

When our formula is valid, we can see the green-colored checkmark below our code. In the end, click SAVE and then FINISHED.

Now, let's add our newly created dimension, which we’ve called Day Type, to our table.

‍

For each day of the week that is a weekday, the formula has returned Weekday and for Sunday and Saturday, it has returned Weekend. This result is correct, but it is not what we exactly expected.

We wanted only two rows, so I need to delete the Day of Week Name dimension.

4. Improving the Code

Let’s get back to the Day Type dimension we have already created and edit the code. You can easily find the data source by searching its name.

We can use OR as a logical operator in our condition. For example, we can check both Monday and Tuesday is a single line using the below code.

We can’t bring all of the days into one line, but we can utilize IN as another logical operator for this purpose.

Now to combine Saturday and Sunday, let’s use another method called Regular Expression, or RegEx We can, of course, use IN operator here as well but I want to demonstrate an alternative method:

Note that the Pipe character “|” used here means OR.

Click on UPDATE and then FINISHED.

We are done, this is the same table as we expected but with fewer lines of code thanks to the use of IN logical operator and Regular Expressions

If you like to know more about the CASE WHEN Statement, please refer to this article from Google Data Studios documentation.