SQL - Retention Rate, Churn Rate

• 이번 글에서는 마케팅의 주요 지표인 Retention Rate(재구매율)와 Churn Rate(이탈율)을 SQL로 파악해보려고 한다.

교재 구매

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

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

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

Retention Rate(재구매율)

• 아래 코드를 통해 각 나라의 연도별 재구매율을 알 수 있다.

-- 국가 거주 구매자 중 다음 연도에서 구매를 한 구매자의 비중
select
    C.country
    , substr(A.orderdate, 1, 4) YY
    , count(distinct A.customerNumber) BU_1
    , count(distinct B.customerNumber) BU_2
    , count(distinct B.customerNumber) / count(distinct A.customerNumber) 재구매율
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;

Chat gpt를 활용한 코드 설명

• 이 MySQL 쿼리는 다른 국가에 거주하는 고객('국가 거주 구매자')의 재구매율(재구매율)을 계산하는 것으로 보입니다. 이 쿼리는 '주문' 테이블과 '고객' 테이블을 조인하여 2년 연속 구매를 한 고객을 찾습니다. 다음은 쿼리에 대한 설명입니다
• SELECT
  • C.country: 고객의 거주 국가를 나타냅니다.
  • substr(A.orderdate, 1, 4) YY: 'orders' 테이블의 주문 날짜 열에서 연도를 추출합니다.
  • count(distinct A.customerNumber) BU_1: 현재 연도의 'orders' 테이블('A'로 별칭 지정)에서 고유 고객 번호를 계산합니다.
  • count(distinct B.customerNumber) BU_2: 전년도의 'orders' 테이블('B'로 별칭 지정)에서 고유 고객 번호를 계산합니다.
  • count(distinct B.customerNumber) / count(distinct A.customerNumber) 재구매율: 전년도(BU_2)의 고유 고객 번호 수를 현재 연도(BU_1)의 고객 번호 수로 나누어 재구매율을 계산합니다.
• FROM
  • 고객 번호가 일치해야 하고 'A'의 주문 연도가 'B'의 연도보다 1년 앞서야 한다는 조건을 사용하여 'A'와 'B'를 조인합니다.
  • 또한 '고객' 테이블('C')을 조인하여 고객 번호를 기준으로 거주 국가를 검색합니다.
• GROUP BY
  • 'C.country'(거주 국가)와 주문 연도(YY)별로 그룹화되어 있어 시간 경과에 따른 여러 국가 고객의 재구매율을 확인할 수 있습니다.

Churn Rate(이탈율)

• 아래 코드를 통해 전체 고객들 중 이탈(Churn)고객과 이탈하지 않은(Non Churn)고객의 숫자를 알 수 있고 이를 이용해 고객의 이탈율을 파악해 볼 수 있다.

-- Churn Rate 1)
select
    case when DIFF >= 90 then 'Churn'
    else 'Non Churn'
        end CHURN_TYPE
    , count(distinct customernumber) N_CUS
from(
    select
        customernumber
        , MX_ORDER
        , '2005-06-01'
        , DATEDIFF('2005-06-01', MX_ORDER) DIFF
    from(
        select
            customernumber
            , MAX(orderdate) MX_ORDER 
        from orders
        group by 1) BASE1
) BASE2
group by 1;

-- 2)
select 69/(69+29);

Chat gpt를 활용한 코드 설명

• 1)번 MySQL 쿼리는 주문 내역을 기반으로 고객의 이탈률을 계산하기 위한 것입니다. 다음과 같은 구조로 되어 있습니다:
• SELECT
  • CHURN_TYPE: 이 열은 조건에 따라 고객을 '이탈' 또는 '비이탈'로 분류하기 위해 CASE 문을 사용합니다. 고정 날짜('2005-06-01')와 최대 주문 날짜(MX_ORDER) 간의 차이(DIFF)가 90일 이상인 경우 고객은 '이탈'로 분류됩니다. 그렇지 않으면 '비 이탈'로 분류됩니다.
  • N_CUS: 이 열은 각 이탈 범주에 속하는 고유 고객의 수를 계산합니다.
• FROM
  • 가장 안쪽 하위 쿼리(BASE1)는 MAX() 함수를 사용하여 'orders' 테이블에서 각 고객(고객 번호)의 최신 주문 날짜(MX_ORDER)를 검색하고 결과를 고객별로 그룹화합니다.
  • 중간 하위 쿼리(BASE2)는 BASE1의 결과를 기반으로 합니다. 이 쿼리는 고객 번호 MX_ORDER를 선택하고 날짜 '2005-06-01'을 하드코딩한 다음 각 고객에 대해 '2005-06-01'과 MX_ORDER 사이의 일수 차이(DIFF)를 계산합니다. 이는 기본적으로 '2005-06-01'을 기준으로 고객의 마지막 주문 이후 경과한 일수를 계산합니다.
• GROUP BY
  • 결과는 '이탈' 또는 '비이탈' 범주인 CHURN_TYPE에 따라 그룹화됩니다.
• 2)번 MySQL 쿼리에서 69는 이탈한 고객 수를 나타내고 29는 이탈하지 않은 고객 수를 나타내는 간단한 계산입니다. 이 계산의 결과는 소수로 표시되는 이탈률입니다.