All right. So, here is another question. If I ran this and let's remove the limit again, we might not necessarily want to do this because you could have a ton of visitor IDs. We have 56,000 in this case, you don't want to just go selecting everything. So generally, you want to filter your data with a where clause or an aggregation function. So, here we've got 56,535 visitors that reached the checkout confirmation. Does that seem a little strange to you? Well, it should. If you remember from the previous lessons, there was maybe 56,000 transactions, sure, in the revenue transactions table, but that doesn't necessarily mean as you remember there was on average of 2-3 products that each visitor purchased. This is not how many unique visitors reached that checkout confirmation page. So, what you've got here is what I call logical error, and logical errors that query will run fine but the results that show are actually not what we actually want. So, what we'll do here is we'll do a count. That's visitor count. Immediately if we try to execute this, we've done an aggregation function on one of the fuels meaning roll up all of those visitor ID columns and aggregation function reduces it from however many N number of rows that it is into a single row for aggregation function. But page title, that field feels left out. Hey, if you're aggregating on one, you've got to aggregate on the other, right? So, in order to aggregate a string function, if you're using an aggregation function in your select clause in one field, you have to use it on the other. You could do a count of page title but that wouldn't make too much sense. So if you're aggregating in the select, all the other fields have to be aggregated. So, how we do that of course is with a group by. So, you can group by the column index, so you could do number two if you wanted. But I like to be a little bit more verbose and I'll put in the actual field that's there. So, this is going to be the count of visitors by each particular page. Let's see if that gets us closer to the answer. So this is really, really interesting. So, now we've got two different things. We've got the visitor count and then we've got the page title, and this is how many visitors have reached that particular page. So, this will tell us a couple of different things. One, we still haven't solved the problem of de-duplicating our visitors. So, I'm just going to go ahead and do that now, and basically say I only want the distinct visitors that have visited one of these pages. But as you see with the pages here, you can have many different variants of the word checkout confirmation. This page is called 11 checkout confirmation. This one is checker conformation and we have order completed, and we got checkout confirmation here. So, if we're just going by the page title or we could go by the page URL, however you want to work it out, and we wanted to filter out just those that are the checkout confirmation page, first let's rerun this query and make sure it works for the distinct count of visitors. So, you can see 19,000 almost 20,000 unique visitors actually purchased. But we have a little data quality problem here. So, the vast majority of them hit the checkout confirmation page, this is where it really gets exciting, right? Because you're assuming that all of the pages in the revenue transactions table are exactly for batum checkout confirmation, but the cool part is now we get to see that our data isn't always what we think it's going to be. So we have nine different records, one visitor reached all of these different smaller pages, and you can see different variants for that page title. So, say we wanted to filter this data. We filter that now using a where clause. So by-and-large we captured almost 20,000 visitors, you have a couple different options, you can ignore the rest of these rows. So, we could do is where the hits.pageTitle is equal to checkout confirmation. If you ran this, you get an error says, "Unexpected identifier confirmation", what on earth. So, it's trying to basically say, "Hey, there's no column called checkout." The reason why that is as you might have guessed is if you're matching on things that are non-numeric, either encapsulate them or escape them in single quotes or double quotes. So, let's go ahead and run that. This is where you'll get verbatim checkout confirmation. Now, what I'll challenge you if I ran this, I just made a C lowercase and a random letter the N here uppercase, still a valid query. Do you think I'll get the same results? Do you think it's case sensitive? The answer is, it is. So, if you're ever working with string values and you're trying to match on something, say all lowercase, this is probably a little bit more common, and you're wondering, "Hey I'm executing this query why on earth am I not getting any results returned? I've definitely done this in my own day." It's because you're not absolutely certain that the value that's going to be returned in that string is uppercase or lowercase. Well, what's one of the tips and tricks that you can do if you didn't care? You can actually invoke a quick function. So, you can say for every single page title that's coming into this query as we scan row by row as our sequel is executing, you can actually make every single one of those field values into a lowercase before we compare it with our string here. So, whatever the page title is make every single character in there lowercase and then compare it against here. So, when you actually ran that, now you don't have to worry about the case sensitivity and you'll get it matched on it and returned anyways. Now what happens if he said, "Hey, I wanted to include those transactions that reached the checkout confirmation page, but it wasn't really called the checkout confirmation page as exactly as we have it here. Is there any way that we can do the wild-card matching?" Absolutely. We can do that instead of using a equivalency operator, like the equal sign is your equivalent directly, we can use a wild-card operator, and to do that we're going to use the like. So we just did like, again, that's pretty much equal to the equal sign because we didn't have any wild-card operators. So, say you wanted to begin or end, or let's just do begin first. Any number of characters could come before the word checkout and then conformations. So, let's see if we get any hits off of that. So, we get a few. So, you can see this is six colon and space than checker confirmation, but remember we had nine there before. So, what you can do of course is add more wildcards to your heart's content. What this will inform you again is the underlying quality of the data that you have, if you remember one of the data points here as we'll find that out is could be even more interesting than this. Is if you're assuming all of these different transactions are associated with some kind of a checkout, let's see if we can do any of the ones that don't match this pattern. So, this is the really interesting part. You expected the checkout confirmation to be somewhere in the page title, yet in our transactions table we have this mugs, cups, drinkware, Google Merchandise Store. So, then you can drill down into that particular record and find out whether or not there was really a transaction or how that ended up to being recorded as a transaction even though that page title wasn't recorded as the transaction there. So again, playing with these different options and getting familiar with the errors that you're going to get out of here is going to be one of the most versatile tools in the toolkit that you have.
