Daily Active Users Model – Part 01 – At Risk MAUs → Reactivated Users / Dead Users

Series bài viết phân tích Daily Active Users Model kết hợp giữa Solution & Data Analysis được tham khảo từ mô hình của Duolingo và phân tích thêm theo góc nhìn cá nhân. (Source: https://www.lennysnewsletter.com/p/how-duolingo-reignited-user-growth)

Nếu bạn muốn học Phân Tích Dữ Liệu Thực Chiến – Learning by Doing Challenges & Case Studies thì inbox mình Tại Đây nhé.

Trạng thái “At Risk MAUs” đóng vai trò như một điểm rẽ nhánh quan trọng trong vòng đời users. Từ trạng thái này, users có thể đi theo hai hướng chính: Quay trở lại thành users hoạt động (“Reactivated Users”) hoặc tiếp tục không hoạt động và trở thành users đã rời bỏ (“Dead Users”).

• Là những users được xác định là không hoạt động vào ngày hôm nay (ngày xét chỉ số), và cũng không hoạt động trong suốt 6 ngày liên tiếp trước đó. Tuy nhiên, điểm quan trọng là họ đã có hoạt động ít nhất một lần trong khoảng thời gian từ 7 ngày đến 29 ngày trước ngày hôm nay.
• Đơn giản hơn, đây là những users đã không hoạt động trong 7 ngày gần nhất, nhưng vẫn được coi là có hoạt động trong khung thời gian 1 tháng (MAU – Monthly Active User). Trạng thái “At Risk” cho thấy users đang ở ranh giới, nếu tiếp tục không hoạt động, có khả năng cao trở thành “Dead Users”.
• At risk MAUs (Hôm nay) = COUNT(Users) sao cho:
  • User KHÔNG hoạt động hôm nay.
  • User KHÔNG hoạt động trong 6 ngày trước (Ngày – 1 đến Ngày – 6)
  • User CÓ hoạt động ít nhất 1 lần trong khoảng từ Ngày – 7 đến Ngày – 29
• VD: Giả sử hôm nay là Ngày 30. TungTen lần cuối hoạt động là vào Ngày 20. Từ Ngày 21 đến Ngày 29, TungTen không hoạt động. Kiểm tra user TungTen:
  • TungTen không hoạt động hôm nay (Ngày 30)? Đúng.
  • TungTen không hoạt động trong 6 ngày trước (Ngày 24 – Ngày 29)? Đúng.
  • TungTen có hoạt động trong khoảng 7 – 29 ngày trước (Ngày 1 – Ngày 23)? Đúng.
  • → Vào ngày 30, TungTen được xếp vào nhóm “At risk MAUs”.
• Điểm đến (Hai hướng chính):
  • Hướng 1 (Tích cực) → Reactivated Users: Nếu users trong nhóm “At risk MAU” hôm nay mà hoạt động trở lại vào ngày mai, họ sẽ được chuyển thành “Reactivated User” vào ngày mai. Đây là kết quả mong muốn, cho thấy user quay lại trước khi bị mất hoàn toàn.
  • Hướng 2 (Tiêu cực) → Dead Users: Nếu users trong nhóm “At risk MAU” tiếp tục không hoạt động và vượt qua mốc 29 ngày không hoạt động liên tiếp (tức là đạt 30 ngày), users sẽ chuyển thành “Dead User”. Quá trình này được thể hiện qua “MAU Loss Rate”.

• Là những user hoạt động vào ngày hôm nay, và ngày hôm qua họ thuộc nhóm “At risk MAUs”. Đây là “ngày đầu tiên quay lại” (first day back) của những users từng có nguy cơ rời bỏ ở mức độ tháng.
• Đơn giản hơn, đây là những users đã không hoạt động ít nhất 7 ngày liên tiếp (ngày hôm qua + 6 ngày trước đó) nhưng có hoạt động trong vòng 29 ngày gần nhất (tính từ ngày hôm qua trở về trước), và hôm nay là ngày đầu tiên họ quay lại sử dụng ứng dụng (first day back).
• Reactivated Users (Hôm nay) = COUNT(Users) sao cho:
  • User CÓ hoạt động Hôm nay.
  • User CÓ thuộc nhóm “At risk MAU” vào Ngày hôm qua (N – 1).
• VD: Giả sử hôm nay là Ngày 30. TungTen sử dụng ứng dụng lần cuối vào Ngày 20. Từ Ngày 21 đến Ngày 29, TungTen không hề mở ứng dụng.
  • Xét vào cuối Ngày 29:
    • TungTen không hoạt động vào Ngày 29.
    • TungTen không hoạt động trong 6 ngày trước đó (Ngày 23 – Ngày 28).
    • TungTen có hoạt động vào Ngày 20, nằm trong khoảng 7 – 29 ngày trước đó.
    • → Vào cuối Ngày 29, TungTen được xếp vào nhóm “At risk MAUs”.
  • Vào Ngày 30, TungTen mở lại và sử dụng ứng dụng.
    • → Vào Ngày 30, TungTen được tính là một “Reactivated Users” vì hôm nay là ngày đầu tiên TungTen quay lại (“first day back”) sau khi ở trạng thái “At risk MAUs” vào ngày hôm qua.
• Điểm đến: Nội dung phần này mình sẽ trình bày ở Part sau để liền mạch các chỉ số liên quan khác.

• Là những users được xác định là không hoạt động vào ngày hôm nay (ngày xét chỉ số) và cũng không có bất kỳ hoạt động nào trong suốt 29 ngày liên tiếp trước đó. Đây là ngưỡng được sử dụng để chính thức coi một người dùng đã “churn” sản phẩm hoặc dịch vụ.
• Dead Users (Hôm nay) = COUNT(Users) sao cho:
  • User KHÔNG hoạt động Hôm nay.
  • User KHÔNG hoạt động trong suốt 29 ngày trước đó (Từ Ngày – 1 đến Ngày – 29)
• Mũi tên chính dẫn đến “Dead Users” trong model bắt nguồn từ “At Risk MAUs“ và được gắn nhãn “MAU Loss Rate” (Tỷ lệ mất người dùng hoạt động hàng tháng).
  • Khi users đang ở trạng thái “At risk MAU” và họ tiếp tục không hoạt động, họ sẽ vượt qua mốc 29 ngày không hoạt động liên tục. Ngay khi điều này xảy ra (tức là đạt 30 ngày không hoạt động), users sẽ chuyển từ “At risk MAU” sang “Dead User”.
• Điểm đến: Nội dung phần này mình sẽ trình bày ở Part sau để liền mạch các chỉ số liên quan khác.

Trạng thái “At risk MAUs” là một chỉ báo quan trọng, dự báo hai khả năng chính: Users sẽ được kích hoạt lại (“Reactivated Users”) nếu có sự can thiệp kịp thời hoặc họ sẽ tiếp tục không hoạt động và trở thành users đã rời bỏ (“Dead Users”). Việc theo dõi và tác động vào nhóm “At risk MAUs” là yếu tố then chốt trong các chiến lược giữ chân và giảm tỷ lệ users rời bỏ.

Kết quả mẫu mong đợi:

user_id	days_last_active_when_transitioned	transition_outcome
1	30	became_reactivated
22	17	became_reactivated
63	30	became_dead_from_at_risk_mau
…	…	…

Giải thích kết quả qua dữ liệu: Ngày xét chỉ số (@target_date) = 2025-04-01

• User 1
  • Hoạt động cuối cùng trước @target_date (2025-04-01): 2025-03-02 (view_product).
  • Hoạt động trong ngày @target_date (2025-04-01): login, purchase.
  • days_inactive_yesterday (2025-03-31): DATEDIFF(DAY, ‘2025-03-02’, ‘2025-03-31’) = 29 ngày (at_risk_mau).
  • days_last_active_when_transitioned: 29 + 1 = 30 ngày.
  • Trạng thái ngày @target_date (2025-04-01): Có hoạt động (login, purchase) → reactivated.
• User 22
  • Hoạt động cuối cùng trước @target_date (2025-04-01): 2025-03-15 (login).
  • Hoạt động trong ngày @target_date (2025-04-01): login.
  • days_inactive_yesterday (2025-03-31): DATEDIFF(DAY, ‘2025-03-15’, ‘2025-03-31’) = 16 ngày (at_risk_mau).
  • days_last_active_when_transitioned: 16 + 1 = 17 ngày.
  • Trạng thái ngày @target_date (2025-04-01): Có hoạt động → reactivated.
• User 63
  • Hoạt động cuối cùng trước @target_date (2025-04-01): 2025-03-02 (login).
  • Không có hoạt động vào ngày @target_date (2025-04-01).
  • days_inactive_yesterday (2025-03-31): DATEDIFF(DAY, ‘2025-03-02’, ‘2025-03-31’) = 29 ngày (at_risk_mau).
  • days_last_active_when_transitioned: 29 + 1 = 30 ngày.
  • Trạng thái ngày @target_date (2025-04-01): Không active, days_inactive_today = 30 → dead.
• Từ kết quả trên bạn sẽ thấy được insight gì? → Nội dung này sẽ có trong khóa học DA BootCamp.

declare @target_date date = '2025-04-01';
with user_last_activity as (
    select
        user_id,
        max(activity_date) as last_activity_date
    from user_activity
    where activity_date < @target_date
    group by user_id
),
user_status_yesterday as (
    select
        u.user_id,
        ula.last_activity_date,
        datediff(day, ula.last_activity_date, dateadd(day, -1, @target_date)) as days_inactive_yesterday,
        case
            when datediff(day, ula.last_activity_date, dateadd(day, -1, @target_date)) between 7 and 29 then 'at_risk_mau'
            when datediff(day, ula.last_activity_date, dateadd(day, -1, @target_date)) >= 30 then 'dead'
            else 'other'
        end as status_yesterday
    from (select distinct user_id from user_activity) u
    left join user_last_activity ula on u.user_id = ula.user_id
),
user_status_today as (
    select
        u.user_id,
        max(case when ua.activity_date = @target_date then 1 else 0 end) as is_active_today,
        datediff(day, ula.last_activity_date, @target_date) as days_inactive_today,
        case
            when max(case when ua.activity_date = @target_date then 1 else 0 end) = 1
                and usy.status_yesterday = 'at_risk_mau' then 'reactivated'
            when max(case when ua.activity_date = @target_date then 1 else 0 end) = 1
                and usy.status_yesterday = 'dead' then 'resurrected'
            when max(case when ua.activity_date = @target_date then 1 else 0 end) = 0
                and datediff(day, ula.last_activity_date, @target_date) >= 30 then 'dead'
            when max(case when ua.activity_date = @target_date then 1 else 0 end) = 0
                and datediff(day, ula.last_activity_date, @target_date) between 7 and 29 then 'at_risk_mau'
            else 'other'
        end as status_today
    from (select distinct user_id from user_activity) u
    left join user_last_activity ula on u.user_id = ula.user_id
    left join user_status_yesterday usy on u.user_id = usy.user_id
    left join user_activity ua on u.user_id = ua.user_id and ua.activity_date = @target_date
    group by u.user_id, ula.last_activity_date, usy.status_yesterday
)
select
    usy.user_id,
    usy.days_inactive_yesterday + 1 as days_since_last_active_when_transitioned,
    case
        when ust.status_today = 'reactivated' then 'became_reactivated'
        when ust.status_today = 'dead' and usy.status_yesterday = 'at_risk_mau' then 'became_dead_from_at_risk_mau'
        else 'no_transition_or_other'
    end as transition_outcome
from user_status_yesterday usy
join user_status_today ust on usy.user_id = ust.user_id
where usy.status_yesterday = 'at_risk_mau'
  and ust.status_today in ('reactivated', 'dead');

Kết quả mong đợi:

reactivation_day_action	num_action_types	avg_active_days_next_week	user_count
purchase + send_message	2	4.67	3
send_message + view_product	2	4	1
view_product	1	2.67	3
send_message	1	2	2
no_significant_action	0	1	1
purchase	1	0.67	3

Giải thích kết quả qua dữ liệu: Ngày xét chỉ số (@target_date) = 2025-04-01

• purchase + send_message
  • User liên quan: User 40, 50, 99.
  • User 40:
    • Ngày 2025-04-01: purchase, send_message.
    • Tuần sau (2025-04-02 đến 2025-04-08): 2025-04-02, 04-03, 04-05, 04-07 → 4 ngày active.
  • User 50:
    • Ngày 2025-04-01: purchase, send_message.
    • Tuần sau: 2025-04-02, 04-03, 04-04, 04-05, 04-07 → 5 ngày active.
  • User 99:
    • Ngày 2025-04-01: purchase, send_message.
    • Tuần sau: 2025-04-02, 04-03, 04-04, 04-05, 04-07 → 5 ngày active.
  • Tổng số ngày active: 4 (User 40) + 5 (User 50) + 5 (User 99) = 14.
  • avg_active_days_next_week = 14 / 3 = 4.67 ngày.
  • user_count = 3
• Tương tự cho các reactivation_day_action khác.
• Từ kết quả trên bạn sẽ thấy được insight gì? → Nội dung này sẽ có trong khóa học DA BootCamp.
  

declare @reactivation_date date = '2025-04-01';
declare @end_date_insight_week date = dateadd(day, 7, @reactivation_date);
with reactivated_users as (
    select distinct ua.user_id
    from user_activity ua
    join (
        select u_inner.user_id,
               case
                   when datediff(day, max(ua_inner.activity_date), dateadd(day, -1, @reactivation_date)) between 7 and 29 then 'at_risk_mau'
                   else 'other'
               end as status_yesterday
        from (select distinct user_id from user_activity) u_inner
        left join user_activity ua_inner on u_inner.user_id = ua_inner.user_id and ua_inner.activity_date < @reactivation_date
        group by u_inner.user_id
    ) usy on ua.user_id = usy.user_id
    where ua.activity_date = @reactivation_date and usy.status_yesterday = 'at_risk_mau'
),
reactivation_day_activity as (
    select 
        user_id,
        isnull(string_agg(case when activity_type != 'login' then activity_type end, ' + '), 'no_significant_action') as action_combination,
        count(distinct case when activity_type != 'login' then activity_type end) as num_action_types
    from user_activity
    where activity_date = @reactivation_date
      and user_id in (select user_id from reactivated_users)
    group by user_id
),
next_week_activity as (
    select
        user_id,
        count(distinct activity_date) as active_days_next_week
    from user_activity
    where user_id in (select user_id from reactivated_users)
      and activity_date > @reactivation_date
      and activity_date <= @end_date_insight_week
    group by user_id
)
select
    rda.action_combination as reactivation_day_actions,
    rda.num_action_types as num_action_types,
    avg(cast(isnull(nwa.active_days_next_week, 0) as float)) as avg_active_days_next_week,
    count(distinct rda.user_id) as user_count
from reactivation_day_activity rda
left join next_week_activity nwa on rda.user_id = nwa.user_id
group by rda.action_combination, rda.num_action_types
order by avg_active_days_next_week desc, num_action_types desc;

declare @reactivation_start_date date = '2025-04-01'; 
declare @reactivation_end_date date = '2025-04-14';   
declare @observation_window_days int = 5; 
with date_series as (
    select @reactivation_start_date as check_date
    union all
    select dateadd(day, 1, check_date)
    from date_series
    where check_date < dateadd(day, @observation_window_days + 5, @reactivation_end_date) 
),
user_daily_status as (
    select distinct
        d.check_date,
        u.user_id,
        case when ua.activity_date is not null then 1 else 0 end as is_active
    from date_series d
    cross join (select distinct user_id from user_activity) u
    left join user_activity ua on u.user_id = ua.user_id and ua.activity_date = d.check_date
),
reactivation_events as (
    select
        uds_today.user_id,
        uds_today.check_date as reactivation_date
    from user_daily_status uds_today
    join user_daily_status uds_yesterday on uds_today.user_id = uds_yesterday.user_id 
        and uds_today.check_date = dateadd(day, 1, uds_yesterday.check_date)
    left join (
        select user_id, check_date, 
               max(is_active) over (partition by user_id order by check_date rows between 6 preceding and 1 preceding) as was_active_1_6_days_ago
        from user_daily_status
    ) recent_act on uds_yesterday.user_id = recent_act.user_id and uds_yesterday.check_date = recent_act.check_date
    where uds_today.is_active = 1 
      and uds_yesterday.is_active = 0 
      and isnull(recent_act.was_active_1_6_days_ago, 0) = 0 
      and exists (
          select 1
          from user_activity ua
          where ua.user_id = uds_yesterday.user_id
            and ua.activity_date between dateadd(day, -30, uds_yesterday.check_date) 
            and dateadd(day, -8, uds_yesterday.check_date)
      ) 
      and uds_today.check_date between @reactivation_start_date and @reactivation_end_date
),
first_week_activity as (
    select
        re.user_id,
        re.reactivation_date,
        count(distinct ua.activity_date) as active_days_first_week,
        max(case when ua.activity_type = 'purchase' then 1 else 0 end) as made_purchase,
        max(case when ua.activity_type = 'send_message' then 1 else 0 end) as sent_message
    from reactivation_events re
    join user_activity ua on re.user_id = ua.user_id
    where ua.activity_date >= re.reactivation_date
      and ua.activity_date < dateadd(day, @observation_window_days, re.reactivation_date)
    group by re.user_id, re.reactivation_date
),
days_to_next_inactive as (
    select
        re.user_id,
        re.reactivation_date,
        min(datediff(day, re.reactivation_date, next_inactive.first_inactive_date)) as days_to_long_inactivity
    from reactivation_events re
    cross apply (
        select top 1
            uds_outer.check_date as first_inactive_date
        from user_daily_status uds_outer
        where uds_outer.user_id = re.user_id
          and uds_outer.check_date > re.reactivation_date
          and uds_outer.is_active = 0
          and not exists (
              select 1
              from user_daily_status uds_inner
              where uds_inner.user_id = uds_outer.user_id
                and uds_inner.check_date > uds_outer.check_date
                and uds_inner.check_date < dateadd(day, 7, uds_outer.check_date)
                and uds_inner.is_active = 1
          )
        order by uds_outer.check_date asc
    ) next_inactive
    group by re.user_id, re.reactivation_date
)
select
    fwa.active_days_first_week,
    fwa.made_purchase,
    fwa.sent_message,
    avg(cast(isnull(dtni.days_to_long_inactivity, 999) as float)) as avg_days_to_next_inactivity,
    count(distinct fwa.user_id) as user_count
from first_week_activity fwa
left join days_to_next_inactive dtni on fwa.user_id = dtni.user_id and fwa.reactivation_date = dtni.reactivation_date
group by fwa.active_days_first_week, fwa.made_purchase, fwa.sent_message
order by avg_days_to_next_inactivity desc;