[자동차 매출 데이터] 연도별 재구매율

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

 

 

재구매율은 특정 기간 구매자 중, 특정 기간에 연달아 구매한 구매자의 비중이다.

연도별 재구매율을 구해 보자.

 

최종 쿼리와 결과

select country, substr(a.orderDate, 1, 4) as year
		,count(distinct a.customerNumber) as BU_1
        ,count(distinct b.customerNumber) as BU_2
        ,count(distinct b.customerNumber) / count(distinct a.customerNumber) as retention_rate
from orders as a
left join orders b on a.customernumber = b.customernumber
					and substr(a.orderDate, 1, 4) = substr(b.orderDate, 1, 4) -1
left join customers as c on c.customerNumber = a.customerNumber
group by 1, 2
;

 

1단계 :  필요한 데이터 불러오기

• 재구매율의 핵심은 '기간'이다. 기간을 기준으로 구매 여부(주문한 고객번호)만 확인하면 되는 것이다. 
• 기간 차를 확인하기 위해 orders 테이블과 orders 테이블을 join 하게 된다.
• 먼저, 기본적으로 공통된 고객 번호를 기준으로 join 하기 위해 customerNumber로 join 한다.
• 여기에 join 조건을 하나 더 걸어주는데, 연도를 기준으로 1년 차이가 나게 컬럼을 배열하기 위해 다음과 같이 작성한다.

select a.customernumber, a.orderDate, b.customernumber, b.orderDate
from orders as a
left join orders b on a.customernumber = b.customernumber
					and substr(a.orderDate, 1, 4) = substr(b.orderDate, 1, 4) -1
;

 

 

2단계 :  국가/연도별로 재구매율 도출

• 국가와 연도(기준연도는 a 로 지정한 Orders 테이블의 orderDate)를 기준으로 group by
• 기준 연도의 고객 수를 합산 : count(distinct   )를 사용
• 1년 후 연도의 고객 수를 합산
• 재구매율 : 1년 후 고객 수 / 기준 연도 고객수를 나누어 재구매율을 계산