[SQL 뉴비 가이드북] 1.로그인 로그에서 SQL로 리텐션 뽑기

SQL 뉴비 가이드북의 첫 탄! 내가 첫 회사 입사 후 맡은 첫 업무였기도 하고, SELECT FROM WHERE JOIN만 알던 나에게 지옥을 선사해준 리텐션이다. 해보기 전엔 어떻게 하는지 감이 안오지만, 차근차근 따라오면 정말 쉽게 뽑을 수 있다.

 

그럼 함께 지표의 아주 아주 기본이 되는 리텐션을 로그인 로그를 바탕으로 뽑아보자. SQL은 모두 빅쿼리 기준!

0.리텐션 

고객 유지로, 고객이나 사용자가 특정 기간 동안 어떤 제품이나 서비스에 계속 관여하거나 이용하는 비율이다. 

https://docs-kr.hackle.io/docs/retention-analysis

이걸 보면 이해하기가 쉬운데, 첫줄 기준으로 모든 기간에 유입한 유저들이 Day0에는 41만명이었으나, 하루가 지나면 그 중 16%인 6만명, 이틀이 지나면 첫 41만명의 11%인 약 5만명이 잔존해있다는걸 보기 쉽게 표기한 것이다. 

 

데이터 분석가 직무를 시작한다면, 아마 무조건 뽑게 될 것이다! 그럼 이 리텐션, 로그인 데이터로 어떻게 만들까? 

 

1.원 테이블 및 최종 만들어야 할 테이블 

 로그인 로그만 있어도 사실 가입 정보를 뽑는건 문제가 되지 않아 가입자 테이블이 따로 없는 곳도 분명 있을 것이다. 

이번 경우에는 회원가입 테이블이 있다 가정하지만, 만약 없는 곳이라면 신규 가입을 식별할 수 있는 컬럼을 활용하거나, 로그인 중 최초 로그인 자료를 활용하면 될 것 같다. 

 

그래서 오늘 사용할 두 테이블의 예시는 이렇게! 

signup_log

 

login_log

 

당연히 login_log에 signup_log에 찍힌 로그인 기록도 있을 것이다.

아무튼 이렇게 생긴 두 테이블을 가지고 아래와 같이 만들 것이다. 결과물을 먼저 올리는 이유는 답을 보기 전에 짜기 전에 어떻게 짤지 구상할 수 있도록 ! 혼자 고민해본 뒤 아래 정답을 보도록 합시당. 

1.일반 버전 (보통 BI에 올릴 땐 이렇게 만든다)

2.PIVOT 버전, BI툴의 성능이 안좋을 때.... (ex.metabaseㅎ)

 

 

*이 작성 계획에서 최적화는 따로 고려하지 않았다. Partitioning 이라던지 샤딩이라던지 사용할 테이블만 미리 선언해준다던지하는 최적화 하는 방식이 각양각색이기 때문... 공통적인 Rule이 있기는 하지만 이부분은 재직중인 회사의 그라운드 룰을 따르는게 나을 것 같기도 하고 뉴비 가이드북에서 최적화를 말하기엔 너무 이른 것 같아서 제외하였으니 참고! 

 

2.SQL 쿼리 작성 

*쿼리 작성 계획

원 테이블을 가지고 아래처럼 만들려면 어떻게 해야할까? 

1.signup_log와 login_log를 account_idx 기준으로 JOIN 시켜서 두 테이블을 결합시켜준다.

2.가입 날짜와 로그인 날짜가 같이 나오면 일자별 차이를 구해준다. 

3.집계함수를 통해 인원수를 계산하고, 이 후 그 값을 활용해 retetion 비율을 구해준다. 

4.빅쿼리에서  PIVOT을 돌릴 순 있지만, 여기서는 따로 진행하지는 않는다. PIVOT 관련 게시물은 따로 올릴예정 

 

(0) 데이터 형식 변환 및 중복제거 

일단, 원 테이블은 time 컬럼들이 모두 DATETIME 형식 (DATE+TIME) 으로 되어 있다. 우리는 Time 까지는 필요 없기 때문에 날짜만 뽑아 오도록 하자. 또한 실제 로그들에는 여러 컬럼이 있을 테니 사용할 컬럼만 select 해와야한다. 

 

1.signup_log 테이블 형식 변환  

SELECT account_idx,DATE(log_time) AS signup_dt
  FROM signup_log

signup_log / log_time을 signup_dt 봐주세요

 

*signup은 id당 1번 찍히는게 원칙이기 때문에 중복제거를 따로 하진 않았다. 다만 log에 문제가 있을 수도 있으니 해줘서 나쁠건 X

 

 

2.login_log 테이블 형식 변환 및 중복제거 

SELECT DISTINCT account_idx,DATE(log_time) AS login_dt
  FROM signup_log

login_log / log_time을 login_dt로 봐주세요

*리텐션을 구할 때, 이 유저가 하루에 2번 들어오던 5번 들어오던 100번 들어오던 그건 상관이 없다. 들어 왔는지가 중요하기 때문에 DATE로 형변환 하면서 같이 중복 제거를 해주자. 이 때 DATETIME인 상태로 중복제거를 하게 되면 당연히 time 인자가 달라지기 때문에 DATE로 바꾼 후 중복제거를 해야한다! 

 

 

(1) 테이블  JOIN 후 날짜 차이 계산  

이렇게 테이블 두개의 전처리를 마쳤다. 그럼 이제 각 유저별로 가입날짜와 login 데이터를 JOIN 시켜야한다.

signup_log에 찍힌 애들은 모두 login_log에 남았기 때문에 여기서는 INNER JOIN이나 LEFT JOIN이나 둘다 사용해도 된다. 단 LEFT JOIN 사용시 FROM 절엔 무조건 signup 테이블이 들어가야한다.

 

(기간을 전체로 설정한다면 상관 없지만, 대부분 언제부터 언제까지 가입한 유저를 대상으로 리텐션을 뽑기 때문에, signup에서 DATE 조건을 걸고 LEFT JOIN 사용해주어야 한다) 

 

*account_idx 기준 inner join 시 

WITH signup AS (
SELECT account_idx,DATE(log_time) AS signup_dt
  FROM signup_log
),

login AS (
SELECT DISTINCT account_idx, DATE(log_time) AS login_dt 
  FROM login_log 
)

SELECT s.account_idx, signup_dt,login_dt 
  FROM signup s 
   	JOIN login l ON s.account_idx=l.account_idx

 

*이후 날짜 차이 추가 

WITH signup AS (
SELECT account_idx,DATE(log_time) AS signup_dt
  FROM signup_log
),

login AS (
SELECT DISTINCT account_idx, DATE(log_time) AS login_dt 
  FROM login_log 
)

--DATE_DIFF(date_expression_a, date_expression_b, date_part)
SELECT s.account_idx, signup_dt,login_dt,DATE_DIFF(login_dt,signup_dt,DAY) AS diff
  FROM signup s 
   	JOIN login l ON s.account_idx=l.account_idx

 

 

 

(2) 카운트 해주기 

그럼 이제 가입날짜가 같은 유저들이 가입 후 지난 며칠동안 몇명이 있었는지 세어주자 

WITH signup AS (
SELECT account_idx,DATE(log_time) AS signup_dt
  FROM signup_log
),

login AS (
SELECT DISTINCT account_idx, DATE(log_time) AS login_dt 
  FROM login_log 
)

--DATE_DIFF(date_expression_a, date_expression_b, date_part)
SELECT signup_dt,DATE_DIFF(login_dt,signup_dt,DAY) AS diff,COUNT(s.account_idx) AS cnt
  FROM signup s 
   	JOIN login l ON s.account_idx=l.account_idx
 GROUP BY 1,2  /*첫번째, 두번째 select 해온 컬럼을 기준으로 GROUP BY*/

 

(3) retention 비율 구하기  

이제 수치로는 유저수가 나왔으니, signup_dt별 0일 대비 얼마나 많은 유저들이 남아가는지 비율로 구해주면 된다.

이때는 window function을 사용하면 되는데 signup_dt 별로 가장 많은 cnt 값 구한 후 각 cnt 값과 나누어주면 끝난다. 

 

윈도우 함수가 낯설다면, 소라고동님 게시글이 아주 잘되어있으니 참고하도록 하자! 

[SQL] 윈도우 함수(Window Function)의 소중함을 느껴보자

 

WITH signup AS (
SELECT account_idx,DATE(log_time) AS signup_dt
  FROM signup_log
),

login AS (
SELECT DISTINCT account_idx, DATE(log_time) AS login_dt 
  FROM login_log 
)

SELECT signup_dt,diff,cnt,cnt/MAX(cnt) OVER(PARTITION BY signup_dt) AS retention
  FROM (
    SELECT signup_dt,DATE_DIFF(login_dt,signup_dt,DAY) AS diff,COUNT(s.account_idx) AS cnt
      FROM signup s 
        JOIN login l ON s.account_idx=l.account_idx
     GROUP BY 1,2  /*첫번째, 두번째 select 해온 컬럼을 기준으로 GROUP BY*/
    )

 

그럼 이제 가입날짜 별로 얼마나 많은 유저가 남았는지를 알 수 있다. 

이제 여기서 signup_log를 구매 유저 로그로 바꾸면 첫 구매 날짜별 리텐션을 알 수 있고 

기준을 일별이 아닌 주간으로 바꾸면 주간 리텐션이 되는 그런 원리이다. 

---

리텐션 이니 뭐니 할 땐 정말 어려워 보이는데 사실 단순 산수이고, 딱 한번만 원리를 깨우치면 그렇게 어렵지 않다. 

한번 스스로 따라해보면서 여기선 왜 join을하고, 여기서 어떻게 나누는지 고민해본다면 이제 자유자재로 리텐션을 구할 수 있을 것이다.그럼 오늘도 글 읽어주셔서 감사합니다 !