优化零售产品的价格

1. 简介

上次更新日期：2021 年 9 月 15 日

推动价格分析和优化所需的数据在本质上是完全独立的（不同的系统、不同的当地情况等），因此开发结构良好、标准化且清晰的 CDM 表至关重要。这包括用于价格优化的关键属性，例如交易、商品、价格和客户。在本文档中，我们将引导您完成下面列出的步骤，为价格分析提供快速入门。您可以根据自己的需求扩展和自定义价格分析。下图展示了本文档中介绍的步骤。

1. 评估数据源：首先，您必须获得将用于创建 CDM 的数据源的清单。在此步骤中，系统还会使用 Dataprep 来探索和识别输入数据中的问题。例如，值缺失和不匹配、命名惯例不一致、重复、数据完整性问题、离群值等。
2. 对数据进行标准化：接下来，修复之前发现的问题，以确保数据的准确性、完整性、一致性和完整性。此过程可能涉及 Dataprep 中的各种转换，例如日期格式、值标准化、单位转换、过滤掉不必要的字段和值，以及拆分、联接或删除源数据。
3. 在一个结构中统一：流水线的下一阶段会将每个数据源联接到一个 BigQuery 中的单个宽表中，该表中以最精细的级别包含所有属性。这种反规范化结构可实现高效的分析查询，且无需联接。
4. 提供数据分析并机器学习/AI：在数据干净且设置好格式以供分析后，分析师可以探索历史数据，以了解之前价格变化的影响。此外，您还可以使用 BigQuery ML 创建预测模型，用于估算未来销售额。这些模型的输出可整合到 Looker 内的信息中心，以创建“假设场景”，商家用户可以分析特定价格变动可能带来的销售额变化。

下图显示了用于构建价格优化分析流水线的 Google Cloud 组件。

构建内容

在本文中，我们将向您介绍如何设计价格优化数据仓库，逐步自动准备数据，使用机器学习技术预测对产品价格更改的影响，以及如何生成报告，为您的团队提供富有实用价值的分析洞见。

学习内容

• 如何将 Dataprep 连接到可存储在关系型数据库、平面文件、Google 表格和其他受支持的应用中且用于价格分析的数据源。
• 如何构建 Dataprep 流程，以在 BigQuery 数据仓库中创建 CDM 表。
• 如何使用 BigQuery ML 预测未来收入。
• 如何在 Looker 中生成报告，以分析历史价格和销售趋势，并了解未来价格变动的影响。

所需条件

• 启用了结算功能的 Google Cloud 项目。了解如何确认您的项目是否已启用结算功能。
• 必须为您的项目启用 BigQuery。新项目会自动启用该功能。否则，请为现有项目启用该 API。您还可以在此处详细了解如何通过 Cloud 控制台开始使用 BigQuery。
• 此外，您还必须在项目中启用 Dataprep。您可以从 Google 控制台的“大数据”部分的左侧导航菜单中启用 Dataprep。按照注册步骤激活该卡。
• 如需设置您自己的 Looker 信息中心，您必须拥有 Looker 实例的开发者访问权限。如需申请试用，请点击此处与我们的团队联系，或使用我们的公共信息中心探索数据流水线对我们的示例数据的结果。
• 熟悉结构化查询语言 (SQL) 并具备以下基础知识：Dataprep by Trifacta、BigQuery、Looker

注意：如需了解本文档中使用的 Dataprep 资产，请参阅此 GitHub 代码库中的数据驱动型定价策略和优化框架 。如需了解 Looker 数据模型和信息中心，请参阅什么是 LookML？以及关联的 GitHub 代码库。

2. 在 BigQuery 中创建 CDM

在本部分中，您将创建通用数据模型 (CDM)，它提供了一个汇总视图，其中包含分析和提出价格更改建议所需的信息。

1. 打开 BigQuery 控制台。
2. 选择要用于测试此参考模式的项目。
3. 使用现有数据集或创建 BigQuery 数据集。将数据集命名为 Pricing_CDM。
4. 创建表：

create table `CDM_Pricing`
(
  Fiscal_Date DATETIME,
  Product_ID STRING,
  Client_ID INT64,
  Customer_Hierarchy STRING,
  Division STRING,
  Market STRING,
  Channel STRING,
  Customer_code INT64,
  Customer_Long_Description STRING,
  Key_Account_Manager INT64,
  Key_Account_Manager_Description STRING,
  Structure STRING,
  Invoiced_quantity_in_Pieces FLOAT64,
  Gross_Sales FLOAT64,
  Trade_Budget_Costs FLOAT64,
  Cash_Discounts_and_other_Sales_Deductions INT64,
  Net_Sales FLOAT64,
  Variable_Production_Costs_STD FLOAT64,
  Fixed_Production_Costs_STD FLOAT64,
  Other_Cost_of_Sales INT64,
  Standard_Gross_Margin FLOAT64,
  Transportation_STD FLOAT64,
  Warehouse_STD FLOAT64,
  Gross_Margin_After_Logistics FLOAT64,
  List_Price_Converged FLOAT64
);

3. 评估数据源

在本教程中，您将使用存储在 Google 表格和 BigQuery 中的示例数据源。

• transactions Google 表格，其中每笔交易对应一行。它包含每种产品的销售数量、总销售额和相关费用等详细信息。
• 产品价格 Google 表格，其中包含指定客户每月每种产品的价格。
• company_descriptions BigQuery 表，包含具体客户信息。

可以使用以下语句创建此 company_descriptions BigQuery 表：

create table `Company_Descriptions`
(
 Customer_ID INT64,
 Customer_Long_Description STRING
);
insert into `Company_Descriptions` values (15458, 'ENELTEN');
insert into `Company_Descriptions` values (16080, 'NEW DEVICES CORP.');
insert into `Company_Descriptions` values (19913, 'ENELTENGAS');
insert into `Company_Descriptions` values (30108, 'CARTOON NT');
insert into `Company_Descriptions` values (32492, 'Thomas Ed Automobiles');

4. 构建流程

在此步骤中，您将导入一个示例 Dataprep 流，该流用于转换和统一上一部分列出 的示例数据集。流表示流水线或将数据集和配方组合在一起的对象，用于转换和联接数据集和配方。

1. 从 GitHup 下载价格优化模式流程包，但不要解压缩。此文件包含用于转换示例数据的定价优化设计模式流程。
2. 在 Dataprep 中，点击左侧导航栏中的“流”图标。然后，在“流”视图中，从上下文菜单中选择导入。导入流后，您可以选择该流进行查看和修改。

3. 在流程的左侧，“Product Pricing”（产品价格）和 Google 表格的三笔交易（每笔交易）都必须以数据集的形式关联。为此，请右键点击 Google 表格数据集对象，然后选择 Replace。然后点击导入数据集链接。点击“修改路径”如下图所示。

将当前值替换为指向 Google 表格的交易和产品价格 的链接。

如果 Google 表格包含多个标签页，您可以在菜单中选择要使用的标签页。点击修改并选择要用作数据源的标签，然后依次点击保存和导入和添加到流程。返回到模态窗口后，点击替换。在此流程中，每个工作表都表示为自己的数据集，以便在稍后的配方中演示如何联合不同的源。

注意：如果您要调整此模式，使其与您连接的自定义数据源一起使用，请点击“+ 新建”屏幕底部的按钮

4. 定义 BigQuery 输出表：

在此步骤中，您将关联每次运行 Dataoprep 作业时要加载的 BigQuery CDM_Pricing 输出表的位置。

在“流视图”(Flow View) 中，点击“架构映射输出”(Schema Mapping Output) 图标，然后在“详细信息”面板中点击“目的地”(Destinations) 标签页。在这里，您可以修改用于测试的手动目标位置输出，以及想要自动执行整个流程时使用的预定目标位置输出。为此，请按以下说明操作：

1. 修改“人工目标位置”：在“详细信息”面板中的“人工目标位置”部分下，点击“修改”按钮。在 Publishing Settings（发布设置）页面的“Publishing Actions”（发布操作）下方，如果已经存在发布操作，则“Edit it”（修改），否则请点击“Add Action”（添加操作）按钮。接着，将 BigQuery 数据集转到您在上一步中创建的 Pricing_CDM 数据集，然后选择 CDM_Pricing 表。确认已选中每次运行时附加到此表，然后点击添加，然后点击保存设置。
2. 修改“计划的目标位置”

在“详细信息”面板的“计划的目标”部分下，点击修改。

这些设置沿用自人工目标页面，您无需进行任何更改。点击“保存设置”。

手动目标位置允许您独立按需运行各个输出作业。这便于在流设计期间测试数据准备配方和验证结果。

如果您希望定期运行流，则可以使用计划目标位置。例如，您可能想要每天将 CDM 重新加载到 BigQuery，以便更新报告以获取最新答案。

5. 将数据标准化

提供的流程会合并交易数据，设置交易数据的格式并进行清理，然后将结果与公司说明和汇总的价格数据联接以生成报告。在这里，您将了解该流程的各个组成部分，如下图所示。

6. 探索事务数据方法

首先，您将探索用于准备事务数据的事务数据配方中发生了什么。点击“流视图”中的“事务数据”对象，在“详细信息”面板上，点击“修改配方”按钮。

“转换器”页面即会打开，“详细信息”面板中会显示配方。该配方中包含应用于数据的所有转换步骤。您可以在每个步骤之间点击以浏览配方，以查看配方中此特定位置的数据状态。

您还可以针对每个配方步骤点击“更多”菜单，然后选择“转到所选项目”或“修改”，探索转换的运作方式。

1. 联合事务：事务数据配方中的第一步联合事务存储在代表每个月的不同工作表中。
2. 实现客户描述标准化：方案中的下一步将实现客户描述标准化。也就是说，客户名称可能稍有变化，因此可能会与名称相似，因此我们希望将它们标准化为在名称上。该配方展示了两种可能的方法。首先，它利用了标准化算法，该算法可以配置不同的标准化选项，例如“Similar strings”（类似字符串）其中，包含共同字符的值聚集在一起，即“发音”其中，听起来相似的值都集中在一起。或者，您也可以使用公司 ID 在 BigQuery 表格中查找公司说明。

注意：虽然本教程提供了一个将客户说明标准化的示例，但为了优化价格，我们建议您对产品名称和日期格式等数据进行标准化。

您可以进一步探索配方，发现用于清理数据和设置数据格式的各种其他方法：删除行、基于模式设置格式、通过查找来丰富数据、处理缺失值或替换不需要的字符。

7. 探索产品价格数据方案

接下来，您可以探索“产品价格数据”配方中会发生什么，该配方会将准备好的交易数据合并到汇总的价格数据中。

点击页面顶部的定价优化设计模式，以关闭转换器页面并返回到流视图。然后，点击“产品定价数据”对象并修改配方。

1. 逆透视每月价格列：点击 2 到 3 个步骤之间的配方，以查看“逆透视”步骤之前的数据。您会注意到，数据在每个月的 1 月 21 日 3 月的不同列中包含交易价值。这种格式不便于在 SQL 中应用聚合（即总和、平均交易）计算。您需要对数据进行逆透视，使每一列都成为 BigQuery 表中的一行。该方案利用 unpivot 函数将 3 列转换为每月的一行，以便更轻松地进一步应用组计算。
2. 按客户、产品和日期计算平均交易价值：我们希望计算每个客户、每个产品和数据的平均交易价值。我们可以使用汇总函数来生成新表格（“分组依据为新表格”）。在这种情况下，数据会在组级别进行汇总，我们会丢失每笔交易的详细信息。或者，我们可以决定将详细信息和汇总值保留在同一个数据集中（选项“按新列分组”），这对于应用比率（即，商品类别对总收入的贡献比例）非常方便。您可以通过修改配方步骤 7 并选择“分组依据为新表”选项来尝试此行为或“按新列分组”看看有何不同
3. 联接定价日期：最后，联接用于将多个数据集合并为更大的数据集，向初始数据集添加列。在此步骤中，价格数据将与基于“Pricing Data.Product Code”的 Transactional Data Recipe 输出相联接。= 交易数据.SKU'以及“Pricing Data.Price Date”=“Transaction Data.Fiscal Date”

如需详细了解可通过 Dataprep 应用的转换，请参阅 Trifacta 数据整理备忘单

8. 探索架构映射方法

最后一个方案“架构映射”可确保生成的 CDM 表与现有 BigQuery 输出表的架构匹配。在这里，快速目标 功能使用模糊匹配来重新设置数据结构的格式，以匹配 BigQuery 表，从而比较两种架构并应用自动更改。

9. 一体化

现在，您已配置来源和目标，并且了解了流的步骤，接下来您可以运行流来转换 CDM 表并将其加载到 BigQuery 中。

1. 运行架构映射输出：在流视图中，选择“架构映射”输出对象，然后点击“运行”按钮。选择“Trifacta Photon”运行环境并取消选中“忽略配方错误”。然后点击“运行”按钮。如果指定的 BigQuery 表存在，Dataprep 将追加新行，否则会创建一个新表。
2. 查看作业状态：Dataprep 会自动打开“运行作业”页面，便于您监控作业的执行情况。系统可能需要几分钟时间才能继续操作并加载 BigQuery 表。作业完成后，价格 CDM 输出将以整洁有序、标准化的格式加载到 BigQuery 中，以供分析之用。

10. 提供数据分析并ML/AI

Google Analytics 前提条件

为了运行一些分析和获得有趣结果的预测模型，我们创建了一个规模更大且相关的数据集，以便发现具体的数据洞见。您需要先将此数据上传到 BigQuery 数据集，然后才能继续本指南。

• 从此 GitHub 代码库下载大型数据集
• 在适用于 BigQuery 的 Google 控制台中，前往您的项目和 CDM_Pricing 数据集。
• 点击菜单，然后打开数据集。我们将通过从本地文件加载数据来创建表。

点击“+ 创建表格”按钮并定义以下参数：

• 通过上传创建表，然后选择 CDM_Pricing_Large_Table.csv 文件
• 架构自动检测，检查架构和输入参数
• 高级选项、写入偏好设置、覆盖表

• 点击“创建表”

创建表并上传数据后，您应该会在适用于 BigQuery 的 Google 控制台中看到新表的详细信息，如下所示。借助 BigQuery 中的价格数据，我们可以轻松提出更全面的问题，以便更深入地分析价格数据。

11. 查看价格变更的影响

例如，您之前更改了商品价格，而订单行为发生了哪些变化，就可能需要进行分析。

1. 首先，创建一个临时表，每次商品价格发生变化时该表都会占一行，并提供该特定商品定价的相关信息，例如每个价格对应的订购数量以及与该价格相关的总净销售额。

create temp table price_changes as (
select
       product_id,
       list_price_converged,
       total_ordered_pieces,
       total_net_sales,
       first_price_date,
       lag(list_price_converged) over(partition by product_id order by first_price_date asc) as previous_list,
       lag(total_ordered_pieces) over(partition by product_id order by first_price_date asc) as previous_total_ordered_pieces,
       lag(total_net_sales) over(partition by product_id order by first_price_date asc) as previous_total_net_sales,
       lag(first_price_date) over(partition by product_id order by first_price_date asc) as previous_first_price_date
       from (
           select
               product_id,list_price_converged,sum(invoiced_quantity_in_pieces) as total_ordered_pieces, sum(net_sales) as total_net_sales, min(fiscal_date) as first_price_date
           from `{{my_project}}.{{my_dataset}}.CDM_Pricing` AS cdm_pricing
           group by 1,2
           order by 1, 2 asc
       )
);
select * from price_changes where previous_list is not null order by product_id, first_price_date desc

2. 接下来，使用临时表格，您可以计算不同 SKU 的平均价格变动：

select avg((previous_list-list_price_converged)/nullif(previous_list,0))*100 as average_price_change from price_changes;

3. 最后，您可以通过查看每次价格变动与订购商品总数之间的关系，分析价格变动后的情况：

select
(total_ordered_pieces-previous_total_ordered_pieces)/nullif(previous_total_ordered_pieces,0) 

CANNOT TRANSLATE

price_changes_percent_ordered_change,
(list_price_converged-previous_list)/nullif(previous_list,0)  

CANNOT TRANSLATE

price_changes_percent_price_change
from price_changes

12. 构建时序预测模型

接下来，借助 BigQuery 内置的机器学习功能，您可以构建 ARIMA 时序预测模型，预测每件商品的待售数量。

1. 首先，创建 ARIMA_PLUS 模型

create or replace `{{my_project}}.{{my_dataset}}.bqml_arima`
options
 (model_type = 'ARIMA_PLUS',
  time_series_timestamp_col = 'fiscal_date',
  time_series_data_col = 'total_quantity',
  time_series_id_col = 'product_id',
  auto_arima = TRUE,
  data_frequency = 'AUTO_FREQUENCY',
  decompose_time_series = TRUE
 ) as
select
 fiscal_date,
 product_id,
 sum(invoiced_quantity_in_pieces) as total_quantity
from
 `{{my_project}}.{{my_dataset}}.CDM_Pricing` 
group by 1,2;

2. 接下来，您使用 ML.FORECAST 函数来预测每种产品的未来销量：

select
*
from
ML.FORECAST(model testing.bqml_arima,
            struct(30 as horizon, 0.8 as confidence_level));

3. 获得这些预测结果后，您可以尝试了解提高价格可能会发生什么情况。例如，如果您将每种产品的价格提高 15%，则可以使用如下查询计算下个月的估算总收入：

select
sum(forecast_value * list_price) as total_revenue
from ml.forecast(mode testing.bqml_arima,
            struct(30 as horizon, 0.8 as confidence_level)) forecasts
left join (select product_id,
                array_agg(list_price_converged
                       order by fiscal_date desc limit 1)[offset(0)] as list_price
                from `leigha-bq-dev.retail.cdm_pricing` group by 1)  recent_prices
using (product_id);

注意：此查询假定价格变动不会对购买行为产生影响。

13. 制作报告

现在，您的反规范化价格数据已集中在 BigQuery 中，并且您已了解如何对此数据运行有意义的查询。接下来，您可以创建一个报告，让企业用户能够探索这些信息并据此采取行动。

如果您已有 Looker 实例，则可以使用此 GitHub 代码库中的 LookML 开始分析此模式的价格数据。只需创建一个新的 Looker 项目，添加 LookML，然后替换每个视图文件中的连接和表名，以与您的 BigQuery 配置匹配即可。

注意：如果您没有 Looker 实例，但有兴趣了解更多信息，可以在此处安排演示。

在此模型中，您会看到我们之前展示的派生表（在此视图文件中），用于检查价格变动：

view: price_changes {
    derived_table: {
      sql: select
        product_id,
        list_price_converged,
        total_ordered_pieces,
        total_net_sales,
        first_price_date,
        lag(list_price_converged) over(partition by product_id order by first_price_date asc) as previous_list,
        lag(total_ordered_pieces) over(partition by product_id order by first_price_date asc) as previous_total_ordered_pieces,
        lag(total_net_sales) over(partition by product_id order by first_price_date asc) as previous_total_net_sales,
        lag(first_price_date) over(partition by product_id order by first_price_date asc) as previous_first_price_date
        from (
      select
         product_id,list_price_converged,sum(invoiced_quantity_in_pieces) as total_ordered_pieces, sum(net_sales) as total_net_sales, min(fiscal_date) as first_price_date
      from ${cdm_pricing.SQL_TABLE_NAME}  AS cdm_pricing
      group by 1,2
      order by 1, 2 asc
      )
       ;;
    }
...

}

以及我们之前介绍的 BigQuery ML ARIMA 模型，用于预测未来的销售情况（参见此视图文件）

view: arima_model {
  derived_table: {
    persist_for: "24 hours"
    sql_create:
      create or replace model ${sql_table_name}
            options
              (model_type = 'arima_plus',
               time_series_timestamp_col = 'fiscal_date',
               time_series_data_col = 'total_quantity',
               time_series_id_col = 'product_id',
               auto_arima = true,
               data_frequency = 'auto_frequency',
               decompose_time_series = true
              ) as
            select
              fiscal_date,
              product_id,
              sum(invoiced_quantity_in_pieces) as total_quantity
            from
              ${cdm_pricing.sql_table_name}
            group by 1,2 ;;
          }
   }
...
}

LookML 还包含示例信息中心。您可以在此处访问信息中心的演示版。信息中心的第一部分为用户提供有关销售、成本、定价和利润率变化的概要信息。作为商家用户，您可能需要创建提醒，以便了解销量是否下降到 X% 以下，因为这可能意味着您应降低价格。

下一部分（如下所示）可让用户深入了解与价格变化有关的趋势。在这里，您可以深入研究具体商品，查看确切的定价以及更改的价格，这有助于精确找到需要进一步研究的特定商品。

最后，在报告底部，您会看到 BigQueryML 模型的结果。使用 Looker 信息中心顶部的过滤条件，您可以轻松输入参数来模拟与上述类似的不同场景。例如，查看如果订单量下降到预测价值的 75%，且所有产品的定价提高 25%，会发生什么情况，如下所示

这由 LookML 中的参数提供支持，这些参数随后会直接整合到此处的测量计算中。借助这种类型的报告，您可以找到所有产品的最佳定价，或深入研究特定产品，以确定应该将价格提高或应用折扣，以及毛收入和净收入的结果如何。

14. 根据您的定价系统进行调整

虽然本教程会转换示例数据源，但您在各种平台上使用的价格资源会面临非常相似的数据挑战。价格资产采用不同的导出格式（通常是 xls、表格、csv、txt、关系型数据库、业务应用），用于提供摘要和详细结果，每种格式都可以连接到 Dataprep。我们建议您首先按照与上述示例类似的方法描述转换要求。阐明规范并确定所需的转换类型后，您可以使用 Dataprep 进行设计。

1. 创建要自定义的 Dataprep 流的副本（点击流右侧的 **... “**更多”按钮，然后选择“复制”选项），或者使用新 Dataprep 流从头开始。
2. 连接到您自己的价格数据集。Dataprep 原生支持 Excel、CSV、Google 表格、JSON 等文件格式。您还可以使用 Dataprep 连接器连接到其他系统。
3. 将数据资产分派到您确定的各个转换类别。对于每个类别，创建一个配方。从此设计模式中提供的流程中获得一些灵感，以转换数据并编写自己的配方。如果您遇到问题，别担心，您可以在 Dataprep 屏幕左下角的聊天对话框中寻求帮助。
4. 将您的配方关联到 BigQuery 实例。您无需费心在 BigQuery 中手动创建表，Dataprep 会自动为您处理。我们建议您在将输出添加到流时选择手动目标位置，并在每次运行时删除表。分别测试每个配方，直至达到预期结果。测试完成后，您会在每次运行时将输出转换为 Append to table，以避免删除之前的数据。
5. 您可以选择关联要按计划运行的流。如果您的流程需要持续运行，这将非常有用。您可以定义一个时间表，让系统每天或每小时根据所需新鲜度加载响应。如果您决定按计划运行流，则需要在每个配方的流中添加时间表目标输出。

修改 BigQuery 机器学习模型

本教程提供了一个示例 ARIMA 模型。不过，在开发模型时，您还可以控制其他参数，以确保模型最适合您的数据。如需了解详情，请参阅此处文档中的 示例。此外，您还可以使用 BigQuery ML.ARIMA_EVALUATE、ML.ARIMA_COEFFICIENTS 和 ML.EXPLAIN_FORECAST 函数来获取关于模型的更多详细信息并做出优化决策。

修改 Looker 报告

按照上述说明将 LookML 导入您自己的项目后，您可以直接修改以添加其他字段、修改计算或用户输入的参数，以及更改信息中心上的可视化图表以满足您的业务需求。如需详细了解如何在 LookML 中进行开发，请点击此处；如需详细了解如何在 Looker 中直观呈现数据，请点击此处。

15. 恭喜

现在，您已了解优化零售产品的“关键步骤”定价！

后续操作

探索其他智能分析参考模式

深入阅读

• 点击此处阅读博客
• 点击此处详细了解 Dataprep
• 如需详细了解 BigQuery 机器学习，请点击此处
• 如需详细了解 Looker，请点击此处