[GA4 코호트 분석] Day9-2 코호트 리텐션 분석 (신규 유저 재방문율 구조화)

각 cohort_week에 유입된 신규 유저가 1주차, 2주차, 3주차…에 얼마나 다시 방문했는가?

SQL

1. 유저별 첫 방문 주차 확인

-- 1. 각 유저가 처음 방문한 주차를 구한다.
-- cohort_week = 해당 유저가 처음 session_start를 발생시킨 주차

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. 주차별 신규 유저 수 확인

-- 2. cohort_week별 신규 유저 수를 집계한다.
-- Part1에서 만든 주차별 신규 유저 규모와 같은 개념

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 cohort_users
FROM first_session
GROUP BY cohort_week
ORDER BY cohort_week;

 

3. 유저별 활동 주차 확인

-- 3. 각 유저가 session_start를 발생시킨 활동 주차를 구한다.
-- activity_week = 유저가 실제로 방문한 주차

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. 첫 방문 주차와 활동 주차 연결

-- 4. 유저별 첫 방문 주차와 이후 활동 주차를 연결한다.
-- week_number = 첫 방문 이후 몇 주차에 다시 방문했는지

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.user_pseudo_id,
  f.cohort_week,
  a.activity_week,
  DATE_DIFF(a.activity_week, f.cohort_week, WEEK) AS week_number
FROM first_session f
JOIN user_activity a
  ON f.user_pseudo_id = a.user_pseudo_id
WHERE a.activity_week >= f.cohort_week
ORDER BY f.cohort_week, f.user_pseudo_id, week_number
LIMIT 100;

 

5. 코호트별 주차별 활성 유저 수 집계

-- 5. cohort_week와 week_number별 active_users를 집계한다.
-- active_users = 해당 코호트에서 해당 주차에 다시 방문한 유저 수

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,
  DATE_DIFF(a.activity_week, f.cohort_week, WEEK) AS week_number,
  COUNT(DISTINCT a.user_pseudo_id) AS active_users
FROM first_session f
JOIN user_activity a
  ON f.user_pseudo_id = a.user_pseudo_id
WHERE a.activity_week >= f.cohort_week
GROUP BY
  f.cohort_week,
  week_number
ORDER BY
  f.cohort_week,
  week_number;

 

6. 최종 리텐션율 계산

-- 6. 최종 리텐션율을 계산한다.
-- retention_rate = active_users / cohort_users * 100

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_retention AS (
  SELECT
    f.cohort_week,
    DATE_DIFF(a.activity_week, f.cohort_week, WEEK) AS week_number,
    COUNT(DISTINCT a.user_pseudo_id) AS active_users
  FROM first_session f
  JOIN user_activity a
    ON f.user_pseudo_id = a.user_pseudo_id
  WHERE a.activity_week >= f.cohort_week
  GROUP BY
    f.cohort_week,
    week_number
),

cohort_size AS (
  SELECT
    cohort_week,
    COUNT(DISTINCT user_pseudo_id) AS cohort_users
  FROM first_session
  GROUP BY cohort_week
)

SELECT
  r.cohort_week,
  r.week_number,
  c.cohort_users,
  r.active_users,
  ROUND(r.active_users / c.cohort_users * 100, 2) AS retention_rate
FROM cohort_retention r
JOIN cohort_size c
  ON r.cohort_week = c.cohort_week
ORDER BY
  r.cohort_week,
  r.week_number;

 

전체 구조 해석

• week_number = 0 → 첫 방문 주차 (무조건 100%)
• week_number = 1 → 1주 뒤 재방문율
• week_number = 2 → 2주 뒤 재방문율

신규 유저 유입은 많지만, 다음 주 재방문율이 얼마나 유지되는지가 중요하다.

 

평균 리텐션 추이 (전체 코호트 평균)

주차	평균 리텐션
Week0	100%
Week1	약 4.02%
Week2	약 1.80%
Week3	약 1.28%
Week4	약 1.10%

신규 유저의 약 96%가 첫 주 이후 다시 돌아오지 않음

• 유입은 발생
• 첫 구매/첫 방문 이후 재방문 유지 실패 가능성 높음

초기 코호트 특징 (11월 초)

• 2020-11-01 → Week1 6.26%
• 2020-11-08 → Week1 6.56%

초기 코호트는 상대적으로 리텐션이 높음
→ 시즌성 / 초기 프로모션 / Holiday 영향 가능성

 

후반 코호트 특징 (12월~1월)

• 2020-12-20 → Week1 2.49%
• 2021-01-24 → Week1 0.93%

후반부로 갈수록 리텐션 급감

1. 신규 유입 품질 저하
2. 프로모션 유입 후 이탈
3. 첫 방문 경험은 있었지만 재방문 동기 부족
4. 구매 이후 CRM/리마케팅 약함

핵심 인사이트

현재 구조 : “Acquisition 중심”

유저는 들어오지만 유지되지 않음

 

필요한 액션

CRM / Retention 전략 필요

• 이메일 리마인드
• 장바구니 리타겟팅
• 첫 구매 후 재방문 쿠폰
• 추천 상품 UX 개선
• 재방문 퍼널 구축

GA4 코호트 분석 결과, 신규 유저 유입 규모는 유지되었지만 평균 1주차 리텐션율은 약 4% 수준으로 매우 낮았으며, 대부분의 유저가 첫 방문 이후 재방문하지 않았다. 이는 유입보다 리텐션 구조 개선이 더 중요한 과제라고 생각한다.

 

시각화

대부분의 코호트가 Week1에서 급격히 감소하였고 시간이 지나며 6% → 3% → 2%와 같이 완만히 감소하였습니다. 즉, 첫 방문 이후 재방문 비율이 낮고 초기 이탈 후 일부 충성 유저만 유지 된다는 것을 알 수 있었습니다.

후반 코호트 데이터가 짧은 이유는 데이터 수집 기간이 끝났기 때문입니다.