SQL - Retention(리텐션) 산출

0. Retention(잔존율)이란 웹 또는 앱에서 특정 시점을 기준으로 일정 시간이 흐른 뒤에도 남아있는, 잔존해 있는 비율을 의미한다. 보통 1 day retention(1일 리텐션)이라 함은 1일 후에도 남아있는 유저의 비율을 의미한다. 실무에서 가입일을 기준으로 유저들의 주(week) 단위 리텐션을 아래의 매트릭스와 같이 관리할 수 있다.

이번 포스팅에는 1일 리텐션을 구해본다.

1. 아래와 같이 게임을 플레이한 유저의 정보를 담고 있는 PLAYS라는 샘플 테이블을 만들자

1 2 3 4 5 6 7

CREATE TABLE PLAYS(id integer, created_at date); INSERT INTO PLAYS VALUES(1, '2019-01-03'); INSERT INTO PLAYS VALUES(2, '2019-01-03'); INSERT INTO PLAYS VALUES(3, '2019-01-03'); INSERT INTO PLAYS VALUES(1, '2019-01-04'); INSERT INTO PLAYS VALUES(2, '2019-01-04');

" SELECT * FROM PLAYS; " 쿼리를 통해 plays 테이블을 아래와 같이 확인할 수 있다.

2. 리텐션을 구하는 방법을 알아보자.

1월 3일의 dau는 3명, 1월 4일의 dau는 2명이다. 따라서 1월 3일 기준 1일 리텐션은 (2/3)*100 = 66%이다. 이 계산 방법을 조건으로 다시 풀어보자
- 일별 dau를 계산해야 한다. dau를 산출하는 쿼리는 "SQL - DAU 산출" 포스팅에서 자세히 확인할 수 있다.
- 1월 3일과 1월 4일을 매칭시킨다. 1월 4일에서 1일을 뺀 1월 3일을 원래 테이블의 1월 3일과 날짜와 유저를 결합시킨다. 그러면 1월 3일의 유저 중 1월 4일에도 잔존해 있는 유저들을 찾을 수 있다. self join(셀프 조인)을 사용한다.
- 1월 3일이 기준으로 분모가 된다. 1월 3일의 dau가 분모이므로 1월 3일을 기준으로 하는 left join을 사용한다. 즉, self join을 left join으로 실행한다.
- 1월 3일의 전체 dau와 1월 3일과 결합된 1월 4일의 dau를 계산한다. 위 두 값을 나눈다.
(1월 3일에도 접속한 유저 중 1월 4일 dau) / (1월 3일 dau) 가 리텐션이 된다.

3. 위 로직을 쿼리로 작성하면 아래와 같다.

1 2 3 4 5 6 7

SELECT DATE(p1.created_at) as date, round(cast(count(distinct p2.id) as float) / cast(count(distinct p1.id) as float), 3) * 100 as retention_1day FROM PLAYS p1 LEFT JOIN PLAYS p2 ON p1.id = p2.id and date(p1.created_at) = date(datetime(p2.created_at, '-1 day')) WHERE date = '2019-01-03' GROUP BY date ;

- ON절의 date(datetime(p2.created_at, '-1 day')) 구문은 p2의 create_at에서 1일을 뺀 값을 date형식으로 구하는 것을 의미한다. p2의 date에서 1일을 뺀 날짜를 p1.의 date와 일치하는 조건으로 조인시켜 p1의 유저 중 1일 후에도 존재하는 유저들을 조인시켜 1일 리텐션을 구할 수 있다. 만약 7일 리텐션을 구하고 싶다면 date(datetime(p2.created_at, '-7 day'))로 바꿔서 7일 후에 잔존하는 유저들을 조인시키면 된다.

- 위 쿼리에서 아래와 같이 LEFT JOIN만 실시해 데이터들이 조인한 모습을 살펴보자.

1 2 3 4 5

SELECT * FROM PLAYS p1 LEFT JOIN PLAYS p2 ON p1.id = p2.id and date(p1.created_at) = date(datetime(p2.created_at, '-1 day')) ;

아래와 같이 데이터가 조인되어 있는 것을 확인할 수 있다.

유저 id가 같고 - p1.id = p2.id - 날짜가 1일 차이가 나는 - date(p1.created_at) = date(datetime(p2.created_at, '-1 day')) - 조건으로 데이터가 left join되었다.

4. 위 테이블을 SELECT절에서 계산하면 아래와 같이 결과를 확인할 수 있다.

끝.