SQL - 코호트 분석, RFM

• 이번 시간에는 마케팅에서 주로 활용되는 코호트 분석과 RFM을 SQL을 통해 알아보려고 한다.

교재 구매

• 아래 교재내용을 토대로 진행하겠습니다.
• 기본 DB설정은 교재의 내용을 참고하시기 바랍니다.

https://www.yes24.com/Product/Goods/86544423

SQL로 맛보는 데이터 전처리 분석 - 예스24

코호트 분석

• 코호트 분석이란 사용자를 공통된 특성을 가진 그룹으로 나누고 시간 흐름에 따른 사용자 행동을 수치화해 분석하는 기법을 말한다. 이때 주로 리텐션, 구매 패턴, 행동 패턴등을 다루게 된다.
• 아래 코드를 통해 최초 구매 월과 구매 간격을 기준으로 하여 각 코호트(공통 특성 집단)의 리텐션과 매출액을 구할 수 있다.

select
    substr(mndt, 1, 7) mm
    , timestampdiff(month, mndt, invoicedate) datediff
    , count(distinct a.customerid) bu
    , sum(sales) sales
from
    (select customerid, min(invoicedate) mndt from dataset3 group by 1) a
    left join (select customerid, invoicedate, UnitPrice*Quantity sales from dataset3) b
    on a.customerid = b.customerid
group by 1, 2;

Chat gpt를 활용한 코드 설명

• 제공된 MySQL 쿼리는 시간 경과에 따른 고객 행동과 관련된 메트릭, 특히 첫 구매(mndt)와 후속 구매 사이의 월을 계산하기 위한 몇 가지 SQL 작업이 포함됩니다.
• 서브쿼리 A
  • 이 서브 쿼리는 데이터셋3의 각 고객(customerid)에 대한 최소 인보이스 날짜(mndt)를 계산합니다.
  • GROUP BY를 사용하여 customerid를 기준으로 데이터를 그룹화하고 MIN 함수를 사용하여 각 고객의 가장 빠른 인보이스 날짜를 검색합니다.
• 서브쿼리 B
  • 이 서브 쿼리는 판매와 관련된 데이터를 선택하는 데 사용됩니다.
  • 여기에는 데이터세트3의 각 레코드에 대한 고객 ID, 송장 날짜 및 계산된 판매 값(단가*수량)이 포함됩니다.
• 메인쿼리
  • 메인쿼리는 하위 쿼리 'a'와 'b'의 결과를 좌측 조인을 사용하여 결합합니다. 즉, 하위 쿼리 'a'의 모든 레코드(고객당 최소 인보이스 날짜)가 하위 쿼리 'b'의 일치하는 레코드와 함께 결과에 포함됩니다.
  • 조인은 customerid 필드에서 수행되어 고객을 각 구매 데이터와 연결합니다.
• SELECT 절
  • substr(mndt, 1, 7) mm: 이 함수는 mndt(최소 송장 날짜) 필드의 처음 7자를 추출하여 효과적으로 YYYY-MM 형식으로 서식을 지정합니다. 이 필드의 이름은 'mm'로 바뀝니다.
  • TIMESTAMPDIFF(월, MNDT, 인보이스 날짜) DATEDIFF: 고객의 첫 구매와 후속 구매 사이의 개월 수를 나타내는 최소 인보이스 날짜(mndt)와 b의 인보이스 날짜 사이의 차이(월)를 계산합니다.
  • count(distinct a.customerid) bu: 결과에서 고유한 고객 ID의 수를 계산합니다. 쿼리 결과에 데이터가 포함된 고유 고객 수를 효과적으로 계산합니다.
  • sum(sales) sales: 각 그룹의 총 매출을 계산합니다. 각 그룹에 대한 하위 쿼리 'b'의 판매 값을 합산합니다.
• GROUP BY 절
  • 결과 집합은 SELECT 절의 처음 두 열, 즉 'mm'(형식이 지정된 인보이스 월) 및 'datediff'(월 차이)를 기준으로 그룹화됩니다. 이렇게 하면 특정 기간 내 고객 행동에 따라 통계를 집계하도록 데이터가 그룹화됩니다.

## RFM

- RFM이란 R(Rencency : 최근 구매일), F(Frequency : 구매 횟수), M(Monetary : 구매 금액 합계)의 약자로 사용자별로 얼마나 최근에/자주/많은 금액을 지출했는지에 따라 사용자를 분류할 수 있는 분석기법이다. 이를 통해 구매 가능성이 높은 고객을 선정할 수 있게 된다.

- 아래 코드를 통해 기준 날짜는 '2011-12-02'으로 하여 각 고객별 RFM을 구할 수 있다.

select
    customerid
    , datediff('2011-12-02', mxdt) recency
    , frequency
    , moneytary
from (
    select
        customerid
        , max(invoicedate) mxdt
        , count(distinct invoiceno) frequency
        , sum(quantity * unitprice) moneytary
    from dataset3
    group by 1
) a;

Chat gpt를 활용한 코드 설명

• 제공된 MySQL 쿼리는 데이터 집합의 고객 데이터를 분석하여 각 고객에 대한 세 가지 중요한 지표인 최근성, 빈도 및 금전적 가치를 계산하도록 설계되었습니다. 이러한 메트릭은 일반적으로 마케팅 및 고객 관계 관리에서 고객을 이해하고 세분화하는 데 사용됩니다.
• 서브쿼리
  • 서브쿼리는 각 고객에 대한 일부 집계 통계를 계산합니다. 이 쿼리는 데이터세트3 테이블에서 데이터를 선택하고 고객 ID를 기준으로 그룹화합니다. 이 하위 쿼리의 목적은 각 고객의 활동에 대한 요약을 만드는 것입니다.
  • customerid : 각 고객의 고유 식별자입니다.
  • max(invoicedate) mxdt : 각 고객의 최대(가장 최근) 인보이스 날짜를 계산하여 각 고객의 가장 최근 거래 날짜를 효과적으로 찾습니다.
  • count(distinct invoiceno) frequency : 각 고객에 대한 고유 인보이스(거래) 수를 계산하여 구매 빈도를 측정합니다.
  • sum(quantity * unitprice) moneytary : 각 고객이 지출한 총 금전적 가치를 계산합니다. 인보이스에 있는 각 항목의 수량과 단가를 곱한 다음 이 값을 합산하여 총 지출액을 얻습니다.
• 메인쿼리
  • 메인 쿼리는 서브 쿼리의 결과를 가져와서 추가로 처리합니다. 최종 결과를 위해 다음 열을 선택합니다.
  • customerid: 각 고객의 고유 식별자입니다.
  • datediff('2011-12-02', mxdt) 최근: 각 고객에 대한 최근 메트릭을 계산합니다. 고정 날짜 '2011-12-02'에서 가장 최근 거래 날짜(mxdt)를 뺍니다. 이는 고객의 마지막 구매 이후 일 수를 제공하여 고객이 얼마나 최근에 구매했는지를 나타냅니다.
  • frequency: 서브 쿼리에서 직접 가져온 값으로, 각 고객에 대한 고유한 인보이스 수(구매 빈도)를 나타냅니다.
  • moneytary: 이 역시 서브 쿼리에서 직접 가져오며, 각 고객이 지출한 총 금전적 가치를 나타냅니다.