[Big Query] Decile분석 : 고객 매출 등급을 10단계로 나누기
Today's Topic
- Decile분석은 무엇인가요?
- Decile분석 실습 - 고객 매출 등급을 10단계로 나누기
- Used Function
- ntile()함수
- over()구문
- 구성비 및 구성비누계 구하기
안녕하세요~!!
오늘은 Decile분석에 대해 살펴볼 예정입니다.
고객 매출을 집계하는 리포터, 마케터, 애널리스트분들께서 자주 접하실 수 있는 분야일 것이라 생각됩니다.
그렇다면 지금부터 같이 학습 해 보도록 하겠습니다!!
1. Decile 분석은 무엇인가요?
Decile? 처음 들었을 땐 이게 무엇인가 감이 잘 안오실 것입니다.
Decile라는 뜻은,
통계에서 1/10을 나타내도록 정렬된 데이터를 10개의 동일한 부분으로 나누는 개념이라고 하네요!
그렇다면 이번 분석, 어떤 분석인지 감이 오실까요?
그렇습니다.
바로 등급을 10등급으로 매기는 것입니다.
그 기준점은 여러가지가 될 수 있겠지요.
예를 들어 고객의 구매 금액으로 기준을 세울 수도 있고 구매 횟수로도 그 기준을 세울 수 있습니다.
분석에서는
매출, 하나의 등급그룹별 평균/합계 매출, 구성비, 구성비누계 등을 구해서 중요도를 파악하게 되는데,
Decile 1쪽에 위치한, 혹은 가깝게 위치한 고객들이 핵심 고객, 즉 매출 증대에 기여하는 주 고객이고,
Decile 10에 가까이 위치한 고객들이 신규 혹은 매출에 많은 영향을 주지 않는 고객이라고 보면 됩니다.
그렇다면 이제 본격적으로 Decile분석을 쿼리를 통해 실습해 보도록 하겠습니다!
2. Decile 분석 실습
Decile분석은 다음 절차대로 진행합니다.
1) 사용자를 구매 금액이 많은 순서대로 정렬합니다.
2) 정렬된 사용자 상위부터 10%씩 Decile 1부터 Decile10까지 그룹을 할당합니다.
3) 각 그룹의 구매 금액 합계를 집계합니다.
4) 전체 구매 금액에 대해 각 Decile구매 금액 비율(구성비)를 계산합니다.
5) 상위에서 누적으로 어느 정도의 비율을 차지하는지 구성비누계를 집계합니다.
해당 분석에 사용된 테이블은 다음과 같습니다. (테이블 이름: purchase)
아래 테이블은 예시이며, 하단에 더 많은 데이터가 있습니다.
user_id: 유저의 id
reg_date: 가입일자
amount: 구매금액
stamp: 구매일자
STEP1. 구매액이 많은 순서로 사용자 그룹을 10등분하기
--3-1) 구매액이 많은 순서로 사용자 그룹을 10등분하기
with
user_purchase_amount as(
select user_id,
sum(amount) as purchased_price
from inlaid-lane-373607.sqlr202301.purchase
group by user_id
)
, user_with_decile as(
select user_id, purchased_price, ntile(10) over (order by purchased_price desc) as decile
from user_purchase_amount
)
select * from user_with_decile;위 쿼리문은 구매액이 많은 순서대로 10등분 그룹화를 시켜준 것입니다.
1) with구문을 사용해 user_purchase_amount 임시테이블을 생성합니다.
2) from절을 사용해 테이블을 추출합니다.
3) group by절을 통해 user_id별로 묶습니다.
4) select절을 활용해 user_id, 구매금액의 합계를 출력합니다. (user_id별 구매금액의 합계입니다.)
,
5) 추가로, user_with_decile임시테이블을 생성합니다.
6) user_purchase_amount 임시테이블에서 user_id, 유저별 구매 총금액 칼럼을 선택합니다.
7) 유저별 구매 총금액을 기반으로 내림차순 정렬하여 10등분한 값을 추출합니다.
함수확인
ntile(expr) over (partition by 조건1 order by 조건2):
측정 조건을 기준으로 expr에 명시된 값만큼 데이터를 분할하고 반환한다.
회원별로 구매한 총 금액과 그에 해당하는 등급이 나왔습니다.
예로, id가 A6631인 고객이 구매한 총 금액은 203400원이고, 2등급입니다.
STEP2. Decile집계하기
이 단계에서는 앞서 구한 Decile들에 대한 통계를 구하는 작업입니다.
각 등급별 총금액, 평균금액, 구성비, 누계비를 구해보겠습니다.
아래 쿼리문은 앞서 step1단계와 이어진 것입니다. 추가된 내용은 decile_amount테이블을 만드는 것 부터입니다.
--3-2) 10분할한 decile들을 집계하기
with
user_purchase_amount as(
select user_id,
sum(amount) as purchased_price
from inlaid-lane-373607.sqlr202301.lec11_6
group by user_id
)
, user_with_decile as(
select user_id, purchased_price, ntile(10) over (order by purchased_price desc) as decile
from user_purchase_amount
)
, decile_amount as(
select decile
, sum(purchased_price) as amount --등급별 전체 금액
, avg(purchased_price) as avg_amount -- 등급별 평균금액
, sum(sum(purchased_price)) over (order by decile) as cumulative_amount --등급별 누계값
, sum(sum(purchased_price)) over() as total_amount --전체 금액 합계
from user_with_decile
group by decile
)
select * from decile_amount
order by decile;
위 쿼리문은 구매액이 많은 순서대로 10등분 그룹화를 시켜준 것입니다.
1) decile_amount 임시 테이블을 생성합니다.
2) from절을 사용해 테이블을 지정하고, group by절을 사용해 decile별로 묶습니다
3) 등급별 전체 금액, 등급별 평균 금액, 등급별 누계값, 전체금액의 합계를 계산합니다. 누계값의 경우, 등급을 오름차순으로 정렬한 상태에서 더하는 것으로 지정합니다.
4) 이용자들의 전체 구매 금액의 합계를 계산합니다.
해당 쿼리문을 실행한 결과는 다음과 같습니다.
STEP3. 구성비와 누계비 구하기
STEP2에서 평균, 합계, 누계값을 계산했다면 이번에는 구성비와 누계비율을 계산하여 Decile를 완성해 보겠습니다.
구성비란, 각 데이터가 차지하고 있는 비율을 말하며, 여기서는 각 등급(decile)이 차지하고 있는 비율입니다.누계비(율)란, 구성비를 누적한 비율입니다.
아래 쿼리문은 앞서 step2단계와 이어진 것입니다. 추가된 내용은 맨 하단의 select절부터입니다.
--3-3) 구성비와 구성비누계 계산하기
with
user_purchase_amount as(
select user_id,
sum(amount) as purchased_price
from inlaid-lane-373607.sqlr202301.lec11_6
group by user_id
)
, user_with_decile as(
select user_id, purchased_price, ntile(10) over (order by purchased_price desc) as decile
from user_purchase_amount
)
, decile_amount as(
select decile
, sum(purchased_price) as amount --등급별 전체 금액
, avg(purchased_price) as avg_amount -- 등급별 평균금액
, sum(sum(purchased_price)) over (order by decile) as cumulative_amount --등급별 누계비
, sum(sum(purchased_price)) over() as total_amount --전체 금액 합계
from user_with_decile
group by decile
)
select decile, amount, avg_amount,
100*amount / total_amount as total_ratio,
100*cumulative_amount / total_amount as cumulative_ratio
from decile_amount
order by decile;구성비의 경우에는 비율이므로, 100*특정 고객의 구매 금액에서 전체 고객의 구매 금액을 나누어 주면 됩니다.
또한, 누계비의 경우에는 100*특정 고객의 누계값에서 전체 고객의 구매 금액을 나누어 주면 됩니다.
해당 쿼리의 결과는 다음과 같습니다.
각 등급별로 합계, 평균은 물론 구성비와 누계비까지 구해진 것을 볼 수 있습니다.
해당 데이터에서 1등급이 무려 20%나 차지하네요. 반면 10등급은 1.6%에 그치는 것을 확인할 수 있습니다.
이렇게 오늘은 decile분석에 대해 살펴보았습니다.
고객의 중요도에 따라 분류한 방법이기 때문에 무작정 나누는 방식에 비해서 좀 더 효율적인 마케팅을 할 수 있게 됩니까
상황에 맞게 잘 사용하는 것이 중요할 것 같습니다.
감사합니다.
본 내용은 데이터분석을 위한 sql레시피 11장을 참고하여 작성되었습니다.