User Movement 지표 쿼리 작성하기
들어가는 글
많은 기업들이 프로덕트 사용자 규모를 확인할 때, DAU, MAU 등과 같은 활성 사용자 수 정도만 모니터링하는 것에서 멈추는 경우가 많습니다. 그러나 DAU, MAU가 이전과 같은 수준을 유지하고 있다고 하더라도, 이를 세분화하여 살펴본다면 프로덕트가 성장하고 있는지, 혹은 퇴보하고 있는지 신호를 파악할 수 있습니다. 이번 아티클에서는 DAU를 좀 더 세분화하여 살펴보기 위한 User Movement 지표의 의미, 그리고 이를 추출하기 위한 쿼리 작성 가이드를 소개하도록 하겠습니다.
User Movement 지표란 무엇인가
User Movement란, DAU나 MAU를 신규 사용자, 복귀 사용자, 기존 사용자, 그리고 이탈 사용자로 세분화하여 표현한 지표를 의미합니다. 특히 Amplitude에서는 이를 Lifecycle Chart 차트 유형으로 제공하고 있습니다.
0. 기본 공식
Day N DAU=Day N 신규 사용자 수+Day N 복귀 사용자 수+Day N 기존 사용자 수Day N 기존 사용자 수=Day N-1 DAU-Day N-1 이탈 사용자 수Day N Quick Ratio= (Day N 신규 사용자 수+Day N 복귀 사용자 수) /Day N 이탈 사용자 수
1. DAU = 신규 사용자 수 + 복귀 사용자 수 + 기존 사용자 수
User Movement는 기본적으로 모든 활성 사용자 수를 신규, 복귀, 기존으로 분류합니다.
신규 사용자
단어 그대로, 프로덕트에 생애 최초로 활성화된 사용자를 의미합니다. BigQuery에 Google Analytics 4 Export Table을 사용하시는 분들이라면, ga_session_number 의 값이나 first_visit 이벤트를 통해 신규 사용자 여부를 쉽게 확인할 수 있습니다.
복귀 사용자
과거에 프로덕트에 활성화된 적이 있지만, 말 그대로 “오랜만에" 재활성화된 사용자를 의미합니다.
하지만 여기에서 난관이 발생합니다. 데이터 분석 관점에서는 “오랜만에" 의 의미를 측정 가능한 형태로 정의를 해야 하는데, 복귀의 기준을 2일, 7일, 30일, 90일 등 어떤 Interval로 두어야 할지 전제를 만들어야 하기 때문입니다.
개인적으로 복귀의 기준은 프로덕트의 자체 특성과 사용자들의 실제 방문 주기를 참고하여 결정해야 한다고 생각합니다. 이를 위해 메신저 앱과 세탁 서비스 앱을 사례로 들어보겠습니다.
- 메신저 앱: 메신저 앱은 도메인 특성상 사용자들을 하루도 빠짐 없이 방문하도록 만들어야 한다는 Goal을 가지기 마련입니다. 메신저 서비스의 사업 매출을 극대화하기 위해서는 인앱 광고가 주 매출원이 되어야 하는데, 광고 노출을 극대화하기 위해서는 매일 습관적으로 방문하는 사용자들이 많아야 하기 때문입니다. 따라서 메신저 앱의 “복귀 사용자"란 최소 2일 만에, 즉 하루 이상을 건너뛰고 방문한 사용자로 정의하는 것이 적절할 것 같습니다.
- 세탁 서비스 앱: 세탁 서비스 앱은 도메인 특성상 사용자들을 매일 방문하도록 만드는 것이 태생적으로 어렵기 마련입니다. 세탁 서비스 사용자들은 1주, 2주, 1개월 등 좀 더 긴 주기를 가지고 이용하기 때문입니다. 따라서 만약 세탁 서비스 앱이 사용자들을 하루도 빠짐 없이 방문하도록 만드는 것을 Goal로 설정할 경우, 사용자들의 행동 패턴이나 Goal 달성 가능성을 고려하지 못한 자원 낭비로 이어지게 될 것입니다. 따라서 세탁 서비스 앱의 “복귀 사용자"란 실제 앱 내 사용자들의 방문 주기의 Median 값을 확인하여 정의하는 것이 바람직합니다. (이상값으로 인한 대표값 왜곡을 방지하고자 Mean 값보다는 Median 값이 적절할 것으로 보입니다.)
기존 사용자
과거에 프로덕트에 활성화된 적이 있고, 복귀 사용자와 달리 “최근까지 계속 활성화된" 사용자를 의미합니다. 즉, 아직 프로덕트에 상주(혹은 잔존)하고 있는 사용자인 것입니다.
2. 기존 사용자 수 = 전일 DAU - 전일 이탈 사용자 수
User Movement에서는 시간이 흐름에 따라 이탈 사용자 수에 의해 기존 사용자 수가 Chain화 됩니다. 아래 그림에서는 이탈의 의미를 2일 Interval로 두었을 때의 Chained Flow를 표현하고 있습니다. 즉, 1일차에 활성화된 사용자들 중 2일차에도 활성화된 사용자를 기존 사용자로 간주하고, 2일차에 활성화되지 않은 사용자들을 이탈된 것으로 보는 것입니다.
이탈 사용자
이탈 사용자는 복귀 사용자의 반대 개념으로 이해할 수 있습니다. 복귀 사용자를 최소 2일 만에 재활성화된 것으로 이해한다면, 이탈 사용자는 2일 미만의 기간 동안 활성화되지 않은 것으로 이해하는 것입니다. (즉, 바로 1일 후에 재활성화되지 않을 경우 “이탈"로 간주하는 것이죠.)
3. Quick Ratio = (신규 사용자 수 + 복귀 사용자 수) / 이탈 사용자 수
Quick Ratio는 프로덕트 규모가 확장되고 있는지 알려주는 지표입니다. 즉, 이탈 사용자 대비 “새롭게 혹은 다시 돌아오는" 사용자들이 얼마나 많은지를 알려주는 것이죠. “욕조의 배수구로 빠져나가는 물에 비해 얼마나 많은 물이 수도꼭지에서 들어오고 있는가”에 빗대어 이해할 수 있습니다.
똑같은 DAU 수준을 유지하고 있다고 하더라도, Quick Ratio의 차이가 발생할 수 있으므로, 단순한 DAU보다 더 많은 정보를 제공할 수 있습니다. 아래 사례의 경우, 3일과 4일의 DAU가 동일한데도 불구하고, Quick Ratio는 크게 상승한 것을 확인할 수 있습니다. 4일차의 이탈 사용자가 크게 감소했기 때문이죠.
User Movement 지표 쿼리 작성하기
이제 User Movement 지표를 추출하기 위해 쿼리문을 작성하는 방법을 Step-by-step으로 설명드리겠습니다.
가정
- DAU를 기반으로 신규 사용자, 복귀 사용자, 기존 사용자, 이탈 사용자 수를 세분화하여 표현한다.
- 1일차의 이탈 사용자 수는 2일차가 되어야 알 수 있는 Lagging 값이다.
- 복귀 사용자는 적어도 2일 후 재활성화된 사용자이다. (≥ 2일)
- 이탈 사용자는 1일 후 활성화가 안된 사용자이다. (< 2일)
STEP 1. `session_start’ 이벤트 테이블에서 날짜, 사용자 ID, 세션 시퀀스 값을 인라인 뷰로 로드합니다.
WITH
CTE_raw AS (
SELECT
date,
user_id,
session_number
FROM
{{SESSION_STARTS_TABLE}}
),session_number의 의미
각 사용자 별로 실행한 세션의 발생 순서를 의미합니다. (1, 2, 3, … Integer 타입)
STEP 2. 각 사용자의 일자별 세션 시퀀스 최솟값을 집계합니다.
CTE_users_min_sn AS (
SELECT
date,
user_id,
MIN(session_number) AS min_sn
FROM
CTE_raw
GROUP BY
date, user_id
),세션 시퀀스 최솟값을 집계하는 이유
동일 날짜에 사용자가 여러 번의 세션을 실행할 수 있습니다. 만약, 홍길동 사용자가 1월 1일에 시퀀스 값이 1인 세션, 2인 세션, 3인 세션까지 총 세 번의 세션을 실행할 경우를 사례로 들어보겠습니다. 이 경우, 1월 1일 홍길동의 min_sn = 1 이고, 이를 바탕으로 홍길동을 1월 1일 “신규 사용자"로 분류할 수 있을 것입니다.
STEP 3. 각 사용자의 일자별 “기존 사용자 여부"와 “이탈 사용자 여부"를 표시합니다.
CTE_users_min_sn_existing_dormant AS (
SELECT
date,
user_id,
min_sn,
CASE
WHEN DATE_DIFF(
date,
LAG(date, 1) OVER (PARTITION BY user_id ORDER BY date), -- 1일 전에 활성화되었는지?
DAY
) = 1 THEN 'existing'
ELSE NULL
END AS is_existing,
CASE
WHEN DATE_DIFF(
LEAD(date, 1) OVER (PARTITION BY user_id ORDER BY date), -- 1일 후에 활성화되었는지?
date,
DAY
) = 1 THEN 'not_dormant'
ELSE NULL
END AS is_dormant
FROM
CTE_users_min_sn
),is_existing 필드의 의미
Window Function인 LAG()를 사용하여 Current Date 이전 기간 중 가장 직전에 활성화되었던 Date 정보를 가져옵니다. 그런 후, DATE_DIFF()를 사용하여 1일 전이었다면 existing(기존 사용자), 그 외에는 NULL로 표시합니다.
is_dormant 필드의 의미
Window Function인 LEAD()를 사용하여 Current Date 이후 기간 중 가장 직후에 활성화되었던 Date 정보를 가져옵니다. 그런 후, DATE_DIFF()를 사용하여 1일 후였다면 not_dormant(이탈 안된 사용자), 그 외에는 NULL로 표시합니다.
STEP 4. min_sn, is_existing, is_dormant 필드를 통해 일자별로 사용자 수를 분류합니다.
CTE_user_movements AS (
SELECT
date,
COUNT(DISTINCT user_id) AS daily_active_users, -- DAU
COUNT(DISTINCT CASE WHEN min_sn = 1 THEN user_id END) AS daily_new_users, -- 신규 사용자
COUNT(DISTINCT CASE WHEN min_sn > 1 AND is_existing IS NULL THEN user_id END) AS daily_resurrected_users, -- 복귀 사용자
COUNT(DISTINCT CASE WHEN min_sn > 1 AND is_existing = 'existing' THEN user_id END) AS daily_existing_users, -- 기존 사용자
COUNT(DISTINCT CASE WHEN is_dormant IS NULL THEN user_id END) AS daily_dormant_users -- 이탈 사용자
FROM
CTE_users_min_sn_existing_dormant
GROUP BY
date
),daily_new_users
해당 일자에 세션 시퀀스 최솟값이 1일 경우, 처음 방문한 것을 의미하므로 신규 사용자로 분류합니다.
daily_resurrected_users
해당 일자에 세션 시퀀스 최솟값이 1이 아니고 기존 사용자도 아니므로 복귀 사용자로 분류합니다.
daily_existing_users
해당 일자에 세션 시퀀스 최솟값이 1이 아니고, 기존 사용자이므로 기존 사용자로 분류합니다.
daily_dormant_users
이탈 안된 여부(not_dormant) 값이 없으므로 이탈 사용자로 분류합니다.
STEP 5. 마지막으로 Quick Ratio를 계산한 후 최종 결과를 출력합니다.
CTE_user_movements_with_quick_ratio AS (
SELECT
*,
(daily_new_users + daily_resurrected_users) / daily_dormant_users AS quick_ratio
FROM
CTE_user_movements
)
SELECT
*
FROM
CTE_user_movements_with_quick_ratio
;quick_ratio의 의미
신규 사용자 수와 복귀 사용자 수의 합을 이탈 사용자 수로 나눈 값입니다. 취향에 따라 Division by Zero 에러를 핸들링하기 위해 SAFE_DIVIDE()를 사용할 수 있고, Division 결과의 반올림 문제를 핸들링하기 위해 CAST()를 사용할 수도 있습니다.
나가는 글
Amplitude, Mixpanel, Google Analytics 등 CRM 데이터 분석 툴을 사용하다보면 익숙해진 탓에 디폴트로 제공하는 지표들을 계산하는 과정이 얼마나 복잡한지 속사정을 의식하기 어렵습니다.
User Movement 지표도 마찬가지입니다. 사용자를 신규, 복귀, 기존, 이탈 유형으로 세분화하는 것이 겉으로는 쉬워보이지만, 그 이면에는 개념을 정의하는 일, 그리고 데이터 마트와 쿼리문을 개발하는 일 등 상당히 복잡하고 난해한 과정들이 숨어 있습니다.
데이터 분석과 엔지니어링도 마찬가지입니다. 마법 처럼 영감을 찾을 수 있을 것만 같지만, 그 전에 많은 개념을 명료하게 정의해야 하고 또 데이터를 명료하게 추출하기 위한 쿼리문을 작성해야 합니다. 앞으로 흥미와 책임감, 두 가지 마인드셋을 모두 갖춘 데이터 담당자가 되기 위해 노력하겠습니다.
