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. >>> 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 <<<
Challenge 1 Solution - Calculating Conversion_Rate
Loading...
Do curso por Google Cloud
Exploring and Preparing your Data with BigQuery
650 classificações
Google Cloud
650 classificações
Curso 1 de 4 em Specialization From Data to Insights with Google Cloud Platform
Na lição
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
Conheça os instrutores
Google Cloud Training
Let's practice now with some challenging SQL questions,
and this is going to be our walk through the solutions.
So, if you have not tried the challenge questions already,
go ahead and give us a go and then tune back into this video and give it a watch.
So, if you are a SQL ninja,
and you picked up a lot in this course already or you had some background,
I'm going to throw a little bit more deeper SQL questions for you to tackle.
The first and foremost,
I want to copy and paste this in from our question list.
Here's the challenge. Again, block comment with command Windows key and forward slash.
For products with over 1,000 units that have been added to cart to interact with,
that are not frisbees,
how many distinct times is a product part of an order,
either a completed order or not?
How many total units of that products?
Like product quantity or part of those orders,
and orders can be complete or incomplete that's fine.
Then, which product had the highest conversion rate?
Again, for conversion here means they've added to
cart or have gotten through the checkout process.
So, we have our dataset over here on the left.
We are going to pull the sessions.
Now, a couple of different things we want here,
we know we don't want frisbees,
so let's tackle the easy one first.
Previewing the data, let's go ahead and again find the product name,
product name is here, just so I don't make any typos.
Select, I'm going to have a product name from our particular data set,
again back tick for standard SQL.
Make sure that you have standard SQL enabled with
a hashtag at the top and/or the checkbox in show options,
dated insights in the e-commerce data set,
and here we have all sessions.
So, you have product names,
and we do not want any frisbees.
So, let's do a list of product names, we'll group them all together,
and make sure that we find a frisbee and make sure that we exclude it,
and order by alphabetical A to Z.
So, that is going to be ascending which is fine.
So, this is going to be all, I think it's 600 and some products that we have, 633.
So, I think there was a dog Frisbee through this.
So, there's dog Frisbee right here,
we don't want that to be included.
How do we filter things out?
Well, filtering rows is down in the WHERE clause.
Now, I could do this,
and where it's not equal to the dog Frisbee,
but what about this quote dog Frisbee?
I could hardcode it, but best to use as you saw the LIKE operator.
So, if I wanted the frisbees,
it could be something like this,
but if I wanted nothing, not like frisbee.
So, do you think this is going to work?
No. Because you forgot your wildcard operators.
Any amount of characters that come before,
any amount of characters that come after 633 products,
how many do you think we'll get now?
Do you think we'll get 631 or do you think we'll
get where I need to have what we're actually filtering on?
Where the product name is not like frisbee?
So, what do you think 633, less, more,
greater or equal to?
So, this is the first insight that it is case sensitive.
So, I have a lowercase f here because I like tricking you guys.
So, one of the things that you can do is wrap this in a lower function,
which basically means passing all the product names,
but first convert all the characters to lowercase and then match on this frisbee.
So, now will see how products this excludes,
making sure that frisbee is no longer there.
So, 631 this is two dog frisbees are out. So, that's great.
We have confirmed that this works and now let's see what ultimately we want to answer.
We need to get a bunch of information on products.
So, first I'm going to bring
our assumption of every row being a view on a particular product,
and in the sessions there should be a field,
as we've been working with so far for the quantity that was ordered.
As you can see product quantity right there,
so let's add in the product quantity,
which indicates to us whether it was part of an incomplete.
When I say an incomplete order that means it was added to the cart,
but the person did not checkout yet.
We'll call it an abandoned cart or it's part of an order that
has actually gone through completion and has revenue associated with it.
So, we're going to count,
and again we're not doing the sum here.
We are going to count that as
potential orders or order complete or incomplete orders whenever you want to call it,
and that's just going to give us again,
if this is a 10 right here,
that's going to count once.
Let's see, we also want to include how many were ordered,
how many non-frisbees were ordered,
and that's going to be the quantity of products that were added.
Now let's see, we have the product name associated with it if we're doing aggregation,
we need to make sure we aggregate on the non aggregating fields.
All right. Let's see if we can't run this and see if it gets us a little bit closer,
we also want to adjust the top ten,
so let's just put our limit there now.
So, the results panel doesn't get too cluttered.
One selected on that, it'll give us 10 records that aren't frisbees,
we're getting a little bit closer now.
So you've the views, we have the time it was added to
an order and we've got a product the amount that was added,
but we have some lower numbers here,
and so if you remember the first part of the query was.
for products with over a thousand units that have been out of the currency.
Units means we need to filter by the quantity product added.
Now, I can't do this in the where clause because
it's an aggregation and I can't use aliases in the where clause.
So, if you're filtering on an aggregation,
immediately you want to think about using the lesser-known SQL clause of having,
and you can use aliases here since that's executed after the initial filtering happens.
Where quantity product added,
I think we want greater than a thousand.
So let's see if that changes our results.
So, all the rest of the ones should drop off except for
maybe that foam can and bottle cooler we will see. This is the minimum.
Again, there's no orderings,
it's kind of choosing a random result here.
So, we want to order by the ones have the highest conversion rate first.
So, conversion rate here is going to be defined as
the amount of views that the product has,
divided by or we want the total amount of times the product was added to an order.
So, this is going to be potential orders divided by the total product views,
and that's going to be again, you have to define
what conversion rate we want to add a comment there.
Now, if I were to run this, do you think is going to work?
And as you see, boom,
these fields don't exist yet from the dataset that you're querying from.
So, if you've used subqueries before
comfortable expressions you welcome to break this out into two queries,
but since we have not covered that yet,
we're gonna do it in the old-fashioned way,
which is specify those fields or the calculated fields here or again.
So, potential orders is actually just going to be this,
and then the total product views and I'll get a percentage of frowny views,
what percentage of those people actually added it to a cart or an environ checked out?
So, we have Frisbee,
we've got our grouping and then we have this,
let's see if that gets us a conversion rate.
So, top 10, great we have a conversion rate here,
these are really low conversion rates cc out of the one percent conversion rate.
So, this is how many times it's been added to an order,
so you can see it's 155 divided by that,
and so let's see if we can't get the highest ones on top.
How you would do that?
Again, since you can use an alias and the order by,
I think I just call it conversion rate,
making liberally used to copy and paste.
Highest to lowest is descending,
this should be our query.
Let's run this and see what non frisbee high converting products that we have.
This is as close to real data too,
so hopefully it should make sense from our perspective. Wow, alright.
So, we have a people really like their Google 25 ounce clear stainless steel bottle.
So, a little bit more than a third of the times that it's been viewed,
it has been added to a cart.
Again, it could have been added,
and when it's added to a cart by the way,
it's normally more than one unit that's actually been added.
But we just want to make sure that they clicked that Add to Cart button
regardless of how many that are actually adding into their cart.
So, that is if we remove the where clause filter just to humor you,
let's see if the dog Frisbee show up in the high conversion or at least the top there.
Yeah, they do. So, a third of the time we are little about 30 percent.
People like really that see the dog Frisbee really wanted dog Frisbee.
Again, keep in mind conversion right here we're just defining as it's part of
an incomplete order like an Add to Cart that's been abandoned or a completed order.
People love those dog frisbees.
So a quick recap of that first one.
You get familiar with filtering out with aggregations using or having
your filtering out a string wildcard match using the not like,
and you're creating a calculated field there.
So lots of different great things that you can practice for basics SQL.
Explore nosso catálogo
Registre-se gratuitamente e obtenha recomendações, atualizações e ofertas personalizadas.
O Coursera proporciona acesso universal à melhor educação do mundo fazendo parcerias com as melhores universidades e organizações para oferecer cursos on-line.
© 2019 Coursera Inc. Todos os direitos reservados.
