优化零售产品的价格

1. 简介

上次更新时间：2021 年 9 月 15 日

用于生成价格分析和优化所需的数据本质上是不同的（不同的系统、不同的本地实际情况等），因此开发结构合理、标准化且干净的 CDM 表至关重要。这包括用于优化价格的关键属性，例如交易、商品、价格和客户。本文档将引导您完成以下步骤，帮助您快速开始使用价格分析功能，并根据自己的需求进行扩展和自定义。下图概述了本文档中介绍的步骤。

评估数据源：首先，您必须获取将用于创建 CDM 的数据源的清单。在此步骤中，我们还会使用 Dataprep 来探索输入数据并发现其中的问题。例如，缺失值和不匹配的值、命名惯例不一致、重复项、数据完整性问题、离群值等。
标准化数据：接下来，修复之前发现的问题，以确保数据的准确性、完整性、一致性和完整性。此过程可能涉及 Dataprep 中的各种转换，例如日期格式设置、值标准化、单位转换、过滤掉不必要的字段和值，以及拆分、联接或去重源数据。
统一为一种结构：流水线的下一阶段会将每个数据源联接到 BigQuery 中的一个宽表中，其中包含所有最精细粒度的属性。这种非规范化结构可实现不需要联接的高效分析查询。
提供分析和机器学习/AI：在数据清理完毕并格式化以供分析后，分析师可以探索历史数据，了解之前价格变动的影响。此外，BigQuery ML 还可用于创建预测模型，以估计未来的销售额。这些模型的输出结果可以纳入 Looker 中的信息中心，以创建“假设情景”，让业务用户能够分析在价格发生特定变化时销售额可能会出现什么情况。

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

构建内容

本文将向您展示如何设计价格优化数据仓库、随时间推移自动执行数据准备、使用机器学习预测产品价格变动的影响，以及开发报告以向您的团队提供可据以采取行动的数据洞见。

学习内容

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

所需条件

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

2. 在 BigQuery 中创建 CDM

在本部分中，您将创建通用数据模型 (CDM)，该模型可提供您需要分析并建议价格变动的信息的整合视图。

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

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 流，用于转换和统一上一部分列出的示例数据集。流表示流水线，或用于汇集数据集和配方的对象，这些数据集和配方用于转换和联接数据。

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

在流程的左侧，必须将“商品价格”和三个“交易”Google 表格分别关联为数据集。为此，请右键点击 Google 表格数据集对象，然后选择替换。然后点击导入数据集链接。点击“修改路径”铅笔图标，如下图所示。

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

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

定义 BigQuery 输出表：

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

在流程视图中，点击“架构映射输出”图标，然后在“详细信息”面板中点击“目标”标签页。然后，修改用于测试的手动目的地输出，以及用于自动执行整个流程的预定目的地输出。为此，请按照以下说明操作：

修改“手动设置的目标平台”：在“详细信息”面板的“手动设置的目标平台”部分下，点击“修改”按钮。在发布设置页面上的“发布操作”下方，如果已存在发布操作，请修改该操作；否则，请点击“添加操作”按钮。然后，在 BigQuery 数据集中找到您在上一步中创建的 Pricing_CDM 数据集，并选择 CDM_Pricing 表。确认已选中每次运行都附加到此表，然后点击添加。点击保存设置。
修改“预定目的地”

在“详细信息”面板的“预定目的地”部分下，点击修改。

这些设置继承自手动目标平台，您无需进行任何更改。点击“保存设置”。

5. 标准化数据

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

6. 探索事务型数据配方

首先，您将了解交易数据配方中的内容，该配方用于准备交易数据。在流程视图中点击“交易数据”对象，然后在“详细信息”面板中点击“修改配方”按钮。

系统会打开“转换器”页面，并在“详细信息”面板中显示配方。配方包含应用于数据的所有转换步骤。您可以在配方中的各个步骤之间点击，以查看配方中相应位置的数据状态，从而在配方中进行导航。

您还可以点击每个配方步骤的“更多”菜单，然后选择“前往所选内容”或“修改”，以了解转换的运作方式。

合并交易：交易数据配方的第一步是合并存储在不同工作表中（分别代表每个月）的交易。
标准化客户说明：此食谱中的下一步是标准化客户说明。这意味着客户名称可能相似，但略有不同，我们需要将它们标准化为一个名称。此食谱演示了两种可能的方法。首先，它利用了标准化算法，该算法可通过不同的标准化选项进行配置，例如“相似字符串”（将具有共同字符的值聚类在一起）或“发音”（将发音相似的值聚类在一起）。或者，您也可以使用公司 ID 在上述 BigQuery 表中查找公司说明。

您可以进一步浏览配方，了解用于清理和格式化数据的各种其他技巧：删除行、基于模式进行格式化、通过查找丰富数据、处理缺失值或替换不需要的字符。

7. 探索商品价格数据 recipe

接下来，您可以探索“Product Pricing Data”配方中发生的情况，该配方会将准备好的交易数据与汇总的价格数据联接起来。

点击页面顶部的“价格优化设计模式”，关闭转换器页面并返回到流程视图。然后，点击“Product Pricing Data”对象并修改配方。

取消透视月度价格列：点击步骤 2 和步骤 3 之间的 recipe，查看取消透视步骤之前的数据外观。您会注意到，数据包含每个月的交易价值，分别位于不同的列中：Jan、Fev、Mar。这种格式不便于在 SQL 中应用汇总（即交易总额、平均交易额）计算。需要对数据进行逆透视，以便将每个列转换为 BigQuery 表中的一行。该配方利用 unpivot 函数将 3 列转换为每个月一行，以便更轻松地应用分组计算。
按客户、产品和日期计算平均交易价值：我们希望计算每个客户、产品和日期的平均交易价值。我们可以使用“聚合函数”并生成新表（选择“分组依据作为新表”选项）。在这种情况下，数据会在群组级别进行汇总，我们会丢失每笔交易的详细信息。或者，我们也可以决定在同一数据集中同时保留详细信息和汇总值（选择“按新列分组依据”选项），这样便可轻松应用比率（即商品类别对总收入的贡献百分比）。您可以尝试修改 recipe 步骤 7，然后选择“按新表分组依据”或“按新列分组依据”，看看效果有何不同。
联接价格日期：最后，使用联接将多个数据集合并为一个更大的数据集，并向初始数据集添加列。在此步骤中，定价数据会与“事务数据”配方的输出联接，联接条件为“定价数据.产品代码”=“事务数据.SKU”和“定价数据.价格日期”=“事务数据.财政日期”

如需详细了解可以使用 Dataprep 应用的转换，请参阅 Trifacta 数据整理速查表

8. 探索架构映射秘笈

最后一个 recipe“架构映射”可确保生成的 CDM 表与现有的 BigQuery 输出表的架构相匹配。在此示例中，我们使用快速目标功能重新设置数据结构格式，以匹配 BigQuery 表，并使用模糊匹配来比较两个架构并应用自动更改。

9. 统一为一个结构

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

运行架构映射输出：在流程视图中，选择架构映射输出对象，然后点击“详细信息”面板中的“运行”按钮。选择“Trifacta Photon”运行环境，然后取消选中“忽略配方错误”。然后点击“运行”按钮。如果指定的 BigQuery 表存在，Dataprep 会附加新行；否则，它会创建一个新表。
查看作业状态：Dataprep 会自动打开“运行作业”页面，以便您监控作业执行情况。系统应会在几分钟内继续操作并加载 BigQuery 表。作业完成后，定价 CDM 输出将以干净、结构化和规范化的格式加载到 BigQuery 中，以便进行分析。

10. 提供分析和机器学习/AI

Google Analytics 前提条件

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

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

点击“+ 创建表”按钮，然后定义以下参数：

基于上传的数据创建表，然后选择 CDM_Pricing_Large_Table.csv 文件
架构自动检测，勾选“架构和输入参数”
高级选项、写入偏好设置、覆盖表

点击“创建表”

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

11. 查看价格变动的影响

例如，您可能需要分析之前更改商品价格后订单行为的变化。

首先，您创建一个临时表，其中包含每次商品价格发生变化时的一行数据，其中包含有关该特定商品定价的信息，例如以每种价格订购的商品数量以及与该价格相关的总净销售额。

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

接下来，在临时表就位后，您可以计算各个 SKU 的平均价格变化：

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

最后，您可以分析价格变动后会发生什么情况，方法是查看每次价格变动与订单中的商品总数之间的关系：

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 时序预测模型，以预测每种商品的销售数量。

首先，您需要创建一个 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;

接下来，您可以使用 ML.FORECAST 函数预测每种产品的未来销售额：

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

有了这些预测结果，您就可以尝试了解提高价格后可能会发生什么情况。例如，如果您将每种产品的价格提高 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 配置。

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

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、sheets、csv、txt、关系数据库、业务应用），可用于导出汇总结果和详细结果，每种格式都可以连接到 Dataprep。建议您先描述自己的转型需求，就像上面的示例一样。在明确规范并确定所需转换类型后，您可以使用 Dataprep 设计转换。

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

修改 BigQuery Machine Learning 模型

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

修改 Looker 报告

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

15. 恭喜

现在，您已了解优化零售商品价格所需的主要步骤！

后续操作

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

深入阅读

点击此处阅读博客
如需详细了解 Dataprep，请点击此处
点击此处详细了解 BigQuery Machine Learning
点击此处详细了解 Looker