[GA4 코호트 분석] Day9-3 코호트 리텐션 분석 (Purchase vs Non-Purchase 리텐션 비교 분석)

"구매 유저가 더 오래 남는가?"

가설

구매 경험이 있는 유저는 비구매 유저보다 리텐션이 높을 것이다.

그룹	기준
Purchase User	purchase 이벤트 경험 있음
Non-Purchase User	purchase 이벤트 없음

 

SQL

1. 유저별 구매 여부 정의

-- 유저별 구매 경험 여부 확인

SELECT
  user_pseudo_id,

  -- purchase 이벤트가 1번 이상 있으면 Purchase User
  CASE
    WHEN COUNTIF(event_name = 'purchase') > 0 THEN 'Purchase User'
    ELSE 'Non-Purchase User'
  END AS user_group

FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`

GROUP BY user_pseudo_id

LIMIT 100;

 

2. 유저별 최초 방문 주차 만들기

-- 유저별 최초 방문 주차 계산

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

LIMIT 100;

 

3. 유저별 활동 주차 만들기

-- 유저별 활동 주차 계산

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'

LIMIT 100;

 

4. 구매 여부별 리텐션 유저 수 계산

-- 구매 유저 vs 비구매 유저의 주차별 활동 유저 수 계산

WITH user_group AS (
  SELECT
    user_pseudo_id,

    CASE
      WHEN COUNTIF(event_name = 'purchase') > 0 THEN 'Purchase User'
      ELSE 'Non-Purchase User'
    END AS user_group

  FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`

  GROUP BY user_pseudo_id
),

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
  g.user_group,
  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

JOIN user_group g
  ON f.user_pseudo_id = g.user_pseudo_id

GROUP BY
  g.user_group,
  f.cohort_week,
  week_number

ORDER BY
  g.user_group,
  f.cohort_week,
  week_number;

 

5. 최종 리텐션율 계산

-- 구매 유저 vs 비구매 유저 리텐션율 계산

WITH user_group AS (
  SELECT
    user_pseudo_id,

    CASE
      WHEN COUNTIF(event_name = 'purchase') > 0 THEN 'Purchase User'
      ELSE 'Non-Purchase User'
    END AS user_group

  FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`

  GROUP BY user_pseudo_id
),

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'
),

retention AS (
  SELECT
    g.user_group,
    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

  JOIN user_group g
    ON f.user_pseudo_id = g.user_pseudo_id

  GROUP BY
    g.user_group,
    f.cohort_week,
    week_number
),

cohort_size AS (
  SELECT
    user_group,
    cohort_week,

    -- 각 그룹/코호트의 Week0 유저 수
    active_users AS cohort_users

  FROM retention

  WHERE week_number = 0
)

SELECT
  r.user_group,
  r.cohort_week,
  r.week_number,
  r.active_users,
  c.cohort_users,

  -- 리텐션율 계산
  ROUND(r.active_users / c.cohort_users * 100, 2) AS retention_rate

FROM retention r

JOIN cohort_size c
  ON r.user_group = c.user_group
 AND r.cohort_week = c.cohort_week

ORDER BY
  r.user_group,
  r.cohort_week,
  r.week_number;

분석 해석

분석 결과, 구매 유저는 비구매 유저보다 전반적으로 더 높은 리텐션을 보이는 경향이 나타났습니다. 이는 구매 경험이 단순한 결제 이벤트가 아니라, 유저의 서비스 몰입도와 재방문 가능성을 높이는 신호로 해석될 수 있습니다. 반면 비구매 유저는 첫 방문 이후 빠르게 이탈하는 비중이 높아, 구매 경험 여부에 따라 유지율 차이가 발생하는 것으로 보입니다.

결론

따라서 이번 데이터는 “구매 경험이 있는 유저가 비구매 유저보다 리텐션이 높을 것이다”라는 가설을 어느 정도 지지합니다. 다만 구매 자체가 리텐션을 직접 만든다기보다, 이미 서비스에 관심이 높거나 만족도가 높은 유저가 구매로 이어졌을 가능성도 함께 고려해야 합니다.

액션 인사이트

앞으로는 구매 유저의 재방문을 더 강화할 수 있도록 구매 후 CRM과 리마인드 전략을 설계해야 합니다. 동시에 비구매 유저가 첫 방문 이후 이탈하지 않도록 온보딩, 리타겟팅, 첫 행동 유도 장치를 보완할 필요가 있습니다.

 

 

• 구매 유저의 평균 리텐션율은 11.77%로 나타났으며, 비구매 유저(1.13%) 대비 약 10배 이상 높게 유지됨
• 특히 초기 1~3주차 구간에서 리텐션 차이가 크게 발생
• 구매 경험 자체가 장기 재방문 행동과 강하게 연결되는 핵심 행동으로 해석 가능