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 <<<
Lab Walkthrough - Logical Errors, Group Bys, Wildcard Filters
Loading...
Do curso por Google Cloud
Exploring and Preparing your Data with BigQuery
603 classificações
Google Cloud
603 classificações
Curso 1 de 4 em Specialization From Data to Insights with Google Cloud Platform
Na lição
Module 3: Exploring your Data with SQL
Learn how to query your data with the basics of SQL (Structured Query Language) and practice writing queries in BigQuery
Conheça os instrutores
Google Cloud Training
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.
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.
