[자동차 매출 데이터] Churn Rate 구하기

본 포스팅은 아래 도서의 내용을 스터디, 참고했습니다.



✅ Churn Rate 란?

활동 고객 중 얼마나 많은 고객이 비활동 고객으로 전환되었는지를 의미하는 지표이다.

기업에서는 이탈 고객 예측과 예측 결과를 바탕으로 적절한 마케팅 전략을 취하고자 노력하므로 Churn Rate 가 중요하다.

Churn Rate는 일반적으로 다음과 같이 정의한다.

max(구매일, 접속일) 이후 일정 기간동안 구매/접속을 하지 않은 상태
보통 90일(3개월)을 기준으로 Churn Rate을 구한다.
마지막 구매, 접속일이 현 시점 기준 3개월이 지난 고객이 이탈 고객이 되는 것이다.

 

1. Churn Rate 구하기

최종 쿼리의 구조

 

• Churn Rate의 기준은 마지막 구매일이다. 따라서 각 고객의 마지막 구매일을 먼저 구해야 한다.

select customerNumber, max(orderdate) as last_order_date
from orders
group by 1

 

• 다음으로, 현 시점(2005년 6월 1일로 가정) 기준으로 며칠이 소요되었는지 계산한다.
• 서브 쿼리를 이용해서 앞 단계에서 구한 데이터셋을 활용한다.
• 이때 날짜 차이를 계산하기 위해 DATEDIFF() 함수를 사용한다.
• DATEDIFF(date1, date2) = date 1 - date 2 계산

select customerNumber, last_order_date
		, '2005-06-01' as end_point
        , datediff('2005-06-01', last_order_date) as diff
from 
 ( select customerNumber
          , max(orderdate) as last_order_date
   from orders
   group by 1
 ) a

중간 결과 확인

 

• 이제 diff 가 90일 이상인 경우를 구분해보자.
• 서브 쿼리를 이용해서 앞 단계에서 구한 데이터셋을 활용한다.
• case when을 사용하여 diff가 90일 이상이면 CHURN, 아니면 NonChurn이라 하자

select *, case when diff >= 90 then 'CHURN' else 'Non-churn' end as churn_type
from (
	select customerNumber, last_order_date
		, '2005-06-01' as end_point
        , datediff('2005-06-01', last_order_date) as diff
    from 
	 ( select customerNumber
			  , max(orderdate) as last_order_date
	   from orders
       group by 1
     ) a
) a
;

결과 확인

 

• 마지막으로 CHURN과 Non-churn 각각에 해당하는 고객수를 각각 계산하면 churn rate를 구할 수 있다.
• 결과를 보면 약 70%의 이탈이 발생하고 있음을 확인할 수 있다.

select case when diff >= 90 then 'CHURN' else 'Non-churn' end as churn_type
	, count(DISTINCT customernumber) as N_cus
from (
	select customerNumber, last_order_date
		, '2005-06-01' as end_point
        , datediff('2005-06-01', last_order_date) as diff
    from 
	 ( select customerNumber
				, max(orderdate) as last_order_date
	   from orders
       group by 1
     ) a
) a
group by 1
;