[TIL 26] 브랜드가 데려온 고객의 가치는 어떻게 될까? 고객 한명 당 가치를 계산하는 LTV 분석

이번 분석 건의 경우 신규 고객 유치에 들어간 비용에 비해 장기적으로 고객이 충분한 수익을 만들어내고 있는가?에 대해 확인해 봤다.

이번엔 안건에 따라 지표를 먼저 생각하고 그 다음에 가설을 세우는 방식으로 진행했다.

 

안건에 따라 어떤 지표를 볼 지에 따라 해석이 달라지기에 이에 대한 과정 기록도 남겨보고자 한다.

 

1. 분석 목적 요약

현재 우리 브랜드는 장기적으로 고객의 충성도가 어떻게 되는 지, 시장 경쟁에서 괜찮을 지 확인해보고자 했다. 

항목	내용
📌 분석 안건	신규 유입 효율성과 수익성 구조 평가
🎯 핵심 질문	“신규 고객 유치에 들어간 비용(CAC)에 비해, 장기적으로 고객이 충분한 수익(LTV)을 만들어내고 있는가?”
🧪 주요 분석	월별 cohort 기준 CAC, LTV 산출 → LTV:CAC 추이 비교
🎯 목적	시장 경쟁 심화 상황 속에서 마케팅 ROI와 브랜드 수익성 구조 진단

---

2. 주요 지표 요약

그에 따라 우선 지표를 먼저 고민해보았다. 

지표명	정의
CAC (Customer Acquisition Cost)	신규 고객 1명을 첫 구매까지 유치하는 데 소요된 마케팅 비용
LTV (Customer Lifetime Value)	고객이 유입된 후 특정 기간 내 발생시킨 평균 누적 구매 금액
LTV:CAC 비율	고객 1명을 유치해서 얻는 수익 대비 투자 비용 비율 (1.5 이상이 바람직)

 

이때 LTV 분석 진행할 때 시계열 코호트 분석을 이용해보고자 한다.
장기 수익성이 어떻게 되고 있는 지를 파악하기 위함이다. 

 

🤔 이때, LTV 시계열 코호트 분석이 있고 이탈율 기반 리텐션 코호트 분석 대표적인 2가지 방법이 있다.

커머스에서는 일반적으로는 후자를 많이 활용하는 듯 했다. 

 

그래서 이 둘의 차이를 이해하고 코호트 분석의 사례도 알아봤다. 

[비교] ① LTV 시계열 분석 vs ② 이탈율 기반 리텐션 코호트 분석 

구분	① LTV 시계열 코호트 분석	② 리텐션/이탈율 기반 코호트 분석
🎯 목적	고객의 장기 수익성(누적 매출) 변화 확인	고객이 얼마나 오래 머무는지(재구매/이탈 패턴) 추적
🔍 지표	누적 구매 금액 (LTV)ex. 3M, 6M, 12M 기준	월별 재구매율, 잔존율, 이탈율 등
📅 관점	단일 cohort의 가치가 시간이 지나며 얼마나 축적되는가	각 cohort가 유입 후 몇 달간 유지되는가
📊 결과 형태	시계열 라인 차트(cohort별 LTV 증가 추이)	코호트 매트릭스(잔존율/이탈율 heatmap)
🧭 활용 포인트	고객 질 변화, ROI 분석, LTV:CAC 비교	리텐션 진단, 제품력/충성도 측정, 이탈 시점 파악
📌 예시 질문	“최근 유입 고객은 과거보다 얼마나 돈을 덜 쓰는가?”	“첫 구매 후 2~3개월 내 이탈률이 얼마나 되는가?”

---

어떤 방식이 더 좋을까?

목적	추천 방식
고객 질과 수익성 분석 (예: LTV:CAC)→ 구매 금액 중심 추적	✅ ① LTV 시계열 분석
재구매율/이탈 시점 파악 (제품력, 리텐션 진단)→ 행동 지속성 중심 추적	✅ ② 이탈율 코호트 분석

 

➡️ 따라서 지금 수행하시는 "수익성 구조 평가 (LTV:CAC)" 목적에는 ① LTV 시계열 분석이 적합해 보였다. 

---

[리스트업] 실무에서 활용되는 주요 코호트 분석 방식

유형 목적	활용 예시
① LTV 코호트 분석	월별 cohort의 누적 구매금액 추이 → 수익성 변화 추적, CAC 비교
② 재구매율 코호트 분석	각 cohort의 n개월 차 재구매율 추이 → 제품 만족도/충성도 확인
③ 리텐션 코호트 분석	매월 유입된 고객 중 n개월 후 남아있는 비율 (잔존율) → 구독형/재방문형
④ 이탈율 분석	n개월 내 이탈한 비율 추적 → 전환 실패, 제품 불만족 분석
⑤ 유입경로별 cohort	유입 채널별 고객군 성과 비교 (ex. 인스타 vs 블로그 유입군)
⑥ 프로모션별 cohort	특정 캠페인 진입 cohort의 LTV, 이탈율 비교
⑦ 제품별 첫구매 cohort	시그니처 제품 vs 일반 제품 유입 고객의 행동 변화 비교

---

요약

항목	판단
지금 상황에 적합한 분석	✅ ① LTV 시계열 코호트 분석 (LTV:CAC 구조 진단 목적)
이탈율 기반 코호트 분석 활용 시점	재구매 이탈 시점 진단, 제품력 분석이 핵심일 때
코호트 분석 방식 확장	리텐션, 구매주기, 할인/이벤트 효과 등 다양하게 확장 가능

---

이에 따라 LTV 기준은 90일, 180일, 365일 단위로 보기로 했다. 

이는 각 비즈니스 재구매 주기에 따라 나눠 보면 좋고 재구매주기가 긴 제품일 수록 구간을 길게 잡아 보면 된다.

✅ LTV 기준 기간별 해석 가이드

기준 기간	분석 의미	해석 포인트
90일 LTV	초기 전환 이후 단기 수익력	브랜드 첫인상 / 빠른 재구매 유도 효과 측정
180일 LTV	중기 충성도	제품력 & 품질 인식에 따른 지속적 소비 판단
365일 LTV	장기 고객 가치	브랜드 충성도, 고객 관계 유지 성과 반영

 

우선 내가 원하는 로직으로 SQL 쿼리를 작성해보기 위해 단계를 고민했다. 

1. 24년 이후 첫구매한 사람들의 모든 주문 값들을 들고 온다. n_times 1인 사람 기초 테이블
2. n_times 1인 사람들의 90일, 180일, 365일 총 구매 이력 합계를 더해서 90일/180일/365일 총 구매 금액컬럼 가져오기 ➡️ 월별 구분 컬럼 더해서 월별 그룹별 LTV를 위한 총 합계를 가져온다.
3. 2번 테이블에서 customer 수와 총 합계를 각각 나눠서 LTV를 구한다.

① 구매회차= 1 조건으로 브랜드 내 첫구매자 추출

👉 브랜드 최초 구매자만 포함하므로, 과거 이력 있는 고객을 정확히 배제 가능

 

② 해당 고객들의 전체 구매 이력 중

• 첫 구매일로부터 90일, 180일, 365일 이내의 구매 내역만 각각 필터링
• 총 결제 금액을 SUM()
• DATE_TRUNC(first_order_date, MONTH)로 월별 그룹화

③ 월별로

• 총 구매 금액 합계
• 총 고객 수
• → 평균 LTV 산정 (LTV = 합계 ÷ 고객 수)

---

✅ SQL 구조 

sql
복사편집
-- Step 1. 브랜드 첫구매자 추출
WITH first_buyers AS (
  SELECT
    member_id,
    MIN(pay_date) AS first_order_date
  FROM orders
  WHERE n_times = 1
  GROUP BY member_id
),

-- Step 2. 유효 주문 이력과 매핑 후 90/180/365일 이내 주문 누적 금액 계산
ltv_base AS (
  SELECT
    f.member_id,
    f.first_order_date,
    DATE_TRUNC(f.first_order_date, MONTH) AS cohort_month,
    o.pay_date,
    DATE_DIFF(o.pay_date, f.first_order_date, DAY) AS days_from_first,
    o.total_payment_amount
  FROM first_buyers f
  JOIN orders o ON f.member_id = o.member_id
  WHERE o.total_payment_amount > 0
)

-- Step 3. 각 고객별 기간별 누적 구매액 계산
, customer_ltv AS (
  SELECT
    member_id,
    cohort_month,
    SUM(CASE WHEN days_from_first BETWEEN 0 AND 90 THEN total_payment_amount ELSE 0 END) AS ltv_90d,
    SUM(CASE WHEN days_from_first BETWEEN 0 AND 180 THEN total_payment_amount ELSE 0 END) AS ltv_180d,
    SUM(CASE WHEN days_from_first BETWEEN 0 AND 365 THEN total_payment_amount ELSE 0 END) AS ltv_365d
  FROM ltv_base
  GROUP BY member_id, cohort_month
)

-- Step 4. 월별 평균 LTV 집계
SELECT
  cohort_month,
  COUNT(*) AS customer_count,
  AVG(ltv_90d) AS avg_ltv_90d,
  AVG(ltv_180d) AS avg_ltv_180d,
  AVG(ltv_365d) AS avg_ltv_365d
FROM customer_ltv
GROUP BY cohort_month
ORDER BY cohort_month

 

해당 쿼리는 아래의 특징을 가지며 이후 확장 활용하기에도 좋다. 

항목	이유
🎯 정확한 Cohort 대상	n_times = 1으로 브랜드 첫구매자만 대상
🧠 기간 기준 유연함	고객별 기준일로부터 90/180/365일 상대적 조건 필터
📊 LTV 왜곡 없음	주문 기준이 아니라 고객 기준으로 평균 산정
📅 시계열 분석 가능	Cohort 월 기준으로 LTV 추이 분석 가능

 

---

LTV 에 더해 첫구매자의 구매력을 확인하기 위해 월별 첫 구매자의 객단가 까지 확인해보았다. 

월별 객단가 = (월 Cohort의 첫구매자 총 결제 금액 합계) ÷ (해당 월 첫구매자 수)

• Cohort 기준 n_times = 1인 고객만 포함
• 해당 고객의 첫 주문 건의 결제 금액만 사용

---

✅ SQL 쿼리

sql
복사편집
WITH first_orders AS (
  SELECT
    member_id,
    MIN(pay_date) AS first_order_date
  FROM `verish-pm.sukyung.member_order_behavior`
  WHERE n_times = 1
  GROUP BY member_id
),

-- 첫구매 주문 정보
first_payment AS (
  SELECT
    f.member_id,
    f.first_order_date,
    DATE_TRUNC(f.first_order_date, MONTH) AS cohort_month,
    o.actual_total_payment_amount
  FROM first_orders f
  JOIN `verish-pm.sukyung.member_order_behavior` o
    ON f.member_id = o.member_id AND o.pay_date = f.first_order_date
  WHERE o.actual_total_payment_amount > 0
)

-- 월별 객단가 집계
SELECT
  cohort_month,
  COUNT(*) AS first_customer_count,
  SUM(actual_total_payment_amount) AS total_first_order_amount,
  SAFE_DIVIDE(SUM(actual_total_payment_amount), COUNT(*)) AS first_order_arpu
FROM first_payment
GROUP BY cohort_month
ORDER BY cohort_month

 

➡️ 이렇게 이번에는 LTV 분석과 함께 코호트 분석을 적용해봤다.

코호트 분석은 고객의 시점별 행동 변화를 추적하는 데 효과적인 분석 방법론이다.

 

크게 LTV 추이형 분석과 이탈율 중심 분석은 실제 실무에서 자주 활용될 수 있다.

 

이 개념에 대해 익혀두고 리텐션/이탈율 기반 코호트 분석까지 확장해보도록 하자.

어쨌든 분석이라는 건 파면 팔 수록 어렵고 특히 논리적으로 흐름이 맞아야 한다고 생각한다.

 

그렇기에 이러한 CASE와 분석 방법, 실무 적용까지 다방면으로 익혀두도록 해야겠다 또 다시 다짐해본다. 

 

✏️ 느꼈던 점 요약
- 분석은 파도 파도 계속 봐야 하고 정답이라고 내리기 어렵다. 해석하기 나름이라는 생각도 많이 든다. 그렇기에 "리터러시 역량"은 매우 중요하다. 

💪🏻 시도할 점
- 분석 과정을 기록하고 그렇게 지표를 선택하게 된 것 / 데이터에 의문을 가졌던 부분들을 고민하고 기록하도록 하기