用 BigQuery ML 和 Google Sheets 数据预测电商网站访客趋势

看看如何使用 BigQuery ML 与 Google Sheets 构建时间预测模型，为商业分析提供助力~
电子表格无处不在！作为最实用的生产力工具之一，Google Workspace 的 Sheets 电子表格工具拥有超过 20 亿用户，可让数据的组织、计算和呈现变得轻而易举。同时，机器学习（简称 ML）也是一项重要的商业工具，能以低成本、高精度进行数据预测。据估计，机器学习在商业领域的应用每年增长超过 40%。

想象一下，如果能够将机器学习的能力应用于电子表格数据，岂不是很有意义？Absolutely！今天，就让我们以“电商网站访客趋势预测”为场景，看看如何使用 BigQuery ML 与 Google Sheets 构建时间预测模型，为商业分析提供助力~

实践流程与背景说明

Google BigQuery 平台内置的 ML 工具，让用户能使用标准 SQL 查询创建机器学习模型。在本文中，我们将讨论如何使用 BigQuery ML 创建时间序列预测模型。

时间序列模型的输入是一系列历史值，输出是一系列未来的预测值。之所以选择这种模型，是因为时间序列数据在电子表格中非常常见。你可以回想一下遇过的分析场景，它们可能是一组有序的日期或数值类型的数据，如销售额、人员配备、运营指标等。

值得一提的是，BigQuery ML 支持神经网络、AutoML 模型、TensorFlow 模型等多种模型。并且，你不需要清楚知道模型是如何构建的，只需提供数据并选择合适的选项，BigQuery ML 就会为你构建和托管模型。

图示：BigQuery ML 模型选择决策树图

此外，要构建模型，你还需要加入训练数据来让它学习模式。幸运的是，这些数据就在你的电子表格中！只需使用 Google Sheets 中的 Connected Sheets（关联工作表）选项，你就可以直接从 BigQuery 访问大量数据，从而确保能够始终使用最新、最安全的数据。

最后，要从 Google Sheets 中执行 BigQuery ML 查询，需用到 Apps Script，这是一个基于云的 JavaScript 平台，可用于扩展 Google Workspace 组件功能。

具体的流程是，Apps Script 代码将从电子表格中提取输入模型的数据，然后执行 BigQuery ML 查询以进行训练和预测，并使用模型输出的预测值来更新电子表格。

表格数据与问题详情

举个例子，我们要使用 BigQuery 公共数据集中的 Google Analytics 数据，来预测电商网站的访问量。这份数据集包含 12 个月的流量、内容和交易数据。先来看一张根据小时来呈现网站访问量的数据图表：

可以看到，这份数据中包含了一些复杂的模式：

从图表中可以看到，访客数量随时间有明显的周期性（季节性）波动，这可能反映了用户访问的某种周期性行为，例如每日或每周的访问模式。

数据中有一些明显的高峰和低谷，显示了在某些时间段内访问量显著增加或减少。这些波动可能与特定事件、促销活动或其他特殊情况相关。

虽然整体趋势看似平稳，但仔细观察可以发现某些时期的访问量有所增加或减少，可能反映了长期趋势的变化。

除了周期性和趋势性变化外，数据中还存在一些随机波动，这些波动可能是由于用户行为的不确定性或外部环境的变化引起的。

BigQuery ML 中强大的预测功能可以很好地处理这些模式。此外，一些峰值可能会影响预测算法，但 BigQuery ML 能够提供自动异常值检测来管理这些事件。

》01 连接 BigQuery 中的数据

无论你的数据来自何处，BigQuery 的示例代码都能与数据兼容。如果你可以访问 BigQuery 中的业务数据，那么我们推荐你在 Google Sheets 中通过 Connected Sheets（关联工作表）来进行连接。

如下图，从 Google Sheets 菜单栏的“数据”-“数据连接器”选项中，选择“连接到 BigQuery”：

然后，按需依次选择云项目、数据集和表格，就完成 BigQuery 示例数据集连接了！

通过关联工作表，你可以使用已经熟悉的 Google Sheets 功能（如数据透视表、图表和公式）来解锁和洞察大数据集，且支持使用 BiqQuery 中的新数据自动刷新来进行更新。

在示例场景中，我们希望创建一个在某个时间点使用快照训练的模型。那么过程就像选择数据“提取”一样简单：

》02 在 Sheets 使用 BigQuery ML 模型

接下来，看看如何在 Google Sheets 用户界面中执行 BigQuery ML 命令。我们将通过 Google Workspace ml-integration-samples 的代码示例来展示如何做到这一点。

通过在 Google Sheets 工作表的菜单栏中打开脚本编辑器（扩展程序 - Apps 脚本），你可以编写 Apps Script 代码并配置集成。下图示例启用了两个高级服务（BigQuery 和 Google Sheets），以支持在脚本中使用 API。

让我们先看一下添加到 Google Sheets 的菜单，看看如何将其链接到项目中的代码：

onOpen() 函数会在电子表格打开时被触发。可以看到它创建了几个菜单项，每个菜单项都链接到函数。例如，“Train” 菜单项将在选中时调用 train() 函数。

/**

• Create menu items linked to functions

*/

function onOpen() {

SpreadsheetApp.getUi()

.createMenu(‘BQML’)

.addItem(‘Train’, ‘train’)

.addItem(‘Forecast’, ‘forecast’)

.addSeparator()

.addSubMenu(SpreadsheetApp.getUi().createMenu(‘Configure’)

.addItem(‘Project’, ‘configureProject’)

.addItem(‘Dataset’, ‘configureDataset’))

.addToUi();

}

》03 模型训练

现在，让我们看看训练函数如何处理提供的这些自定义数据。比如，用户在 Google Sheets 工作表中选择一个区域，每个单元格都包含一个日期和要预测的数据。训练代码会提取这些数据值并填充到 Range 对象中。

然后，将这些行插入到临时的 BigQuery 表中。最后，执行查询以使用表中的数据创建时间序列模型。

/**

• Create a forecasting model based on the input data

*/

function train() {

// …

// Populate temporary table in BigQuery with selected data from sheet

const inputs = SpreadsheetApp.getActiveRange().getValues();

populateTable(project, table, [DATETIME_COLUMN, DATA_COLUMN], inputs);

// Create a new model using training data in BigQuery

const request = {

query: 'CREATE OR REPLACE MODEL ' + model + ' ’ +

getOptionsStr(CREATE_OPTIONS) + ’ AS SELECT * FROM ' + table + '',

useLegacySql: false,

};

runQuery(request, project);

}

现在，我们来看一个可以由训练代码生成的示例查询。它指定模型类型为“ARIMA”（用于时间序列），以及临时表中的时间戳和数据列。BigQuery ML 中还有其他选项可用（例如节假日），但未包含在本示例中。

CREATE OR REPLACE MODEL

sheets_forecast.sheets_forecast_model OPTIONS( MODEL_TYPE=‘ARIMA’,

TIME_SERIES_TIMESTAMP_COL=‘datetime’,

TIME_SERIES_DATA_COL=‘data’) AS

SELECT

FROM

sheets_forecast.sheets_forecast_training_data

》04 创建预测

要进行预测，你需要在电子表格中选择多行，然后在 Google Sheets 菜单中选择“预测”。脚本将调用模型上的 ML.FORECAST() 函数，并将“范围”指定为所选的行数。

对于每个时间段，将从预测中提取两个字段：

预测时间戳，格式化为包含日期、时间和时区的字符串。

该时间戳的预测值。

假设，我们选择了 3 行，查询可能如下所示：

SELECT

FORMAT_TIMESTAMP(“%FT%T%Ez”, forecast_timestamp),

forecast_value

FROM

ML.FORECAST(MODEL sheets_forecast.sheets_forecast_model,

STRUCT(3 AS horizon))

如此，预测结果就生成好了！如下图所示，可以在 Google Sheets 中看到这个电商网站最后一周的实际访问数据为蓝色，最后 3 天的预测数据为红色。

立即跟随示例开始实践

结合使用 Google Sheets 与 BigQuery ML ，你可以将机器学习的强大能力应用于电子表格中的任何数据。在整个过程中，你只需要拥有足够多的高质量数据来从中提取有意义的模式。

此外，你可以查看代码示例，展示了只要数据具有日期和数值，训练过程如何处理所有类型的数据。

你甚至可以根据需求，修改示例以使用不同的模型类型和不同的选项，或者处理不同的电子表格布局。然后，业务用户可以简单地使用菜单构建新模型并进行预测。

最后，想要跟着操作的数据粉，也可以通过一个预装了代码示例的模板表格来快速入门。你还可以按照代码示例中的说明自行安装。

如果还想获得更多 BigQuery ML 实践经验，推荐观看 Code Lab 教程。有了 Google Sheets 和 BigQuery ML，你将能解决比以往更多的问题！

除了上述实践之外，其实 Google Sheets 还提供了许多便捷功能，大大提升了出海企业财务团队的数据分析和工作效率。

👉 如果你想进一步探索 Google Sheets ，申请试用企业版 Google Workspace办公协作组件，解锁和体验由 Gemini AI 驱动的全新办公协作方式吧~

👉下载 Gemini 提示词使用手册，解锁如何在不同业务场景中使用 AI 提升生产力！