코호트-리텐션 분석(2) - SQL을 활용하여 코호트 분석하기

코호트-리텐션 분석(1) - 엑셀을 활용하여 코호트 분석하기를 먼저 보면 좋습니다

코호트-리텐션 분석(1) - 엑셀을 활용하여 코호트 분석하기

 

오히려 엑셀... 나쁘지 않을지도?

2. SQL을 활용하여 코호트 분석하기

SQL을 활용한 코호트 분석은 데이터리안 SQL 데이터 분석 캠프 실전반을 수강하며 배운 내용을 응용했다. 강의에서는 sqlsolve US E-commerce Records 2020 데이터를 활용했지만 이번에도 누가, 언제, 무엇을 했는지에 대한 raw 데이터만 있으면 가능하다.

Step 1. Raw 데이터 준비
강의에서는 첫 구매에 대한 데이터가 별도의 테이블로 제공되었는데, 엑셀에서 첫 방문일자를 추출했던 것처럼 SQL로도 할 수 있겠다는 생각이 들었다.

Step 2. 첫 구매일자 추출
※ 아래 나오는 모든 쿼리는 가공된 예시입니다.

With 첫구매데이터 as(
Select min(구매일자) as 첫구매일자, user_id
From 구매내역
Group by user_id
)

Select user_id, 첫구매일자, 구매일자
From 구매내역 left join 첫구매데이터 … // 이하 생략
Order by user_id, 구매일자

첫 구매일자에 대한 데이터를 with 문을 활용하여 별도로 구해놓고, 구매내역 테이블과 join 하면 한 사용자의 로그 중에서 첫 방문일자가 언제인지 알 수 있다. 엑셀에서 첫 방문일자를 구한 것과 동일하게 user_id와 구매일자로 정렬까지 해준다.

Step 3. 첫 구매일자, 구매일자의 월 컬럼 추출

-- with 문 생략

Select 
	user_id, 
	첫구매일자, 
	구매일자, 
	date_format(첫구매일자, ‘%-Y-%m’) 첫구매월, 
	date_format(구매일자, ‘%-Y-%m’) 구매월
From 구매내역 left join 첫구매데이터 … // 이하 생략
Order by user_id, 구매일자

이 부분이 엑셀과 약간 다른 부분인데 SQL에서는 중복 제거된 사용자수를 구하기 위해 추출했던 CHK, END_CHK 컬럼을 따로 구할 필요가 없기 때문이다. 코호트별 사용자수나 시간의 흐름에 따라 변화하는 사용자수 모두 distinct count 해주기만 하면 된다.

Step 4-1. 코호트별 사용자수 구하기

-- with 문 생략

Select 
	date_format(첫구매일자, ‘%-Y-%m’) 첫구매월, 
	count(distinct user_id) month0
From 구매내역 left join 첫구매데이터 … // 이하 생략
Order by user_id, 구매일자

앞서 언급한대로 코호트별 사용자수는 user_id 를 distinct count 해준다.

Step 4-2. 시간의 흐름에 따라 변화하는 사용자수 구하기

-- with 문 생략

Select 
	date_format(첫구매일자, ‘%-Y-%m’) 첫구매월, 
	count(distinct user_id) month0,
	count(distinct case when date_add(첫구매월, interval 1 month) = 구매월 then user_id end) month1
From 구매내역 left join 첫구매데이터 … // 이하 생략
Order by user_id, 구매일자

이 부분이 핵심인데 조건문을 사용해서 첫구매월로부터 한 달의 시간이 지난 값과 구매월의 값이 같은지 판단하고, 맞으면 user_id를 distinct count 해준다.
엑셀에서 sumifs문을 사용했던 “코호트 리스트에서 cohort1,2,3..에 해당하는 코호트를 찾고, age_by_month 리스트에서 1,2,3…에 해당하는 값을 찾아 사용자 수를 합해주는” 행위를 SQL에서는 조건문을 활용해서 값을 찾고 더해주는 것이다!
이후로는 두 달, 세 달…의 시간이 지난 값을 구하기 위해 반복 작성하면 된다.

-- with 문 생략

Select 
	date_format(첫구매일자, ‘%-Y-%m’) 첫구매월, count(distinct user_id) month0,
	count(distinct case when date_add(첫구매월, interval 1 month) = 구매월 then user_id end) month1
	count(distinct case when date_add(첫구매월, interval 2 month) = 구매월 then user_id end) month2
	count(distinct case when date_add(첫구매월, interval 3 month) = 구매월 then user_id end) month3

	… // 이하 생략

From 구매내역 left join 첫구매데이터 … // 이하 생략
Order by user_id, 구매일자

 

리텐션 구하기

-- with 문 생략

Select 
	date_format(첫구매일자, ‘%-Y-%m’) 첫구매월, count(distinct user_id) month0,
	Round(count(distinct case when date_add(첫구매월, interval 1 month) = 구매월 then user_id end) / count(distinct user_id, 0)

	… // 이하 생략

From 구매내역 left join 첫구매데이터 … // 이하 생략
Order by user_id, 구매일자

SQL에서도 엑셀과 마찬가지로 리텐션을 구하기 위해 시간의 흐름에 따라 변화하는 사용자수를 코호트별 사용자수로 나누고 Round 함수로 소수점 처리까지 해주면 리텐션 비율을 구할 수 있다.
아래 이미지 역시 블러처리 하였지만 위쪽 테이블이 사용자수, 아래쪽 테이블이 리텐션 비율로 계산한 결과이다.

출처: 데이터리안

SQL 코호트 분석의 장점

훨씬 안정적이고 품이 덜 든다. 특히 반복 작성해야 하는 부분만 제외하면 작성해야 하는 내용도 많지 않다. 숙지해야 할 내용은 with문으로 전처리 데이터 준비하기, 조건문으로 첫구매월과 구매월 비교하기, date_add 함수 활용하기 정도이다. 데이터량이 많아져도 raw 데이터 자체를 관리할 필요는 없어서 안정적이라고 느꼈다.

SQL 코호트 분석의 단점

반복 작성해야 하는 부분에서 필요하다면 쿼리 개선을 할 수도 있겠지만 현재로서는 굳이... 오히려 이후로는 기본적인 코호트 분석을 바탕으로 특정 그룹의 사용자들을 대상의 분석을 들어가야 하는 단계라는 생각이 든다.

---

번외

앞서 엑셀과 SQL로 분석한 내용을 응용해서 주별 코호트-리텐션 분석도 가능한데 (이미 엑셀로는 시도해봄)
주는 월과 다르게 기준이 없기 때문에(ex. 2022년 기준 8월 5주차 = 9월 1주차와 같이 중첩되는 기간) 코호트를 정의하기 어려웠다. 그래서 코호트를 1주차에서 52주차로 정의하는 것도 방법이다.

본 내용은 데이터리안 'SQL 데이터 분석 캠프 실전반' 을 수강하며 작성한 내용입니다.