There are those that use Data Studio.
Then there are those that use formulas in Data Studio.
Using custom formulas to manipulate data, create new metrics (or even custom calculations) can help take your Data Studio reports to the next level. This article will dig in to 5 ways you can use simple Data Studio formulas like CASE WHEN to take your skills from beginner to intermediate. But first an important question: what exactly is the CASE WHEN formula?
What is CASE WHEN in Data Studio?
CASE WHEN is a custom formula that evaluates a list of conditions (or cases), before returning a desired result. In essence, if you want to show a value conditionally based on different criteria, CASE WHEN is the perfect formula for you. Here’s an example.
Let’s say you want to group different sizes of orders into three categories, Small, Medium and Large. Using CASE WHEN in Data Studio, you can set up a custom dimension that looks like this:
WHEN Order Size < 100 THEN “Small”
WHEN Order Size < 200 THEN “Medium”
WHEN Order Size >= 200 THEN “Large”
Using CASE WHEN for conditional text
Let’s say you want to create a Google Data Studio report that adapts to the way it’s being used. For example, you could create a text box that compliments a user when it sees an Ad Data metric that looks like it’s risen above a certain threshold.
In this case we’ll have a positive cheer if impressions are above 50,000, and a “Needs Improvement” callout if it falls under.
1) Create a custom field
The first step is to create a custom field by navigating to the bottom right of your “Data” column and selecting Add a Field.
After that, you can insert your CASE WHEN formula, where as you type in a metric or dimension that’s already in your data source, you should see it prepopulate, like the screenshot below:
2) Insert your custom dimension and stylize
Now that you have your dimension set, which will display a line of text based on a conditional value, it’s time to insert the dimension into your report.
To do that, add a “Chart” from the Insert tab, and drag your dimension into the chart. It might look a little funky at first, so let’s stylize it.
Make the chart headers transparent, remove the header row, remove pagination and row numbers, and boom. Just like that, you have conditional text that adjusts depending on another value.
Using CASE WHEN to filter data
Another use case for CASE WHEN is to filter particular data sets depending on a conditional value. For example, if let’s say there’s a marketer dispensing a paid ad data report to different stakeholders across different departments.
While this report may contain all digital marketing, inbound and sales data, you want to only show data relevant to each department.
Fortunately, with CASE WHEN, this is a simple 4 step process.
1) Create a new parameter
The first step is to create a new parameter that will be used to fuel a data control. To do this, click the “Add a parameter” in the bottom right of your report. There, you’ll select permitted values as List of values, and enter each value accordingly:
2) Create a data control with that new parameter
The next step is to create a new data control, which will be represented by the parameter we’ve just created.
Now add the parameter as the control field:
3) Create a new conditional field that shows data, well, conditionally
Now that we have our parameter and control set up, it’s time to create new conditional fields that are connected to our current data set, but that are now formulated to only show data based on specific criteria.
Here is where we’ll use the CASE WHEN method to create a new field. In this case, I want to set up a conditional field that returns one of either Marketing, Sales or Organic/Inbound based off the different platforms we have in our data set.
WHEN Platform = “Paid Search” AND Conditional Data = “Marketing” THEN “Marketing”
WHEN Platform = “Paid Search Partners” THEN “Sales”
With this field configured, you can now add it to your any charts and graphs, and use our data parameter above (Conditional Data) to filter out values accordingly.
Using CASE WHEN to password protect reports
While we cover this topic in more detail in this article about how to create password protected Data Studio reports, here’s a quick run down of the process for those keen on using CASE WHEN for password protection.
Users may want to password protect their Data Studio reports if they’re sharing their report publicly and only want certain users to have access. This could be achieved with email sharing, but when you’re trying to share en masse, email sharing runs into limitations.
Instead, CASE WHEN provides a quick and nimble solution to protecting your reports with a password.
1) Create a new parameter, “password”
We’ll start by creating that parameter password, which will then be used for an input box data control that you can put on the same page, or a separate page. Keep the type as “text”.
2) Create a conditional field, “Include”
Next, we’ll create a conditional field called “Include”. In essence this field will use CASE WHEN to evaluate the value of the password input box (controlled by the password parameter), and return a 1 or an 0 depending on the value you’ve set. For example:
3) Add a filter to the charts you’d like to hide, dependent on the value of “Include”
The final step is to create a filter on the charts you’d like to hide. This filter will essentially run as follows:
If Include = 1, then include all the data
If Include = 1, then exclude all the data.
And that’s it! In three simple steps, you’ve password protected your data studio report.