概要

BigQuery は、Google が提供する低コスト、NoOps のフルマネージド分析データベースです。BigQuery では、インフラストラクチャを所有して管理したりデータベース管理者を置いたりすることなく、テラバイト単位の大規模なデータでクエリを実行できます。また、SQL が採用されており、従量課金制というメリットもあります。このような特長のおかげで、有用な情報を得るためのデータ分析に専念できます。

新たに利用できるようになった e コマース データセットには、Google Merchandise Store に関する数百万件の Google アナリティクスのレコードが含まれており、BigQuery のテーブルに読み込まれています。このラボでは、このデータセットのコピーを使用します。提示されるサンプル シナリオで、データを参照し、重複する情報を削除する方法を確認します。その後、ラボではデータに対しさらに分析を行います。

データ分析用に提供される BigQuery クエリを理解し、応用するために、標準 SQL クエリ構文をご確認ください。

演習内容

このラボでは、BigQuery を使用して以下の操作を行います。

• e コマース データセットにアクセスする
• データセットのメタデータを確認する
• 重複するエントリを削除する
• クエリを作成して実行する

設定と要件

各ラボでは、新しい Google Cloud プロジェクトとリソースセットを一定時間無料で利用できます。

1. Qwiklabs にシークレット ウィンドウでログインします。

2. ラボのアクセス時間（例: 1:15:00）に注意し、時間内に完了できるようにしてください。
   一時停止機能はありません。必要な場合はやり直せますが、最初からになります。

3. 準備ができたら、[ラボを開始] をクリックします。

4. ラボの認証情報（ユーザー名とパスワード）をメモしておきます。この情報は、Google Cloud Console にログインする際に使用します。

5. [Google Console を開く] をクリックします。

6. [別のアカウントを使用] をクリックし、このラボの認証情報をコピーしてプロンプトに貼り付けます。
   他の認証情報を使用すると、エラーが発生したり、料金の請求が発生したりします。

7. 利用規約に同意し、再設定用のリソースページをスキップします。

タスク 1. BigQuery でラボのプロジェクトにスターを付ける

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

BigQuery コンソールを開く

1. Google Cloud Console で、ナビゲーション メニュー > [BigQuery] を選択します。[Cloud Console の BigQuery へようこそ] メッセージ ボックスが開きます。このメッセージ ボックスにはクイックスタート ガイドへのリンクと、UI の更新情報が表示されます。
2. [完了] をクリックします。

BigQuery の一般公開データセットは、デフォルトでは BigQuery のウェブ UI には表示されないため、一般公開データセットのプロジェクトを開きます。

4. [+ データを追加] をクリックします。

5. [名前を指定してプロジェクトにスターを付ける] を選択します。

6. [プロジェクト名] に「data-to-insights」と入力します。

7. [スターを付ける] をクリックします。

8. [エクスプローラ] ペインに、スターを付けた data-to-insights プロジェクトが表示されます。

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

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

[従来のエクスプローラ] セクションに戻り、all_sessions_raw テーブルのデータを確認します。

1. [data-to-insights] プロジェクトを開きます。
2. [ecommerce] を開きます。
3. [all_sessions_raw] をクリックします。

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

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

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

問題:

重複する行を特定する

データのサンプルを見ることで、データセットに含まれる内容を理解しやすくなります。SQL を使用せずにテーブルのサンプル行をプレビューするには、[プレビュー] タブをクリックします。

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

ここでは、全フィールドに SQL の GROUP BY 関数を使用し、全フィールドの値が重複している行の数を集計（COUNT）するクエリを作成します。

• 全フィールドの値が重複している行がなければ、COUNT で 1 が返されます。グループ化の対象となる同じ値を持つ行が他にないためです。

• 全フィールドの値が重複している行がある場合は、それらの行がグループ化されて COUNT が 1 よりも大きくなります。 クエリの最後の部分は HAVING を使用した集計フィルタで、これにより重複行を持つ（COUNT が 1 より大きい）結果のみが表示されます。

• 次のクエリをコピーしてクエリエディタに貼り付け、[実行] をクリックして、すべての列が重複しているレコードを見つけます。エディタタブが表示されていない場合は、[+]（SQL クエリ）をクリックします。

#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. [+]（SQL クエリ）をクリックします。
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 と指定します。

結果

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

8. 全訪問者による閲覧数（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;

結果

9. 参考: クエリを改良し、訪問者が同じ商品を複数回閲覧した場合に重複して計上されないようにしてみてください。つまり、個々の商品の閲覧数が、訪問者 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 つ目のクエリで、すべての訪問者と商品に対して集計を行います。

結果

10. 次に、前のクエリを拡張して、商品ごとの合計注文回数と、合計注文個数（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;

結果

問題:

11. クエリを拡張し、商品ごとの平均注文個数も表示されるようにします（合計注文個数÷合計注文回数: 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 で最大でした。

タスク 4. SQL を使用した練習

SQL のスキルをテストする用意はできましたか？以下の練習問題をお試しください。

問題 1: コンバージョン率を計算する

1. 以下の条件を満たす商品のコンバージョン率を取得するクエリを作成してください。

• カートに追加された、または注文された個数が 1,000 を超えている
• かつ、フリスビー（frisbee）以外の商品である

2. 以下の質問に回答してください。

• 商品が注文に含まれていた一意の回数は何回ですか？（完了した注文か未完了の注文かは問わない）
• 注文に含まれていた商品の合計個数はいくつですか？（完了した注文か未完了の注文かは問わない）
• コンバージョン率が最大であった商品は何ですか？

3. 以下の未完成のクエリを完成させてください。

#standardSQL SELECT COUNT(*) AS product_views, COUNT(productQuantity) AS potential_orders, SUM(productQuantity) AS quantity_product_added, v2ProductName FROM `data-to-insights.ecommerce.all_sessions` WHERE v2ProductName NOT LIKE 'frisbee' GROUP BY v2ProductName HAVING quantity_product_added > ORDER BY conversion_rate LIMIT 10;

正解例:

#standardSQL SELECT COUNT(*) AS product_views, COUNT(productQuantity) AS potential_orders, SUM(productQuantity) AS quantity_product_added, (COUNT(productQuantity) / COUNT(*)) AS conversion_rate, v2ProductName FROM `data-to-insights.ecommerce.all_sessions` WHERE LOWER(v2ProductName) NOT LIKE '%frisbee%' GROUP BY v2ProductName HAVING quantity_product_added > 1000 ORDER BY conversion_rate DESC LIMIT 10;

問題 2: 訪問者の購入手続きの状況を追跡する

• アクション タイプ（eCommerceAction_type）と、各タイプに関連付けられた訪問者（fullVisitorId）の数（訪問者が重複している場合は 1 人として計上）を取得するクエリを作成してください。

正解例:

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

参考

アクション タイプには以下のマッピングを使用します。 Unknown（不明）= 0 Click through of product lists（商品リストのクリックスルー）= 1 Product detail views（商品の詳細の閲覧）= 2 Add product(s) to cart（カートへの商品の追加）= 3 Remove product(s) to cart（カートからの商品の削除）= 4 Check out（購入手続き中）= 5 Completed purchase（購入の完了）= 6 Refund of purchase（購入の払い戻し）= 7 Checkout options（購入手続きのオプション）= 8

• CASE ステートメントを使用して前のクエリに新しい列を追加し、eCommerceAction_type のラベル（「Completed purchase」など）を表示します。

正解例:

#standardSQL SELECT COUNT(DISTINCT fullVisitorId) AS number_of_unique_visitors, eCommerceAction_type, CASE eCommerceAction_type WHEN '0' THEN 'Unknown' WHEN '1' THEN 'Click through of product lists' WHEN '2' THEN 'Product detail views' WHEN '3' THEN 'Add product(s) to cart' WHEN '4' THEN 'Remove product(s) from cart' WHEN '5' THEN 'Check out' WHEN '6' THEN 'Completed purchase' WHEN '7' THEN 'Refund of purchase' WHEN '8' THEN 'Checkout options' ELSE 'ERROR' END AS eCommerceAction_type_label FROM `data-to-insights.ecommerce.all_sessions` GROUP BY eCommerceAction_type ORDER BY eCommerceAction_type;

カートに商品を追加した訪問者が購入を完了した割合（%）はいくらですか？

正解: 35.68%（19988÷56010=0.3568）

問題 3: 品質が高いセッションの放棄されたカートを追跡する

• 集計関数を使用して、商品をカートに追加したものの購入手続きを完了しなかった（ショッピング カートを放棄した）訪問者のセッション ID（重複は排除する）を取得するクエリを作成してください。

正解例:

#standardSQL # 品質が高い放棄されたカート SELECT #unique_session_id CONCAT(fullVisitorId,CAST(visitId AS STRING)) AS unique_session_id, sessionQualityDim, SUM(productRevenue) AS transaction_revenue, MAX(eCommerceAction_type) AS checkout_progress FROM `data-to-insights.ecommerce.all_sessions` WHERE sessionQualityDim > 60 # 品質が高いセッション GROUP BY unique_session_id, sessionQualityDim HAVING checkout_progress = '3' # 「3」は「カートへの追加」 AND (transaction_revenue = 0 OR transaction_revenue IS NULL)

ラボを終了する

ラボでの学習が完了したら、[ラボを終了] をクリックします。ラボで使用したリソースが Qwiklabs から削除され、アカウントの情報も消去されます。

ラボの評価を求めるダイアログが表示されたら、星の数を選択してコメントを入力し、[送信] をクリックします。

星の数は、それぞれ次の評価を表します。

• 星 1 つ = 非常に不満
• 星 2 つ = 不満
• 星 3 つ = どちらともいえない
• 星 4 つ = 満足
• 星 5 つ = 非常に満足

フィードバックを送信しない場合は、ダイアログ ボックスを閉じてください。

フィードバック、ご提案、修正が必要な箇所については、[サポート] タブからお知らせください。

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