Hi, welcome to the solution video for Exercise 4.5 Analyzing Test Results; the solution video. So, here we're going to use the order binary metric from our previous Exercise 4.4. We're going to compute the following, we're going to compute the count of users for each treatment group for test ID seven who had an order. So, we start out with this table and we're going to put some parentheses around it. We will call this test, we'll call this order binary. From that, we're going to roll it up because we just care here about the test ID. Actually, that we can put, we can put down here. We can say, where the test ID is seven because we just want to analyze a quiz one test. Then we want to know the test assignment and we want to count the distinct, shouldn't need to be distinct. Because everyone is just treated once on the account the user id as users. Then I want to count sum, the order binary as orders, it's so fast. Looks like I'm going to need a from statement, I'm going to rename this because that's also the name of our table. So, it's confusing. We need to do a group by. We want to look at aggregate so let's do that. Cool. So, we have the test assignment, treatment, control, the number of users in the group. I include that because they're not actually the same number of users and then, the number of orders that are completed. So, if I want to go and look at the results, I can do that by putting in my number of successes, I'm going copy that. From here we've got, in their control, we've got 3,212 successes out of this many users. Then we've got in the other group 3,357 orders out of this many users. We can compute it. Looks like we have a p-value of 0.03, and the improvement in order binary is somewhere between 0.33 percent and 9.4 percent with an average or a calculated lift of 4.9 percent. So, whatever this test was testing, it looks like it had a statistically significant, or potentially statistically significant increase on the number of orders, the users who make an order. Which is cool. For this next question, we're going to compute a different binary metrics. So, to do this, I'm just going to cheat off of my work from the previous question. I'm going to go back and edit just a part of it. So, here instead of doing order binary, I'm going to comment this out for now. We want to look at the event of someone viewing an item. So, I'm just going to hide this all and a little sub table, and we're just going to do select star from dsv events. I want to know where the event name, is view item. Let's just make sure that I got that. Just look like this just has one parameter but I can limit it as well. Just say, actually it doesn't matter. So, I'm just looking for users who had these events. So, instead of orders I'm going to call this views. I'm still going to match it on user ID. Then, in here, instead of calling this orders.created, I'm just going to call it views in event time. Then I'm going to change everything views binary. Let's see how that works. Those are pretty quick swap. I was using most of the structure of the existing query to get this item view situation. So, you can go in and you can look and see if this feature, whatever it is, for a test ID seven made a statistically significant difference on how many users who viewed an item or not. Let's go straight into the next one. Let's go and make a metric where we look at the view binary but in a short amount of time. So, I'm going to cheat off of what we just did and just alter it a little bit. So, now, I'm going to add in one extra condition into this view binary, I'm going to call it view binary 30 days. So, it doesn't just need to be this view time is occurring after the assignment event, I also need for, let me see if I can minus up nicely so that it's easier to read. I also need for the views.event time to be within 30 days of the assignment. So, there's actually a way to do this and it's a little bit different in each flavor of SQL. But in this flavor of SQL that mode is using, we're going to use this function called date part and we're going to extract the day from each of these dates. We're going to take the views event time and we're going to subtract off the assignment event time. I'm just going to come just in from here. Then I want to make sure that this is less than 30. So, I want to make sure this is below 30, I'm going wrap this in some parentheses, and I'm going to add that comma there. So, and I'll delete this line. Let's see if we get this to work. Then I'm just going to add this in up top 30 days. So, you can see that there's a big difference between the views that happened ever versus the views that happened right after the test. Now, let's go on to the next one. Here, we're going to be computing the count of users per treatment groups, the average value of each metric per treatment group, and the standard deviation which is something that you need for analyzing mean value metrics. That you don't necessarily need for proportion metrics because the standard deviation is obvious based on the fact that all the values are either zero or one. So, we're going to start with this query which is the solution from the previous exercise. We're going to do something similar. We're going to wrap it in some parenthesis, and we'll call this mean metrics. Here, I'm going to select the test ID and am going to select count the user ID as users. Then, for any metric that I want to look at, let's just start with invoices. I'm going to do an average of the invoices. That's going to include some of those zeros and I'm going to call that average invoices, and then I'm going to do a standard deviation of the invoices as, I can spell sometimes. I don't know, from a clause. You're going to bump all these out by two spaces, and then I need to put this test ID in the group by for any to restrict to just one test ID, and I'm going to bump this out by two spaces just because, let's see how it goes. So, I could do this with any, I'm missing something, which is the test assignment. I don't want to lump those altogether and want to know how they're different between the groups. There we go. Also adding one order by test ID. So, here we can see the way is where the invoice averages changes. We can look at it in the context of this standard deviation. This is something you would do to set up evaluating an AB test based on some mean metrics, and you can just swap in if you wanted to look at line items if you wanted to look at total revenue. You could swap that in here, here, et cetera and just get the same idea.
