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

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

程式碼研究室簡介

subject上次更新時間：3月 30, 2021
account_circle作者：Leigha Jarett

1. 簡介

從事問卷調查的原因有很多，例如評估客戶滿意度、進行市場調查、改善產品或服務，或是鼓勵員工參與。不過，如果您先前使用過問卷調查資料，可能會覺得難以使用標準格式。在本指南中，我們會建立擷取 Google 表單結果的自動化管道、準備透過 Cloud Dataprep 分析的資料、將資料載入 BigQuery，並讓您的團隊使用 Looker 或數據分析等工具進行視覺化分析。

建構項目

在本程式碼研究室中，您將使用 Dataprep 將 Google 表單範例的回覆內容轉換為實用的資料分析格式。您需要將轉換後的資料推送到 BigQuery，這樣就能使用 SQL 提出更深入的問題，並將這些問題彙整至其他資料集，以執行更強大的分析作業。課程結束後，您可以探索預先建立的資訊主頁，或將自己的商業智慧工具連結至 BigQuery 來建立新報表。

課程內容

  • 如何使用 Dataprep 轉換問卷調查資料
  • 如何將問卷調查資料推送至 BigQuery
  • 如何從問卷調查資料中取得更多深入分析

軟硬體需求

  • 已啟用計費功能、BigQuery 和 Dataprep 的 Google Cloud 專案
  • Dataprep 的基本知識很有幫助，但並非必要
  • 具備 BigQuery 和 SQL 的基本知識會有幫助，但並非必要

2. 管理 Google 表單回覆

我們會先仔細查看 Google 表單提供的問卷調查範例。

f3d25efd2cc923f5.png

您可以從「回覆」匯出問卷調查結果來建立新試算表，或將結果載入現有試算表。Google 表單會繼續在試算表中加入回應，因為作答者提交回覆後，除非您取消選取 [接受回應]按鈕。

d499e5a4dccdf5fd.png

4939332a5d8f9f19.png

接著，一起來看看每種回應類型，以及在 Google 試算表檔案中的翻譯方式。

3. 轉換問卷調查回應

問卷調查問題可分為四組，各組均採用特定匯出格式。視問題類型而定，您必須以特定方式重組資料。這裡會檢視每個群組以及需要套用的轉換類型。

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

  • 問題名稱：資料欄名稱
  • 回應：儲存格值
  • 轉換需求：不需要轉換；回應會按原樣載入

3eeedc50b0fd54fd.png

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

  • 問題名稱：資料欄名稱
  • 回應：含有分號分隔符的值清單 (例如「Resp 1; Resp 4; Resp 6」)。
  • 轉換需求：需要擷取及透視值清單，因此每個回應都會變成新的資料列。

cab8a38a96a13ce4.png

選擇題方格問題

以下是選擇題的範例。這時必須從每列選取一個值。

c6ea3d47d4dd5e78.png

  • 問題名稱：每個問題都會變成資料欄名稱，格式為「問題 [選項]」。
  • 回應：格線中的每個回應都會變成含有不重複值的資料欄。
  • 轉換需求條件：在表格中，每個問題/答案都必須成為新的資料列，並分成兩欄。一個包含問題 option 資料欄，以及包含回應的其他資料欄。

9223d0271516c58d.png

單選核取方塊方格問題

以下是核取方塊格線的範例。從各個資料列中選取 [無] 到多個值。

4e3189b8cc2d4a8b.png

  • 問題名稱：每個問題都會變成資料欄名稱，格式為「問題 [選項]」。
  • 回應：網格中的每個回應都會變成一欄，列出以分號分隔的值。
  • 轉換規定：這類問題類型結合了「核取方塊」和「複選方格」而且必須依照此順序解決

首先，必須擷取及透視每個回應的值清單，讓每個答案成為該特定問題的新資料列。

第二：每次回應都必須在資料表中成為新的列，並拆分為兩欄。其中一欄包含問題 option，另一欄包含答案。

3c3c2bd098e03003.png

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

4. 建立 Cloud Dataprep 流程

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

下載 Google Forms Analytics 設計模式流程套件 (不要解壓縮)。在 Cloud Dataprep 應用程式中，按一下左側導覽列中的「Flows」圖示。然後在「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 資料轉換方案。

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

afa421849b1bd398.png

建議你從「乾淨標頭」開始逐一探索每一份食譜然後是「SingleChoiceSELECT-Questions」後面則加入其他食譜

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

449da06d96cd520e.png

4ac6e14f578d0707.png

6. 執行 Cloud Dataprep 流程

正確設定來源和目的地後，您可以執行流程來轉換回應並將其載入 BigQuery。選取各項輸出內容，然後按一下「Run」(執行)按鈕。如果指定的 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

如要提升分析成效，您可以將問卷調查回應加入客戶關係管理資料，瞭解參與者是否對應至資料倉儲中既有的帳戶。這有助貴公司針對客戶服務或鎖定新產品，做出更明智的決策。

以下說明如何根據作答者的網域和帳戶網站，將問卷調查資料彙整至帳戶表格。您現在可以依帳戶類型查看回應的分佈情形，瞭解現有客戶帳戶中有多少作答者。

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依照數據分析提供的所有操作說明。如要瞭解詳情，請前往這裡查看數據分析主要功能的簡介和簡介。您也可以在此處找到我們預先建立的數據分析資訊主頁。

5e744869e3fe3f8f.png

9. 清除

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

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