Overview

BigQuery ML enables users to create and execute machine learning models in BigQuery using SQL queries. The goal is to democratise machine learning by enabling SQL practitioners to build models using their existing tools and to increase development speed by eliminating the need for data movement.

The ecommerce dataset has millions of Google Analytics records for the Google Merchandise Store loaded into BigQuery, which you will use in this lab to create a model that predicts whether a visitor will make a transaction.

Objectives

In this lab, you learn how to perform the following tasks:

• Create a machine learning model in BigQuery
• Evaluate a machine learning model in BigQuery
• Use a machine learning model in BigQuery

Setup and requirements

For each lab, you get a new Google Cloud project and set of resources for a fixed time at no cost.

1. Sign in to Qwiklabs using an incognito window.

2. Note the lab's access time (for example, 1:15:00), and make sure you can finish within that time.
   There is no pause feature. You can restart if needed, but you have to start at the beginning.

3. When ready, click Start lab.

4. Note your lab credentials (Username and Password). You will use them to sign in to the Google Cloud Console.

5. Click Open Google Console.

6. Click Use another account and copy/paste credentials for this lab into the prompts.
   If you use other credentials, you'll receive errors or incur charges.

7. Accept the terms and skip the recovery resource page.

Open BigQuery Console

1. In the Google Cloud Console, on the Navigation menu , click BigQuery.
   The Welcome to BigQuery in the Cloud Console dialog opens. This dialog provides a link to the quickstart guide and lists UI updates.

2. Click Done to close the dialog.

Task 1. Create a dataset

In this task you create a BigQuery Dataset that will be used throughout the lab.

1. In the left pane under the Explorer section, click on View actions icon next to your BigQuery project ID (qwiklabs-gcp-xxxx).

2. Select Create dataset.

The Create dataset dialog opens.

3. Set the Dataset ID to bqml_lab, leave all other options at their default values.

4. Click CREATE DATASET.

Task 2. Explore the data

In this task you add a query to the query editor and examine the response. The data you use in this lab sits in the bigquery-public-data project, that is available to all.

1. Add the query to the query EDITOR box, and then click the RUN button:

#standardSQL SELECT IF(totals.transactions IS NULL, 0, 1) AS label, IFNULL(device.operatingSystem, "") AS os, device.isMobile AS is_mobile, IFNULL(geoNetwork.country, "") AS country, IFNULL(totals.pageviews, 0) AS pageviews FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*` WHERE _TABLE_SUFFIX BETWEEN '20160801' AND '20170631' LIMIT 10000;

The data tables have a lot of columns, but very few of them are of interest to you when creating the ML model. Here the visitor's device's operating system is used, whether said device is a mobile device, the visitor's country and the number of page views will be used as the criteria for whether a transaction has been made. In this case, label is what you're trying to fit to (or predict).

This data will be the training data for the ML model you create. The training data is being limited to those collected from 1 August 2016 to 31 June 2017. This is done to save the last month of data for "prediction". It is further limited to 10,000 data points to save some time.

2. Click on the Save button.

3. In the dropdown menu, select Save View to save this query as a view.

4. In the Save view dialog box, specify the following, and leave the remaining settings as their defaults:

Property	Value
Dataset ID*	bqml_lab
Table name	training_data

5. Click Save.

Task 3. Create a model

In this task you create a model to predict whether a visitor will make a transaction.

1. Replace the query with the following:

#standardSQL CREATE OR REPLACE MODEL `bqml_lab.sample_model` OPTIONS(model_type='logistic_reg') AS SELECT * from `bqml_lab.training_data`;

2. Click Run.

In this case, bqml_lab is the name of the dataset, sample_model is the name of the model, training_data is the transactions data you looked at in the previous task. The model type specified is binary logistic regression.

Running the CREATE MODEL command creates a Query Job that will run asynchronously so you can, for example, close or refresh the BigQuery UI window.

(Optional) Model information & training statistics

If interested, you can get information about the model.

1. In the Explorer pane, click the arrow to the left of the BigQuery project ID (qwiklabs-gcp-xxxx).

2. Expand the bqml_lab dataset on the left-hand menu.

3. Expand the Models dataset and then click the sample_model dataset in the UI.

4. Click Details. You should find some basic model info and training options used to produce the model.

5. Click TRAINING, and then in the View as option, click the Table radio button. You should see a table similar to this:

Task 4. Evaluate the model

In this task you evaluate the Model.

1. Replace the query with the following:

#standardSQL SELECT * FROM ml.EVALUATE(MODEL `bqml_lab.sample_model`);

2. Click Run.

In this query, you use the ml.EVALUATE function to evaluate the predicted values against the actual data, and it shares some metrics of how the model performed. You should see a table similar to this:

Task 5. Use the model

In this task you use the model to try and predict the number of transactions made by visitors of each country.

1. Click Compose new query.

2. Copy the following query into the Editor pane:

#standardSQL SELECT IF(totals.transactions IS NULL, 0, 1) AS label, IFNULL(device.operatingSystem, "") AS os, device.isMobile AS is_mobile, IFNULL(geoNetwork.country, "") AS country, IFNULL(totals.pageviews, 0) AS pageviews, fullVisitorId FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*` WHERE _TABLE_SUFFIX BETWEEN '20170701' AND '20170801';

3. Click Run.

You'll realize the SELECT and FROM portions of the query is similar to that used to generate training data. There is the additional fullVisitorId column which you will use for predicting transactions by individual user. The WHERE portion reflects the change in time frame (July 1 to August 1, 2017).

4. Click on the Save button.

5. In the dropdown menu, select Save View to save this query as a view.

6. In the Save view dialog box, specify the following, and leave the remaining settings as their defaults:

Property	Value
Dataset ID*	bqml_lab
Table name	july_data

7. Click Save.

8. Replace the query with the following:

#standardSQL SELECT country, SUM(predicted_label) as total_predicted_purchases FROM ml.PREDICT(MODEL `bqml_lab.sample_model`, ( SELECT * FROM `bqml_lab.july_data`)) GROUP BY country ORDER BY total_predicted_purchases DESC LIMIT 10;

With this query you will try to predict the number of transactions made by visitors of each country, sort the results, and select the top 10 countries by purchases:

9. Click Run.

In this query, you're using ml.PREDICT and the BQML portion of the query is wrapped with standard SQL commands. In this lab you're interested in the country and the sum of purchases for each country, so that's why SELECT, GROUP BY and ORDER BY. LIMIT is used to ensure you only get the top 10 results.

You should see a table similar to this:

10. Replace the query with the following:

Here is another example. This time you will try to predict the number of transactions each visitor makes, sort the results, and select the top 10 visitors by transactions:

#standardSQL SELECT fullVisitorId, SUM(predicted_label) as total_predicted_purchases FROM ml.PREDICT(MODEL `bqml_lab.sample_model`, ( SELECT * FROM `bqml_lab.july_data`)) GROUP BY fullVisitorId ORDER BY total_predicted_purchases DESC LIMIT 10;

11. Click Run.

You should see a table similar to this:

Review

In this lab you created, evaluated, and used a machine model in BigQuery.

End your lab

When you have completed your lab, click End Lab. Qwiklabs removes the resources you’ve used and cleans the account for you.

You will be given an opportunity to rate the lab experience. Select the applicable number of stars, type a comment, and then click Submit.

The number of stars indicates the following:

• 1 star = Very dissatisfied
• 2 stars = Dissatisfied
• 3 stars = Neutral
• 4 stars = Satisfied
• 5 stars = Very satisfied

You can close the dialog box if you don't want to provide feedback.

For feedback, suggestions, or corrections, please use the Support tab.

Copyright 2022 Google LLC All rights reserved. Google and the Google logo are trademarks of Google LLC. All other company and product names may be trademarks of the respective companies with which they are associated.