顧客交易紀錄分析實務 26 26.1 概述 26.2 資料分析的過程 26.3 顧客交易紀錄分析說明 26.4 實作一、顧客活動剖析實務演練 26.5 實作二、RFM分析實務演練

26.1 概述 p.344 80-20法則告訴我們：「一個企業80%的利潤來自於20%的顧客。」少部份的顧客決定企業大部份的利潤，但是如何從一堆交易資料中找出那些企業前20%的重要顧客呢？這就是企業必須深入思考的問題。 本章將介紹由網路商店或商品銷售系統的顧客交易資料庫中，找出重要顧客的步驟與實際操作方法。

26.2 資料分析的過程 顧客交易資料分析的過程可分成下列四個步驟： (1)資料來源、 (2)資料加工、 (3)資料儲存、 (4)分析處理。 p.344 顧客交易資料分析的過程可分成下列四個步驟： (1)資料來源、 (2)資料加工、 (3)資料儲存、 (4)分析處理。 大型與中小型企業的分析過程相同，只是使用的IT設備等級與資料規模不同(請參照圖26-1及圖26-2)。

圖26-1 大型網路商店顧客交易資料分析過程 p.345

圖26-2 中小型網路商店顧客交易資料分析過程 p.345

26.2.1 資料來源 p.346 資料來源指得是存放顧客交易資料的原始紀錄，它可能是任何格式的電子檔案（如Word、Excel…等）或應用系統（如EC網站系統、POS銷售點系統、進銷存管理系統、ERP系統的銷售模組…等）使用的資料庫管理系統（如Access、MySQL、SQL Server、Oracle、Sybase…等DBMS）。

26.2.2 資料加工 p.346 資料加工主要的工作是ETL，它包括資料萃取(Data Extraction)、資料轉換(Data Transformation)與資料載入(Data Loading)三個項目。 資料加工的程式撰寫不易，自動化處理困難，經常需要人工參與作業。以企業建置資料倉儲的專案為例，平均60%~70%左右的時間會花在資料萃取、轉換與載入的工作上，因此ETL是進行顧客交易資料分析過程中最重要的程序。

ETL的說明 資料萃取： 資料轉換： 資料載入： p.346 資料萃取： 使用ETL工具在不同的作業平台上擷取不同的檔案格式，這些檔案格式包括：關聯式資料庫、階層式資料、文字檔或其他特殊資料檔案。 資料轉換： 資料轉換的工作是整理所擷取的資料以便載入到資料倉儲或關聯式資料庫等資料儲存系統，其工作項目包括：資料驗證、資料清理、資料整合、新資料的產生、資料架構的非正規化與資料彙總。 資料載入： 使用ETL工具，把經過轉換過的資料載入到下階段「資料儲存」過程的資料儲存系統裡。

萃取資料的問題 p.346 資料不一致： 例如某一個資料庫（Database）以No表示顧客編號，而另一個資料表卻以No表示產品的編號，如此在資料整合時將會發生「同名異義」的問題。 資料不完整： 例如資料表（Table）中的某些資料項只有欄位名稱（Field Name）而沒有值。

萃取資料的問題(續) 資料格式或型態不同： 資料重複： 例如零件單價，生產部門的資料庫是以新台幣為單位，而採購部門的資料庫以美元為單位。 p.347 資料格式或型態不同： 例如零件單價，生產部門的資料庫是以新台幣為單位，而採購部門的資料庫以美元為單位。 資料重複： 例如將來自不同部門的顧客資料整合時，相同的顧客只需儲存一個，其他同樣的顧客即屬重複資料。

26.2.3 資料儲存 p.347 資料儲存是指將經過資料加工後的資料存放在特定的實體資料儲存系統。大企業採用的資料儲存系統可能會是高檔的資料倉儲，而中小企業可以利用關聯式資料庫來做為資料儲存系統。

資料倉儲 p.347 資料倉儲簡單的說，就是搜集來自其他系統的有用資料，存放在一個整合的儲存系統內，供決策支援（Decision Support）或資料分析使用。 資料倉儲的目標是在組織中「於正確的時間，將正確的資料交給正確的人。」這是一種持續性的作業，不是做完一次就結束的，它不同於過去以交易為導向（Transaction-Oriented）的系統開發方式，資料倉儲是「具有主題導向、整合性、時間差異性、不變動性等特徵的一種管理性資料庫」。資料倉儲通常存放5~10年不同時期的歷史資料，以作為趨勢分析、預測、比較之用。因為資料倉儲不是一般線上交易處理（On Line Transaction Process, OLTP）系統，資料存入其中後就不應該任意更改，只提供查詢的功能。

圖26-3 星形結構描述(Star Schema) p.348 最常用來管理多維度關聯式資料的方法為星形結構描述(Star Schema)。星形結構描述是由一個事實資料表(Fact Table)和多個與其連結的維度資料表(Dimension Table)所組成(圖26-3)。

圖26-4 雪花結構描述(SnowFlake Schema) p.348 另一種管理多維度關聯式資料的方法則是雪花結構描述(SnowFlake Schema)用多個維度資料表定義階層，比單一維度資料表更正規化，更節省空間，但較難維護(圖26-4)。

圖26-5：Cube儲存模式 p.349

26.2.4 分析處理 p.350 分析處理過程，主要是利用預先規劃好的多種分析角度，將資料儲存系統（例如資料倉儲）內的資料，利用資料分析工具進行運算並轉換成決策支援分析的資訊，以供企業決策人員使用。在IT工具的選用上，大企業比較會使用OLAP（線上即時分析處理）及Data Mining（資料探勘）來做為大量資料的分析工具；而中小企業則可以利用SQL（結構化查詢語言）、Excel的樞紐分析表及RFM分析技術，當做顧客資料的分析工具。

資料的分析工具 線上即時分析處理(OLAP)工具 資料探勘(Data Mining)工具 資料探勘的定義為：「從大量資料中，尋找事前未知、有效且可以付諸行動的規則或知識。」它提供企業從龐大的資料倉儲系統中，發掘出顧客感興趣或是有意義的資訊。例如：從產品銷售的倉儲資料中找出在超級市場中購買麵包的顧客，其中有20%的人也會買牛奶。根據此現象超市將可調整麵包與牛奶的擺放位置，或進行相關的行銷活動來增加收益。

26.3 顧客交易紀錄分析說明 表26-2：資料分析實作程序 1.資料來源 2.資料加工 3.資料儲存 4.分析處理 1 p.350 表26-2：資料分析實作程序 1.資料來源 2.資料加工 3.資料儲存 4.分析處理 1 osCommerce 網路商店系統 MySQL Connectors SQL Access資料庫管理系統 Excel-樞紐分析表 RFM 2 Northwind 北風貿易系統

26.4 實作1-顧客活動剖析實務演練 p.352 物流學者Edward H. Frezelle在《Supply Chain Strategy》一書中說到，顧客活動剖析檔案包括三個主要項目： 顧客銷售活動輪廓檔案（CSAP） 依照銷售額及銷售數量來將顧客區分為A、B、C三級。 產品品項銷售活動輪廓檔案（ISAP） 依照產品的金額、銷售數量來將進行排序及分類。 顧客-產品品項銷售活動輪廓檔案（CISAP）。 可以交叉顯示出企業各類別的顧客在各產品上的銷售情形。

26.4.1 資料來源探索 從資料庫中整理出分析時會用到的資料表 p.353 從資料庫中整理出分析時會用到的資料表 依據顧客活動剖析檔案的功能需求，所需彙整的檔案（指資料表Tables）與屬性（指欄位Fields）如下： 資料表名稱 欄 位 名 稱 客戶檔 客戶編號、公司名稱、地址、電話 訂貨主檔 訂單號碼、客戶編號、訂單日期 訂貨明細檔 訂單號碼、產品號碼、單價、數量、折扣 產品資料檔 產品號碼、產品、單價

繪製資料表的關聯圖 p.353~354 資料庫關聯圖 圖26-11：繪製資料庫關聯圖 圖26-12: Northwind資料庫關聯圖

26.4.2 資料加工 p.354 2 3 1 4 圖26-13：新增查詢（1）

圖26-14：新增查詢（2） p.354 點選此處

圖26-15：新增查詢（3） p.355 點選此處 圖26-16：新增查詢（4）

圖26-17：Q1-顧客活動剖析SQL敘述 p.355 SELECT 訂貨主檔.訂單號碼, 訂貨主檔.客戶編號, 客戶.公司名稱, 訂貨主檔.訂單日期, 訂貨明細.產品編號, 產品資料.產品, 訂貨明細.單價, 訂貨明細.數量, Int(訂貨明細.單價*訂貨明細.數量*(1-訂貨明細.折扣)) AS 金額 FROM 產品資料 INNER JOIN ((客戶 INNER JOIN 訂貨主檔 ON 客戶.客戶編號 = 訂貨主檔.客戶編號) INNER JOIN 訂貨明細 ON 訂貨主檔.訂單號碼 = 訂貨明細.訂單號碼) ON 產品資料.產品編號 = 訂貨明細.產品編號;

圖26-18：新增查詢（5） p.355 點選此處

圖26-19：SQL查詢結果 p.356 點選此處

圖26-20：儲存檔案 p.356 2 1

圖26-21：匯出（1） p.356 1 2

圖26-22：匯出（2） p.357 2 3 1

26.4.3 資料儲存 p.357 2 1 3 4 圖26-23：匯入資料表

圖26-24：匯入資料表 p.357 2 4 3 1 5

圖26-25：匯入「顧客活動剖析」資料表 p.358

26.4.4 分析處理 p.358 圖26-26：啟動Excel 1 3 圖26-27：開啟DW.mdb 4 2

圖26-28：開啟查詢 p.358 點選此處 1 2 圖26-29：顯示開啟查詢結果

圖26-30：步驟3之1 (樞紐分析表) p.359

圖26-31：步驟3之2 p.359 圖26-32：步驟3之3

圖26-33：樞紐分析表 p.359 維度區B 維度區A 量值區

圖26-34：顧客銷售活動輪廓檔案 p.360 購買總金額前五名顧客

圖26-35：產品品項銷售活動輪廓檔案 p.360 銷售總金額前五名產品

圖26-36：顧客-產品品項銷售活動輪廓檔案 p.360 前五名顧客所購買的前五名產品

26.5 實作二、RFM分析實務演練 p.361 本項RFM實作將採用Miglautsch的顧客五等分法，即R值依最近購買日期由近至遠，分別給予五個等分5至1分；F值依購買頻率多至少，給予五個等分5至1分；M值依購買總金額高至低，給予五個等分5至1分。由此分法（R,F,M）=（1,1,1）~（5,5,5）至多將分出125個級別之顧客。但因為Northwind資料庫的顧客數目太少，只能分出27個級別。 RFM分析使用的軟體RFM42.exe可由http://www.dbmarketing.com/ 處下載，並以E-mail方式向網站索取Serial Number。

26.5.1 資料來源探索 p.361 本過程與26.4.1節相同，請參照前項的敘述。

26.5.2 資料加工 p.361 請依照26.4.2節的做法，在Northwind資料庫的查詢物件下，新增一查詢「Q2-RFM分析」，內容如圖26-37。再利用【匯出】的功能，在「我的文件」資料夾下，產生一個新檔案：RFM分析.DBF。

圖26-37：Q2-RFM分析SQL敘述 p.361 SELECT 訂貨主檔.客戶編號, Max(訂貨主檔.訂單日期) AS 最近購買日, Count(訂貨主檔.訂單號碼) AS 購買頻率, Sum(Int([單價]*[數量]*(1-[折扣]))) AS 購買總金額 FROM 訂貨主檔 INNER JOIN 訂貨明細 ON 訂貨主檔.訂單號碼 = 訂貨明細.訂單號碼 GROUP BY 訂貨主檔.客戶編號 ORDER BY 訂貨主檔.客戶編號;

26.5.3 資料儲存 p.361 請依照26.4.3節的做法，將「RFM分析.DBF」匯入DW.mdb資料庫之內。

26.5.4 分析處理 利用Excel將「RFM分析」資料表轉換為CVS格式 p.362 利用Excel將「RFM分析」資料表轉換為CVS格式 請依照26.4.4節的做法，開啟DW.mdb資料庫的「RFM分析」資料表。並將其【另存新檔】為「RFM分析.csv」（該檔案會存於C:\EC-資料分析 資料夾）。

利用RFM for Windows 4.2軟體進行RFM分析 p.362 點選此處 圖26-38：開啟RFM for Windows 4.2

圖26-39：進入RFM 4.2並開啟RFM分析.csv p.362 1 2 5 4 3

圖26-40：RFM分析.csv p.362

圖26-41：產生RFM碼（1） p.363 1 2 3

圖26-42：產生RFM碼（2） p.363 1.改為10 3.改為27 2.改為250 4. 點選此處

圖26-43：產生RFM碼（3） 點選此處

圖26-44：產生RFM碼（4） p.364 2 1

圖26-45：產生RFM碼（5） p.364