使用 BigQuery ML 构建电子商务推荐系统

了解如何使用 BigQuery 机器学习构建推荐系统,以根据 BigQuery 中的客户数据生成产品或服务推荐。然后,了解如何通过将数据导出到 Google Analytics 360 或 Cloud Storage,或以编程方式从 BigQuery 表中读取数据,将这些数据提供给其他生产系统。

如果您有已位于 BigQuery 中的数据,我们推荐采用此方法。借助 BigQuery 机器学习,您可使用标准 SQL 查询创建机器学习模型,根据 BigQuery 中的客户数据对其进行训练,以及部署该模型。您无需将数据导出到其他产品或构建模型训练和部署流水线,并且 BigQuery 会自动扩缩来处理所需的任何计算资源。

您在本教程中创建的机器学习模型使用矩阵分解,这是一种根据用户偏好数据创建推荐系统的常见且有效的方法。如需详细了解此方法,请参阅矩阵分解

本教程使用在 BigQuery 上公开托管的 Google Analytics(分析)示例数据集。此数据集提供来自 Google Merchandise Store(销售 Google 品牌商品的真实电子商务商店)的 12 个月(2016 年 8 月至 2017 年 8 月)经过模糊处理的 Analytics 360 数据。

目标

  • 将示例数据处理为适合训练矩阵分解模型的格式。
  • 创建、训练和部署矩阵分解模型。
  • 从已部署模型获取客户最可能感兴趣的产品预测结果。
  • 将 BigQuery 中的预测数据导出到一个或多个其他产品,用于向客户进行推荐。

费用

本教程使用 Google Cloud 的以下收费组件:

  • BigQuery
  • BigQuery ML

您可使用价格计算器根据您的预计使用量来估算费用。 Google Cloud 新用户可能有资格申请免费试用

准备工作

  1. 在 Google Cloud Console 的项目选择器页面上,选择或创建一个 Google Cloud 项目。

    转到“项目选择器”

  2. 确保您的 Cloud 项目已启用结算功能。 了解如何确认您的项目是否已启用结算功能

完成本教程后,您可以删除所创建的资源以避免继续计费。如需了解详情,请参阅清理

处理示例数据

使用矩阵分解时,可评估显式或隐式用户反馈以确定客户偏好。为了使用显式反馈,数据集必须包含有关用户产品偏好的数据,例如 1 到 5 的星级。如果没有显式反馈可用,则必须使用其他行为指标来推断客户偏好,例如查看用户在商品详情页面上花费的总时间。这是本教程中使用的方法。

如需训练矩阵分解模型,您需要一个表,其中的列可用于标识客户、所评级商品和隐式评级。在本部分中,您将创建一个这样的表,其中包含 useriditemIdsession_duration 列,其中 session_duration 列包含用户会话用于指定商品产品页面的时长。

按照以下步骤,使用 Google Analytics(分析)示例数据集中的数据创建这种表:

  1. 在 Google Cloud Marketplace 中打开 Google Analytics(分析)示例数据集,然后点击查看数据集。这将打开已选择 Google Analytics(分析)示例数据集的 BigQuery 控制台。
  2. 资源部分,选择要在其中完成本教程的项目。
  3. 点击创建数据集

    显示“创建数据集”按钮的位置。

  4. 创建数据集页面中执行以下操作:

    • 对于数据集 ID,请输入 bqml
    • 数据位置部分,选择离您最近的任意位置。
    • 点击创建数据集
  5. 如果您计划将数据导出到 Analytics 360,请执行此步骤,否则请跳过此步骤。

    查询编辑器窗格中,运行以下 SQL 语句将 Google Analytics(分析)示例数据的子集复制到新表中,并填充 clientId 字段,此字段将用作在下一步中汇总隐式用户反馈的键:

    CREATE OR REPLACE TABLE bqml.ga_clientid_sample AS (
      SELECT *
      FROM `bigquery-public-data.google_analytics_sample.ga_sessions_2017*`
      LIMIT 100000);
    
     UPDATE bqml.ga_clientid_sample
       SET clientId = fullvisitorId
       WHERE true;
    

    将受众群体数据导入 Analytics 360 时,必须使用 clientId 作为键字段。clientId 通常是 fullVisitorId 的哈希版本,但它并未填充到 Google Analytics(分析)示例数据集中。如需在您自己的 Analytics 360 数据中填充 clientId,您需要创建一个自定义维度并进行填充。

  6. 运行以下 SQL 语句以创建包含训练数据的表。如果您计划将数据导出到 Analytics 360,请运行使用 clientId 字段作为键的版本。如果您计划在其他营销系统使用相应数据,请运行使用 fullVisitorId 字段作为键的版本。

    clientId

    CREATE OR REPLACE TABLE bqml.aggregate_web_stats AS (
      WITH
        durations AS (
          --calculate pageview durations
          SELECT
            CONCAT(clientId,'-',
                 CAST(visitNumber AS STRING),'-',
                 CAST(hitNumber AS STRING) ) AS visitorId_session_hit,
            LEAD(time, 1) OVER (
              PARTITION BY CONCAT(clientId,'-',CAST(visitNumber AS STRING))
              ORDER BY
              time ASC ) - time AS pageview_duration
          FROM
            `bqml.ga_clientid_sample`,
            UNNEST(hits) AS hit
        ),
        prodview_durations AS (
          --filter for product detail pages only
         SELECT
            CONCAT(clientId,'-',CAST(visitNumber AS STRING)) AS userId,
            productSKU AS itemId,
            IFNULL(dur.pageview_duration,
             1) AS pageview_duration,
          FROM
            `bqml.ga_clientid_sample` t,
            UNNEST(hits) AS hits,
            UNNEST(hits.product) AS hits_product
          JOIN
            durations dur
          ON
            CONCAT(clientId,'-',
                   CAST(visitNumber AS STRING),'-',
                   CAST(hitNumber AS STRING)) = dur.visitorId_session_hit
          WHERE
          eCommerceAction.action_type = "2"
        ),
        aggregate_web_stats AS(
          --sum pageview durations by userId, itemId
          SELECT
            userId,
            itemId,
            SUM(pageview_duration) AS session_duration
          FROM
            prodview_durations
          GROUP BY
            userId,
            itemId )
        SELECT
         *
       FROM
          aggregate_web_stats
    );
    

    fullVisitorId

    CREATE OR REPLACE TABLE bqml.aggregate_web_stats AS (
      WITH
        durations AS (
          --calculate pageview durations
          SELECT
            CONCAT(fullVisitorId,'-',
                 CAST(visitNumber AS STRING),'-',
                 CAST(hitNumber AS STRING) ) AS visitorId_session_hit,
            LEAD(time, 1) OVER (
              PARTITION BY CONCAT(fullVisitorId,'-',CAST(visitNumber AS STRING))
              ORDER BY
              time ASC ) - time AS pageview_duration
          FROM
            `bigquery-public-data.google_analytics_sample.ga_sessions_2017*`,
            UNNEST(hits) AS hit
        ),
        prodview_durations AS (
          --filter for product detail pages only
         SELECT
            CONCAT(fullVisitorId,'-',CAST(visitNumber AS STRING)) AS userId,
            productSKU AS itemId,
            IFNULL(dur.pageview_duration,
             1) AS pageview_duration,
          FROM
            `bigquery-public-data.google_analytics_sample.ga_sessions_2017*` t,
            UNNEST(hits) AS hits,
            UNNEST(hits.product) AS hits_product
          JOIN
            durations dur
          ON
            CONCAT(fullVisitorId,'-',
                   CAST(visitNumber AS STRING),'-',
                   CAST(hitNumber AS STRING)) = dur.visitorId_session_hit
          WHERE
          eCommerceAction.action_type = "2"
        ),
        aggregate_web_stats AS(
          --sum pageview durations by userId, itemId
          SELECT
            userId,
            itemId,
            SUM(pageview_duration) AS session_duration
          FROM
            prodview_durations
          GROUP BY
            userId,
            itemId )
        SELECT
         *
       FROM
          aggregate_web_stats
    );
    
  7. 运行以下 SQL 语句以在生成的 bqml.aggregate_web_stats 表中查看日期示例:

    SELECT
     *
    FROM
      bqml.aggregate_web_stats
    LIMIT
      10;
    

    您应该会看到如下所示的结果:

    已处理的训练数据的前 10 行。

购买灵活槽

如果您为 BigQuery 使用按需价格,则必须购买灵活槽,然后为其创建预留和分配,以训练矩阵分解模型。如果您在 BigQuery 中使用固定价格,则可以跳过本部分。

您必须具有 bigquery.reservations.create 权限才能购买灵活槽。将此权限授予项目所有者以及 bigquery.adminbigquery.resourceAdmin 预定义 Identity and Access Management 角色。

  1. 在 BigQuery 控制台中,点击预留
  2. 如果系统将您重定向到 BigQuery Reservation API 页面以启用该 API,请点击启用。否则,请继续下一步。
  3. 预留页面上,点击购买槽
  4. 购买槽页面上,按如下方式设置选项:

    1. 承诺期限中,选择灵活
    2. 位置中,选择在处理示例数据过程中创建数据集时选择的任意位置。
    3. 槽数中,选择 500
    4. 点击下一步
    5. 确认购买中,输入 CONFIRM

  5. 点击购买

  6. 点击查看槽承诺

  7. 请等待 20 分钟,让系统完成容量预配。预配容量之后,槽承诺状态将变为绿色并显示对勾标记

  8. 点击创建预留

  9. 创建预留页面上,按如下方式设置选项:

    1. 预留名称中,输入 model
    2. 位置中,选择您购买灵活槽所在的任意位置。
    3. 槽数中,输入 500
    4. 点击保存。这将返回到预留页面。
  10. 选择分配标签页。

  11. 选择组织、文件夹或项目中,点击浏览

  12. 输入您要在其中完成本教程的项目名称。

  13. 点击选择

  14. 预留中,选择您创建的模型预留。

  15. 点击创建

  16. 点击 BigQuery 以返回 BigQuery 控制台。

创建、训练和部署模型

运行 CREATE MODEL SQL 语句以创建、训练和部署矩阵分解模型:

      CREATE OR REPLACE MODEL bqml.retail_recommender
      OPTIONS(model_type='matrix_factorization',
            user_col='userId',
            item_col='itemId',
            rating_col='session_duration',
            feedback_type='implicit'
            )
      AS
      SELECT * FROM bqml.aggregate_web_stats;

训练完成后,经过训练的模型将部署为 bqml.retail_recommender

使用经过训练的模型进行预测

使用 ML.RECOMMEND SQL 函数从已部署 bqml.retail_recommender 模型中获取预测。

  1. 如需查看推荐数据的示例,请运行以下 SQL 语句以获取表示指定 userId 的前 5 项推荐的预测:

    DECLARE MY_USERID STRING DEFAULT "0824461277962362623-1";
    
    SELECT
      *
    FROM
      ML.RECOMMEND(MODEL `bqml.retail_recommender`,
      (SELECT MY_USERID as userID)
                  )
    ORDER BY predicted_session_duration_confidence DESC
    LIMIT 5;
    

    您应该会看到如下所示的结果:

    给定用户 ID 的前 5 项推荐。

  2. 运行以下 SQL 语句以获取所有用户的前 5 项预测。这会生成大量的行,因此该输出将写入表中,然后检索前十条记录,以便您查看数据示例。

    -- Create output table of top 5 predictions
    CREATE OR REPLACE TABLE bqml.prod_recommendations AS (
    WITH predictions AS (
        SELECT
          userId,
          ARRAY_AGG(STRUCT(itemId,
                           predicted_session_duration_confidence)
                    ORDER BY
                      predicted_session_duration_confidence DESC
                    LIMIT 5) as recommended
        FROM ML.RECOMMEND(MODEL bqml.retail_recommender)
        GROUP BY userId
    )
    
    SELECT
      userId,
      itemId,
      predicted_session_duration_confidence
    FROM
      predictions p,
      UNNEST(recommended)
    );
    
    -- Show table
    SELECT
     *
    FROM
      bqml.prod_recommendations
    ORDER BY
      userId
    LIMIT
      10;
    

    您应该会看到如下所示的结果:

    所有用户的前 10 项推荐。

在生产环境中使用预测推荐

获取推荐后,如何将它们提供给生产流水线取决于您的使用场景。以下几个部分介绍如何将预测数据导出至 Analytics 360 或 Cloud Storage,或者以编程方式将 BigQuery 中的数据读入 Pandas DataFrame。

将推荐导出到 Analytics 360

如果要将数据导出到 Analytics 360,我们建议您为每款产品提供一列,以对客户购买相应产品的可能性进行评分,如下所示:

clientId likelihoodProductA likelihoodProductB
123 .6527238 .3464891
456 .8720673 .2750274
789 .5620734 .9127595

如需为每个产品创建“购买可能性”列,请按照一步在 BigQuery 中轻松执行 pivot() 中介绍的方式创建 pivot() 过程。

  1. 运行以下 SQL 语句以创建 pivot 过程:

    CREATE OR REPLACE FUNCTION
    `bqml.normalize_col_name`(col_name STRING) AS (
      REGEXP_REPLACE(col_name,r'[/+#|]', '_'
    ));
    
    CREATE OR REPLACE PROCEDURE `bqml.pivot`(
      table_name STRING
      , destination_table STRING
      , row_ids ARRAY<STRING>
      , pivot_col_name STRING
     , pivot_col_value STRING
      , max_columns INT64
      , aggregation STRING
      , optional_limit STRING
      )
    
    BEGIN
    
      DECLARE pivotter STRING;
    
      EXECUTE IMMEDIATE (
       "SELECT STRING_AGG(' "||aggregation
        ||"""(IF('||@pivot_col_name||'="'||x.value||'", '||@pivot_col_value||', null)) e_'||bqml.normalize_col_name(x.value))
       FROM UNNEST((
           SELECT APPROX_TOP_COUNT("""||pivot_col_name||", @max_columns) FROM `"||table_name||"`)) x"
      ) INTO pivotter
      USING pivot_col_name AS pivot_col_name, pivot_col_value AS pivot_col_value, max_columns AS max_columns;
    
      EXECUTE IMMEDIATE (
       'CREATE OR REPLACE TABLE `'||destination_table
       ||'` AS SELECT '
       ||(SELECT STRING_AGG(x) FROM UNNEST(row_ids) x)
       ||', '||pivotter
       ||' FROM `'||table_name||'` GROUP BY '
       || (SELECT STRING_AGG(''||(i+1)) FROM UNNEST(row_ids) WITH OFFSET i)||' ORDER BY '
       || (SELECT STRING_AGG(''||(i+1)) FROM UNNEST(row_ids) WITH OFFSET i)
       ||' '||optional_limit
      );
    
    END;
    
  2. 运行以下 SQL 语句以创建包含每款产品的 clientId 和“购买可能性”列的表:

    CALL bqml.pivot(
      'bqml.prod_recommendations' # source table
      , 'bqml.prod_recommendations_export' # destination table
      , ['userId'] # row IDs
      , 'itemId' # pivot column name
      , 'predicted_session_duration_confidence' # pivot column value
      , 30 # max number of columns
      , 'AVG' # aggregation
      , '' # optional_limit
    );
    
  3. 运行以下 SQL 语句以在生成的 bqml.prod_recommendations_export 表中查看日期示例:

    SELECT
      *
    FROM
      bqml.prod_recommendations_export
    ORDER BY
      userId
    LIMIT
      10;
    

    您应该会看到如下所示的结果:

    所有用户的前 10 项推荐。

数据格式正确后,请将其保存为 CSV 文件,然后使用数据导入将数据导入 Analytics 360 中。请注意,导出的推荐数据中的列名称必须映射到 Analytics 360 数据导入架构。例如,如果数据导入架构是 ga:clientId, ga:dimension1, ga:dimension2,则数据中的列名称应为 ga:clientId, ga:dimension1, ga:dimension2。BigQuery 不允许在列名称中使用英文冒号,因此您必须先在导出的 CSV 文件中更新列名称,然后才能导入。

如果需要,您可以使用 BigQuery 机器学习模型MoDeM(营销模型部署)参考实现来方便将数据加载到 Analytics 360 中。按照 BQML 部署模板笔记本中的互动式说明开始操作。

将推荐导出到 Cloud Storage

按照导出表数据中的说明,将 BigQuery 表中的推荐数据导出到 Cloud Storage。

以编程方式读取推荐

按照使用 BigQuery 客户端库下载表数据中的说明,使用 BigQuery Storage API 将推荐数据从 BigQuery 表读入 Pandas DataFrame 中。或者,您可以使用其中一个 BigQuery 客户端库给自己的解决方案编程。

总结

您已完成本教程,现在了解如何使用 BigQuery ML 训练 Recommender 系统、部署模型和将结果用于生产环境。

清理

为避免系统因本教程中使用的资源而向您的 Google Cloud 帐号收取费用,请删除包含资源的项目,或者保留项目但仅删除这些资源。

无论采用哪种方式,您都应移除这些资源,以免日后再为这些资源付费。以下部分介绍如何删除这些资源。

删除项目

为避免支付费用,最简单的方法是删除您为本教程创建的项目。

  1. 在 Cloud Console 中,转到管理资源页面。

    转到“管理资源”

  2. 在项目列表中,选择要删除的项目,然后点击删除
  3. 在对话框中输入项目 ID,然后点击关闭以删除项目。

删除组件

如果您不想删除项目,请使用以下几个部分删除本教程的可计费组件。

删除 BigQuery 数据集

  1. 打开 BigQuery 控制台
  2. 资源部分中,展开要在其中完成本教程的项目,然后选择 bqml 数据集。
  3. 点击数据集窗格标题中的删除数据集
  4. 在出现的叠加窗口中,输入 bqml,然后点击删除

删除灵活槽

如果您创建了灵活槽,请执行以下步骤将其删除:

  1. 在 BigQuery 控制台中,点击预留
  2. 选择分配标签页。
  3. 找到您为模型预留创建的分配行,然后点击操作列中的更多 列,接着点击删除
  4. 选择预留标签页。
  5. 找到模型预留行,点击操作列中的更多 ,然后点击删除
  6. 选择槽承诺标签页。
  7. 找到包含 500 个灵活槽数的行,点击操作列中的更多 ,然后点击删除
  8. 确认移除槽承诺中,输入 REMOVE
  9. 点击继续

后续步骤