Cách Tính Cohort Retention Trong SQL

Discussion in 'Tableau' started by tableau, Aug 11, 2017.

  1. tableau

    tableau Well-Known Member

    Mất người dùng thân thiết. Mất khách hàng quan trọng. Nếu bạn là một người khởi đầu bạn biết rằng khách hàng là vua. Bạn nên luôn luôn đo lường và cải thiện mức độ giữ chân người dùng, vì vậy bạn có thể giữ nhiều người dùng hơn theo thời gian. Trong bài đăng này, sẽ hướng dẫn bạn cách tính toán số người dùng duy trì dữ liệu của chính bạn trong SQL.

    Defining retention
    Nếu người A sử dụng sản phẩm này vào Thứ Hai, và sử dụng lại vào thứ ba, cô ấy là một người dùng được giữ lại. Nếu người B sử dụng sản phẩm vào thứ hai, nhưng không phải vào thứ ba, ông là một người dùng đã mất hiệu lực. Giữ lại thứ hai là số người dùng được giữ lại chia cho tổng số người dùng. Nếu A và B là hai người duy nhất vào thứ Hai, thì việc giữ lại thứ hai là 50%.

    Tính tolans user retention cơ bản
    Chìa khóa để tính toán tỷ lệ giữ chân là tính người dùng đang hoạt động tại thời điểm số 1, sau đó đếm số người hoạt động ở thời điểm số 2. Một cách dễ dàng để làm điều này trong SQL là để lại tham gia bảng hoạt động người dùng của bạn để nó giống như vậy:

    select*

    from activity

    left join activity as future_activity on

    activity.user_id = future_activity.user_id

    and activity.date = future_activity.date - interval '1 day'

    Bây giờ, đối với mỗi hàng hoạt động của người dùng, ta có - trong cùng một hàng - hoạt động của họ 1 ngày trong tương lai. Điều này cho chúng ta một bảng lý tưởng để tính toán lưu giữ với một số tính đơn giản:

    select

    activity.date, ​

    count(distinct activity.user_id) as active_users, ​

    count(distinct future_activity.user_id) as retained_users,​

    count(distinct future_activity.user_id) /

    count(distinct activity.user_id)::float as retention​

    from activity​

    left join activity as future_activity on

    activity.user_id = future_activity.user_id​

    and activity.date = future_activity.date - interval '1 day'

    group by 1​


    Trong công cụ phân tích dữ liệu, chúng ta nhận được biểu đồ này:
    upload_2017-8-11_8-24-51.png

    Để có thêm tín dụng, hãy thay đổi thời gian giữ lại 1 ngày sang 7 ngày hoặc 30 ngày để nắm bắt được ý nghĩa tham gia của người dùng dài hạn.
    select

    users.date as date,

    count(distinct activity.user_id) as new_users,

    count(distinct future_activity.user_id) as retained_users,

    count(distinct future_activity.user_id) /

    count(distinct activity.user_id)::float as retention

    from activity

    -- Limits activity to activity from new users

    join users on

    activity.user_id = users.id

    and users.date = activity.date

    left join activity as future_activity on

    activity.user_id = future_activity.user_id

    and activity.date = future_activity.date - interval '1 day'

    group by 1

    upload_2017-8-11_8-25-34.png

    Ta thấy rằng mặc dù tỷ lệ giữ lại chung là 46%, tỷ lệ giữ chân người dùng mới chỉ là 5,8%! Bây giờ ta thấy tại sao nó rất hữu ích để chia ra người dùng mới. Cải thiện giữ người dùng mới nên rõ ràng là một ưu tiên.

    Để xem xét việc giữ lại người dùng, chỉ cần thay đổi:
    users.date = activity.date
    đến:
    users.date != activity.date

    Điều này loại trừ hoạt động từ những người dùng tham gia vào ngày đó. Truy vấn bây giờ giống như sau:
    select

    activity.date as date,

    count(distinct activity.user_id) as new_users,

    count(distinct future_activity.user_id) as retained_users,

    count(distinct future_activity.user_id) /

    count(distinct activity.user_id)::float as retention

    from activity

    -- Limits activity to activity from existing users

    join users on

    activity.user_id = users.id

    and users.date != activity.date

    left join activity as future_activity on

    activity.user_id = future_activity.user_id

    and activity.date = future_activity.date - interval '1 day'

    group by 1

    upload_2017-8-11_8-27-36.png
    Theo dự kiến, tỷ lệ giữ chân người dùng hiện tại cao hơn mức trung bình chung: 66% so với 46%.

    Calculating retention in cohorts
    Sẽ rất hữu ích để so sánh việc duy trì người dùng tham gia vào tuần A với những người tham gia vào tuần B. Điều này cho phép ta xem liệu những thay đổi sản phẩm của ta có cải thiện tỷ lệ duy trì của ta hay không. Lý tưởng nhất là sẽ kết thúc bằng biểu đồ như sau:

    upload_2017-8-11_8-28-0.png
    Ta sẽ bắt đầu bằng cách xác định một vài truy vấn phụ hữu ích để đơn giản hóa vấn đề. New_user_activity giới hạn hoạt động của người dùng cho người dùng mới:
    with new_user_activity as (

    select activity.*from activity

    join users on

    users.id = activity.user_id

    and users.date = activity.date

    )​

    cohort_active_user_count Tính toán tổng số người dùng đang hoạt động - mẫu số trong tính toán lưu giữ trong mỗi nhóm hàng ngày:
    , cohort_active_user_count as (

    select

    date, count(distinct user_id) ascount

    from new_user_activity

    groupby1

    )​

    Trên hết, ta sẽ thực hiện một vài thay đổi nhỏ hơn cho truy vấn chính:

    Tính toán thời gian lưu giữ - số ngày giữ lại – như future_activity.date - new_user_activity.date, Và nhóm theo nhóm.Với nhóm này, ta sẽ mất số đơn giản của người dùng đang hoạt động trong nhóm. May mắn thay ta nghĩ về điều này và làm cho cohort_active_user_count Truy vấn phụ, ta có thể tham gia và sử dụng làm mẫu số. Cuối cùng, chúng ta sẽ quấn truy vấn vào một truy vấn bên ngoài tạo ra kết xuất đẹp, loại trừ các hàng và hàng giả mạo.
    select date, 'Day '|| to_char(period, 'DD') as period,

    new_users, retained_users, retention

    from (

    select

    new_user_activity.date as date,

    (future_activity.date

    - new_user_activity.date) as period,

    max(cohort_size.count) as new_users, -- all equal in group

    count(distinct future_activity.user_id) as retained_users,

    count(distinct future_activity.user_id) /

    max(cohort_size.count)::float as retention

    from new_user_activity

    leftjoin activity as future_activity on

    new_user_activity.user_id = future_activity.user_id

    and new_user_activity.date < future_activity.date

    and (new_user_activity.date + interval '10 days')

    >= future_activity.date

    leftjoin cohort_active_user_count as cohort_size on

    new_user_activity.date = cohort_size.date

    groupby1, 2) t

    where period isnotnull

    orderby date, period​

    Chú ý đến phạm vi Join, Một trong những thủ thuật SQL yêu thích của tôi, để có được nhiều ngày duy trì trong một biểu đồ.
    VD:
    upload_2017-8-11_8-29-53.png
    Với Periscope, chúng ta có thể tự động xoay kết quả rồi tô màu theo phần trăm:
    upload_2017-8-11_8-30-12.png
    Making retention more specific
    Nhớ cách lưu giữ người dùng mới và người dùng hiện tại khác nhau như thế nào? Bạn có thể thấy các biến thể giống nhau trên nhiều phân đoạn người dùng. Cần vượt qua sự lưu giữ bằng cách: Nhân khẩu học, nội dung trả lời của người dùng, Thu toán thanh toán với người dùng không phải trả tiền, các loại hoạt động khác nhau - xem, tạo, mua, v.v ...






     
  2. Loading...


Share This Page