[SQL] Decile 분석을 작성하는 SQL 쿼리 (with. Google BigQuery)

책 '데이터 분석을 위한 SQL 레시피'를 참고했습니다.(11강)

 

 

1. Decile 분석이란?

 

 

Decile -> 10분위수(어떤 집합체를 특정 변수에 따라 10개로 균등하게 나눈 집단의 하나)

 

 

예시로 뉴질랜드의 학교 제도에는 데사일(decile) 제도라는 것이 존재한다.

 

데사일제도는 1995년부터 뉴질랜드정부가 학교 교육의 평준화 목적으로 재정적으로 힘든 학교들에게 부족한 지원금액을 할당할 목적으로 학생들의 사회 계정적 지위, 부모들의 교육수준 등을 기초로 1부터 10까지 분류한 학교 재정 자립도를 의미한다.

 

쉽게 생각해 각 학교마다 가난한 가정의 비율을 조사한 후, 그 비율이 제일 높은 상위 10퍼센트 학교가 decile1, 

가장 낮은 학교가 decile10이다. decile1 그룹은 지원금액을 할당 해야하는 중요도가 상대적으로 높을 것이고,

decile10은 중요도가 낮을 것이다.

 

이렇게 데이터를 10단계로 분할해 중요도를 파악하는 것을 decile 분석이라 하며, 사용자 특성 등 데모그래픽 한 데이터가 없을 경우, 사용자 액션으로 분석을 해보는 것이 가능하다.

 

 

2. Decile 분석 과정

 

Decile 분석 과정은 다음과 같다.

 

1)  Decile 등급을 사용금액이 높은 순으로 부여하기 위해 구매 금액이 많은 순으로 정렬한다.

2) 구매금액이 많은 상위 10% 씩 Decile1 부터 10까지 그룹을 할당한다.

3) 각 그룹별 금액 합계를 집계한다.

4) (3) 에서 구한 그룹 별 합계금액을 전체 구매 금액으로 나눠 금액비율을 계산한다.

5) Decile1부터 10까지 금액비율의 누계를 집계한다.

 

 

 

 

 

 

 

3. Decile 분석 작성 쿼리문

 

테이블명 : sql-study-project-376507.sql_study_bigquery.action_log

 

1. 정렬 후 Decile 번호부여 (분석과정 1,2에 해당)

with
  user_purchase_amount as(
    select user_id,sum(amount) as purchase_amount
      from sql-study-project-376507.sql_study_bigquery.action_log
        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
  order by decile,purchase_amount desc;

 

 

ntile -> 지정한 만큼의 범위를 그룹화 하여 순위를 부여한다.

위와 같은 경우는  purchase_amount를 기준으로 내림차순 하고(order by) 총 10개의 그룹을 만들어(ntile(10) 번호를 부여한다는 의미이다.

 

 

 

2. 이어서 Decile 그룹별 금액합계를 계산한다. (분석과정 3에 해당)

with
  user_purchase_amount as(
   	-- 위와 동일
  )
, users_with_decile as(
	-- 위와 동일 
 )
, decile_with_purchase_amount as(
  select decile,
    sum(purchase_amount) as amount, -- decile 그룹별 금액합계
    avg(purchase_amount) as avg_amount, -- decile 그룹별 금액 평균
    sum(sum(purchase_amount)) over(order by decile) as cumulative_amount, -- decile 그룹별 합계금액 누계
    sum(sum(purchase_amount)) over() as total_amount -- 전체 합계 금액
      from users_with_decile
       group by decile
)
select * from decile_with_purchase_amount
  order by decile;

 

※ 주의 !
쿼리문을 작성하며 중간 집계함수를 두번 쓰는 sum(sum(purchase_amount)) 부분을
현재 작성하는 임시 테이블에서 sum(purchase_amount) 부분 앨리어스를 amount로 하니
sum(amount)라고 하면 되지 않을까? 생각했지만
조회하고자 하는 select 쿼리가 참조하는 테이블이 users_with_decile이기 때문에 users_with_decile 테이블의 amount가 조회되어 똑같은 값이 나오지 않는다.

 

 

 

3. 마지막으로 구성비와 구성비누계를 계산한다.(분석과정 4,5 에 해당)

with
  user_purchase_amount as(
    select user_id,sum(amount) as purchase_amount
      from sql-study-project-376507.sql_study_bigquery.action_log
        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
)
, 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 users_with_decile
       group by decile
)
select decile,amount,avg_amount,
  amount / total_amount * 100 as total_ratio, -- decile 그룹별 금액 합계 / 전체 금액 합계
  cumulative_amount / total_amount * 100 as cumulative_ratio -- 누계 금액 합계 / 전체 금액 합계
    from decile_with_purchase_amount
      order by decile;

이를 통해 현 총 매출에 대해 그룹별 얼만큼 비율을 차지하는지 알 수 있습니다.

 

 

 

 

Decile 분석은 다른 특징을 가진 분석 방법으로 세분화 할 시 분석에 조금 더 유연해진다.

예를 들어 Decile 그룹번호가 높을수록 중요도는 떨어지지만 이 고객인 아직 정착되지 않은 고객을 뜻할 수도 있다.

이러한 다양한 특징을 파악하여 중요도가 낮지만 리텐션을 높히는 대책을 세워 중요도가 높은 고객이 될 수도 있으며 매출또한 상승할 수 있다.

 

 

 

 

 

 

 

끝.