เตรียมไฟล์ส่งออกคำสั่งซื้อก่อนวิเคราะห์ลูกค้า: เช็กลิสต์สำหรับ CSV และ Excel

สถานการณ์ตัวอย่างเพื่อให้เห็นความสำคัญ

ลองนึกภาพร้านขายเครื่องประดับออนไลน์สมมติในไทยแห่งหนึ่ง — ขอเรียกว่า Saifah Studio — เปิดร้านมาแล้วสามปี และมีข้อมูลคำสั่งซื้อสะสมราว 18,000 รายการจากสองช่องทางการขาย หัวหน้าฝ่ายปฏิบัติการต้องการทำการแบ่งกลุ่มลูกค้าแบบ RFM (Recency, Frequency, Monetary) เพื่อให้ทีมรู้ว่าควรกลับไปติดต่อลูกค้ากลุ่มไหนก่อน เธอส่งออกไฟล์ CSV จากแพลตฟอร์มร้านค้า ดับเบิลคลิกเปิดใน Excel แล้วสังเกตทันทีว่าคอลัมน์วันที่ดูผิดปกติ: บางรายการชิดซ้ายเหมือนข้อความ บางรายการชิดขวาเหมือนตัวเลข และเมื่อลองสุ่มตรวจ ก็พบว่าคำสั่งซื้อที่เกิดขึ้นวันที่ 5 เมษายนกลับแสดงเป็น 4 พฤษภาคม หากไม่มีเช็กลิสต์ตรวจสอบก่อนนำเข้าอย่างเป็นระบบ ข้อมูลวันที่ที่ผิดเพี้ยนนี้ก็จะไหลต่อไปทำให้การคำนวณ Recency ทั้งหมดด้านล่างผิดตามไปด้วย

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

ฟิลด์ในไฟล์ตัวอย่าง: ฟิลด์หลัก 4 ฟิลด์ และฟิลด์แนะนำ 2 ฟิลด์

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

ฟิลด์หลัก 4 ฟิลด์คือ order_id, customer_id, order_date และ order_value ส่วนฟิลด์แนะนำที่ไม่บังคับ 2 ฟิลด์คือ order_status และ product_category เก็บไว้เมื่อจำเป็นต้องแยกออเดอร์ที่ยกเลิกหรือใช้ทดสอบ หรือวิเคราะห์ตามหมวดสินค้า หากไฟล์รวมข้อมูลจากหลายแพลตฟอร์ม channel เป็นอีกฟิลด์เสริมที่ช่วยเก็บแหล่งที่มาและให้ทีมตรวจรหัสที่อาจซ้ำกันข้ามแหล่งข้อมูล

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

แถวที่ใช้ได้ vs. แถวที่ใช้ไม่ได้ — ตัวอย่างสอนแบบลงรายละเอียด

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

แถว 10001 ใช้ได้สมบูรณ์: มีทุกฟิลด์ที่จำเป็นและชนิดข้อมูลถูกต้อง วันที่อยู่ในรูปแบบ ISO 8601 ที่ไม่กำกวม แถวนี้จึงนำไปใช้คำนวณ Recency, Frequency และ Monetary ได้ครบถ้วนโดยไม่มีข้อจำกัด

แถว 10002 มีค่า order_value ว่าง นี่ไม่ใช่ข้อผิดพลาดร้ายแรง เพราะแถวยังมี customer_id และ order_date ที่ถูกต้องอยู่ จึงยังใช้กับ Recency (วันที่ซื้อล่าสุด) และ Frequency (จำนวนคำสั่งซื้อที่ไม่ซ้ำกัน) ได้ตามปกติ สิ่งที่แถวนี้รองรับไม่ได้คือการคำนวณ Monetary ที่อิงรายได้ ป้ายกำกับที่ถูกต้องของแถวนี้คือ 'ใช้ได้จำกัด — ไม่เหมาะสำหรับการวิเคราะห์ Monetary ที่อิงรายได้' หากคุณลบทั้งแถวทิ้งเพียงเพื่อหลีกเลี่ยงค่าว่าง จะกลายเป็นการลดจำนวน Frequency ของลูกค้าคนนั้นแบบเงียบ ๆ ซึ่งเป็นความผิดพลาดที่แย่กว่า

แถว 10003 ไม่มี customer_id เมื่อไม่มีตัวระบุนั้น แถวนี้ก็ไม่สามารถเชื่อมเข้ากับผู้ซื้อคนใดได้ จึงไม่สามารถนำไปใช้สร้างกลุ่มลูกค้า RFM ระดับลูกค้า หรือใช้กำหนดการอยู่ใน cohort ได้ อย่างไรก็ตาม แถวยังสามารถนำไปใช้กับยอดรายได้รวมระดับคำสั่งซื้อได้ หากคุณต้องการตัวเลขรายได้รวมของร้าน ป้ายกำกับที่ถูกต้องของแถวนี้คือ 'ใช้ได้จำกัด — ใช้ไม่ได้สำหรับ RFM ระดับลูกค้าหรือการเชื่อม cohort; ใช้ได้เฉพาะยอดรวมรายได้ระดับคำสั่งซื้อ'

แถว 10004 มีวันที่ผิดจริงคือ 2024-02-30 เดือนกุมภาพันธ์ไม่มีวันที่ 30 แม้เป็นปีอธิกสุรทิน ค่านี้จึงไม่ใช่วันที่ซื้อที่มีอยู่จริง ต้องตรวจออเดอร์ในแพลตฟอร์มต้นทางแล้วแก้ไฟล์ก่อนคำนวณโดยอาศัยวันที่ ส่วนค่าอย่าง April 5 2024 ซึ่งใช้ชื่อเดือนภาษาอังกฤษเป็นปัญหาอีกแบบหนึ่ง ตัวแปลงบางตัวอ่านได้ แต่ผลขึ้นกับภาษาและการตั้งค่า จึงไม่ควรเรียกวันที่ภาษาอังกฤษที่อ่านได้ว่าเป็นวันที่ผิดโดยตัวมันเอง

วันที่ที่สลับวันและเดือนได้

ข้อความ 04/05/2024 อาจหมายถึง 4 พฤษภาคม หรือ 5 เมษายน ในร้านสมมติของเรา ไฟล์ใช้วัน/เดือน/ปี ดังนั้นวันที่ที่ต้องการคือ 4 พฤษภาคม ถ้าอ่านแบบเดือน/วัน/ปีจะได้อีกวันที่หนึ่งซึ่งมีอยู่จริงเหมือนกัน จึงดูสมเหตุสมผลได้แม้ความหมายทางธุรกิจเปลี่ยนไป ควรตรวจตัวอย่างที่ตัวเลขวันและเดือนต่างไม่เกิน 12 แล้วเทียบกับออเดอร์ต้นทาง

Power Query ใช้ข้อมูล locale เมื่อแปลงข้อความเป็นวันที่ ในตัวแก้ไขคำสั่ง Change Type → Using Locale ให้เลือกชนิดข้อมูลปลายทางและ locale ที่เหมาะสม ตัวอย่างเช่น ไฟล์วัน/เดือน/ปีที่ระบุชัดว่าใช้ปีคริสต์ศักราชสามารถทดลองด้วย locale ที่ตรงกัน เช่น English (United Kingdom) อย่าสรุปว่าต้องเลือกภาษาไทยสำหรับทุกไฟล์จากร้านไทย ให้ตรวจรูปแบบปี รูปแบบต้นทาง และวันที่ที่แปลงได้จริงด้วย

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

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

อ้างอิง: Microsoft: Set a locale or region for data (Power Query)

เลือกรูปแบบวันที่ที่ปลอดภัยสำหรับไฟล์ส่งออก

ใช้ปีสี่หลักและระบุรูปแบบวันที่ให้ชัด ค่า YYYY-MM-DD ที่มีข้อตกลงกำกับ เช่น 2024-05-04 ตรวจสอบง่ายกว่าวันที่คั่นด้วยเครื่องหมายทับโดยไม่อธิบาย แต่ยังต้องทดสอบว่าเครื่องมือปลายทางอ่านอย่างไร ระบุด้วยว่าเป็นปีคริสต์ศักราชหรือพุทธศักราช การเปลี่ยนรูปแบบแสดงผลของเซลล์ไม่ได้แปลงระบบปีโดยตัวมันเอง

ถ้าแพลตฟอร์มส่งออกแบบวัน/เดือน/ปีและเปลี่ยนไม่ได้ ให้บันทึกรูปแบบนั้นในเอกสารส่งต่อ เลือก locale ที่ตรงกันเมื่อแปลงใน Power Query หรือเลือกการเรียงวันเดือนปีที่เหมาะสมหาก Text Import Wizard ในรุ่นที่ใช้มีตัวเลือกนี้ เก็บตัวอย่างทั้งวันที่ที่สลับวันเดือนได้และวันที่ที่เลขวันเกิน 12 แล้วเทียบค่าหลังแปลงกับออเดอร์เดิม

ถ้าไฟล์มีเวลาและเขตเวลา ให้ตกลงก่อนว่าการวิเคราะห์จะใช้วันตามเขตเวลาใด แล้วจึงตัดส่วนเวลาออก ตัวอย่างที่สร้างขึ้นเพื่อสอนคือ 2024-05-05 00:30 ที่ UTC+7 ตรงกับ 2024-05-04 17:30 UTC ส่วนเวลาช่วงดึกของ UTC+7 ยังเป็นวันเดียวกันใน UTC ไม่ได้ข้ามไปเป็นวันพรุ่งนี้ เก็บเวลาต้นฉบับและค่า offset จนกว่าทีมจะเลือกข้อตกลงเดียวกัน

วิธีนำเข้าที่ควบคุมได้ — อย่าดับเบิลคลิกเปิด CSV

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

ในรุ่นเดสก์ท็อปที่รองรับ เริ่มจาก Data → From Text/CSV ตรวจตัวอย่างแล้วเปิดตัวแก้ไขการแปลงก่อนโหลด ตรวจขั้นตอนเปลี่ยนชนิดข้อมูลที่ระบบอาจเพิ่มให้เอง ตั้งคอลัมน์รหัสเป็น Text ตั้งแต่ขั้นตอนแปลงแรก จากนั้นแปลงคอลัมน์วันที่ดิบด้วย locale ที่ตรงกับข้อตกลงของไฟล์ ตรวจออเดอร์ที่รู้วันที่แน่นอนสองสามรายการก่อนยอมรับผล

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

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

อ้างอิง: Microsoft: Set a locale or region for data (Power Query) · Microsoft: Import or export text (.txt or .csv) files

กับดักของระบบวันที่ 1904

ถ้าวันที่ที่คัดลอกข้ามสมุดงานคลาดกันประมาณสี่ปีอย่างสม่ำเสมอ ให้ตรวจระบบวันที่ของสมุดงานควบคู่กับการตั้งค่านำเข้า Excel รองรับระบบ 1900 และ 1904 Microsoft ระบุว่าเลขลำดับของวันที่เดียวกันต่างกัน 1,462 วัน นี่เป็นสาเหตุที่ควรตรวจ ไม่ใช่หลักฐานว่าปัญหาวันที่ CSV ทุกกรณีเกิดจากตัวเลือกนี้

ตัวอย่างของ Microsoft คือ 5 กรกฎาคม 2011 มีเลขลำดับ 40729 ในระบบ 1900 และ 39267 ในระบบ 1904 ปัญหานี้เกี่ยวกับเลขลำดับวันที่และการตีความของสมุดงาน อย่าสรุปว่าข้อความธรรมดาอย่าง 2024-05-04 จะถูกเลื่อนวันโดยอัตโนมัติเพียงเพราะมาจาก CSV

ใน Excel บน Windows ตรวจ File → Options → Advanced → When calculating this workbook → Use 1904 date system ส่วน Mac มีตำแหน่งตัวควบคุมต่างกัน ก่อนเปลี่ยนตัวเลือก ให้บันทึกสำเนาแล้วเทียบวันที่ที่รู้จากต้นทางกับสมุดงานปัจจุบัน ทดลองเปลี่ยนบนสำเนาและตรวจทุกคอลัมน์วันที่ โดยเฉพาะเมื่อสมุดงานรวมข้อมูลจากหลายแหล่งอยู่แล้ว

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

อ้างอิง: Microsoft: Date systems in Excel

การจัดการตัวระบุตัวเลขที่มีความยาวมาก

ชนิดข้อมูลตัวเลขแบบ floating-point ของ Excel มีความแม่นยำสำหรับเลขฐานสิบอย่างมีนัยสำคัญประมาณ 15 หลัก จำนวนเต็มที่ยาวเกิน 15 หลัก — ซึ่งพบได้บ่อยใน order ID ที่แพลตฟอร์มสร้างขึ้น และ customer ID บางแบบ — จะทำให้หลักท้าย ๆ ถูกแทนด้วยศูนย์เมื่อถูกเก็บเป็นตัวเลข เช่น order ID 1234567890123456 จะเข้ามาในเซลล์เป็น 1234567890123450 และความเปลี่ยนแปลงนี้มองไม่เห็นเลยหากไม่เทียบกับต้นฉบับ ออเดอร์คนละรายการ 2 รายการอาจถูกเก็บด้วย ID เดียวกัน ทำให้การนับจำนวนออเดอร์ที่ไม่ซ้ำสำหรับ Frequency ไม่น่าเชื่อถืออย่างมาก

ตั้งคอลัมน์รหัสเป็น Text ก่อนขั้นตอนแปลงเป็นตัวเลขที่จะทำให้หลักหาย ใน Power Query ให้ตรวจขั้นตอน Changed Type ที่เพิ่มอัตโนมัติ การเปลี่ยนกลับเป็น Text หลังแปลงตัวเลขจนข้อมูลเสียแล้วไม่ได้คืนรหัสเดิม ต้องแทนที่หรือลบขั้นตอนแปลงตัวเลขของคอลัมน์รหัสก่อนหน้านั้น แล้วตรวจค่าดิบ ส่วน Text Import Wizard สามารถกำหนดคอลัมน์เป็น Text ก่อนนำเข้าได้เช่นกัน

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

อ้างอิง: Microsoft: Keeping leading zeros and large numbers

การกระทบยอดก่อนนำเข้า: นับจำนวนเรคอร์ด ไม่ใช่จำนวนบรรทัด

ก่อนนำเข้าไฟล์ คุณต้องมีจำนวนเรคอร์ดจากต้นทางที่เชื่อถือได้ไว้ใช้เทียบกับสิ่งที่ Excel รับเข้าไป สัญชาตญาณแรกของหลายคนคือการนับจำนวนบรรทัด เช่น ใช้ wc -l บนระบบตระกูล Unix หรือใช้ Get-Content ใน PowerShell บน Windows แล้วลบ 1 เพื่อหักแถวหัวตารางออก แต่วิธีนี้ไม่ใช่การนับจำนวนเรคอร์ด

ฟิลด์ใน CSV อาจมีการขึ้นบรรทัดใหม่อยู่ภายในเครื่องหมายคำพูด ทำให้หนึ่งเรคอร์ดกินหลายบรรทัดจริง Microsoft ระบุว่า Power Query Csv.Document ที่ใช้ QuoteStyle.Csv จะถือการขึ้นบรรทัดใหม่ในเครื่องหมายคำพูดเป็นส่วนหนึ่งของฟิลด์ ตัวอย่างที่สร้างขึ้นคือไฟล์สามเรคอร์ดออเดอร์ซึ่งมีหมายเหตุสองบรรทัดหนึ่งรายการ จะมีบรรทัดข้อมูลเกินสามบรรทัดแต่ยังมีเพียงสามเรคอร์ด การนับบรรทัดดิบจึงไม่ใช่ฐานกระทบยอดที่ถูกต้อง

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

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

อ้างอิง: Microsoft: Csv.Document — quoted line breaks

การตรวจจำนวนแถวและความครบถ้วนหลังนำเข้า

เมื่อข้อมูลอยู่ใน Excel แล้ว ให้ยืนยันจำนวนแถวทั้งหมดด้วยวิธีที่ไม่ขึ้นกับว่าคอลัมน์ใดคอลัมน์หนึ่งมีข้อมูลครบหรือไม่ หากคุณแปลงช่วงข้อมูลที่นำเข้าเป็น Excel Table แล้ว (Insert → Table) ฟังก์ชัน =ROWS(Table1) จะคืนค่าจำนวนแถวข้อมูลที่แน่นอน โดยไม่สนว่าช่องใดจะว่างหรือไม่ วิธีนี้สะอาดที่สุดและไม่ถูกรบกวนจากค่าว่าง

ถ้าคุณไม่ได้สร้าง Table ให้ใช้ COUNTA กับคอลัมน์ที่สคีมาของคุณกำหนดว่าไม่มีวันว่าง เช่น order_id ซึ่งเป็นตัวเลือกที่แข็งแรงที่สุด เพราะทุกแถวต้องมีตัวระบุคำสั่งซื้อ จึงจะนำไปวิเคราะห์ได้อย่างมีความหมาย หลีกเลี่ยงการใช้ COUNTA หรือ ROWS ลบ COUNTBLANK กับคอลัมน์ที่อาจว่างได้ตามปกติ (เช่น order_value, product_category) เพราะสูตรเหล่านี้นับจำนวนเซลล์ที่ไม่ว่างในคอลัมน์ที่เลือก ไม่ได้นับจำนวนแถวทั้งหมด และจะนับต่ำกว่าจริงแบบเงียบ ๆ ทันทีที่มีค่าว่าง

สำหรับคอลัมน์วันที่ สูตรช่วยอย่าง =ISTEXT(C2) บอกว่าเซลล์ที่ตรวจเป็นข้อความหรือไม่ เปลี่ยน C2 ให้ตรงกับคอลัมน์วันที่จริง ผล TRUE ต้องตีความต่อ อาจเป็นข้อความดิบที่ตั้งใจรักษาไว้ หรือยังไม่ได้แปลงข้อมูล ส่วน FALSE เพียงอย่างเดียวก็ไม่ยืนยันว่าวันที่ถูกต้อง ให้เทียบกับออเดอร์ที่รู้วันที่และตรวจข้อผิดพลาดการแปลง อย่าใช้สูตรนี้แทนการตรวจวันที่ทั้งหมด

สำหรับ order_value ให้นับช่องว่างด้วย =COUNTBLANK(D2:D18001) แล้วบันทึกผลลัพธ์ไว้ ค่าว่างในจุดนี้ไม่ใช่ความผิดพลาดเสมอไป แต่ต้องถูกกันออกจากการคำนวณ Monetary อย่างตั้งใจ ไม่ใช่ตีความเป็นศูนย์ เพราะ order_value ที่ว่างมีความหมายต่างจากคำสั่งซื้อที่มูลค่าเป็นศูนย์ จดจำนวนช่องว่างนี้ไว้ในบันทึกการทำงาน เพื่อให้เพื่อนร่วมงานที่มาตรวจผลภายหลังเข้าใจว่าทำไมจำนวนแถวที่ใช้คำนวณ Monetary อาจน้อยกว่าจำนวนแถวที่ใช้คำนวณ Frequency

การเข้ารหัส ตัวตั้งค่าภูมิภาค และสิ่งที่ไม่ควรเปลี่ยน

Microsoft ระบุว่า CSV แบบ UTF-8 ที่บันทึกพร้อม byte order mark สามารถเปิดตามปกติใน Excel ได้ และแนะนำ Power Query หรือ Get Data From Text เป็นทางเลือกสำหรับกรณีอื่น สำหรับร้านไทย ให้ตรวจชื่อสินค้าหรือข้อความไทยสองสามค่าหลังโหลดโดยเทียบกับต้นฉบับ ถ้าตัวอักษรเพี้ยน ให้ตรวจการเข้ารหัสในขั้นตอนนำเข้าแล้วเริ่มใหม่จากไฟล์ที่ไม่แก้ไข แทนการพิมพ์ชื่อที่เสียใหม่ด้วยมือ

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

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

อ้างอิง: Microsoft: Opening CSV UTF-8 files correctly in Excel · Microsoft: Set a locale or region for data (Power Query)

สรุปเช็กลิสต์ก่อนนำเข้า และการส่งต่อข้อมูล

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

ข้อ (a): ตรวจฟิลด์และโครงสร้างแถว ในไฟล์ตัวอย่าง RFM ที่ครบทั้งสามมิติ ให้ตรวจ order_id, customer_id, order_date และ order_value พร้อมตกลงว่าแต่ละแถวแทนอะไร เครื่องมือปลายทางอาจต้องการรายการฟิลด์ ชื่อฟิลด์ หรือรูปแบบต่างออกไป อ่านข้อกำหนดที่มีเอกสารรองรับและทดสอบไฟล์ตัวอย่างขนาดเล็กก่อนโหลดไฟล์เต็ม เช็กลิสต์นี้ไม่ได้รับประกันว่าไฟล์จากทุกแพลตฟอร์มนำเข้าได้ทันที

ข้อ (b): บันทึกรูปแบบวันที่และระบบปี ทดสอบตัวอย่างค่าที่ตีความได้หลายแบบ ใน Power Query ให้เลือก locale ที่ตรงกับไฟล์เมื่อแปลงเป็น Date ใช้คำว่า DMY, MDY หรือ YMD เฉพาะเมื่อ Text Import Wizard ที่ติดตั้งมีตัวเลือกเรียงวันเดือนปี บันทึกทั้งการตั้งค่าและผลเทียบต้นทางกับค่าที่แปลง

ข้อ (c): นำเข้าคอลัมน์ ID เป็นข้อความ ก่อนคลิก Load หรือ Finish ในตัวช่วยนำเข้าใด ๆ ให้ตั้ง order_id และ customer_id เป็นชนิด Text ตรวจตัวอย่าง ID ที่มีความยาวมากหลังนำเข้า โดยเทียบกับ CSV ต้นฉบับในโปรแกรมแก้ไขข้อความล้วน

ข้อ (d): กระทบยอดจำนวนเรคอร์ด หาจำนวนเรคอร์ดแบบรู้โครงสร้าง CSV ก่อนนำเข้า หลังนำเข้าให้ใช้ =ROWS(Table1) หรือใช้ COUNTA กับ order_id เพื่อยืนยันจำนวนแถวข้อมูลหลังนำเข้า ตัวเลขสองชุดนี้ต้องตรงกัน

ข้อ (e): เทียบวันที่ดิบและวันที่ที่แปลงกับออเดอร์ที่รู้แน่นอน สูตรตรวจชนิดข้อความช่วยตรวจได้แต่รับรองการอ่านวันที่ไม่ได้ แก้วันที่ไม่มีจริงอย่าง 2024-02-30 จากแพลตฟอร์มต้นทาง เก็บแถวที่ยังแก้ไม่ได้แยกไว้ และบันทึกว่าเรคอร์ดใดไม่ถูกใช้วิเคราะห์โดยอาศัยวันที่

ข้อ (f): เมื่อคัดลอกเลขลำดับวันที่ข้ามสมุดงาน ให้ตรวจว่าระบบวันที่ตรงกันหรือไม่ หากคลาดเคลื่อน 1,462 วันอย่างสม่ำเสมอ ให้ตรวจบนสำเนาก่อนเปลี่ยนค่า ขั้นตอนนี้ไม่ได้ทดแทนการตรวจวัน/เดือนหรือเขตเวลา

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

หลังตรวจครบ ไฟล์จะพร้อมมากขึ้นสำหรับการตรวจความเหมาะสมก่อนนำเข้า ไม่ใช่รับประกันว่าใช้ได้กับทุกเครื่องมือ ต้องตรวจช่องที่จำเป็น รูปแบบที่รองรับ และวิธีจัดการแถวที่ไม่ครบของระบบปลายทางก่อนโหลด CasperBrain ใช้ RFM แบบ 4 quartiles โดย 1 คือดีที่สุด และ 4 คืออ่อนที่สุด คะแนนรวมเป็นข้อความ เช่น 114 ข้อตกลงนี้เฉพาะกับผลิตภัณฑ์ คะแนนจากระบบอื่นอาจใช้สเกลหรือทิศทางต่างกัน จึงไม่ควรคัดลอกมาใช้โดยไม่ปรับ เก็บข้อตกลงการให้คะแนนไว้กับรายงานเพื่อให้ทีมตีความข้อมูลเดียวกันอย่างสม่ำเสมอ

แหล่งข้อมูลและวิธีจัดทำ

ตัวอย่างในบทความเป็นข้อมูลสมมติ ไม่มีข้อมูลลูกค้าจริง บทความจัดทำโดยทีม CasperBrain โดยใช้ AI ช่วยร่าง ตรวจข้อเท็จจริงและภาษาก่อนเผยแพร่

• Microsoft: Set a locale or region for data (Power Query)
• Microsoft: Import or export text (.txt or .csv) files
• Microsoft: Date systems in Excel
• Microsoft: Opening CSV UTF-8 files correctly in Excel
• Microsoft: Keeping leading zeros and large numbers
• Microsoft: Csv.Document — quoted line breaks