[SQL을 이용해 데이터 분석하기] 고객의 구매 현황 및 패턴 분석하기 (재구매율 및 구매주기 분석)

---

재구매율 및 구매주기를 분석하기 위해, 기준을 정해야 한다.

• 재구매자: 최초 구매일 이후, +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  구매주기_구간;

---

---

엑셀 형식의 분석 보고서

재구매율 및 구매주기 분석.xlsx

0.02MB

---