OLIST 코호트 분석 (리텐션)

구너탱·2023년 11월 14일
CohortRetentionsql데이터 분석 프로젝트리텐션분석 프레임워크이커머스코호트코호트 분석
0

분석 프레임워크

목록 보기
1/3

Olist dataset은 실제 비즈니스 데이터를 임의 추출하여 익명화하며 가공된 데이터로, 제공된 Olist dataset 상으로 재구매율과 리텐션이 1% 미만으로 매우 낮아 코호트 분석으로 유의미한 결론을 도출하기에 적합하지 않다고 판단되었음. 따라서, 아쉽지만 아래 진행하던 프로젝트를 중단하고 퍼널 분석으로 전환하기로 결정하였음.

  • 필요한 테이블 전체 JOIN 쿼리 (참고용)
    SELECT *
FROM olist_orders_dataset AS o
LEFT JOIN olist_customers_dataset AS c ON o.customer_id = c.customer_id
LEFT JOIN olist_order_items_dataset AS oi ON o.order_id = oi.order_id
LEFT JOIN olist_products_dataset AS p ON oi.product_id = p.product_id
LEFT JOIN product_category_name_translation AS pt ON p.product_category_name = pt.product_category_name
    ➡️ 필요에 따라 편하게 추출하기 위해서 JOIN 다 시켜버리기. SELECT 문에 추출할 컬럼 쓰고 WHERE에 조건 걸기만 하면 됨.

✨17년 9월~18년 8월 월별 주문 건수 추이✨

  • 쿼리
    SELECT COUNT(DISTINCT(CASE WHEN order_purchase_timestamp BETWEEN '2017-09-01 00:00:00' AND '2017-09-30 23:59:59' THEN order_id END)) AS 17_SEP
     , COUNT(DISTINCT(CASE WHEN order_purchase_timestamp BETWEEN '2017-10-01 00:00:00' AND '2017-10-31 23:59:59' THEN order_id END)) AS 17_OCT
     , COUNT(DISTINCT(CASE WHEN order_purchase_timestamp BETWEEN '2017-11-01 00:00:00' AND '2017-11-30 23:59:59' THEN order_id END)) AS 17_NOV
     , COUNT(DISTINCT(CASE WHEN order_purchase_timestamp BETWEEN '2017-12-01 00:00:00' AND '2017-12-31 23:59:59' THEN order_id END)) AS '17_DEC'
     , COUNT(DISTINCT(CASE WHEN order_purchase_timestamp BETWEEN '2018-01-01 00:00:00' AND '2018-01-31 23:59:59' THEN order_id END)) AS 18_JAN
     , COUNT(DISTINCT(CASE WHEN order_purchase_timestamp BETWEEN '2018-02-01 00:00:00' AND '2018-02-28 23:59:59' THEN order_id END)) AS 18_FEB
     , COUNT(DISTINCT(CASE WHEN order_purchase_timestamp BETWEEN '2018-03-01 00:00:00' AND '2018-03-31 23:59:59' THEN order_id END)) AS 18_MAR
     , COUNT(DISTINCT(CASE WHEN order_purchase_timestamp BETWEEN '2018-04-01 00:00:00' AND '2018-04-30 23:59:59' THEN order_id END)) AS 18_APR
     , COUNT(DISTINCT(CASE WHEN order_purchase_timestamp BETWEEN '2018-05-01 00:00:00' AND '2018-05-31 23:59:59' THEN order_id END)) AS 18_MAY
     , COUNT(DISTINCT(CASE WHEN order_purchase_timestamp BETWEEN '2018-06-01 00:00:00' AND '2018-06-30 23:59:59' THEN order_id END)) AS 18_JUN
     , COUNT(DISTINCT(CASE WHEN order_purchase_timestamp BETWEEN '2018-07-01 00:00:00' AND '2018-07-31 23:59:59' THEN order_id END)) AS 18_JUL
     , COUNT(DISTINCT(CASE WHEN order_purchase_timestamp BETWEEN '2018-08-01 00:00:00' AND '2018-08-31 23:59:59' THEN order_id END)) AS 18_AUG
FROM olist_orders_dataset

➡️ 17년 9월부터 18년 8월까지 1년치에 대한 주문 건수 추출. 해당 일자로 특정한 이유는 OLIST 데이터 내 전체기간(16년~18년) 중에서 가장 샘플링이 골고루 되어 있는 1년치 기간으로 보여졌기 때문임. 나머지 기간은 데이터가 없거나, 데이터 규모가 극소해서 제대로 된 비즈니스 경향을 반영하고 있다고 판단하기 어려웠음.

+)

Olist는 16년 처음 비즈니스 시작. 따라서 16년은 데이터가 제대로 적재되지 못 했고, 17년은 성장기였기 때문에 차츰 늘어남. 18년은 안정기로 평균적으로 주문 건이 높은 편이라고 볼 수 있음.

➡️ 2017년 블랙 프라이데이는 11월 24일 이었음. 주요 쇼핑몰은 11월 초부터 블랙 프라이데이 세일을 시작해 블랙 프라이데이 다음날 토요일 세일 종료한다고 함. OLIST 데이터도 샘플링한 것이긴 하나 11월에 최고 주문 건수를 달성하고 12월에 급감한 것을 보면 11월 내에 블랙 프라이데이 세일을 한 것으로 추정됨.

➡️ 12월에도 세일이 이어지거나 주문 건수가 많은 채로 유지할 줄 알았는데 12월에는 떨어짐. 아마 11월에 세일을 크게 하고 재고를 거의 털고 나면 12월에는 세일 X, 재고 X라 주문 건수가 떨어지지 않을까 생각. 12월 연휴 기간인 슈퍼 세터데이 (12월 16일), 크리스마스 (12월 25일), 박싱데이 (12월 26일), 송년의 날 (12월 31일)의 근처 1~2주 주문 건수가 어땠는 지 특정지어 봐도 좋을 듯. → 아래에 보면 12월 일별 주문 유저수 추이 그래프가 있는데 12월이라고 특별하게 프로모션이라던가 있었을 거 같지 않음. 왜냐하면 12월 특정일자라고 해서 주문 유저 수가 압도적으로 많을 때가 없음.

💡 블랙 프라이데이 세일 기준으로 리텐션 분석을 하려면 11월 1일부터 주차별로 코호트 분석을 해보면 좋겠다는 생각. 11월에 특정 시기 유입된 코호트가 12월까지 리텐션이 유지가 되는지? 12월은 주문건수가 많이 떨어지는 데 리텐션을 유지하려면 어떻게 해야 하는지 전략을 세우면 어떨까.

📈 11월의 주문 유저 수 추이를 살펴보자

🔹 ✨11월 일별 주문 유저 수✨

  • 쿼리
    SELECT DATE_FORMAT(o.order_purchase_timestamp, '%Y-%m-%d') AS order_date
     , COUNT(DISTINCT c.customer_unique_id) AS cnt_order_users
FROM olist_orders_dataset AS o
LEFT JOIN olist_customers_dataset AS c ON o.customer_id = c.customer_id
WHERE o.order_purchase_timestamp BETWEEN '2017-11-01 00:00:00' AND '2017-11-30 23:59:59'
GROUP BY DATE_FORMAT(o.order_purchase_timestamp, '%Y-%m-%d')

11월초부터 세일을 했다고 하기엔 주문 유저 수가 2017년 11월 24일을 기점으로 급증. 아마 블랙 프라이데이에만 행사를 한 거 같음. 혹은 블랙프라이데이 마감 직전이라 유저가 몰렸을 수도. 리텐션 분석을 한다면 11월 24일 유입한 유저를 기점으로 그 이후 유저 수가 어떻게 유지 되었는지 봐야하나? 🤔 → 코호트를 일주일 이상 열흘 이하의 기간 단위별 유입된 유저로 설정. 위 그래프상으로 블랙 프라이데이가 포함되면서 시작-끝이 11월 주문 유저 수 평균(248.87) 내외의 기간을 블랙 프라이데이의 직접 영향권으로 정의.

🔹 블랙 프라이데이 전후 2주(11월 20일~12월 3일) 주문 유저 수

  • 쿼리
    SELECT DATE_FORMAT(o.order_purchase_timestamp, '%Y-%m-%d') AS order_date
     , COUNT(DISTINCT c.customer_unique_id) AS cnt_order_users
FROM olist_orders_dataset AS o
LEFT JOIN olist_customers_dataset AS c ON o.customer_id = c.customer_id
WHERE o.order_purchase_timestamp BETWEEN '2017-11-20 00:00:00' AND '2017-12-3 23:59:59'
GROUP BY DATE_FORMAT(o.order_purchase_timestamp, '%Y-%m-%d')

11월 20일 월요일부터 12월 3일 일요일까지의 주문 유저 수 추이를 살펴봄 .. 블랙 프라이데이라는 소스를 가지고 리텐션 분석을 어떻게 해야 할까? 블랙 프라이데이에는 그 때만 유입된 고객이 많을 것이다. 그 이후에도 고객이 유지가 되어야 좋은 마케팅 전략을 펼친거라고 볼 수 있지 않나? 그렇다면 유지 되는 지 알아보는 기간은 어떻게 잡을 것인가… → 코호트를 일주일 이상 열흘 이하의 기간 단위별 유입된 유저로 설정. 위 그래프상으로 블랙 프라이데이가 포함되면서 시작-끝이 11월 주문 유저 수 평균(248.87) 내외의 기간을 블랙 프라이데이의 직접 영향권으로 정의.

리텐션의 모든 것 참고 (코호트 분석)

리텐션의 모든 것(All about Retention)2: 리텐션 분석과 실행

“그때 유입된 고객들 리텐션 어때요?”

두 번째 질문에 답하기 위해서는 코호트 분석으로 리텐션을 도출해야 해요. 코호트란 특정 기간에 같은 경험을 한 동질 집단을 의미해요. 예를 들면 특정 기간 동안 진행된 신규 가입 이벤트로 들어온 고객을 하나의 코호트로 간주하는 거예요. 메이크대쉬에서는 기간, 지역, 채널, 상품등으로 코호트를 설정할 수 있어요.

https://openads-real.s3.amazonaws.com/openadsAdmin/smart-editor/images/20221101144935691_0c5a6bb5-536f-4aa7-bf76-737d516572c0.png

Cohort 차트

월별로 리텐션을 확인하는 것과 달리 코호트 분석으로 리텐션을 확인하면 무엇을 발견할 수 있을까요? 바로 우리도 모르고 있던 충성 고객의 필요 조건을 발견할 수도 있습니다.

식료품 구독 서비스를 제공하는 고객사의 코호트 차트에서 특이한 점이 발견되었어요. 2022년 5월 유입된 고객들(2022년 5월 코호트)의 첫 구매 후 3개월 째 리텐션이 다른 시기에 유입된 고객들의 리텐션보다 평균 5.5%p 높았어요. 3개월 동안 고객 한 명이 누적으로 결제한 금액도 2022년 5월 코호트가 다른 코호트에 비해 45% 더 컸어요. 이런 수치상의 차이를 코호트 차트에서 발견하면 5월에 유입된 고객들과 다른 고객들간의 행동 패턴의 차이를 찾아내는 액션으로 이어집니다.

실제 식료품 구독 서비스를 전개하는 고객사는 5월에 유입된 고객들의 첫 번째 구매 아이템과 두 번째 구매 아이템이 다른 시기에 유입된 고객들과 다르다는 사실을 발견했어요. 5월에 유입된 고객들만 Item Journey가 달랐던 것이죠. 그로스 마케터는 이런 힌트를 놓치지 않고 실험을 설계합니다. 충성 고객으로 이어질 것이라 추정되는 Item Journey를 따르도록 유도하는 그룹과 서비스의 가장 일반적인 Item Journey를 따르는 그룹으로 구분해서 A/B 테스트를 진행해요. 동일 시기에 유입되었으나 Item Journey가 다른 두 그룹의 3개월 뒤 리텐션을 비교하면 충성 고객으로 이어지는 구매 여정을 발견할 수 있습니다.

정리하자면, 리텐션은 2가지 분석 방법으로 봐야해요. 월별 분석으로 서비스의 전체적인 리텐션이 예상 범위 안에 있는지 아니면 예상을 벗어나고 있는지 체크해야 합니다. 그리고 코호트 분석으로 어떤 경험이 이후의 고객 리텐션에 유의미한 차이를 가져오는지 확인해야 합니다. 두 가지 모두 메이크대쉬를 활용하면 개발자 없이 하루만에 확인할 수 있어요. 지금 메이크대쉬의 Growth 팀에 우리 서비스의 정확한 리텐션을 문의해 보세요.

💡 코호트를 전체 customer+order 데이터에서 고객별로 처음 구매한 기간(MIN)값이 블랙 프라이데이가 포함된 기간인 고객들과 다른 시기에 유입된 고객들로 나눠서 리텐션을 서로 비교해보면 어떨까?

➡️ 코호트를 열흘 단위로 1월까지 나눠서 리텐션 분석을 해보면 11월 21일~11월 30일 사이에 최초로 유입한 코호트(블랙 프라이데이 첫 구매)와 다른 시기별 유입 코호트와 비교를 할 수 있을 것이다. 그리고 각각 나눈 코호트를 기반으로 1️⃣ 제품별, 제품 카테고리(olist_products_dataset)별 구매 물품에 대한 분석을 또 해보면 유의미하지 않을까?

+)

2️⃣ 코호트별 결제 금액 규모를 살펴본다(olist_order_payments_dataset)

3️⃣ 코호트별 할인 여부를 살펴본다(olist_order_payments_dataset 결제 금액과 olist_order_items_dataset 상품금액+운송료를 비교하면 할인 여부를 알 수 있음)

4️⃣ 코호트별 지역 배송 기간을 살펴본다. 지역(olist_geolocation_dataset)별 배송기간(olist_orders_dataset)의 차이와 재구매의 연관성 알아보기

📉 11월 이후 월별 주문 유저 수의 추이를 살펴보자

🔹 12월 일별 주문 유저 수

4일 (월요일), 11일 (월요일), 18일 (월요일), 26일 (화요일 이긴 하나 25일이 크리스마스고 그 다음날)

즉, 한 주의 첫 평일에 주문 유저 수가 급증.

🔹 1월 일별 주문 유저 수

1월 4일 (목), 1월 8일(월), 1월 15일(월), 1월 22일(월), 1월 29일(월) 마다 주문 유저 수 급증. 거의 월요일마다 늘어나는 이유가 뭘까? 🤔

  • 이커머스 쇼핑 시계 (참고 자료)

    Shopping Time, 이커머스 쇼핑 시계

    **월요병 퇴치가 시급해요!
    월요일 오전부터 낮시간에 가장 붐비는 쇼핑몰

    전체 쇼핑몰의 시간대별 방문 현황을 살펴보았을 때, 가장 많은 방문이 일어난 요일과 시간은 월요일 오후 2시대였습니다. 그 다음으로는 월요일 오전 11시대가 뒤를 이었습니다. 이는 전체 요일 동시간대의 평균치보다 각각 31.02%, 18.50% 더 높은 수치였습니다.

💡 재밌는 분석 결과. 월요일마다 접속하는 유저 수가 많으니 월요일마다 쿠폰을 발행한다던가, 특가 상품을 올린다거나 하면 가장 효과적 !

🔹 12월~1월 일별 주문 유저 수

11월부터 1월 사이에 주문 유저 수를 일별로 봤을 때 11월 24일 주문 유저 수가 압도적으로 많고 다른 날짜는 따라잡지 못함.

📊 유입 시기별로 코호트를 나눠 분석해보자

🔹 월초(1일~10일), 월 중순(11일~20일), 월말(21일~30/31일) 코호트 분석

  • 쿼리 (65줄 짜리 쿼리가 완성됨 😂)
    WITH first_order AS (
  SELECT c.customer_unique_id AS c_unique_id
       , MIN(o.order_purchase_timestamp) AS first_order
  FROM olist_orders_dataset AS o
  LEFT JOIN olist_customers_dataset AS c ON o.customer_id = c.customer_id
  GROUP BY c.customer_unique_id
  HAVING first_order BETWEEN '2017-11-01 00:00:00' AND '2018-01-31 23:59:59'
), order_date AS (
  SELECT c.customer_unique_id
       , o.order_id
       , DATE_FORMAT(o.order_purchase_timestamp, '%Y-%m-%d') AS real_order_date
       , CASE WHEN o.order_purchase_timestamp BETWEEN '2017-11-01 00:00:00' AND '2017-11-10 23:59:59' THEN '2017-11-01'
              WHEN o.order_purchase_timestamp BETWEEN '2017-11-11 00:00:00' AND '2017-11-20 23:59:59' THEN '2017-11-11'
              WHEN o.order_purchase_timestamp BETWEEN '2017-11-21 00:00:00' AND '2017-11-30 23:59:59' THEN '2017-11-21'
              WHEN o.order_purchase_timestamp BETWEEN '2017-12-01 00:00:00' AND '2017-12-10 23:59:59' THEN '2017-12-01'
              WHEN o.order_purchase_timestamp BETWEEN '2017-12-11 00:00:00' AND '2017-12-20 23:59:59' THEN '2017-12-11'
              WHEN o.order_purchase_timestamp BETWEEN '2017-12-21 00:00:00' AND '2017-12-31 23:59:59' THEN '2017-12-21'
              WHEN o.order_purchase_timestamp BETWEEN '2018-01-01 00:00:00' AND '2018-01-10 23:59:59' THEN '2018-01-01'
              WHEN o.order_purchase_timestamp BETWEEN '2018-01-11 00:00:00' AND '2018-01-20 23:59:59' THEN '2018-01-11'
              WHEN o.order_purchase_timestamp BETWEEN '2017-01-21 00:00:00' AND '2018-01-31 23:59:59' THEN '2018-01-21'
         END AS order_date_range
       , DATE_FORMAT(f.first_order, '%Y-%m-%d') AS first_order_date
       , CASE WHEN f.first_order BETWEEN '2017-11-01 00:00:00' AND '2017-11-10 23:59:59' THEN '2017-11-01'
              WHEN f.first_order BETWEEN '2017-11-11 00:00:00' AND '2017-11-20 23:59:59' THEN '2017-11-11'
              WHEN f.first_order BETWEEN '2017-11-21 00:00:00' AND '2017-11-30 23:59:59' THEN '2017-11-21'
              WHEN f.first_order BETWEEN '2017-12-01 00:00:00' AND '2017-12-10 23:59:59' THEN '2017-12-01'
              WHEN f.first_order BETWEEN '2017-12-11 00:00:00' AND '2017-12-20 23:59:59' THEN '2017-12-11'
              WHEN f.first_order BETWEEN '2017-12-21 00:00:00' AND '2017-12-31 23:59:59' THEN '2017-12-21'
              WHEN f.first_order BETWEEN '2018-01-01 00:00:00' AND '2018-01-10 23:59:59' THEN '2018-01-01'
              WHEN f.first_order BETWEEN '2018-01-11 00:00:00' AND '2018-01-20 23:59:59' THEN '2018-01-11'
              WHEN f.first_order BETWEEN '2017-01-21 00:00:00' AND '2018-01-31 23:59:59' THEN '2018-01-21'
         END AS cohort
  FROM olist_orders_dataset AS o
  LEFT JOIN olist_customers_dataset AS c ON o.customer_id = c.customer_id
  INNER JOIN first_order AS f ON c.customer_unique_id = f.c_unique_id
  WHERE o.order_purchase_timestamp BETWEEN '2017-11-01 00:00:00' AND '2018-01-31 23:59:59'
)

SELECT cohort
     , COUNT(DISTINCT customer_unique_id) AS '0'
     , COUNT(DISTINCT(CASE
                          WHEN (cohort LIKE '20%-%-01' OR cohort LIKE '20%-%-11' OR cohort = '2017-11-21') AND DATE_ADD(cohort, INTERVAL 10 DAY) = order_date_range THEN customer_unique_id
                          WHEN (cohort = '2017-12-21' OR cohort = '2018-01-21') AND DATE_ADD(cohort, INTERVAL 11 DAY) = order_date_range THEN customer_unique_id
                      END)) AS '1'
     , COUNT(DISTINCT(CASE
                          WHEN (cohort LIKE '%-11-%' OR cohort LIKE '%-%-01') AND DATE_ADD(cohort, INTERVAL 20 DAY) = order_date_range THEN customer_unique_id
                          WHEN (cohort LIKE '%-%-11' OR cohort LIKE '%-%-21') AND cohort NOT LIKE '%-11-%' AND DATE_ADD(cohort, INTERVAL 21 DAY) = order_date_range THEN customer_unique_id
                      END)) AS '2'
     , COUNT(DISTINCT(CASE
                          WHEN cohort LIKE '%-11-%' AND DATE_ADD(cohort, INTERVAL 30 DAY) = order_date_range THEN customer_unique_id
                          WHEN (cohort LIKE '%-12-%' OR cohort LIKE '%-01-%') AND DATE_ADD(cohort, INTERVAL 31 DAY) = order_date_range THEN customer_unique_id
                      END)) AS '3'
     , COUNT(DISTINCT(CASE
                          WHEN (cohort = '2017-11-01' OR cohort = '2017-11-11') AND DATE_ADD(cohort, INTERVAL 40 DAY) = order_date_range THEN customer_unique_id
                          WHEN (cohort = '2017-11-21' OR cohort LIKE '%-12-%' OR cohort LIKE '%-01-%') AND DATE_ADD(cohort, INTERVAL 41 DAY) = order_date_range THEN customer_unique_id
                      END)) AS '4'
     , COUNT(DISTINCT(CASE
                          WHEN cohort = '2017-11-01' AND DATE_ADD(cohort, INTERVAL 50 DAY) = order_date_range THEN customer_unique_id
                          WHEN (cohort = '2017-11-11' OR cohort = '2017-11-21' OR cohort LIKE '%-12-%' OR cohort LIKE '%-01-%') AND DATE_ADD(cohort, INTERVAL 51 DAY) = order_date_range THEN customer_unique_id
                      END)) AS '5'
     , COUNT(DISTINCT(CASE WHEN DATE_ADD(cohort, INTERVAL 61 DAY) = order_date_range THEN customer_unique_id END)) AS '6'
     , COUNT(DISTINCT(CASE WHEN DATE_ADD(cohort, INTERVAL 71 DAY) = order_date_range THEN customer_unique_id END)) AS '7'
     , COUNT(DISTINCT(CASE WHEN DATE_ADD(cohort, INTERVAL 81 DAY) = order_date_range THEN customer_unique_id END)) AS '8'
FROM order_date
GROUP BY cohort
    ➡️ 더 간단하게 쓸 수 있는 방법은 없었을까? 어떤 달은 30일이고 어떤 달은 31일이다 보니.. 한 달을 3등분해서 코호트 분석을 하니까 날짜가 더해지는 게 일정치가 않아서 (어떤 코호트에서는 +10일, 어떤 코호트에서는 +11일) 복잡해짐 ㅎㅎ..

1️⃣ 11월 초 유입 코호트의 경우 12월, 1월까지 꾸준하게 리텐션이 있는 편. (평균 0.20%) 잠재 충성 고객으로 정의.

2️⃣ 11월 중순 유입 코호트의 경우, 11월 말(블랙 프라이데이)에는 리텐션이 최고치를 갱신하지만 그 이후로 급격히 떨어짐. (평균 0.21%) 반짝 세일 헌터로 정의.

3️⃣ 11월 말 유입 코호트의 경우 리텐션이 낮으나 12월, 1월에도 유지 됨. (평균 0.15%) 잠재 충성 고객으로 정의.

공통적으로 17년 12월에 리텐션이 감소하다가 12월 말에는 리텐션이 최저를 기록.

  • 코호트별 추출 쿼리

    11월 초 유입 코호트

    SELECT c.customer_unique_id AS c_unique_id
      , MIN(o.order_purchase_timestamp) AS first_order
FROM olist_orders_dataset AS o
LEFT JOIN olist_customers_dataset AS c ON o.customer_id = c.customer_id
GROUP BY c.customer_unique_id
HAVING first_order BETWEEN '2017-11-01 00:00:00' AND '2017-11-10 23:59:59'

    11월 중순 유입 코호트

    SELECT c.customer_unique_id AS c_unique_id
      , MIN(o.order_purchase_timestamp) AS first_order
FROM olist_orders_dataset AS o
LEFT JOIN olist_customers_dataset AS c ON o.customer_id = c.customer_id
GROUP BY c.customer_unique_id
HAVING first_order BETWEEN '2017-11-11 00:00:00' AND '2017-11-20 23:59:59'

    11월 말 유입 코호트

    SELECT c.customer_unique_id AS c_unique_id
      , MIN(o.order_purchase_timestamp) AS first_order
FROM olist_orders_dataset AS o
LEFT JOIN olist_customers_dataset AS c ON o.customer_id = c.customer_id
GROUP BY c.customer_unique_id
HAVING first_order BETWEEN '2017-11-21 00:00:00' AND '2017-11-30 23:59:59'

📝 11월 시기별 코호트로 나눠서 특성 파악

1️⃣ 11월 초 유입 코호트의 경우 12월, 1월까지 비교적 적당한 수준으로 꾸준하게 리텐션이 있는 편. (평균 0.20%) ➡️ 잠재 충성 고객으로 정의

2️⃣ 11월 중순 유입 코호트의 경우, 11월 말(블랙 프라이데이)에는 리텐션이 최고치를 갱신하지만 그 이후로 급격히 떨어짐. (평균 0.21%) ➡️ 반짝 세일 헌터로 정의

3️⃣ 11월 말 유입 코호트의 경우 평균 리텐션이 낮으나 12월, 1월에도 유지 됨. (평균 0.15%) ➡️ 잠재 충성 고객으로 정의

1️⃣ 코호트별 구매 제품 카테고리(olist_products_dataset)

  • 쿼리
    WITH nov_cohort AS (
  SELECT c.customer_unique_id AS c_unique_id
       , CASE WHEN MIN(o.order_purchase_timestamp) BETWEEN '2017-11-01 00:00:00' AND '2017-11-10 23:59:59' THEN 'nov_beginning'
              WHEN MIN(o.order_purchase_timestamp) BETWEEN '2017-11-11 00:00:00' AND '2017-11-20 23:59:59' THEN 'nov_middle'
              WHEN MIN(o.order_purchase_timestamp) BETWEEN '2017-11-21 00:00:00' AND '2017-11-30 23:59:59' THEN 'nov_ending'
         END AS cohort
  FROM olist_orders_dataset AS o
  LEFT JOIN olist_customers_dataset AS c ON o.customer_id = c.customer_id
  GROUP BY c.customer_unique_id
  HAVING cohort IS NOT NULL
)

SELECT nc.cohort
     , pt.product_category_name_english AS category
     , COUNT(DISTINCT o.order_id) AS cnt
FROM olist_orders_dataset AS o
LEFT JOIN olist_customers_dataset AS c ON o.customer_id = c.customer_id
LEFT JOIN olist_order_items_dataset AS oi ON o.order_id = oi.order_id
LEFT JOIN olist_products_dataset AS p ON oi.product_id = p.product_id
LEFT JOIN product_category_name_translation AS pt ON p.product_category_name = pt.product_category_name
LEFT JOIN nov_cohort AS nc ON c.customer_unique_id = nc.c_unique_id
WHERE nc.cohort IS NOT NULL
GROUP BY nc.cohort, pt.product_category_name_english
HAVING cohort = 'nov_beginning'
ORDER BY cohort, cnt DESC
LIMIT 10

➡️ 카테고리 뿐 아니라 카테고리 내에서 어떤 상품들을 구입했는 지도 알아보면 좋을 듯. 상품의 가격대라던가, 상품의 성격, 특성을 파악하기 위해서

2️⃣ 코호트별 결제 금액 규모(olist_order_payments_dataset)

  • 쿼리
    WITH nov_cohort AS (
  SELECT c.customer_unique_id AS c_unique_id
       , CASE WHEN MIN(o.order_purchase_timestamp) BETWEEN '2017-11-01 00:00:00' AND '2017-11-10 23:59:59' THEN 'nov_beginning'
              WHEN MIN(o.order_purchase_timestamp) BETWEEN '2017-11-11 00:00:00' AND '2017-11-20 23:59:59' THEN 'nov_middle'
              WHEN MIN(o.order_purchase_timestamp) BETWEEN '2017-11-21 00:00:00' AND '2017-11-30 23:59:59' THEN 'nov_ending'
         END AS cohort
  FROM olist_orders_dataset AS o
  LEFT JOIN olist_customers_dataset AS c ON o.customer_id = c.customer_id
  GROUP BY c.customer_unique_id
  HAVING cohort IS NOT NULL
)

SELECT nc.cohort
     , ROUND(AVG(payment_value), 2) AS avg_payment
     , ROUND(MIN(payment_value), 2) AS min_payment
     , ROUND(MAX(payment_value), 2) AS max_payment
FROM olist_orders_dataset AS o
LEFT JOIN olist_customers_dataset AS c ON o.customer_id = c.customer_id
LEFT JOIN olist_order_items_dataset AS oi ON o.order_id = oi.order_id
LEFT JOIN olist_order_payments_dataset AS p ON o.order_id = p.order_id
LEFT JOIN nov_cohort AS nc ON c.customer_unique_id = nc.c_unique_id
WHERE nc.cohort IS NOT NULL
GROUP BY nc.cohort

▪️ AVG

▪️ MIN

▪️ MAX

▪️ 코호트별 결제 금액에 대한 박스 플랏

전체 거래금액 기준으로 봤을 때는 BRL 250 미만 거래가 대부분이라 박스 플랏과 중위값이 뚜렷하게 보이지 않음.

BRL 250 기준으로 이상, 미만으로 나눈 박스 플랏

BRL 250 = KRW 67,448

3️⃣ 코호트별 할인 여부 → olist_order_payments_dataset 결제 금액과 olist_order_items_dataset 상품금액+운송료를 비교하면 할인 여부를 알 수 있음

4️⃣ 코호트별 지역 배송 기간 → 지역(olist_geolocation_dataset)별 배송기간(olist_orders_dataset)의 차이와 재구매의 연관성 알아보기

profile
구너탱
데이터 꿈나물
다음 포스트

코호트(Cohort)란?

2개의 댓글

comment-user-thumbnail
호호
2023년 12월 27일

안녕하세요! 좋은 글 감사합니다! 혹시
해당 글에서 SQL 쿼리 짜시고 시각화는 어떤 걸로 하신건가요...?

1개의 답글