[AARRR] MySQL - 클래식/롤링 리텐션Retention 구하기

MySQL로 클래식, 롤링 리텐션을 구해보겠습니다.

 

solvesql의 '월별 주문 리텐션' 문제를 참고했으며,

원본 데이터는 Kaggle 'United States E-Commerce records 2020'입니다.

 

클래식 리텐션

WITH records_preprocessed AS (
  SELECT r.customer_id
     , r.order_id
     , r.order_date
     , c.first_order_date
     , date_format(r.order_date, '%Y-%m-01') as order_month
     , date_format(c.first_order_date, '%Y-%m-01') as first_order_month
  FROM records as r
  inner join customer_stats as c on r.customer_id = c.customer_id
)



SELECT first_order_month
     , count(distinct customer_id) as month0
     , count(distinct case when DATE_ADD(first_order_month, INTERVAL 1 MONTH) = order_month then customer_id end) month1
     , count(distinct case when DATE_ADD(first_order_month, INTERVAL 2 MONTH) = order_month then customer_id end) month2
     , count(distinct case when DATE_ADD(first_order_month, INTERVAL 3 MONTH) = order_month then customer_id end) AS month3
     , count(distinct case when DATE_ADD(first_order_month, INTERVAL 4 MONTH) = order_month then customer_id end) month4
     , count(distinct case when DATE_ADD(first_order_month, INTERVAL 5 MONTH) = order_month then customer_id end) month5
     , count(distinct case when DATE_ADD(first_order_month, INTERVAL 6 MONTH) = order_month then customer_id end) month6
     , count(distinct case when DATE_ADD(first_order_month, INTERVAL 7 MONTH) = order_month then customer_id end) month7
     , count(distinct case when DATE_ADD(first_order_month, INTERVAL 8 MONTH) = order_month then customer_id end) month8
     , count(distinct case when DATE_ADD(first_order_month, INTERVAL 9 MONTH) = order_month then customer_id end) month9
     , count(distinct case when DATE_ADD(first_order_month, INTERVAL 10 MONTH) = order_month then customer_id end) month10
     , count(distinct case when DATE_ADD(first_order_month, INTERVAL 11 MONTH) = order_month then customer_id end) month11

FROM records_preprocessed
group by first_order_month

 

classic_retention.xlsx

0.01MB

롤링 리텐션

WITH CUSTOMERS AS (
  SELECT c.customer_id
       , c.first_order_date
       , c.last_order_date
       , date_format(c.first_order_date, "%Y-%m-01") as first_order_month
       , date_format(c.last_order_date, "%Y-%m-01") as last_order_month
       , date_format(r.order_date, '%Y-%m-01') as order_month
  from customer_stats as c
  inner join records as r on c.customer_id = r.customer_id
)

SELECT C.FIRST_ORDER_MONTH 
     , count(distinct customer_id) as month0
     , count(distinct case when DATE_ADD(first_order_month, INTERVAL 1 MONTH) <= last_order_month then customer_id end) month1
     , count(distinct case when DATE_ADD(first_order_month, INTERVAL 2 MONTH) <= last_order_month then customer_id end) month2
     , count(distinct case when DATE_ADD(first_order_month, INTERVAL 3 MONTH) <= last_order_month then customer_id end) month3
     , count(distinct case when DATE_ADD(first_order_month, INTERVAL 4 MONTH) <= last_order_month then customer_id end) month4
     , count(distinct case when DATE_ADD(first_order_month, INTERVAL 5 MONTH) <= last_order_month then customer_id end) month5
     , count(distinct case when DATE_ADD(first_order_month, INTERVAL 6 MONTH) <= last_order_month then customer_id end) month6
     , count(distinct case when DATE_ADD(first_order_month, INTERVAL 7 MONTH) <= last_order_month then customer_id end) month7
     , count(distinct case when DATE_ADD(first_order_month, INTERVAL 8 MONTH) <= last_order_month then customer_id end) month8
     , count(distinct case when DATE_ADD(first_order_month, INTERVAL 9 MONTH) <= last_order_month then customer_id end) month9
     , count(distinct case when DATE_ADD(first_order_month, INTERVAL 10 MONTH) <= last_order_month then customer_id end) month10
     , count(distinct case when DATE_ADD(first_order_month, INTERVAL 11 MONTH) <= last_order_month then customer_id end) month11
FROM CUSTOMERS AS C
group by first_order_month
order by first_order_month

rolling_retention.xlsx

0.02MB