The Case Function is one of the most advanced and most useful functions in Google Data Studio. In today’s lesson, Ahmad is here to give us a tutorial on how we can use the Case statement in building our dashboards and create calculated fields.
Google Data Studio https://datastudio.google.com
Ahmads website Siavak http://siavak.com/
🎓 Learn more from Measureschool: https://measureschool.com/products
🚀Looking to kick-start your data journey? Hire us: https://measureschool.com/services/
📚 Recommended Measure Books: https://kit.com/Measureschool/recommended-measure-books
📷 Gear we used to produce this video: https://kit.com/Measureschool/measureschool-youtube-gear
I T T T. If This Then That. If you are using Data Studio, then you should learn about the case function. Now functions are a very important part within Google Data Studio to create calculated fields. And the case function is probably the most advanced, most useful, but also most powerful function you can learn. And it really works like If This Then That. So I’ve asked Ahmad to come up with a little tutorial to explain the case function to us and how we can utilize it inside of our Data Studio dashboards. Now, we got lots of cover, so Ahmad take it away.
Thanks, Julian. This is Ahmad from Siavak. And in this video, I’m going to show you how to use Case When 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. 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. This is a rather advanced feature of Google Data Studio. So I assume you already know the basics, like how to go to build reports, a data sources and visualize data using charts. Let’s begin. We are going to start with creating a blank report and connecting it to Google Analytics demo account as a data source. So let’s select Google Analytics and demo account. Master view and connect it. Here we have our dimensions and metrics.
Let’s add this data source to the report. And we are good to go. So remember, our aim today is to create a table and categorize days of the week into two groups of weeks days versus 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.
Now let’s add our metrics. I want sessions. It’s good to have page views, conversion rate. And we also want the average order value. Average order value. Let’s make it a bit bigger. So we can see all the data. And just a quick tip, if you double click and one of these edges of the columns, it will resize all the columns automatically to show you all the data. Now for dimension, we don’t want medium. We want something closer to see type of the weekday which we have day of week name, which is something that is available in Google Analytics by default. Okay, we just want to use it as a starting point. So here we go. We have our weekdays at the name of the day of the week, number of 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-row being weekday and the second-row being weekend. 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 that their values are evaluated based on some calculations on other fields values. Sounds good. Let’s create one, we need to go to the resources menu and click on Manage added data sources to see our data source and edit it. When we edit our data source, we can add new fields. Were clicking on this all we arrive at this interface which allows us to give our new field a name. I’m going to name it date type, or let’s say sorry, day type. And then in the formula section, I’m going to use and introduce you to case when the statement. So before I start typing in the formula, let’s see what do we want to achieve in the table above. We want to write weekday. If it’s a Thursday, we want to write weekday, if it’s a Tuesday or Wednesday or Friday. But if it’s Saturday, or it’s Sunday, we want to write weekend instead. Okay, now let’s see how can we write this logic with a case when formula? Okay, CASE WHEN formula, and I write it in uppercase, but it’s okay, you can write in lowercase as well.
Looks like this. Okay, now, it is gonna say it’s invalid, but it’s okay. Because I’m just showing you it doesn’t allow me to input sample texts here. Okay. So, I mean, when a condition is met, the condition is met, we want to return a result. Okay, let’s allow the studio to do its autocomplete. But what I mean is results. Let’s see if we can get rid of that. Yeah.
And when another condition is true, we want to return another result. And we always end it by the word End. We can do something else which is else. Which means if none of these conditions are met, then return another result. This is the syntax of a case when statement. Now in this case, we do not need else because we only have seven values for weekdays for the name of the day of the week. And we already know for each value we want to return which string or text value. So let’s start the writing.
When day of week name equals two. Let’s start with Monday. Then return weekly. Similarly, when it’s Tuesday, return weekday. It’s Wednesday, again return weekday. Also, return weekday for Thursday and for Friday. But I pasted it twice now. But if it’s a Saturday, return weekend. And if it’s a Sunday, again, return weekend. Let’s wait for it to validate a formula. It says our formula is valid with this green checkmark. And now we can save it and click finished. Now let’s add our newly created dimension which is date type. So our table okay looks good. For each day of the week, that is a weekday, it has returned weekday. And for Sunday and Saturday, it has returned weekend. But we didn’t want this we wanted only two rows. So I need to get rid of their week name as a field. And we’ll have our table we can see our sessions, page views, ecommerce conversion rate, and average order value per week day versus per weekend. And we already can see some interesting data in here. So if I was the owner of this shop, I would rather advertise and promote my products on weekdays because the average order value is like five times bigger than average order value on weekends. Now this is going to be a quick tutorial. And I cannot show you all the details of case when statement. But let’s go back to the formula. Because I want to show you some interesting things about it. Again, we go to resource, manage added data sources. It is our data source.
Type in to find our field, click on function. And here is our formula. Now the first thing I want to talk about is the logical operators that you can use in the conditions section of case when the statement. So instead of equals, we can use exclamation mark equals which means does not equal something. Okay. And another thing that we can use, we can use all and raise another condition. So all day of week name equals Tuesday. It’s a big day.
So as you can see in a single line, we have checked for both Monday, and oh my typo. For both Monday and Tuesday. Okay, now we can’t bring all of these days into one line. But there is still a better solution. Here, let’s remove line three to line six because I want to bring all weekdays in one line. For this, I’m going to use in.
In a case when statement we can use in and then parentheses. And then inside these parentheses, we can have multiple values separated by comma. Okay, like Monday and Friday.
So the result is the same. But we achieved all the logic that we wanted. Now to combine Saturday and Sunday, for there’s a few more little bit more technical, I’m going to use a regular expression. And yes, you can use regular expression, in a case when a statement, which you know is powerful. And it brings the flexibility of this formula to a whole next level. If you do not know what regular expression means then it’s fine you can use in as so before. We wrote it much more easier in seven lines, it is possible. But I encourage you to go and check out regular expression because it’s so much powerful. And it comes in handy in many situations. So for this, I’m going to write regexP_match.
And then I’m going to have my field name. And then I’m going to have my regular expression in here which in this case is simple. Saturday pipe, which means or Sunday. So basically in the language of regular expression, I’m saying that when day a week is Saturday or Sunday. The formula is valid, let’s update it and click finished. Great. Now we have the same table which has two lines of code. If you’re curious to know more about the case when statement, please refer to Google Data studios documentation. It’s easy just Google it. And hope you’ve enjoyed this tutorial and thanks for watching. Bye.
All right, so there you have it. This is how you can utilize the case function within Data Studio. Do you have any other use cases that we haven’t thought about? Then let us know down below in the comments. We’d love to hear from you. And if you want to learn more about the functionality of Data Studio, definitely check out our playlist right there. And don’t forget to subscribe to our channel because we bring you new videos just like this one every week. Now my name is Julian. See you on the next one.