[PostgreSQL] 5. Decile 분석을 사용해 사용자를 10단계 그룹으로 나누기

Decile 분석

사용자 특징 분석시 성별과 연령 등의 데이터가 있다면 이러한 속성에 따른 특징을 확인할 수 있다.

데모그래픽한 데이터가 존재하지 않는 경우, 사용자 액션으로 속성을 정의해 보는 것도 좋다.

 

데이터를 10단계로 분할해서 중요도를 파악하는 것을 'Decile 분석'이라고 한다.

여기서는 사용자의 구매 금액에 따라 순위를 구분하고 중요도를 파악하는 리포트를 만들어본다.

 

📢 단, Decile 분석은 데이터 검색 기간에 따라 문제가 있다.
검색 기간이 너무 장기간이면 과거의 우수 고객이었지만, 현재는 휴면인 고객이 포함될 가능성이 있다.
반대로 검색 대상이 단기간이라면 정기적으로 구매하는 안정 고객이 포함되지 않고, 해당 기간 동안에만 일시적으로 많이 구매한 사용자가 우수 고객으로 취급 될 수 있다. → 따라서, RFM 분석도 함께 시행한다.

 

1. 사용자를 구매 금액이 많은 순으로 정렬하기
2. 정렬된 사용자 상위부터 10%씩 Decile 1부터 10까지 그룹을 할당하기
3. 각 그룹의 구매 금액 합계 집계하기
4. 전체 구매 금액에 대해 각 Decile의 구매 금액 비율(구성비)을 계산하기
5. 상위에서 누적으로 어느 정도의 비율을 차지하는지 구성비 누계 집계하기

결과 예시

---

1. 구매액이 많은 순으로 사용자 그룹을 10개로 나누기

• NTILE 함수를 사용한다.
  • 주어진 열의 값을 정렬한 후 그 값들을 지정된 수의 구간(버킷)으로 나누어주는 역할을 한다.
  • 이를 통해 데이터를 분석할 때 백분위수 또는 등분된 그룹을 생성할 수 있다.

WITH
user_purchase_amount AS ( 
	SELECT user_id
		, sum(amount) AS purchase_amount
	FROM action_log
	WHERE ACTION = 'purchase'
	GROUP BY user_id
)
, users_with_decile AS (
	SELECT user_id
		, purchase_amount
		, ntile(10) OVER (ORDER BY purchase_amount desc) AS decile
	FROM user_purchase_amount
)
SELECT *
FROM users_with_decile
;

결과 샘플

---

2. 그룹별 합계, 평균 구매 금액, 누계 구매 금액, 전체 구매 금액 계산

• 합계와 평균은 SUM, AVG로 쉽게 구할 수 있다.
• 누계 구매 금액은 decile 로 Order by 하여 윈도우 함수를 사용하여 sum을 한다.
• 전체 구매 금액도 윈도우 함수를 사용하여 sum 한다.

WITH 
user_purchase_amount AS (
	SELECT user_id	
		, sum(amount) AS purchase_amount
	FROM action_log 
	WHERE ACTION = 'purchase'
	GROUP BY user_id
)
, user_with_decile AS (
	SELECT user_id	
		, purchase_amount
		, ntile(10) OVER (ORDER BY purchase_amount desc) AS decile
	FROM user_purchase_amount
)
-- 합계 계산 부분! 
, decile_with_purchase_amount AS (
	SELECT decile
		, sum(purchase_amount) AS amount
		, avg(purchase_amount) AS avg_amount
		, sum(sum(purchase_amount)) OVER (ORDER BY decile) AS cumulative_amount
		, sum(sum(purchase_amount)) OVER () AS total_amount
	FROM user_with_decile
	GROUP BY decile
)
SELECT *
FROM decile_with_purchase_amount
;

---

3. 구성비와 구성비누계 추가

• 매출 합계가 모두 구해진 상태이니, 총 매출로만 나누면 쉽게 구성비와 구성비누계가 구해진다.

...위와 동일

SELECT decile
	, amount
	, avg_amount
	, 100.0 * amount / total_amount AS total_ratio
	, 100.0 * cumulative_amount / total_amount AS cumulative_ratio
FROM decile_with_purchase_amount
;

 

---

🎯 포인트

👉 Decile 분석을 시행하고, 각 Decile의 특징을 다른 분석 방법으로 세분화해서 조사하면 사용자의 속성을 자세히 파악할 수 있다.

👉 예를 들어, Decile 7 ~ 10은 정착되지 않은 고객을 나타낸다. 따라서 리텐션을 높이기 위한 대책(메일 발송 등)을 세울 수 있다.
     이에 해당하는 사람들의 속성과 관련된 데이터를 더 수집하고 활용하는 방법도 있다.