[열정페이 - SQL] 4장 코호트 분석 정리(생존자 분석, 리턴십 분석)

- 생존자 분석이란?

고객 이탈이나 탈퇴 같은 특정 이벤트가 발생하기전까지의 기간 등을 파악하는 데 활용한다.

개체가 일정 시간 뒤에 남아있을 비율을 예측하거나, 생존 분석에 코호트를 이용해 생존 비율을 증가시키거나 감소하는 특성 및 환경 등을 찾아내기도 한다.

 

리텐션 분석과 비슷하지만 생존 분석에서는 해당 구간 및 그 이후 구간 내 지속적인 개체 존재 여부가 중요하다.

 

---

 

- 생존자 분석 실습

1. 구간 설정

select id_bioguide
,date_part('century',min(term_start)) as first_century
,min(term_start) as first_term
,max(term_start) as last_term
,date_part('year',age(max(term_start),min(term_start))) as tenure
from legislators_terms
group by 1;

이 코드는 의원들 별로 가장 첫 재임 시작 날짜와 가장 마지막 재임 시작 날짜를 비교해 근속기간을 나타내는 코드이다.

실행결과

이처럼 근속기간(tenure)를 나타내고 이 기간을 구간으로 설정해서 분석을 진행 할 것이다.

리텐션 분석과 비슷한 것 같지만 시작날짜를 기준으로 하는 것이 아닌 구간을 설정한다는 것이 가장 큰 차이점이다.

 

마찬가지로 위의 테이블을 서브쿼리로 사용하여 추가적인 분석을 해보자

 

2. 근속기간이 10년이 넘은 의원 계산

select first_century
,count(distinct id_bioguide) as chort_size
,count(distinct case when tenure >= 10 then id_bioguide
								end) as survived_10
,round(count(distinct case when tenure >= 10 then id_bioguide
								end)*1.0/count(distinct id_bioguide),2) as pct
From								
(
	select id_bioguide
	,date_part('century',min(term_start)) as first_century
	,min(term_start) as first_term
	,max(term_start) as last_term
	,date_part('year',age(max(term_start),min(term_start))) as tenure
	from legislators_terms
	group by 1
)a
group by 1;

실행결과

실행결과는 다음과 같다.

조건문을 수정하여 근속기간을 수정하거나 재임을 5회이상한 의원들의 비율 또한 구할 수 있다.

전체적인 흐름은 리텐션 분석과 유사하다.

 

생존 분석은 리텐션 분석과 깊은 관련이 있다. 리텐션은 시작 날짜부터 구간마다 남아있는 개체 비율을 분석하는 방면

생존 분석은 특정 구간에서의 생존 개체 비율을 분석한다.

 

---

- 리턴십 분석이란? 

생존 분석의 반대 개념으로 개체가 특정 시간 또는 그 이후에도 살아남아 있는지가 아니라, 특정 날짜에서 개체가 다시 돌아오는지 혹은 특정 행동을 반복하는지를 계산한다.

 

주로 소매업 관련 분석에 사용되지만 다양한 도메인의 분석에 유용하다.

예를들어 대학에서 선행강의를 듣고 후속 강의를 듣는 학생의 비율이나, 병원에서 첫 진찰 후 후속 진료를 받는 환자의 비율을 확인 할 때 사용된다.

 

이번 실습 데이터에서는 의원 데이터를 사용해

상원의원직과 하원의원직을 모두 수행한 의원 수와 그중 하원의원직을 먼저 수행하고 상원의원이 된 사람의 비율을 계산해 볼 것 이다.

 

1. 코호트 크기 구하기

의원 유형이 하원의원인 사람들에 한해, 세기별 임기를 시작한 하원의원의 수를 구한다.

select first_century
,count(distinct id_bioguide) as chort_size
from
(
	select id_bioguide
	,date_part('century',min(term_start)) as first_century
	from legislators_terms
	WHERE term_type = 'rep'
	GROUP BY 1
)
group by 1;

실행결과

2. 조건에 맞게 필터링 하기

위 커리와 legislaltors_terms 테이블과 join을 수행해 하원의원이었다가 나중에 상원의원이 된 의원수를 구한다.

select first_century
,count(distinct a.id_bioguide) as chort_size
from
(
	select id_bioguide
	,min(term_start) as first_term
    ,date_part('century',min(term_start)) as first_century
	from legislators_terms
    WHERE term_type = 'rep'
	GROUP BY 1
) a 
join legislators_terms b on a.id_bioguide = b.id_bioguide
and b.term_type = 'sen' and b.term_start > a.first_term
GROUP BY 1;

실행결과

 

3. 비율구하기

위 두 테이블에 LEFT JOIN을 수행해 하원의원으로 첫 임기를 시작하고 그 후에 상원의원직을 수행한 비율을 구한다.

Select aa.cohort_century
,round(bb.rep_and_sen*1.0/aa.reps,2) as pct
From 
(
	Select date_part('century', a.first_term) as cohort_century
	,count(id_bioguide) as reps
	From 
	(
		select id_bioguide, min(term_start) as first_term
		from legislators_terms
		where term_type = 'rep'
		group by 1
	) a
	group by 1 
) aa
LEFT JOIN
(
	Select date_part('century', b.first_term) as cohort_century
	,count(distinct b.id_bioguide) as rep_and_sen
	FROM
	(
		select id_bioguide, min(term_start) as first_term
			from legislators_terms
		where term_type = 'rep'
		group by 1
	) b
	join legislators_terms c on b.id_bioguide = c.id_bioguide
	and c.term_type = 'sen' and c.term_start > b.first_term
	GROUP BY 1
) bb on aa.cohort_century = bb.cohort_century
;

실행결과

하원의직을 먼저 수행한후 상원의원직까지 수행한 의원의 비율은 18세기에 가장 높은 것을 알 수 있다.

 

테이블을 여러개 조인해서 사용하다 보니 별칭이 굉장히 중요하다는 것을 느꼈다. 물론 순차적으로 서브쿼리부터 작성하고 이해한다면 문제없겠지만 외부 테이블을 여러개 가져다 쓸때는 별칭도 신경써서 만들어야 할 것이다.

 

실습데이터에서는 하원의원을 수행한뒤 다음에 상원의원을 수행한 것을 리턴한 것으로 보았지만 다른 데이터에서는 또 다른 리턴의 정의를 해야할 것이다.

---

- 크로스 섹션 분석

코호트 분석과 대조적으로 크로스 섹션 분석은 특정 시점을 기준으로 개인 또는 그룹 간 차이를 비교한다.

Ex) 소득과 교육 수준의 상관 관계를 밝히는 데 활용

 

데이터를 모두 수집할 필요 없이 특정 시점의 데이터만 수집하면 된다.

 

하지만 적절하지 못한 시기를 선택해서 분석하면 생존자 편향이 발생한다

Ex) 우수 고객들의 공통점 찾기