arrow_back

Predict Soccer Match Outcomes with BigQuery ML: Challenge Lab

Join Sign in

Predict Soccer Match Outcomes with BigQuery ML: Challenge Lab

1 hour 30 minutes 3 Credits

GSP374

Google Cloud selp-paced labs logo

Overview

In a challenge lab you’re given a scenario and a set of tasks. Instead of following step-by-step instructions, you will use the skills learned from the labs in the quest to figure out how to complete the tasks on your own! An automated scoring system (shown on this page) will provide feedback on whether you have completed your tasks correctly.

When you take a challenge lab, you will not be taught new Google Cloud concepts. You are expected to extend your learned skills, like changing default values and reading and researching error messages to fix your own mistakes.

To score 100% you must successfully complete all tasks within the time period!

This lab is recommended for students who have enrolled in the Predict Soccer Match Outcomes with BigQuery ML skill badge quest. Are you ready for the challenge?

Topics tested:

  • Upload files from Cloud Storage into BigQuery tables using the Cloud Console

  • Write and execute queries that join information from multiple tables

  • Analyze soccer event data using various BigQuery features

  • Write functions in BigQuery to help with calculations to be performed on soccer shot data

  • Create and evaluate an expected goals model using BigQuery ML

  • Apply an expected goals model to make a prediction from new data using BigQuery ML

Setup and Requirements

Before you click the Start Lab button

Read these instructions. Labs are timed and you cannot pause them. The timer, which starts when you click Start Lab, shows how long Google Cloud resources will be made available to you.

This hands-on lab lets you do the lab activities yourself in a real cloud environment, not in a simulation or demo environment. It does so by giving you new, temporary credentials that you use to sign in and access Google Cloud for the duration of the lab.

To complete this lab, you need:

  • Access to a standard internet browser (Chrome browser recommended).
Note: Use an Incognito or private browser window to run this lab. This prevents any conflicts between your personal account and the Student account, which may cause extra charges incurred to your personal account.
  • Time to complete the lab---remember, once you start, you cannot pause a lab.
Note: If you already have your own personal Google Cloud account or project, do not use it for this lab to avoid extra charges to your account.

How to start your lab and sign in to the Google Cloud Console

  1. Click the Start Lab button. If you need to pay for the lab, a pop-up opens for you to select your payment method. On the left is the Lab Details panel with the following:

    • The Open Google Console button
    • Time remaining
    • The temporary credentials that you must use for this lab
    • Other information, if needed, to step through this lab
  2. Click Open Google Console. The lab spins up resources, and then opens another tab that shows the Sign in page.

    Tip: Arrange the tabs in separate windows, side-by-side.

    Note: If you see the Choose an account dialog, click Use Another Account.
  3. If necessary, copy the Username from the Lab Details panel and paste it into the Sign in dialog. Click Next.

  4. Copy the Password from the Lab Details panel and paste it into the Welcome dialog. Click Next.

    Important: You must use the credentials from the left panel. Do not use your Google Cloud Skills Boost credentials. Note: Using your own Google Cloud account for this lab may incur extra charges.
  5. Click through the subsequent pages:

    • Accept the terms and conditions.
    • Do not add recovery options or two-factor authentication (because this is a temporary account).
    • Do not sign up for free trials.

After a few moments, the Cloud Console opens in this tab.

Note: You can view the menu with a list of Google Cloud Products and Services by clicking the Navigation menu at the top-left. Navigation menu icon

Challenge scenario

Use BigQuery to load the data from the Cloud Storage bucket, write and execute queries in BigQuery, analyze soccer event data. Then use BigQuery ML to train an expected goals model on the soccer event data and evaluate the impressiveness of World Cup goals.

Task 1. Data ingestion

Load the tables created with JavaScript Object Notation (JSON) and CSV data format into the dataset using the following information:

Field Value
Source Cloud Storage
Select file from Cloud Storage bucket spls/bq-soccer-analytics/events.json
File format JSONL (Newline delimited JSON)
Table name
Schema Check the box marked Schema Auto detect

Load another table of soccer data format CSV into the dataset using the following information below:

Field Value
Source Cloud Storage
Select file from Cloud Storage bucket spls/bq-soccer-analytics/tags2name.csv
File format CSV
Table name
Schema Check the box marked Auto detect

Click Check my progress to verify the objective Check tables are created

Task 2. Analyze soccer data

Build a query that shows the success rate on penalty kicks by each player.

Points to consider:

  • Join the table with the players table to get player names from their IDs
  • Filter on penalty kicks
  • Group by player ID and player name
  • Player should attempt at least 5 penalty kicks
  • Order by penalty kick success rate
Note: tag 101 represents a goal using the table

Click Check my progress to verify the objective: Check penalty kick success rate

Task 3. Gain insight by analyzing soccer data

Create a new query to analyze shot distance. For shots, use (x, y) values from the positions field in the table.

Points to consider:

  • Calculate shot distance using the midpoint of the goal mouth (, ) as the ending location.
  • Calculate pass distance by x-coordinate and y-coordinate differences, then convert to estimated meters using the average dimensions of a soccer field ( x ).
  • Add an isGoal field by looking "inside" the tags field.
  • Filter the table to shots only.
  • Shot distance must be less than 50.
  • The final SELECT statement aggregates the number of shots, the number of goals and the percentage of goals from shots by distance rounded to the nearest meter.
Note: The approximate dimensions of a soccer field are used with the x-coordinate and y-coordinate distances as inputs to the distance formula.

Click Check my progress to verify the objective: Analyze shot distance

Task 4. Create a regression model using soccer data

Create some user-defined functions in BigQuery that help with shot distance and angle calculations, which help to prepare the soccer event data for eventual use in an ML model.

Calculate shot distance from (x,y) coordinates

Define a function for calculating the shot distance from (x,y) coordinates in the soccer dataset using the following code-blocks.

CREATE FUNCTION `{{{project_0.startup_script.shot_distance|shot distance to goal}}}`(x INT64, y INT64) RETURNS FLOAT64 AS ( /* Translate 0-100 (x,y) coordinate-based distances to absolute positions using "average" field dimensions of {{{project_0.startup_script.x_axis|X-axis length}}}x{{{project_0.startup_script.y_axis|Y-axis length}}} before combining in 2D dist calc */ SQRT( POW(({{{project_0.startup_script.x_goal_mouth|X-axis goal mouth length}}} - x) * {{{project_0.startup_script.x_axis|X-axis length}}}/100, 2) + POW(({{{project_0.startup_script.y_goal_mouth|Y-axis goal mouth length}}} - y) * {{{project_0.startup_script.y_axis|Y-axis length}}}/100, 2) ) );

Click Check my progress to verify the objective Calculate shot distance

Calculate shot angle from (x,y) coordinates

Define a function for calculating the shot angle from (x,y) coordinates in the soccer dataset using the following code-blocks.

CREATE FUNCTION `{{{project_0.startup_script.shot_angle|shot angle to goal}}}`(x INT64, y INT64) RETURNS FLOAT64 AS ( SAFE.ACOS( /* Have to translate 0-100 (x,y) coordinates to absolute positions using "average" field dimensions of {{{project_0.startup_script.x_axis|X-axis length}}}x{{{project_0.startup_script.y_axis|Y-axis length}}} before using in various distance calcs */ SAFE_DIVIDE( ( /* Squared distance between shot and 1 post, in meters */ (POW({{{project_0.startup_script.x_axis|X-axis length}}} - (x * {{{project_0.startup_script.x_axis|X-axis length}}}/100), 2) + POW({{{project_0.startup_script.y_half_length|Y-axis half}}} + (7.32/2) - (y * {{{project_0.startup_script.y_axis|Y-axis length}}}/100), 2)) + /* Squared distance between shot and other post, in meters */ (POW({{{project_0.startup_script.x_axis|X-axis length}}} - (x * {{{project_0.startup_script.x_axis|X-axis length}}}/100), 2) + POW({{{project_0.startup_script.y_half_length|Y-axis half}}} - (7.32/2) - (y * {{{project_0.startup_script.y_axis|Y-axis length}}}/100), 2)) - /* Squared length of goal opening, in meters */ POW(7.32, 2) ), (2 * /* Distance between shot and 1 post, in meters */ SQRT(POW({{{project_0.startup_script.x_axis|X-axis length}}} - (x * {{{project_0.startup_script.x_axis|X-axis length}}}/100), 2) + POW({{{project_0.startup_script.y_half_length|Y-axis half}}} + 7.32/2 - (y * {{{project_0.startup_script.y_axis|Y-axis length}}}/100), 2)) * /* Distance between shot and other post, in meters */ SQRT(POW({{{project_0.startup_script.x_axis|X-axis length}}} - (x * {{{project_0.startup_script.x_axis|X-axis length}}}/100), 2) + POW({{{project_0.startup_script.y_half_length|Y-axis half}}} - 7.32/2 - (y * {{{project_0.startup_script.y_axis|Y-axis length}}}/100), 2)) ) ) /* Translate radians to degrees */ ) * 180 / ACOS(-1) ) ;

Click Check my progress to verify the objective Calculate shot angle

Create an expected goals model using BigQuery ML

Use BigQuery ML to create and execute a machine learning model in BigQuery using standard SQL queries. In this case, you build an expected goals model from the soccer event data to predict the likelihood of a shot going in for a goal given its type, distance, and angle.

Expected goals models are commonly used in soccer analytics to measure the quality of shots and finishing/saving ability given shot quality, and they have a variety of applications in both retrospective match analysis and making projections.

Points to consider:

  • The top section will be the actual model creation code, specify the type of model and label for the outcome variable.
  • 101 is a known Tag for 'goals' from the goals table.
  • The SELECT statement aggregates isGoal outcome variable along with features of interest from the event data, shot distance, and angle calculated using the user-defined functions defined in the previous step.
  • Join enables the determination of which competition each shot came from.
  • Filter out World Cup matches for model fitting purposes and include both "open play" & free kick shots (including penalties).

Click Check my progress to verify the objective Create BigQuery logistic regression model

Once the model is done training - look for a "Query complete" notification in the Query results section - click Go to model at the far right next to the message about model creation.

This opens up a new tab that has information about the model that was just trained.

Click to EVALUATION tab and look at the metrics, particularly Log loss and ROC AUC under Aggregate Metrics.

Task 5. Make predictions from new data with the BigQuery model

Now that you've fit an expected goals model of reasonable accuracy and explainability, you can apply it to "new" data - in this case, the 2018 World Cup (which was left out of the model fitting).

The logistic regression model created in the previous step is used to assess the difficulty of each shot and goal in that competition, enabling the identification of the most "impressive" goals in the tournament.

Get probabilities for all shots in the 2018 World Cup

Use BigQuery ML's prediction functionality with the logistic regression model fit in the previous step to look at the probability of each shot scoring in the World Cup.

Points to consider:

  • The top section is the actual model prediction code, specifying the type of model.
  • The SELECT statement aggregates isGoal outcome variable along with features of interest from the event data, shot distance, and angle calculated using the user-defined functions defined in the previous step.
  • Join enables the determination of which competition each shot came from.
  • Look only at World Cup matches for model predictions and include both "open play" and free kick shots (including penalties).

Click Check my progress to verify the objective Make predictions from the model

Congratulations!

You have completed the Predict Soccer Match Outcomes with BigQuery ML: Challenge Lab by creating machine learning models with soccer data. You:

  • Uploaded files stored in Cloud Storage into BigQuery tables.
  • Wrote and executed queries to analyze data in BigQuery tables.
  • Created user-defined functions in BigQuery to calculate shot distance and shot angle.
  • Used BigQuery ML to build an expected goals model.
  • Used BigQuery ML's prediction functionality on "new" data from the 2018 World Cup to determine some of the most impressive goals in the tournament.

Predict Soccer Match Outcomes with BigQuery ML skill badge

Earn Your Next Skill Badge

This self-paced lab is part of the Predict Soccer Match Outcomes with BigQuery ML skill badge quest. Completing this skill badge quest earns you the badge above, to recognize your achievement. Share your badge on your resume and social platforms, and announce your accomplishment using #GoogleCloudBadge.

Manual Last Updated June 17, 2022

Lab Last Tested April 4, 2022

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.