轉換 Google 表單問卷調查回覆並載入 BigQuery

1. 簡介

問卷調查的用途相當廣泛，例如評估顧客滿意度、進行市場調查、改善產品或服務，或是評估員工參與度。不過，如果您曾嘗試處理問卷調查資料，可能就會知道標準格式難以使用。在本指南中，我們將建構自動化管道，擷取 Google 表單結果、使用 Cloud Dataprep 準備資料以供分析、將資料載入 BigQuery，並讓團隊使用 Looker 或 Google 數據分析等工具執行視覺化分析。

建構項目

在本程式碼研究室中，您將使用 Dataprep 將範例 Google 表單問卷調查的回覆轉換為適合資料分析的格式。您會將轉換後的資料推送至 BigQuery，以便使用 SQL 提出更深入的問題，並將資料加入其他資料集，進行更強大的分析。最後，您可以探索預先建構的資訊主頁，或將自己的商業智慧工具連結至 BigQuery，建立新的報表。

課程內容

  • 如何使用 Dataprep 轉換問卷調查資料
  • 如何將問卷調查資料推送至 BigQuery
  • 如何從問卷調查資料中取得更多洞察資訊

軟硬體需求

  • 已啟用計費、BigQuery 和 Dataprep 的 Google Cloud 專案
  • 瞭解 Dataprep 的基本概念會有幫助，但並非必要
  • 瞭解 BigQuery 和 SQL 的基本概念會有幫助，但並非必要

2. 管理 Google 表單回應

首先，我們來仔細查看範例問卷調查的 Google 表單回覆。

f3d25efd2cc923f5.png

如要匯出問卷調查結果，請按一下「回覆」分頁中的 Google 試算表圖示，然後建立新試算表，或將結果載入現有試算表。只要您取消選取「接受回覆」按鈕，Google 表單就會繼續將回覆新增至試算表。

d499e5a4dccdf5fd.png

4939332a5d8f9f19.png

現在來看看每種回應類型，以及在 Google 試算表檔案中的對應方式。

3. 轉換問卷調查回覆

問卷調查問題可歸為四個類別，各有特定的匯出格式。您需要根據問題類型，以特定方式重組資料。在此，我們會檢查每個群組，以及需要套用的轉換類型。

單選題：簡答、段落、下拉式選單、線性尺度等。

  • 問題名稱：資料欄名稱
  • 回應：儲存格值
  • 轉換需求：不需要轉換，系統會直接載入回應。

3eeedc50b0fd54fd.png

選擇題：複選題、核取方塊

  • 問題名稱：資料欄名稱
  • 回應：以半形分號分隔的值清單 (例如「Resp 1; Resp 4; Resp 6」)
  • 轉換需求：需要擷取並透視值清單，讓每個回應都成為新資料列。

cab8a38a96a13ce4.png

單選方格題

以下是選擇題的範例。你必須從每一列選取單一值。

c6ea3d47d4dd5e78.png

  • 問題名稱：每個問題都會成為欄名，格式為「問題 [選項]」。
  • 回應：格線中的每個個別回應都會成為具有不重複值的資料欄。
  • 轉換規定：每個問題/答案都必須成為表格中的新資料列，並分成兩欄。一欄列出問題選項，另一欄列出回覆。

9223d0271516c58d.png

複選方格題

以下是核取方塊格線的範例。每列可選取零到多個值。

4e3189b8cc2d4a8b.png

  • 問題名稱：每個問題都會成為欄名，格式為「問題 [選項]」。
  • 回應：格線中的每個個別回應都會變成一個資料欄，並以半形分號分隔值清單。
  • 轉換需求：這類問題會合併「核取方塊」和「選擇題格線」類別，且必須依此順序解決。

首先，您需要擷取並透視每個回應的值清單，讓每個答案都成為特定問題的新資料列。

第二：每個個別的回應都必須成為表格中的新列，並分成兩欄。一欄提及問題選項，另一欄則提供答案。

3c3c2bd098e03003.png

接下來，我們將說明如何使用 Cloud Dataprep 處理這些轉換。

4. 建構 Cloud Dataprep Flow

在 Cloud Dataprep 中匯入「Google 表單 Analytics 設計模式」

下載 Google 表單 Analytics 設計模式流程套件 (請勿解壓縮)。在 Cloud Dataprep 應用程式中，按一下左側導覽列中的「流程」圖示。接著在「Flows」頁面中，從內容選單選取「Import」。

ba7c0cb0eec398df.png

匯入流程後，請選取匯入的流程進行編輯，畫面應如下所示：

44978861eb34ec71.png

連結 Google 試算表問卷調查結果試算表

在流程左側，資料來源必須重新連結至包含 Google 表單 結果的 Google 試算表。以滑鼠右鍵按一下 Google 試算表資料集物件，然後選取「取代」。

55c16f0c04366f0c.png

然後按一下對話方塊底部的「Import Datasets」連結。按一下「編輯路徑」鉛筆圖示。

8afeef260c96277f.png

然後將目前的值替換為這個連結，該連結會指向含有部分 Google 表單 結果的 Google 試算表。你可以使用我們的範例或自己的副本：https://docs.google.com/spreadsheets/d/1DgIlvlLceFDqWEJs91F8rt1B-X0PJGLY6shkKGBPWpk/edit?usp=sharing

按一下「前往」，然後按一下右下方的「匯入及新增至流程」。返回強制回應後，按一下右下角的「取代」按鈕。

連結 BigQuery 資料表

在流程右側，您需要將輸出內容連結至自己的 BigQuery 執行個體。針對每個輸出，按一下圖示，然後依下列方式編輯屬性。

首先，請編輯「手動目的地」

a3fc2cb80153ec25.png

在下一個「發布設定」畫面中，按一下編輯按鈕

85791e6162a370de.png

看到「發布動作」畫面時，請點選 BigQuery 連線並編輯其屬性，變更連線設定。

1f3e4887baaeaffd.png

選取要將 Google 表單結果載入的 BigQuery 資料集。如果您尚未建立任何 BigQuery 資料集，可以選取「預設」。

f4eaa05ecf9de162.png

編輯「手動目的地」後，請按照相同方式處理「預定目的地」輸出內容。

46edea1b8ca63270.png

按照相同步驟逐一迭代每個輸出內容。您總共需要編輯 8 個目的地。

5. Cloud Dataprep 流程說明

「Google 表單數據分析設計模式」流程的基本概念，是按照先前所述，對問卷調查回覆執行轉換作業，也就是將每個問題類別細分成特定的 Cloud Dataprep recipe。

這個流程會將問題分成 4 個表格 (為求簡單，對應 4 個問題類別)

afa421849b1bd398.png

建議您從「Clean Headers」開始，逐一探索每個食譜，然後是「SingleChoiceSELECT-Questions」，接著是底下的每個食譜。

所有食譜都會加上註解，說明各種轉換步驟。在方案中，您可以編輯步驟，並預覽特定資料欄的變更前後狀態。

449da06d96cd520e.png

4ac6e14f578d0707.png

6. 執行 Cloud Dataprep Flow

來源和目的地設定完成後，即可執行流程，將回覆內容轉換並載入 BigQuery。選取每個輸出內容，然後按一下「執行」按鈕。如果指定的 BigQuery 資料表存在，Dataprep 會附加新資料列，否則會建立新資料表。

47cf50f6d17a5b1e.png

按一下左側窗格的「工作記錄」圖示，即可監控工作。系統應會在幾分鐘內完成程序並載入 BigQuery 資料表。

afc79eeb27202fb4.png

所有工作完成後，問卷調查結果會以乾淨、結構化和正規化的格式載入 BigQuery，方便您進行分析。

7. 在 BigQuery 中分析問卷調查資料

在 Google BigQuery 控制台中，您應該可以查看每個新資料表的詳細資料

df370873572511ac.png

將問卷調查資料匯入 BigQuery 後，您就能輕鬆提出更全面的問題，深入瞭解問卷調查回覆內容。舉例來說，假設您想瞭解不同職稱的人最常使用的程式設計語言，可以編寫如下查詢：

SELECT
   programming_answers.Language  AS programming_answers_language,
   project_answers.Title  AS project_answers_title,
   AVG((case when programming_answers.Level='None' then 0 
when programming_answers.Level='beginner' then 1
when programming_answers.Level='competent' then 2 
when programming_answers.Level='proficient' then 3
when programming_answers.Level='expert' then 4 
else null end) ) AS programming_answers_average_level_value
FROM `my-project.DesignPattern.A000111_ProjectAnswers` AS project_answers
INNER JOIN `my-project.A000111_ProgrammingAnswers` AS programming_answers
ON programming_answers.RESPONSE_ID = project_answers.RESPONSE_ID
GROUP BY 1,2
ORDER BY 3 DESC

如要進一步提升分析成效，您可以將問卷調查回覆與 CRM 資料合併，查看參與者是否對應至資料倉儲中已納入的任何帳戶。這有助於商家做出更明智的決策，例如提供客戶服務或針對新產品發布活動指定目標對象。

以下說明如何根據受訪者的網域和帳戶網站，將問卷調查資料加入帳戶表格。現在，您可以依帳戶類型查看回覆分布情形，瞭解有多少回覆者屬於現有客戶帳戶。

SELECT
   account.TYPE  AS account_type,
   COUNT(DISTINCT project_answers.Domainname) AS project_answers_count_domains
FROM `my-project.A000111_ProjectAnswers` AS project_answers
LEFT JOIN `my-project.testing.account` AS account 
ON project_answers.Domainname=account.website
GROUP BY 1

8. 執行視覺化分析

現在問卷調查資料已集中存放在資料倉儲中，您可以使用商業智慧工具輕鬆分析資料。我們在 數據分析 和 Looker 中建立了一些範例報表。

Looker

如果您已有 Looker 執行個體，可以使用這個資料夾中的 LookML ，開始分析這個模式的範例問卷調查和 CRM 資料。只要建立新的 Looker 專案、新增 LookML，然後將檔案中的連線和資料表名稱替換為符合 BigQuery 設定的名稱即可。如果您沒有 Looker 執行個體，但有興趣瞭解詳情，請在這裡安排示範活動。

129db05d6f85f484.png

數據分析

或者，如要在 數據分析 中建立報表，請點按含有 Google 交叉符號的「空白報表」框架，然後連線至 BigQuery。請按照數據分析的所有指示操作。如要瞭解詳情，請參閱這篇文章，快速入門並認識數據分析的主要功能。您也可以在這裡找到預先建構的數據分析資訊主頁。

5e744869e3fe3f8f.png

9. 清除

如要避免付費，最簡單的方法就是刪除您為本教學課程建立的 Cloud 專案。或者，您也可以刪除個別資源。

  1. 在 Cloud Console 中前往管理資源
  2. 在專案清單中選取要刪除的專案，然後點按「刪除」。
  3. 在對話方塊中輸入專案 ID，然後按一下「Shut down」(關機) 即可刪除專案。