GSP851

The data used in this lab comes from the following sources:

• Pappalardo et al., (2019) A public data set of spatio-temporal match events in soccer competitions, Nature Scientific Data 6:236, https://www.nature.com/articles/s41597-019-0247-7
• Pappalardo et al. (2019) PlayerRank: Data-driven Performance Evaluation and Player Ranking in Soccer via a Machine Learning Approach. ACM Transactions on Intelligent Systems and Technologies (TIST) 10, 5, Article 59 (September 2019), 27 pages. DOI: https://doi.org/10.1145/3343172

Objectives

In this lab, you will learn how to:

• Write functions in BigQuery to help with calculations to be performed on soccer shot data.
• Create and evaluate "expected goals" models using BigQuery ML.
• Apply an expected goals model to "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 are made available to you.

This hands-on lab lets you do the lab activities in a real cloud environment, not in a simulation or demo environment. It does so by giving you new, temporary credentials 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 (recommended) or private browser window to run this lab. This prevents 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: Use only the student account for this lab. If you use a different Google Cloud account, you may incur charges to that 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 dialog opens for you to select your payment method. On the left is the Lab Details pane with the following:

   • The Open Google Cloud 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 Cloud console (or right-click and select Open Link in Incognito Window if you are running the Chrome browser).

   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 below and paste it into the Sign in dialog.

   {{{user_0.username | "Username"}}}

   You can also find the Username in the Lab Details pane.

4. Click Next.

5. Copy the Password below and paste it into the Welcome dialog.

   {{{user_0.password | "Password"}}}

   You can also find the Password in the Lab Details pane.

6. Click Next.

   Important: You must use the credentials the lab provides you. Do not use your Google Cloud account credentials. Note: Using your own Google Cloud account for this lab may incur extra charges.

7. 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 Google Cloud console opens in this tab.

Note: To access Google Cloud products and services, click the Navigation menu or type the service or product name in the Search field.

Task 1. Open BigQuery

The BigQuery console provides an interface to query tables, including public datasets offered by BigQuery.

1. In the Cloud Console, from the Navigation menu select BigQuery.

The Welcome to BigQuery in the Cloud Console message box opens. This message box provides a link to the quickstart guide and the release notes.

2. Click Done.

The BigQuery console opens.

Note: The process for creating the dataset and tables is taught in the BigQuery Soccer Data Ingestion lab. In this lab the focus is on learning how to query the information.

Once the tables are created the display will be similar to below:

In this section the BigQuery interface was used to access the console. The console provides a convenient way to add information to a dataset. BigQuery uses tables to represent data in a structured way.

In the next section learn more about creating functions in BigQuery.

Task 2. Calculate shot distance and shot angle

In this section, you will create some user-defined functions in BigQuery that help with shot distance and angle calculations, helping to prepare the soccer event data for eventual use in an ML model.

Calculate shot distance from (x,y) coordinates

To motivate the need for the function, start by looking at the positions field in the events table a bit more - this is a repeated field that contains 1 or more (x, y) pairs per event.

Per Wyscout, a leading data company in the soccer industry that provided this data, these represent the origin and (if applicable) destination positions associated with the event, on a 0-100 scale representing the percentage of the field from the perspective of the attacking team. See the screenshot of the table below showing positions corresponding to a few different event types for a few example events.

The first (x, y) pair in positions for events that are shots represent the location on the field from which the shot originated.

Note: Calculating the distance of a shot from a given location to the goal involves using the midpoint of the goal mouth (100, 50) as the ending location, the known average dimensions of a soccer field (105x68, per Wikipedia; there is no standard field size) and the 2-dimensional distance formula.

1. Copy and paste the following code into the query Editor:

CREATE FUNCTION `soccer.GetShotDistanceToGoal`(x INT64, y INT64) RETURNS FLOAT64 AS ( /* Translate 0-100 (x,y) coordinate-based distances to absolute positions using "average" field dimensions of 105x68 before combining in 2D dist calc */ SQRT( POW((100 - x) * 105/100, 2) + POW((50 - y) * 68/100, 2) ) );

This specifies the name of the function, the inputs and their types (2 integers), the output type (a float), and the actual logic implementing the 2-dimensional distance formula on the scaled x- and y-coordinate distances.

2. Click Run.

The Query results section should display a message saying This statement created a new function named: .soccer.GetShotDistanceToGoal.

Click Check my progress to verify the objective

Check the query has been run

Calculate shot angle from (x,y) coordinates

The general process here is similar to the shot distance calculation, but applied to shot angle. In this case, the angle calculated is the one made by the location of the shot and the goal line, as shown below (image credit to Ian Dragulet).

Larger angles arise from being close to the goal and in the center, so this is somewhat correlated with the distance calculation performed above. The shot angle calculations involve using BigQuery's trigonometric functions on the (x, y) data.

1. In the query Editor, click "+" (SQL query).
2. Add the following code into the query Editor. This creates a shot angle function like the shot distance function above with 2 integer inputs and a floating point output, but with a more detailed trigonometric calculation using the Law of Cosines to get the angle to the goal:

CREATE FUNCTION `soccer.GetShotAngleToGoal`(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 105x68 before using in various distance calcs */ SAFE_DIVIDE( ( /* Squared distance between shot and 1 post, in meters */ (POW(105 - (x * 105/100), 2) + POW(34 + (7.32/2) - (y * 68/100), 2)) + /* Squared distance between shot and other post, in meters */ (POW(105 - (x * 105/100), 2) + POW(34 - (7.32/2) - (y * 68/100), 2)) - /* Squared length of goal opening, in meters */ POW(7.32, 2) ), (2 * /* Distance between shot and 1 post, in meters */ SQRT(POW(105 - (x * 105/100), 2) + POW(34 + 7.32/2 - (y * 68/100), 2)) * /* Distance between shot and other post, in meters */ SQRT(POW(105 - (x * 105/100), 2) + POW(34 - 7.32/2 - (y * 68/100), 2)) ) ) /* Translate radians to degrees */ ) * 180 / ACOS(-1) ) ;

3. Click Run.
4. The Query results section should display a message saying This statement created a new function named .soccer.GetShotAngleToGoal.

Click Check my progress to verify the objective Check the query has been run

In this section you used BigQuery to make functions to calculate shot distance and angle.

In the next section learn how to build a model to calculate expected goals using the distance and angle of each shot.

Task 3. Create expected goals models using BigQuery ML

In this section, you will use BigQuery ML to create and execute machine learning models in BigQuery using standard SQL queries. In this case, you will build expected goals models on the soccer event data to predict the likelihood of a shot going in for a goal given its type, distance, and angle (these are known to be good predictors of the chance of a goal, as demonstrated in the BigQuery Soccer Data Analytical Insight lab).

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.

Fitting a logistic regression model for expected goals

First, you will fit a logistic regression model to the data.

1. In the query Editor, click "+" (SQL query).
2. Add the following code into the query Editor.

CREATE MODEL `soccer.xg_logistic_reg_model` OPTIONS( model_type = 'LOGISTIC_REG', input_label_cols = ['isGoal'] ) AS SELECT Events.subEventName AS shotType, /* 101 is known Tag for 'goals' from goals table */ (101 IN UNNEST(Events.tags.id)) AS isGoal, `soccer.GetShotDistanceToGoal`(Events.positions[ORDINAL(1)].x, Events.positions[ORDINAL(1)].y) AS shotDistance, `soccer.GetShotAngleToGoal`(Events.positions[ORDINAL(1)].x, Events.positions[ORDINAL(1)].y) AS shotAngle FROM `soccer.events` Events LEFT JOIN `soccer.matches` Matches ON Events.matchId = Matches.wyId LEFT JOIN `soccer.competitions` Competitions ON Matches.competitionId = Competitions.wyId WHERE /* Filter out World Cup matches for model fitting purposes */ Competitions.name != 'World Cup' AND /* Includes both "open play" & free kick shots (including penalties) */ ( eventName = 'Shot' OR (eventName = 'Free Kick' AND subEventName IN ('Free kick shot', 'Penalty')) ) ;

The top section is the actual model generation code, specifying the type of model and label for the outcome variable. The SELECT statement creates the isGoal outcome variable along with features of interest from the event data, including the shot distance and angle calculated using the functions created in the previous step. The joins enable the determination of which competition each shot came from, which is employed to filter out the World Cup data from this modeling (saved for use later on after the model is fit).

3. Click Run.

The Query results section should display a message saying This statement will create a new model named: :soccer.xg_logistic_reg_model. Depending on the type of model, this may take several hours to complete. In this case, it should only take a minute or two to finish.

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

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

5. Click to the Evaluation tab and take a look at some of the metrics, particularly "Log loss" and "ROC AUC" under Aggregate Metrics.

Note: Results may vary slightly based on the inherent randomness in the model fitting procedure.

Click Check my progress to verify the objective

Check the query has been run

Since the primary interest lies in the accuracy of goal probabilities from the model (not explicitly accuracy in terms of predicting 0s and 1s at a given threshold), log loss and ROC AUC are good metrics to focus on. The numbers themselves are most useful in comparing different models, which is done in a later section.

Understanding the Logistic regression model fit

Now that you've fit the logistic regression model, you can use BigQuery ML's weights functionality to see the model coefficients for each predictor.

1. In the query EDITOR, click "+" (SQL query).

2. Copy and paste the following code into the query EDITOR. This is a simple call to the ML.WEIGHTS function to the logistic regression model that you fit in the previous step:

SELECT * FROM ML.WEIGHTS(MODEL soccer.xg_logistic_reg_model) ;

3. Click Run. The results are displayed below the query window.

There are weights for each input - a single numerical weight for the continuous features, 1 value per potential category for the categorical features - that correspond to coefficients in a logistic regression model. It's sometimes dangerous to interpret logistic regression model coefficients directly because of correlation among the predictors, among other things.

At minimum, the sign of these seem reasonable based on general subject matter knowledge of soccer:

• Penalty kicks have a much higher chance of success than other shot types
• Shots at further distance have less chance of success
• Shots with greater angles to the goal have higher chance of success

Seeing these results can provide some confidence that the model is doing something logical.

Create a boosted tree model for expected goals

Next, you will fit a boosted tree model to the data using BigQuery ML's implementation of XGBoost, and compare the results to the previously fit logistic regression model. In theory, boosted trees can be more accurate because of their ability to take into account nonlinear relationships between features and the outcome as well as interactions among features.

1. In the query Editor, click "+" (SQL query).
2. Copy and paste the following code into the query Editor:

CREATE MODEL `soccer.xg_boosted_tree_model` OPTIONS( model_type = 'BOOSTED_TREE_CLASSIFIER', input_label_cols = ['isGoal'] ) AS SELECT Events.subEventName AS shotType, /* 101 is known Tag for 'goals' from goals table */ (101 IN UNNEST(Events.tags.id)) AS isGoal, `soccer.GetShotDistanceToGoal`(Events.positions[ORDINAL(1)].x, Events.positions[ORDINAL(1)].y) AS shotDistance, `soccer.GetShotAngleToGoal`(Events.positions[ORDINAL(1)].x, Events.positions[ORDINAL(1)].y) AS shotAngle FROM `soccer.events` Events LEFT JOIN `soccer.matches` Matches ON Events.matchId = Matches.wyId LEFT JOIN `soccer.competitions` Competitions ON Matches.competitionId = Competitions.wyId WHERE /* Filter out World Cup matches for model fitting purposes */ Competitions.name != 'World Cup' AND /* Includes both "open play" & free kick shots (including penalties) */ ( eventName = 'Shot' OR (eventName = 'Free Kick' AND subEventName IN ('Free kick shot', 'Penalty')) ) ;

The SQL statement is identical to the one to fit the logistic regression above, except for changes in the model type to pick a boosted tree classifier and the name of the model object created.

3. Click Run. The Query results section should display a message saying This statement will create a new model named: :soccer.xg_boosted_tree_model. Depending on the type of model, this may take several hours to complete. In this case, it should take several minutes to finish.

Note: Training the model can take 15-20 minutes to complete.

While you wait, check out this overview of BigQuery ML.

Once the model has been trained, verify the step has been completed successfully.

4. Once the model is done training, look for a "Query complete" notification in the Query results section
5. Click on Go to model at the far right next to the message about model creation.

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

6. Click to the Evaluation tab and take a look at some of the metrics, particularly "Log loss" and "ROC AUC" under Aggregate Metrics.

Note: Results may vary slightly based on the inherent randomness in the model fitting procedure.

The log loss and ROC AUC of the boosted tree model is pretty similar to that from the logistic regression model, but slightly worse (higher log loss and lower ROC AUC).

Click Check my progress to verify the objective

Check the query has been run

In this section BigQuery was used to fit machine learning models using soccer data. An expected goals model can be created in a variety of ways.

In the next section learn how to apply an expected goals model to new data using BigQuery ML.

Task 4. Apply an expected goals model to new data

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 will be used to assess the difficulty of each shot and goal in that competition, enabling identification of the most "impressive" goals in the tournament.

Get probabilities for all shots in 2018 World Cup

You can use BigQuery ML's prediction functionality with the logistic regression model fit in the previous step (or even the boosted tree model, if you prefer) to look at the probability of each shot scoring or not in the World Cup.

1. In the query Editor, click "+" (SQL query).
2. Copy and paste the following code into the query Editor:

SELECT * FROM ML.PREDICT( MODEL `soccer.xg_logistic_reg_model`, ( SELECT Events.subEventName AS shotType, /* 101 is known Tag for 'goals' from goals table */ (101 IN UNNEST(Events.tags.id)) AS isGoal, `soccer.GetShotDistanceToGoal`(Events.positions[ORDINAL(1)].x, Events.positions[ORDINAL(1)].y) AS shotDistance, `soccer.GetShotAngleToGoal`(Events.positions[ORDINAL(1)].x, Events.positions[ORDINAL(1)].y) AS shotAngle FROM `soccer.events` Events LEFT JOIN `soccer.matches` Matches ON Events.matchId = Matches.wyId LEFT JOIN `soccer.competitions` Competitions ON Matches.competitionId = Competitions.wyId WHERE /* Look only at World Cup matches for model predictions */ Competitions.name = 'World Cup' AND /* Includes both "open play" & free kick shots (including penalties) */ ( eventName = 'Shot' OR (eventName = 'Free Kick' AND subEventName IN ('Free kick shot', 'Penalty')) ) ) )

This calls the ML.PREDICT function on the logistic regression model that was fit previously.

The SELECT statement creates the same fields on all shots from the events data as the ones used to fit the model, but this time it filters to only World Cup matches (instead of leaving them out) since those are the shots the model is to be applied to.

3. Click Run. The results are displayed below the query window.

The output shows a couple of prediction-related fields along with the original fields in that data of shot type, distance, angle, and success:

• predicted_isGoal, a binary prediction of whether the shot would result in a goal or not
• predicted_isGoal_probs, an array of (label, prob) pairs that shows the probability of each shot being successful and not from the model

Identify most unlikely goals using model probabilities

Building on the previous section that yielded probabilities for all shots in the 2018 World Cup, use BigQuery to extract the probability of each goal, then sort from lowest to highest to see the most unlikely goals scored in the World Cup based on the factors in the model.

Theoretically, this should yield a list of some of the most impressive goals in the tournament, at least based on the factors in the model (distance, angle, etc.).

1. In the query Editor, click "+" (SQL query).
2. Add the following code into the query Editor:

SELECT predicted_isGoal_probs[ORDINAL(1)].prob AS predictedGoalProb, * EXCEPT (predicted_isGoal, predicted_isGoal_probs), FROM ML.PREDICT( MODEL `soccer.xg_logistic_reg_model`, ( SELECT Events.playerId, (Players.firstName || ' ' || Players.lastName) AS playerName, Teams.name AS teamName, CAST(Matches.dateutc AS DATE) AS matchDate, Matches.label AS match, /* Convert match period and event seconds to minute of match */ CAST((CASE WHEN Events.matchPeriod = '1H' THEN 0 WHEN Events.matchPeriod = '2H' THEN 45 WHEN Events.matchPeriod = 'E1' THEN 90 WHEN Events.matchPeriod = 'E2' THEN 105 ELSE 120 END) + CEILING(Events.eventSec / 60) AS INT64) AS matchMinute, Events.subEventName AS shotType, /* 101 is known Tag for 'goals' from goals table */ (101 IN UNNEST(Events.tags.id)) AS isGoal, `soccer.GetShotDistanceToGoal`(Events.positions[ORDINAL(1)].x, Events.positions[ORDINAL(1)].y) AS shotDistance, `soccer.GetShotAngleToGoal`(Events.positions[ORDINAL(1)].x, Events.positions[ORDINAL(1)].y) AS shotAngle FROM `soccer.events` Events LEFT JOIN `soccer.matches` Matches ON Events.matchId = Matches.wyId LEFT JOIN `soccer.competitions` Competitions ON Matches.competitionId = Competitions.wyId LEFT JOIN `soccer.players` Players ON Events.playerId = Players.wyId LEFT JOIN `soccer.teams` Teams ON Events.teamId = Teams.wyId WHERE /* Look only at World Cup matches to apply model */ Competitions.name = 'World Cup' AND /* Includes both "open play" & free kick shots (but not penalties) */ ( eventName = 'Shot' OR (eventName = 'Free Kick' AND subEventName IN ('Free kick shot')) ) AND /* Filter only to goals scored */ (101 IN UNNEST(Events.tags.id)) ) ) ORDER BY predictedgoalProb

This builds on the ML.PREDICT function call in the previous section, filtering to only shots that were goals (excluding penalties, since the goal is to get the most "impressive" goals), extracting only the probability of a goal being scored from the predictions, and ordering by that.

A few fields are added in the SELECT statement from the events data and the other tables that can be joined to it to get other information of interest on each goal like the player and team who scored it, match date and information, and minute into the match of the goal.

3. Click Run. The results are displayed below the query window.

Click Check my progress to verify the objective

Check the query has been run

The top few lines show the 2018 World Cup goals that the model believed had the least likely chance of going in - all with fairly long shot distances (near 30 meters) and relatively tight angles (between 10° and 15°).

For some visual validation, you can watch the Antoine Griezmann goal that earned the top spot on this list - to be fair, it's more of an unlikely goal than an impressive one based on how it was played by the goalkeeper.

For more of a "wow" goal in line with what this analysis intends to uncover, check out this amazing shot by Korea Republic's Son Heung-min vs Mexico that came in second in the results.

In the next section test your understanding of what you have learned in this introduction to BigQuery ML.

Pop quiz

Test your understanding of BigQuery by completing the short quiz on the topics covered in this lab.

Congratulations!

You have created machine learning models with soccer data. You created user-defined functions in BigQuery to calculate shot distance and shot angle, then used BigQuery ML to build an expected goals model, and 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.

Google Cloud training and certification

...helps you make the most of Google Cloud technologies. Our classes include technical skills and best practices to help you get up to speed quickly and continue your learning journey. We offer fundamental to advanced level training, with on-demand, live, and virtual options to suit your busy schedule. Certifications help you validate and prove your skill and expertise in Google Cloud technologies.

Manual Last Updated January 30, 2025

Lab Last Tested January 30, 2025

Copyright 2025 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.