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 コンソールにログインする方法

1. [ラボを開始] ボタンをクリックします。ラボの料金をお支払いいただく必要がある場合は、表示されるポップアップでお支払い方法を選択してください。 左側の [ラボの詳細] パネルには、以下が表示されます。

   • [Google コンソールを開く] ボタン
   • 残り時間
   • このラボで使用する必要がある一時的な認証情報
   • このラボを行うために必要なその他の情報（ある場合）

2. [Google コンソールを開く] をクリックします。 ラボでリソースが起動し、別のタブで [ログイン] ページが表示されます。

   ヒント: タブをそれぞれ別のウィンドウで開き、並べて表示しておきましょう。

   注: [アカウントの選択] ダイアログが表示されたら、[別のアカウントを使用] をクリックします。

3. 必要に応じて、[ラボの詳細] パネルから [ユーザー名] をコピーして [ログイン] ダイアログに貼り付けます。[次へ] をクリックします。

4. [ラボの詳細] パネルから [パスワード] をコピーして [ようこそ] ダイアログに貼り付けます。[次へ] をクリックします。

   重要: 認証情報は左側のパネルに表示されたものを使用してください。Google Cloud Skills Boost の認証情報は使用しないでください。 注: このラボでご自身の Google Cloud アカウントを使用すると、追加料金が発生する場合があります。

5. その後次のように進みます。

   • 利用規約に同意してください。
   • 一時的なアカウントなので、復元オプションや 2 要素認証プロセスは設定しないでください。
   • 無料トライアルには登録しないでください。

その後このタブで Cloud Console が開きます。

注: 左上にある [ナビゲーション メニュー] をクリックすると、Google Cloud のプロダクトやサービスのリストが含まれるメニューが表示されます。

タスク 1. BigQuery でラボのプロジェクトを固定する

このセクションでは、data-to-insights プロジェクトを使用環境のリソースに追加します。

1. ナビゲーション メニュー > [BigQuery] の順にクリックします。

[Cloud コンソールの BigQuery へようこそ] メッセージ ボックスが開きます。

注: [Cloud Console の BigQuery へようこそ] メッセージ ボックスには、クイックスタート ガイドと UI の更新情報へのリンクが表示されます。

2. [完了] をクリックします。
3. BigQuery の一般公開データセットは、デフォルトでは BigQuery のウェブ UI に表示されません。一般公開データセット プロジェクトを開くには、「data-to-insights」をコピーします。
4. [+ 追加] > [名前を指定してプロジェクトにスターを付ける] の順にクリックしたら、名前を「data-to-insights」に設定します。[スターを付ける] をクリックします。

[エクスプローラ] セクションに data-to-insights プロジェクトが表示されます。

タスク 2. e コマースデータを確認し、重複するレコードを特定する

シナリオ: データ アナリスト チームが、e コマース ウェブサイトに関する Google アナリティクスのログを BigQuery にエクスポートしました。また、ウェブサイトを訪れたユーザーのセッションに関する未加工データすべてを含む新しいテーブルを作成しました。

all_sessions_raw テーブルのデータを確認します。

1. data-to-insights の近くにある [ノードを開きます] アイコンをクリックして、プロジェクトを展開します。
2. [ecommerce] を開きます。
3. [all_sessions_raw] をクリックします。

右側のペインに、テーブルデータに関する以下の 3 つのビューを含むセクションが表示されます。

• [スキーマ] タブ: [フィールド名]、[タイプ]、[モード]、[説明]。データの整理に使用する論理制約
• [詳細] タブ: テーブルのメタデータ
• [プレビュー] タブ: テーブルのプレビュー

4. [詳細] タブをクリックして、テーブルのメタデータを表示します。

質問:

重複する行を特定する

データのサンプルを見ることで、データセットに含まれる内容を理解しやすくなります。

1. SQL を使用せずにテーブルのサンプル行をプレビューするには、[プレビュー] タブをクリックします。

2. スクロールして行を一通り確認します。行を一意に特定できる単一のフィールドはありません。そのため、重複する行を特定するには高度なロジックが必要です。

3. 以下で使用するクエリは、各フィールドに対して SQL の GROUP BY 関数を実行し、すべてのフィールドの値が重複している行の数を COUNT 関数を使って集計します。

• 全フィールドの値が重複している行がなければ、COUNT からは 1 が返されます。グループ化の対象となる同じ値を持つ行が他にないためです。
• 全フィールドの値が重複している行が複数存在する場合は、それらの行がグループ化され、COUNT からは 1 よりも大きい値が返されます。

クエリの最後の部分は HAVING を使用した集計フィルタで、これにより重複行を持つ（COUNT が 1 より大きい）結果のみが表示されます。そのため、重複する値を持つレコードの数は、結果として生成されるテーブルの行数と一致します。

4. 次のクエリをコピーしてクエリエディタに貼り付け、[実行] でクエリを実行してすべての列の値が重複しているレコードを見つけます。

#standardSQL SELECT COUNT(*) as num_duplicate_rows, * FROM `data-to-insights.ecommerce.all_sessions_raw` GROUP BY fullVisitorId, channelGrouping, time, country, city, totalTransactionRevenue, transactions, timeOnSite, pageviews, sessionQualityDim, date, visitId, type, productRefundAmount, productQuantity, productPrice, productRevenue, productSKU, v2ProductName, v2ProductCategory, productVariant, currencyCode, itemQuantity, itemRevenue, transactionRevenue, transactionId, pageTitle, searchKeyword, pagePathLevel1, eCommerceAction_type, eCommerceAction_step, eCommerceAction_option HAVING num_duplicate_rows > 1;

注: 一意のキーがある独自のデータセットを使用している場合でも、分析を開始する前に COUNT、GROUP BY、HAVING を使用して行が一意であることを確認することをおすすめします。

[進行状況を確認] をクリックして、目標に沿って進んでいることを確認します。 重複する行を特定する

新しい all_sessions テーブルを分析する

このセクションでは、重複を排除した all_sessions というテーブルを使用します。

シナリオ: データ分析チームから以下のクエリが提供され、スキーマのスペシャリストが、キーとなるフィールド（スキーマ内の各レコードで一意となる必要がある）を特定しました。

• 次のクエリを実行して、今度は all_sessions テーブルに重複するデータが存在しないことを確認します。

#standardSQL # スキーマ: https://support.google.com/analytics/answer/3437719?hl=ja SELECT fullVisitorId, # 一意の訪問者 ID（ユニーク ユーザー ID） visitId, # 1 人のユーザーが複数回訪問する場合がある date, # YYYYMMDD 形式の文字列で保存されるセッションの日付 time, # 個々のサイトのヒット回数（訪問者のセッションあたり 0 以上） v2ProductName, # 商品にはカラー バリエーションなどがあるため一意ではない productSKU, # 商品ごとに一意 type, # 訪問者は「ページ」を訪問したり「イベント」をトリガーしたりする場合がある（両方行う場合もある） eCommerceAction_type, # 「カートへの追加」および「購入の完了」とマッピングされる eCommerceAction_step, eCommerceAction_option, transactionRevenue, # 注文に対しての収益 transactionId, # 収益に結びついたトランザクションの一意の識別子 COUNT(*) as row_count FROM `data-to-insights.ecommerce.all_sessions` GROUP BY 1,2,3 ,4, 5, 6, 7, 8, 9, 10,11,12 HAVING row_count > 1 # 重複を特定

このクエリではレコードが 1 件も返されません。

注: SQL では、「GROUP BY fullVisitorId」ではなく「GROUP BY 1」のように、列のインデックスに対して GROUP BY や ORDER BY 関数を実行できます。

タスク 3. e コマースデータに対する基本的な SQL を作成する

このセクションでは、e コマース データセットで分析情報を得るためのクエリを実行します。

合計ユニーク ユーザー数を表示するクエリを作成する

このクエリでは、合計閲覧数（product_views を集計）と、ユニーク ユーザー数（fullVisitorID を集計）を確認します。

1. [+]（新しいクエリの作成）アイコンをクリックします。
2. エディタに次のクエリを入力します。

#standardSQL SELECT COUNT(*) AS product_views, COUNT(DISTINCT fullVisitorId) AS unique_visitors FROM `data-to-insights.ecommerce.all_sessions`;

3. リアルタイム クエリ バリデータで緑色のチェックマークのアイコンが表示されているかを確認することで、構文が有効であることを確かめられます。
4. [実行] をクリックします。 結果からユニーク ユーザー数を確認します。

結果:

5. 次に、参照元サイト（channelGrouping）ごとのユニーク ユーザー（fullVisitorID）の数を表示するクエリを作成します。

#standardSQL SELECT COUNT(DISTINCT fullVisitorId) AS unique_visitors, channelGrouping FROM `data-to-insights.ecommerce.all_sessions` GROUP BY channelGrouping ORDER BY channelGrouping DESC;

結果:

6. すべての一意の商品名（v2ProductName）をアルファベット順で一覧表示するクエリを作成します。

#standardSQL SELECT (v2ProductName) AS ProductName FROM `data-to-insights.ecommerce.all_sessions` GROUP BY ProductName ORDER BY ProductName

ヒント: SQL で ORDER BY 句を使用した場合、デフォルトでは昇順（ASC）、つまり A から Z の順になります。降順にする場合は、ORDER BY <フィールド名> DESC と指定します。

結果:

このクエリでは、計 633 件の商品（行）が返されます。

7. 全訪問者による閲覧数（product_views）が上位 5 件の商品を一覧表示するクエリを作成します（訪問者が同じ商品を複数回閲覧した場合もそのまま計上します）。このクエリでは、商品（v2ProductName）の閲覧数（product_views）を集計し、リストを降順に並べ替え、上位 5 件のエントリを表示します。

ヒント: Google アナリティクスでは、訪問者の接点のタイプが「page」、「screenview」、「event」、「transaction」、「item」、「social」、「exception」、「timing」の場合に、商品が「閲覧」される可能性があります。ここではクエリの目的を考えて、type = 'PAGE' のみでフィルタします。

#standardSQL SELECT COUNT(*) AS product_views, (v2ProductName) AS ProductName FROM `data-to-insights.ecommerce.all_sessions` WHERE type = 'PAGE' GROUP BY v2ProductName ORDER BY product_views DESC LIMIT 5;

結果:

8. 参考: クエリを改良し、訪問者が同じ商品を複数回閲覧した場合に重複して計上されないようにしてみましょう。つまり、個々の商品の閲覧数が、訪問者 1 人につき 1 回のみ計上されるようにします。

WITH unique_product_views_by_person AS ( -- 各訪問者が閲覧した商品を取得する（重複は排除） SELECT fullVisitorId, (v2ProductName) AS ProductName FROM `data-to-insights.ecommerce.all_sessions` WHERE type = 'PAGE' GROUP BY fullVisitorId, v2ProductName ) -- 閲覧数が上位の商品を集計し、並べ替える SELECT COUNT(*) AS unique_view_count, ProductName FROM unique_product_views_by_person GROUP BY ProductName ORDER BY unique_view_count DESC LIMIT 5

ヒント: SQL の WITH 句を使用すると、複雑なクエリを複数のステップに分割できます。ここでは、訪問者ごとに各商品を 1 回だけ計上するクエリを最初に作成します。その後、2 つ目のクエリで、すべての訪問者と商品に対して集計を行います。

結果:

9. 次に、前のクエリを拡張して、商品ごとの合計注文回数と、合計注文個数（productQuantity）も表示されるようにします。

#standardSQL SELECT COUNT(*) AS product_views, COUNT(productQuantity) AS orders, SUM(productQuantity) AS quantity_product_ordered, v2ProductName FROM `data-to-insights.ecommerce.all_sessions` WHERE type = 'PAGE' GROUP BY v2ProductName ORDER BY product_views DESC LIMIT 5;

結果:

問題:

10. クエリを拡張し、商品ごとの平均注文個数も表示されるようにします（合計注文個数÷合計注文回数: SUM(productQuantity)÷COUNT(productQuantity)）。

#standardSQL SELECT COUNT(*) AS product_views, COUNT(productQuantity) AS orders, SUM(productQuantity) AS quantity_product_ordered, SUM(productQuantity) / COUNT(productQuantity) AS avg_per_order, (v2ProductName) AS ProductName FROM `data-to-insights.ecommerce.all_sessions` WHERE type = 'PAGE' GROUP BY v2ProductName ORDER BY product_views DESC LIMIT 5;

結果

問題:

「22 oz YouTube Bottle Infuser」の平均注文個数（avg_per_order）が 9.38 で最大でした。

[進行状況を確認] をクリックして、目標に沿って進んでいることを確認します。 e コマースデータに対する基本的な SQL を作成する

お疲れさまでした

BigQuery を使用してデータを表示したりクエリを実行したりして、プロダクト マーケティングのさまざまな側面についての有用な情報を得ることができました。

次のステップと詳細情報

• BigQuery の一般公開データセットをご確認ください。
• Google アナリティクス アカウントをお持ちで、BigQuery で独自のデータセットをクエリするには、こちらのエクスポート ガイドの手順を実施してください。
• BigQuery に関して知っておくべき 15 の重要なことをご確認ください。
• BigQuery に関してさらに詳しく学習する場合は、以下のラボをご確認ください。
  • BigQuery でのよくある SQL エラーのトラブルシューティング
  • BigQuery の気象データ

Google Cloud トレーニングと認定資格

Google Cloud トレーニングと認定資格を通して、Google Cloud 技術を最大限に活用できるようになります。必要な技術スキルとベスト プラクティスについて取り扱うクラスでは、学習を継続的に進めることができます。トレーニングは基礎レベルから上級レベルまであり、オンデマンド、ライブ、バーチャル参加など、多忙なスケジュールにも対応できるオプションが用意されています。認定資格を取得することで、Google Cloud テクノロジーに関するスキルと知識を証明できます。

マニュアルの最終更新日: 2024 年 1 月 26 日

ラボの最終テスト日: 2023 年 8 月 24 日

Copyright 2024 Google LLC All rights reserved. Google および Google のロゴは Google LLC の商標です。その他すべての企業名および商品名はそれぞれ各社の商標または登録商標です。