Declie 분석

Declie 분석 

데이터를 10단계로 분할해서 중요도를 파악하는 방법 

(Decile 은 10분의 1 의미) 

 

[데이터]

Decile 분석과정 

1. 사용자를 구매 금액이 많은 순으로 정렬 

2. 정렬된 사용자 상위 부터 10%씩 Declie 1 부터 Decile 10 까지의 그룹을 할당

3. 각 그룹의 구매 금액 합계를 집계

4. 전체 구매 금액에 대해 각 Decile의 구매 금액 비율를 계산 

5. 상위에서 누적으로 어느 정도의 비율을 차지하는지 구성비누계를 집계 

 

 

구매액이 많은 순서로 사용자 그룹을 10등분하는 쿼리 

# 구매액이 많은 순서로 사용자 그룹을 10등분하는 쿼리 

WITH user_purchase_amount AS ( 
    SELECT 
       user_id 
      ,SUM(amount) AS purchase_amount 
    FROM `sqltest-312808.test8.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)
    FROM user_purchase_amount 

)

SELECT * 
FROM user_with_decile

 

10분할한 Decile 들을 집계하는 쿼리 

WITH user_purchase_amount AS ( 
    SELECT 
       user_id 
      ,SUM(amount) AS purchase_amount 
    FROM `sqltest-312808.test8.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
ORDER BY decile 

 

구매액이 많은 Decile 순서로 구성비와 구성비누계를 계산하는 쿼리 

WITH user_purchase_amount AS ( 
    SELECT 
       user_id 
      ,SUM(amount) AS purchase_amount 
    FROM `sqltest-312808.test8.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  
  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 
 ORDER BY decile;

Decile 의 특징을 다른 분석 방법으로 세분화해서 조사하면 사용자의 속성을 자세하게 파악할 수 있다. 

예를 들어 Decile 7~10은 정착되지 않는 고객을 나타나며, 메일 매거진 등으로 리텐션을 높이는 대책을 세울 수 있다.

 

Reference

데이터 분석을 위한 SQL 레시피