2018年ワールドカップの全ゴールを視覚化する

以下はThe 2018 World Cup Visualized: All the Goals So Farの日本語訳です。

The 2018 World Cup Visualized: All the Goals So Far

Data Studioを使って、全てのゴールがどこから撃たれたかインタラクティブに可視化した。
データはBigQueryで抽出された。
詳細は以下に記す。

各点は、プレイヤーが得点した場所を示しています。

左側：自殺点、自チームのゴールに蹴り込んでしまったことを表します。
右側：気になるチームだけを表示するインタラクティブな操作が可能です。

さらに詳しい分析については、Eric SchmidtのAnalyzing the World Cup using Google Cloudをご覧ください。

How I Built this Visualization

Data acquisition

データ収集。
このチャートは、2014年のワールドカップ結果を分析するのに使ったのと同じソースを使用しています。
生データを再共有することはできませんので、必要であればOpta Sportsとライセンス契約してください。

https://t.co/G2R22iR1ZX
Google I/O 2014 - Predicting the future with the Google Cloud Platform

— ラナ・クアール (@rana_kualu) 2018年6月26日

Extracting the goals

得点情報の抜き出し。
チームIDからチーム名を取り出すコードは少々トリッキーでした。
自殺点は自チームがマイナスされるものであって、自チームの得点になるものではありません。

#standardSQL
WITH team_names AS (
  SELECT team_id, MIN(name) name
  FROM (
    SELECT away_team_id team_id, away_team_name name
    FROM `cloude-sandbox.galacticos.games`
    WHERE competition_id = 4
    UNION ALL
    SELECT home_team_id team_id, home_team_name name
    FROM `cloude-sandbox.galacticos.games`
    WHERE competition_id = 4
  ) 
  GROUP BY 1
)
SELECT a.* EXCEPT(game_id, player_id), b.name versus
  , (SELECT MAX(name) FROM `cloude-sandbox.galacticos.sqauds` WHERE player_id = a.player_id) player
FROM (
SELECT id, DATE(event_timestamp) date, x, y, min minute
  , (SELECT name FROM team_names WHERE team_id=a.team_id) team
  , game_id, CONCAT('p', CAST(player_id AS STRING)) player_id
FROM `cloude-sandbox.galacticos.events` a
WHERE competition_id = 4
AND type_id IN (16)
AND DATE(event_timestamp) > '2018-01-01'
) a
JOIN (
  SELECT id, home_team_name name FROM `cloude-sandbox.galacticos.games` UNION ALL SELECT id,  away_team_name name FROM `cloude-sandbox.galacticos.games` 
) b
ON a.game_id = b.id
AND a.team != b.name
ORDER BY 1, 2

Scheduling result extraction to a Google sheet

抽出結果のGoogleスプレッドシートへの貼り付けのスケジューリング。
私はAni LopezによるBigQuery scheduling sheetを修正して使用しました。
結果をクエリに保存するかわりに同じシートに保存するようにしました。

Simplest way to schedule #BigQuery jobs. GSheet > https://t.co/xTEbR79PD3
Sends Success/Fail email
1 Copy the sheet
2 Update the query and the details of the table to append results
3 Go to Tools > Script Editor
4 Create trigger for the day/time you want the query to run
Enjoy!

— Ani Lopez (@anilopez) 2018年1月30日

Connect Data Studio to the sheet

BigQueryの結果をData Studioに取り込むかわりに、前述のスプレッドシートを指定します。

Visualize

Data Studioすげー :)

Refresh

データセットが更新されるとスプレッドシートのクエリが実行され、そしてData Studioに反映されます。

Next steps

もっと話をしたいですか？
MediumとTwitterをチェックしてください。
reddit.com/r/bigqueryを読んで、BigQueryにチャレンジしましょう。
毎月1テラバイトまで無料で分析できます。

感想

Googleすげー、Data Studioすげー、って話なのですが、途中が省略されすぎててよくわかりません。

著者のFelipe HoffaはGoogleのDeveloper Advocateで、要するにGoogleを使えばこんなこともできるぞーって紹介する人ですね。
ということでまあ、確かにすごいというのはわかるのですが、具体的な中身がThat’s the Data Studio magic :)しか書かれてないので実際どうすればいいのかさっぱりだ。

あとサッカー全く見てないので正しいのかどうかわかりません。