[실습] SQL을 활용한 리텐션 분석하기

* 이번 포스팅은 리텐션 개념에 대해 공부하다 직접 해보는게 도움이 될 것 같아 진행해본 내용입니다.

 

관련 포스팅

2021.09.23 - [#2. 분석 개념 노트] - [개념노트] AARRR을 책으로 배웠습니다 #3 : 리텐션(Retention)

사용 데이터

Online Retail Data Set from UCI ML repo

사용 tool

• PostgreSQL
• Excel

목적

• 직접 리텐션 차트를 만들어보고 코호트별 리텐션 분석을 경험해보기 위함.

주의점

• 실제로 업무상 리텐션 분석을 할 수 없는 상황이라 학습을 목적으로 진행해본 내용입니다.
• 아주 기초적인 수준의 리텐션 분석 내용이기 때문에 '이렇게 한번 해봤구나.?' 정도로 생각해주세요 :)
• 그리고 혹시 보완할 점이나 잘못된 부분이 있으면 언제든지 말씀 주시면 정말 감사하겠습니다~

* 쿼리 부분을 스킵하고 싶으시다면 왼쪽 목차에 '3.리텐션 차트 분석하기' 부분으로 바로 이동해주시면 됩니다!

---

0. 데이터 소개

이번에 사용한 데이터는 캐글에서 다운로드를 한 데이터입니다.

캐글에 적혀있는 데이터 설명을 보고 간략하게 정리를 해봤습니다.

* 데이터 종류
 : 송장(Invoice)별 거래 데이터

* 특징
 : 특별한 선물(Unique-gift)을 판매하는 회사.
   고객은 주로 도매상.

* 데이터 크기
 : 541,909 rows * 8 columns

컬럼명	컬럼 설명
InvoiceNo	상품 구매 시 발행된 송장 번호
StockCode	구매 상품 코드
Description	구매 상품명
Quantity	구매 수량
InvoiceDate	구매 날짜
UnitPrice	단가
CustomerID	고객 ID
Country	구매 국가

 

이 데이터셋을 PostegreSQL 로 불러와 sales_raw 라는 테이블로 저장했습니다.

select * from sales_raw limit 10;

데이터가 잘 불러와진것을 확인할 수 있었는데요.

데이터를 살펴보면서 이번 분석에서 정하고 갈 기준 몇 가지를 스스로 정해보았습니다.

 

0.1. 분석 전 기준 정하기

1. 코호트의 기준

리텐션 차트를 만들 때 코호트의 기준을 "최초로 상품을 거래한 월이 같은 고객"으로 정했습니다.

같은 월을 기준으로 삼은 이유는 데이터를 살펴보았을 때 고객들의 거래가 Daily 하지 않았기 때문입니다.

• 해당 데이터의 기간인 2010-12-01 ~ 2011-12-09 동안 4,240명의 Distinct 한 고객 중 약 90%(3,821명의 고객)가 10번 이하의 거래를 기록했습니다.
  → 월별로 리텐션을 구하기로 한 이유.
• 같은 기간을 주 단위로 환산해보면 약 53주가 되는데, 53번 미만의 거래를 기록한 고객이 4,230명으로 약 99.8%에 해당하는 수치입니다.
  → 주별 리텐션도 계산이 힘들 것 같다는 생각을 하게된 이유.

 

2. 데이터 전처리 내용

1. sales_raw 데이터를 확인해보면 동일한 Date | No (=InvoiceNo) | customerid 별 여러 행이 존재함을 확인할 수 있습니다. 
   최소 단위가 상품코드로 되어있어 이런 상황이 발생했는데, 리텐션 차트를 만들 땐 customerid 를 기준으로 계산을 하기 때문에 Date | customerid | Country 를 기준으로 group by 를 해주기로 했습니다.
   (이 과정에서 No(=InvoiceNo) 컬럼도 필요가 없어 제외했습니다.)
   
   
2. quantity 의 값이 음의 값을 가지는 데이터가 있었습니다.
   아마도 환불을 한 경우인 것 같은데, Date | No (=InvoiceNo) | customerid  을 기준으로 group by 를 해준 뒤 sum(quantity) 가 음의 값을 가지는 행은 제외하기로 했습니다.
   → sum(quantity) 가 음의 값을 가지는 고객은 거래를 하지 않은 고객으로 판단.
   
   
3. customerid 에 null 값이 있었습니다.
   리텐션 차트를 만들 때 꼭 필요한 customerid 가 null 값인 경우는 해당 데이터를 제외했습니다.

 

 

1. 리텐션 차트 만들기

위 기준들을 기반으로 SQL 을 이용해 리텐션 차트를 만들어보겠습니다!

 

1.1. 데이터 전처리

위에서 언급한 전처리 내용을 반영하는 과정입니다.

create view pre_sales 
as (	
	select substr(cast(date as text),1,4) as year
		, substr(cast(date as text),6,2) as month
		, customerid, country, sum(quantity) quantity
	from sales_raw  
	where customerid is not null
	group by 1,2,3,4
	having sum(quantity) > 0
);
select * from pre_sales

• 중복된 행을 제거해주기 위해 group by 를 해주었습니다. 
• customerid 가 null 인 값은 제외했습니다.
• sum(quantity) 값이 양수인 값만 추출했습니다.
• Date 컬럼을 year, month 로 구분해주었습니다.
  → cast() 함수로 데이터 타입을 바꾼 뒤 문자열 함수인 substr() 을 이용해 year, month 만 뽑아냈습니다.

 

1.2. 월별 Index 만들어주기

리텐션 차트를 만들기 위해서는 월별 Index 를 붙혀줘야 했습니다.

여기서 말하는 월별 Index 를 조금 설명을 해보자면 이렇습니다.

리텐션 차트를 그릴 때 필요한 기준시점(M+1, M+2, ... M+12)을 만들어주기 위해 필요한 Index

월별 Index를 붙혀주기 위해 만들 테이블 컬렴에 대해 설명을 해보면 아래와 같습니다.

컬럼명	설명
base_ym	첫 구매가 발생한 월 (= 월별 코호트)
year	거래가 발생한 연도
month	거래가 발생한 월
idx	월별 인덱스

create View monthly_idx
as(
	select base_ym, year, month
		, row_number() over(partition by base_ym order by year, month) as idx

	from(select distinct year, month from pre_sales) a
		cross join (select distinct concat(year, month) as base_ym from pre_sales) b

	where base_ym <= concat(year, month)
);

select * from monthly_idx

(왼쪽) 월별 Index,  (오른쪽) 이해를 돕기 위한 월별 Index 전체 모습 그림..

이렇게 base_ym 을 만들어놓는 이유는 매월 거래를 하지 않는 고객이 있기 때문입니다.

• 2011년 1월에 거래를 했다가 2011년 4월에 그 다음 거래를 한 고객이 있을 경우.
  • SQL 에서 row_number() 함수를 이용해 Idx 를 만들면 월별 인덱스가 꼬여버리는 경우가 생겨버립니다.
    

    customerid	year	month	idx
    A	2011	01	1
    A	2011	04	2

    이 두번째 행의 idx 는 리텐션 차트에서는 4 가 되어야 합니다.

그렇기 때문에 월별 인덱스 테이블을 하나 만들어(cross join 이용) Join 해주는 방식으로 인덱스를 붙혀주기로 했습니다.

그리고 첫 거래가 발생한 월(=base_ym) 이전의 구매연도와 월은 필요가 없기 때문에 제거해주었습니다. (where 절의 내용)

 

 

1.3. 고객 구매 데이터(=pre_sales)에 월별 인덱스 붙혀주기

이제 고객 정보가 들어있는 pre_sales 에 월 인덱스를 붙혀주어 전처리 과정을 마무리하려 합니다.

월별 인덱스를 붙혀주기 위한 Key 는 '각 고객별 첫 거래발생 연도와 월'이 됩니다.

create view sales_fin
as(
	
	select a.*, idx
	from(
		
		select a.*, base_ym
		from pre_sales a
		left join (
			
			select *
			from (
				select customerid, country, year, month, concat(year, month) base_ym
					, row_number() over(partition by customerid order by year, month) as base_idx
				from pre_sales
			) a
			where base_idx = 1
			
		) b
			on a.customerid = b.customerid
	) a
	left join monthly_idx b
		on a.base_ym = b.base_ym
		and a.year = b.year
		and a.month = b.month

);

select * from sales_fin

위 쿼리구문의 약간의 설명을 해보자면,

• Line 11 ~ 14 : 각 customerid 별 첫 거래 발생 연도, 월을 만들어 줍니다.
  (이는 코호트의 기준이 되는 base_ym 이 됩니다.)
• Line 7 ~ 20 : 고객 구매 데이터인 pre_sales 에 코호트의 기준이 되는 base_ym 을 붙혀줍니다.
• Line 22 ~ 25 : 리텐션 계산을 위해 미리 만들어 놓은 월별 인덱스를 붙혀줍니다.

위 쿼리의 결과는 아래와 같습니다!

sales_fin 

이제 리텐션 차트를 만들기 위한 전처리 과정이 끝났습니다.

이제 sales_fin 을 이용해서 리텐션 차트를 만들어봅시다!

 

 

2. 리텐션 차트 만들기

앞서 귀찮은 작업들을 거쳐가며 데이터를 깔끔하게 만들어뒀기 때문에 리텐션 차트를 만드는 과정은 아주 간단합니다.

 

2.1. 국가별 비중 확인하기

리텐션 차트를 만들기 앞서 국가별 고객의 비중을 확인해봤습니다.

select country, round((cnt*100)/sum(cnt) over(),2) as rate
from(
	select country, count(country) as cnt
	from sales_fin
	group by 1
) a
order by rate desc

37개의 국가가 있었지만 약 90%가 United Kingdom에서 구매한 고객들이었습니다.

그래서 대부분이 속해있는 United Kingdom 의 고객에 대한 리텐션만 살펴보기로 했습니다.

 

2.2. 볼륨(Volume) 구하기

이제 각 코호트별 고객의 수를 나타내는 볼륨(Volume)을 구해보겠습니다.

select base_ym, yyyymm, idx, count(customerid) as volume
from sales_fin
where country = 'United Kingdom'
	and idx = 1
group by 1,2,3
order by 1,2,3

volume 이 점점 줄어드는 것을 확인할 수 있었습니다.

일단은 리텐션 차트를 완성시켜보겠습니다.

 

2.3. 리텐션 차트 만들기

이제 시간이 지남에 따라 유지되고 있는 고객의 수를 확인해봅시다.

select base_ym
	, max(case when idx = 1 then volume end) as m1
	, max(case when idx = 2 then volume end) as m2
	, max(case when idx = 3 then volume end) as m3
	, max(case when idx = 4 then volume end) as m4
	, max(case when idx = 5 then volume end) as m5
	, max(case when idx = 6 then volume end) as m6
	, max(case when idx = 7 then volume end) as m7
	, max(case when idx = 8 then volume end) as m8
	, max(case when idx = 9 then volume end) as m9
	, max(case when idx = 10 then volume end) as m10
	, max(case when idx = 11 then volume end) as m11
	, max(case when idx = 12 then volume end) as m12
from (
	select base_ym, yyyymm, idx, count(customerid) as volume
	from sales_fin
	where country = 'United Kingdom'
	group by 1,2,3
) a
group by base_ym
order by base_ym

• Line 15 ~ 18 : 각 월별 인덱스별 customerid 의 수를 구해줍니다.
• Line 1 ~ 14 : Pivot을 해주기 위해 case when 식을 활용해주었습니다. (관련 게시글은 여기에서 확인 가능합니다!)
  
  

기간별 고객 수 

이렇게 리텐션 차트를 만들어보았습니다.

이제 Excel 을 이용해서 이것저것 분석을 해보도록 하겠습니다.

 

 

3. 리텐션 차트 분석하기

SQL 로 만든 리텐션 차트를 Excel 로 가져와 작업을 진행했습니다.

3.1. 리텐션 차트

사용자 수를 나타낸 리텐션 차트를 비율로 수정했습니다.

그럼 개념 설명을 했던 포스팅에서 리텐션 차트에서 살펴봐야할 항목들을 위 리텐션차트를 보며 적용해봅시다.

 

1) 하나의 코호트 안에서 기간에 따른 유지율 살펴보기

2010년 12월 코호트를 기준으로 리텐션 추이를 나타내는 그래프를 그려보았습니다.

2010년 12월 코호트 리텐션 추이

그래프를 확인하며 알 수 있는 부분은 다음과 같습니다.

- 첫 달이 지난 후 35.5%의 고객이 재구매를 했습니다.

- 그 이후로 꾸준히 35% 안밖의 안정적인 리텐션을 유지하고 있으며, 11개월이 지난 시점에는 49.7% 까지 리텐션이 올라갔습니다.

- m+12 의 경우 데이터가 12월 10일까지밖에 없었기 때문에 정확한 수치라고 보기 어렵습니다.

 

이러한 부분을 살펴보며 생각을 할 수 있었던 부분은,

- 첫 달이 지나고 나서 안정적으로 리텐션이 유지되는 이상적인 그래프 모양을 보여주었습니다.

- 11개월이 지난 시점이 2011년 11월인데 연말이 다가오는 외부적인 요인에 영향을 받았을지도 모른다 생각을 했습니다.

- 일단 월별 고객층이 안정적으로 유지되고 있으니 좀 더 짧은 주기로 제품을 주문할 수 있도록 프로모션을 진행하는 것도 좋을 것 같다는 생각이 들었습니다.
 → 상품 정기 배송 구독 시 할인 등

 

그런데 다른 코호트의 리텐션을 확인해보면 유독 2010년 12월 코호트의 리텐션 값이 높은 것을 확인할 수 있습니다.

이로부터 생각해볼만한 부분은 다음과 같습니다.

• 어떤 프로모션을 진행했는지는 모르겠지만 그 프로모션의 효과가 좋았다고 생각할 수 있다.
• 만약 프로모션을 진행하지 않았다면 해당 고객들의 특성을 파악하는 것이 리텐션 개선에 큰 도움이 될 수 있다고 생각된다.
  → (살펴보다 저도 궁금해져서 다시 데이터를 들여다보니 고객 특성 데이터(성, 연령 등)가 전혀 없어서 별 다른 특성을 확인할 수가 없네요..)

 

리텐션 차트를 살펴보다보니 특정한 시기에 리텐션 값이 올라가는 경향이 있는 것 같아 조금 다르게(제멋대로) 리텐션 차트를 조작해봤습니다.

제멋대로 바꿔본 리텐션 차트

각 기준년월에 따른 리텐션 수치를 확인해보려고 위와 같이 차트를 조금 바꿔봤는데요.

첫 구매 후 기간에 따라 리텐션 수치가 어떻게 변화하는지 살펴보기보다 특정 기준년월의 리텐션이 어떤지 살펴보기 위한 목적이었습니다.

• 2011년 9월부터 리텐션 값이 이전보다 높게 유지되는 것을 확인할 수 있었습니다.
• 2011년 11월에는 모든 코호트 그룹에서 가장 높은 리텐션 값을 보여주었습니다.

이렇게 살펴보니 4분기가 되면 도매상들이 연말을 미리 준비하거나, 4분기 영국에 특별한 기념일이 많이 있을 수 있다는 생각이 들었습니다.

 

그래서 첫 번째 항목에 대해서 살펴본 결과를 정리해보자면 이렇게 정리를 해볼 수 있겠습니다.

- 비교적 안정적으로 리텐션이 유지되는 모습을 보였다.
  → 고객과의 관계가 잘 유지되고 있다.

- 유독 2011년 12월 코호트 그룹이 높은 리텐션을 보였다.
  → 해당 코호트 그룹의 특성을 파악하여 다른 코호트 그룹과 비교하는 과정이 필요하다.

- 4분기에 리텐션 값이 높아지는 경향이 있다.
  → 연말을 준비하거나 특별한 이벤트가 있는 등의 외부적인 영향이 있을 가능성이 있다.
  → 해당 시즌에 많이 팔리는 제품을 분석해서 제품 다각화를 하거나 4분기 이후에도 지속적으로 구매를 유도할 수 있는 프로모션 진행을 고려해볼만 하다.

 

 

2) 서로 다른 코호트의 동일 기간 리텐션 비교하기

이번에는 축의 가로가 아닌 세로, 즉 상이한 코호트 그룹별 동일 기간 리텐션을 살펴보겠습니다.

편의를 위해 다시 한번 리텐션 차트를 가져왔습니다.

리텐션 차트

리텐션 차트를 동일 기간별로 확인을 해봤을 때 생각해본 내용은 아래와 같습니다.

2011년 12월에 해당하는 값은 2011-12-09 까지밖에 없기 때문에 제외하였습니다.

- 2010년 12월 코호트 그룹이 유난히 높은 리텐션을 보여주고 있다.

- 코호트 그룹을 살펴봤을 때 2011년 2월 ~ 2011년 7월 코호트 그룹의 리텐션이 상대적으로 낮다.
  → 계절성을 가지고 있을 가능성이 있다.

- m+1 리텐션이 급격히 떨어진 후 시간이 지날수록 회복되는 추세를 가진다.
  → 초기 리텐션을 잡아줄 필요가 있다. 

 

3) 코호트 그룹별 규모의 변화 확인하기

마지막으로 코호트 그룹별 규모를 확인해봅시다.

- 코호트 그룹의 규모는 2011년 5월 ~ 2011년 8월의 규모가 상대적으로 작다.

- 1분기, 4분기의 코호트 그룹의 규모가 상대적으로 높다. 특히 12월에 가장 높다.

  → 앞서 생각한 내용과 함께 생각한다면 확실히 계절의 영향 또는 연휴나 이벤트가 많은 시즌의 영향을 받을  가능성이 있다.
  → 수요가 낮은 시즌에 고객을 유입시킬 프로모션 진행을 고려해보면 좋을 듯 하다.

 

 

4. 끝맺으며

이렇게 이번에는 SQL 을 이용해서 리텐션 차트를 만들어보고 분석(?)을 진행해보았습니다.

진행하면서 느낌점을 몇 자 적으며 마치겠습니다.

• 😊 좋았던 점
  1. SQL로 데이터를 정제하는 과정이 꽤 재미있었습니다.
     (취준할 때 Python, R 로 이것저것 했던 기억이 나서 좋았습니다 ㅎ)
  2. 퀄리티는 보장할 수 없지만 데이터를 활용해서 리텐션 분석을 진행해보며 직접 골똘히 생각해보는 시간을 가져볼 수 있었습니다. 덕분에 리텐션의 전반적인 이해도를 높힐 수 있었습니다.
  3. 리텐션 이해를 위한 기초 수준의 실습이라는 목적에는 근접한 것 같아 기분이 좋습니다.
• 😣 아쉬웠던 점
  1. 원천 데이터가 가지는 성질(=영국의 온라인 도매업체에 대한 이해도)을 상세하게 알지 못한 채로 진행했던 터라 조금 아쉬움이 남습니다.
  2. 데이터의 기간이 더 길었다면 조금 더 많은 생각을 해볼 수 있었을텐데 이 부분도 아쉬웠습니다.

 

---

긴 글 읽어주셔서 감사합니다!

수정할 부분이나 보완할 부분 있다면 언제든 댓글 남겨주세요 😊