Data Challenge #161125: Phương Trình Retention

Hãy xem Retention (Tỷ lệ giữ chân) là một kết quả (output) mà bạn hướng tới. Để đạt được kết quả này, sản phẩm phải tập trung cải thiện 3 yếu tố đầu vào (inputs) then chốt:

• Activation (Kích hoạt): Đây không chỉ là việc đăng ký tài khoản. Activation là hành trình ngắn đưa user từ “tò mò” đến khoảnh khắc “Aha!” – lần đầu tiên họ cảm nhận được giá trị thực sự của sản phẩm.
  • Ví dụ: User tải App (tò mò), liên kết thành công ngân hàng (vượt qua rào cản ban đầu), và dùng voucher để thanh toán một ly cà phê miễn phí (khoảnh khắc “Aha!”). Họ đã được kích hoạt vì đã thấy App tiện lợi và có lợi.
• Engagement (Tương tác): Sau khoảnh khắc “Aha!”, user có quay lại không? Engagement đo lường mức độ và tần suất họ chủ động sử dụng các tính năng của App. Đây là thước đo cho thấy sản phẩm đang dần trở thành một thói quen.
  • Ví dụ: Một user được coi là có tương tác tốt khi họ không chỉ dùng App để săn voucher, mà còn chủ động dùng để trả tiền điện, chuyển tiền cho bạn bè, mua vé xem phim.
• Resurrection (Hồi sinh): Không phải user nào cũng ở lại. Resurrection là nỗ lực để kéo những user đã “ngủ đông” (dormant) quay trở lại. Đây không phải là spam, mà là những lời mời gọi có giá trị, đúng thời điểm.
  • Ví dụ: Gửi một voucher giảm giá tiền điện 50% cho một user đã không hoạt động 6 tháng, ngay trước kỳ đóng tiền điện của họ.

• Retention (Bề rộng – Breadth): 
  • Nó trả lời câu hỏi đơn giản: “Trong tháng qua, user có hoạt động hay không?”.
  • Đây là một chỉ số nhị phân (Có/Không).
• Engagement (Chiều sâu – Depth): 
  • Nó trả lời câu hỏi sâu hơn: “Nếu họ được giữ chân, họ đã tích cực tương tác với sản phẩm như thế nào?”.
  • Chỉ số này đo lường cường độ tương tác (số lần giao dịch, số tính năng sử dụng, giá trị giao dịch).
• Mối quan hệ cốt lõi: 
  • Một người dùng được giữ chân (retained) chưa chắc đã tương tác sâu (highly engaged) (ví dụ: 1 tháng chỉ mở app 1 lần).
  • Nhưng một người dùng tương tác sâu gần như chắc chắn sẽ được giữ chân.
  • Do đó, cải thiện Engagement là đòn bẩy mạnh mẽ nhất để tăng trưởng Retention bền vững.

Tèo Đầu To:

• Hành trình: 
  • Tèo, một người yêu công nghệ, nghe bạn bè giới thiệu và quyết định thử App.
  • Tèo không chỉ tải app mà còn hoàn thành ngay bước liên kết ngân hàng (một rào cản lớn) vì tò mò về gói quà.
  • Ngay chiều hôm đó, Tèo dùng App quét mã QR trả tiền cà phê, và voucher 50.000đ được tự động áp dụng. Giao dịch hoàn tất trong 3 giây.
• Phân tích dưới lăng kính Growth:
  • Activation: Khoảnh khắc “Aha!” của Tèo rất rõ ràng: thanh toán không tiền mặt vừa nhanh, vừa có lợi. App đã thành công đưa Tèo qua luồng kích hoạt một cách trơn tru, biến sự tò mò thành trải nghiệm giá trị ngay lập tức.
  • Engagement: App đã xuất sắc trong việc bủa vây Tèo bằng cả 3 vòng lặp thói quen:
    • Vòng lặp tự nhiên (Organic): Nhu cầu trả tiền điện, nước, mua vé xem phim (triggers) đều được Tèo giải quyết bằng App (action) vì nó nhanh và tiện (reward).
    • Vòng lặp được tạo ra (Manufactured): Thông báo đẩy “Hoàn tiền 50% vé xem phim Lật Mặt 7” (trigger) đã thúc đẩy Tèo thực hiện một hành động mà có thể anh không định làm, củng cố thêm sự gắn bó.
    • Vòng lặp môi trường (Environment): Khi mua hàng trên Tiki, nút “Thanh toán bằng App” xuất hiện ngay đúng lúc Tèo cần (trigger), khiến việc chọn App để thanh toán trở thành một lựa chọn không cần suy nghĩ.
  • Kết quả (Retention & Engagement): Tèo không chỉ đạt Bề rộng (Retention) vì anh có hoạt động, mà còn đạt Chiều sâu (Engagement) vượt trội với 25 giao dịch đa dạng.
• Bài học từ Tèo: 
  • Hành trình của Tèo cho thấy một kịch bản lý tưởng: luồng kích hoạt mượt mà, theo sau là sự kết hợp thông minh của các vòng lặp thói quen để biến user mới thành một user trung thành, có giá trị cao.

Tý Đít Lép:

• Hành trình: 
  • Thấy Tèo được lợi, Tý cũng tải App.
  • Tý hơi ngần ngại ở bước liên kết ngân hàng vì lo ngại bảo mật, nhưng cuối cùng cũng hoàn thành để lấy voucher.
  • Tý dùng voucher mua thẻ cào điện thoại thành công. Tuy nhiên, sau đó, anh gần như không mở lại app. Các thông báo khuyến mãi anh nhận được không liên quan đến nhu cầu của anh.
• Phân tích dưới lăng kính Growth:
  • Activation: Tý đã được kích hoạt vì anh đã trải qua khoảnh khắc “Aha!” (mua thẻ cào được giảm giá). App đã thành công ở bước này.
  • Engagement: 
    • App đã không thể xây dựng được bất kỳ vòng lặp thói quen nào với Tý.
    • Thói quen dùng tiền mặt của Tý quá lớn, và các thông báo đẩy không được cá nhân hóa đã khiến Tý cảm thấy bị làm phiền thay vì được giúp đỡ. Chúng không trở thành một “trigger” hiệu quả.
  • Kết quả (Retention & Engagement): Tý trở thành một người dùng rời bỏ (churned user). Chiều sâu tương tác của anh bằng không.
• Bài học từ Tý: 
  • App đã tốn chi phí để thu hút Tý, kích hoạt anh thành công, nhưng lại thất bại trong việc giữ chân vì không nuôi dưỡng được sự tương tác sau khoảnh khắc “Aha!”.
  • Đây là một sự lãng phí nguồn lực, cho thấy tầm quan trọng của việc xây dựng Engagement ngay sau Activation.

Tủn Bụng Bự:

• Hành trình: 
  • Tủn đã cài App từ rất lâu, chỉ để nhận tiền Tèo chuyển một lần duy nhất rồi quên bẵng đi.
  • Tủn là một người dùng ngủ đông (dormant user).
  • Bỗng một ngày, gần đến kỳ đóng tiền điện, anh nhận được email từ App: “Tủn ơi, App nhớ bạn! Tặng riêng bạn voucher 100.000đ thanh toán hóa đơn điện.”
• Phân tích dưới lăng kính Growth:
  • Resurrection: Đây là một chiến dịch hồi sinh hoàn hảo. Sự thành công đến từ 3 yếu tố: 
    • Đúng thời điểm (ngay khi nhu cầu phát sinh) 
    • Giá trị cao (voucher đủ lớn để phá vỡ sự ì)
    • Cá nhân hóa (nhắm đúng nhu cầu thiết thực).
  • Tái kích hoạt (Re-activation): Khi Tủn dùng voucher để trả tiền điện, anh đã trải qua một khoảnh khắc “Aha!” lần thứ hai. Anh được tái kích hoạt.
  • Kết quả (Retention & Engagement): Tủn đã được hồi sinh (resurrected) thành công, chuyển từ trạng thái “dormant” sang “active“.
• Bài học từ Tủn: 
  • Đừng bao giờ từ bỏ người dùng cũ.
  • Một chiến dịch Resurrection thông minh, dựa trên dữ liệu có thể mang họ trở lại.
  • Thử thách tiếp theo là làm sao để giữ chân Tủn, áp dụng các bài học từ Tèo để xây dựng các vòng lặp thói quen cho anh, tránh để anh “ngủ đông” trở lại.

Tóm lại, để tăng trưởng bền vững, bạn không thể chỉ nhìn vào một con số Retention chung chung. Bạn phải liên tục trả lời các câu hỏi: Làm sao để có nhiều Tèo hơn? Làm sao để có ít Tý hơn? Và làm sao để tìm lại và giữ chân những người như Tủn?

Chỉ số MAU (User hoạt động hàng tháng) vẫn đang ổn, nhưng nó đang che giấu một sự thật:

• Bạn đang đốt tiền để thu hút nhiều user như ‘Tý Đít Lép’ – những user được kích hoạt thành công rồi rời bỏ ngay sau đó.
• Trong khi đó, bạn không biết mình có bao nhiêu ‘Tèo Đầu To’ và làm thế nào để tạo ra thêm những người như họ.
• Tệ hơn, bạn đang bỏ quên cả một mỏ vàng là những ‘Tủn Bụng Bự’ có thể được hồi sinh với chi phí thấp.

Ban lãnh đạo không cần những con số chung chung, cái họ cần là lượng hóa được những nhóm user này.

Viết truy vấn SQL Server để phân loại mỗi user (user_id) vào một trong các nhóm sau, dựa trên các chỉ số hành vi:

Các chỉ số hành vi cần tính cho mỗi người dùng:

• recency_days: Số ngày kể từ lần giao dịch cuối cùng.
• frequency: Tổng số lượng giao dịch đã thực hiện.
• service_variety: Số lượng dịch vụ khác nhau đã sử dụng.
• user_age_days: Số ngày kể từ ngày đăng ký.
• max_inactivity_period: Khoảng thời gian không hoạt động (tính bằng ngày) dài nhất giữa hai lần giao dịch.

Định nghĩa các phân khúc user:

• Power Users (Giống Tèo):
  • Giao dịch lần cuối trong vòng 30 ngày (recency_days <= 30).
  • Có ít nhất 20 giao dịch (frequency >= 20).
  • Đã sử dụng ít nhất 4 dịch vụ khác nhau (service_variety >= 4).
• Resurrected Users (Giống Tủn):
  • Đã từng có một khoảng “ngủ đông” ít nhất 180 ngày (max_inactivity_period >= 180).
  • Đã giao dịch trở lại trong vòng 30 ngày gần đây (recency_days <= 30).
• Churned Activated Users (Giống Tý):
  • Đăng ký tài khoản chưa đầy 1 năm (user_age_days < 365).
  • Chỉ có từ 1 đến 3 giao dịch (frequency BETWEEN 1 AND 3).
  • Không có giao dịch nào trong hơn 90 ngày (recency_days > 90).
• New Active Users:
  • Đăng ký tài khoản trong vòng 30 ngày (user_age_days <= 30).
  • Đã có ít nhất 1 giao dịch (frequency > 0).
• At-Risk Users:
  • Giao dịch lần cuối cách đây từ 91 đến 180 ngày (recency_days BETWEEN 91 AND 180).
• Casual Users:
  • Không thuộc các nhóm trên nhưng có giao dịch trong vòng 90 ngày (recency_days <= 90) và có frequency > 0.
• Dormant Users:
  • Không có giao dịch nào trong hơn 180 ngày (recency_days > 180).
• New Registered:
  • Người dùng mới đăng ký nhưng chưa có bất kỳ giao dịch nào (frequency = 0).

DECLARE @today_date DATETIME = '2024-07-16';
WITH user_transaction_stats AS (
    SELECT
        u.user_id,
        u.registration_date,
        COUNT(t.transaction_id) AS frequency, 
        MIN(t.transaction_date) AS first_transaction_date, 
        MAX(t.transaction_date) AS last_transaction_date, 
        COUNT(DISTINCT t.service_id) AS service_variety 
    FROM users u
    LEFT JOIN transactions t ON u.user_id = t.user_id 
    GROUP BY u.user_id, u.registration_date
),
user_inactivity_periods AS (
    SELECT
        user_id,
        MAX(inactivity_days) AS max_inactivity_period 
    FROM (
        SELECT
            user_id,
            DATEDIFF(day, LAG(transaction_date, 1, transaction_date) OVER (PARTITION BY user_id ORDER BY transaction_date), transaction_date) AS inactivity_days
        FROM transactions
    ) AS inactivity_data
    GROUP BY user_id
),
final_user_profile AS (
    SELECT
        s.user_id,
        s.registration_date,
        s.frequency,
        s.service_variety,
        DATEDIFF(day, s.last_transaction_date, @today_date) AS recency_days,
        DATEDIFF(day, s.registration_date, @today_date) AS user_age_days,
        DATEDIFF(day, s.registration_date, s.first_transaction_date) AS days_to_first_transaction,
        ISNULL(i.max_inactivity_period, 0) AS max_inactivity_period
    FROM user_transaction_stats s
    LEFT JOIN user_inactivity_periods i ON s.user_id = i.user_id 
)
SELECT
    p.user_id,
    CASE
        WHEN p.recency_days <= 30 AND p.frequency >= 20 AND p.service_variety >= 4 THEN 'Power User'
        WHEN p.max_inactivity_period >= 180 AND p.recency_days <= 30 THEN 'Resurrected User'
        WHEN p.user_age_days < 365 AND p.frequency BETWEEN 1 AND 3 AND p.recency_days > 90 THEN 'Churned Activated User'
        WHEN p.user_age_days <= 30 AND p.frequency > 0 THEN 'New Active User'
        WHEN p.recency_days BETWEEN 91 AND 180 THEN 'At-Risk User'
        WHEN p.recency_days <= 90 AND p.frequency > 0 THEN 'Casual User'
        WHEN p.recency_days > 180 THEN 'Dormant User'
        ELSE 'New Registered'
    END AS user_segment
FROM final_user_profile p
ORDER BY p.user_id;

Cách tiếp cận hiệu quả nhất cho các bài toán phân tích phức tạp là đi ngược từ kết quả cuối cùng.

• Đích đến: Phân loại mỗi user_id vào một nhóm cụ thể.
• Logic: Sử dụng một cấu trúc CASE WHEN ... THEN ... END để áp dụng các quy tắc phân loại.
• Câu hỏi đặt ra: Để viết được các điều kiện CASE WHEN, bạn cần những chỉ số (metrics) nào?
• Các chỉ số cần thiết:
  • recency_days: Số ngày từ lần giao dịch cuối.
  • frequency: Tổng số giao dịch.
  • service_variety: Số lượng dịch vụ khác nhau đã dùng.
  • user_age_days: “Tuổi” của tài khoản (tính từ ngày đăng ký).
  • max_inactivity_period: Khoảng thời gian “ngủ đông” dài nhất.
• Vấn đề: Các chỉ số này không có sẵn trong cơ sở dữ liệu. Bạn phải tính toán chúng từ dữ liệu thô trong bảng users và transactions.

• recency_days: Cần MAX(transaction_date) của mỗi user.
  • Công thức: DATEDIFF(day, MAX(transaction_date), @today_date)
• frequency: Cần COUNT(transaction_id) của mỗi user.
• service_variety: Cần COUNT(DISTINCT service_id) của mỗi user.
• user_age_days: Cần registration_date của mỗi user.
  • Công thức: DATEDIFF(day, registration_date, @today_date)
• max_inactivity_period:
  • Logic: Đối với mỗi user, bạn cần so sánh ngày của mỗi giao dịch với ngày của giao dịch ngay trước đó.
  • SQL: Window Function LAG().
  • Thực hiện:
    1. Sắp xếp các giao dịch của mỗi user theo ngày (PARTITION BY user_id ORDER BY transaction_date).
    2. Dùng LAG(transaction_date, 1) OVER (...) để lấy ngày giao dịch trước đó.
    3. Dùng DATEDIFF để tính khoảng cách (số ngày) giữa giao dịch hiện tại và giao dịch trước đó.
    4. Cuối cùng, dùng MAX() trên tất cả các khoảng cách đã tính để tìm ra khoảng nghỉ dài nhất.

Sử dụng CTEs giúp chia nhỏ bài toán, làm cho code trở nên rõ ràng, dễ đọc và dễ gỡ lỗi.

• CTE 1: user_transaction_stats
  • Nhiệm vụ: Tính các chỉ số cơ bản.
  • SQL: LEFT JOIN users và transactions, sau đó GROUP BY user_id để tính frequency,  service_variety, last_transaction_date.
• CTE 2: user_inactivity_periods
  • Nhiệm vụ: Tính riêng chỉ số phức tạp max_inactivity_period.
  • SQL: Sử dụng LAG() trong một subquery hoặc CTE lồng nhau, sau đó GROUP BY user_id để tìm MAX() của các khoảng nghỉ.
• CTE 3: final_user_profile
  • Nhiệm vụ: Gộp tất cả các chỉ số lại với nhau.
  • SQL: JOIN kết quả từ CTE 1 và CTE 2. Thực hiện các phép tính DATEDIFF cuối cùng để ra  recency_days  và user_age_days. Kết quả của CTE này là một bảng ảo hoàn chỉnh, mỗi dòng là một user với đầy đủ các chỉ số hành vi.
• Truy vấn cuối cùng:
  • Nhiệm vụ: Áp dụng các quy tắc phân loại.
  • SQL: SELECT từ final_user_profile và sử dụng CASE WHEN để gán nhãn phân khúc cho từng user.

• Tầm quan trọng của thứ tự CASE WHEN
  • CASE WHEN sẽ trả về kết quả của điều kiện ĐÚNG đầu tiên nó gặp. Do đó, thứ tự phải đi từ cụ thể nhất đến chung chung nhất.
  • Ví dụ: Một Power User cũng thỏa mãn điều kiện của Casual User. Nếu WHEN Casual User... được đặt trước, tất cả Power User sẽ bị phân loại sai.
  • Thứ tự đúng:
    • Nhóm đặc biệt & giá trị cao: Power User, Resurrected User.
    • Nhóm có điều kiện đặc thù: Churned Activated User, New Active User.
    • Nhóm theo trạng thái: At-Risk User.
    • Nhóm cho active user: Casual User (phải đứng sau các nhóm active cụ thể khác).
    • Nhóm không hoạt động: Dormant User.
    • Nhóm mặc định: New Registered (dùng ELSE để bắt các trường hợp còn lại, chủ yếu là user chưa có giao dịch).
• Xử lý NULL và các trường hợp biên
  • LEFT JOIN vs INNER JOIN: Phải dùng LEFT JOIN từ users sang transactions. Nếu không, tất cả những user chưa từng giao dịch (New Registered) sẽ bị loại khỏi kết quả.
  • recency_days cho user mới: Đối với user chưa có giao dịch, last_transaction_date là NULL, dẫn đến recency_days cũng là NULL. Điều này là đúng và giúp họ không bị phân loại nhầm vào các nhóm active. Cuối cùng, họ sẽ được xử lý bởi mệnh đề ELSE.
  • max_inactivity_period cho user có 0-1 giao dịch:
    • Những user này sẽ không có “khoảng nghỉ” nào, dẫn đến max_inactivity_period là NULL sau khi join.
    • ISNULL(i.max_inactivity_period, 0) là một bước xử lý cực kỳ quan trọng. Nó gán giá trị 0 cho những trường hợp này, đảm bảo các phép so sánh logic sau đó (ví dụ max_inactivity_period >= 180) hoạt động chính xác.
• Hàm LAG()
  • LAG(transaction_date, 1, transaction_date) OVER (...)
  • Tham số thứ ba (transaction_date) là giá trị mặc định. Khi LAG được áp dụng cho giao dịch đầu tiên của một user, không có giao dịch nào “trước đó”. Thay vì trả về NULL, nó sẽ trả về chính ngày giao dịch đó.
  • DATEDIFF giữa một ngày và chính nó là 0. Điều này đảm bảo giao dịch đầu tiên không tạo ra một khoảng nghỉ, giúp logic tính toán luôn đúng.

Với mô hình Challenge-based Learning, bạn sẽ không chỉ học Business Domain, mà được giải các bài toán như trong doanh nghiệp, rèn tư duy phân tích và tạo Portfolio ngay trong các khóa học phân tích dữ liệu thực chiến. Inbox mình TẠI ĐÂY nếu bạn muốn đăng ký nhé!