[SQL을 이용해 데이터 분석하기] 고객의 구매 현황 및 패턴 분석하기 (재구매율 및 구매주기 분석)
2022. 2. 7. 05:29ㆍ독학으로 취업 문 뿌수기/SQL
728x90
반응형
SMALL
재구매율 및 구매주기를 분석하기 위해, 기준을 정해야 한다.
- 재구매자: 최초 구매일 이후, +1일 후 구매자
- 구매주기: 구매간격(최근구매일자 - 최초구매일자) / (구매횟수 -1)
1. 분석 전 회원 프로파일 분석용 데이터 마트 생성하기
- FROM절 서브 쿼리 테이블(회원번호, 최초 및 최근 구매일자, 구매 횟수, 재구매 여부, 구매 간격, 구매 주기)
2. 데이터 마트를 활용해, 분석 보고서 작성
- 재구매 회원수 비중(%)
- 평균 구매주기 및 구매주기 구간별 회원수
<재구매율 및 구매주기 분석용 데이터 마트>
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
)AS A;
1. 재구매 회원수 비중(%)
SELECT COUNT(DISTINCT MEM_NO) AS 구매회원수
,COUNT(DISTINCT CASE WHEN 재구매여부 = 'Y' THEN MEM_NO END) AS 재구매회원수
FROM RE_PUR_CYCLE;
2. 평균 구매주기
SELECT AVG(구매주기)
FROM RE_PUR_CYCLE
WHERE 구매주기 > 0;
2-1. 구매주기 구간별(FROM절) 회원수
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
)AS A
GROUP
BY 구매주기_구간;
엑셀 형식의 분석 보고서
728x90
반응형
LIST
'독학으로 취업 문 뿌수기 > SQL' 카테고리의 다른 글
[SQL을 이용해 데이터 분석하기] 제품의 가치 분석하기 (제품 성장률 분석) (0) | 2022.02.07 |
---|---|
[SQL을 이용해 데이터 분석하기] 고객 가치 평가모형 분석 (RFM 분석) (0) | 2022.02.07 |
[SQL을 이용해 데이터 분석하기] 회원 프로파일 분석 (0) | 2022.02.07 |
기초 데이터 분석을 위한 핵심 SQL 완전 정복 (0) | 2022.02.06 |