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

1. 简介

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

构建内容

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

学习内容

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

所需条件

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

多选多选网格题

以下是复选框网格的示例。您可以在每行中选择 0 到多个值。

4e3189b8cc2d4a8b.png

  • 题目名称：每个单独的题目都会成为一个列名称，格式为“题目 [选项]”。
  • 响应：网格中的每个单独响应都会成为一个列，其中包含以英文分号分隔的值列表。
  • 转换要求：这些题目类型结合了“复选框”和“多选题网格”类别，必须按此顺序解决。

首先，需要提取并转换每个回答的值列表，以便每个答案都成为特定问题的新行。

第二：每个单独的回答都必须成为表格中的新行，并拆分为两列。一列包含题目选项，另一列包含答案。

3c3c2bd098e03003.png

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

4. 构建 Cloud Dataprep 流

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

下载 Google 表单 Google Analytics 设计模式流程软件包（无需解压缩）。在 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

在随即显示的“Publishing Settings”（发布设置）屏幕中，点击“Edit”（修改）按钮

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. 执行可视化分析

现在，您的调查问卷数据集中在数据仓库中，您可以轻松地在业务智能工具中分析这些数据。我们在 Data Studio 和 Looker 中创建了一些示例报告。

Looker

如果您已经有 Looker 实例，可以使用此文件夹中的 LookML 开始分析此模式的样本调查问卷和 CRM 数据。只需创建一个新的 Looker 项目、添加 LookML，然后替换文件中的连接和表名称，使其与您的 BigQuery 配置相匹配即可。如果您还没有 Looker 实例，但有兴趣了解详情，可以点击此处预约演示。

129db05d6f85f484.png

数据洞察

或者，如需在 Data Studio 中创建报告，请点击带有 Google 十字图案的框架“空白报告”，然后连接到 BigQuery。请按照 Data Studio 中的所有说明操作。如需了解详情，请点击此处，快速上手并了解 Data Studio 的主要功能。您还可以在此处找到我们预构建的数据洞察信息中心。

5e744869e3fe3f8f.png

9. 清理

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

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