SQL 실무 : DAU 및 신규, 복귀, 기존 유저 구별하기

 

오늘은 고객의 로그인 기록을 통해 데이터 분석을 해보도록 하겠습니다. 아래와 같이 가상의 모바일 게임 로그인 기록 데이터를 가지고 우리는 DAU(Daily Active User)의 숫자와 고객이 신규 유저인지, 기존 유저인지, 아니면 복귀 유저인지 파악해보고, 마지막으로 고객 세그먼트별 DAU를 계산한 뒤 시각화까지 해보겠습니다.

 

데이터는 보시는 것처럼 일자별로 게임에 로그인 한 유저 ID 정보를 보여주고 있는 가상의 데이터 입니다. 데이터는 8월과 9월, 약 두달 간의 데이터가 있다고 가정하도록 하겠습니다.

 

log_date	app_name	user_id
2013-08-01	game-01	33754
2013-08-01	game-01	28598
2013-08-01	game-01	117
2013-08-01	game-01	6605
2013-08-01	game-01	346
2013-08-01	game-01	123
2013-08-01	game-01	8122
2013-08-01	game-01	31687
2013-08-01	game-01	33764
2013-08-01	game-01	30994

 

1. DAU 계산하기

 

첫 번째로 데일리 액티브 유저가 얼마나 되는지 확인해보도록 하겠습니다. 한가지 주의할 사항은 회사별로 DAU를 구하는 기준은 다를 수 있다는 점입니다. 예를 들어 모바일 게임 같은 경우에 고객이 일주일에 한번 접속한다면 이 고객은 액티브 유저라고 하기 어려울 수 있습니다. 하지만 쏘카나 그린카와 같은 카셰어링을 이용하는 고객이 일주일에 한번씩 정기적으로 차량을 이용한다고 하면 이 고객은 액티브 유저라고 볼 수 있을 것 입니다. 

 

그렇기 때문에 명확한 DAU에 대한 기준이 세워졌다면 DAU를 구하는 것은 크게 어렵지 않습니다. 여기서는 DAU를 일자별 접속 회원수로 잡고 계산을 해보도록 하겠습니다. 계산 시 주의해야 할 부분은 하루에 두번 이상 접속한 유저가 있을 수 있기 때문에 해당 유저의 접속 횟수는 1로 집계해주어야 하며, 이때 DISTINCT 함수를 사용해주어야 합니다.

 

-- 일별 접속자수(DAU)
SELECT log_date, COUNT(DISTINCT user_id) dau
FROM log
GROUP BY log_date;

 

🕒 실행결과

 

 

log_date	dau
2013-08-01	3320
2013-08-02	2631
2013-08-03	2476
：	：
2013-09-28	2134
2013-09-29	1695
2013-09-30	2444

 

2. 신규, 복귀, 기존 유저 구별하기

 

다음으로는 로그 데이터를 이용해 유저별로 신규, 복귀, 기존 유저 여부를 구분해보도록 하겠습니다. 어떤 날짜에 접속한 유저가 신규 유저인지, 아니면 기존 가입 유저인지, 아니면 굉장히 오랜만에 접속한 복귀 유저인지를 판단하기 위해서 윈도우 함수인 LAG(), LEAD() 함수를 사용할 것입니다.

 

LAG(), LEAD() 함수를 잘 모르는 분들을 위해 간단히 설명을 해보도록 하겠습니다. 여기서는 로그인 날짜를 기준으로 데이터를 핸들링하고 있는데요. LAG() 함수는 기준날짜의 이전 로우를 반환하고, LEAD() 함수는 기준날짜의 이후 로우 값을 반환하게 됩니다. 즉, 기준날짜가 8월 3일이라면, LAG() 함수를 통해 반환되는 값은 8월 2일이 되고, LEAD() 함수를 통해 반환되는 값은 8월 4일이 됩니다. 만약, 8월 3일을 기준날짜로 LAG() 함수를 통해 A 유저의 이전 로그인 기록을 반환하라는 쿼리를 실행했을 때, NULL값이 반환된다면 이 유저는 이전 접속 기록이 없으므로 신규 유저라고 볼 수 있을 것입니다. 또한, 이번에는 NULL값이 아닌 8월 1일이 반환 된다면 이 유저는 이틀 만에 접속한 유저라고 볼 수 있겠죠. 

 

그래서, 다음 쿼리를 통해 LAG() 함수를 이용해 마지막 로그인 날짜를 계산해주고, 기준 날짜와 마지막 로그인 날짜의 차이를 이용해 며칠 만에 접속한 유저인지 확인할 수 있습니다. 그리고 마지막 로그인 일자가 없는 유저는 신규유저, 기준일로부터 14일이 지나 다시 로그인 이력이 있는 유저는 복귀유저, 기준일로부터 14일 이후로 접속 이력이 없는 유저는 이탈유저라고 구별해보도록 하겠습니다.

 

-- 접속 데이터 기반 신규, 복귀, 기존 유저 구별하기
SELECT user_id
     , log_date
     , LAG(log_date) OVER (PARTITION BY user_id ORDER BY log_date) last_login
     , DATEDIFF(log_date, LAG(log_date) OVER (PARTITION BY user_id ORDER BY log_date)) comeback_day  -- 며칠 만에 들어왔는지
     , CASE WHEN LAG(log_date) OVER (PARTITION BY user_id ORDER BY log_date) IS NULL THEN 1 ELSE 0 END new_user
     , CASE WHEN DATEDIFF(log_date, LAG(log_date) OVER (PARTITION BY user_id ORDER BY log_date)) > 14 THEN 1 ELSE 0 END AS comback_user
     , CASE WHEN DATEDIFF((LEAD(log_date) OVER (PARTITION BY user_id ORDER BY log_date)), log_date) > 14 THEN 1 ELSE 0 END AS out_user
FROM log;

 

🕒 실행결과

 

user_id	log_date	last_login	comeback_day	new_user	comback_user	out_user
1	2013-09-01			1	0	0
1	2013-09-02	2013-09-01	1	0	0	0
1	2013-09-03	2013-09-02	1	0	0	0
1	2013-09-04	2013-09-03	1	0	0	0
1	2013-09-05	2013-09-04	1	0	0	0
1	2013-09-06	2013-09-05	1	0	0	0
1	2013-09-07	2013-09-06	1	0	0	0
1	2013-09-08	2013-09-07	1	0	0	0
1	2013-09-09	2013-09-08	1	0	0	0
1	2013-09-10	2013-09-09	1	0	0	0
1	2013-09-11	2013-09-10	1	0	0	0
1	2013-09-12	2013-09-11	1	0	0	0
1	2013-09-13	2013-09-12	1	0	0	0
1	2013-09-14	2013-09-13	1	0	0	0
1	2013-09-15	2013-09-14	1	0	0	0
1	2013-09-16	2013-09-15	1	0	0	0
1	2013-09-17	2013-09-16	1	0	0	0
1	2013-09-18	2013-09-17	1	0	0	0
1	2013-09-19	2013-09-18	1	0	0	0
1	2013-09-20	2013-09-19	1	0	0	0
1	2013-09-21	2013-09-20	1	0	0	0
1	2013-09-22	2013-09-21	1	0	0	0
1	2013-09-23	2013-09-22	1	0	0	0
1	2013-09-24	2013-09-23	1	0	0	0
1	2013-09-25	2013-09-24	1	0	0	0
1	2013-09-26	2013-09-25	1	0	0	0
1	2013-09-27	2013-09-26	1	0	0	0
1	2013-09-28	2013-09-27	1	0	0	0
1	2013-09-30	2013-09-28	2	0	0	0
2	2013-08-07			1	0	0
2	2013-08-08	2013-08-07	1	0	0	0
2	2013-08-17	2013-08-08	9	0	0	0
2	2013-08-21	2013-08-17	4	0	0	1
2	2013-09-17	2013-08-21	27	0	1	0
2	2013-09-24	2013-09-17	7	0	0	0
2	2013-09-26	2013-09-24	2	0	0	0
2	2013-09-30	2013-09-26	4	0	0	0

실행결과를 보면 로그인 아이디가 '1'인 유저는 꾸준하게 지속적으로 접속을 한 기존 유저라고 볼 수 있을 것이고, '2'인 유저는 8월 17일을 기점으로 약 27일 동안 접속을 하지 않았다가 오랜만에 접속한 복귀 유저라고 판단할 수 있을 것입니다.

 

3. 세그먼트별 DAU 계산하기

 

마자막으로 처음에 구했던 DAU를 쪼개서 이번에는 세그먼트별 DAU를 계산해보도록 하겠습니다. 즉, 일자별로 신규유저의 유입 수, 기존유저의 유입 수를 나눠서 구분해보는 것입니다. 쿼리는 아래와 같이 위에서 구해두었던 신규/복귀/기존 유저 정보를 가지고 GROUP BY()를 통해 일자별로 각각 몇명이 유입되었는지 확인할 수 있습니다.

 

-- 세그먼트별 DAU
WITH dau AS(
SELECT user_id
     , log_date
     , LAG(log_date) OVER (PARTITION BY user_id ORDER BY log_date) last_login
     , DATEDIFF(log_date, LAG(log_date) OVER (PARTITION BY user_id ORDER BY log_date)) comeback_day  -- 며칠 만에 들어왔는지
     , CASE WHEN LAG(log_date) OVER (PARTITION BY user_id ORDER BY log_date) IS NULL THEN 1 ELSE 0 END new_user
     , CASE WHEN DATEDIFF(log_date, LAG(log_date) OVER (PARTITION BY user_id ORDER BY log_date)) > 14 THEN 1 ELSE 0 END AS comback_user
     , CASE WHEN DATEDIFF((LEAD(log_date) OVER (PARTITION BY user_id ORDER BY log_date)), log_date) > 14 THEN 1 ELSE 0 END AS out_user
FROM log
) 
SELECT log_date, new_user, comback_user, count(distinct user_id) daily_active_user
FROM dau
GROUP BY log_date, new_user, comback_user, out_user

 

🕒 실행결과

 

log_date	new_user	comback_user	daily_active_user
2013-08-01	1	0	3320
2013-08-02	0	0	1622
2013-08-02	1	0	1009
2013-08-03	0	0	1754
2013-08-03	1	0	722
2013-08-04	0	0	1860
2013-08-04	1	0	574

 

8월 1일부터 서비스가 오픈했기 때문에 당일에는 복귀 유저는 없고 오직 신규 유저만 있습니다. 8월 2일에는 기존에 가입한 유저 1622명이 다시 접속을 했고, 새로 가입한 1009명이 추가로 접속했다고 이해하면 됩니다. 이 결과를 시각화로 나타내면 다음과 같습니다.

 

 

해당 데이터는 가상 데이터이기 때문에 정확한 데이터 분석을 해볼 수는 없습니다. 하지만 이러한 시각화를 통해 다음과 같은 생각은 해볼 수 있을 것입니다. 신규 유저가 줄고 있다면 왜 그런 것일까? 복귀 유저가 조금씩 늘어나고 있는 것 같은데 복귀 유저 프로모션이 효과가 있는 것일까? 기존 유저의 이탈을 막으려면 어떻게 해야할까? 등 다양한 가설을 세워서 데이터 분석을 해볼 수 있습니다.

 

 

작성된 내용 중 잘못된 부분이나 궁금한 사항이 있다면 언제든지 피드백 부탁 드립니다.

 

감사합니다.