[GA4 코호트 분석] Day9-7 코호트 리텐션 분석 (Retention vs Purchase Analysis)

 

"오래 남는 유저가 실제 구매도 많이 하는가?"

 

분석 목적

리텐션이 높은 유저 그룹이 실제 구매 전환에서도 높은 성과를 보이는지 확인한다.

단순히 사용자를 오래 유지시키는 것이 아니라 비즈니스 성과(구매)와 연결되는 유지 전략이 필요한지 검증한다.

 

SQL

1. 코호트 생성 확인

-- 유저별 최초 방문 주차를 기준으로 cohort_week 생성
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
ORDER BY cohort_week
LIMIT 100;

 

2. 코호트별 유저 수 확인

-- cohort_week별 신규 유저 수 확인
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
  cohort_week,
  COUNT(DISTINCT user_pseudo_id) AS total_users
FROM first_session
GROUP BY cohort_week
ORDER BY cohort_week;

 

3. 유저별 활동 주차 확인

-- 유저가 session_start를 발생시킨 활동 주차 확인
SELECT DISTINCT
  user_pseudo_id,
  DATE_TRUNC(
    DATE(TIMESTAMP_MICROS(event_timestamp)),
    WEEK
  ) AS activity_week
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE event_name = 'session_start'
ORDER BY user_pseudo_id, activity_week
LIMIT 100;

 

4. 코호트별 리텐션 유저 수 확인

-- 최초 방문 이후 다시 방문한 유저 수 계산
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
),

user_activity AS (
  SELECT DISTINCT
    user_pseudo_id,
    DATE_TRUNC(
      DATE(TIMESTAMP_MICROS(event_timestamp)),
      WEEK
    ) AS activity_week
  FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
  WHERE event_name = 'session_start'
)

SELECT
  f.cohort_week,

  -- week_number가 1 이상인 경우만 재방문으로 계산
  COUNT(DISTINCT CASE
    WHEN DATE_DIFF(a.activity_week, f.cohort_week, WEEK) > 0
    THEN a.user_pseudo_id
  END) AS retained_users

FROM first_session f
LEFT JOIN user_activity a
  ON f.user_pseudo_id = a.user_pseudo_id
GROUP BY f.cohort_week
ORDER BY f.cohort_week;

 

5. 코호트별 구매 유저 수 확인

-- cohort_week별 구매 경험 유저 수 계산
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_users AS (
  SELECT DISTINCT
    user_pseudo_id
  FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
  WHERE event_name = 'purchase'
)

SELECT
  f.cohort_week,

  -- 해당 cohort에서 한 번이라도 구매한 유저 수
  COUNT(DISTINCT p.user_pseudo_id) AS purchasers

FROM first_session f
LEFT JOIN purchase_users p
  ON f.user_pseudo_id = p.user_pseudo_id
GROUP BY f.cohort_week
ORDER BY f.cohort_week;

 

6. 최종 결과

-- Part7: 코호트별 리텐션율과 구매율 비교
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
),

user_activity AS (
  -- 유저별 활동 주차 생성
  SELECT DISTINCT
    user_pseudo_id,
    DATE_TRUNC(
      DATE(TIMESTAMP_MICROS(event_timestamp)),
      WEEK
    ) AS activity_week
  FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
  WHERE event_name = 'session_start'
),

cohort_size AS (
  -- cohort_week별 전체 유저 수
  SELECT
    cohort_week,
    COUNT(DISTINCT user_pseudo_id) AS total_users
  FROM first_session
  GROUP BY cohort_week
),

retention_table AS (
  -- 최초 방문 주차 이후 다시 방문한 유저 수
  SELECT
    f.cohort_week,
    COUNT(DISTINCT CASE
      WHEN DATE_DIFF(a.activity_week, f.cohort_week, WEEK) > 0
      THEN a.user_pseudo_id
    END) AS retained_users
  FROM first_session f
  LEFT JOIN user_activity a
    ON f.user_pseudo_id = a.user_pseudo_id
  GROUP BY f.cohort_week
),

purchase_table AS (
  -- cohort_week별 구매 유저 수
  SELECT
    f.cohort_week,
    COUNT(DISTINCT p.user_pseudo_id) AS purchasers
  FROM first_session f
  LEFT JOIN (
    SELECT DISTINCT
      user_pseudo_id
    FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
    WHERE event_name = 'purchase'
  ) p
    ON f.user_pseudo_id = p.user_pseudo_id
  GROUP BY f.cohort_week
)

SELECT
  c.cohort_week,
  c.total_users,
  r.retained_users,
  p.purchasers,

  -- 리텐션율 = 재방문 유저 수 / 전체 코호트 유저 수
  ROUND(r.retained_users * 100.0 / c.total_users, 2) AS retention_rate,

  -- 구매율 = 구매 유저 수 / 전체 코호트 유저 수
  ROUND(p.purchasers * 100.0 / c.total_users, 2) AS purchase_rate

FROM cohort_size c
LEFT JOIN retention_table r
  ON c.cohort_week = r.cohort_week
LEFT JOIN purchase_table p
  ON c.cohort_week = p.cohort_week
ORDER BY c.cohort_week;

 

KPI

• Avg Retention Rate: 6.20%
• Highest Retention Rate: 12.59% (2020-11-08 Cohort)
• Avg Purchase Rate: 1.56%
• Highest Purchase Rate: 2.96% (2020-11-08 Cohort)
• Correlation (Retention ↔ Purchase): 0.872

1. 리텐션이 높은 코호트는 구매율도 높았다

2020-11-08 Cohort

• Retention Rate: 12.59%
• Purchase Rate: 2.96%

전체 기간 중 가장 높은 수치를 기록했다.

 

2021-01-31 Cohort

• Retention Rate: 0%
• Purchase Rate: 0.42%

전체 기간 중 가장 낮은 수준을 보였다.

즉, 오래 남는 유저가 실제 구매로 이어질 가능성도 높다는 패턴을 확인할 수 있었다.

 

2. 시간이 지날수록 리텐션과 구매율이 함께 감소

11월 Cohort

• 리텐션 8~12%
• 구매율 2~3%

12월 이후

• 리텐션 3~5%
• 구매율 0.5~2%

수준으로 감소하였다.

신규 유입 규모는 증가했지만 장기 유지와 구매 전환 품질은 오히려 하락한 것으로 볼 수 있다.

 

3. 강한 양의 상관관계 확인

상관계수 : 0.872

일반적으로 0.7 이상 → 강한 양의 상관관계으로 해석한다.

리텐션이 높은 코호트→ 구매율도 높은 경향

 

 

시각화

코호트별 리텐션율과 구매율 간 상관계수는 0.872로 나타났으며, 유지되는 유저가 실제 구매 성과에도 긍정적인 영향을 미치는 경향을 확인하였다.

분석 목적

코호트별 리텐션율과 구매율을 함께 분석하여 유지되는 유저가 실제 비즈니스 성과(구매)로 이어지는지 확인하였다.

핵심 결과

• 평균 리텐션율은 6.20%
• 평균 구매율은 1.56%
• 최고 리텐션과 최고 구매율은 모두 2020-11-08 Cohort에서 발생
• 리텐션과 구매율 간 상관계수는 0.872

인사이트

리텐션이 높은 코호트일수록 구매율도 높게 나타났다. 단순히 사용자를 많이 유입시키는 것보다 장기적으로 유지시키는 전략이 구매 성과 향상에도 긍정적인 영향을 줄 수 있음을 확인하였다.