轉換 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

選擇題：多個選項、核取方塊

  • 問題名稱：資料欄名稱
  • Response：以分號分隔的值清單 (例如「Resp 1; Resp 4; Resp 6」)
  • 轉換要求：系統需要擷取並樞紐化值清單，讓每個回覆都成為新的資料列。

cab8a38a96a13ce4.png

單選方格題型

以下是選擇題的範例。必須從每個資料列中選取一個值。

c6ea3d47d4dd5e78.png

  • 問題名稱：每個個別問題都會成為欄位名稱，格式為「Question [選項]」。
  • 回應：每個格線中的個別回應都會成為包含不重複值的資料欄。
  • 轉換要求：每個問題/答案都必須成為表格中的新列，並分成兩個欄。一個欄位提及問題的選項，另一個欄位則包含回應。

9223d0271516c58d.png

多項選擇題核取方塊格題型

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

4e3189b8cc2d4a8b.png

  • 問題名稱：每個個別問題都會成為欄位名稱，格式為「Question [選項]」。
  • 回應：每個格狀圖中的個別回應都會變成資料欄，並以半形冒號分隔值清單。
  • 轉換要求：這類問題結合了「核取方塊」和「選擇題表格」類別，必須按照這個順序解決。

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

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

3c3c2bd098e03003.png

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

4. 建構 Cloud Dataprep 流程

在 Cloud Dataprep 中匯入「Google 表單數據分析設計模式」

下載 Google 表單 Analytics 設計模式流程套件 (不解壓縮)。在 Cloud Dataprep 應用程式中，按一下左側導覽列中的「流程」圖示。然後在「流程」頁面中，選取內容選單中的「匯入」。

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

依序點選右下方的「Go」和「Import & Add to Flow」。回到模式後，請按一下右下方的「取代」按鈕。

連結 BigQuery 資料表

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

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

a3fc2cb80153ec25.png

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

85791e6162a370de.png

當您看到「發布動作」畫面時，請按一下 BigQuery 連線並編輯其屬性，以便變更連線設定。

1f3e4887baaeaffd.png

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

f4eaa05ecf9de162.png

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

46edea1b8ca63270.png

按照相同的步驟重複執行每個輸出項目。您必須編輯 8 個目的地。

5. 解說 Cloud Dataprep 流程

「Google 表單分析設計模式」流程的基本概念，是針對調查回覆執行轉換作業，如先前所述，將每個問題類別分解為特定的 Cloud Dataprep 資料轉換方案。

這項流程會將問題拆分為 4 個表格 (對應於 4 個問題類別，方便說明)

afa421849b1bd398.png

建議您逐一探索每個食譜，從「清除標頭」開始，然後是「SingleChoiceSELECT-Questions」，接著是其他下方食譜。

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

449da06d96cd520e.png

4ac6e14f578d0707.png

6. 執行 Cloud Dataprep 流程

來源和目的地都已正確設定，您可以執行流程，將回應轉換並載入至 BigQuery。選取每個輸出項目，然後按一下「執行」按鈕。如果指定的 BigQuery 資料表已存在，Dataprep 會附加新的資料列，否則會建立新資料表。

47cf50f6d17a5b1e.png

按一下左側面板中的「工作記錄」圖示，即可監控工作。系統可能需要幾分鐘才能繼續操作並載入 BigQuery 資料表。

afc79eeb27202fb4.png

所有工作完成後，系統會以清晰、結構化且標準化的格式，將問卷調查結果載入 BigQuery，方便您進行分析。

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

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

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 ，開始分析此模式的樣本調查和客戶關係管理資料。只要建立新的 Looker 專案、新增 LookML，然後在檔案中替換連結和資料表名稱，以符合 BigQuery 設定即可。如果您沒有 Looker 執行個體，但有興趣進一步瞭解，可以在這裡安排示範。

129db05d6f85f484.png

數據分析

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

5e744869e3fe3f8f.png

9. 清除

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

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