Google Data Studio: How to use the CASE function and IF statements? | Lachezar Arabadzhiev | Skillshare

Playback Speed


  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x

Google Data Studio: How to use the CASE function and IF statements?

teacher avatar Lachezar Arabadzhiev, Founder and CEO @SkildLabs

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Lessons in This Class

4 Lessons (13m)
    • 1. Introduction

      0:25
    • 2. Creating a custom dimension using a CASE statement

      4:56
    • 3. Using a CASE with regular expressions and REGEXP_MATCH

      4:27
    • 4. Creating an IF statement within a custom metrics

      3:14
  • --
  • Beginner level
  • Intermediate level
  • Advanced level
  • All levels
  • Beg/Int level
  • Int/Adv level

Community Generated

The level is determined by a majority opinion of students who have reviewed this class. The teacher's recommendation is shown until at least 5 student responses are collected.

7

Students

--

Projects

About This Class

In this class, you learn about of the most useful functions in Google Data Studio, namely the CASE. Knowing how to write a CASE statement is not only helpful in Data Studio, but also it allows you to understand how computer logic works. In particular, we are going to explore the following scenarios:

  • Creating a custom dimension using a CASE statement.
  • Using a CASE with regular expressions and REGEXP_MATCH.
  • Creating an IF statement within a custom metrics.

dca52570.png

Meet Your Teacher

Teacher Profile Image

Lachezar Arabadzhiev

Founder and CEO @SkildLabs

Teacher

Hey there! I'm Lachezar Arabadzhiev and for the past five years, I have helped companies harness the power of data in a variety of ways to drive business growth and innovation. I began my career as a digital marketer at Microsoft but soon transitioned to the audience and analytics world, where I had the opportunity to work with major brands including Air Canada, RBC, Walmart, Kimberly-Clark, Nintendo, Mazda, and HSBC. In addition, I am the owner of the Data Studio Canada educational website. 

Throughout all that time, I always loved teaching and developing learning programs for my colleagues and friends. That passion materialized in early 2021 in the form of a brand new company that I was luckily enough to start.

I am currently the Founder and CEO of SkildLabs, where ... See full profile

Class Ratings

Expectations Met?
  • Exceeded!
    0%
  • Yes
    0%
  • Somewhat
    0%
  • Not really
    0%
Reviews Archive

In October 2018, we updated our review system to improve the way we collect feedback. Below are the reviews written before that update.

Why Join Skillshare?

Take award-winning Skillshare Original Classes

Each class has short lessons, hands-on projects

Your membership supports Skillshare teachers

Learn From Anywhere

Take classes on the go with the Skillshare app. Stream or download to watch on the plane, the subway, or wherever you learn best.

Transcripts

1. Introduction: Hey, there lectures are here with another video on data visualization with Google Data Studio. In this class, you're going to learn how to create came in if statements, which are probably two of the most popular and useful features in the data Studio platform. You're also going to learn about conditional in regular expressions and how they can be super helpful when dealing with complex dimensions. Now let's get started. 2. Creating a custom dimension using a CASE statement: In this video, we're going to create a custom dimension by using one of the most popular functions in Google Data Studio. And that's the case. We're still going and keep the same data source that we used for the previous video. And let's try to create linear dimension now. So I'm going to go into add dimension. Then click Create Field, and get to my Formula tab. Now, remember that we're doing this just because of convenience. So you can see both the table and this popup, but in this case will only be valid for this specific chart. If you want the case to be valid for the entire report, will have to create it on the resources. Let's name this strategy extended. And the formula here, it's going to be a little bit more lengthy, but here is the main structure. So you first start with case to specify that you're starting a case. And the case is a combination almost similar to a function. You can put different conditions within it. And if they're all satisfied, there is different outcomes. And then right when. And this specifies the beginning of what we're trying to create, which is a condition. So when strategy name, so we're going to click on this one in and I'm gonna open brackets. And so, so far we've mentioned it's the win condition. We're starting a new one. Strategy name is the dimension that we want to source from and in really specifies what are we looking for in this strategy name dimension. So we're going to open parenthesis or you have to open these quotes, both single and double ones work. Just make sure that you're consistent throughout the whole formula. So I'm going to say here, well, let's say that strategy, strategy, two, strategy for are actually the same thing. And you just want to give them a different name in tell Google Data Studio that so I can combine them. So strategy, strategy for. So we've mentioned that already and we're closing the brackets. And the next thing we're going to write is then. So we've put the condition. Now what is the outcome of this condition? Well, we're going to say if this is correct, then just mentioned combo 25. So that's our main statement here. We're going to click Enter to go through and you can do another win strategy. Underscore name. In. We're going to open this say strategy. Maybe three this time. Strategy for. And then we're gonna do, then is time is combo 3 and 4. Now what happens here is we've applied these two conditions. There were a lot of other strategies. So what's going to happen with them? Well, that's how we really end a case statement. So we're going to write else and write either combo. So we're essentially saying within strategy 25, we have one of our first conditions. Then we have 34 here. And we specified what we want Google Data Studio in this case to return if none of these match, then else would be just put other combo. And you can always end, of course with an end. And that should give you the green tick here. So I'm going to click Apply. And then strategy extended is already in my table. So let's see what has happened. We're going to expand this a little bit. So strategy to, well, it matches, it is strategy 2 or 5, so it's the combo. We have strategy 4, that's 3 or 4 here. Great. Same with strategy five. Every single other combination with 1310 that doesn't match, it went to other combo. So by doing this, you can organize your data in a much better way and create new dimensions that match your own rules. In the next video, we're going to take this a step further by introducing regular expressions. We're really doing more complex case statements. 3. Using a CASE with regular expressions and REGEXP_MATCH: This custom dimension point look a little bit more complex, but don't worry about it. We're just going to unpack it once I write it. So select the table once again and go to the strategy extended. So we're going to keep that one here and actually create a new one and add dimension. And similar to this, we're going to do create field and ride strategy, red X. And that's simply just specify that we're gonna be using regular expressions. Now the regular expressions that Google uses, the full a specific model and you will see all that in the resource section and some of the notes that I've left. And essentially they're these very smart ways of specifying a search pattern based on some symbols and characters that you can add to the formula you're writing. It will become much clearer when we start typing. I'm going to type case once again. Then we're going to do when. And here we're gonna do something different. We're going to write the wrench underscore match. And that burden, I'm going to click on it, and that's going to open these little brackets here. So the regular expression is really going to help us to save some lines of this code and do things more efficiently. What it normally have here on every single formula that is, when Data Studio, we have this little tool tip, a little pop up that has some explanation of how the formula works. So there's a summary. It tells you that returns true if x matches, why false otherwise. So you're kinda testing these conditions as they go. It tells you what x is and what the regular expression is. And there is a little example. So let's type our regular expression. We're going to start with the dimension that we want to source information from and that strategy name again. And then we're gonna do a comma here. And inside, we're just going to type strategy. And then undo the square brackets. And I'm going to write one to five. And so you're going to see what's going to happen in a minute on this one and close this. We're done with our regular expression. Then we're gonna do then. And we're going to say combo one to five. For every other strategy name. We're just going to use the else statement and write other. And then we're going to end this and click Apply. Now let's see what happened here. Well, the strategy me from one to five, we have renamed them to come a one-to-five. And basically you can see it everywhere. So 4, 5, 1, strategy 2, and then Strategy 3. Everything else is other. And, and we did this in a very efficient way. So let's just go back and take a look one more time at the strategy rejects that we just wrote. So we had the one statement we starting the condition ridge expression match strategy. And then in these parentheses, we put the name strategy here because we know that every single string of texts, every single word starts with strategy. And then instead of typing it like last time, we're using in enlisting every single strategy 1, strategy 2, 3, 4, and 5, which is told Google Data Studio, I want every strategy from one to five. And then really, in order to do that, you need to have the square brackets and just the numbers from one to five. If I change this from one to eight, it will reflect on the table. And by doing that, you're saving yourself a lot of time, especially if you're dealing with a table with, let's say, a 100 different strategies or 100 different entries. With one line of code, we managed to actually write the regular expression and sort our data as opposed to running more. So I would recommend you to look at what regular expressions can do for Data Studio in Google Analytics and get more familiar with them. Of course, I'll share some resources on that in the next video, which will be the last in this module, we're going to talk about if statements, which is the newest addition to the Google Data Studio family. 4. Creating an IF statement within a custom metrics: In this last video, Let's see what we can do with if statements. So I'm going to remove all the extended and the rejects that we've set up. And I'm going to focus here on the metrics. So we have impressions and clicks. And let's think about this scenario. Let's say that you've made a mistake within your click data. And every single strategy that has more than 5000 clicks actually had an error and the data was overstated. So we'll have to adjust that. However, most of the times we will go to the data source and we'll have to merge tables and update things. But imagine that we're in a rush just time and we have to fix this right now. To do that, we can use an if statement. So I'm gonna go here to add metric in this case, and then click Create field. And for the names we're going to write clicks adjusted. And for the formula we're going to start with, if. Once again we're going to do those little brackets and then we did the tooltip that tells us really how this if statement of works. So you probably have encountered this in a lot of other type of software. The if statement is really you have a condition, you have a result, you have a positive outcome if that condition is true, and then if it's not true, you have another outcome. So we're just going to build that based on this logic. And let's assume the condition is if we have more than 5000 clicks, then it means there has been an error and we have to adjust that and lower it. So to do that, we'll have to type clicks greater than men here we're going to put 5000. Now the important concept here is when you put a metric such as clicks, what you're specifying here is just the clicks. But even if you write the formula correctly with this, it's going to turn out to be wrong because you need to put the syllable clicks in order for this to be correct, because the sum of all clicks would mean the current setup of this table for, let's say strategy 15, you have 6 thousand clicks. That's the sum of a lot of other clinics that were associated with a different strategy 15's. So make sure that you always have some, this will be applicable to a lot of other scenarios as well. Then after this, we're gonna do a comma here. And we're gonna say, now if this condition is true, then we're going to say return one. So we're going to make it a Boolean field and you'll see in a second why. And then if it's not true, we're going to have a 0. It is as simple as that. So we're going to close this off here. We're going to keep it as a number and we're going to apply. And let's see what happens. So right away, we can see it's perfect. So we have cliques suggested, we have three ones. So the top ones are greater than 5000. So they become true to this condition. And the rest are zeros because they're lower than 5000. And with this, we can see how an if statement would work in Google Data Studio. Of course, you can make this way more complex by adding more steep means and perhaps nesting it within a case.