GSP407

總覽

BigQuery 是 Google 的全代管 NoOps 數據分析資料庫，價格相當實惠。您可以使用 BigQuery 查詢 TB 規模的資料，不必管理基礎架構，也不需要資料庫管理員。BigQuery 使用 SQL 語法，並提供「即付即用」模式。您可專心分析資料，找出有意義的深入分析結果。

我們已在 BigQuery 資料表中載入新推出的電子商務資料集，其中包含數百萬筆 Google Analytics 的 Google 商品網路商店記錄。在本實驗室中，您將使用該資料集的副本，根據範例情境查看資料，並找出方法移除重複資訊。然後，我們還會引導您進一步分析資料。

如要測試我們提供的 BigQuery 查詢，並據此分析資料，請參閱 BigQuery 的查詢語法參考資料。

目標

在這個實驗室中，您會使用 BigQuery 執行以下操作：

• 存取電子商務資料集
• 查看資料集的中繼資料
• 移除重複項目
• 編寫及執行查詢

設定和需求

瞭解以下事項後，再點選「Start Lab」按鈕

請詳閱以下操作說明。實驗室活動會計時，且中途無法暫停。點選「Start Lab」後就會開始計時，顯示可使用 Google Cloud 資源的時間。

您將在真正的雲端環境完成實作實驗室活動，而不是模擬或示範環境。為此，我們會提供新的暫時憑證，供您在實驗室活動期間登入及存取 Google Cloud。

為了順利完成這個實驗室，請先確認：

• 可以使用標準的網際網路瀏覽器 (Chrome 瀏覽器為佳)。

注意事項：請使用無痕模式 (建議選項) 或私密瀏覽視窗執行此實驗室，這可以防止個人帳戶和學員帳戶之間的衝突，避免個人帳戶產生額外費用。

• 是時候完成實驗室活動了！別忘了，活動一旦開始將無法暫停。

注意事項：務必使用實驗室專用的學員帳戶。如果使用其他 Google Cloud 帳戶，可能會產生額外費用。

如何開始研究室及登入 Google Cloud 控制台

1. 點選「Start Lab」按鈕。如果實驗室會產生費用，畫面上會出現選擇付款方式的對話方塊。左側的「Lab Details」窗格會顯示下列項目：

   • 「Open Google Cloud console」按鈕
   • 剩餘時間
   • 必須在這個研究室中使用的臨時憑證
   • 完成這個實驗室所需的其他資訊 (如有)

2. 點選「Open Google Cloud console」；如果使用 Chrome 瀏覽器，也能按一下滑鼠右鍵，選取「在無痕視窗中開啟連結」。

   接著，實驗室會啟動相關資源，並開啟另一個分頁，顯示「登入」頁面。

   提示：您可以在不同的視窗中並排開啟分頁。

   注意：如果頁面中顯示「選擇帳戶」對話方塊，請點選「使用其他帳戶」。

3. 如有必要，請將下方的 Username 貼到「登入」對話方塊。

   {{{user_0.username | "Username"}}}

   您也可以在「Lab Details」窗格找到 Username。

4. 點選「下一步」。

5. 複製下方的 Password，並貼到「歡迎使用」對話方塊。

   {{{user_0.password | "Password"}}}

   您也可以在「Lab Details」窗格找到 Password。

6. 點選「下一步」。

   重要事項：請務必使用實驗室提供的憑證，而非自己的 Google Cloud 帳戶憑證。 注意：如果使用自己的 Google Cloud 帳戶來進行這個實驗室，可能會產生額外費用。

7. 按過後續的所有頁面：

   • 接受條款及細則。
   • 由於這是臨時帳戶，請勿新增救援選項或雙重驗證機制。
   • 請勿申請免費試用。

Google Cloud 控制台稍後會在這個分頁開啟。

注意：如要使用 Google Cloud 產品和服務，請點選「導覽選單」，或在「搜尋」欄位輸入服務或產品名稱。

工作 1：將實驗室專案固定在 BigQuery

在本節中，您會將 data-to-insights 專案新增到環境資源中。

1. 依序點選「導覽選單」>「BigQuery」。

接著，畫面中會顯示「歡迎使用 Cloud 控制台中的 BigQuery」訊息方塊。

注意：「歡迎使用 Cloud 控制台中的 BigQuery」訊息方塊，會列出快速入門指南的連結和使用者介面更新內容。

2. 點選「完成」。

根據預設，BigQuery 網頁版 UI 不會顯示 BigQuery 公開資料集。如要開啟公開資料集專案，請複製「data-to-insights」這段文字。

3. 依序點選「+ 新增」>「依據名稱為專案加上星號」，然後將名稱設為 data-to-insights。請按一下「加上星號」。

data-to-insights 專案現在會列於 Explorer 區段中。

工作 2：瀏覽電子商務資料並找出重複記錄

情境：資料分析師團隊已將電子商務網站的 Google Analytics 記錄匯出至 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 函式確認資料列並未重複，然後再開始分析。

點選「Check my progress」，確認目標已達成。 找出重複資料列

分析新的 all_sessions 資料表

在本節中，您將使用名為 all_sessions 的簡化資料表。

情境：資料分析師團隊向您提供以下查詢，而您的結構定義專家已按照結構定義，找出在各項記錄中都不能重複的索引鍵欄位。

1. 執行查詢，確認沒有重複項目，但這次在 all_sessions 資料表中搜尋：

#standardSQL # schema: https://support.google.com/analytics/answer/3437719?hl=en SELECT fullVisitorId, # the unique visitor ID visitId, # a visitor can have multiple visits date, # session date stored as string YYYYMMDD time, # time of the individual site hit (can be 0 to many per visitor session) v2ProductName, # not unique since a product can have variants like Color productSKU, # unique for each product type, # a visitor can visit Pages and/or can trigger Events (even at the same time) eCommerceAction_type, # maps to ‘add to cart', ‘completed checkout' eCommerceAction_step, eCommerceAction_option, transactionRevenue, # revenue of the order transactionId, # unique identifier for revenue bearing transaction 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 # find duplicates

此查詢未傳回任何記錄。

注意：在 SQL 中，您可以對資料欄索引執行 GROUP BY 或 ORDER BY 函式，方法類似使用 GROUP BY 1 (而非 GROUP BY fullVisitorId)。

工作 3：對電子商務資料編寫基本 SQL

在本節中，您會對電子商務資料集執行查詢，藉此取得深入分析。

編寫查詢以顯示不重複訪客總數

查詢會分別計算 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 field_name DESC。

結果：

這項查詢一共傳回 633 個產品 (資料列)。

7. 編寫查詢，列出所有訪客中 (包含多次查看同一產品者)，檢視次數最高的五項產品 (product_views)。查詢會計算產品 (v2ProductName) 的檢視次數 (product_views)，以遞減順序列出前 5 個項目：

提示：在 Google Analytics 中，訪客可在以下互動類型期間「檢視」產品：'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. 額外操作：現在修正查詢，如果是多次查看同一產品的訪客，就不再重複計算他們的產品檢視次數。每位訪客查看同一產品的次數都僅計為一次：

WITH unique_product_views_by_person AS ( -- find each unique product viewed by each visitor SELECT fullVisitorId, (v2ProductName) AS ProductName FROM `data-to-insights.ecommerce.all_sessions` WHERE type = 'PAGE' GROUP BY fullVisitorId, v2ProductName ) -- aggregate the top viewed products and sort them 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 子句，將複雜的查詢分割為幾個步驟。在本例中，我們會先建立查詢來找出每位訪客檢視的各項不重複產品，並計為一次。然後，再編寫一個查詢，對所有訪客和產品進行匯總。

結果：

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 盎司 YouTube 雙層隨行杯的 avg_per_order 最高，每筆訂單有 9.38 個單位。

點選「Check my progress」，確認目標已達成。 對電子商務資料編寫基本 SQL

恭喜！

恭喜！在本實驗室中，您透過 BigQuery 查看及查詢資料，對產品行銷的許多面向取得意義深遠的深入分析。您學到如何存取電子商務資料集、查看資料集中繼資料、移除重複項目，以及編寫和執行查詢。

後續步驟/瞭解詳情

• 探索 BigQuery 公開資料集。
• 您有 Google Analytics 帳戶，並且想在 BigQuery 中查詢自己的資料集嗎？請按照匯出指南的說明操作。
• 查看「15 Awesome things you probably didn’t know about BigQuery」(15 項您可能不知道的 BigQuery 優點)。
• 查看其他實驗室，進一步瞭解 BigQuery：
  • 利用 BigQuery 排解常見的 SQL 錯誤
  • BigQuery 中的天氣資料

Google Cloud 教育訓練與認證

協助您瞭解如何充分運用 Google Cloud 的技術。我們的課程會介紹專業技能和最佳做法，讓您可以快速掌握要領並持續進修。我們提供從基本到進階等級的訓練課程，並有隨選、線上和虛擬課程等選項，方便您抽空參加。認證可協助您驗證及證明自己在 Google Cloud 技術方面的技能和專業知識。

使用手冊上次更新日期：2024 年 4 月 2 日

實驗室上次測試日期：2024 年 4 月 2 日

Copyright 2025 Google LLC 保留所有權利。Google 和 Google 標誌是 Google LLC 的商標，其他公司和產品名稱則有可能是其關聯公司的商標。