แบ่ง Segment ลูกค้าด้วย RFM Analysis : ตอนที่ 2 Static DAX

จากเนื้อหาตอนที่แล้วผมได้แสดงวิธีทำ RFM Analysis กันด้วยสูตร Excel ปกติกันไปแล้ว คราวนี้ผมจะมาแสดงวิธีทำด้วยสูตร DAX กันบ้าง ซึ่งจะสามารถใช้ได้ทั้งใน Power BI และใน Power Pivot ของ Excel ด้วยนะครับ

ซึ่งผมคิดไว้ว่าวิธีการจัด Segment ด้วย RFM Analysis ด้วย DAX ผมจะเขียนถึงใน 2 ลักษณะ คือ แบบ Static โดยมองลูกค้าทั้งหมดมาเทียบกันเสมอ นั่นคือลูกค้าคนนึงจะถูก Assign Segment ครั้งเดียวจบ ได้อะไรก็อันนั้นเลย (เหมือนที่ผมทำใน Excel) และแบบ Dynamic คือ จะเปรียบเทียบกันเฉพาะลูกค้าที่มองเห็นอยู่ภายใต้ Filter ที่เลือกเท่านั้น ดังนั้นลูกค้าคนนึงอาจเป็นสุดยอดลูกค้าในกลุ่มนึง แต่เป็นลูกค้าประจำในอีกกลุ่มนึงที่ใหญ่กว่าก็ได้

ซึ่งใน Post นี้ผมขอพูดถึงในแบบ Static ก่อน ซึ่งจะง่ายกว่าแบบ Dynamic แต่ก็มีหลายประเด็นน่าสนใจครับ เช่น สูตรบางอัน Excel มีแต่ DAX ไม่มี ก็ต้องแก้ปัญหาด้วยวิธีที่แตกต่างกัน เป็นต้น

ไฟล์ประกอบ

ผมจะใช้ไฟล์เดิม โดยเอาจาก https://www.kaggle.com/kyanyoga/sample-sales-data นะครับ แต่คราวนี้เอาเข้าไปทุกคอลัมน์ได้เลยครับ เผื่อเราจะวิเคราะห์อย่างอื่นด้วย

พอโหลดข้อมูลเข้าไป จะได้แบบนี้ โดยผมตั้งชื่อตารางว่า sales_data นะครับ

เริ่มเตรียมข้อมูลเพื่อทำ RFM แบบ Static กัน

อันนี้จะคล้ายๆ กับที่เราทำใน Excel เลย คือ มีการสร้างคอลัมน์ต่างๆ ขึ้นมาจริงๆ เพื่อคำนวณเรื่องที่เราสนใจ แต่คราวนี้ผมจะใช้ Concept ของ Data Model มาช่วยด้วยนะครับ ดัังนั้นผมจะสร้างตารางลูกค้าออกมาด้วยคำสั่ง ALL ดังนี้ (จะใช้ DISTINCT หรือ VALUES ก็ได้)

เราใช้ DAX New Table ดังนี้

customer_table = ALL(sales_data[CUSTOMERNAME])

ก็จะได้ตารางใหม่ขึ้นมาเป็นชื่อลูกค้าแบบไม่ซ้ำกัน ซึ่งอย่าลืมสร้าง Relationship กันตารางหลักด้วยนะ (ถ้าใน Excel จะสร้าง DAX New Table ไม่ได้ ให้ใช้ Power Query สร้างด้วยการ Remove Duplicates นะครับ)

พอสร้าง Relationship แล้วจะเห็นว่าตาราง customer_table จะสามารถไหลไป filter sales_data ได้นะครับ (จากทิศลูกศร)

สร้าง Measure เพื่อคำนวณประเด็นหลักๆ

สร้าง New Measure ในตาราง sales_data เพื่อคำนวณเรื่องหลักๆ ก่อน

LastOrderDate = MAX(sales_data[ORDERDATE])

OrderCount = COUNTROWS(sales_data)

TotalSalesAmt = SUM(sales_data[SALES])

ทีนี้ จากที่ผมได้แก้ไขใน Version Excel ไปว่า ยอดเงินที่คิด ควรจะคิดยอดเงินเฉลี่ยต่อครั้งมากกว่าที่จะใช้ยอดเงินรวม ดังนั้นผมจะสร้างอีกคอลัมน์ขึ้นมาดังนี้

AvgSalesAmt = DIVIDE([TotalSalesAmt],[OrderCount])

สร้างคอลัมน์เพิ่มในตาราง customer

เราจะสร้างวันที่ล่าสุดที่ซื้อ ความถี่ที่ซื้อ และจำนวนเงินทั้งหมดที่จ่ายออกมาดังนี้

ให้ไปที่ cutomer_table แล้ว New Column ขึ้นมานะครับ

CustLastOrderDate = [LastOrderDate]

CustOrderCount = [OrderCount]

CustTotalSalesAmt = [TotalSalesAmt]

CustAvgSalesAmt = [AvgSalesAmt]

สูตรพวกนี้ทำงานได้เป็นข้อมูลรายลูกค้า เพราะการอ้างอิง Measure ตรงๆ จริงๆ แล้วจะมีผลเหมือนใช้ฟังก์ชัน CALCULATE ไปครอบสูตรของ Measure นั้นอีกที ซึ่งกระตุ้นให้เกิด Context Transition ที่สามารถเปลี่ยน Row Context ให้เป็น Filter Context ได้ครับ (ถ้าไม่มี Context Transition ผลลัพธ์แต่ละบรรทัดจะเท่ากันหมด เพราะไม่มี Filter)

สรุปได้ผลลัพธ์ดังนี้

พอได้แบบนี้แล้วต่อไปผมก็จะต้องคำนวณพวก Percentile Rank ต่างๆ ออกมา แต่ปัญหาคือ ใน DAX มันไม่มี PERCENTRANK ให้ใช้เหมือนกับใน Excel

ดังนั้นผมจะคำนวณด้วย PERCENTILE.INC ปกติ แล้วเทียบค่าเอา แต่พอลองทำกับข้อมูลวันที่แล้วมันไม่ยอมอีก ดังนั้นผมจึงต้องสร้างอีกคอลัมน์ให้วันที่เป็นเลขธรรมดาๆ ก่อน ดังนี้

CustLastOrderDateNum = customer_table[CustLastOrderDate]*1

จากนั้นเราก็จะสามารถคำนวณ Group ต่างๆ ได้แล้ว ด้วยการ Add Column ดังนี้

คำนวณกลุ่มเรื่อง Recency

R Group = 
VAR P20value=PERCENTILE.INC(customer_table[CustLastOrderDateNum],0.2)
VAR P40value=PERCENTILE.INC(customer_table[CustLastOrderDateNum],0.4)
VAR P60value=PERCENTILE.INC(customer_table[CustLastOrderDateNum],0.6)
VAR P80value=PERCENTILE.INC(customer_table[CustLastOrderDateNum],0.8)
RETURN SWITCH(TRUE(),
customer_table[CustLastOrderDateNum]<=P20value,1,
customer_table[CustLastOrderDateNum]<=P40value,2,
customer_table[CustLastOrderDateNum]<=P60value,3,
customer_table[CustLastOrderDateNum]<=P80value,4,
5)

Tips : จะเห็นว่าผมใช้ VAR มาช่วยประกาศตัวแปร เพื่อแบ่งสูตรออกเป็นส่วนๆ จะได้ลดความมึนงงลงได้ ตอนอ่านสูตรจะง่ายขึ้น นอกจากนี้ยังใช้ SWITCH + TRUE มาช่วยเขียนเงื่อนไขหลายเงื่อนไขแทนการใช้ IF ซ้อน IF ได้ด้วย

คำนวณกลุ่มเรื่อง Frequency

F Group = 
VAR P20value=PERCENTILE.INC(customer_table[CustOrderCount],0.2)
VAR P40value=PERCENTILE.INC(customer_table[CustOrderCount],0.4)
VAR P60value=PERCENTILE.INC(customer_table[CustOrderCount],0.6)
VAR P80value=PERCENTILE.INC(customer_table[CustOrderCount],0.8)
RETURN SWITCH(TRUE(),
customer_table[CustOrderCount]<=P20value,1,
customer_table[CustOrderCount]<=P40value,2,
customer_table[CustOrderCount]<=P60value,3,
customer_table[CustOrderCount]<=P80value,4,
5)

คำนวณกลุ่มเรื่อง Monetary

M Group = 
VAR P20value=PERCENTILE.INC(customer_table[CustAvgSalesAmt],0.2)
VAR P40value=PERCENTILE.INC(customer_table[CustAvgSalesAmt],0.4)
VAR P60value=PERCENTILE.INC(customer_table[CustAvgSalesAmt],0.6)
VAR P80value=PERCENTILE.INC(customer_table[CustAvgSalesAmt],0.8)
RETURN SWITCH(TRUE(),
customer_table[CustAvgSalesAmt]<=P20value,1,
customer_table[CustAvgSalesAmt]<=P40value,2,
customer_table[CustAvgSalesAmt]<=P60value,3,
customer_table[CustAvgSalesAmt]<=P80value,4,
5)

จะได้ผลลัพธ์ดังรูป

เตรียมตารางเกณฑ์การจัด RFM Segment

ต่อไปเราก็สร้างตารางอ้างอิงว่าคะแนนอะไรจะอยู่กลุ่มไหน โดยจะ Get Data จาก Excel ที่ทำไว้ในตอนที่แล้วก็ได้ ซึ่งหน้าตาใน Excel เป็นแบบนี้

ก่อนอื่นใน Power Query Editor ให้เรา Add Index Column เพื่อให้มันบันทึกการเรียงลำดับของเราไว้ก่อน (เพราะลำดับ Priority นั้นสำคัญครับ)

จากนั้นเพื่อให้เขียนสูตรง่ายขึ้น เราจะใช้ Power Query แตกตัวเลขที่คั่นด้วย Comma ออกเป็นหลายๆ บรรทัดไปเลย ด้วยคำสั่ง Split by Delimiter ด้วย Comma แต่ให้ Split into Rows แทน (กดทีละคอลัมน์)

ทำไปเรื่อยๆ กับทั้ง 3 คอลัมน์ จะได้ผลลัพธ์ของทุก Combination แล้วกด Close & Apply เพื่อ Load Data ออกมายัง Data Model

จากนั้นในส่วนของ Data แล้วให้เรากำหนดให้คอลัมน์ Segment ให้ Sort by Column ที่ชื่อว่า Index ด้วยนะครับ แล้วลองเรียงน้อยไปมากดู จะได้แบบนี้

Assign Segment ให้ลูกค้า

คราวนี้ให้เรากลับมาที่ customer_table เพื่อที่จะคำนวณ Segment ให้ลูกค้าของเราได้ซักที

หลักการคือ เราจะเอาข้อมูล r f m group ของลูกค้าแต่ละคน ไป Filter ตาราง RFMtable แล้วเอาผลลัพธ์ตัวบนสุดมา ซึ่งเราจะใช้ CACULATETABLE ในการ Filter เงื่อนไขก่อน (ใช้ FILTER ก็ได้ แต่ CALCULATETABLE จะคำนวณเร็วกว่า) แล้วใช้ TOPN เพื่อคัดมาแค่บนสุดแถวเดียวโดยยึดตาม index จากน้อยไปมาก จากนั้นค่อยเลือกเอาคอลัมน์ segment ออกมาด้วย SELECTCOLUMNS ดังนี้

สรุปคือ เรา Add New Column แล้วใส่สูตรดังนี้

StaticSegment = 
VAR CurrentR=customer_table[R Group]
VAR CurrentF=customer_table[F Group]
VAR CurrentM=customer_table[M Group]
VAR filterTable=CALCULATETABLE(RFMtable,
RFMtable[R]=CurrentR,
RFMtable[F]=CurrentF,
RFMtable[M]=CurrentM)
VAR Top1=TOPN(1,filterTable,[Index],ASC)
VAR getSegment=SELECTCOLUMNS(Top1,"FilterSegment",[Segment])
RETURN getSegment

จะได้ Segment ของลูกค้ามาดังรูป

กำหนดการเรียง Segment

เพื่อทำให้ StaticSegment เรียงตามที่ผมคิด ผมจึงต้องสร้างตาราง RFMRableRef ที่บรรทัดไม่เบิ้ลขึ้นมาอีกตารางนึง (ไม่ต้อง Split) เพื่อกำหนดวิธีการเรียงไว้ เพื่อที่คราวนี้เราจะ SortbyColumn index ให้กับคอลัมน์ Segment ด้วยครับ เวลาลากลง Visual จะได้เรียงได้

แล้วลากเส้นเชื่อมกับ customer_table ซะเพื่อให้มันไป Filter customer_table ได้ แล้วจะไหลไป filter sales data ต่อได้อีก

จากนั้นอาจสร้าง Measure เพิ่มเติม เช่น นับจำนวนลูกค้าแบบไม่ซ้ำกันด้วย DISTINCTCOUNT

CustCount = DISTINCTCOUNT(sales_data[CUSTOMERNAME])

สร้าง Visual

พอสร้าง visual จะได้แบบนี้ ซึ่งได้ผลลัพธ์เหมือนใน Excel เป๊ะๆ แล้ว

แต่ใน Power BI มันดีกว่า Excel ตรงที่เราสร้างกราฟอื่น ให้มัน interactive กันได้ เช่น

คลิ๊กเลือกดู Segment ที่สนใจ กราฟข้างล่างเปลี่ยน

คลิ๊กดูช่วงวันที่ที่สนใจ กราฟอื่นๆ ก็เปลี่ยน

จบการทำ RFM แบบ Static

และนี่ก็คือตัวอย่างการใช้ Power BI ทำ RFM Analysis แบบ static ครับ ในตอนหน้าเราจะมาทำแบบ Dynamic กัน นั่นคือ สมมติเลือกลูกค้าแค่ USA ก็จะทำการจัด RFM เปรียบเทียบ Ranking เฉพาะลูกค้าใน USA เท่านั้น ไม่สนใจประเทศอื่น เป็นต้น แปลว่าระหว่างไม่ Filter อะไรเลย ลูกค้าคนนึงก็อาจเป็น Segment นึง แต่พอการเลือก Filter เปลี่ยนไป ลูกค้าคนเดิมนั้นอาจเปลี่ยน Segment ได้ด้วย ก็เลยเรียกว่าแบบ Dynamic ครับ ซึ่งจะยากกว่าเดิมพอสมควร