사용자 분석 - 리텐션에 영향을 주는 액션 구하기

우리 서비스의 주간 리텐션을 알고 싶어요
리텐션을 높이려면 어떻게 해야 하나요?

문제 정의하기

리텐션은 고객이 유지되는 비율로 잔존율이라고도 표현한다. 사용자가 서비스를 지속적으로 이용하는데에는 다양한 요인이 복합적으로 작용하므로 단순히 기능 추가나 UI 개선으로 리텐션 개선의 본질적인 문제를 해결할 수 있다고 보기 어렵다. 따라서 리텐션에 긍정적/부정적 영향을 주는 요인을 강화/제거하는 방향으로 접근하는 것이 바람직하다.

가령 14일 차의 리텐션을 개선하고 싶다면 7일 차의 리텐션 판정 기간 동안 사용자가 어떠한 액션을 했는지 조사한다. 리텐션에 영향을 주는 액션의 사용률이 낮다면 사용자들이 해당 액션을 할 수 있게 설명을 추가하거나 이벤트를 통해 액션 사용을 촉진하고 사용자 시나리오와 동선 등도 함께 검토한다.

필요한 데이터

유저 데이터(유저id, 가입일자), 활동 로그 데이터(세션id, 유저id, 이벤트명, 발생일시)

[Step 1] 리텐션 구하기

1) 리텐션 마스터 테이블 생성 (repeat_interval)

with repeat_interval as (
    select '7 day retention' as index_name, 1 as interval_begin_date, 7 as interval_end_date
    union all select '14 day retention' as index_name, 8 as interval_begin_date, 14 as interval_end_date
    union all select '21 day retention' as index_name, 15 as interval_begin_date, 21 as interval_end_date
    union all select '28 day retention' as index_name, 22 as interval_begin_date, 28 as interval_end_date
)

select *
from repeat_interval

✔ 리텐션 집계 시작(interval_begin_date)과 종료(interval_end_date) 구간 정의

2) 리텐션 구간-로그 테이블 생성 (action_log_with_index_date)

, action_log_with_index_date as (
select
    u.user_id,
    u.register_date,
    date(a.stamp) as action_date,
    max(date(a.stamp)) over () as latest_date,
    DATE_ADD(u.register_date, INTERVAL r.interval_begin_date day) as index_begin_date,
    DATE_ADD(u.register_date, INTERVAL r.interval_end_date day) as index_end_date,
    r.index_name
from mst_users as u left outer join action_log as a on u.user_id = a.user_id cross join repeat_interval as r
)

✔ 유저와 액션 로그, 리텐션 마스터 테이블 크로스조인 하기
✔ 가입일자를 기준으로 한 리텐션 구간과 액션 로그의 조합

3) 액션 플래그 테이블 생성 (user_action_flag)

, user_action_flag as (
select
    user_id,
    register_date,
    action_date,
    index_begin_date,
    index_end_date,
    index_name,
    sign(sum(case when index_end_date <= latest_date then
    		case when action_date between index_begin_date and index_end_date then 1 
                else 0 end 
                end)
	) as index_date_action
from action_log_with_index_date
group by user_id, register_date, index_name, index_begin_date, index_end_date
)

✔ 리텐션 구간 내에 action이 발생했으면 1, 아니면 0으로 플래그(index_date_action)

4) 리텐션 구하기 (retention_rate)

with repeat_interval as (
-- 리텐션 마스터 테이블 생략
)
, action_log_with_index_date as (
-- 리텐션 마스터-로그 테이블 생략
)
, user_action_flag as (
-- 액션 플래그 테이블 생략
)
select
    register_date,
    index_name,
    avg(100 * index_date_action) as retention_rate
from user_action_flag
group by register_date, index_name
order by register_date, index_name

✔ 가입일자별 리텐션 비율(7 day, 14 day, 21 day, 28 day)

---

[Step 2] 액션별 리텐션 구하기

1) 액션 마스터 테이블 생성 (mst_actions)

, mst_actions as (
    select 'view' as action
    union all select 'comment' as action
    union all select 'follow' as action
)

2) 유저-액션 테이블 생성 (mst_user_actions)

, mst_user_actions as (
    select
    	u.user_id,
        u.register_date,
        a.action
    from mst_users as u cross join mst_actions as a
)

✔ 유저와 액션 마스터 테이블 크로스조인 하기

3) 액션 플래그 테이블 생성 (register_action_flag)

with repeat_interval as (
-- 리텐션 마스터 테이블 생략
)
, action_log_with_index_date as (
-- 리텐션 마스터-로그 테이블 생략
)
, user_action_flag as (
-- 액션 플래그 테이블 생략
)
, mst_actions as (
-- 액션 마스터 테이블 생략
)
, mst_user_actions as (
-- 유저-액션 테이블 생략
)
, register_action_flag as (
    select 
        distinct m.user_id,
        m.register_date,
        m.action,
        (case when a.action is not null then 1
        else 0 end) as do_action,
        index_name,
        index_date_action
    from mst_user_actions as m left join action_log as a on m.user_id = a.user_id and m.register_date = date(a.stamp) and m.action = a.action
    left join user_action_flag as f on m.user_id = f.user_id
    where f.index_date_action is not null
)

✔ 리텐션 구간 내에 action이 발생했으면 1, 아니면 0으로 플래그(index_date_action)

4) 액션별 리텐션(retention_rate)

with repeat_interval as (
-- 리텐션 마스터 테이블 생략
)
, action_log_with_index_date as (
-- 리텐션 마스터-로그 테이블 생략
)
, user_action_flag as (
-- 액션 플래그 테이블 생략
)
, mst_actions as (
-- 액션 마스터 테이블 생략
)
, mst_user_actions as (
-- 유저-액션 테이블 생략
)
, register_action_flag as (
-- 액션 플래그 테이블 생략
)
select
    action,
    count(1) as users,
    avg(100 * do_action) as usage_rate,
    index_name,
    avg(case do_action when 1 then 100 * index_date_action end) as retention_rate,
    avg(case do_action when 0 then 100 * index_date_action end) as no_action_retention_rate
from register_action_flag
group by index_name, action
order by index_name, action

해석

특정 액션의 실행이 리텐션 상승으로 이어질 것으로 보여도, 해당 액션을 실행하는 진입 장벽이 높다면 리텐션에 영향을 조금 주더라도 액션을 실행하는 진입 장벽이 낮은 액션을 기반으로 대책을 세우는 것이 좋다.(ex. 동영상 업로드보다는 이미지 업로드 촉진하기)

데이터 출처: 데이터 분석을 위한 SQL 레시피