All right. So, challenge number three. So the key concepts again to wrap up challenge number two is, be careful when you're using strings, versus integers especially when doing mappings, and you could do a case statement when you're mapping things and that was pretty much it, and the quick insights that you can get there. So, challenge number three, it's one of the more involved ones. So, if you got this without any help, special kudos to you. Let's read the question here. Write a query using aggregation functions that returns the unique session IDs. So, session ID will probably be a combination of the full visitor ID and then the visit ID, since a full visitor ID can have multiple visits, and a visit ID is not necessarily unique, as we know from our schema. So, just to recap what I said a little bit easier. The visit ID is an identifier for the session, this is only unique to the user. For a complete Unique ID, you should combine full visitor ID and visit ID into what we're going to call session ID or unique session ID. So, that's the first thing that we're going to do. So, we're going to get all that good identifying information for those folks who have added a product to the cart but have never completed checkout. So,it's great. So, you get a list of acts or sessions, where folks have added products to the cart and then didn't go through checkout. So, this is great for re-marketing or you get those nice emails where it's like, hey you know you're shopping carts lonely, don't forget to check out, you would be surprised how effective those emails can be too. Let's see. Let's say we want high-quality, we'll also add additional cart of high-quality sessions. So, you don't want to waste our time and our marketing efforts on folks who were never going to check out anyways. So, the first thing, first, we need to select what fields we're going to use, it's going to be the same data set, we got our session and this one is going to be a fun one. So, let's go into all sessions. We're going to need the folders ready just clicking the schema here it gives it to us we're going to need the visit ID and we're going to have to combine those somehow, so we're going to combine these to get a session for that particular user and then we also want a session quality dimension. So, this is something that's provided just free for us, for each different session. Google Analytics has some nice features built in, some machine learning behind the scenes where grades the quality from zero to a 100, of this session. And if you don't believe me, go into the documentation here. When I found out about this flow field I was trilled. I was like, "Oh, this is great." So, it's an estimate how close a particular session was the transacting from one to 100. And I will continue to look up documentation how that actually works behind the scenes because I think that's particularly fascinating but right now we'll take it at face value. So, you got assessing coordinate dimension great and now we need to, we'll take the let's just gets revenues of the transactions and let's see what we got so far. And I group this by one, two and three since these three fields are not aggregated and we do have revenue. So, what we do not have so far one, two and three, I don't have a comma here that's where it thinks that it's only two fields. So, we've got visitor ID for his radius is encoded dimension and if you got the revenue associated with those transactions. Why do we need the transaction revenue associated with it well you think how are we going to exclude those folks who have gone through the cart but also gone through the checkout process. So, we want to make sure that if we did a filter like this, where you're filtering on the checkout process, you don't want to also include those who've already transacted, who've already complete checkout because if they got an email saying their carts have been abandoned they'll be super confused. They are like, "Hey. Yes. I did added to cart but I also checked out." So, we want to make sure that we have a filter for when those transaction is going to be negative or sorry, going to be not positive, no revenue. And If were going to filter on aggregation, we actually need to do a halving. So, you want to make sure out of these 524,000,that we have here, we have where the transactions revenue. I'm not sure of the quality of the data, so I'm going to say whether it's null or the trends action revenue is going to be equal to zero. So, let's see if that reduces some of the data points that we have here. So, 520,000, any of the ones where the transactions revenue, I miscopy this, yes there was an S. So, some 500,000 and did it drop that number basically this is excluding the universe where they did check out which we don't want. All right. So, while that runs let's think a little bit about what else we need to bring out. Well, we need to find some way yeah dropped and this was well above 500,000 which is great so we're now starting to exclude those, so these let's go ahead and just solve the problem right now to bring these into a single field. So, we use a concatenation function very simple concatenation function, brings it into a nice string fuel that's going mess up our group by index here so, we are going to now grouped by the unique session ID, the quality of that particular session. Now, we're going to little bit more only once and when to define a high-quality session as anything again it's zero to 100 are going to say anything over 60 thinkings. It might seem arbitrary but I will say that the machine learning they provided us that value we're going to trust it for now is going to be given additional focus. So, interesting part we looked at before this is a string, this is an integer.so you can't concatenate those two together we're going to have to actually convert this into a string before we can concatenate it it's safer to do it that way because you don't necessarily know whether or not the full visitor ID can be successfully converted to a number. So it's going to convert them or have them bolted strings concatenate them together and then give that another go. Great. So, now we have a wow we went from 500,000 to 1,500. So, now we're getting really really close. So, what we haven't looked at yet is where they are in the cart process we want to make sure that they've added the product to the cart at least and not just looked at those pages. So,m One of the great ways that we can do that is with the e-commerce action type again and that's coming our checkout process progress. Now, if we ran this we need to add a comma and if we're grouping by things we're going to group them by here. Let's run this now we're adding where they on the checkout process and since we're filtering on revenue. So, you can see we don't want this we want to make sure that they've added to the cart but they haven't gone further or they haven't at least completed a transaction such that revenue has been recorded, so what do we now? Great part of what we can do, how far did they how far did they get, we can basically say well what's the max if we were to cast this as an integer and because it's a string right now I don't really trust doing a max and a string value. Let's see if it is cast as integer. We should take the maximum. How far they've gotten as the checkout progress. Let's see if my, I think it's integer, it might be in int 64 was here and 64. So, what's the furthest that they've gone some people have gone, and we've got the, we're grouping by the session quality dimension and we no longer want to group by the eCommerce action type, because we don't want just the latest progress that they've gotten. All right, we're getting there. So, the latest progress for these folks is zero. We want to ignore those. We want to make sure that we only target folks who are high-quality sessions but and again, you may want these people, you may want 1,500 folks send that email on out that have no revenue that's great, but we want to actually focus on folks that are kept technically abandoned carts, meaning that they've gotten to the add to cart page. But this could be a great sub list that you wanted to email anyways because it's a little bit wider. So, how do you filter out the max, the aggregation? Well, we're going to come back down to our having and since we have access to the alias in the having clause. Where the checkout progress is equal to three and I don't need to escape that I don't believe and three is equal to added to cart. Now, here's the really interesting part of first that should work on unexpected identifier transactions revenue, it's because I added this in the comment. Let's run this. Now, our results could have change. We can check our progress is three, might not necessarily be sure one of the things that I want to save as a cache is when you're combining ands and ors and you have multiple things in here, we want to actually say that they've added it to the cart and one the following is true. In order to do that, you actually want to have the parentheses here. It's basically say, it has to meet this condition and then the transaction revenue has to be either null or zero. So, just make sure to be very careful use of parentheses especially when you're chaining together ands and ors are logical operators. All right. Let's rerun that again and this should be very close to our list, so we have 426 folks with high-quality sessions is to find 60 and above, who is latest progress? Non-revenue generating has gotten to Add to Cart, fantastic. So, now that you have this session ids again you can get into the marking aspect of it where you could find the Windows users revisit or just send him an e-mail, if you have their account information on file or otherwise have a re-marking campaign or targeting them, but that is the analytic side. Now, imagine one of the things we're going to focus on in the next course is actually how you can create this into view, that you can then have automatically populated. So, you don't have if you store this as a table obviously this is going get stale. But if you store this query, behind the scenes, that you can access that view and it will be the freshest and latest data, so if you wanted to actually send out an email every month or every week or depending upon the user, maybe you could even have it change to when they leave your site, if they don't come back to your site within a week then you send them a staggered time-wise emails as well. Well, that's the end of the challenge questions this last one was probably the hardest review of what we've covered here. So, you've got a couple of type conversion functions, we've got a liberal use of filtering out aggregations, we reintroduce the session quality dimension which is one of the really fun ones for reporting for Google Analytics, we've introduced the concept of working with a nulls, and we've played with the checkout progress for adding to cards as well. All right. I hope you enjoyed that and we'll get back to the lectures.
