制定最佳的零售產品價格

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。從 Google 控制台啟用 Dataprep，方法是前往左側導覽選單的「大數據」部分，然後啟用 Dataprep。按照註冊步驟啟用。
如要設定自己的 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 的範例資料來源。

交易 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 中，按一下左側導覽列中的「流程」圖示。接著在「Flows」檢視畫面中，選取內容選單中的「Import」。匯入流程後，即可選取並查看及編輯流程。

在流程左側，產品價格和三份交易 Google 試算表都必須連結為資料集。如要執行這項操作，請在 Google 試算表資料集物件上按一下滑鼠右鍵，然後選取「取代」。然後點選「Import Datasets」連結。按一下「編輯路徑」鉛筆圖示，如下圖所示。

將目前的值替換為指向交易和產品價格 Google 試算表的連結。

如果 Google 試算表包含多個分頁，您可以在選單中選取要使用的分頁。按一下「編輯」，選取要用做資料來源的分頁，然後按一下「儲存」和「匯入並新增至流程」。返回模式後，按一下「取代」。在這個流程中，每個工作表都會以自己的資料集表示，以便在後續的食譜中示範如何合併不同來源。

定義 BigQuery 輸出資料表：

在這個步驟中，您要將 BigQuery CDM_Pricing 輸出資料表的位置與 Dataoprep 工作建立關聯，以便每次執行工作時載入資料表。

在「流程檢視」頁面中，按一下「結構定義對應輸出」圖示，然後在「詳細資料面板」中，按一下「目的地」分頁標籤。然後編輯用於測試的手動目的地輸出內容，以及用於自動執行整個流程的排定目的地輸出內容。如要這麼做，請按照下列指示操作：

編輯「手動目的地」：在「詳細資料」面板中，按一下「手動目的地」部分下方的「編輯」按鈕。在「發布設定」頁面的「發布動作」下方，如果已有發布動作，請編輯該動作；否則請按一下「新增動作」按鈕。然後瀏覽 BigQuery 資料集，找到您在先前步驟中建立的 Pricing_CDM 資料集，並選取 CDM_Pricing 資料表。確認已勾選「Append to this table every run」(每次執行時附加至這個表格)，然後按一下「Add」(新增)。按一下「Save Settings」(儲存設定)。
編輯「預定目的地」

在「詳細資料」面板的「排定的目的地」部分下方，按一下「編輯」。

系統會沿用手動目的地設定，因此您不需要進行任何變更。按一下「儲存設定」。

5. 標準化資料

提供的流程會合併、格式化及清理交易資料，然後將結果與公司說明和匯總價格資料彙整，以供報表使用。您將逐步瞭解流程的各個元件，如下圖所示。

6. 探索交易資料食譜

首先，您將瞭解交易資料食譜的運作方式，這個食譜用於準備交易資料。在「流程檢視畫面」中按一下「交易資料」物件，然後在「詳細資料面板」中按一下「編輯配方」按鈕。

轉換器頁面隨即開啟，詳細資料面板中會顯示方案。方案包含套用至資料的所有轉換步驟。您可以在每個步驟之間點選，查看方案中這個特定位置的資料狀態，藉此在方案中瀏覽。

您也可以點選每個 Recipe 步驟的「更多」選單，然後選取「前往所選項目」或「編輯」，瞭解轉換作業的運作方式。

聯集交易：交易資料方案的第一個步驟是聯集儲存在不同工作表中的交易，代表每個月的交易。
標準化顧客描述：食譜的下一個步驟是標準化顧客描述。也就是說，顧客名稱可能相似，但略有不同，我們希望將這些名稱統一為一個。食譜會示範兩種可能的方法。首先，這項功能會運用標準化演算法，並可透過不同的標準化選項進行設定，例如「類似字串」(將含有相同字元的數值歸類在一起)，或「發音」(將發音相似的數值歸類在一起)。或者，您也可以使用公司 ID，在上述 BigQuery 資料表中查詢公司說明。

您可以進一步瀏覽食譜，瞭解用於清理及格式化資料的各種其他技巧：刪除資料列、根據模式設定格式、透過查閱擴充資料、處理遺漏值，或取代不需要的字元。

7. 探索產品價格資料食譜

接著，您可以探索「產品定價資料方案」中發生的情況，該方案會將準備好的交易資料彙整到匯總的定價資料中。

按一下頁面頂端的「PRICING OPTIMIZATION DESIGN PATTERN」，關閉「Transformer Page」並返回「Flow View」。然後按一下「產品價格資料」物件，並編輯配方。

取消透視每月價格資料欄：按一下步驟 2 和 3 之間的配方，查看取消透視步驟前的資料。您會發現資料包含每個月份 (1 月、2 月、3 月) 的交易價值，且各月份的交易價值分別位於不同資料欄。這種格式不適合在 SQL 中套用匯總 (即總和、平均交易) 計算。資料必須取消樞紐分析，這樣每個資料欄都會成為 BigQuery 資料表中的資料列。食譜會運用 unpivot 函式，將 3 個資料欄轉換為每個月一個資料列，方便進一步套用群組計算。
計算各個用戶端、產品和日期的平均交易價值：我們想計算各個用戶端、產品和日期的平均交易價值。我們可以運用匯總函式，產生新資料表 (「分組依據做為新資料表」選項)。在這種情況下，資料會匯總至群組層級，我們也會遺失每筆交易的詳細資料。或者，我們也可以決定將詳細資料和匯總值保留在同一個資料集中 (「依新資料欄分組依據」選項)，這樣就能輕鬆套用比率 (即產品類別對整體收益的貢獻百分比)。如要試試這個行為，請編輯方案步驟 7，然後選取「Group by as a new table」(以新表格分組) 或「Group by as a new column(s)」(以新資料欄分組) 選項，即可查看差異。
彙整價格日期：最後，使用 join 將多個資料集合併成一個較大的資料集，並在初始資料集中加入資料欄。在這個步驟中，價格資料會根據「價格資料.產品代碼」=「交易資料.SKU」和「價格資料.價格日期」=「交易資料.會計年度日期」，與交易資料配方輸出內容合併。

如要進一步瞭解可透過 Dataprep 套用的轉換，請參閱 Trifacta 資料整理快速參考指南

8. 探索結構定義對應食譜

最後一個 recipe「結構定義對應」可確保產生的 CDM 資料表符合現有 BigQuery 輸出資料表的結構定義。這裡使用「快速目標」功能，透過模糊比對比較兩個結構定義並套用自動變更，重新格式化資料結構以符合 BigQuery 資料表。

9. 整合至單一結構

設定來源和目的地，並探索流程步驟後，您就可以執行流程，將 CDM 資料表轉換並載入 BigQuery。

執行結構定義對應輸出內容：在流程檢視畫面中，選取「結構定義對應」輸出物件，然後按一下「詳細資料」面板中的「執行」按鈕。選取「Trifacta Photon」執行環境，並取消勾選「Ignore Recipe Errors」。然後按一下「執行」按鈕。如果指定的 BigQuery 資料表存在，Dataprep 會附加新資料列，否則會建立新資料表。
查看工作狀態：Dataprep 會自動開啟「執行工作」頁面，方便您監控工作執行情況。系統應會在幾分鐘內完成程序並載入 BigQuery 資料表。工作完成後，定價 CDM 輸出內容會以乾淨、結構化和標準化的格式載入 BigQuery，方便您進行分析。

10. 提供數據分析與機器學習/AI 技術

Analytics 先決條件

為了執行一些分析和預測模型，並獲得有趣的結果，我們建立了一個較大的相關資料集，以發掘特定洞察資訊。請先將這項資料上傳至 BigQuery 資料集，再繼續閱讀本指南。

從這個 GitHub 存放區下載大型資料集
在 Google BigQuery 控制台中，前往您的專案和 CDM_Pricing 資料集。
按一下選單，然後開啟資料集。我們會從本機檔案載入資料，藉此建立資料表。

按一下「+ Create Table」(+ 建立資料表) 按鈕，然後定義下列參數：

從上傳的檔案建立資料表，然後選取 CDM_Pricing_Large_Table.csv 檔案
結構定義自動偵測，勾選「結構定義和輸入參數」
進階選項、寫入偏好設定、覆寫資料表

按一下「建立資料表」

建立資料表並上傳資料後，您應該會在 BigQuery 的 Google 控制台中看到新資料表的詳細資料，如下所示。有了 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、試算表、csv、txt、關聯式資料庫、商務應用程式)，每種格式都可以連結至 Dataprep。建議您先描述轉換需求，做法與上述範例類似。釐清規格並找出所需轉換類型後，您就可以使用 Dataprep 設計轉換。

複製要自訂的 Dataprep 流程 (按一下流程右側的「更多」按鈕 **...**，然後選取「複製」選項)，或使用新的 Dataprep 流程從頭開始。
連結至您自己的價格資料集。Dataprep 原生支援 Excel、CSV、Google 試算表、JSON 等檔案格式。您也可以使用 Dataprep 連接器連線至其他系統。
將資料資產分配到您識別的各個轉換類別。為每個類別建立一個食譜。您可以參考這個設計模式提供的流程，從中汲取靈感來轉換資料，並編寫自己的方案。如果遇到困難，請別擔心，只要在 Dataprep 畫面左下方的對話方塊中尋求協助即可。
將 recipe 連結至 BigQuery 執行個體。您不必在 BigQuery 中手動建立資料表，Dataprep 會自動為您處理。建議您在流程中新增輸出內容時，選取「手動目的地」，並在每次執行時捨棄資料表。請逐一測試每個套件，直到獲得預期結果為止。測試完成後，您會在每次執行時將輸出內容轉換為「附加至表格」，避免刪除先前的資料。
您也可以選擇將流程與排程建立關聯，讓流程依排程執行。如果程序需要持續執行，這項功能就相當實用。您可以根據所需的新鮮度定義排程，每天或每小時載入回應。如果您決定排定流程執行時間，則必須在流程中為每個方案新增「排定目的地輸出」。

修改 BigQuery 機器學習模型

本教學課程提供 ARIMA 模型範例。不過，開發模型時，您可以控管其他參數，確保模型最符合您的資料。如需更多詳細資料，請參閱說明文件中的範例。此外，您也可以使用 BigQuery ML.ARIMA_EVALUATE、ML.ARIMA_COEFFICIENTS 和 ML.EXPLAIN_FORECAST 函式，進一步瞭解模型並做出最佳化決策。

編輯 Looker 報表

將 LookML 匯入自己的專案後 (如上述說明)，即可直接編輯，新增其他欄位、修改計算或使用者輸入的參數，以及變更資訊主頁上的資料視覺化，以符合業務需求。如要瞭解如何使用 LookML 開發，請參閱這篇文章；如要瞭解如何在 Looker 中將資料視覺化，請參閱這篇文章。

15. 恭喜

你現在已瞭解零售產品定價的最佳化關鍵步驟！

後續步驟

探索其他智慧型數據分析參考模式。

其他資訊

按這裡閱讀網誌
如要進一步瞭解 Dataprep，請參閱這篇文章
如要進一步瞭解 BigQuery Machine Learning，請參閱這篇文章
如要進一步瞭解 Looker，請按這裡