SQL 문법을 몇 가지 살펴봤었다.
문제를 풀어보자.
문제: 국가 별 재구매율 구하기
재구매율은 특정 기간 동안 이전에 구매한 고객이 다시 제품이나 서비스를 구매하는 비율을 나타낸다.
일반적으로 재구매율은 특정 기간 동안의 재구매 건수를 전체 구매 건수로 나눈 후 백분율로 계산한다.
사용하는 데이터는 MYSQL SampleDATA 이다.
전체 플로우를 생각해보면.
- 과거주문 A와 현재주문 B를 비교.
- 날짜를 확인하니
DATE와주문자관련 열 확인. 같은 주문자가 이후 다시 주문했는지 확인.
- 날짜를 확인하니
전체 재구매율 계산하기
- 국가별 재구매율 계산하기
우선 해당 데이터를 가져온 후.
1
USE classicmodels;
0. ERD
ERD를 얼추 살펴보니
- orders : customerNumber, orderDate
가 필요하다.
같은 테이블에서 같은 주문자를 찾아야 하니 SELF JOIN 한다.
1. 과거주문 A와 현재주문 B를 비교
같은 주문자가 1년 후 다시 구매했는지 확인하려 한다.
즉 A 주문과 B 주문의 구매자가 동일하고, 구매 기간은 1년이 차이나야 한다.
해당 내용을 코드로 작성하면
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT
A.customerNumber
, A.orderDate
, B.orderDate
FROM orders A
LEFT JOIN orders B
ON A.customerNumber = B.customerNumber
AND SUBSTR(A.orderDate,1,4) = SUBSTR(B.orderDate,1,4) -1
--A의 고객과 B의 고객이 같은 경우. 즉, 같은 고객의 A(구매연도) == B(구매연도)-1(즉 1년뒤) 이 같은 경우를 찾는다.
LIMIT 5
;
LEFT JOIN으로 동일한 테이블을 JOIN하고 키는 고객번호, 그리고 구매 년 1년 차이를 확인한다.
어떤 고객은 2004년에 차를 두 번씩 샀나보다.
2. 전체 재구매율 계산하기
1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT
COUNT(DISTINCT B.customerNumber) / COUNT(DISTINCT A.customerNumber) AS R_Rate
-- (재구매고객) / (전체고객)
FROM orders A
LEFT JOIN orders B
ON A.customerNumber = B.customerNumber
AND SUBSTR(A.orderDate,1,4) = SUBSTR(B.orderDate,1,4) -1
LIMIT 5
;
전체고객의 약 77%는 자동차를 1년 이내에 재 구매 했다.
1년마다 자동차를 바꾸다니.. 부럽다.
3. 국가별 재구매율 계산하기
어떤 나라의 구매자가 가장 재구매율이 높을까?
찾아보자
그 전에 다시 한번 ERD를 살펴보면
구매고객의 국가는 customers 테이블의 country 열에 있다.
다행히 현재 사용하는 order 테이블의 cusetomerNumber과 JOIN 할 수 있겠다.
country 로 GROUP BY 하고 RANK()로 가장 재구매율이 높은 국가를 찾아보자.
하다보니 궁금해졌으니 YEAR 로도 나누어보자.
즉 연도별 국가 재구매율!
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SELECT
*
, RANK()OVER(ORDER BY R_Rate DESC) AS RANK_R
FROM (
SELECT
country
, SUBSTR(A.orderDate,1,4) AS Year_R
, COUNT(DISTINCT B.customerNumber) / COUNT(DISTINCT A.customerNumber) AS R_Rate
FROM orders 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 C
ON A.customerNumber = C.customerNumber
GROUP BY 1, 2
) A
;
생각보다 재구매율이 너무 높아서 100% 가 아닌 국가 중 가장 높은 국가를 살펴봤다.
무려 18개의 국가+연도가 재구매율을 100% 달성, 그 다음은 2003년의 프랑스가 약 87%로 19위로 실질적인 2위이다.
연도를 빼고 종합 계산하면?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT
*
, RANK()OVER(ORDER BY R_Rate DESC) AS RANK_R
FROM (
SELECT
country
, COUNT(DISTINCT B.customerNumber) / COUNT(DISTINCT A.customerNumber) AS R_Rate
FROM orders 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 C
ON A.customerNumber = C.customerNumber
GROUP BY 1
) A
;
여전히 100% 달성국가가 많다.
무려 9개국!
서비스가 엄청 좋은가보다.
2위 국가는 83%의 프랑스, 3위 국가는 80%의 영국이다.
번외) 국가별 재 구매인원수 계산하기.
그런데 Retention Rate가 높다고 시장이 크다는 이야기는 아니다.
한 국가에서 한 명이 구매+재구매하는 것과 백 명 중 80명이 구매+재구매 하는건 다르니까.
이번엔 실제 구매 고객의 수를 파악해보자!
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SELECT
country
, SUBSTR(A.orderDate,1,4) AS Year_R
, COUNT(DISTINCT B.customerNumber) / COUNT(DISTINCT A.customerNumber) AS R_Rate
, COUNT(DISTINCT A.customerNumber) AS ALL_Cus
, COUNT(DISTINCT B.customerNumber) AS R_Cus
FROM orders 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 C
ON A.customerNumber = C.customerNumber
GROUP BY 1, 2
;
전체 구매고객과 재구매 고객 데이터다.
재구매 고객 기준 정렬하면
로 미국이 실질적으로 가장 큰 시장임을 알 수 있다.
재구매율로만 봤을때와 다른 결과이다.
2004년과 2005년의 결과가 낮은 이유는, 해당 데이터가 2005년 중순에 마지막으로 업데이트 되어 실질적인 재구매율은 2003년 데이터를 봐야한다.