피벗 테이블과 코호트 분석 (BDA학회 SQL 문법 기초 연습반)

따`ddah 2024. 11. 14. 21:17

수업에서는 피벗 테이블과 코호트 분석을 활용하여 데이터를 재구성하고 중요한 인사이트를 얻는 방법을 배웠습니다. 데이터를 분석할 때는 단순히 값을 확인하는 것을 넘어 어떤 기준점에서 데이터가 어떻게 변화하는지를 보는 것이 중요했습니다. 이를 위해 SQL 쿼리를 작성하여 월별 매출과 고객 활동을 분석해봤습니다.

📌 1. 피벗 테이블을 활용한 월별 매출 분석

우선, 피벗 테이블을 활용하여 고객별 월별 매출 데이터를 확인해봤습니다.
아래는 주요 쿼리 코드입니다.

select 
    o.customerNumber as customerID,
    sum(case when date_format(o.orderDate, '%Y-%m') = '2003-05' then od.quantityOrdered * od.priceEach else 0 end) as revenue_200305,
    sum(case when date_format(o.orderDate, '%Y-%m') = '2003-06' then od.quantityOrdered * od.priceEach else 0 end) as revenue_200306
from 
    orders o 
join 
    orderdetails as od 
on od.orderNumber = o.orderNumber
group by o.customerNumber
order by 1;

이 코드를 통해 2003년 5월과 6월의 매출을 각각 계산했으며, 고객별 매출의 변화를 비교할 수 있었습니다.

📌 2. 코호트 분석: 고객의 첫 구매 이후 변화 분석

다음으로는 고객의 첫 구매 시점을 기준으로 한 코호트 분석을 진행했습니다. 코호트 분석은 고객의 행동 변화를 시계열로 추적하며, 마케팅 효과나 매출 변화를 확인하는 데 유용한 기법이었습니다.

(1) 첫 구매 데이터 추출

with FirstOrder as (
select 
    c.customerNumber,
    min(o.orderDate) as firstorderdate
from customers c
join 
    orders o on c.customerNumber = o.customerNumber
group by 
    c.customerNumber
)

(2) 코호트 데이터 구성 및 피벗 테이블로 변환

select
    cohortGroup,
    sum(case when cohortGroup = orderperiod then total_revenue else 0 end) as Month0_revenue,
    sum(case when period_diff(date_format(orderperiod,'%Y%m'), date_format(cohortGroup,'%Y%m')) = 1 then total_revenue else 0 end) as Month1_revenue,
    sum(case when period_diff(date_format(orderperiod,'%Y%m'), date_format(cohortGroup,'%Y%m')) = 2 then total_revenue else 0 end) as Month2_revenue
from CohortData
group by cohortGroup
order by 1;

이 코드를 통해 고객이 첫 구매한 달과 이후 두 달 동안의 매출 변화를 확인할 수 있었습니다. 하지만 분석 결과, Month1_revenue 값이 0으로 나와 이를 검증하는 추가 쿼리도 작성해봤습니다.

📌 3. Month1 데이터 검증

데이터를 검증하는 과정에서, 첫 구매 이후 한 달 동안 아무도 구매하지 않았는지 확인해보기 위해 아래와 같은 쿼리를 작성했었습니다.

select 
    count(*)
from 
    CohortData
where 
    period_diff(date_format(orderperiod, '%Y%m'), date_format(cohortGroup, '%Y%m')) = 1;

✨ 배운 점과 느낀 점

이번 수업은 데이터 분석에서 구체적인 목표와 검증 프로세스의 중요성을 느끼게 해주는 시간이었습니다. 단순히 쿼리를 작성하는 데 그치지 않고, 데이터의 흐름을 이해하며 논리적으로 문제를 접근하는 것이 핵심이었습니다.

BDA 학회는 이런 실질적인 학습을 통해 데이터 분석 역량을 키우고, 다양한 기업 실무에 활용 가능한 기술을 배우는 곳이었습니다. 특히, 대외활동과 취업 준비를 동시에 진행할 수 있어 매우 유익했습니다.

728x90