>>> 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.
Lab Walkthrough - Building Data Transformation Recipes with a UI
Loading...
From the course by Google Cloud
Exploring and Preparing your Data with BigQuery
499 ratings
Google Cloud
499 ratings
Course 1 of 4 in the Specialization From Data to Insights with Google Cloud Platform
From the lesson
Module 5: Cleaning and Transforming your Data
Understand the importance of creating high quality datasets and learn the tools that will help you transform your data
Meet the Instructors
Google Cloud Training
All right. That's the end of the exploration phase.
Now, it's actually starting to sort the time to change some of these recipes together.
So, the first thing that I want to do is drop columns that we're not going to be using.
So going back here, we took a look at a ton of these columns.
Let's go ahead and drop or delete item quantity.
We're going to delete item revenue.
I'm going to try to find visitor ID if it's here.
Another fast way to find these columns is in the columns view here.
So, if I search for- I'm not sure if I'm pulling it in.
Now, we don't pull in visitor ID but visitor- visitor ID,
not visit ID, but visitor ID has been deprecated. Let's see.
Those are the two that we're going to drop there.
Now, let's take a look at our recipe.
So, in the lower right-hand corner,
even without you knowing,
when you started to drop these columns,
it's creating this logic with the script that's
going to be part of this pipeline that we're going to build.
If you hover over the plus here,
you can add a new step but I just want to view the recipe.
If I click on Recipe.
You remember the product SKU that had the red quality bar?
It automatically saved.
Hey, make sure every time you convert the product SKU to string,
delete these two different columns.
You're already building a recipe together, and you don't even know it.
So, let's start adding in a little bit more sophistication here.
So, add a new step,
and there is a transformation.
This is a box by the way.
The transformation, you can start typing in anything if you imagine.
We're going to be doing concatenations, case statements,
pretty much anything that you think you can do in SQL even pretty advanced functions.
We'll going to be covering a- spoiler alert.
We're going to be covering un-nesting and arrays in
most of our advanced course, in the specialization.
But for now, we will settle for deduplication of all of the different rows.
We're going to add this in, and the great part is if you can see any duplicative rows,
we'll try to hunt some down.
It will highlight them for those that are actually affected.
So, we're going to remove completely duplicative rows from the dataset.
Also, the goal here is to filter for
an apparel manager who's only interested in apparel
and those items that actually have revenue associated with them.
So, let's look for a transaction revenue.
For here, one of the great things is
instead of doing new stuff and trying to find out the formula yourself,
there is a suggested mode where you can actually click on all the missing values.
If you click on that, it prompts you with a couple of different suggestions.
Let's say I like to use all the time,
and it says, you have a couple different options.
You clicked on this missing value and what you can do is a couple of different things.
Commonly, folks wanted to delete the rows which
is what we're going to do or you can just keep
those rows or you can create a new column and flag those if you want.
We actually want to filter this out,
so we're going to delete those rows where there is
no transaction, total transaction revenue.
So that it reduce our dataset from about 13,000 or 12,000 down to 3,000 so far.
So, watch down here.
We dropped two columns down to 30,
and now we want to keep
filtering and winnowing down this dataset to only the fields that we want.
So, let's take a look at "Type".
We only want to filter for type of page, ignoring the events.
We're not double counting any items there.
Let's take a look. I'm looking for the apparel product.
I've got the productSKU, ProductName.
Here is the ProductCategory.
Apparel is number one for me here.
I'm just going to go and click on that,
and filter only the rows that contains apparel.
As you're building this recipe,
the great thing about this sample that is loaded in,
you can see that we're down to 676 rows right here,
is it's filtering these live for you as well.
So you can see, a lot of the apparel products,
the product Variant is now no longer,
not set as dominant.
So, each of these different histograms updates as well.
So, it's a great way to explore your data,
even if you don't want to ultimately build a dataset like this.
You can still explore what are the most popular variants for products.
So, small, large, medium, XL,
2XL, six medium or 10 to 12.
So, a lot of different product variants for.
You can imagine pie for clothing.
Okay. So let's see.
We've got 676 rows.
That is good for us.
The last couple of things we need to do is create two new columns.
So, I'm really annoyed by the visitor ID and my lack of
a good field for the combination of full visitor ID in these sessions.
I'm going to create a new step. We're going
to merge these two together, concatenate them.
If you type in concat or merge,
it'll take you to the same place.
We're going to merge those two columns together.
Full visitor ID, start typing and it will autocomplete for you.
The other column we want is visitID.
You can see automatically as you're starting to type this in,
and it'll say, "hey,
I'm going to concatenate these two together,
do you want to have a delimiter for- I think it's single quotes.
Do you want to have a hyphen in between them or star or something like that?
You can specify those.
I'm going to keep it as just completely jammed together.
We'll call this unique_session_ID.
Great. So, we got a good field for that.
That'll be good for reporting later on when we're doing group bys and we don't
need to do the concatenation in SQL anymore.
So we've got that unique identifier.
Now, one of my,
you'll begin to start memorizing all of the different e-commerce action types here,
and you'll notice- here's a uninteresting quiz question for you.
Why do we only have e-commerce action types of five or six?
If you remember, just because you memorized it like me,
five or six corresponds to the check-outs.
Completed, purchase, I think it's number six.
Somewhere along the checkout process is number five.
But if you don't have those memorized, that's fine.
We're actually going to be creating a case statement here.
But to answer that quiz question, the only reason why you have those
two is because we proactively filter down
for only those who have a transaction value present.
That's why if it has been part of checkout,
you're not going to have any transaction associated with it.
So here, we're going to create a new step.
Now, if you start typing in case or if then or conditions that'll get you here,
and much like we did the case statement in one of
the challenge questions in the previous lab,
we are going to be building a case statement effectively through the UI here.
So, we don't want a single condition for an if/then/ else,
we actually want to specify a case statement
in the column that we want to specify that on,
its e-commerceAction_type and we have quite a few cases.
So, we have one case here.
I'm going to, it's nine in total including zero.
So, zero through eight.
Again, as a handy reference,
I'm going to flip over to the BigQuery schema,
and I'm going to find action type.
These are the nine that you'd be mapping inside of there.
So, let us start the mapping.
So, I'm going to add 1,2,3,4,5,6,7,8,9,
and it's exactly what you think of anything to do.
Case one is zero.
Case one, it's one. Case one, it's two.
You can do these.
I like putting in all the numbers first, all the different cases.
But essentially, we're going to map those to those string values.
So, we have that lookup table in there.
You won't have to memorize those.
The default value, if it's none of those,
we're going to have that just return a null value,
and when it give this the creative column name of eCommerceAction_label.
Now, we actually need to specify what those different values mean.
So for here, number one is, and again,
this is just copying and pasting from that list from the schema,
indicates that they've clicked to the product lists.
Number two is they've gone further than that.
They've actually looked at some product detail pages.
Number three is they've added the product of the cart.
So, pasting that in.
Number four, they've got it to the cart.
Taken a step backwards,
they've actually removed it from the cart. Sad face there.
Number five is going to be a checkout.
It's part of the checkout process.
Number six as they've gone through checkout,
and they've completed a purchase.
Number seven is the purchase has been refunded.
These are just static options for Google Analytics in general.
So, when you load your own personal Google Analytics dataset in there,
you'll be able to take these exact same options that you're doing here.
Of course, I missed one.
At the top is zero, which is unknown.
So, as I was typing these in,
if you've been watching carefully,
the right side panel has started to fill in here with our different options.
So, six corresponds to a completed purchase, five is checkout.
So, you can see that this isn't going to be our new,
this is a preview.
That doesn't mean it's created yet until you scroll down,
verify, everything looks good, and then click,
"Add that to the part of the recipe",
and then that gets promoted as a table that you can have here. All right.
Sorry. A column that you can have here.
So, 676 rows in our sample dataset.
Now, what if we want to- we're good with our recipe here?
Just quickly reviewing that,
the ninth different steps that we have here.
What are we going to- how do we actually kick this off on
our full sample or full raw dataset that's part
of our Quick Labs account that we set up in our eCommerce dataset?
Explore our Catalog
Join for free and get personalized recommendations, updates and offers.
Coursera provides universal access to the world’s best education,
partnering with top universities and organizations to offer courses online.
© 2018 Coursera Inc. All rights reserved.
