arrow_back

BigQuery Soccer Data Analysis

Join Sign in

BigQuery Soccer Data Analysis

45 minutes 1 Credit

GSP849

Google Cloud selp-paced labs logo

Overview

In this lab you will learn more fundamentals of sports data science by writing and executing queries to query data stored in BigQuery tables. The emphasis of the lab is to illustrate how the database works and answer some interesting questions related to the following topics in soccer.

  • Most total goals scored
  • Most attempted passes
  • Best penalty success rate

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

In this lab, you will:

  • Query soccer match event data in BigQuery

  • Write and execute queries that join information from multiple tables

Open BigQuery

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

In the Cloud Console, from the Navigation menu select BigQuery:

BigQuery_menu.png

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.

Click Done.

The BigQuery console opens.

bq-console.png

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 this:

bq-tables-available.png

In the next section, begin to learn the fundamentals of creating queries in BigQuery.

Matches with the most goals

In this section, create a query that joins together multiple tables featuring soccer data. Based on the information available, you can perform some basic analytics such as the most total goals scored in a match by both teams (in a specific league).

  1. In the Query editor, click Compose new query.

  2. Add the following query to the query Editor.

SELECT date, label, (team1.score + team2.score) AS totalGoals FROM `soccer.matches` Matches LEFT JOIN `soccer.competitions` Competitions ON Matches.competitionId = Competitions.wyId WHERE status = 'Played' AND Competitions.name = 'Spanish first division' ORDER BY totalGoals DESC, date DESC

Here is what the query will do:

  • joins the matches table (which has final scores) with the competitions table.

  • filter down to "Spanish first division" matches only.

  • order by a calculated field that represents total goals in a match.

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

68e1db0f5cb46eba.png

Click Check my progress to verify the objective

Check the query has been run

In this section BigQuery was used to illustrate how to define a query that shows soccer information. The query creates a filter that displays specific information about matches from a specific league and enables the information to be categorized by a defined field.

Players with the most passes

In this section, create a query that joins together multiple tables featuring soccer data. Based on the information available, you can perform some basic analytics such as total passes by players.

  1. In the Query editor, click Compose new query.
  2. Add the following query into the query Editor.

This query:

  • joins the events table (which has a record of every pass) with the players table to get player names from their IDs

  • groups by player

  • counts the number of passes for each one

  • orders by the players with the most passes

SELECT playerId, (Players.firstName || ' ' || Players.lastName) AS playerName, COUNT(id) AS numPasses FROM `soccer.events` Events LEFT JOIN `soccer.players` Players ON Events.playerId = Players.wyId WHERE eventName = 'Pass' GROUP BY playerId, playerName ORDER BY numPasses DESC LIMIT 10
  1. Click Run. The results are displayed below the query window.

e3aa74060ce1c70a.png

Click Check my progress to verify the objective

Check the query has been run

In this section BigQuery was used to illustrate how to define a query that shows player information. The query creates a join that displays specific information about a playerId and enables the information to be categorized by a defined field.

In the next section learn more about the existing dataset and explore how it can be used to determine the penalty kick success rate of players.

Determine penalty kick success rate

In this section, create a query that joins together multiple tables featuring soccer data. Based on the information available, you can perform some analytics such as the success rate on penalty kicks by each player.

  1. In the Query editor, click Compose new query.

  2. Copy and paste the following query into the query Editor:

SELECT playerId, (Players.firstName || ' ' || Players.lastName) AS playerName, COUNT(id) AS numPKAtt, SUM(IF(101 IN UNNEST(tags.id), 1, 0)) AS numPKGoals, SAFE_DIVIDE( SUM(IF(101 IN UNNEST(tags.id), 1, 0)), COUNT(id) ) AS PKSuccessRate FROM `soccer.events` Events LEFT JOIN `soccer.players` Players ON Events.playerId = Players.wyId WHERE eventName = 'Free Kick' AND subEventName = 'Penalty' GROUP BY playerId, playerName HAVING numPkAtt >= 5 ORDER BY PKSuccessRate DESC, numPKAtt DESC

The query aggregates the number of penalty kick attempts and successful ones by player and filters to those with at least 5 penalty kick attempts before ordering by success rate.

The above query joins the events table, in this case filtered to only penalty kicks, with the players table to get player names from their IDs.

The tags field in the events table uses BigQuery's array functionality (allowing more than 1 tag to be stored per event), so it must be unnested to determine if the kick was good or not (one can confirm that tag 101 represents a goal using the tags2name table).

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

be4ff29e3c429a31.png

Click Check my progress to verify the objective

Check the query has been run

In this section BigQuery was used to illustrate how to define a query that shows player information relating to penalty kicks. The query creates a join that displays specific information about a playerId and enables more detailed information to be displayed.

Pop Quiz

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

Congratulations!

You have successfully written and executed queries to analyze data stored in BigQuery tables.

sports_analytics

Finish your Quest

This self-paced lab is part of the Sports Analytics: Pitch Perfect BigQuery Quest. A Quest is a series of related labs that form a learning path. Completing this Quest will earn you the badge above to recognize your achievement. You can make your badges public and link to them in your online résumé or social media account. Enroll in this Quest to get immediate credit for completing this lab. See other available Quests.

Take your next lab

Continue your Quest with BigQuery Soccer Data Analytical Insight.

Google Cloud Training & 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 12, 2022
Lab Last Tested December 12, 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.