클래식 리텐션, Stickiness, 롤링리텐션 My sql

월별 클래식 리텐션 구하기 

WITH a AS (
SELECT r.customer_id
      ,r.order_id
      ,r.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 r 
    LEFT JOIN customer_stats c ON c.customer_id=r.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)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 a
GROUP BY first_order_month
ORDER BY first_order_month

 

온라인 쇼핑몰의 Stickiness

 

 

 DAU 구하기

SELECT order_date as dt 
     , COUNT(DISTINCT customer_id) as dau
FROM records
WHERE order_date BETWEEN '2020-11-01' AND '2020-11-30'
GROUP BY dt

 

WAU, Stickiness

WITH a AS(
SELECT order_date as dt 
     ,DATE_SUB(order_date, INTERVAL 6 DAY)days
     , COUNT(DISTINCT customer_id) as dau
FROM records
GROUP BY dt,days)

SELECT a.dt as dt 
      ,a.dau
      ,COUNT(DISTINCT customer_id) as wau
      ,ROUND( a.dau/COUNT(DISTINCT customer_id),2) as stickiness
FROM records r
     JOIN a ON r.order_date BETWEEN a.days AND a.dt
GROUP BY a.dt,a.dau
HAVING dt BETWEEN '2020-11-01' AND '2020-11-30'
ORDER BY dt

• 조인을 = 가 아닌 BETWEEN AND 로 연결하는 방식 이용 
• BETWEEN a AND b 할때 a와 b 컬럼이 내가 비교하고자 하는 같은 테이블에 있는 것을 해야 함. orderdate는 이미 dt와 달라진 값! 
• ROUND 안에 알리야스가 있으면 오류하 나지! 
• WITH 절이 복잡한 쿼리 할때는 편하다. 

 

월별 주문 리텐션 (롤링 리텐션)

WITH a AS (
SELECT r.customer_id
      ,r.order_id
      ,r.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 r 
    LEFT JOIN customer_stats c ON c.customer_id=r.customer_id
            )

SELECT first_order_month 
      ,COUNT(DISTINCT customer_id)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)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 a      
GROUP BY first_order_month     
ORDER BY first_order_month