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

1. 简介

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

构建内容

在此 Codelab 中，您将使用 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 流

在 Cloud Dataprep 中导入“Google 表单分析设计模式”

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

ba7c0cb0eec398df.png

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

44978861eb34ec71.png

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

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

55c16f0c04366f0c.png

然后，点击该模态窗口底部的“导入数据集”链接。点击“修改路径”铅笔图标。

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 Flow

现在，您的来源和目标位置已正确配置，您可以运行该流程，将回答转换并加载到 BigQuery 中。选择每个输出，然后点击“运行”按钮。如果指定的 BigQuery 表存在，Dataprep 会附加新行；否则，它会创建一个新表。

47cf50f6d17a5b1e.png

点击左侧窗格中的“作业历史记录”图标，以监控作业。系统应会在几分钟内继续操作并加载 BigQuery 表。

afc79eeb27202fb4.png

所有作业完成后，调查结果将以干净、结构化且规范化的格式加载到 BigQuery 中，以便进行分析。

7. 在 BigQuery 中分析调查数据

在 Google Console for 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 控制台中，前往“管理资源”页面
  2. 在项目列表中，选择要删除的项目，然后点击删除。
  3. 在对话框中输入项目 ID，然后点击关停以删除项目。