[SQL] 19. 많이 쓰이는 분석 프레임(2) - 코호트 분석

시간이 흐르면서 어떻게 바뀌고 있을까? 코호트 분석

코호트 분석 (Cohort Analysis)

• 시간 흐름에 따라 growth와 retention을 측정하는 방법
• 나누어진 그룹을 시간에 따라 지표의 변화를 측정
• Time 코호트 : 실무에서 가장 많이 적용되는 방법
  • 제품이나 서비스를 사용한 시기에 따라 고객을 그룹화
• Behavior 코호트
• Size 코호트

 

TIME 코호트

코호트란? (가입)

• 그룹1 : 1월에 가입한 사람들
• 그룹2 : 2월에 가입한 사람들
• 그룹3 : 3월에 가입한 사람들

코호트란? (구매)

• 그룹1 : 1월에 첫 구매한 사람들 (2월, 3월, 4월... 계속 구매하는지 살핌)
• 그룹2 : 2월에 첫 구매한 사람들 (3월, 4월, 5월... 계속 구매하는지 살핌)
• 그룹3 : 3월에 첫 구매한 사람들 (4월, 5월, 6월... 계속 구매하는지 살핌)
• 주차별, 분기별...

코호트 분석

• 코호트 : 앱을 설치한 일자
• 코호트인덱스 : 앱을 설치하고 얼마나 지났는지
• 지표(메트릭) : 액티브 유저의 리텐션 (구매고객 비율) - 구매 금액을 보기도 한다
• 위 : 추적한지 오래된 그룹
• 아래 : 최근부터 추적한 그룹
• 삼각형모양, 대각선으로 같은 날짜
• 대각선 바깥쪽이 가장 최신 데이터, 안쪽으로 갈수록 과거의 데이터

코호트 분석으로 알 수 있는 것

가로 : 리텐션 over 유저의 라이프타임

• DAY 1~3 = 계속해서 감소, DAY 4~7 = 역시 계속 감소, DAY 8~10 = 조금 덜 감소
• ex) 커플 채팅앱 비트윈 -> 유저들이 3일까지는 채팅을 계속해야 진성유저가 되더라, 3일 연속 접속을 하면 이모티콘을주는 부상체계로 리텐션을 높인다
• 리텐션의 값이 대략적으로 +-10%면 유의미한 값이라고 볼 수 있다

세로 : 리텐션 over 프로덕트 라이프사이클

• 아래로 내려올수록 더 최근 비즈니스 상황을 보여줌
• 리텐션 수치가 떨어지고 있다면 좋지 못한 상황

 

코호트 분석 내 업무에 적용하기

내 비즈니스 특성에 맞는 코호트 주기를 정하는 것이 중요하다

• 앱설치 : daily
• 여행업 : 3M, 6M
• 구독형 (넷플릭스) : Monthly
• 요일별 사용패턴이 있는 비즈니스 : Weekly

 

구매 데이터를 활용하여 코호트 매트릭스 만들어보기

• 가장 먼저 코호트의 기간 기준을 어떻게 정할 것인지가 매우 중요하다

SELECT customer_id, DATE(MIN(invoice_date)) AS cohort_day
FROM data.sales
GROUP BY customer_id

WITH를 사용해서 위 서브쿼리 결과를 저장해준다.

WITH first_purchase AS (
  SELECT customer_id, DATE(MIN(invoice_date)) AS cohort_day
  FROM data.sales
  GROUP BY customer_id
)

SELECT s.*, f.cohort_day
FROM data.sales s LEFT JOIN first_purchase f
ON s.customer_id = f.customer_id

 

 

sales테이블에 첫구매일인 cohort_day가 붙여지게 된다.

• DATE_DIFF : 날짜의 차이를 구하는 함수

WITH first_purchase AS (
  SELECT customer_id, DATE(MIN(invoice_date)) AS cohort_day
  FROM data.sales
  GROUP BY customer_id
)

SELECT s.*, f.cohort_day, DATE_DIFF(DATE(invoice_date), cohort_day, MONTH) AS cohort_index
FROM data.sales s LEFT JOIN first_purchase f
ON s.customer_id = f.customer_id

• DATE_TRUNC : 지정한 기준에 따라 날짜를 잘라주는 함수

WITH first_purchase AS (
  SELECT customer_id, DATE(MIN(invoice_date)) AS cohort_day
  FROM data.sales
  GROUP BY customer_id
)

SELECT s.*, f.cohort_day, DATE_DIFF(DATE(invoice_date), cohort_day, MONTH) AS cohort_index, DATE_TRUNC(cohort_day, MONTH) AS cohort_group
FROM data.sales s LEFT JOIN first_purchase f
ON s.customer_id = f.customer_id

이제 위 테이블을 서브쿼리 삼아 cohort 그룹별 고객 수 테이블을 구한다.

WITH first_purchase AS (
  SELECT customer_id, DATE(MIN(invoice_date)) AS cohort_day
  FROM data.sales
  GROUP BY customer_id
)


SELECT cohort_group, cohort_index, COUNT(DISTINCT customer_id) AS customer_count
FROM (
  SELECT s.*, f.cohort_day, DATE_DIFF(DATE(invoice_date), cohort_day, MONTH) AS cohort_index, DATE_TRUNC(cohort_day, MONTH) AS cohort_group
  FROM data.sales s LEFT JOIN first_purchase f
  ON s.customer_id = f.customer_id
)
GROUP BY cohort_group, cohort_index

엑셀의 피벗 테이블을 이용하여 위 테이블을 정리하여 리텐션을 살펴본다.

• 첫 달에는 많이 떨어질 수 있다.
• 그 이후에 얼마나 안정적으로 가냐가 중요하다.
• 이 비즈니스는 첫 달 이후로 재구매 비율이 안정적으로 유지되고 있다. (긍정적인 케이스) - 유저 라이프타임
  • 종합적으로 봤을때, 유저의 리텐션이 건강하게 이어지고 있다.
• 프로덕트 라이프사이클도 크게 안좋아지는 것이 없다. - 프로덕트 라이프사이클
  • 하지만 2010.12.1. 코호트 그룹이 다른 그룹보다 높은 것으로 보아 어떤 이벤트 등을 통해 건강한 유저를 잘 데려왔다고 볼 수 있다.
  • 즉, 코호트 분석은 이벤트 등이 특정 기간의 분석에도 용이하다