>>> By enrolling in this specialization you agree to the Qwiklabs Terms of Service as set out in the FAQ and located at: https://qwiklabs.com/terms_of_service <<< Welcome to the Coursera specialization, From Data to Insights with Google Cloud Platform brought to you by the Google Cloud team. I’m Evan Jones (a data enthusiast) and I’m going to be your guide. This first course in this specialization is Exploring and Preparing your Data with BigQuery. Here we will see what the common challenges faced by data analysts are and how to solve them with the big data tools on Google Cloud Platform. You’ll pick up some SQL along the way and become very familiar with using BigQuery and Cloud Dataprep to analyze and transform your datasets. This course should take about one week to complete, 5-7 total hours of work. By the end of this course, you’ll be able to query and draw insight from millions of records in our BigQuery public datasets. You’ll learn how to assess the quality of your datasets and develop an automated data cleansing pipeline that will output to BigQuery. Lastly, you’ll get to practice writing and troubleshooting SQL on a real Google Analytics e-commerce dataset to drive marketing insights.
Challenge 2 Solution - Track Visitor Checkout Progress
Loading...
來自 Google Cloud 的課程
Exploring and Preparing your Data with BigQuery
448 個評分
從本節課中
Module 2: Big Data Tools Overview
Learn what are the key big data tools on Google Cloud Platform that you will be using to analyze, prepare, and visualize data
與講師見面
Google Cloud Training
All right. Number 2.
Let's copy in our question here.
All right, so, you know the people, I feel like I should buy one of those [inaudible] we sell.
So, let's see. Challenge Number 2.
Write a query that shows the e-commerce action type.
Again, there's like eight or nine types,
add to cart being Number 3,
completed checkout being Number 6,
and again, that's just me memorizing this schema here.
E-commerce, where's the type?
Yeah, e-commerce action type. There we go.
Shows the e- commerce action type and the distinct count
of visitor ID associated with each type, okay.
So, this is where users are,
in terms of the checkout,
or how many unique visitors are in the checkout process,
or how far they've gotten.
So, we want again same table, so,
we're just going to copy and paste that,
give us all of our sessions,
and give us the,
let's go into all sessions,
and let's pull open in this schema,
I often Control F in the browser,
which brings me down here, which is a useful tip.
Let's bring in the e-commerce action type,
and we also want the distinct count of full visitor ID.
So, let's do the distinct count first, count distinct,
full visitor ID and let's call it as number of,
let say unique visitors in e-commerce action type.
Immediately, since we did an aggregation on one,
your branch should immediately be jumping to the group by the column index,
which is the second one or repeated here.
We'll also order it by,
since the actions are sequential in the checkout process,
it's got to be added to cart before you can check out,
and let's see if this gives us what we want.
Now, I'd expect that there will be a lot more added to cart that had
been checked out, since it's cumulative.
Everything that's been added to cart,
the subset of that has actually been part of checkout.
Checkout is Number 6, so,
I want to include that here.
So, it looks like no action.
One, I think it's they viewed a product list.
I think I'm going to bring this in here,
just so I make sure I'm speaking the truth here.
Number 1 is the click-through lists.
Number 2 is they viewed the product detail.
Number 3 is they added to cart.
Four is that removed it from cart, interesting.
Some cool insights you can probably get there.
Like, I really don't want to talk for this anymore.
I should have bought it. Five is they've checked out completed purchases.
Number 6, they are in the checkout options still,
and unknown is zero.
Probably not engaged in the e-commerce pipeline yet.
Block comment that out, just so we have that for a little bit later.
Okay, so we know for each of them and the different types,
we have the mapping associated with each.
You saw, it was hard for me to memorize,
which each of these different steps are so,
is there a way that we can just associate the label with these,
without having to ingest a new table yet? We haven't covered that.
So, let's see if we can just do a,
if you've worked with case statements before,
we can definitely do that here.
So, let's say do a little bit of a mapping exercise here.
Case statement basically says,
it's a kind of chained if then together.
So, take a look at this field,
when it's zero, then do something, when it's one.
So, this is probably the most boring part of the video right here for you.
So, just bear with me. When it's three then,
and then when it's four then do something else,
when it's five, when it's six.
So, those of you who have already worked through the lab,
you'll probably notice the error that I'm building up for you here.
After that, we're going to say,
if it's not any of those,
throw an error, because we expected to have a value and that would be useful for us.
Then, we need to end the case,
I'm going to give an alias.
The whole thing that we're just going to call it eCommerceAction type,
give it a label.
Great, so now does that work?
Once that, it's not going to work,
it's not going to return anything.
So, this is going to be click-through of product lists,
is Number One, unknown terror of typing things by hand.
I think three is added to the cart,
I'm just going to copy these things verbatim.
Number 2 is viewed a product detail page, and again,
once you have these insights,
is these sample queries,
if you work with your own Google Analytics dataset,
you can go wild, you can see like what product detail pages are viewed the most,
but then, nobody checks out with those products,
so you could revise those product detail pages,
because they have a lower conversion rate.
So, if you're working with e-commerce,
porting the data into BigQuery is a great first step to even sharpen your Analytics jobs,
but really get some really cool insights.
Number 5 is they're in the checkout process.
Number 4 is, here we go, gigantic case statement.
They removed it from the cart.
So, I'm going to execute selected on this.
Now, here's the issue. I'm going to run it.
So, it's saying that the matching that I had didn't really work.
So, I'm going go back into the,
actually I'm not going to rerun that query,
but the point I wanted to show is that,
this field right here as I hover over it,
you can see that's a string.
So, although it's a number,
it looks like it's a number and the display, the query results,
it actually needs to be escaped since it's included as a string.
So, this is very important when we start to do mathematical operations on this later on.
We might have to convert it back into a number.
Keeping that in mind, I'm actually going to have this be, instead of an error.
I'm going to create this a null. In case we have to convert this a little bit later.
So, that should do it.
Let's see. All that work just to get the explanation.
Yup, here we go.
Where are they in the checkout process?
Great. Unknown all the way down to completed purchase about 20,000 people,
and you can get even the subset of those.
I mean, you can start doing cool ratios, right?
So, divide your total completed purchases by the amount people that have added to cart,
and then, you get your drop off rate.
So, it looks like about 50 percent of the people do not complete the checkout.
Actually, I think that's part of our question, so,
let's actually do, you can do it in a query.
If something else, BigQuery is a nice calculator for you.
So, I'm just going to do it here,
and that'll give us the percentage of folks that have added to cart,
who then completed a purchase, which is the numerator.
So, 35 percent of the time,
if you've added to your cart,
you will go on to complete a purchase.
So, you can begin to think from a marketing perspective,
can you promote those folks
that I've added to cart but then abandoned it? Why? What are the reasons?
You can drill down into a treasure trove of detail there.
But we're not done yet.
Actually, that is the insight that we want for challenge Number 2.
We're not done with the challenges.
So, it's about 35 percent add to cart, and then checkout.
Tickets for e-commerce standpoint.
Again, the success metrics are all relative.
So, it's just, is that good?
It depends on your business.
If you're selling $100,000,
a million dollar items,
and you get a third of them going from cart to checkout, great.
