Building Customer Single View — Customer 360

ใครก็ตามที่ทำ Analytics คงจะรู้จักกับคำว่า Customer Single View สำหรับใครที่เพิ่งเริ่มๆ นะครับ Customer Single View ก็คือ การสรุปทุกสิ่งทุกอย่างของลูกค้า 1 คนให้เหลือข้อมูลเพียงแค่ 1 record

ตัว Single Record ก็คือ Single View ของลูกค้า 1 คนนี่แหละครับ แต่การที่ 1 record จะสามารถแสดงภาพทั้งหมดของลูกค้าคนเดียวได้นั้น ก็จะต้องมีการกำหนด attributes หรือ variables ของลูกค้าที่เราอยากทราบ

วันนี้จะมาทำตัวอย่างของ Customer Single View แบบง่ายๆ ให้ดูกันนะครับ

ข้อมูลสำหรับ Case Study

ข้อมูลที่วิเคราะห์สนุกที่สุด ก็คงจะเป็นข้อมูล Retail เพราะมี customer interaction และ transactions เยอะที่สุด

ข้อมูลตัวอย่างที่จะมาสาธิตให้ดู ก็คือ ข้อมูล Supermarket จาก dunnhumby (เป็นบริษัทที่ทำการวิเคราะห์ข้อมูลให้กับ Tesco ครับ)

ซึ่ง Dunnhumby ก็ได้เอาข้อมูล Transactions ของ Supermarket มาปล่อยให้เราได้วิเคราะห์เล่น แต่เป็นข้อมูลปีเก่าๆ นะครับ ไปลอง download มาเล่นได้เลย

หน้าตาของ Table ก็จะเป็นแบบด้านล่างเลยครับ

Table Structure

ข้อมูลก็จะเป็นราย Transaction Items ก็คือ ละเอียดถึงสินค้าแต่ละชิ้นในตะกร้า (สินค้าหลายๆ ชิ้น รวมกันเป็น 1 ตะกร้า)


Customer Single View Design

การออกแบบ Customer Single View ก็จะเริ่มจากการกำหนด Attributes หรือ Variables ที่เราสนใจในตัวลูกค้า ซึ่งก็จะมาจากฝั่ง Business นะครับ ว่าอยากจะรู้อะไรในตัวลูกค้าบ้าง

ถ้ามืดแปดด้าน ไม่รู้จะเริ่มจากไหน ก็เริ่มจากอะไรที่ง่ายที่สุดครับ มาเริ่มกับ RFM Framework ก่อนเลยก็ได้

RFM ย่อมาจาก Recency, Frequency, and Monetary ครับ ก็คือ ดูว่า ลูกค้ามาล่าสุดเมือไหร่ มาบ่อยขนาดไหน ใช้จ่ายเป็นตัวเงินมากขนาดไหน

ถ้ามาดูในภาพรวมก่อน ก็จะได้เป็นตัวแปร

  1. Last Visit (R)
  2. Total Visit (F)
  3. Total Spend (M)

มาเริ่มสร้าง Customer Single View ด้วย SQL แบบง่ายๆ กันเลยนะครับ เราได้ Upload data จาก dunnhumby ไปอยู่บน Google BigQuery เป็นที่เรียบร้อยแล้ว

Google BigQuery ก็เป็น Cloud Data Warehouse ของ Google ครับ SQL ก็เป็น SQL 2011 standard และมี extensions บางอย่างเพิ่มอีกนิดหน่อย

หน้าตาข้อมูลจะเป็นแบบรูปด้านล่างนะครับ

ตัวแปร 3 ตัวที่เรากำหนดกันไว้เบื้องต้น ก็คือ Last Visit, Total Visit, Total Spend ก็มาแปลงเป็น SQL ง่ายๆ ดังนี้ครับ

SELECT
 CUST_CODE,
 MAX(SHOP_DATE) AS LAST_VISIT,
 COUNT(DISTINCT BASKET_ID) AS TOTAL_VISIT,
 SUM(SPEND) AS TOTAL_SPEND
FROM
 [sut-bigdata-166022:GROCERY.TRANSACTIONS]
WHERE
 CUST_CODE IS NOT NULL
GROUP BY
 CUST_CODE

เป็นอันเสร็จเรียบร้อย BigQuery ก็ทำการ process data ได้อย่างรวดเร็ว ของเค้าดีจริงๆ อิอิ

หมายเหตุ ตอน Query เราจะตัดลูกค้าที่ CUST_CODE เป็น NULL ออกนะครับ เป็นพวก Non-Members ต้องแยกออกไปต่างหากครับ

แต่ถ้าดูแค่ภาพรวมนั้น จะคลาดเคลื่อนไปเยอะใช่ไหมครับ เพราะลูกค้าแต่ละคน เริ่มเป็นลูกค้ามายาวนานไม่เท่ากัน การดูแค่ Total อย่างเดียว จะไม่ถูกต้อง เราก็ต้องมาดูว่าแต่ละคนนั้น มีการแวะมาหาเราเฉลี่ยเท่าไหร่ ซึ่งการเฉลี่ยนั้น ก็ต้องกำหนดกรอบเวลาให้เหมาะสมในแต่ละธุรกิจ Supermarket อาจจะดูเป็นรายสัปดาห์ หรือ รายเดือน แต่โรงพยาบาล มาดูรายเดือนก็คงไม่เหมาะ อาจจะเป็นรายปีไปเลย

ตัวอย่างนี้ เรามาหาเป็นรายสัปดาห์กันครับ ก็จะมีตัวแปรเพิ่มอีก 2 ตัว คือ

4. Average Weekly Visit (F)

5. Average Weekly Spend (M)

พอเปลี่ยนเป็น SQL ก็ง่ายๆ ครับ หา Total Visit กับ Total Spend มาหารด้วย No. of Active Week

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

6. Average Basket Size (M)

เหมือนเดิมครับ ก็นำ Total Spend มาหารด้วย No. of Basket

SELECT
 CUST_CODE,
 MAX(SHOP_DATE) AS LAST_VISIT,
 COUNT(DISTINCT BASKET_ID) AS TOTAL_VISIT,
 SUM(SPEND) AS TOTAL_SPEND,
 COUNT(DISTINCT BASKET_ID)/COUNT(DISTINCT SHOP_WEEK) AS AVG_WEEKLY_VISIT,
 SUM(SPEND)/COUNT(DISTINCT SHOP_WEEK) AS AVG_WEEKLY_SPEND,
 SUM(SPEND)/COUNT(DISTINCT BASKET_ID) AS AVG_BASKET_SIZE
FROM
 [sut-bigdata-166022:GROCERY.TRANSACTIONS]
WHERE
 CUST_CODE IS NOT NULL
GROUP BY
 CUST_CODE, SHOP_WEEK

มาถึงขั้นนี้ เราก็ได้ Customer Single View ตามแบบ RFM เบื้องต้นแล้วครับ

Purchase Evolution — Velocity Variables

เพื่อเพิ่มความเก๋ไก๋ให้กับ Customer Single View ของเรา เราจะไม่หยุด แค่ RFM ครับ เราจะไปต่อกันที่ Purchase Evolution หรือ การติดตามการเปลี่ยนแปลงของพฤติกรรมลูกค้าตามช่วงเวลา เช่น เราอาจจะดูย้อนหลังเทียบไปว่า Transaction ล่าสุดของลูกค้า เป็นยังไงมั่ง แล้วย้อนไปดู Transaction 2–3 อันก่อนหน้านั้นเทียบกัน ว่ายอดซื้อ ขึ้น หรือ ลง อย่างมีนัยสำคัญหรือไม่

บางคนก็มองว่าอันนี้เป็น Velocity Variables ว่าลูกค้ามีทิศทางในการจับจ่าย เพิ่มขึ้น หรือ ต่ำลง อันนี้จะดูราย Transaction ก็ได้นะครับ หรือ เป็นรายสัปดาห์ หรือ รายเดือนก็ได้

เพื่อลดความซับซ้อนซ่อนเงื่อนในการแสดงตัวอย่าง จะขอทำเป็น Transaction ให้ดูก่อน

เรามาเริ่มที่การเรียกดูยอดซื้อระดับ Transaction แล้ว ยอดไปดู Transaction ก่อนหน้าไปอีก 2 อันครับ

ถ้าใช้ SQL ธรรมดา ก็จะต้อง Join กันวุ่นวาย แล้วมาลดหลั่นการ join ให้ปวดหัว เคยขอร้อง(แกมบังคับให้คนอื่นทำให้ ทำมายังไงก็ผิดตลอด) ตอนนี้ เราสามารถใช้ Window Function ชื่อว่า LAG() วิ่งไปมองย้อนหา Transaction ที่แล้วมาให้ครับ ง่ายมาก

ขั้นตอนการทำอาจจะปวดหัวหน่อย มี step ประมาณนี้ครับ

  1. ยุบรวมข้อมูลระดับ Item มาให้เป็น Basket ก่อน ก็ SUM(SPEND) GROUP BY CUST_CODE, BASKET_ID จะได้ข้อมูลเป็นราย Customer-Basket เราตั้งชื่อ SUM(SPEND) ระดับตะกร้าเป็น BASKET_SIZE ก่อนนะครับ
  2. หาข้อมูลของตะกร้าที่ผ่านมาของลูกค้าแต่ละคน โดยใช้ LAG()
LAG(BASKET_SIZE, 1) OVER 
(PARTITION BY CUST_CODE ORDER BY BASKET_ID) as PREVIOUS_1BASKET_SIZE

ตัวฟังก์ชัน ก็จะบอกว่าให้ไปหา ที่แล้ว LAG(BASKET_SIZE, 1) ย้อนไป 1 record โดยหาภายใน CUST_CODE เดียวกัน (PARTITION BY CUST_CODE) โดยการย้อนกลับไปนั้นให้เรียงลำดับตาม BASKET_ID (ORDER BY BASKET_ID)

งงมั้ยเอ่ย … แรกๆ ก็จะงงมากๆ เลยครับ ทำไปเรื่อยๆ จะง่ายมากเอง ด้วยความขี้เกียจเราเลยรวม Query เข้าด้วยกัน เป็น Nested Query ครับ

SELECT
 CUST_CODE,
 BASKET_ID,
 BASKET_SIZE,
 LAG(BASKET_SIZE, 1) OVER (PARTITION BY CUST_CODE ORDER BY BASKET_ID) as PREVIOUS_1BASKET_SIZE,
 LAG(BASKET_SIZE, 2) OVER (PARTITION BY CUST_CODE ORDER BY BASKET_ID) as PREVIOUS_2BASKET_SIZE
FROM
(SELECT
 CUST_CODE,
 BASKET_ID,
 SUM(SPEND) as BASKET_SIZE,
FROM
 [sut-bigdata-166022:GROCERY.TRANSACTIONS]
WHERE
 CUST_CODE IS NOT NULL
GROUP BY
 CUST_CODE,
 BASKET_ID)

เราก็จะได้ข้อมูลราย Basket นะครับ เทียบกับ Basket ก่อนหน้านี้ ไป 2 ตะกร้า เป็น Purchase Evolution ของลูกค้าแต่ละราย

ถ้าสังเกตดูนะครับ อันนี้ ยังไม่ใช่ Customer Single View เพราะลูกค้าแต่ละราย ยังมีหลาย Records อยู่ เดี๋ยวเราจะเอาอันนี้ ไป Join กับ RFM Customer Single View อันข้างบนครับ

Combining RFM with Purchase Evolution

เราจะเอา Customer Single View ของ RFM ที่เตียมไว้ มาชนกัน Purchase Evolution ครับ โดย จะไม่เอามาทั้ง Evolution จะเอาเฉพาะ Last Basket ครับ ซึ่ง ใน RFM เราจะ Modify นิดหน่อย เพื่อให้เค้ามี Last Basket ID ไว้ Join กับ Purchase Evolution

ด้วยความขี้เกียจอีกแล้วครับ เราจะรวบทุกอย่างให้อยู่ใน Query เดียวกัน โดยเอา 2 Query มา Join กัน กลายเป็น Nested ซ้อน Nested

SELECT
 A.CUST_CODE AS CUST_CODE,
 LAST_VISIT,
 TOTAL_VISIT,
 TOTAL_SPEND,
 AVG_WEEKLY_VISIT,
 AVG_WEEKLY_SPEND,
 AVG_BASKET_SIZE,
 BASKET_SIZE AS LAST_BASKET_SIZE,
 PREVIOUS_1BASKET_SIZE,
 PREVIOUS_2BASKET_SIZE
FROM (
 SELECT
 CUST_CODE AS CUST_CODE,
 MAX(SHOP_DATE) AS LAST_VISIT,
 MAX(BASKET_ID) AS LAST_BASKET_ID,
 COUNT(DISTINCT BASKET_ID) AS TOTAL_VISIT,
 SUM(SPEND) AS TOTAL_SPEND,
 COUNT(DISTINCT BASKET_ID)/COUNT(DISTINCT SHOP_WEEK) AS AVG_WEEKLY_VISIT,
 SUM(SPEND)/COUNT(DISTINCT SHOP_WEEK) AS AVG_WEEKLY_SPEND,
 SUM(SPEND)/COUNT(DISTINCT BASKET_ID) AS AVG_BASKET_SIZE,
 FROM
 [sut-bigdata-166022:GROCERY.TRANSACTIONS]
 WHERE
 CUST_CODE IS NOT NULL
 GROUP BY
 CUST_CODE,
 SHOP_WEEK) A
JOIN (
 SELECT
 CUST_CODE,
 BASKET_ID,
 BASKET_SIZE,
 LAG(BASKET_SIZE, 1) OVER (PARTITION BY CUST_CODE ORDER BY BASKET_ID) AS PREVIOUS_1BASKET_SIZE,
 LAG(BASKET_SIZE, 2) OVER (PARTITION BY CUST_CODE ORDER BY BASKET_ID) AS PREVIOUS_2BASKET_SIZE
 FROM (
 SELECT
 CUST_CODE,
 BASKET_ID,
 SUM(SPEND) AS BASKET_SIZE,
 FROM
 [sut-bigdata-166022:GROCERY.TRANSACTIONS]
 WHERE
 CUST_CODE IS NOT NULL
 GROUP BY
 CUST_CODE,
 BASKET_ID)) B
ON
 A.CUST_CODE = B.CUST_CODE
 AND A.LAST_BASKET_ID = B.BASKET_ID

เท่านี้ก็เป็นอันเสร็จเรียบร้อยในการทำ Customer Single View ครับ