Ratio Metrics & Churn Users

• Klipfolio, một công ty SaaS cho phép users tạo bảng điều khiển (dashboard) trực tuyến.
• Chỉ số đo lường được áp dụng trong case này: (Số lượt edit dashboard) / (Số lượt save dashboard) => Được gọi là tỷ lệ kém hiệu quả (Inefficiency Rate).
• Những users có chỉ số này cao (tức là edit rất nhiều nhưng save lại ít) có nguy cơ churn cao hơn.
• Việc phải edit nhiều lần mới đạt được kết quả mong muốn và save lại cho thấy users có thể đang gặp khó khăn, tốn nhiều công sức hơn để sử dụng sản phẩm. Sự không hiệu quả này dẫn đến trải nghiệm không tốt và làm tăng nguy cơ churn của users.

• Table: users
  • user_id, signup_date, last_seen_date, churn_date, is_churned
• Table: dashboard_actions
  • action_id, user_id, dashboard_id, action_type, action_timestamp

• Challenge 01: Tính edits_per_save cho mỗi user.
  • Thước đo cơ bản, định lượng được mức độ “kém hiệu quả” của từng user riêng lẻ khi họ sử dụng sản phẩm. Đây là bước đầu tiên để xác định ai có thể đang gặp khó khăn.

declare @end_date datetime = getdate();
declare @start_date datetime = dateadd(day, -90, @end_date);
with user_action_counts as (
    select
        user_id,
        sum(case when action_type = 'edit' then 1 else 0 end) as total_edits,
        sum(case when action_type = 'save' then 1 else 0 end) as total_saves
    from dashboard_actions
    where action_timestamp between @start_date and @end_date
    group by user_id
)
select
    u.user_id,
    u.is_churned,
    isnull(ua.total_edits, 0) as total_edits_last_90d,
    isnull(ua.total_saves, 0) as total_saves_last_90d,
    case
        when isnull(ua.total_saves, 0) > 0 then
            cast(isnull(ua.total_edits, 0) as float) / ua.total_saves
        else
            case when isnull(ua.total_edits, 0) > 0 then 9999.0 else 0.0 end
    end as edits_per_save_ratio
from users u
left join user_action_counts ua on u.user_id = ua.user_id
where isnull(ua.total_edits, 0) > 0 or isnull(ua.total_saves, 0) > 0
order by edits_per_save_ratio desc;

• Challenge 02: Phân tích tương quan giữa nhóm tỷ lệ và churn.
  • Kiểm chứng giả thuyết cốt lõi: Liệu việc sử dụng sản phẩm kém hiệu quả (tỷ lệ edits_per_save cao) có thực sự liên quan đến việc user rời bỏ sản phẩm nhiều hơn không?

with user_total_actions as (
    select
        user_id,
        sum(case when action_type = 'edit' then 1 else 0 end) as total_edits,
        sum(case when action_type = 'save' then 1 else 0 end) as total_saves
    from dashboard_actions
    -- where action_timestamp > 'yyyy-mm-dd' -- co the gioi han thoi gian neu can
    group by user_id
), user_ratios as (
    select
        uta.user_id,
        uta.total_edits,
        uta.total_saves,
        case
            when uta.total_saves > 0 then cast(uta.total_edits as float) / uta.total_saves
            else case when uta.total_edits > 0 then 9999.0 else 0.0 end
        end as edits_per_save_ratio
    from user_total_actions uta
    where uta.total_edits > 0 or uta.total_saves > 0 
), user_ratio_groups as (
    select
        ur.user_id,
        ur.edits_per_save_ratio,
        case
            when ur.edits_per_save_ratio < 3.0 then '1_low (<3)'
            when ur.edits_per_save_ratio >= 3.0 and ur.edits_per_save_ratio < 8.0 then '2_medium (3-8)'
            when ur.edits_per_save_ratio >= 8.0 then '3_high (>=8)'
            else '0_no_activity_or_ratio'
        end as efficiency_group
    from user_ratios ur
)
select
    urg.efficiency_group,
    count(distinct u.user_id) as total_users_in_group,
    sum(case when u.is_churned = 1 then 1 else 0 end) as churned_users_in_group,
    cast(sum(case when u.is_churned = 1 then 1 else 0 end) as float) * 100.0 / count(distinct u.user_id) as churn_rate_percent
from user_ratio_groups urg
join users u on urg.user_id = u.user_id
group by urg.efficiency_group
order by urg.efficiency_group;

• Challenge 3: Phân tích xu hướng tỷ lệ trước khi churn.
  • Khám phá xem sự kém hiệu quả có phải là một dấu hiệu cảnh báo sớm hay không. Liệu user có xu hướng “vật lộn” nhiều hơn ngay trước khi họ quyết định rời đi? Nếu có, chúng ta có thể phát hiện nguy cơ churn sớm hơn để can thiệp.

with monthly_actions as (
    select
        user_id,
        datefromparts(year(action_timestamp), month(action_timestamp), 1) as action_month,
        sum(case when action_type = 'edit' then 1 else 0 end) as monthly_edits,
        sum(case when action_type = 'save' then 1 else 0 end) as monthly_saves
    from dashboard_actions
    group by
        user_id,
        datefromparts(year(action_timestamp), month(action_timestamp), 1)
), monthly_ratios as (
    select
        ma.user_id,
        ma.action_month,
        ma.monthly_edits,
        ma.monthly_saves,
        case
            when ma.monthly_saves > 0 then cast(ma.monthly_edits as float) / ma.monthly_saves
            else case when ma.monthly_edits > 0 then 9999.0 else 0.0 end
        end as monthly_edits_per_save_ratio
    from monthly_actions ma
    where ma.monthly_edits > 0 or ma.monthly_saves > 0
), churned_users_months_before_churn as (
    select
        mr.user_id,
        u.churn_date,
        mr.action_month,
        mr.monthly_edits_per_save_ratio,
        datediff(month, mr.action_month, u.churn_date) as months_before_churn
    from monthly_ratios mr
    join users u on mr.user_id = u.user_id
    where u.is_churned = 1 and u.churn_date is not null
      and mr.action_month <= u.churn_date 
)
select
    cumbc.months_before_churn,
    avg(cumbc.monthly_edits_per_save_ratio) as avg_ratio_before_churn,
    count(distinct cumbc.user_id) as num_users_in_group
from churned_users_months_before_churn cumbc
where cumbc.months_before_churn between 0 and 6 
group by cumbc.months_before_churn
order by cumbc.months_before_churn asc; 

• Challenge 4: Phân tích tương tác giữa tỷ lệ và thời gian sử dụng.
  • Liệu sự kém hiệu quả có tác động khác nhau đến new users (giai đoạn onboarding quan trọng) so với users đã gắn bó lâu năm hay không. Điều này giúp tối ưu hóa trải nghiệm cho từng giai đoạn vòng đời khách hàng.

with user_total_actions as (
    select
        user_id,
        sum(case when action_type = 'edit' then 1 else 0 end) as total_edits,
        sum(case when action_type = 'save' then 1 else 0 end) as total_saves
    from dashboard_actions
    group by user_id
), user_ratios as (
    select
        uta.user_id,
        case
            when uta.total_saves > 0 then cast(uta.total_edits as float) / uta.total_saves
            else case when uta.total_edits > 0 then 9999.0 else 0.0 end
        end as edits_per_save_ratio
    from user_total_actions uta
    where uta.total_edits > 0 or uta.total_saves > 0
),
user_tenure_groups as (
    select
        u.user_id,
        u.is_churned,
        datediff(day, u.signup_date, isnull(u.churn_date, u.last_seen_date)) as tenure_days,
        case
            when datediff(day, u.signup_date, isnull(u.churn_date, u.last_seen_date)) < 60 then '1_new (<60d)'
            when datediff(day, u.signup_date, isnull(u.churn_date, u.last_seen_date)) >= 60
             and datediff(day, u.signup_date, isnull(u.churn_date, u.last_seen_date)) < 180 then '2_medium (60-180d)'
            else '3_tenured (>=180d)'
        end as tenure_group
    from users u
),
user_efficiency_groups as (
    select
        ur.user_id,
        case
             when ur.edits_per_save_ratio < 3.0 then 'low_efficiency'
             when ur.edits_per_save_ratio >= 3.0 and ur.edits_per_save_ratio < 8.0 then 'medium_efficiency'
             when ur.edits_per_save_ratio >= 8.0 then 'high_efficiency'
             else 'zero_ratio'
        end as efficiency_group
    from user_ratios ur
)
select
    utg.tenure_group,
    ueg.efficiency_group,
    count(distinct utg.user_id) as total_users,
    sum(case when utg.is_churned = 1 then 1 else 0 end) as churned_users,
    cast(sum(case when utg.is_churned = 1 then 1 else 0 end) as float) * 100.0 / count(distinct utg.user_id) as churn_rate_percent
from user_tenure_groups utg
join user_efficiency_groups ueg on utg.user_id = ueg.user_id
group by
    utg.tenure_group,
    ueg.efficiency_group
order by
    utg.tenure_group,
    ueg.efficiency_group;

• Challenge 05: Phân tích kết hợp tỷ lệ và tần suất hoạt động
  • Liệu người dùng kém hiệu quả là người rất tích cực nhưng liên tục gặp khó khăn hay là người chỉ thử một chút rồi bỏ cuộc? Hiểu điều này giúp phân loại mức độ rủi ro và chiến lược can thiệp phù hợp hơn.

declare @end_date datetime = getdate();
declare @start_date datetime = dateadd(day, -90, @end_date);
with user_actions_last_90d as (
    select
        user_id,
        sum(case when action_type = 'edit' then 1 else 0 end) as total_edits,
        sum(case when action_type = 'save' then 1 else 0 end) as total_saves,
        count(*) as total_actions 
    from dashboard_actions
    where action_timestamp between @start_date and @end_date
    group by user_id
), user_metrics_last_90d as (
    select
        ua.user_id,
        ua.total_actions,
        case
            when ua.total_saves > 0 then cast(ua.total_edits as float) / ua.total_saves
            else case when ua.total_edits > 0 then 9999.0 else 0.0 end
        end as edits_per_save_ratio
    from user_actions_last_90d ua
    where ua.total_actions > 0 
),
user_segments as (
    select
        um.user_id,
        um.edits_per_save_ratio,
        um.total_actions,
        case
            when um.edits_per_save_ratio >= 8.0 then 'high_efficiency_ratio'
            else 'low_medium_efficiency_ratio' 
        end as efficiency_segment,
        case
            when um.total_actions >= 50 then 'high_activity' 
            else 'low_activity'
        end as activity_segment
    from user_metrics_last_90d um
)
select
    us.efficiency_segment,
    us.activity_segment,
    count(distinct u.user_id) as total_users,
    sum(case when u.is_churned = 1 then 1 else 0 end) as churned_users,
    case
        when count(distinct u.user_id) > 0 then
            cast(sum(case when u.is_churned = 1 then 1 else 0 end) as float) * 100.0 / count(distinct u.user_id)
        else 0.0
    end as churn_rate_percent
from user_segments us
join users u on us.user_id = u.user_id
group by
    us.efficiency_segment,
    us.activity_segment
order by
    us.efficiency_segment,
    us.activity_segment;

• Challenge 06: Phân tích chuỗi EDIT liên tiếp kéo dài.
  • Phát hiện những khoảnh khắc “bế tắc” của users (thể hiện qua việc edit liên tục không ngừng nghỉ mà không save). Những khoảnh khắc này có thể là tín hiệu dự báo churn tức thời và mạnh mẽ hơn so với tỷ lệ trung bình.
    

with action_sequences as (
    select
        action_id,
        user_id,
        dashboard_id,
        action_type,
        action_timestamp,
        case
            when lag(action_type, 1, action_type) over(partition by user_id, dashboard_id order by action_timestamp) = action_type
            then 0 
            else 1
        end as is_new_sequence_start
    from dashboard_actions
),
sequence_groups as (
    select
        aqs.*,
        sum(aqs.is_new_sequence_start) over(partition by user_id, dashboard_id order by action_timestamp rows unbounded preceding) as sequence_group_id
    from action_sequences aqs
),
edit_streaks as (
    select
        user_id,
        dashboard_id,
        sequence_group_id,
        min(action_timestamp) as streak_start_time,
        max(action_timestamp) as streak_end_time,
        count(*) as streak_length 
    from sequence_groups
    where action_type = 'edit'
    group by user_id, dashboard_id, sequence_group_id
),
users_with_long_streaks as (
    select distinct user_id
    from edit_streaks
    where streak_length >= 10 
)
select
    case
        when uwls.user_id is not null
        then 'experienced long edit streak (>=10)'
        else 'no long edit streak'
    end as user_streak_experience,
    count(distinct u.user_id) as total_users,
    sum(case when u.is_churned = 1 then 1 else 0 end) as churned_users,
    case
        when count(distinct u.user_id) > 0 then
           cast(sum(case when u.is_churned = 1 then 1 else 0 end) as float) * 100.0 / count(distinct u.user_id)
        else 0.0
    end as churn_rate_percent
from users u
left join users_with_long_streaks uwls on u.user_id = uwls.user_id
group by
    case
        when uwls.user_id is not null
        then 'experienced long edit streak (>=10)'
        else 'no long edit streak'
    end;

• Challenge 07: Phân tích tác động của “Lộ trình Hiệu quả” đến tỷ lệ churn dài hạn.
  • Đánh giá xem việc users thay đổi mức độ hiệu quả khi sử dụng sản phẩm theo thời gian (ví dụ: từ kém hiệu quả trở nên hiệu quả hơn, hoặc ngược lại) có thực sự ảnh hưởng đến khả năng họ gắn bó lâu dài hay không.

declare @period1_start_month int = 1; 
declare @period1_end_month int = 2;   
declare @period2_start_month int = 3; 
declare @period2_end_month int = 4; 
with actions_period1 as (
    select
        da.user_id,
        sum(case when da.action_type = 'edit' then 1 else 0 end) as edits_p1,
        sum(case when da.action_type = 'save' then 1 else 0 end) as saves_p1
    from dashboard_actions da
    join users u on da.user_id = u.user_id
    where da.action_timestamp >= dateadd(month, @period1_start_month - 1, u.signup_date)
      and da.action_timestamp < dateadd(month, @period1_end_month, u.signup_date)
    group by da.user_id
), ratio_period1 as (
    select
        user_id,
        edits_p1,
        saves_p1,
        case
            when saves_p1 > 0 then cast(edits_p1 as float) / saves_p1
            else case when edits_p1 > 0 then 9999.0 else 0.0 end
        end as ratio_p1
    from actions_period1
    where edits_p1 > 0 or saves_p1 > 0 
),
actions_period2 as (
     select
        da.user_id,
        sum(case when da.action_type = 'edit' then 1 else 0 end) as edits_p2,
        sum(case when da.action_type = 'save' then 1 else 0 end) as saves_p2
    from dashboard_actions da
    join users u on da.user_id = u.user_id
    where da.action_timestamp >= dateadd(month, @period2_start_month - 1, u.signup_date)
      and da.action_timestamp < dateadd(month, @period2_end_month, u.signup_date)
      and u.signup_date <= dateadd(month, -@period2_start_month, getdate())
    group by da.user_id
), ratio_period2 as (
    select
        user_id,
        edits_p2,
        saves_p2,
        case
            when saves_p2 > 0 then cast(edits_p2 as float) / saves_p2
            else case when edits_p2 > 0 then 9999.0 else 0.0 end
        end as ratio_p2
    from actions_period2
    where edits_p2 > 0 or saves_p2 > 0 
),
categorized_ratios as (
    select
        u.user_id,
        u.is_churned,
        case
            when rp1.ratio_p1 < 3.0 then 'low'
            when rp1.ratio_p1 >= 3.0 and rp1.ratio_p1 < 8.0 then 'medium'
            when rp1.ratio_p1 >= 8.0 then 'high'
            else 'inactive_p1' 
        end as category_p1,
        case
            when rp2.ratio_p2 < 3.0 then 'low'
            when rp2.ratio_p2 >= 3.0 and rp2.ratio_p2 < 8.0 then 'medium'
            when rp2.ratio_p2 >= 8.0 then 'high'
            else 'inactive_p2' 
        end as category_p2
    from users u
    left join ratio_period1 rp1 on u.user_id = rp1.user_id
    left join ratio_period2 rp2 on u.user_id = rp2.user_id
    where u.signup_date <= dateadd(month, -@period2_end_month, getdate())
      and rp1.user_id is not null 
      and rp2.user_id is not null 
),
trajectory_segments as (
    select
        user_id,
        is_churned,
        category_p1,
        category_p2,
        case
            when category_p1 = 'high' and category_p2 = 'low' then 'improved (h->l)'
            when category_p1 = 'high' and category_p2 = 'medium' then 'improved (h->m)'
            when category_p1 = 'medium' and category_p2 = 'low' then 'improved (m->l)'
            when category_p1 = 'low' and category_p2 = 'high' then 'worsened (l->h)'
            when category_p1 = 'low' and category_p2 = 'medium' then 'worsened (l->m)'
            when category_p1 = 'medium' and category_p2 = 'high' then 'worsened (m->h)'
            when category_p1 = 'low' and category_p2 = 'low' then 'consistent_low'
            when category_p1 = 'medium' and category_p2 = 'medium' then 'consistent_medium'
            when category_p1 = 'high' and category_p2 = 'high' then 'consistent_high'
            else 'other_transition' 
        end as efficiency_trajectory
    from categorized_ratios
    where category_p1 not like 'inactive%' and category_p2 not like 'inactive%'
)
select
    ts.efficiency_trajectory,
    count(ts.user_id) as total_users,
    sum(case when ts.is_churned = 1 then 1 else 0 end) as churned_users,
    case
        when count(ts.user_id) > 0 then
            cast(sum(case when ts.is_churned = 1 then 1 else 0 end) as float) * 100.0 / count(ts.user_id)
        else 0.0
    end as churn_rate_percent
from trajectory_segments ts
group by ts.efficiency_trajectory
order by churn_rate_percent desc;