[MYSQL] DATEDIFF를 사용한 Churn Rate 구하기

오늘은 어제 했던 코드들을 기반으로 해서 예제 코드를 추가적으로 진행했다.

오전 수업에서 한 내용은 고객 중에 미 주문 고객으로 얼마나 전환이 된건지 확인하기 위한 지표를 만들어봤다.

이 부분들을 알기 위해서는 거쳐야 할 과정들이 있는데 그 과정대로 작성해보겠다.

오늘 활용한 예제들은 모두  교재 SQL로 맛보는 데이터 전처리 분석(노수영 저) 를 참고했다.

추가적으로 어제 주석으로 설명을 달아둔 내용에서 큰 틀이 바뀐 것이 없어 코드 흐름에 대한 설명만 작성하겠다.

 

 

1. 구매자의 구매일자 불러오기

먼저 최초 주문일자와 마지막 주문일자를 알아야 이 부분을 응용해서 미주문 일자들이 얼마나 되는지를 알 수 있다.

그리고 이 결과를 고객 번호에 따라 하나의 값으로 묶어준다.

 

SELECT
	MAX(orderdate) mx_order -- 마지막 구매일
    , MIN(orderdate) mn_order -- 최초 구매일
FROM orders;

-- 2005/06/01 기준 각 고객의 마지막 구매일이 얼마나 소요되는지
SELECT
	customernumber
	, MAX(orderdate) '마지막 구매일'
    , MIN(orderdate) '최초 구매일'
FROM orders
GROUP BY 1;

 

 

2. DATEDIFF 사용해서 날짜 구하기

각 고객번호당 최초 주문일자와 마지막 주문일자를 구했으니,

두 날의 차이를 구해서 이용한지 얼마나 오래되었는지 날짜의 차를 구한다.

 

SELECT
	customernumber
    , MAX(orderdate) MX_ORDER -- 이 테이블의 마지막 구매일 (전 고객 기준)
                              -- GROUP BY 마지막 구매일 (각 고객 기준)
FROM orders
GROUP BY 1;

SELECT
	customernumber
    , MX_ORDER
    , '2005-06-01' -- 전 고객 대상 마지막 구매일
    , DATEDIFF('2005-06-01', MX_ORDER) DIFF
FROM(
	SELECT
		customernumber
		, MAX(orderdate) MX_ORDER
	FROM orders
	GROUP BY 1
) BASE
;

 

 

3. 기준 세우기

위에서 두 날의 차이까지 구해봤다.

이제 해야할 것은 최초 주문일자와 마지막 주문일자의 차이를 얼마 이상이면 미주문 고객으로 파악할 건지 기준을 세워야한다.

나는 예제를 그대로 사용해서 DIFF가 90일 이상으로 나오면 미주문 고객으로 전환했다.

컬럼명부터 내용까지 2005-06-01 로 지정했는데 그 이유는 저 날을 마지막 주문으로 삼고자 했기 때문이다.

이 과정에서 핵심으로 사용된게 서브쿼리이다.

메인쿼리에서는 DIFF가 90일 이상이면 Churn으로 미만이면 Non Churn으로 하는 것을 기준으로 삼았고,

서브쿼리에서는 위의 과정에서 사용한 DIFF를 구하는 과정을 사용했다.

 

SELECT 
	*
	, CASE WHEN DIFF >= 90 THEN 'CHURN' ELSE 'NON-CHURN' END Churn_type
FROM (
	SELECT
		customernumber
		, MX_ORDER
		, '2005-06-01' -- 전 고객 대상 마지막 구매일
		, DATEDIFF('2005-06-01', MX_ORDER) DIFF
	FROM(
		SELECT
			customernumber
			, MAX(orderdate) MX_ORDER
		FROM orders
		GROUP BY 1
	) BASE ) BASE2
   GROUP BY 1 
;

 

 

4. 미주문 고객들의 주로 구매한 품목 확인

장기 미주문 고객들이 앞전에 어떤 카테고리의 물품들을 주로 구매했었는지 확인해보고자 한다.

여기서도 앞전의 코드에 대한 서브쿼리가 계속 사용된다.

 

CREATE TABLE churn_list AS 
SELECT 
	CASE WHEN DIFF >= 90 THEN 'CHURN ' ELSE 'NON-CHURN' END churn_type
    , customernumber
FROM 
	(
		SELECT 
			customernumber
			, mx_order
			, '2005-06-01' END_POINT
			, DATEDIFF('2005-06-01', mx_order) DIFF
		FROM
			(
				SELECT 
					customernumber
					, max(orderdate) mx_order
				FROM orders
				GROUP BY 1
			) BASE
    ) BASE
;

SELECT * FROM churn_list;

 

 

5. 미주문 고객과 제품에 따른 주문 통계를 계산

장기 미주문 고객과 비교적 단기인 미주문 고객 그룹별로 나눠서 각 주문량이 많은 순으로 결과를 나타내고 싶다.

우선 결과를 먼저 보여주자면 아래와 같다.

 

SELECT
	D.churn_type
	, C.productline
    -- BU : 각 그룹 내에서 고유한 고객의 수
    , COUNT(DISTINCT B.customernumber) BU
FROM orderdetails A

-- orderdetails와 orders 테이블을 ordernumber 열을 기준으로 조인하고, A.ordernumber와 B.ordernumber가 일치하지 않는 경우에도 결과를 반환함
LEFT
JOIN orders B
ON A.ordernumber = B.ordernumber

-- orderdetails 테이블(A)과 products 테이블(C)을 productcode 열을 기준으로 조인하며, 제품 정보를 주문 상세 정보와 연결함
LEFT
JOIN products C
ON A.productcode = C.productcode

-- orders 테이블(B)과 churn_list 테이블(D)을 customernumber 열을 기준으로 조인하며, 고객 이탈 정보를 주문 정보와 연결함
LEFT
JOIN churn_list D
ON B.customernumber = D.customernumber
-- 같은 이탈 유형 및 제품 라인을 가진 행을 그룹화함
GROUP BY 1,2
--  이탈 유형별로 가장 많은 주문을 한 고객을 상위에 표시
ORDER BY 1,3 DESC
;

 

 

이것으로 오늘 오전 수업 때 배운 내용의 복기 끝!