사용자 분석 - MAU 구하기

우리 서비스의 MAU를 알고 싶어요
어떻게 그 수치가 나오게 되었는지 설명해주세요

문제 정의하기

MAU 숫자만 봤을 때는 개선을 위한 전략이나 액션 아이템을 도출하기 어렵기 때문에 MAU를 구성하는 사용자의 유형을 나누어 봐야 한다.

MAU(월간 활성 사용자)는 다음의 케이스들로 이루어져 있다.
✔ 신규 사용자: 이번 달에 처음 사용한 경우
✔ 재사용자: 지난 달에도 사용하고 이번 달에도 사용한 경우
✔ 컴백사용자: 지난 달에 사용하지 않았고 이번 달에 사용했으나 이번 달 첫 사용이 아닌 경우

필요한 데이터

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

풀이

with monthly_user_action as (
select 
    u.user_id,
    DATE_FORMAT(u.register_date, '%Y-%m') as register_month,
    DATE_FORMAT(a.stamp, '%Y-%m') as action_month,
    date_Add(DATE_FORMAT(a.stamp, '%Y-%m'), interval -1 month) as action_month_pre
from mst_users as u join action_log as a on u.user_id = a.user_id
)
, monthly_user_with_type as (
select
    action_month,
    user_id,
    case 
    	when register_month = action_month then 'new'
        when action_month_pre = lag(action_month) over (partition by user_id order by action_month) then 'repeat'
        else 'comeback' end as C
from monthly_user_action
)
select
    action_month,
    count(user_id) as mau,
    count(case when C = 'new' then 1 end) as new_user,
    count(case when C = 'repeat' then 1 end) as repeat_user,
    count(case when C = 'comeback' then 1 end) as comeback_user
from monthly_user_with_type
group by action_month
order by action_month

✔ register_month 와 action_month가 일치하면 신규 사용자
✔ 이전 월에도 액션이 있다면 재사용자 (action_month -1 = lag(action_month))

action_month	mau	new_user	repeat_user	comeback_user
2016-01	3421	325	65	0
2016-02	2154	432	243	0

해석

이번 달의 MAU는 NNN이다. 그 중 이번 달에 신규 유입된 사용자는 NNN으로 NN%를 차지하고 재사용자는 NN%이다. 남은 NN%는 컴백 사용자로 구성된다.

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