잔존율

잔존율 

서비스 등록 후 수개월 후에 어느 정도 비율의 사용자가 서비스를 지속해서 사용하고 있는지 보여지는 비율 

 

 

	2016년 1월	2016년 2월	2016년 3월	2016년 4월	2016년 5월	2016년 6월
2016년 1월	100(100%)
2016년 2월	90 (90%)	150(100%)
2016년 3월	80(80%)	120(80%)	120(100%)
2016년 4월	70(70%)	90(60%)	90(75%)	200(100%)
2016년 5월	60(60%)	60(40%)	90(75%)	150(75%)	250(100%)
2016년 6월	50(50%)	30(20%)	30(25%)	100(50%)	125(50%)	220(100%)

[사용자의 잔존율] 

 

다음과 같은 항목에 문제점이 없는지 확인할 수 있다. 

 

1. 이전과 비교해 n개월 후의 잔존율이 내려갔다면?

   - 신규 등록자가 서비스를 사용하기 위한 장벽이 높아지지는 않았는지 확인

2. n 개월 후에 잔존율이 갑자기 낮아지는 경향이 있다면?

   - 서비스의 사용 목적을 달성하는 기간이 예상보다 너무 짧지는 않은지 확인

3. 오래 사용하던 사용자인데도 특정 월을 기준으로 사용하지 않게 되었다면 ? 

   - 사용자가 서비스 내부에서의 경쟁 등으로 빨리 지친 것은 아닌지 확인

 

쿼리환경: bigquery 

 

사용자의 잔존율을 월 단위로 집계하려면 , 알단 사용자의 서비스 등록일로 부터 12개월 후까지의 월을 도출해내기 위한 추가 테이블 필요. 

 

WITH mst_intervals as (
    SELECT *
    FROM UNNEST([0,1,2,3,4,5,6,7,8,9,10,11,12]) AS interval_month 
)

SELECT *
FROM mst_intervals ;

 

사용자 기준월 기준으로  + n개월 컬럼 테이블 생성 

WITH mst_intervals as (
    SELECT *
    FROM UNNEST([0,1,2,3,4,5,6,7,8,9,10,11,12]) AS interval_month 
)
, mst_users_with_index_month as ( 
    SELECT 
     u.user_id 
    ,u.register_date 
    ,date_add(date(timestamp(u.register_date)),interval i.interval_month month) as index_date -- 기준월 + n개월 날짜 
    ,substr(u.register_date,1,7) as register_month  -- 기준월 
    ,substr(cast(date_add(date(timestamp(u.register_date)),interval i.interval_month month) as string),1,7) as index_month -- 기준월 + n개월
    FROM `sqltest-312808.test8.mst_users` as u 
    CROSS JOIN  mst_intervals as i 
)

SELECT *
FROM mst_users_with_index_month 
ORDER BY user_id,register_date,index_date 

 

WITH mst_intervals as (
    SELECT *
    FROM UNNEST([0,1,2,3,4,5,6,7,8,9,10,11,12]) AS interval_month 
)
, mst_users_with_index_month as ( 
    SELECT 
     u.user_id 
    ,u.register_date 
    ,date_add(date(timestamp(u.register_date)),interval i.interval_month month) as index_date -- 기준월 + n개월 날짜 
    ,substr(u.register_date,1,7) as register_month  -- 기준월 
    ,substr(cast(date_add(date(timestamp(u.register_date)),interval i.interval_month month) as string),1,7) as index_month -- 기준월 + n개월
    FROM `sqltest-312808.test8.mst_users` as u 
    CROSS JOIN  mst_intervals as i 
)
,action_log_in_month AS (
    --액션 로그의 날짜에서 월 부분만 추출하기 
    SELECT DISTINCT 
       user_id 
       ,substr(stamp,1,7) AS action_month 
    FROM 
      `sqltest-312808.test8.action_log`
)


SELECT 
   -- 사용자 마스터, 액션로그를 결합한 뒤, 월별로 잔존율 집계하기 
   u.register_month 
   ,u.index_month 
   ,sum(case when a.action_month is not null then 1 else 0 end) as users 
   ,avg(case when a.action_month is not null then 100.0 else 0.0 end) as retension_rate 
 FROM 
   mst_users_with_index_month as u 
  LEFT JOIN 
   action_log_in_month as a  
  ON u.user_id = a.user_id 
  AND u.index_month = a.action_month 
GROUP BY 
 u.register_month,u.index_month  
ORDER BY 
 u.register_month, u.index_month 

 

Reference 

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