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

2024. 11. 17. 20:08 · 대외활동 및 인턴/빅데이터 분석 학회 BDA

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으로 가져와 시각화하여 인사이트 도출.
728x90

'대외활동 및 인턴 > 빅데이터 분석 학회 BDA' 카테고리의 다른 글

전체 매출의 상위 20%를 차지하는 제품 구하기 (BDA학회 SQL 문법 기초 연습반)  (0) 2025.01.11
테이블 JOIN 할 때, 기준이 되는 테이블은? (BDA학회 SQL 문법 기초 연습반)  (1) 2024.11.21
[강연] 태블로 기초 이론  (1) 2024.11.16
[강연] 전공자를 위한 데이터 직무 커리어패스  (6) 2024.11.15
피벗 테이블과 코호트 분석 (BDA학회 SQL 문법 기초 연습반)  (0) 2024.11.14
'대외활동 및 인턴/빅데이터 분석 학회 BDA' 카테고리의 다른 글
  • 전체 매출의 상위 20%를 차지하는 제품 구하기 (BDA학회 SQL 문법 기초 연습반)
  • 테이블 JOIN 할 때, 기준이 되는 테이블은? (BDA학회 SQL 문법 기초 연습반)
  • [강연] 태블로 기초 이론
  • [강연] 전공자를 위한 데이터 직무 커리어패스
따`ddah
따`ddah
    250x250
  • 따`ddah
    IT's ddah
    따`ddah
  • 관리    글쓰기
  • 전체
    오늘
    어제
    • 분류 전체보기
      • Projects
        • Auto Post : SNS 자동 업로더
      • kmooc
        • 기계 학습 기반의 데이터 공학
      • Algorithm
        • [C] Do it! 자료구조와 함께 배우는 알고리..
        • 알고리즘 스터디
        • 코딩 테스트
      • Basic
        • 운영체제 OS
        • 컴퓨터구조
        • 소프트웨어공학 (UML)
      • DBMS
        • 데이터베이스 이론
        • MySQL
        • Oracle SQL
        • BigQuery
        • Yammer
      • Programming
        • Python
        • C
        • Java
        • React
        • JavaScript
        • R
      • 빅데이터
      • AI
        • 멀티미디어응용
        • 머신러닝
        • 인공지능
      • 자격증
        • Azure DP-900
        • Azure AI-900
        • SQLD
        • CSTS
      • 대외활동 및 인턴
        • 인턴
        • LG Aimers
        • Outta
        • 빅데이터 분석 학회 BDA
        • 세계시민교육연구소 청년단 GYIA
      • Tool
        • Git
        • IDE
      • 도서
        • IT
      • 그 외
        • 단축키
        • ✞
  • 블로그 메뉴

    • 홈
    • 태그
    • 방명록
  • 링크

  • 공지사항

  • 인기 글

  • 태그

    취업
    자료형
    주석
    파이썬
    python
    input
    취업준비
    AI역량검사
    react
    자바스크립트
    sql
    파이썬{}
    티스토리챌린지
    dbms
    javascript
    리액트
    오라클SQL
    importturtle
    대외활동
    BDA학회
    빅데이터분석
    print(f"")
    Py
    파이썬 챗봇 만들기
    대학생학회
    Oracle
    오블완
    이름나이
    jsx
    js
  • 최근 댓글

  • 최근 글

  • hELLO· Designed By정상우.v4.10.3
따`ddah
코호트 분석과 검증 쿼리의 중요성 (BDA학회 SQL 문법 기초 연습반)
상단으로

티스토리툴바