SQL Case Study: Churn Analysis Model [01]

NEW (Khách hàng mới – Tháng đầu): Đây là trạng thái của một khách hàng trong tháng đầu tiên họ thực hiện giao dịch mua hàng.
- NEW SINGLE: Nếu trong tháng đầu tiên, khách hàng chỉ mua hàng một ngày duy nhất.
- NEW MULTI: Nếu trong tháng đầu tiên, khách hàng mua hàng từ hai ngày trở lên.
ACTIVE NEW: Trạng thái này xảy ra khi một khách hàng “NEW” (Single hoặc Multi) tiếp tục mua hàng trong tháng kế tiếp (Tháng M+1). Đây được xem là khởi đầu của một chuỗi “Active” vì tháng trước đó họ chưa từng ở trạng thái này.
ACTIVE EVERGREEN: Nếu một khách hàng “ACTIVE NEW” tiếp tục mua hàng trong tháng tiếp theo nữa (Tháng M+2), họ sẽ được chuyển sang trạng thái này. “ACTIVE EVERGREEN” duy trì khi khách hàng tiếp tục mua sắm liên tục qua các tháng.
EVERGREEN: Nếu khách hàng ở trạng thái “ACTIVE EVERGREEN” và tiếp tục mua sắm liên tục, họ sẽ duy trì ở trạng thái “EVERGREEN”.
NO ORDER: Trạng thái này xảy ra khi một khách hàng ở bất kỳ trạng thái nào (“NEW”, “ACTIVE EVERGREEN”) không phát sinh đơn hàng trong một khoảng thời gian nhất định.
- Nếu một khách hàng “NEW” không mua hàng trong tháng kế tiếp (M+1), họ sẽ tạm nghỉ ngay sau đó.
- Nếu một khách hàng “ACTIVE NEW” hoặc “ACTIVE EVERGREEN” ngừng mua hàng tháng tiếp theo, họ sẽ được coi là tạm nghỉ trong ≥ 1 tháng.
CHURN: Khách hàng được coi là “CHURN“ khi họ đã rời bỏ và không hoạt động trong một thời gian dài.
- Nếu một khách hàng “NO ORDER” (từ trạng thái “NEW”) không quay lại mua sắm trong vòng ≥ 2 tháng.
- Nếu một khách hàng “REACTIVE” không tiếp tục mua hàng trong tháng tiếp theo, họ sẽ bị coi là “CHURN“.
REACTIVE: Đây là trạng thái của khách hàng đã từng ở trạng thái “NO ORDER” (tạm nghỉ ít nhất 1 tháng) và sau đó quay trở lại mua hàng.
Sau khi khách hàng trở thành “REACTIVE”, chu trình sẽ tiếp tục:
- Nếu họ tiếp tục mua hàng trong tháng tiếp theo, họ sẽ chuyển sang trạng thái “ACTIVE NEW”, bắt đầu một chuỗi hoạt động mới sau giai đoạn tạm nghỉ.
- Nếu họ không mua hàng trong tháng tiếp theo, họ sẽ rơi vào trạng thái “CHURN”.

Tư Duy Hai Luồng Dẫn Đến Trạng Thái “CHURN”

Tư duy 01:
- Bắt đầu: Một khách hàng ở trạng thái “NEW” (mua hàng trong tháng đầu tiên).
- Hành vi: Sau tháng đầu tiên đó, họ không tiếp tục mua hàng trong tháng kế tiếp (tháng M+1). Họ sẽ rơi vào trạng thái “NO ORDER”.
- Điều kiện Churn: Nếu tình trạng “NO ORDER” này kéo dài từ 2 tháng trở lên, hệ thống sẽ xác định khách hàng này là “CHURN”.
- Logic Business: Bạn cho khách hàng mới một thời gian ân hạn là 2 tháng. Nếu sau khi mua lần đầu, khách hàng biến mất trong 2 tháng liên tiếp => không thể thuyết phục khách hàng này trở thành người mua sắm thường xuyên và xem như đã mất họ.
Tư duy 02:
- Bắt đầu: Một khách hàng đã từng hoạt động (“ACTIVE EVERGREEN”), sau đó họ ngừng mua hàng trong ít nhất 1 tháng (“NO ORDER”).
- Hành vi: Khách hàng này sau đó quay trở lại mua hàng và được chuyển sang trạng thái “REACTIVE”. Đây là một tín hiệu rất tích cực.
- Điều kiện Churn: Tuy nhiên, nếu ngay trong tháng tiếp theo sau lần mua hàng “REACTIVE” đó, khách hàng lại không phát sinh bất kỳ đơn hàng nào nữa, họ sẽ bị chuyển thẳng vào trạng thái “CHURN”.
- Logic Business: Bạn coi lần quay trở lại của khách hàng là một cơ hội vàng để giữ chân họ. Nếu khách hàng không duy trì được đà mua sắm ngay lập tức => nỗ lực tái kích hoạt đã thất bại và khách hàng này thực sự đã rời bỏ.

Unlocking Insight Qua 07 Tasks SQL

Task 01: Xây Dựng Trạng Thái Khách Hàng Hàng Tháng

Xây dựng một bảng lịch sử thể hiện trạng thái (“NEW”, “ACTIVE NEW”, “CHURN”, v.v.) của từng khách hàng vào từng tháng.

report_month	cust_id	cust_state
2024-01	cust_101	NEW MULTI
2024-01	cust_102	NEW SINGLE
2024-02	cust_101	ACTIVE NEW
2024-02	cust_102	NO ORDER
2024-03	cust_101	ACTIVE EVERGREEN
2024-03	cust_102	CHURN

SQL Coding Task 01 & Tutorial Step by Step: Dành cho các bạn học viên SQLDA và Data Analyst BootCamp.

Task 02: Phân Tích Tỷ Lệ Chuyển Đổi Phễu (Funnel Conversion)

Tìm ra những điểm rơi rụng lớn nhất trong hành trình của khách hàng. Tỷ lệ khách hàng “NEW” trở thành “ACTIVE NEW” là bao nhiêu? Bao nhiêu người từ “ACTIVE EVERGREEN” rơi vào “NO ORDER”?

new_cust_cohort	total_new_custs	active_new_M1	churn_M1	evergreen_M2
2024-01	1000	450	550	300
2024-02	1200	500	700	320

SQL Coding Task 02 & Tutorial Step by Step: Dành cho các bạn học viên SQLDA và Data Analyst BootCamp.

Task 3: So sánh Giá trị Vòng đời (LTV) giữa “NEW SINGLE” và “NEW MULTI”

Khách hàng mua nhiều lần ngay trong tháng đầu tiên có thực sự giá trị hơn về lâu dài không? So sánh tổng chi tiêu trung bình sau 3, 6, 12 tháng của hai nhóm này.

initial_state	avg_revenue_3_months	avg_revenue_6_months
NEW SINGLE	1,200,000	1,800,000
NEW MULTI	3,500,000	6,200,000

SQL Coding Task 03 & Tutorial Step by Step: Dành cho các bạn học viên SQLDA và Data Analyst BootCamp.

Task 4: Tìm Kiếm Tín Hiệu Báo Trước Churn

Phân tích hành vi của khách hàng ngay trước khi họ rơi vào trạng thái “NO ORDER” hoặc “CHURN”. So sánh số lượng đơn hàng, giá trị trung bình đơn (AOV) của tháng cuối cùng hoạt động với 3 tháng trước đó của chính họ.

cust_id	last_active_month	aov_last_month	avg_aov_prev_3_months	signal_aov_decreased
cust_501	2024-05	250,000	700,000	TRUE
cust_502	2024-05	800,000	750,000	FALSE

SQL Coding Task 04 & Tutorial Step by Step: Dành cho các bạn học viên SQLDA và Data Analyst BootCamp.

Task 5: Đo lường Hiệu quả của Nhóm “REACTIVE”

Khách hàng “REACTIVE” có thực sự trung thành? So sánh tỷ lệ nhóm “REACTIVE” trở thành “CHURN” ngay tháng sau với tỷ lệ nhóm “ACTIVE NEW” trở thành “CHURN”.

initial_group	total_custs	churn_next_month	churn_rate
REACTIVE	500	200	40.0%
ACTIVE NEW	4,500	450	10.0%

SQL Coding Task 05 & Tutorial Step by Step: Dành cho các bạn học viên SQLDA và Data Analyst BootCamp.

Task 6: Xác Định Vùng Cảnh Báo

Lập danh sách các khách hàng đang ở trạng thái “NO ORDER”, “CHURN”, họ đã tạm ngưng bao lâu và trạng thái cuối cùng của họ trước khi tạm ngưng là gì. Điều này giúp ưu tiên các chiến dịch giữ chân.

customer_id	last_active_state	months_inactive	total_his_spend
cust_789	ACTIVE EVERGREEN	2	15,000,000
cust_801	ACTIVE NEW	1	2,500,000

SQL Coding Task 06 & Tutorial Step by Step: Dành cho các bạn học viên SQLDA và Data Analyst BootCamp.

Task 7: Xây Dựng Ma Trận Chuyển Đổi Trạng Thái

Phân tích xác suất một khách hàng chuyển từ State A sang State B trong tháng tiếp theo.

from_state	to_state	transition_count	transition_probability
ACTIVE EVERGREEN	ACTIVE EVERGREEN	8,000	0.80
ACTIVE EVERGREEN	NO ORDER	2,000	0.20
NO ORDER	REACTIVE	300	0.30
NO ORDER	CHURN	700	0.70

Task Phát Sinh 07.01: So Sánh Transition Behavior Giữa Các COHORTS Khác Nhau

cohort	cohort_year	from_state	to_state	prob	notes
H1	2024	NEW SINGLE	ACTIVE NEW	0.67	H1: 67% retention
H2	2024	NEW SINGLE	ACTIVE NEW	0.40	H2: 40% retention (worse!)
H1	2024	NO ORDER	CHURN	0.70	H1: 70% churn
H2	2024	NO ORDER	CHURN	0.85	H2: 85% churn (worse!)