轉換 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」(關閉) 即可刪除專案。