将 Google 表单调查问卷回复转换并加载到 BigQuery 中

1. 简介

开展问卷调查的原因有很多：评估客户满意度、开展市场调研、改进产品或服务，或者评估员工敬业度。不过，如果您在尝试处理问卷调查数据之前，可能知道标准格式难以使用。在本指南中，我们将构建一个自动化流水线，用于捕获 Google 表单结果、准备数据以便通过 Cloud Dataprep 进行分析、将数据加载到 BigQuery 中，并让您的团队使用 Looker 或数据洞察等工具执行可视化分析。

构建内容

在此 Codelab 中，您将使用 Dataprep 将示例 Google 表单调查问卷的回复转换为有助于数据分析的格式。您将转换后的数据推送到 BigQuery，在 BigQuery 中，您可以使用 SQL 提出更深入的问题，并将其联接到其他数据集，从而实现更强大的分析。最后，您可以探索预构建的信息中心，或者将自己的商业智能工具连接到 BigQuery 以创建新报告。

学习内容

  • 如何使用 Dataprep 转换调查数据
  • 如何将调查问卷数据推送到 BigQuery
  • 如何从调查问卷数据中获得更多洞见

所需条件

2. 管理 Google 表单回复

首先，我们来详细了解一下 Google 表单对我们的示例调查问卷的回复。

f3d25efd2cc923f5.png

您可以从“回复”部分导出调查问卷结果标签页，只需点击 Google 表格图标并创建新电子表格或将结果加载到现有电子表格即可。回复者提交回复时，Google 表单会继续将回复添加到电子表格中，直到您取消选择“接受回复”按钮。

d499e5a4dccdf5fd.png

4939332a5d8f9f19

现在，我们来看一下每种响应类型及其在 Google 表格文件中如何转换。

3. 转换调查问卷回复

调查问卷问题可以分为四个类别，每个系列对应一种特定的导出格式。根据问题类型，您需要以特定方式重构数据。在这里，我们了解每个组以及需要应用的转换类型。

单选题：简答题、段落、下拉列表、线性量表等

  • 问题名称：列名称
  • 响应：单元格值
  • 转换要求：不需要转换；响应将按原样加载。

3eeedc50b0fd54fd.png

选择题：单选题，多选题

  • 问题名称：列名称
  • 响应：带有分号分隔符的值列表（例如“Resp 1; Resp 4; Resp 6”）
  • 转换要求：需要提取并透视值列表，因此每个响应都会成为一个新行。

cab8a38a96a13ce4.png

选择题网格问题

下面是一个选择题示例。必须在每行中选择一个值。

c6ea3d47d4dd5e78.png

  • 问题名称：每个问题都会成为一个列名称，格式为“问题 [选项]”。
  • 回答：网格中的每个回复都会成为一个具有唯一值的列。
  • 转换要求：每个问题/答案都必须在表格中成为一个新行，并划分为两列。一列提及问题 option，另一列包含回答。

9223d0271516c58d

选择题多选题网格问题

下面是一个复选框网格示例。您可以在每行中不选择任何值或选择多个值。

4e3189b8cc2d4a8b

  • 问题名称：每个问题都会成为一个列名称，格式为“问题 [选项]”。
  • 回答：网格中的每个回答都会变成一列，其中一列值以英文分号分隔。
  • 转换要求：这些问题类型结合“多选题”和“单选题”网格中必须按此顺序解析。

首先，每个回答的值列表需要提取和透视，因此每个回答都会成为特定问题的新行。

第二：每个回复必须在表中成为一个新行，并分成两列。一列提及问题 option，另一列包含答案。

3c3c2bd098e03003

接下来，我们将展示如何使用 Cloud Dataprep 处理这些转换。

4. 构建 Cloud Dataprep 流

导入“Google 表单分析设计模式”更新

下载 Google 表单分析设计模式流程包（无需解压缩）。在 Cloud Dataprep 应用中，点击左侧导航栏中的“流”图标。然后，在“流”页面中，从上下文菜单中选择“导入”。

ba7c0cb0eec398df.png

导入流后，选择导入的流进行修改，屏幕应如下所示：

44978861eb34ec71

关联 Google 表格调查问卷结果电子表格

在数据流的左侧，数据源必须重新连接到包含 Google 表单结果的 Google 表格。右键点击 Google 表格数据集对象，然后选择“替换”。

55c16f0c04366f0c

然后点击“导入数据集”链接。点击“修改路径”铅笔。

8afeef260c96277f

然后，将当前值替换为指向包含一些 Google 表单结果的 Google 表格的链接。您可以使用我们的示例或您自己的副本：https://docs.google.com/spreadsheets/d/1DgIlvlLceFDqWEJs91F8rt1B-X0PJGLY6shkKGBPWpk/edit?usp=sharing

点击“确定”然后点击“导入和添加到流"。返回到模态窗口后，点击“替换”按钮。

连接 BigQuery 表

在流程的右侧，您需要将输出连接到您自己的 BigQuery 实例。对于每个输出，请点击相应图标，然后按如下方式修改其属性。

首先，修改“手动”目标

a3fc2cb80153ec25.png

在下面的“发布设置”中屏幕上，点击“修改”按钮

85791e6162a370de

当您看到“发布操作”屏幕上，您需要点击 BigQuery 连接并修改其属性来更改连接设置。

1f3e4887baaeaffd

选择您要将 Google 表单结果加载到其中的 BigQuery 数据集。您可以选择“默认”（如果您尚未创建任何 BigQuery 数据集）。

f4eaa05ecf9de162.png

修改“手动设置的目标平台”后，继续以相同的方式设置“预定的目标平台”输出。

46edea1b8ca63270

按照相同的步骤迭代每个输出。您总共需要修改 8 个目的地。

5. Cloud Dataprep 流程介绍

“Google 表单分析设计模式”的基本理念流程就是对调查问卷回复执行转换（如前所述），将每个问题类别分解为特定的 Cloud Dataprep 数据转换配方。

此流程将问题划分为 4 个表格（为简单起见，分别对应于 4 个问题类别）

afa421849b1bd398.png

建议你从“Clean Headers”开始逐一探索每个食谱然后点击“SingleChoiceSELECT-Questions”后面依次跟着其他食谱。

所有方案都添加了注释，以解释各种转换步骤。在配方中，您可以修改某个步骤并预览特定列之前/之后的状态。

449da06d96cd520e

4ac6e14f578d0707

6. 运行 Cloud Dataprep 流程

现在，您的来源和目标已正确配置，您可以运行数据流来转换响应并将其加载到 BigQuery 中。选择每个输出并点击“Run”按钮。如果指定的 BigQuery 表已存在，Dataprep 将附加新行，否则会创建一个新表。

47cf50f6d17a5b1e

点击“作业历史记录”图标以监控作业。系统可能需要几分钟时间才能继续操作并加载 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 开始分析此模式的示例调查问卷和 CRM 数据。只需创建一个新的 Looker 项目，添加 LookML，然后替换文件中的连接名称和表名称，使其与 BigQuery 配置相匹配即可。如果您没有 Looker 实例，但有兴趣了解更多信息，可以点击此处安排演示。

129db05d6f85f484

数据洞察

或者，要在数据洞察中创建报告，请点击带有 Google 叉号图标“空白报告”的框架并连接到 BigQuery。按照数据洞察中的所有说明操作。如需了解更多信息，请点击此处查看快速入门以及数据洞察主要功能简介。您还可以在此处找到我们预构建的数据洞察信息中心。

5e744869e3fe3f8f

9. 清理

若要避免产生费用，最简单的方法是删除您为本教程创建的 Cloud 项目。或者，您也可以删除各个资源。

  1. 在 Cloud 控制台中，前往“管理资源”
  2. 在项目列表中，选择要删除的项目，然后点击删除。
  3. 在对话框中输入项目 ID，然后点击关停以删除项目。