[SQL] MAU(Monthly Active Users)의 속성을 나누어 분석하기 & 지표 개선 방법(with. Google BigQuery)

책 '데이터 분석을 위한 SQL 레시피'를 참고했습니다. (12강)

 

 

1. MAU란?

• Monthly Active User의 약자로, 한 달 동안 해당 서비스를 이용한 순수 이용자 수를 나타내는 지표를 뜻한다.
• MAU는 순수 방문자 수(Unique Visitor)를 나타내기 때문에 한 명이 해당 기간 동안 여러 번 서비스를 이용했더라도 한 명으로 집계한다.
• 보통 게임 등 인터넷 기반 서비스에서 해당 서비스를 얼마나 많은 사용자가 실제로 이용하고 있는지 나타내는 지표 중 하나로 활용한다.

 

하지만 MAU는 월별 이용자의 수만 알 수 있으며, 어떤 사용자가 서비스를 사용하는지 제대로 파악할 수 없다.

서비스 사용자의 구성을 더 자세히 파악하기 위해 MAU를 3가지 속성으로 나누어 분석할 수 있다.

 

 

2. MAU의 3가지 속성

• 신규 사용자  -> 분석할 날짜 기준 이번 달에 등록한 사용자
• 리피트 사용자 -> 분석할 날짜 기준 이전 달에도 사용했던 사용자
• 컴백 사용자 -> 분석할 날짜 기준 이번 달도, 저번 달도 사용하지 않았던 한동안 사용하지 않았다가 돌아온 사용자

위 3가지 속성을 활용해 sql 쿼리를 작성 해 볼것이다.

 

 

3. MAU 지표를 만드는 SQL 쿼리

 

1) MAU내역을 substr으로 추출한 월을 이용해 3가지 속성으로 사용자를 구별할 수 있다.

 

with
monthly_user_action as (  # 월별 사용자를 집계하는 쿼리
  select distinct u.user_id, # 순수 사용자를 집계하기 때문에 distinct로 중복 제거
    substr(u.register_date,1,7) as register_month, # 가입월 추출
    substr(l.stamp,1,7) as action_month, # 액션 당시 월 추출
    substr(cast(date_sub(date(timestamp(l.stamp)), interval 1 month) as string),1,7) as action_month_priv 
    	#date_add는 날짜만큼 더하기, date_sub는 빼기 (위 기준 1달 빼기{interval 1 month})
      from sql-study-project-376507.sql_study_bigquery.mst_users as u
    inner join
      sql-study-project-376507.sql_study_bigquery.action_log as l
        on u.user_id = l.user_id
)
, monthly_user_with_type as (
  select action_month, user_id, case when register_month = action_month then "new_user" # 액션월과 가입월이 같으면 신규 이용자
                                      when action_month_priv = lag(action_month) over(partition by user_id order by action_month) then "repeat_user"
                                      			# 위 쿼리에서 1달을 뺀 월과 액션 당시 기준 월의 전월과 일치할 시 리피트 사용자
                                      else "come_back_user" end as c
    , action_month_priv
      from monthly_user_action
)
select action_month,count(user_id) as mau, count(case when c = 'new_user' then 1 end) as new_users,
  count(case when c = 'repeat_user' then 1 end) as repeat_users,
  count(case when c= 'come_back_user' then 1 end) as come_back_users
    from monthly_user_with_type
      group by action_month
        order by action_month;

결과는 다음과 같다.

 

2) 리피트 사용자를 3가지 속성으로 분류할 수 있다.

 

• 신규 리피트 사용자 -> 이전 달에는 신규 사용자였으며 / 이번 달에는 리피트 사용자인 경우
• 기존 리피트 사용자 -> 이전 달에는 리피트 사용자였으며 / 이번 달에도 리피트 사용자인 경우
• 컴백 리피트 사용자 -> 이전 달에는 컴백 사용자였으며 / 이번 달에는 리피트 사용자인 경우

이런 식으로 리피트 사용자를 추가로 분류하면 같은 리피트 사용자라도 더 상세하게 사용자를 파악하고 활용할 수 있다.

 

case when 구문을 이용해 쉽게 구분할 수 있다.

 

with
monthly_user_action as (  
 	위와 동일
)
, monthly_user_with_type as (
 	위와 동일
)
, monthly_users as(
  select m1.action_month,count(m1.user_id) as mau,
  count(case when m1.c = 'new_user' then 1 end) as new_users,  
  count(case when m1.c = 'repeat_user' then 1 end) as repeat_users,
  count(case when m1.c= 'come_back_user' then 1 end) as come_back_users,
  count(case when m1.c = 'repeat_user' and m0.c = 'new_user' then 1 end) as new_repeat_users,
  count(case when m1.c = 'repeat_user' and m0.c = 'repeat_user' then 1 end) as continuous_repeat_users,
  count(case when m1.c = 'repeat_user' and m0.c = 'come_back_user' then 1 end) as come_back_repeat_users,
    from monthly_user_with_type as m1
    left outer join		# 같은 테이블을 앨리어스를 다르게 부여하여 결합이 가능하다.
      monthly_user_with_type as m0
        on m1.user_id = m0.user_id
          and m1.action_month_priv = m0.action_month
            group by m1.action_month
)
select * from monthly_users
  order by action_month;

 

 

 

 

 

 

---

 

※ 지표 개선 방법

지금까지는 사용자를  파악하기 위한 리포트 및 SQL에 대해 다뤄보았다.

하지만 서비스 제공자의 궁극적인 목표는 사용자를 파악하는 것이 아닌 매출 또는 사용자 수등 지표를 늘리는 것이며 사용자를 파악하는 것은 목표를 달성하기 위한 수단일 뿐이다.

 

지표를 향상시키기 위해 다음과 같은 방법을 사용한다.

1.  달성하고 싶은 지표를 결정한다.
2. 사용자 행동 중에서 지표에 영향을 많이 줄 것으로 보이는 행동을 결정한다.
3. (2)에서 결정한 행동 여부와 속성들을 집계하고, (1)에서 결정한 지표를 만족하는 사용자의 비율을 비교한다.

 

전에 다루었던 RFM 분석을 예로 예시를 들어보겠다.

1.  RFM 지표중 monetary(구매 금액)를  상승시키고 싶다.
2. 구매 금액이 높은 사용자는 안마의자를 구매했던 것 같다.
3.  안마의자를 구매한 사용자를 집계하고, 앞의 조건을 만족하는 구매 금액을 비교한다.

 

 

 

 

끝.