Now in this module, we'll cover some of the performance characteristics of Data Studio when it comes to cache. We'll also look at you can even write SQL directly within Data Studio. And we'll cover some of the key points and sharing dashboards. First and foremost, one of the great things that you can do with a lot of visualization tools, Google Data studio included, is to create these on the fly calculated fields, by using things like IF statements, CASE statements, as you see fit. So instead of for example, if it's too much of a complication, then go back in a BigQuery, and then create these case statements in SQL, you can do so directly through their Google Data Studio UI. So let's walk through one of those demos together. So here we've got a quick calculated field example. I've use the same IRS 2015 reporting example that we have in the past. Let's take a look at the end product and then we reverse engineer every part of it and you'll get familiar with how to create some of these advanced things like filters and calculated fields as we go along. So first and foremost, one of the things that we wanted to do was flag any of the 2015 charity filers that have been terminated. And then also set up as separate flags for communication, if they have filed with paper as opposed to electronically. So we got a filter for the charity name, and we've got a filter called flag to communicate. That's what we're going to create together. So a flag to communicate has three options that we're going to set up. It's okay. And it's terminated. And then terminated, but also a paper filer. So 2015, the filings that were 69 that were terminated but also paper filers. And you can see that you can filter into those 69. And we have some other quick tables to set up and some scorecards over here to get you a sense for some of the details surrounding those charities themselves. So of those 69, as soon as the filter takes hold here, we'll get a little bit more insight into what those were. So we have about 2500 employees, this is charity code number three, which is honestly like a bucket for the majority of the charities. I think it's like 80 percent of those out there filing. It's called subsection code three, which is a religious, educational, charitable, scientific. Pretty much everything under the sun. But also, others that were under social and recreational clubs and additional more as well. And then here are some more details around these specific charities themselves. It looks like some are actually missing a name in the lookup table, which speaks to data quality. So what we want to do, is we want to create a calculated field that says, if there are paper filer and they've been terminated, it's two separate fields, then set that up as like a static value for us to put a filter on. And the last thing that we want to do is actually add that to a filter. We'll show you how to create those filters as well. So, I am going to go to the Google Data Studio homepage and I'm going to create this from scratch. So what I'm going to do now, is I'm going to go back into editing the actual report. And what are the additional benefits of a report? It's going to have multiple pages. In this case, I'm just going to duplicate what you see here and just reverse engineer it, so you can just see. So page two of two is now blank. Page one is the finished dashboard. Page two is going to be a recreation. But you can imagine, if you have many different audiences that want to see many different things, page one for example, can be a summary that attracts the greatest common denominator. And you can focus on drilling into some of these sub pages as well. Okay. So page two, the first thing they want to do is take a look at how to create a new calculated field. To do that, let's just create a simple bar chart, could be anything but it's going to give us access to the data source, and the dimensions and metrics here. Now what you want to do is we're going to add a new dimension. And all the way at the bottom, in order to create your own calculated field, you need to get into the data editor. How you do that? Is create new dimension all the way at the bottom. And then this plus sign right here, as we have hover you'll see create a calculated field. So I'm going to do, instead of a flag to communicate, I'm going go ahead and completely delete that. And I'll show you how to recreate that on the fly. So we create a new flag field called, we'll call the same name, flag, to communicate. You can leave that D value automatically generated. Now for CASE statements, what it's going to be is, case WHEN, and then some condition, and then, then, set it as a value. And then you can do multiple of those. And then add an else in the actual case statements. So very much like stable. So I want to do Case when, and then there's a field where it cease operations. That is the autocomplete fill or ceased_operations equals yes or capital Y. This is just me knowing the data model a little bit beforehand. And, the efiled_indicator is equal to paper, capital P, then we can set up some summary flag. Then we could say something like terminated. And there a paper filer. Now you could do an else here, and then have electronic, terminated or something like that, but we're going to be a little bit more explicit. So we're going to go WHEN ceased_operations. When ceased_operations is equal to Y. And this is the other version of cease operations. And it would also be more explicit as well if we wanted to. And efiling indicator equals, going all the way to the end here, electronic. You can't really see as I'm typing this up. Then we want terminated and electronic filer, and then we're just going to end it directly after that. If you don't do an else, the else is inferred to be null. And we're going to go ahead and create that field. Sending my syntax is good, which it is fantastic. And if you get an invalid formula here, just go ahead and tweak that syntax. Maybe you're missing like a single quote, or there is a mix of dimensions in ledgers. There's some different rules when it comes creating these calculated fields. So let's review what we have. We have this case statement as flags, things that are e-filed paper and the charity has been terminated. And then we've had the special flag values as well. And the last thing we want to do, instead of actually having a null value going all the way to the end here. I want to do do else. I'm just typing in, okay AY. A little bit hard to see but going all the way over there, you can see that. Else, if they're not terminated, then they're just going to be Okay. Let me go ahead and hit done there. And that's how we create a calculated field. Let's take a look at what that looks like. I'm just going to delete this graph created earlier. Going to our filter control all the way to the top those three lines, click and drag out a filter control. So you'd see, it automatically assumes a dimension that you want to filter on, and then metric associated with it. So this is our flag to communicate, which is actually the dimension that our filter on. It's going to give those three options, not terminated, terminated but filed electronically, and terminated but filed with paper. And what we can do is we can look at the total assets and liabilities as one of the metrics. We're gonna go view that. And you can see, this is the total amount. Let's say you just wanted the count that was actually terminated. We're going to go back, clicking on the filter, clicking on the metric. We actually want to click that pencil icon to edit it, and what we can do there is the count distinct. We actually want to count the distinct EIN numbers. That's a little bit more unique than just a total assets and liabilities number. Now, to do that, here is one of the special tricks. So click on the edit. Now, instead of doing a sum, because you're doing a sum potentially on EIN numbers, which doesn't make too much sense. It's like summing itself on number and doesn't make too much sense. We go ahead and count distinct. This is how you want to actually approach that aggregation. Set a review. That one, two, three number, go ahead and click on that. This is what aggregation you're treating it as. And then go back into view, and we should be good to go. So you have a flag to communicate. We have 256,000 that are normal, and we have 70 that were terminated and filed electronically and 69 that were terminated and paper filers. Now, that's great. So I'm going to go ahead and filter for those that are say just terminated and paper filers. But since we have no data tables or no scorecards or anything like that, that filter doesn't do too much good without underlying graphs to support it. So let's quickly create some of those scorecards. So scorecard is this real fun chart right here. And it's just a large number. So if you want to just say, I just want to get the count of employees for these six charities. I want to go into my metrics. Instead of clicking at edit, click on the actual pill itself. Number of employees should be a metric. There's number of employees there, so 17 million. And, again, the filters are applied. I'm right clicking on this. I'm going to duplicate this. So if you have a chart that has a lot of stylistic formatting already, you can right click and duplicate that, save yourself some work. And you can add aliases to these fields as well for a different display in. Let's do revenue and then expenses. So, total revenue is a field. That's great. It's going to be a very large number. I'm going to duplicate that, then I'm also going to show you how to actually get that neat formatting for the numbers. Again using our grid for alignment here visually. Click on total revenue, and then I want total functional expenses just very quickly. And, now, let's make these numbers a little bit prettier. So for total revenue, instead of the 2 million that's there, click on style. Compact those numbers together by checking that check box that's right there. And the same goes for the total functional expenses as well. Okay. We have our scorecards. And, again, you can select all three of these and then move them in tandem as a group. And, now, we want a little bit more detail. So we're going to allow the ability to filter down on individual charities. And to do that, we're actually going to have just the charity name as another dimensional filter here. And filters can interact with each other, which is pretty nice. So you actually filter down just the terminated charities. That also filter the list of what are available to choose from in that name filter. And we'll choose the number of employees instead of the total liabilities, a little bit more informational. Okay. Last but not least is two very quick data tables, and you'll see this is very quick to create. If you remember from the initial visualization lesson that we had, click and drag that data table, and here's where you can add as many as you like. So, here, and this is just preference, what I'm visualizing on is the charity type, which to know is whether or not it's an educational charity or a business league or for charity. And we have the number of employees per each of the charity at different times. So number of employees is the measure or metric, and the dimension is the charity type. And, again, these are going to be filterable as soon as we go into our view and sort the filter. That's a good data table. I'm going to right click and duplicate that, especially if you have those stylistic elements you don't want to keep repeating over and over. And then, here, I'm going to add a little bit more specific details about the charities themselves. So I'm going to add multiple dimensions. So I'm going to add the name. I'm going to add the state. Let's do name first. I'm going to click and drag the name above the charity type. And that reorders the column positioning in the data table, which is nice. And then I'm going to add the state. So name, charity type, and the state. Number of employees is fine, but if you wanted to add additional metrics, you could as well. So we could do something like total revenue. And there you have it. You can click and edit this data table. Play around with the alignment and the formatting a little bit. You can see for a lot of these charity names, it's actually null, and that represents a data quality issue that you could look at in the 2015 versus the organizational EIN table itself. Let's see if we can filter on a few of these and get some of those terminated charities out of that, believe it was 69, so terminated and paper filers. You can additionally filter on the charities. And you can additionally filter on the new filtered list for the names of these charities. So say if we just wanted to focus on deselecting all these 12 employees for national benevolent, you could get the state, the employees, the revenue. And, again, we could make that feel a little bit wider, so you can make that name a little bit more easy to read. We're going to go ahead and shrink down the numbers and then widen the actual name. And this is for the visualization, as much of an art form as is science. Going back into the view, filtering for say just a few of these. Actually, let's flag to communicate filter them first. We get those terminations. Selecting all and only those that have been terminated and all those terminations. Let's get to the top three by number of employees. And for 2015, this is the inside. So these are this top three that were paper filers and the number of employees and the total revenue and which state that that charity organization was from. Okay. So to review, we've covered how to set up a filter, how to create a calculated field using one of those case statements and then how to create scorecards and filterable tables. They interact with those different filters that you've chosen. Last but not least, one of the quick points that we're going to cover is going into file, report settings, you can toggle the cache on or off, which if you have near real time data, it's probably best to actually disable the cache. If you're talking like sub 30 minutes or sub 10-minute data, that's actually flowing in here from BigQuery, probably best to disable that cache to get that more near real time view, sacrificing a little bit of the performance. But the default instead of cache enabled, and we'll cover a little bit more about that in the slides again. Okay. Back to the lesson. Okay. Now, one of the really interesting parts about Google Data Studio is there's actually two separate layers of cache. There's cache in the visualization tool side, which is called prefetch cache, and there's a typical BigQuery cache that happens behind the scenes when that query is created and then passed through to the BigQuery. And BigQuery cache, you can handle just like you would normally before. You can disable a cache on the query, or you can actually pass in a function that invalidates the cache. And on the visualization tool side, as you saw in our UI demo, you can actually disable that prefetch cache as well. So you can break both of these caches by using that refresh cache button, or you can permanently turn off that prefetch cache as you see in the last bullet point there by going to the report settings and toggling off that checkbox.
