Select Page
วิธีดับเบิ้ลคลิ๊กแสดงข้อมูลรายะเอียดใน Pivot แบบ Data Model ให้เกิน 1000 บรรทัด

วิธีดับเบิ้ลคลิ๊กแสดงข้อมูลรายะเอียดใน Pivot แบบ Data Model ให้เกิน 1000 บรรทัด

บทความนี้เป็น Trick สั้นๆ ครับ ปกติแล้ว ถ้าเราใช้ Pivot Table แบบปกติ เราจะสามารถ Double Click ที่ผลลัพธ์แต่ละช่องเพื่อจะแสดงข้อมูลที่มาของเลขนั้นๆ ได้ครบเลย แต่ถ้าเป็น Pivot Table โหมด Data Model มันจะแสดงข้อมูลได้แค่ 1000 บรรทัดแรกของการ Filter นั้นๆ ซึ่งเป็นเรื่องที่น่าหงุดหงิดมาก

ลองลงมือทำให้เกิดปัญหา Pivot ได้แค่ 1000

สมมติเรามีข้อมูลสมมติโดยสร้างเป็นเลข 1-200,000 ไว้ (ผมใช้ Home->Fill-> Series ช่วยสร้างจะได้เร็วๆ)

วิธีดับเบิ้ลคลิ๊กแสดงข้อมูลรายะเอียดใน Pivot แบบ Data Model ให้เกิน 1000 บรรทัด 1

แล้วสร้างคอลัมน์ Group อีกซักอันให้มีกลุ่มละ 90,000 ตัว ด้วยการเขียนสูตรว่า

=ROUNDUP(A2/90000,0)
วิธีดับเบิ้ลคลิ๊กแสดงข้อมูลรายะเอียดใน Pivot แบบ Data Model ให้เกิน 1000 บรรทัด 2

จากนั้นเรา convert เป็น Table ซะ แล้วเอาเข้า Pivot แบบ Data Model

วิธีดับเบิ้ลคลิ๊กแสดงข้อมูลรายะเอียดใน Pivot แบบ Data Model ให้เกิน 1000 บรรทัด 3

เรา Pivot ข้อมูลอะไรก็แล้วแต่ สมมติได้ดังนี้

วิธีดับเบิ้ลคลิ๊กแสดงข้อมูลรายะเอียดใน Pivot แบบ Data Model ให้เกิน 1000 บรรทัด 4

แล้วเราอยากรู้ว่าเลข 135000.5 มาจากข้อมูลบรรทัดไหนบ้าง เราก็กด ดับเบิ้ลคลิ๊กได้เลย มันจะสร้าง sheet ใหม่ แล้ว แสดงข้อมูลบรรทัดที่เป็นแหล่งที่มาของเลข 135000.5 นั้น (แสดงผลการ Drill Through จาก Filter Context นั่นเอง)

ปัญหาคือ ถ้าเป็น Pivot ปกติจะแสดงครับ แต่ Pivot แบบ Data Model จะแสดงตัวอย่างแค่ 1000 บรรทัดแรก!

วิธีดับเบิ้ลคลิ๊กแสดงข้อมูลรายะเอียดใน Pivot แบบ Data Model ให้เกิน 1000 บรรทัด 5

ถ้าเราอยากเห็นมากกว่า 1000 แถวต้องทำไง?

ให้เราไปแก้ Setting ของ connection ตามรูปนี้ โดยคลิ๊กขวาที่ ThisWorkbookDataModel แล้วเลือก Properties…

วิธีดับเบิ้ลคลิ๊กแสดงข้อมูลรายะเอียดใน Pivot แบบ Data Model ให้เกิน 1000 บรรทัด 6

จากนั้นแก้ตัวเลขใน OLAP Drill Through ให้เยอะๆ ไปเลย เช่น เท่ากับจำนวนแถว Excel

วิธีดับเบิ้ลคลิ๊กแสดงข้อมูลรายะเอียดใน Pivot แบบ Data Model ให้เกิน 1000 บรรทัด 7

คราวนี้ลบผลการ Drill Through อันเดิมที่ได้ 1000 แถวทิ้งไปซะ (ลบทั้งชีทเลยก็ได้) แล้วลองดับเบิ้ลคลิ๊กใหม่ครับ

นี่ไง คราวนี้ออกมาครบเลย เย้!

วิธีดับเบิ้ลคลิ๊กแสดงข้อมูลรายะเอียดใน Pivot แบบ Data Model ให้เกิน 1000 บรรทัด 8
ลองทำ Machine Learning ใน Excel เทคนิค K-Means Clustering  แบบไม่ง้อ VBA

ลองทำ Machine Learning ใน Excel เทคนิค K-Means Clustering แบบไม่ง้อ VBA

การแบ่งกลุ่มข้อมูลนั้นจริงๆ สามารถทำได้หลายวิธี ซึ่งวันนี้ผมจะมานำเสนอวิธีที่เรียกว่า K-Means Clustering นั่นคือ ให้โปรแกรมพยายามจัดกลุ่มที่มีความใกล้เคียงกันเข้าอยู่เป็นกลุ่มเดียวกันได้ด้วยตัวมันเอง โดยที่เราไม่ต้องเป็นคนบอกมันว่าแบบไหนควรอยู่กลุ่มอะไร ซึ่งมันต้องมีการวน Loop คำนวณซ้ำๆ หลายรอบ

หลายคนอาจคิดว่าการวน Loop จะต้องใช้ VBA เขียนโปรแกรมเอาแน่ๆ แต่ในบทความนี้ผมจะทำให้ดูว่า วิธีการเขียนสูตรธรรมดาๆ ในโหมดที่เรียกว่า Iterative Calculation ก็สามารถทำได้ครับ! รับรองว่าคุณจะได้เห็นแง่มุมใหม่ๆ ในการใช้สูตร Excel แบบที่ปกติไม่ค่อยได้เห็นแน่นอนครับ ^^

ลองลงมือทำดู

เราจะใช้ข้อมูลลูกค้าในห้าง อันนี้ https://www.kaggle.com/shwetabh123/mall-customers

โดยที่ผมจะลองแบ่งกลุ่มจากข้อมูลแค่ 2 แกน คือ Annual Income (k$) และ Spending Score (1-100) เท่านั้นครับ ซึ่งหากลองทำ Scatter Plot จะได้ดังนี้

ลองทำ Machine Learning ใน Excel เทคนิค K-Means Clustering แบบไม่ง้อ VBA 9

ซึ่งถ้าดูจากกราฟที่ออกมา ผมคิดว่าน่าจะแบ่งลูกค้าเป็น 5 กลุ่ม (เรียกจำนวนกลุ่มว่า k=5)

ขั้นตอนต่อไป ผมสร้าง sheet ใหม่แล้วเราจะ random สร้างจุดขึ้นมา 5 จุดจาก Range ของข้อมูลที่มี เสร็จแล้วทำให้กลายเป็น Value ไว้ด้วย จะได้ไม่เปลี่ยนอีก (x คือ Income, y คือ spending)

ลองทำ Machine Learning ใน Excel เทคนิค K-Means Clustering แบบไม่ง้อ VBA 10

จากนั้นคำนวณว่าแต่ละจุดอยู่ใกล้ Point กลางอันไหนเท่าไหร่ ด้วยสูตรระยะห่างระหว่างจุด จากความสัมพันธ์เพื่อคำนวณด้านตรงข้ามสามเหลี่ยมมุมฉากสมัยเด็กๆ (ผมมีเปลี่ยนชื่อคอลัมน์เป็น Income กับ spending ด้วยจะได้ง่ายๆ)

Warning : การหาระยะห่าง ควรทำให้ข้อมูลตัวเลขมีปริมาณที่เป็น Standard ใกล้เคียงกันก่อน เพราะหากเลขเยอะๆ เช่น รายได้เป็นหมื่นเป็นล้าน มาหาระยะห่างกับเลขน้อยๆ เช่น อายุ ที่เป็นแค่หลักสิบ ระยะห่างจากรายได้มันจะกลับ effect ของอายุหมดเลย แต่พอดีข้อมูลนี้เลขมันค่อนข้างมีปริมาณพอๆ กันอยู่แล้วเลยไม่มีปัญหา

=SQRT((x1-x2)^2+(y1-y2)^2)
=SQRT(([@Income]-VLOOKUP(F$1,Pointกลาง!$A:$C,2,0))^2+([@Spending]-VLOOKUP(F$1,Pointกลาง!$A:$C,3,0))^2)
ลองทำ Machine Learning ใน Excel เทคนิค K-Means Clustering แบบไม่ง้อ VBA 11

แล้วเราก็ดูว่าห่างจากจุดกลางอันไหนน้อยที่สุด จะถือว่าข้อมูลอยู่ในกลุ่มนั้น ซึ่งเราก็เขียนสูตรหาได้ดังนี้

MinDistance (คอลัมน์ K) ก็ใช้ฟังก์ชัน MIN ปกติเลย

=MIN(F2:J2)

ส่วนอยู่กลุ่มไหน (คอลัมน์ L) คำนวณดังนี้

=INDEX($F$1:$J$1,MATCH([@MinDistance],F2:J2,0))
ลองทำ Machine Learning ใน Excel เทคนิค K-Means Clustering แบบไม่ง้อ VBA 12

จากนั้นกลับมาอีกชีทนึง แล้วผมก็ใช้ countifs เพื่อนับว่าแต่ละกลุ่มมีกี่จุด ดังนี้

=COUNTIFS(Mall_Customers[อยู่กลุ่มไหน],Pointกลาง!A2)
ลองทำ Machine Learning ใน Excel เทคนิค K-Means Clustering แบบไม่ง้อ VBA 13

ความหมายของ K-Means

ขั้นตอนต่อไปคือ เราจะเอาจุดในกลุ่มเดียวกันมาหาค่าเฉลี่ย แล้วสถานปนาให้กลายเป็นจุด Center ตัวใหม่ แล้ววน Loop ทำแบบนี้ไปเรื่อยๆ จนกว่าจุด Center จะไม่เปลี่ยนอีกแล้ว ถึงจะจบขั้นตอน มันก็เลยเรียกว่า k-means ไงล่ะ เพราะเป็นการหาค่าเฉลี่ยของสมาชิกในกลุ่ม

แล้วเราจะทำเรื่องแบบนี้ใน Excel ได้ยังไง…

หลายคนอาจคิดว่ายังไงก็ต้องใช้ VBA ทำแน่ๆ แต่ในบทความนี้ผมจะลองทำอีกวิธีนึงนั่นก็คือ การใช้สูตรแบบ Iterative Calculation ครับ ซึ่งเป็นโหมดที่รองรับสูตรแบบงูกินหางหรือที่เรียกใช้ตัวเองซ้ำๆ ได้ โดยจะใช้งานได้จะต้องไปเปิดโหมดนี้ใน Excel Option ซะก่อน ซึ่งผมจะตั้งค่าให้มันทำทีละ 1 ครั้ง และเปิดโหมด Cal แบบ Manual ที่ต้องกด F9 ซะก่อน (จะได้เห็นภาพชัดๆ ว่าคืออะไรกันแน่?)

ลองทำ Machine Learning ใน Excel เทคนิค K-Means Clustering แบบไม่ง้อ VBA 14

Iterative Calculation ทำงานยังไง?

สมมติเราเขียนสูตรในช่อง F2 ว่า

=F2+1

ซึ่งจะเห็นว่าสูตรนี้มันเรียกข้อมูลจากตัวเองมาบวก 1 ซึ่งถ้าเป็นโหมดทำงานปกติใน Excel มันจะด่าเราว่าเป็น Circular Reference แล้วผลลัพธ์จะออกมาเป็น 0 ตลอด

แต่ถ้าเราเปิดโหมด Iterative Calculation แล้ว สูตรนี้จะทำงานได้แบบไม่ Error โดยที่ถ้า Maximum Iteration เป็น 1 เมื่อกด F9 1 ทีมันก็จะคำนวณ 1 รอบ ทำให้ช่อง F2 มีค่าเพิ่มเรื่อยๆ ทีละ 1 หลังจากกด F9

เอา Iterative Calculation มาวน Loop

เราก็มาแก้สูตรของ Center แต่ละตัว ให้เป็นการเฉลี่ยภายในกลุ่มตัวเอง ซึ่งผมจะใช้ AVERAGEIFS มาช่วยครับ (สูตรนี้เป็นสูตรงูกินหาง เพราะกลุ่มก็คิดจากจุด Center แล้วจุด Center ดันคิดจากกลุ่มอีก)

คำนวณค่า X (ลาก B2:B6 ใส่สูตรแล้วกด Ctrl+Enter เพื่อใส่สูตรพร้อมกัน)

=AVERAGEIFS(Mall_Customers[Income],Mall_Customers[อยู่กลุ่มไหน],Pointกลาง!A2)

คำนวณค่า Y (ลาก C2:C6 ใส่สูตรแล้วกด Ctrl+Enter เพื่อใส่สูตรพร้อมกัน)

=AVERAGEIFS(Mall_Customers[Spending],Mall_Customers[อยู่กลุ่มไหน],Pointกลาง!A2)

แล้วจะได้ผลลัพธ์ตามนี้ ซึ่งจำนวณจุดจะยังไม่เปลี่ยน (เพราะยังไม่ได้กด F9 เพื่อ Calculate)

ลองทำ Machine Learning ใน Excel เทคนิค K-Means Clustering แบบไม่ง้อ VBA 15

คราวนี้เรากด F9 สูตรทั้งไฟล์จะคำนวณ 1 ครั้ง จะได้ผลดังนี้

ลองทำ Machine Learning ใน Excel เทคนิค K-Means Clustering แบบไม่ง้อ VBA 16

คราวนี้เรากด F9 สูตรทั้งไฟล์จะคำนวณอีก 1 ครั้ง จะเห็นว่าจุดเปลี่ยนตำแหน่งไป (ลอง Plot กราฟดูก็ดีนะ)

ลองทำ Machine Learning ใน Excel เทคนิค K-Means Clustering แบบไม่ง้อ VBA 17

กด F9 ดูอีกซักรอบ จะเห็นเลยว่าจุดค่อยๆ ขยับ

ลองทำ Machine Learning ใน Excel เทคนิค K-Means Clustering แบบไม่ง้อ VBA 18

คราวนี้เราขี้เกียจมานั่งกด F9 หลายๆ รอบ ดังนั้นเราไปแก้ Option ให้มัน iterate ทีละ 10 ดีกว่า

ลองทำ Machine Learning ใน Excel เทคนิค K-Means Clustering แบบไม่ง้อ VBA 19

แล้วเราก็ลองกด F9 ดู จะเห็นว่าคราวนี้มันทำทีเดียว 10 รอบต่อการกด F9 1 ครั้งเลย

ลองทำ Machine Learning ใน Excel เทคนิค K-Means Clustering แบบไม่ง้อ VBA 20

ถ้ากดอีกที ก็จะเพิ่มอีก 10 จะเห็นว่าจุดไม่เปลี่ยนตำแหน่งแล้ว แสดงว่านี่แหละคือจุดสมดุลย์ที่จะเป็นจุดกึ่งกลางของแต่ละกลุ่มของเราแล้วล่ะครับ

ลองทำ Machine Learning ใน Excel เทคนิค K-Means Clustering แบบไม่ง้อ VBA 21

หมายเหตุ : จริงๆ เราสามารถใส่จำนวน Iteration เยอะๆ เช่น 100 และทำแบบ Auto Calculate ก็ได้นะครับ แค่ผมทำแบบ Manual ให้ดูจะได้เห็นผลชัดๆ เฉยๆ

ลอง Random จุดเริ่มต้นใหม่อีกที

มันมีความเป็นไปได้ที่จุดเริ่มต้นแบบนึง ก็จะได้ผลลัพธ์แบบนึง ดังนั้นเราจะลอง Random จุดเริ่มต้นใหม่ โดยเราจะสร้าง Cell ขึ้นมาทำหน้าที่เป็น Switch เช่น H2 โดยเงื่อนไขคือ ถ้า H2=1 เราจะทำการ Random ใหม่นั่นเอง

ดังนั้นเราจะแก้สูตรของค่า X,Y ของ Center เป็นแบบนี้แทน

คำนวณค่า X (ลาก B2:B6 ใส่สูตรแล้วกด Ctrl+Enter เพื่อใส่สูตรพร้อมกัน)

=IF($H$2="Y",RANDBETWEEN($B$9,$B$10),AVERAGEIFS(Mall_Customers[Income],Mall_Customers[อยู่กลุ่มไหน],Pointกลาง!A2))

คำนวณค่า Y (ลาก C2:C6 ใส่สูตรแล้วกด Ctrl+Enter เพื่อใส่สูตรพร้อมกัน)

=IF($H$2="Y",RANDBETWEEN($C$9,$C$10),AVERAGEIFS(Mall_Customers[Spending],Mall_Customers[อยู่กลุ่มไหน],Pointกลาง!A2))

แล้วเราก็แก้ F2 ใหม่ด้วย เป็นดังนี้

=IF(H2="Y",1,F2+1)

ซึ่งพอใส่ให้ H2 เป็น Y แล้วกด F9 จะพบว่าจุดจะเปลี่ยนที่เพราะถูก Random ใหม่ และจำนวนรอบถูก Reset

ลองทำ Machine Learning ใน Excel เทคนิค K-Means Clustering แบบไม่ง้อ VBA 22

คราวนี้ลองลบค่า Y ออกจากช่อง H2 แล้วกดปุ่ม F9 จะได้ว่าจุดจะเปลี่ยนที่เพราะคำนวณค่า Mean ของกลุ่ม พอกดไปเรื่อยๆ คราวนี้จะไปสมดุลย์ที่นี่

ลองทำ Machine Learning ใน Excel เทคนิค K-Means Clustering แบบไม่ง้อ VBA 23

เดี๋ยวเราจะลอง Random ค่าหลายๆ แบบ แล้ว Plot กราฟให้เห็นกลุ่ม และจุดศูนย์กลางชัดๆ

Plot กราฟ แยกกลุ่มให้เห็นภาพชัดๆ

เพื่อที่จะ Plot Scatter Plot แยกสีตามกลุ่มได้ เราต้องมี Data หลายๆ คอลัมน์ตามกลุ่มซะก่อน โดยผมใช้ Power Query Pivot Column แตก Group ออกมาเป็นหลายๆ คอลัมน์ซะ อันนี้เป็น MCode ของ query ที่ผมสร้างจาก table Mall_Customers นะครับ (code ดูน่าปวดหัว แต่จริงๆ เกิดจากการกดปุ่มไปเรื่อยๆ ไม่มีอะไรต้องเขียนเองเลย)

let
     Source = Excel.CurrentWorkbook(){[Name="Mall_Customers"]}[Content],
     #"Renamed Columns1" = Table.RenameColumns(Source,{{"Annual Income (k$)", "Income"}, {"Spending Score (1-100)", "Spending"}}),
     #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1",{"อยู่กลุ่มไหน", "Income", "Spending"}),
     #"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns1",{{"อยู่กลุ่มไหน", type text}, {"Income", Int64.Type}, {"Spending", Int64.Type}}),
     #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
     #"Pivoted Column" = Table.Pivot(#"Added Index", List.Distinct(#"Added Index"[อยู่กลุ่มไหน]), "อยู่กลุ่มไหน", "Spending", List.Sum),
     #"Sorted Rows" = Table.Sort(#"Pivoted Column",{{"Index", Order.Ascending}}),
     #"Removed Other Columns" = Table.SelectColumns(#"Sorted Rows",{"Income", "Center1", "Center2", "Center3", "Center4","Center5"}),
     #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"Center1", "Group1"}, {"Center2", "Group2"}, {"Center3", "Group3"}, {"Center4", "Group4"}, {"Center5", "Group5"}})
 in
     #"Renamed Columns"
ลองทำ Machine Learning ใน Excel เทคนิค K-Means Clustering แบบไม่ง้อ VBA 24

แล้วค่อยสร้างกราฟ จาก Data ที่ Link จาก PrepareChart มาอีกที (ถ้าสร้างจากผลลัพธ์ query ตรงๆ แล้วบางที cell มันเลื่อนตำแหน่ง) โดยเขียนสูตรประมาณนี้

=IF(PrepareChart!A2="",NA(),PrepareChart!A2)
ลองทำ Machine Learning ใน Excel เทคนิค K-Means Clustering แบบไม่ง้อ VBA 25

จะเห็นว่าการจัดกลุ่มแต่ละรอบอาจไม่เหมือนกันก็ได้ บางอันก็ดูดีกว่าอันอื่น เช่นอันนี้ ผมว่าดูดีที่สุดในความคิดผม

ลองทำ Machine Learning ใน Excel เทคนิค K-Means Clustering แบบไม่ง้อ VBA 26

ส่วนอันอื่นๆ ก็ได้ผลเปลี่ยนไป ขึ้นอยู่กับ Random ตอนแรกไปลงแถวๆ ไหน

ลองทำ Machine Learning ใน Excel เทคนิค K-Means Clustering แบบไม่ง้อ VBA 27
ลองทำ Machine Learning ใน Excel เทคนิค K-Means Clustering แบบไม่ง้อ VBA 28

จบแล้ว

ในบทความนี้ก็เป็นการแนะนำ Concept ของการใช้เทคนิค K-Means Clustering และการใช้โหมด Iterative Calculation ใน Excel ครับ แต่ว่ายังไม่ได้แสดงวิธีหาจำนวนกลุ่มที่ Optimize ที่สุดนะครับ ว่า k ควรจะมีค่าเป็นเท่าไหร่? ไว้เดี๋ยวมีโอกาสจะเขียนแนะนำวิธีตัดสินใจให้อีกที แต่ถ้าใครอยากรู้ตอนนี้ก็สามารถไปอ่านเพิ่มเติมได้ที่นี่ก่อนได้ครับ

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

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

จากเนื้อหาตอนที่แล้วผมได้แสดงวิธีทำ 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 นะครับ

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

เริ่มเตรียมข้อมูลเพื่อทำ 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 นะครับ)

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

พอสร้าง 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)

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

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

พอได้แบบนี้แล้วต่อไปผมก็จะต้องคำนวณพวก 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)

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

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

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

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

แบ่ง Segment ลูกค้าด้วย RFM Analysis : ตอนที่ 2 ทำด้วย DAX แบบ Static 33
SegmentDescriptionRFM
สุดยอดลูกค้าสุดยอดลูกค้า ดีสุดในทุกด้าน4,54,54,5
เคยเป็นสุดยอดแต่หายไปนานเคยสุดยอด แต่ไม่ซื้อนานแล้ว1,24,54,5
ลูกค้าใหม่จ่ายเยอะลูกค้าใหม่ เพิ่งมาซื้อครั้งแรกๆ ซื้อก้อนโต4,514,5
ลูกค้าใหม่จ่ายน้อยลูกค้าใหม่ เพิ่งมาซื้อครั้งแรกๆ ซื้อนิดเดียว4,511,2,3
นานมาทีจ่ายเยอะนานๆ มาที ซื้อก้อนใหญ่3,4,51,24,5
นานมาทีจ่ายเยอะแต่หายไปนานนานๆ มาที ซื้อก้อนใหญ่ แต่ไม่ซื้อนานแล้ว1,21,24,5
มาบ่อยจ่ายน้อยมาบ่อยๆ แต่ซื้อนิดเดียว3,4,54,51,2
มาบ่อยจ่ายน้อยแต่หายไปนานมาบ่อยๆ แต่ซื้อนิดเดียว แต่ไม่ซื้อนานแล้ว1,24,51,2
ลูกค้าประจำลูกค้าประจำ ซื้อบ่อย3,4,53,4,51,2,3,4,5
ลูกค้าประจำแต่หายไปนานเคยเป็นลูกค้าประจำ แต่ไม่ซื้อนานแล้ว1,23,4,51,2,3,4,5
ไม่ค่อยสำคัญน้อยในทุกด้าน1,21,21,2
อื่นๆไม่เข้าพวกข้างบน 1,2,3,4,5 1,2,3,4,5 1,2,3,4,5
ตารางเกณฑ์ RFM อันนี้ผมคิดขั้นเอง ใครจะเอาไปใช้ก็เอาไปใช้ได้เลย ไม่ต้องมาขอผมก็ได้ครับ แค่ให้เครดิต ThepExcel ก็พอครับ

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

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

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

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

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

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

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

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 ลูกค้าด้วย RFM Analysis : ตอนที่ 2 ทำด้วย DAX แบบ Static 37

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

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

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

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

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

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

CustCount = DISTINCTCOUNT(sales_data[CUSTOMERNAME])

สร้าง Visual

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

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

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

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

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

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

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

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

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

แบ่ง Segment ลูกค้าด้วย RFM Analysis  : ตอนที่ 1 ทำด้วย Excel

แบ่ง Segment ลูกค้าด้วย RFM Analysis : ตอนที่ 1 ทำด้วย Excel

การแบ่งกลุ่มลูกค้าหรือ การทำ Customer Segmentation เพื่อบริหารจัดการลูกค้าแต่ละกลุ่มอย่างเหมาะสมเป็นเรื่องที่สำคัญต่อการทำธุรกิจมาก เพราะว่าเราไม่สามารถลงแรงเพื่อดูแลลูกค้าทุกคนเท่าๆ กันได้ขนาดนั้น (ลองคิดตามกฎ 80:20 สิ)

แต่เราจะแบ่งลูกค้ายังไงดีล่ะ? แบ่งตามอะไร? แค่แบ่งตามยอดซื้อเท่านั้นเหรอ?
Model หนึ่งที่สามารถใช้ Data มาช่วยในการแบ่งกลุ่มลูกค้าได้นั่นก็คือ RFM Model หรือ RFM Analysis นั่นเอง

RFM Analysis คืออะไร?

RFM Analysis คือเทคนิคที่ใช้ Data เกี่ยวกับพฤติกรรมการซื้อของของลูกค้าในการแบ่งกลุ่มลูกค้า โดยขึ้นกับปัจจัย 3 ตัว ซึ่งย่อเป็น RFM นั่นก็คือ

  • Recency : ซื้อครั้งล่าสุดสดๆ ร้อนๆ แค่ไหน? (Recent ที่แปลว่าไม่นานมานี้)
  • Frequency : ซื้อบ่อยแค่ไหน?
  • Monetary : มูลค่าเงินที่ซื้อเยอะแค่ไหน?

Edit : เดิมที ผมเขียนบทความนี้คิด Monetary เป็นยอดเงินรวมที่ลูกค้านั้นจ่ายทั้งหมดเลย แต่มีผู้รู้ คือ อาจารย์เช็ค Thanachart Ritbumroong ได้ให้คำแนะนำว่า ควรจะคิดจากมูลค่าเงินเฉลี่ยต่อครั้ง (Ticket Size) จะดีกว่าการคิดรวม (ซึ่งเป็นวิธีโบราณ) เพราะลูกค้าแต่ละคนมีอายุการเป็นลูกค้าไม่เท่ากัน ซึ่งผมก็เห็นด้วยเลยครับ การคิดจากยอดรวมมันมีความซ้ำซ้อนกับความถี่ในการซื้อไปแล้วด้วยซ้ำ ดังนั้นผมจึงจะขอแก้บทความนี้ใหม่ เป็นคิดเงินจากยอดเฉลี่ยต่อครั้งแล้วกันครับ

จากนั้นก็เอาข้อมูลของลูกค้ามาให้คะแนนในแต่ละด้าน โดยอาจจะให้คะแนน 1-5 ก็ได้ (1 คือคะแนนน้อย, 5 คือเยอะ) หรือบางค่ายอาจแบ่งเป็น 4 ท่อนก็ได้ อันนี้แล้วแต่เลยครับ แต่ในที่นี้ผมจะแบ่ง 5 ให้ดู เพราะแบ่ง 4 มันง่ายไป อิอิ

จากนั้นก็เอาผลที่ได้มาแบ่งเป็น Segment อีกทีซึ่งมีหลายสูตรมากๆ บางสำนักก็แบ่งเป็น 6 กลุ่มบ้าง 12 กลุ่มบ้าง ซึ่งผมว่ามันไม่ใช่สาระสำคัญ แต่สิ่งสำคัญคือการคัดเลือกกลุ่มที่เราควรจะเข้าไปจัดการต่อมากกว่า ว่าจะจัดการกลุ่มไหนยังไงดี? บางทีแบ่งเยอะไปก็จัดการไม่ถูกนะ

ข้อมูลตัวอย่าง

เพื่อให้เห็นภาพมากขึ้น เรามาลองวิเคราะห์ข้อมูลตัวอย่างอันนี้กันครับ เป็นไฟล์ csv นะ
https://www.kaggle.com/kyanyoga/sample-sales-data

คัดเลือกข้อมูลที่ต้องการ

ในไฟล์จะมีข้อมูลที่ไม่เกี่ยวข้องอยู่ด้วยเต็มไปหมด ผมจะขอคัดเลือกมาเฉพาะที่ต้องการ ซึ่งผมจะขอใช้ Power Query ทำเพราะสามารถจัดการประเภทข้อมูลได้ด้วยง่ายดี (แต่เพื่อนๆ จะใช้ Excel ปกติทำก็ได้นะ)

สรุปแล้ว ผมใช้ Data แค่นี้ก็เพียงพอต่อการทำ RFM แล้วล่ะ นั่นก็คือ ชื่อลูกค้า วันที่ซื้อของ และมูลค่าการซื้อครั้งนั้น

แบ่ง Segment ลูกค้าด้วย RFM Analysis : ตอนที่ 1 ทำด้วย Excel 43

พอได้ข้อมูลแบบนี้ เดี๋ยวเราจะเอาไปเข้า PivotTable ต่อเลย เพื่อหาข้อมูลเป็นรายลูกค้า (จริงๆ จะใช้ Group By ใน Power Query ก็ได้ แต่กลัวบางคนใช้ไม่เป็น)

พอเอาเข้า Pivot Table เราก็จะลากสรุปข้อมูลรายลูกค้าดังนี้

แบ่ง Segment ลูกค้าด้วย RFM Analysis : ตอนที่ 1 ทำด้วย Excel 44

ผมลาก OrderDate ลงมา 2 รอบ ซึ่งมันจะทำการสรุปด้วย Count ให้ ผมจะเปลี่ยนให้สรุปด้วยการใช้ค่า Max แทน เพื่อให้ได้วันที่ล่าสุดที่ซื้อ (อย่าลืมว่าวันที่คือตัวเลข ดังนั้นหาค่า Max ได้) ส่วน OrderDate2 เป็นการนับซึ่งผมจะถือว่าเป็น Frequency การซื้อของลูกค้าคนนั้นไปซะ

การเปลี่ยนวันที่ในคอลัมน์ B เป็น MAX จะได้เลข 3 หมื่นเกือบ 4 หมื่นออกมา ซึ่งมันคือค่าที่แท้จริงของวันที่ เราแค่กดคลิ๊กขวา Number Format แล้วเปลี่ยน Format เป็นวันที่ได้เลย ส่วนยอดเงินเฉลี่ยต่อครั้งในคอลัมน์ E ก็เกิดจากการเอายอดเงินรวมหารด้วยจำนวนครั้งที่ซื้อครับ (อันนี้ใช้วิธีหารเอาข้างๆ แบบลูกทุ่งเลย ในความเป็นจริงจะใช้ Calculated Field หรือ Measure ก็ได้นะครับ)

แบ่ง Segment ลูกค้าด้วย RFM Analysis : ตอนที่ 1 ทำด้วย Excel 45

ที่นี้เราก็ได้ค่า R F M แล้วดังนี้

แบ่ง Segment ลูกค้าด้วย RFM Analysis : ตอนที่ 1 ทำด้วย Excel 46

แบ่งคะแนนด้วยวิธีไหนดี?

หากเราเอาแค่ค่าน้อยสุดมากสุดมาแบ่งเป็น 5 ท่อนเฉยๆ มันจะไม่ Work อย่างแรง เนื่องจากข้อมูลมันเบ้ขวามากๆ ทำให้แต่ละท่อนมีข้อมูลต่างกันมากเลย ดังนั้นวิธีที่น่าจะ Work ที่สุดก็คือการหาค่า Percentile ที่ 20,40,60,80 มาเป็นตัวแบ่งนั่นเอง

หมายเหตุ : ที่เราสามารถคำนวณข้อมูลวันที่ได้เลยตรงๆ ไม่ต้องเอามาลบหาระยะห่างวัน เพราะถ้าวันที่ใหม่ เลขจะเยอะ ทำให้ได้อยู่ในกลุ่มคะแนนมากอยู่แล้ว

แต่ถ้าหากเราทำใน Excel เราสามารถใช้ PERCENTRANK มาช่วยหาได้ว่าค่าที่สนใจอยู่ที่ Percentile เท่าไหร่ แล้วเราค่อยมาปรับให้เป็น 1-5 อีกทีก็ได้ ดังนี้

อันนี้คำนวณอันดับ Percentile ของแต่ละค่าออกมาก่อน

=PERCENTRANK.INC(G$2:G$93,G2)
แบ่ง Segment ลูกค้าด้วย RFM Analysis : ตอนที่ 1 ทำด้วย Excel 47

จากนั้นปรับให้เป็น Score 1-5 ซะ

=IF(K2=0,1,CEILING.MATH(K2/0.2,1))
แบ่ง Segment ลูกค้าด้วย RFM Analysis : ตอนที่ 1 ทำด้วย Excel 48

ได้เวลาจัด Segment

การจัด Segment นั้นทำได้หลายแบบมากๆ เช่น ถ้าไปดูตามเว็บต่างๆ จะเห็นว่ามีจัดเป็น Champions, Loyal Customer, New Customers อะไรแบบนี้ ซึ่งจริงๆ มันไม่ได้มีมาตรฐานสากลอะไร เช่น อันนี้คือไปเจอมาจากเว็บเมืองนอกดังนี้

SegmentDescriptionRFM
ChampionsBought recently, buy often and spend the most4 – 54 – 54 – 5
Loyal CustomersSpend good money. Responsive to promotions2 – 43 – 44 – 5
Potential LoyalistRecent customers, spent good amount, bought more than once3 – 51 – 31 – 3
New CustomersBought more recently, but not often4 – 5< 2< 2
PromisingRecent shoppers, but haven’t spent much3 – 4< 2< 2
Need AttentionAbove average recency, frequency & monetary values3 – 43 – 43 – 4
About To SleepBelow average recency, frequency & monetary values2 – 3< 3< 3
At RiskSpent big money, purchased often but long time ago< 32 – 52 – 5
Can’t Lose ThemMade big purchases and often, but long time ago< 24 – 54 – 5
HibernatingLow spenders, low frequency, purchased long time ago2 – 32 – 32 – 3
LostLowest recency, frequency & monetary scores< 2< 2< 2
https://blog.rsquaredacademy.com/customer-segmentation-using-rfm-analysis/

ถ้าพิจารณาดูแล้ว จะเห็นว่า คะแนนในตารางข้างบนที่ผมหามาได้ ตัว M ของเค้าหมายถึงยอดเงินรวม ไม่ใช่ยอดเฉลี่ยต่อครั้งเหมือนอันใหม่ที่ผมทำ ดังนั้นตารางนี้จึงไม่เหมาะกับผม

ในบทความนี้ผมจึงคิดเกณฑ์ใหม่ขึ้นมาเองให้เหมาะสมกับการให้คะแนนของผมเอง ตามตารางนี้ครับ (เราคิดการแบ่งของเราเองได้นะ ไม่ได้ผิด!) โดยที่หากลูกค้าคนใดสามารถอยู่ได้หลายกลุ่มพร้อมกัน ผมจะถือว่าให้เค้าอยู่ในกลุ่มที่สูงที่สุดยึดตาม Priority ใน List นี้ด้วยครับ

ผมเอาคะแนนที่เป็นไปได้ใส่ลงไปใน Cell โดยคั่นด้วย comma เลยแบบนี้ จะได้อ่านค่าได้ตรงๆ ด้วย FIND, SEARCH ได้ง่าย

SegmentDescriptionRFM
สุดยอดลูกค้าสุดยอดลูกค้า ดีสุดในทุกด้าน4,54,54,5
เคยเป็นสุดยอดแต่หายไปนานเคยสุดยอด แต่ไม่ซื้อนานแล้ว1,24,54,5
ลูกค้าใหม่จ่ายเยอะลูกค้าใหม่ เพิ่งมาซื้อครั้งแรกๆ ซื้อก้อนโต4,514,5
ลูกค้าใหม่จ่ายน้อยลูกค้าใหม่ เพิ่งมาซื้อครั้งแรกๆ ซื้อนิดเดียว4,511,2,3
นานมาทีจ่ายเยอะนานๆ มาที ซื้อก้อนใหญ่3,4,51,24,5
นานมาทีจ่ายเยอะแต่หายไปนานนานๆ มาที ซื้อก้อนใหญ่ แต่ไม่ซื้อนานแล้ว1,21,24,5
มาบ่อยจ่ายน้อยมาบ่อยๆ แต่ซื้อนิดเดียว3,4,54,51,2
มาบ่อยจ่ายน้อยแต่หายไปนานมาบ่อยๆ แต่ซื้อนิดเดียว แต่ไม่ซื้อนานแล้ว1,24,51,2
ลูกค้าประจำลูกค้าประจำ ซื้อบ่อย3,4,53,4,51,2,3,4,5
ลูกค้าประจำแต่หายไปนานเคยเป็นลูกค้าประจำ แต่ไม่ซื้อนานแล้ว1,23,4,51,2,3,4,5
ไม่ค่อยสำคัญน้อยในทุกด้าน1,21,21,2
อื่นๆไม่เข้าพวกข้างบน 1,2,3,4,5 1,2,3,4,5 1,2,3,4,5
ตารางเกณฑ์ RFM อันนี้ผมคิดขั้นเอง ใครจะเอาไปใช้ก็เอาไปใช้ได้เลย ไม่ต้องมาขอผมก็ได้ครับ แค่ให้เครดิต ThepExcel ก็พอครับ

โดยที่ผมสร้าง Table ขึ้นมาว่า RFMtable ดังนี้

แบ่ง Segment ลูกค้าด้วย RFM Analysis : ตอนที่ 1 ทำด้วย Excel 49

เพื่อให้เห็นภาพชัดที่สุด ผมจะเอา 12 Segment นี้ไปเป็นหัวตาราง แล้วคำนวณว่าลูกค้าแต่ละคนเข้า Segment นั้นได้หรือไม่ ดังนี้

สูตรใน S2 เป็นดังนี้ (ใช้ Logic AND ในการคิดแต่ละช่อง)

=AND(ISNUMBER(FIND($O2,INDEX(RFMtable[R],MATCH(S$1,RFMtable[Segment],0)))),
 ISNUMBER(FIND($P2,INDEX(RFMtable[F],MATCH(S$1,RFMtable[Segment],0)))),
 ISNUMBER(FIND($Q2,INDEX(RFMtable[M],MATCH(S$1,RFMtable[Segment],0)))))
แบ่ง Segment ลูกค้าด้วย RFM Analysis : ตอนที่ 1 ทำด้วย Excel 50

ทีนี้เราจะหาว่า FinalSegment โดยหลักการคือ หาว่า TRUE ตัวแรกอยู่ที่อันไหน อันนี้ใช้ INDEX+MATCH มาช่วยได้ดังนี้

=INDEX($S$1:$AD$1,MATCH(TRUE,S2:AD2,0))
แบ่ง Segment ลูกค้าด้วย RFM Analysis : ตอนที่ 1 ทำด้วย Excel 51

พอเอาข้อมูลไป Pivot ก็จะได้ผลลัพธ์ประมาณนี้

แบ่ง Segment ลูกค้าด้วย RFM Analysis : ตอนที่ 1 ทำด้วย Excel 52

สรุป

จะเห็นว่าวิธีที่ผมทำให้ดูในบทความนี้ ค่อนข้างทุกลักทุเลหน่อยๆ คือมี Pivot แล้วมาทำต่อข้างนอก Pivot แล้วเอาไป Pivot ใหม่อีกที แต่มันก็สามารถได้คำตอบเหมือนกัน

ในตอนต่อไป ผมจะทำด้วยวิธีใช้ DAX ใน Power BI ซึ่งจะสามารถเขียน Measure ออกมาคำนวณให้ได้ผลลัพธ์ที่ต้องการได้แบบอัตโนมัติกว่านี้ครับ ใครสนใจก็รอติดตามได้เลยครับ

Math Skill vs Stage of Life : Excel Version

Math Skill vs Stage of Life : Excel Version

คิดว่าเพื่อนๆ คงพอเคยเห็น Meme ตัวนี้มาแล้วว่า เรียน Math ยากๆ เช่น Calculus มาแทบตาย สุดท้ายมาถึงวัยทำงานก็เหลือแค่ Spreadsheet หรือ Excel ที่ใช้แค่การบวกลบคูณหารธรรมดาๆ…

เพื่อให้ภาพมันครบขึ้น ก็เลยลองวาดเส้นต่อดูว่าในความคิดของผม เวลาใช้ Excel จริงๆ มันก็มีเรื่องที่ยากขึ้นเรื่อยๆ แล้วก็กลับมาง่ายแล้วก็ยากใหม่สลับไปมาเช่นกันตัวอย่างเช่น เราใช้ Excel จากพื้นฐานไปจนถึง ทำ Pivot ได้ (ซึ่งการทำ Pivot เป็นนี่ง่ายกว่าแก้สมการสมัยเด็กจริงๆ นะ 555) สามารถเขียนสูตร Array Formula ยากๆ ได้ ใช้ VBA เขียนโปรแกรมซับซ้อนได้… (อันนี้มีตั้งแต่ง่าย จนยากพอๆ กับ Calculus แหละ)

ไปๆ มาๆ ในช่วงหลังผมใช้แค่ Power Query อย่างเดียวสามารถเตรียมข้อมูลทดแทนสูตรยากๆ และ VBA ได้เกือบหมดเลย ซึ่งมีตั้งแต่กดเครื่องมือง่ายๆ อย่างเดียว ไปจนถึงการใช้งานที่ลึกขึ้นไปเรื่อยๆ ก็จะเจอเรื่องยากอีกครั้งนั่นคือ Power Query M Code

พอใช้ Power Query เตรียมข้อมูลได้ ก็ไปถึงขั้นสรุปข้อมูล ที่ต้องเปลี่ยนจาก Pivot Table ธรรมดาที่มีข้อจำกัด ไปใช้ Power Pivot/Power BI แทน ซึ่งต้องใช้ ภาษา DAX ซึ่งมีความสามารถเยอะมากๆ แล้วก็ลึกมากๆ เช่นกัน

ผมมองว่าเจ้า Power Query M Code และ DAX เนี่ยมีเนื้อหาเยอะจนเรียนกันไม่มีวันสิ้นสุดเลย ขอบอกกกก และหลายเรื่องมันลึกซึ้งและใช้ Logic สูงไม่แพ้การทำโจทย์ Calculus และการเขียนโปรแกรมเลยนะ

แล้วเพื่อนๆ เองคิดยังไงกับกราฟนี้บ้างครับ? มองว่าอะไรง่าย อะไรยาก เวลาใช้งานจริงใช้แค่ไหน? คล้ายกันกับของผมมั้ยครับ ^^

หากอย่างเรียนรู้เกี่ยวกับ Power Query ลองดูคอร์สออนไลน์นี้ครับ

 

คอร์สออนไลน์ Excel Power Up!

Math Skill vs Stage of Life : Excel Version 53

Excel Power Up 2021 : พลังแห่งข้อมูล สร้างได้ด้วย Power Query

คอร์สนี้เป็น version อัปเดทเนื้อหาให้เข้าใจง่ายขึ้นครับ (คนที่เคยลงอันเก่าแล้ว สามารถเข้าเรียนอันนี้ได้ฟรี)

เพื่อนๆ จะได้เรียนรู้การใช้เครื่องมือ Power Query ซึ่งมีประโยชน์ในการรวบรวมและดัดแปลงข้อมูลให้อยู่ในรูปแบบที่เหมาะสม ซึ่งมีความสามารถดังนี้

  • ช่วยรวบรวมข้อมูลได้หลายรูปแบบ ทั้ง จากหลายๆ ชีท หลายๆ ไฟล์/ format
  • ดัดแปลงข้อมูลให้มีหน้าตาเหมาะต่อการนำไปใช้งานต่อ เช่น เอาไปวิเคราะห์ต่อด้วย PivotTable / Data Model
  • เวลาจะทำซ้ำ แค่กด Refresh ปุ่มเดียว เหมาะกับงาน Routine มาก
  • Power Query เป็นเครื่องมือที่เป็นส่วนหนึ่งของ Excel Version 2016 ขึ้นไป  (ถ้า Excel 2013 ต้องโหลด Add-in เพิ่ม) และมีใน Power BI ด้วย
  • ในคอร์ส Power Query นี้จะเน้นเรื่อง Power Query โดยเฉพาะ ซึ่งจะมีเนื้อหาที่ลงลึกกว่า Section Power Query Editor ที่อยู่ในคอร์ส Power BI
  • Scope เนื้อหาในคอร์สนี้จะคล้ายกับหนังสือ Excel Power Up แต่จะมีการยกตัวอย่างการประยุกต์ใช้จริงมากกว่าหนังสือครับ
  • ราคา 1,590 บาท เนื้อหาจัดเต็ม