[GA4 코호트 분석] Day9-8 코호트 리텐션 분석 (LTV 분석)

 

"어떤 코호트가 실제로 돈을 벌어주는가?"

분석 목적

 

• Cohort별 총 매출
• Cohort별 ARPU (Revenue per User)
• Cohort별 Purchase Frequency
• Cohort별 AOV (Average Order Value)

SQL

1. Cohort별 총 매출

 

 

• 어떤 주차에 유입된 유저들이 가장 많은 매출을 만들었는가?
• 리텐션이 높은 코호트가 실제로도 매출이 높은가?

 

-- Cohort별 총 매출 분석

WITH first_session AS (

  SELECT
    user_pseudo_id,

    -- 최초 방문 주차 = Cohort
    DATE_TRUNC(
      MIN(DATE(TIMESTAMP_MICROS(event_timestamp))),
      WEEK
    ) AS cohort_week

  FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
  WHERE event_name = 'session_start'
  GROUP BY user_pseudo_id

),

purchase_data AS (

  SELECT
    user_pseudo_id,

    DATE(TIMESTAMP_MICROS(event_timestamp)) AS purchase_date,

    -- 구매 매출
    ecommerce.purchase_revenue AS revenue

  FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
  WHERE event_name='purchase'
    AND ecommerce.purchase_revenue IS NOT NULL
)

SELECT

  f.cohort_week,

  ROUND(SUM(p.revenue),2) AS total_revenue,

  COUNT(*) AS purchase_events

FROM first_session f

JOIN purchase_data p
ON f.user_pseudo_id = p.user_pseudo_id

GROUP BY 1
ORDER BY 1;

핵심 코드

DATE_TRUNC(MIN(DATE(...)), WEEK)

 

→ 유저의 최초 방문 주차 추출

SUM(revenue)

 

→ Cohort별 누적 매출 계산

 

2. ARPU (Revenue Per User)

 

• 코호트 규모 차이를 제거
• 유저 1명당 얼마를 벌어오는지 확인

 

-- Cohort별 ARPU 분석

WITH first_session AS (

  SELECT
    user_pseudo_id,

    -- 최초 방문 주차
    DATE_TRUNC(
      MIN(DATE(TIMESTAMP_MICROS(event_timestamp))),
      WEEK
    ) AS cohort_week

  FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
  WHERE event_name='session_start'
  GROUP BY user_pseudo_id

),

cohort_size AS (

  -- Cohort별 유저 수
  SELECT
    cohort_week,
    COUNT(*) AS users

  FROM first_session
  GROUP BY 1

),

cohort_revenue AS (

  -- Cohort별 총 매출
  SELECT

    f.cohort_week,

    SUM(ecommerce.purchase_revenue) AS revenue

  FROM first_session f

  JOIN `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*` e
    ON f.user_pseudo_id=e.user_pseudo_id

  WHERE event_name='purchase'
    AND ecommerce.purchase_revenue IS NOT NULL

  GROUP BY 1

)

SELECT

  r.cohort_week,

  ROUND(r.revenue,2) AS total_revenue,

  c.users,

  -- 유저당 평균 매출
  ROUND(r.revenue/c.users,2) AS arpu

FROM cohort_revenue r

JOIN cohort_size c
ON r.cohort_week=c.cohort_week

ORDER BY 1;

핵심 코드

ROUND(revenue / users, 2)

 

→ ARPU

Revenue Per User

 

3. Purchase Frequency

 

• 한 번 사고 끝나는가?
• 반복 구매하는가?

-- Cohort별 구매 빈도 분석

WITH first_session AS (

  SELECT
    user_pseudo_id,

    DATE_TRUNC(
      MIN(DATE(TIMESTAMP_MICROS(event_timestamp))),
      WEEK
    ) AS cohort_week

  FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
  WHERE event_name='session_start'
  GROUP BY user_pseudo_id

),

purchase_count AS (

  SELECT

    f.cohort_week,

    -- 전체 구매 건수
    COUNT(*) AS purchases,

    -- 구매 유저 수
    COUNT(DISTINCT f.user_pseudo_id) AS purchasers

  FROM first_session f

  JOIN `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*` e
    ON f.user_pseudo_id=e.user_pseudo_id

  WHERE event_name='purchase'

  GROUP BY 1

)

SELECT

  cohort_week,

  purchases,

  purchasers,

  -- 구매 유저 1명당 평균 구매 횟수
  ROUND(
    purchases/purchasers,
    2
  ) AS purchase_frequency

FROM purchase_count

ORDER BY 1;

 

핵심 코드

COUNT(*) AS purchases

 

→ 전체 구매 건수

COUNT(DISTINCT user_pseudo_id)

 

→ 실제 구매 유저 수

purchases / purchasers

 

→ 구매 빈도

 

4. AOV (Average Order Value)

• 구매 빈도가 아니라 구매 금액 자체 확인

-- Cohort별 평균 주문 금액(AOV)

WITH first_session AS (

  SELECT

    user_pseudo_id,

    DATE_TRUNC(
      MIN(DATE(TIMESTAMP_MICROS(event_timestamp))),
      WEEK
    ) AS cohort_week

  FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
  WHERE event_name='session_start'
  GROUP BY user_pseudo_id

)

SELECT

  f.cohort_week,

  -- 평균 주문 금액
  ROUND(
    AVG(ecommerce.purchase_revenue),
    2
  ) AS avg_order_value,

  ROUND(
    SUM(ecommerce.purchase_revenue),
    2
  ) AS total_revenue,

  COUNT(*) AS purchase_events

FROM first_session f

JOIN `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*` e
ON f.user_pseudo_id=e.user_pseudo_id

WHERE event_name='purchase'
  AND ecommerce.purchase_revenue IS NOT NULL

GROUP BY 1

ORDER BY 1;

핵심 코드

AVG(ecommerce.purchase_revenue)

 

→ AOV (Average Order Value)

SUM(ecommerce.purchase_revenue)

 

→ 총 매출

COUNT(*)

 

→ 구매 횟수

 

해석

1. Cohort별 총 매출

KPI

Highest Revenue Cohort

2020-12-06 Cohort

 

Total Revenue

48,008

 

Purchase Events

686건

 

 

12월 첫째 주에 유입된 유저들이 가장 높은 매출을 발생시켰다.

이는 단순히 유입 규모 때문일 수도 있지만, 해당 기간에 유입된 유저들의 구매 의도가 높았을 가능성도 존재한다.

- 유입량이 많은 코호트가 항상 가장 가치 있는 코호트는 아니다.

 

2. ARPU 분석

KPI

Highest ARPU Cohort

2020-11-08 Cohort

 

ARPU

2.69

 

Users

16,066명

 

11월 8일 Cohort는 전체 매출 규모는 가장 크지 않았지만 유저 1명당 발생시킨 매출은 가장 높았다.

• 유입 규모보다 유입 품질이 중요할 수 있음
• 특정 유입 채널 또는 캠페인이 더 가치 있는 유저를 데려왔을 가능성

 

3. Purchase Frequency 분석

KPI

Highest Purchase Frequency

2020-11-15 Cohort

 

Purchase Frequency

1.56회

 

11월 15일 Cohort는 구매 유저 1인당 평균 1.56회의 구매를 기록했다.

즉, 다른 Cohort보다 반복 구매 성향이 강했다.

 

CRM 관점 해석

• 재구매 가능성이 높은 유저군
• 장기 고객으로 전환될 가능성이 높은 유저군

 

4. AOV 분석

KPI

Highest AOV Cohort

2021-01-17 Cohort

 

Average Order Value

74.50

 

1월 17일 Cohort는 주문 횟수는 많지 않았지만 주문 1건당 금액이 가장 높았다.

• 고가 상품 구매
• 묶음 구매
• 특정 프로모션 효과

최종 인사이트

Insight 1

매출이 가장 높은 Cohort와 ARPU가 가장 높은 Cohort는 다르다.

→ 유입량보다 유입 품질이 중요할 수 있다.

Insight 2

2020-11-15 Cohort는 구매 빈도가 가장 높았다.

→ CRM 및 리텐션 관점에서 우수한 고객군이다.

Insight 3

2021-01-17 Cohort는 객단가가 가장 높았다.

→ 고가 상품 구매 가능성이 높은 유저군으로 볼 수 있다.

Insight 4

LTV는 단순 리텐션보다 비즈니스 가치에 직접 연결되는 지표이다.

→ 유지율이 높은 유저보다 실제 매출을 만드는 유저를 식별하는 것이 중요하다.

시각화

LTV = ARPU × Purchase Frequency

"어떤 Cohort가 가장 가치가 높은가?"

2020-11-15 Cohort는 ARPU는 최고가 아니었지만 구매 빈도가 가장 높아 최종적으로 가장 높은 LTV(3.78)를 기록했다.

단순 매출 규모보다 고객의 반복 구매 행동이 장기 가치에 더 큰 영향을 미칠 수 있음을 확인하였다.