チェックポイント
Identify duplicate rows
/ 30
Write basic SQL on ecommerce data
/ 70
BigQuery で SQL を使用して e コマース データセットを探索する
GSP407
概要
BigQuery は、Google が提供する低コスト、NoOps のフルマネージド分析データベースです。BigQuery では、インフラストラクチャを所有して管理したりデータベース管理者を置いたりすることなく、テラバイト単位の大規模なデータでクエリを実行できます。また、SQL が採用されており、従量課金制というメリットもあります。このような特長を活かし、有用な情報を得るためのデータ分析に専念できます。
新たに利用できるようになった e コマース データセットには、Google Merchandise Store に関する数百万件の Google アナリティクスのレコードが含まれており、BigQuery のテーブルに読み込まれています。このラボでは、このデータセットのコピーを使用します。提示されるサンプル シナリオで、データを参照し、重複する情報を削除する方法を確認します。その後、データ分析についてさらに詳しく学習します。
データ分析用に提供される BigQuery クエリを理解して操作するには、BigQuery のクエリ構文リファレンスを参照してください。
目標
このラボでは、BigQuery を使用して以下の操作を行います。
- e コマース データセットにアクセスする
- データセットのメタデータを確認する
- 重複するエントリを削除する
- クエリを作成して実行する
設定と要件
[ラボを開始] ボタンをクリックする前に
こちらの手順をお読みください。ラボの時間は記録されており、一時停止することはできません。[ラボを開始] をクリックするとスタートするタイマーは、Google Cloud のリソースを利用できる時間を示しています。
このハンズオンラボでは、シミュレーションやデモ環境ではなく、実際のクラウド環境を使ってご自身でラボのアクティビティを行うことができます。そのため、ラボの受講中に Google Cloud にログインおよびアクセスするための、新しい一時的な認証情報が提供されます。
このラボを完了するためには、下記が必要です。
- 標準的なインターネット ブラウザ(Chrome を推奨)
- ラボを完了するために十分な時間を確保してください。ラボをいったん開始すると一時停止することはできません。
ラボを開始して Google Cloud コンソールにログインする方法
-
[ラボを開始] ボタンをクリックします。ラボの料金をお支払いいただく必要がある場合は、表示されるポップアップでお支払い方法を選択してください。 左側の [ラボの詳細] パネルには、以下が表示されます。
- [Google Cloud コンソールを開く] ボタン
- 残り時間
- このラボで使用する必要がある一時的な認証情報
- このラボを行うために必要なその他の情報(ある場合)
-
[Google Cloud コンソールを開く] をクリックします(Chrome ブラウザを使用している場合は、右クリックして [シークレット ウィンドウでリンクを開く] を選択します)。
ラボでリソースが起動し、別のタブで [ログイン] ページが表示されます。
ヒント: タブをそれぞれ別のウィンドウで開き、並べて表示しておきましょう。
注: [アカウントの選択] ダイアログが表示されたら、[別のアカウントを使用] をクリックします。 -
必要に応じて、下のユーザー名をコピーして、[ログイン] ダイアログに貼り付けます。
{{{user_0.username | "Username"}}} [ラボの詳細] パネルでも [ユーザー名] を確認できます。
-
[次へ] をクリックします。
-
以下のパスワードをコピーして、[ようこそ] ダイアログに貼り付けます。
{{{user_0.password | "Password"}}} [ラボの詳細] パネルでも [パスワード] を確認できます。
-
[次へ] をクリックします。
重要: ラボで提供された認証情報を使用する必要があります。Google Cloud アカウントの認証情報は使用しないでください。 注: このラボでご自身の Google Cloud アカウントを使用すると、追加料金が発生する場合があります。 -
その後次のように進みます。
- 利用規約に同意してください。
- 一時的なアカウントなので、復元オプションや 2 要素認証プロセスは設定しないでください。
- 無料トライアルには登録しないでください。
その後、このタブで Google Cloud コンソールが開きます。
タスク 1. BigQuery でラボのプロジェクトを固定する
このセクションでは、data-to-insights プロジェクトを使用環境のリソースに追加します。
- ナビゲーション メニュー > [BigQuery] の順にクリックします。
[Cloud コンソールの BigQuery へようこそ] メッセージ ボックスが開きます。
- [完了] をクリックします。
BigQuery の一般公開データセットは、デフォルトでは BigQuery のウェブ UI に表示されません。一般公開データセット プロジェクトを開くには、「data-to-insights」をコピーします。
-
[+ 追加] > [名前を指定してプロジェクトにスターを付ける] の順にクリックしたら、名前を「
data-to-insights
」に設定します。[スターを付ける] をクリックします。
[エクスプローラ] セクションに data-to-insights プロジェクトが表示されます。
タスク 2. e コマースデータを確認し、重複するレコードを特定する
シナリオ: データ アナリスト チームが、e コマース ウェブサイトに関する Google アナリティクスのログを BigQuery にエクスポートしました。また、ウェブサイトを訪れたユーザーのセッションに関する未加工データすべてを含む新しいテーブルを作成しました。
all_sessions_raw
テーブルのデータを確認します。
- data-to-insights の近くにある [ノードを開く] アイコンをクリックして、プロジェクトを展開します。
- [ecommerce] を開きます。
- [all_sessions_raw] をクリックします。
右側のペインに、テーブルデータに関する以下の 3 つのビューを含むセクションが表示されます。
- [スキーマ] タブ: [フィールド名]、[タイプ]、[モード]、[説明]。データの整理に使用する論理制約
- [詳細] タブ: テーブルのメタデータ
- [プレビュー] タブ: テーブルのプレビュー
- [詳細] タブをクリックして、テーブルのメタデータを表示します。
問題:
重複する行を特定する
データのサンプルを見ることで、データセットに含まれる内容を理解しやすくなります。
-
SQL を使用せずにテーブルのサンプル行をプレビューするには、[プレビュー] タブをクリックします。
-
スクロールして行を一通り確認します。行を一意に特定できる単一のフィールドはありません。そのため、重複する行を特定するには高度なロジックが必要です。
-
以下で使用するクエリは、各フィールドに対して SQL の
GROUP BY
関数を実行し、すべてのフィールドの値が重複している行の数をCOUNT
関数を使って集計します。
- 全フィールドの値が重複している行がなければ、
COUNT
からは 1 が返されます。グループ化の対象となる同じ値を持つ行が他にないためです。 - 全フィールドの値が重複している行が複数存在する場合は、それらの行がグループ化され、
COUNT
からは 1 よりも大きい値が返されます。
クエリの最後の部分は HAVING
を使用した集計フィルタで、これにより重複行を持つ(COUNT
が 1 より大きい)結果のみが表示されます。そのため、重複する値を持つレコードの数は、結果として生成されるテーブルの行数と一致します。
- 次のクエリをコピーしてクエリエディタに貼り付け、[実行] でクエリを実行してすべての列の値が重複しているレコードを見つけます。
[進行状況を確認] をクリックして、目標に沿って進んでいることを確認します。
新しい all_sessions テーブルを分析する
このセクションでは、重複を排除した all_sessions
というテーブルを使用します。
シナリオ: データ分析チームから以下のクエリが提供され、スキーマのスペシャリストが、キーとなるフィールド(スキーマ内の各レコードで一意となる必要がある)を特定しました。
- 次のクエリを実行して、今度は
all_sessions
テーブルに重複するデータが存在しないことを確認します。
このクエリではレコードが 1 件も返されません。
GROUP BY fullVisitorId
」ではなく「GROUP BY 1
」のように、列のインデックスに対して GROUP BY や ORDER BY 関数を実行できます。タスク 3. e コマースデータに対する基本的な SQL を作成する
このセクションでは、e コマース データセットで分析情報を得るためのクエリを実行します。
合計ユニーク ユーザー数を表示するクエリを作成する
このクエリでは、合計閲覧数(product_views
を集計)と、ユニーク ユーザー数(fullVisitorID
を集計)を確認します。
- [+](新しいクエリの作成)アイコンをクリックします。
- エディタに次のクエリを入力します。
- リアルタイム クエリ バリデータで緑色のチェックマークのアイコンが表示されているかを確認することで、構文が有効であることを確かめられます。
- [実行] をクリックします。 結果からユニーク ユーザー数を確認します。
結果:
- 次に、参照元サイト(
channelGrouping
)ごとのユニーク ユーザー(fullVisitorID
)の数を表示するクエリを作成します。
結果:
- すべての一意の商品名(
v2ProductName
)をアルファベット順で一覧表示するクエリを作成します。
ヒント: SQL で ORDER BY 句を使用した場合、デフォルトでは昇順(ASC)、つまり A から Z の順になります。降順にする場合は、ORDER BY <フィールド名> DESC と指定します。
結果:
このクエリでは、計 633 件の商品(行)が返されます。
- 全訪問者による閲覧数(
product_views
)が上位 5 件の商品を一覧表示するクエリを作成します(訪問者が同じ商品を複数回閲覧した場合もそのまま計上します)。このクエリでは、商品(v2ProductName
)の閲覧数(product_views
)を集計し、リストを降順に並べ替え、上位 5 件のエントリを表示します。
ヒント: Google アナリティクスでは、訪問者の接点のタイプが「page」、「screenview」、「event」、「transaction」、「item」、「social」、「exception」、「timing」の場合に、商品が「閲覧」される可能性があります。ここではクエリの目的を考えて、type = 'PAGE' のみでフィルタします。
結果:
- 参考: クエリを改良し、訪問者が同じ商品を複数回閲覧した場合に重複して計上されないようにしてみましょう。つまり、個々の商品の閲覧数が、訪問者 1 人につき 1 回のみ計上されるようにします。
ヒント: SQL の WITH
句を使用すると、複雑なクエリを複数のステップに分割できます。ここでは、訪問者ごとに閲覧した各商品を 1 回だけ計上するクエリを最初に作成します。その後、2 つ目のクエリで、すべての訪問者と商品に対して集計を行います。
結果:
- 次に、前のクエリを拡張して、商品ごとの合計注文回数と、合計注文個数(
productQuantity
)も表示されるようにします。
結果:
問題:
- クエリを拡張し、商品ごとの平均注文個数も表示されるようにします(合計注文個数÷合計注文回数:
SUM(productQuantity)
÷COUNT(productQuantity)
)。
結果
問題:
「22 oz YouTube Bottle Infuser」の平均注文個数(avg_per_order)が 9.38 で最大でした。
[進行状況を確認] をクリックして、目標に沿って進んでいることを確認します。
お疲れさまでした
お疲れさまでした。このラボでは、BigQuery を使用してデータを表示したりクエリを実行したりして、プロダクト マーケティングのさまざまな側面についての有用な情報を得ることができました。このラボでは、e コマース データセットにアクセスする方法、データセットのメタデータを確認する方法、重複するエントリを削除する方法、クエリを作成して実行する方法を学習しました。
次のステップと詳細情報
- BigQuery の一般公開データセットをご確認ください。
- Google アナリティクス アカウントをお持ちで、BigQuery で独自のデータセットをクエリするには、こちらのエクスポート ガイドの手順を実施してください。
- BigQuery に関して知っておくべき 15 の重要なことをご確認ください。
- BigQuery に関してさらに詳しく学習する場合は、以下のラボをご確認ください。
Google Cloud トレーニングと認定資格
Google Cloud トレーニングと認定資格を通して、Google Cloud 技術を最大限に活用できるようになります。必要な技術スキルとベスト プラクティスについて取り扱うクラスでは、学習を継続的に進めることができます。トレーニングは基礎レベルから上級レベルまであり、オンデマンド、ライブ、バーチャル参加など、多忙なスケジュールにも対応できるオプションが用意されています。認定資格を取得することで、Google Cloud テクノロジーに関するスキルと知識を証明できます。
マニュアルの最終更新日: 2024 年 4 月 2 日
ラボの最終テスト日: 2024 年 4 月 2 日
Copyright 2024 Google LLC All rights reserved. Google および Google のロゴは Google LLC の商標です。その他すべての企業名および商品名はそれぞれ各社の商標または登録商標です。