[열정페이 - SQL] 4장 코호트 분석 정리(리텐션 분석)

코호트 분석이란?

코호트(cohort) : 시작하는 시간 기준으로 동일한 특징을 지닌 집단(사람, 회사 등등)

 

코호트라는 단어는 코로나가 유행할때 코호트 격리 때문에 어느정도 익숙한 단어이다.

코호트 격리 사례

 

 

• 코호트 분석: 특정 행동(구매, 회원가입, 사이트 방문 등)에 기반해 그룹을 나누고 그 행동의 변화를 분석.
• 코호트 격리: 특정 상황(감염, 질병 노출 등)에 기반해 그룹을 나누고 그 집단의 상태 변화를 추적.

둘 다 특정 시점을 기준으로 비슷한 집단을 묶어 변화를 파악한다는 점이 비슷하다.

 

 

---

 

코호트에서 각 개인은 스스로가 속한 집단이 무엇인지 인지하고 있을 수도 있고 모르는 상태에서 분석하기도 한다.

 

코호트 분석의 목적은?

집단별 행동 패턴이 어떻게 변화하는지 비교.

Ex) 광고로 유입된 고객과 지인 추천으로 유입된 고객의 서로 다른 장기 구매 패턴을 파악

---

코호트 그룹화, 코호트 시계열 데이터, 코호트 행동 집계 지표라는 세가지 요소로 구성

 

- 코호트 그룹화

나이나 국적 혼인여부등 다양한 특징으로 그룹핑하기도 하지만 다양한 특징들이 데이터에 포함되는 경우에는 시작 날짜에만 기준을 두고 그룹을 나누는 것이 좋다.

 

- 시계열

시계열 데이터는 개체의 전체 일생을 포함해야한다. 그룹에서 이탈하지 않고 남아있는 생존자만 끝까지 데이터셋에 남아 생존자 편향이 발생할 수 있다.

또한 상황에 맞는 충분한 시계열 데이터를 확보해야한다.(예를들어 고객이 한달에 한번 상품을 구매한다면 여러달에 걸친 데이터가 필요하고 일년에 한번 산다면 몇년에 걸친 데이터가 필요하다.)

 

- 집계 지표

상황에 따라 코호트별로 적절한 집계 함수(sum,count,avg)를 사용

 

---

실습 데이터 소개

legislators 테이블

legislators 테이블에는

미국 의원들의 간단한 신상정보들이 문자열 형태로 저장되어있고 칼럼에 따라 null 값이 많은 것이 특징이며

정수로 표현된 id 값들도 존재한다.

 

legislators_ terms 테이블

legislators_ terms에는 의원의 임기 시작 날짜와 종료 날짜, 의원 유형, 소속정당이 저장돼 있다.

두 테이블에는 공통적으로 id_bioguide(인명사전)가 저장 되어있다.

 

 

---

- 리텐션 분석

리텐션은 고객의 활동 시간 날짜로부터 행동 데이터가 저장된 시간 구간의 개수를 의미한다.

주로 사용자 유지율, 고객 충성도, 제품이나 서비스에 대한 관심도를 분석할 때 사용된다.

 

개체 수 리텐션 값은 첫 구간에 100%가 되며 시간이 흐를수록 유지되거나 감소할 수는 있지만

절대 100%를 초과할 수 없다. 리텐션 커브가 평평하게 유지되는지 혹은 계속해소 감소하는지 관찰

 

하지만 수익이나 행동 수 리텐션 값은 증가하기도 한다. 

 

실습

- 첫 임기를 시작한 날짜를 기준으로 의원들의 리텐션 수 분석 = 임기를 시작하고 구간별로 얼마나 이탈하는지 혹은 얼마나 유지하는지 파악

 

- 리텐션 계산하기

1. 코호트 정의

select id_bioguide, min(term_start) as first_term
from legislators_terms
group by 1;

각 의원들이 첫 임기를 시작한 날짜를 계산

 

2. 시계열 데이터 생성

select date_part('year',age(b.term_start,a.first_term)) as period
,count(distinct a.id_bioguide) as cohort_retained
from
(
select id_bioguide, min(term_start) as first_term
from legislators_terms
group by 1
) a
join legislators_terms b on a.id_bioguide = b.id_bioguide
group by 1 
;

정의한 코호트를 바탕으로 시계열 데이터를 생성했다

실행결과

실행 결과를 보면 총 12,518명의 의원중 첫 임기 시작일을 기준으로 연차를 계산하고 연차별로 몇명의 의원들이 존재하는지 출력하였다.

 

만약 10년차 의원이 있다면 period가 0부터 10까지는 카운트 되지만 이후부터는 카운트 되지 않는다. 따라서 period가 증가함에 따라 카운트는 유지되거나 줄어들기만 할 뿐 절대 증가하지는 않는다.

 

 

 

 

 

추가로 위의 코드를 서브쿼리로 이용하여 기간별 재임 의원의 퍼센트를 계산하는 코드이다.

select period
, first_value(cohort_retained) over (order by period) as cohort_size
,cohort_retained
,cohort_retained*1.0/first_value(cohort_retained) over (order by period) as pct
from
(
	select date_part('year',age(b.term_start,a.first_term)) as period
	,count(distinct a.id_bioguide) as cohort_retained
	from
	(
		select id_bioguide, min(term_start) as first_term
		from legislators_terms
		group by 1
	) a
	join legislators_terms b on a.id_bioguide = b.id_bioguide
	group by 1
) aa
;

시각화 결과

이런식으로 추가적인 계산으로 보기좋게 시각화가 가능하다

 

---

 

 

- 시계열 데이터에서 코호트 분석하기

어떤 기준으로 코호트를 나눌까?

> 시계열에서 개체가 처음 등장한 날짜를 기준으로 일반적으로 나눈다. 왜냐하면 특정 이벤트를 다른 날짜에 시작한 그룹과 서로 다른 행동 패턴을 보이는 경우가 많기 때문에

Ex) 기술과 경영시간에 배운 얼리어답터 vs 레이트 어답터

 

실습 데이터에 적용 

- 첫 임기를 시작한 시기를 기준으로 코호트 나누기

 

1. 임기 시작일을 기준으로 임기기간별 코호트 크기 구하기

Select date_part('year',a.first_term) as first_year
,coalesce(date_part('year',age(c.date, a.first_term)),0) as period
,count(distinct a.id_bioguide) as cohort_retained
From	
(
		select id_bioguide, min(term_start) as first_term
		from legislators_terms
		group by 1
	) a
	join legislators_terms b on a.id_bioguide = b.id_bioguide
	LEFT join date_dim c on c.date between b.term_start and b.term_end
	and c.month_name = 'December' and c.day_of_month = 31
	Group by 1,2
	;

 

이를 이용해 

실행결과

시작 연도와 임기기간별로 코호트 크기를 구할 수 있다.

 

2. 코호트 분석하기

위의 테이블을 서브쿼리로 사용하고 파티션 절을 이용해 코호트 비율까지 계산하기

select first_year,
period
,first_value(cohort_retained) over (partition by first_year
order by period) as cohort_size
,cohort_retained
,cohort_retained*1.0/first_value(cohort_retained) over (partition by first_year
order by period) as pct

From
(
	Select date_part('year',a.first_term) as first_year
	,coalesce(date_part('year',age(c.date, a.first_term)),0) as period
	,count(distinct a.id_bioguide) as cohort_retained
	From	
		(
			select id_bioguide, min(term_start) as first_term
			from legislators_terms
			group by 1
		) a
		join legislators_terms b on a.id_bioguide = b.id_bioguide
		LEFT join date_dim c on c.date between b.term_start and b.term_end
		and c.month_name = 'December' and c.day_of_month = 31
		Group by 1,2
	)aa ;

 

서브쿼리를 먼저 만들어 놓고 최종결과를 만들어내면 조금 더 이해하기 쉽다. 

코호트 사이즈와 퍼센트 구하는 방식은 앞서 진행한 임기기간별 비교와 동일하다.

실행결과

실행결과로 임기를 시작한 시기별로 파티션을 나누어 임기기간이 늘어남에 따라 남아있는 의원의 수와 비율을 알 수 있다. 추가로 조건을 줘서 원하는 시기나 임기기간에 결과만 모아서 분석을 진행 할 수 도 있다.

 

---

 

- 다른 테이블에 저장된 속성으로 코호트 분석하기

앞에서는 시계열 데이터를 기준으로 코호트를 정의했다면 이번에는 다른 테이블에 있는 속성을 이용해서 코호트를 정의해 볼 것이다.

 

원래사용하던 legislators_terms에 legislators 테이블의 성별 필드를 가져와 코호트를 나누고 리텐션을 분석 할 것이다.

 

SELECT d.gender
,coalesce(date_part('year',age(c.date,a.first_term)),0) as period
,count(distinct a.id_bioguide) as cohort_retained
From
	(
		select id_bioguide, min(term_start) as first_term
		from legislators_terms
		group by 1
		
	) a
	Join legislators_terms b on a.id_bioguide = b.id_bioguide
	left join date_dim c on c.date between b.term_start and b.term_end
	and c.month_name = 'December' and c.day_of_month = 31
	Join legislators d on a.id_bioguide = d.id_bioguide
	Group by 1,2
	Order by 2,1
	;

 

추가로 Join절을 사용해서 legislators 테이블에 성별을 가져왔다 나머지는 앞에 서브쿼리 만들기 코드와 동일하다.

실행결과

추가로 성별 칼럼이 추가된 모습

 

이번에도 만든 테이블을 서브쿼리로 사용하여 성별과 기간 별로 코호트 사이즈를 출력하고 퍼센트까지 완성해보자.

 

Select gender
,period
,first_value(cohort_retained) over (partition by gender order by period) 
as cohort_size
,cohort_retained
,cohort_retained*1.0/ first_value(cohort_retained) over (partition by gender order by period)
as pct
From
(
	SELECT d.gender
	,coalesce(date_part('year',age(c.date,a.first_term)),0) as period
	,count(distinct a.id_bioguide) as cohort_retained
	From
		(
			select id_bioguide, min(term_start) as first_term
			from legislators_terms
			group by 1
		
		) a
		Join legislators_terms b on a.id_bioguide = b.id_bioguide
		left join date_dim c on c.date between b.term_start and b.term_end
		and c.month_name = 'December' and c.day_of_month = 31
		Join legislators d on a.id_bioguide = d.id_bioguide
		Group by 1,2
) aa
Order by 2,1
	;

 

실행결과

이를 이용해 코호트를 성별로 정의하고 분석을 진행 할 수 있다.

이런 과정을 응용한다면 외부의 테이블을 가져와서 원하는 칼럼별로 분석도 진행할 수 있다.

 

 

---

 

 

이상으로 코호트 리텐션 분석에 대해 정리해보았다.

리텐션 분석은 특정 시간 윈도우에서의 개체의 존재 여부를 의미한다.

완전히 새로운 개념이 아니라 복잡하지는 않았지만

막상 실습 데이터를 이용하려고 하니 신경써야 할 것이 많았다.

우선 어떤 속성을 기준으로 코호트를 나눌 것인지에 따라 다양한 분석 결과가 나올 수 있으며 

이를 확인하기 위해서는 적절한 집계함수를 사용해야 한다.

 

추가로 결측 데이터가 있는 경우에는 적절한 조치를 통해 해결 할 수 있다.

Ex) 실습 데이터에서 임기 종료가 없는경우 의원별 임기기간 더해주기, 앞뒤 데이터에 값 가져오기 등등