이 “데이터 분석을위한 SQL 레시피”를 참조하십시오. (12강)
1. MAU란?
- Monthly Active User의 약자로, 한 달 동안 서비스를 이용한 순수한 사용자 수를 나타내는 지표를 의미한다.
- MAU는 순수 방문자 수(Unique Visitor)를 나타내기 위해서, 한 명이 그 기간중에 몇번이나 서비스를 이용했다고 해도, 혼자서 집계한다.
- 통상, 게임 등 인터넷 베이스의 서비스로, 그 서비스를 얼마나의 유저가 실제로 이용하고 있는지를 나타내는 지표의 하나로서 활용한다.
그러나 MAU는 월별 사용자 수만 알 수 있으며 어떤 사용자가 서비스를 사용하는지 올바르게 파악할 수 없습니다.
서비스 사용자의 구성을 보다 자세히 파악하기 위해 MAU를 3개의 속성으로 나누어 분석할 수 있다.
2. MAU의 세 가지 속성
- 신규 사용자 -> 분석할 날짜 기준 이달 등록 사용자
- 반복 사용자 -> 분석할 날짜 기준 전월에 사용한 사용자
- 컴백 사용자 -> 분석할 날짜 기준 이번 달도 전월도 사용하지 않았다 잠시 사용하지 않았 사용자
위의 3가지 속성을 활용하여 sql 쿼리를 작성해 본다.
3. MAU 메트릭을 생성하는 SQL 쿼리
1) MAU 내역 substr로 추출한 달를 사용하여 세 가지 속성으로 사용자를 구별할 수 있습니다.
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에 대해 설명했습니다.
그러나 서비스 제공 업체 궁극의 목표는 사용자를 파악하는 대신 판매 및 사용자 수에 대한 지표를 늘리는 것그리고 사용자를 파악하는 것은 목표를 달성하기 위한 수단일 뿐이다.
측정항목을 개선하려면 다음 방법을 사용합니다.
- 달성하고자 하는 지표결정합니다.
- 사용자 행동 중 지표에 영향을 미치는 것으로 보이는 행동결정합니다.
- (2)에서 결정한 행동의 유무와 속성을 집계그리고 (1)에서 결정한 지표를 만족한다.
사용자 비율 비교한다.
앞에서 살펴본 RFM 분석을 예로 들어 보겠습니다.
- RFM 지표 monetary(구입 금액)을 상승하고 싶다.
- 구매 금액이 높은 사용자는 마사지 의자를 구입한 것 같다.
- 마사지 의자 구매한 사용자 집계그리고 이전 조건을 충족 구매 금액 비교한다.
끝.