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

1. 简介

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

构建内容

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

学习内容

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

所需条件

2. 管理 Google 表单回复

我们先来仔细看看示例调查问卷收到的 Google 表单回复。

f3d25efd2cc923f5.png

如需导出调查问卷结果,您可以点击“回复”标签页中的 Google 表格图标,然后创建新的电子表格或将结果加载到现有电子表格中。在您取消选择“接受回复”按钮之前,Google 表单会继续将回复者提交的回复添加到电子表格中。

d499e5a4dccdf5fd.png4939332a5d8f9f19.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 应用中,点击左侧导航栏中的“流”图标。然后,在“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

点击“Go”(前往),然后点击右下角的“Import & Add to Flow”(导入并添加到流)。返回到模态窗口后,点击右下角的“替换”按钮。

连接 BigQuery 表

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

首先,修改“手动目标位置”

a3fc2cb80153ec25.png

在随后的“发布设置”界面中,点击修改按钮

85791e6162a370de.png

看到“发布操作”界面后,您需要点击 BigQuery 连接并修改其属性,以更改连接设置。

1f3e4887baaeaffd.png

选择要将 Google 表单结果加载到的 BigQuery 数据集。如果您尚未创建任何 BigQuery 数据集,可以选择“default”。

f4eaa05ecf9de162.png

修改“手动目标位置”后,对“预定目标位置”输出执行相同的操作。

46edea1b8ca63270.png

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

5. Cloud Dataprep 流程说明

“Google 表单分析设计模式”流程的基本思路是,按照前面所述对调查问卷回复执行转换,即针对每个问题类别分别制定一个 Cloud Dataprep 数据转换配方。

此流程将问题分为 4 个表(为简单起见,对应 4 个问题类别)

afa421849b1bd398.png

我们建议您逐一探索每个方案,从“清理标题”开始,然后是“单选 SELECT 问题”,接着是其下的每个其他方案。

所有配方都带有注释,用于解释各种转换步骤。在配方中,您可以修改步骤并预览特定列的前后状态。

449da06d96cd520e.png4ac6e14f578d0707.png

6. 运行 Cloud Dataprep 流程

现在,您的来源和目标位置已正确配置,您可以运行该流,以转换回复并将其加载到 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,然后点击关停以删除项目。