[SQL] 재구매율 및 구매주기 분석

* 재구매율 및 구매주기 분석 기준
- 재구매율 및 구매주기를 분석하기 위해서는 먼저 기준을 정해야 한다.
- 재구매자: 최초 구매일 이후, +1일 후 구매자
- 구매주기: 구매간격(최근 구매일자 - 최초 구매일자) / (구매횟수 - 1)

1. 데이터 테이블 생성
- DATE_ADD: 시간 더하기. 앞에는 기준 날짜를 넣고 그 다음에는 더하고 싶은 시간. 즉, INTERVAL (SECOND~YEAR까지 모두 가능)
- DATEDIFF: 날짜 차이 구하기

CREATE TABLE RE_PUR_CYCLE AS
SELECT *,
CASE WHEN DATE_ADD(최초구매일자, INTERVAL +1 DAY) <= 최근구매일자 THEN 'Y' ELSE 'N' END AS 재구매여부,
DATEDIFF(최근구매일자, 최초구매일자) AS 구매간격,
CASE WHEN 구매횟수 - 1 = 0 OR DATEDIFF(최근구매일자, 최초구매일자) = 0 THEN 0
ELSE DATEDIFF(최근구매일자, 최초구매일자) / (구매횟수 - 1) END AS 구매주기
FROM
(SELECT MEM_NO,
MIN(ORDER_DATE) AS 최초구매일자,
MAX(ORDER_DATE) AS 최근구매일자,
COUNT(ORDER_NO) AS 구매횟수
FROM SALES
WHERE MEM_NO <> '9999999'
GROUP BY MEM_NO) A;

결과 테이블

2. 재구매 회원수

SELECT COUNT(DISTINCT MEM_NO) AS 구매회원수,
COUNT(DISTINCT CASE WHEN 재구매여부 = 'Y' THEN MEM_NO END) AS 재구매회원수
FROM RE_PUR_CYCLE;

결과 테이블

3. 평균 구매주기 및 구매주기 구간별 회원수

SELECT AVG(구매주기) AS 평균_구매주기
FROM RE_PUR_CYCLE
WHERE 구매주기 > 0;

결과 테이블

SELECT 구매주기_구간, COUNT(MEM_NO) AS 회원수
FROM
(SELECT *,
CASE WHEN 구매주기 <=7 THEN '7일 이내'
WHEN 구매주기 <=14 THEN '14일 이내'
WHEN 구매주기 <=21 THEN '21일 이내'
WHEN 구매주기 <=28 THEN '28일 이내'
ELSE '29일 이후' END AS 구매주기_구간
FROM RE_PUR_CYCLE
WHERE 구매주기 > 0) A
GROUP BY 구매주기_구간;

결과 테이블