วิธีคำนวณ Customer Lifetime Value จาก CSV (ทีละขั้นตอน)

ในการคำนวณมูลค่าตลอดช่วงชีวิตของลูกค้า (customer lifetime value) จาก CSV ของรายการธุรกรรม ให้คำนวณมูลค่าการสั่งซื้อเฉลี่ย ความถี่ในการซื้อ และอายุขัยของลูกค้า นำทั้งสามค่ามาคูณกัน จากนั้นคูณด้วยอัตรากำไรขั้นต้นของคุณ หลักการคำนวณนั้นตรงไปตรงมา แต่การตัดสินใจในเชิงดุลยพินิจ เช่น ลูกค้ากลุ่มใดที่ถือว่าเลิกใช้บริการ (churned) และควรจะมองย้อนกลับไปในอดีตหรือมองไปข้างหน้า คือสิ่งที่จะตัดสินว่าผลลัพธ์ที่ได้นั้นมีประโยชน์หรือไม่

ทีมสองทีมสามารถใช้สูตรเดียวกันกับไฟล์เดียวกันแล้วได้ผลลัพธ์สองตัวเลขที่ต่างกันถึงสามเท่า ซึ่งไม่มีทีมใดผิด พวกเขาแค่ตอบคำถามที่ต่างกันโดยไม่ทันสังเกต

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

สิ่งที่คุณต้องเตรียมก่อนเริ่มต้น

คุณต้องมีไฟล์ส่งออกข้อมูลในระดับรายการธุรกรรม (transaction-level export) โดยมีหนึ่งแถวต่อหนึ่งคำสั่งซื้อ ซึ่งประกอบด้วยรหัสระบุตัวตนของลูกค้า วันที่สั่งซื้อ และมูลค่าการสั่งซื้อ การสรุปข้อมูลเป็นรายเดือนจะใช้ไม่ได้ผล เนื่องจากมูลค่าตลอดช่วงชีวิตของลูกค้านั้นโดยพื้นฐานแล้วเป็นเรื่องเกี่ยวกับพฤติกรรมของลูกค้าแต่ละรายเมื่อเวลาผ่านไป

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

แบบย้อนหลัง (Historical) หรือแบบคาดการณ์ (Predictive) มูลค่าตลอดช่วงชีวิตของลูกค้าแบบย้อนหลังจะรวมยอดเงินที่ลูกค้าจ่ายจริงไปแล้วจนถึงปัจจุบัน ส่วนแบบคาดการณ์จะประมาณการสิ่งที่พวกเขาจะจ่ายในอนาคต แบบย้อนหลังนั้นมีหลักฐานอ้างอิงได้ชัดเจนและมักจะต่ำกว่าความเป็นจริงเสมอ ส่วนแบบคาดการณ์จะมีประโยชน์มากกว่าสำหรับการจัดทำงบประมาณ และขึ้นอยู่กับสมมติฐานการรักษาลูกค้า (retention) ของคุณโดยสิ้นเชิง

เกณฑ์การพิจารณาว่าเลิกใช้บริการ (churned) สำหรับธุรกิจแบบสมัครสมาชิก (subscription) เรื่องนี้ทำได้ง่าย นั่นคือวันที่ยกเลิกบริการ แต่สำหรับธุรกิจแบบเน้นการทำธุรกรรม (transactional) จะไม่มีเหตุการณ์ยกเลิกบริการ ดังนั้นคุณจึงต้องกำหนดกฎเกณฑ์ขึ้นมา ตัวอย่างเช่น ลูกค้าที่ไม่ได้ซื้อสินค้าเลยในช่วง 12 เดือนจะถือว่าหมดสถานะลูกค้า (lapsed) ให้เลือกช่วงเวลาหนึ่งและบันทึกไว้

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

วิธีการคำนวณด้วยตนเอง

ทางเลือกที่ 1: คำนวณมูลค่าการสั่งซื้อเฉลี่ยและความถี่ในการซื้อ

จัดกลุ่มไฟล์รายการธุรกรรมตามรหัสระบุตัวตนของลูกค้า สำหรับลูกค้าแต่ละราย ให้รวมมูลค่าการสั่งซื้อและนับจำนวนคำสั่งซื้อ มูลค่าการสั่งซื้อเฉลี่ยคือรายได้ทั้งหมดหารด้วยจำนวนคำสั่งซื้อทั้งหมดในฐานข้อมูล ความถี่ในการซื้อคือจำนวนคำสั่งซื้อทั้งหมดหารด้วยจำนวนลูกค้าที่ไม่ซ้ำกันในช่วงเวลานั้น

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

ทางเลือกที่ 2: เพิ่มเส้นโค้งการรักษาลูกค้า (retention curve)

อายุขัยของลูกค้า (customer lifespan) คือข้อมูลนำเข้าที่คนส่วนใหญ่มักจะเดาเอาเอง เวอร์ชันที่สามารถอ้างอิงได้อย่างสมเหตุสมผลควรมาจากข้อมูลของคุณเอง โดยจัดกลุ่มลูกค้าตามเดือนที่ได้ลูกค้ามา (acquisition month) จากนั้นนับจำนวนลูกค้าที่ยังคงใช้งานอยู่ในแต่ละเดือนถัดๆ ไป

วิธีนี้จะทำให้ได้เส้นโค้งการรักษาลูกค้า อายุขัยเฉลี่ยจะอยู่ที่ประมาณพื้นที่ใต้เส้นโค้งนั้น ซึ่งในสเปรดชีตก็คือผลรวมของอัตราการรักษาลูกค้าในแต่ละเดือน หากลูกค้า 100 รายมีอัตราการรักษาลูกค้าอยู่ที่ 80%, 65% และ 55% ตลอดระยะเวลา 3 เดือน คุณกำลังสะสมอายุขัยของลูกค้าไปทีละเดือน แทนที่จะเดาเป็นตัวเลขกลมๆ คู่มือของเราเกี่ยวกับการสร้างแผนภูมิการรักษาลูกค้าแบบกลุ่ม (cohort retention chart) จะครอบคลุมในส่วนของแผนภูมิ และบทความเกี่ยวกับการวิเคราะห์กลุ่มประชากร (cohort analysis) คืออะไร จะอธิบายถึงแนวคิดนี้

ทางเลือกที่ 3: แปลงเป็น LTV และอัตราส่วน LTV:CAC

นำมูลค่าการสั่งซื้อเฉลี่ยคูณด้วยความถี่ในการซื้อและคูณด้วยอายุขัยของลูกค้า จากนั้นคูณด้วยอัตรากำไรขั้นต้น มูลค่าตลอดช่วงชีวิตของลูกค้าที่คำนวณจากรายได้ (revenue-based) จะทำให้ตัวเลขสูงเกินจริง และบางครั้งอาจสูงเกินไปมาก เนื่องจากวิธีนี้จะปฏิบัติกับสินค้าที่มีอัตรากำไรต่ำเหมือนกับสินค้าที่มีอัตรากำไรสูง

จากนั้นหารด้วยต้นทุนในการได้มาซึ่งลูกค้า (customer acquisition cost) อัตราส่วนนี้คือจุดประสงค์ที่แท้จริงของตัวเลขนี้ มูลค่าตลอดช่วงชีวิตของลูกค้าที่อยู่เดี่ยวๆ นั้นเป็นเพียงข้อมูลทั่วไป แต่มูลค่าตลอดช่วงชีวิตของลูกค้าเมื่อเทียบกับสิ่งที่คุณจ่ายไปเพื่อให้ได้ลูกค้ามานั้นคือข้อมูลเพื่อการตัดสินใจ

จุดที่การคำนวณด้วยตนเองเริ่มช้าลง

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

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

และสมมติฐานต่างๆ ก็จะมองไม่เห็น หกเดือนต่อมาไฟล์จะแสดงตัวเลขหนึ่งขึ้นมา และไม่มีใครจำได้ว่ามันคำนวณจากรายได้หรืออัตรากำไร หรือใช้ช่วงเวลาการเลิกใช้บริการแบบใด นั่นคือสาเหตุที่ตัวเลขที่ล้าสมัยไปลงเอยอยู่ในสไลด์นำเสนอต่อคณะกรรมการ

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

วิธีคำนวณมูลค่าตลอดช่วงชีวิตของลูกค้าด้วย Powerdrill Bloom

ขั้นตอนที่ 1: อัปโหลดข้อมูลของคุณ

อัปโหลด CSV ของรายการธุรกรรมโดยตรง Powerdrill Bloom จะวิเคราะห์โครงสร้างคอลัมน์ เพื่อให้คุณยืนยันได้ว่ามีรหัสระบุตัวตนของลูกค้าที่ใช้งานได้และคอลัมน์วันที่ที่ถูกต้อง ก่อนที่จะเริ่มทำการจัดกลุ่มข้อมูลใดๆ

ขั้นตอนที่ 2: อธิบายการคำนวณด้วยภาษาธรรมชาติ

ระบุกฎเกณฑ์ต่างๆ เหมือนที่คุณพูดคุยกับเพื่อนร่วมงาน เช่น กำหนดให้ลูกค้าที่ไม่มีการซื้อสินค้าใน 12 เดือนเป็นผู้ที่หมดสถานะลูกค้า ใช้อัตราเปอร์เซ็นต์กำไรขั้นต้นนี้ และคำนวณมูลค่าตลอดช่วงชีวิตของลูกค้าตามช่องทางที่ได้ลูกค้ามา จากนั้นถามคำถามติดตามผลที่สำคัญ เช่น ตัวเลขมีความแตกต่างกันอย่างไรระหว่างกลุ่มควอไทล์บนและควอไทล์ล่าง หรือตัวเลขดังกล่าวเปลี่ยนแปลงไปอย่างไรในแต่ละกลุ่มลูกค้าที่ได้มา (acquisition cohorts)

ขั้นตอนที่ 3: ส่งออกแผนภูมิ รายงาน หรือสไลด์นำเสนอ

ดึงข้อมูลตารางแยกตามกลุ่มลูกค้า เส้นโค้งการรักษาลูกค้าที่อยู่เบื้องหลัง หรือสรุปแบบลายลักษณ์อักษรที่มีการบันทึกสมมติฐานควบคู่ไปกับตัวเลขออกมาใช้งาน

ทำไมวิธีนี้ถึงดีกว่าการสร้างโมเดลใหม่ในทุกไตรมาส

	การใช้สเปรดชีต	Powerdrill Bloom
การแบ่งกลุ่มตามช่องทาง	สร้างชุดสูตรใหม่สำหรับแต่ละกลุ่ม	เพิ่มเข้าไปในคำสั่ง
การเปลี่ยนช่วงเวลาการเลิกใช้บริการ	ปรับปรุงตารางการรักษาลูกค้าใหม่	ถามใหม่อีกครั้งด้วยช่วงเวลาใหม่
การบันทึกสมมติฐาน	จดบันทึกด้วยตนเอง ซึ่งมักจะสูญหาย	ระบุไว้ในคำสั่งอย่างชัดเจน
ไฟล์ของไตรมาสถัดไป	ขยายช่วงข้อมูล ตรวจสอบสูตร	อัปโหลดและถามใหม่อีกครั้ง

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

ข้อผิดพลาดที่พบบ่อย

การใช้รายได้แทนอัตรากำไร มูลค่าตลอดช่วงชีวิตของลูกค้าที่คำนวณจากรายได้จะทำให้ตัวเลขของทุกธุรกิจดูดีเกินจริง และทำให้การเปรียบเทียบกับต้นทุนในการได้มาซึ่งลูกค้าบิดเบือนไปอย่างมาก

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

การเดาอายุขัยของลูกค้า "ประมาณสองปี" คือข้อมูลนำเข้าที่พบบ่อยที่สุดและมีหลักฐานอ้างอิงได้น้อยที่สุด ควรคำนวณจากเส้นโค้งการรักษาลูกค้าของคุณเองแทน แม้ว่าเส้นโค้งนั้นจะสั้นก็ตาม

การละเลยผลกระทบจากผู้รอดชีวิต (survivor effect) มูลค่าตลอดช่วงชีวิตของลูกค้าแบบย้อนหลังจะนับเฉพาะลูกค้าที่มีอยู่แล้ว ซึ่งจะเอนเอียงไปทางลูกค้าที่อยู่มานาน การคำนวณแบบอิงกลุ่ม (cohort-based) จะช่วยหลีกเลี่ยงปัญหานี้ได้

การรายงานโดยไม่มี CAC มูลค่าตลอดช่วงชีวิตของลูกค้าที่ $900 จะไม่มีความหมายอะไรเลย จนกว่าคุณจะรู้ว่าต้นทุนในการได้มาซึ่งลูกค้านั้นคือ $90 หรือ $800

การคิดว่าตัวเลขนี้จะคงทนถาวร ควรคำนวณใหม่ทุกครั้งเมื่อราคา ผลิตภัณฑ์ หรือสัดส่วนการได้มาซึ่งลูกค้ามีการเปลี่ยนแปลงอย่างมีนัยสำคัญ ตัวเลขที่ยกยอดมาจากปีที่แล้วก็เป็นเพียงการเดาที่ใส่จุดทศนิยมเข้าไปเท่านั้น

บทสรุป

การคำนวณมูลค่าตลอดช่วงชีวิตของลูกค้าจาก CSV มี 3 ขั้นตอน ได้แก่ มูลค่าการสั่งซื้อเฉลี่ยและความถี่, เส้นโค้งการรักษาลูกค้าจากกลุ่มลูกค้าของคุณเอง, จากนั้นคืออัตรากำไรและอัตราส่วนเมื่อเทียบกับต้นทุนในการได้มาซึ่งลูกค้า การทำในสเปรดชีตนั้นได้ผลดี จนกระทั่งคุณจำเป็นต้องแบ่งกลุ่มลูกค้า หรือต้องการทดสอบช่วงเวลาการเลิกใช้บริการที่แตกต่างออกไป

หากโมเดลนี้จะถูกสร้างใหม่เฉพาะตอนที่มีคนขอในที่ประชุมเท่านั้น ลองใช้ Powerdrill Bloom กับไฟล์ส่งออกรายการธุรกรรมของคุณ หน้า CSV AI assistant ของเราจะครอบคลุมเวิร์กโฟลว์ที่กว้างขึ้น และบทความเกี่ยวกับการแปลงไฟล์ CSV ให้เป็นแผนภูมิ จะครอบคลุมในส่วนของผลลัพธ์ที่เป็นภาพ

คำถามที่พบบ่อย

สูตรสำหรับคำนวณมูลค่าตลอดช่วงชีวิตของลูกค้าคืออะไร?

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

ฉันจะคำนวณมูลค่าตลอดช่วงชีวิตของลูกค้าใน Excel ได้อย่างไร?

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

LTV แบบย้อนหลัง (Historical) และแบบคาดการณ์ (Predictive) แตกต่างกันอย่างไร?

แบบย้อนหลังจะรวมยอดเงินที่ลูกค้าจ่ายไปแล้ว จึงมักจะต่ำกว่าความเป็นจริงเสมอและมีหลักฐานอ้างอิงได้ง่าย ส่วนแบบคาดการณ์จะประมาณการยอดใช้จ่ายในอนาคตจากสมมติฐานการรักษาลูกค้า ซึ่งทำให้มีประโยชน์มากกว่าสำหรับการวางแผน แต่ก็มีโอกาสผิดพลาดได้ง่ายกว่าเช่นกัน

อัตราส่วน LTV ต่อ CAC ที่ดีคือเท่าใด?

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

ฉันควรจัดการกับลูกค้าที่ไม่ได้ซื้อสินค้าเมื่อเร็วๆ นี้อย่างไร?

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