코호트 분석과 검증 쿼리의 중요성 (BDA학회 SQL 문법 기초 연습반)

1️⃣ 코호트 분석 이해하기

코호트 분석은 고객의 첫 구매 시점을 기준으로 시간에 따른 행동 패턴을 분석하는 방법입니다. 특히, 첫 구매 후 다음 달의 재구매 여부를 확인하는 것이 주요 과제였습니다. 이를 통해 고객 유지율이나 마케팅 효과를 평가할 수 있었습니다.

---

📌 핵심 쿼리: 첫 구매 이후 한 달 내 구매 검증

with FirstOrder as (
    select 
        c.customerNumber,
        date_format(min(o.orderDate), '%Y-%m') as CohortGroup,
        min(o.orderDate) as FirstOrderDate
    from customers as c
    join orders o on c.customerNumber = o.customerNumber
    group by c.customerNumber
),
NextMonthOrders as (
    select
        fo.customerNumber,
        fo.CohortGroup,
        date_format(o.orderDate, '%Y-%m') as OrderPeriod
    from FirstOrder fo
    join orders o on fo.customerNumber = o.customerNumber
    where 
        o.orderDate > fo.FirstOrderDate
        and date_format(o.orderDate, '%Y-%m') = date_format(date_add(fo.FirstOrderDate, interval 1 month), '%Y-%m')
)
select 
    fo.customerNumber,
    fo.CohortGroup as FirstPurchaseMonth,
    count(nmo.OrderPeriod) as NextMonthPurchaseCount
from FirstOrder fo 
left join NextMonthOrders nmo on fo.customerNumber = nmo.customerNumber
group by fo.customerNumber, fo.CohortGroup
order by 1, 2;

📌 검증 포인트

• 30일 기준과 월 기준 차이
  • 30일 간격으로 보면 2003-01-31에서 2003-02-01은 하루 차이지만, 논리적으로는 다음 달의 구매로 간주해야 합니다.
  • date_add와 datediff를 활용해 이를 명확히 구분했습니다.
• NextMonthOrders 검증 쿼리
  • 특정 고객 데이터를 확인하며 분석 결과를 검증했습니다.

select *
from orders
where customerNumber = 157;

---

2️⃣ 결과 및 느낀 점

수업에서 다룬 SQL 쿼리는 실무에서도 자주 사용되는 방식으로, 데이터를 다각도로 분석하는 데 매우 유용했습니다. 특히, 데이터 검증을 통해 분석 결과를 신뢰할 수 있도록 만드는 과정이 중요하다는 것을 배웠습니다.

---

✨ 휴회기간 동안의 나만의 계획

앞으로의 시간은 BDA 학회 활동을 심화시키고, 추가적인 데이터 분석 프로젝트를 진행하는 데 집중하려 합니다. 특히, 다양한 실무 데이터를 활용하여 아래와 같은 주제를 심도 있게 탐구할 계획입니다.

• 고객 생애 가치(LTV) 분석: 코호트 분석을 확장하여 장기적인 고객 가치를 평가.
• 정확한 월별 비교 분석 쿼리 작성: datediff, date_add 등을 활용한 정교한 데이터 분석 실습.
• Python을 활용한 시각화: SQL 결과를 Python으로 가져와 시각화하여 인사이트 도출.