신규 가입자의 2주차 재방문율 감소 문제

PM과 마케팅 팀의 의견 차이

📌 비지니스 문제 시나리오

최근 월간 활성 사용자(MAU)는 안정적이지만, 신규 가입자의 2주차 재방문율이 20% 이하로 떨어짐
PM은 "추천 상품 노출 방식"이 문제라고 가설을 세웠지만,
마케팅팀은 "신규 사용자 온보딩 프로세스의 부족"이 더 큰 문제라고 주장함.

현재 팀의 핵심 과제는 리텐션(사용자 재방문) 개선입니다.

 

미션:

1. 리텐션 분석을 통해 2주차 재방문율이 낮은 원인을 규명하는 코드 작성과,
2. 작성한 SQL을 최적화된 방식으로 작성하여 분석 속도를 높이며,
3. 최종적으로 실행 계획을 예측 → 옵티마이저 관점에서 성능을 점검해야 함

 

---

➡️ Step 1. 리텐션 분석 기본 프레임 설계

이 문제를 해결해 나아가 보자.

 

❓ 리텐션을 어떤 방식으로 정의할 것인가? (코호트 vs 비코호트)

리텐션은 기본적으로 “특정 시점 이후 다시 돌아온 사용자 비율”이고,

이를 어떤 단위로 측정할지에 따라 방식이 달라진다.

 

그렇다면 던져야 하는 질문은?

 

누구를 기준으로 측정할 것인가?

A: 가입일(또는 첫 방문일)을 기준으로 삼는다 → “신규 사용자 리텐션”

B: 특정 이벤트(예: 구매, 장바구니 추가)를 기준으로 삼는다 → “이벤트 기반 리텐션”

C: 전체 활성 사용자 대상 → “전체 유저 잔존율”

→ 현재 비지니스 목표는 신규 가입자의 2주차 재방문율이니, A가 적합할 가능성이 높다.

 

리텐션을 며칠 단위로 볼까?

선택지 A: 일 단위 → D1(다음날), D7(7일차), D14(14일차), …

선택지 B: 주 단위 → W1(1주차), W2(2주차), …

선택지 C: 월 단위 → M1(1개월차), M2(2개월차), …

→ 초반에는 일 단위로 세밀히 본 다음 나중에 묶는 전략을 선택해보자.

 

누가 잔존(리텐션)했다고 볼까?

선택지 A: 앱에 한 번이라도 접속하면 잔존으로 본다 (단순 방문 리텐션)

선택지 B: 특정 행동(구매, 장바구나 추가 등)을 하면 잔존으로 본다 (행동 기반 리텐션)

→ 단순 방문으로 리텐션을 집계하게 되면 활성화 사용자 수를 과도하게 측정할 수 있으며,

식별이 불가한 사용자들로 인해 리텐션을 계산하는 데 문제가 발생할 가능성이 있다.

따라서, 특정 행동을 한 유저에 대해 잔존했다고 본다.

 

리텐션을 코호트별로 볼까요, 아니면 전체 평균으로 볼까?

선택지 A: 코호트 리텐션 → 가입 주차별로 묶어서 본다. (예: 7월 첫째주 가입자 vs 둘째 주 가입자 비교)

선택지 B: 비코호트 리텐션 → 전체 평균적으로 D7=20%, D14=10%처럼 한 눈에 본다

→ PM과 마케팅팀이 “왜 낮은지 원인 규명”을 원하므로, 코호트 리텐션이 유리할 것으로 예상

 

 

➡️ Step 2. 테이블 구조 설계

리텐션 분석에 필요한 최소 테이블 구조는 다음과 같을 것이다.

📍 유저 테이블 (users)

user_id	signup_date
101	2025-07-01
102	2025-07-03
103	2025-07-05

 

📍 유저 행동 테이블 (user_events)

→ 리텐션 정의가 “특정 행동 기반”이므로 다음 구조가 필요할 것이다.

user_id	event_date	event_type
101	2025-07-02	view_product
101	2025-07-05	purchase
102	2025-07-10	add_to_cart
103	2025-07-14	purchase

 

 

➡️ Step 3. 코호트 리텐션 SQL 초안 

위 테이블 구조에 맞춰 SQL을 작성해보자.

-- 1) 기본 코호트 리텐션 계산
SELECT
    WEEK(u.signup_date) AS cohort_week, -- 가입 주차
    DATEDIFF(e.event_date, u.signup_date) AS days_since_signup, -- 가입 후 n일차
    COUNT(DISTINCT u.user_id) AS retained_users
FROM users u
JOIN user_evnts e
    ON u.user_id = e.user_id
    AND e.event_type IN ('purchase', 'add_to_chart') -- 행동 기반 리텐션
    AND DATEDIFF(e.event_type, u.signup_date) BETWEEN 1 AND 14 -- 14일차까지
GROUP BY cohort_week, days_since_signup
ORDER BY cohort_week, days_since_signup;

 

✅ SQL이 반환하는 리텐션 표 예시

| cohort_week  | days_since_signup   | retained_users  |
| ------------ | ------------------- | --------------- |
| 2025-06-30   | 1                   | 50              |
| 2025-06-30   | 7                   | 20              |
| 2025-06-30   | 14                  | 10              |
| 2025-07-07   | 1                   | 80              |
| 2025-07-07   | 7                   | 30              |

 

 

---

🤔 SQL 튜닝 관점에서 생각해 볼 포인트는 어떤 것들이 있을까?

단계별로 질문 해보자.

 

1. 위 SQL을 실행하면 옵티마이저는 어떤 실행 계획을 선택할까?

users와 user_events는 u.user_id = e.user_id라는 동등 조인(=)으로 연결되며,

옵티마이저는 선택도(Selectivity)가 더 좋은 쪽(즉, 필터링으로 데이터가 더 줄어드는 쪽)을 먼저 엑세스 하려 할 것이다.

 

추가로 확장한다면

옵티마이저는 조인 순서를 선택할 때 users의 row 수(가입자 수)와 user_events의 이벤트 발생 수를 비교해

비용이 더 낮은 쪽을 드라이빙 테이블(먼저 읽는 테이블)로 선택한다.

 

지금 쿼리에서는 user_events가 수천만 건이라면,

옵티마이저는 users → user_events 순으로 진행하는 것이 일반적으로 유리하다.

 

이는 u.signup_date가 JOIN 이전에 먼저 코호트 기준으로 필터링되면,

이후 user_events를 조인할 때 user_id 후보 수가 훨씬 줄어든다.

1. users에서 cohort_week, signup_date 필터 -> 후보 user_id 추출
2. 후보 user_id만 user_events에서 탐색 -> IN (user_id_list)

 

 

2. e.event_type과 DATEDIFF 조건은 인덱스 사용이 가능한가?

event_type 컬럼에 인덱스를 생성하면 MySQL 옵티마이저는

IN 절에 명시된 값들을 사용하여 해당 레코드를 빠르게 찾을 수 있다.

ALTER TABLE user_events ADD INDEX idx_event_type (event_type);

 

그러나, DATEDIFF()와 같이 컬럼에 함수를 적용한 결과값에 대해서는 일반적인 인덱스를 직접 사용할 수 없다.

인덱스는 컬럼의 원래 값을 기반으로 정렬되어 있기 때문에,

쿼리 실행 시마다 함수가 계산되므로, 인덱스된 값을 활용할 수 없다.

 

그럼 어떡하죠?

event_date 자체를 활용하는 범위 조건으로 변환하여 간접적으로 활용할 수 있다.

e.event_date BETWEEN DATE_ADD(u.signup_date, INTERVAL 1 DAY)
AND DATE_ADD(u.signup_date, INTERVAL 14 DAY)

 

 

3. 만약 user_events가 수천만 건이라면, 이 SQL의 성능 병목은 어디에서 생길까?

옵티마이저는 2가지의 병목을 겪는다.

 

1. user_events의 Full-Scan 위험

JOIN 순서가 잘못 잡히거나 DATEDIFF로 인해 인덱스를 활용하지 못하면,

옵티마이저가 user_events를 Full-scan 해야한다.

 

2. GROUP BY 비용

-- JOIN 이후
GROUP BY cohort_week, days_since_signup

 

GROUP BY는 JOIN 이후 수행되므로, JOIN 결과가 커지면 GROUPING 비용이 급증한다.

 

 

💡 병목 완화 전략

1. 사전 필터링

users에서 2주 이내 가입자만 먼저 필터링해 드라이빙 테이블의 크기를 줄인다.

-- 1, 사전 필터링을 통한 튜닝
-- 조인 전에 각 테이블을 먼저 필터링하여 처리할 데이터량 감소
SELECT
    WEEK(filtered_users.signup_date) AS cohort_week,
    DATEDIFF(filtered_events.event_date, filtered_users.signup_date) AS days_since_signup,
    COUNT(DISTINCT filtered_users.user_id) AS retained_users
FROM (
    SELECT user_id, signup_date
    FROM users
    WHERE signup_date >= DATE_SUB(CURDATE(), INTERVAL 3 MONTH)
) filtered_users
JOIN (
    -- 이벤트 테이블 사전 필터링 (관련 이벤트만)
    SELECT user_id, event_date
    FROM user_events
    WHERE event_type IN ('purchase', 'add_to_cart')
        AND event_date >= DATE_SUB(CURDATE(), INTERVAL 3 MONTH)
) filtered_events ON filtered_users.user_id = filtered_events.user_id
WHERE DATEDIFF(filtered_events.event_date, filtered_users.signup_date) BETWEEN 1 AND 14
GROUP BY cohort_week, days_since_signup
ORDER BY cohort_week, days_since_signup

 

2. 인덱스 재설계

user_events에 (event_type, event_date, user_id) 복합 인덱스를 고려한다.

-- 2. 인덱스 재설계를 통한 튜닝
-- user 테이블 인덱스
CREATE INDEX idx_users_signup_date_id ON users(signup_date, user_id);

-- user_events 테이블 인덱스
CREATE INDEX idx_events_type_date_user ON user_events(event_type, event_date, user_id);
CREATE INDEX idx_events_user_type_date ON user_events(user_id, event_type, event_date);

-- 인덱스 활용 최적화된 쿼리
SELECT
    WEEK(u.signup_date) AS cohort_week,
    DATEDIFF(e.event_date, u.signup_date) AS days_since_signup,
    COUNT(DISTINCT u.user_id) AS retained_users
FROM users u
INNER JOIN user_events e ON u.user_id = e.user_id
WHERE u.signup_date >= DATE_SUB(CURDATE(), INTERVAL 3 MONTH) -- 인덱스 활용
    AND e.event_type IN('purchase', 'add_to_cart') -- 인덱스 활용
    AND e.event_date >= u.signup_date -- 불필요한 과거 이벤트 제외
    AND e.event_date <= DATE_ADD(u.signup_date, INTERVAL 14 DAY) -- 범위 최적화
GROUP BY cohort_week, days_since_signup
ORDER BY cohort_week, days_since_signup;

 

3. Subquery로 이벤트 필터링 후 조인

옵티마이저가 강제로 필터 순서를 바꾸게끔 유도하는 방법도 있다.

-- 3. 서브쿼리로 이벤트 필터링 후 조인하는 튜닝
-- 관련 이벤트가 있는 사용자만 먼저 식별한 후 조인
WITH cohort_users AS (
    -- Step 1: 코호트별 사용자 기본 정보
    SELECT
        user_id,
        signup_date,
        WEEK(signup_date) AS cohort_week
    FROM users
    WHERE signup_date >= DATE_SUB(CURDATE(), INTERVAL 3 MONTH)
),
relevant_events AS (
    -- Step 2: 리텐션 관련 이벤트만 추출
    SELECT
        e.user_id,
        e.event_date
    FROM user_events e
    INNER JOIN cohort_users cu ON e.user_id = cu.user_id
    WHERE e.event_type IN ('purchase', 'add_to_cart')
        AND e.event_date > cu.signup_date -- 가입일 이후만
        AND e.event_date <= DATE_ADD(cu.signup_date, INTERVAL 14 DAY) -- 14일 이내만
),
retention_data AS (
    -- Step 3: 리텐션 데이터 계산
    SELECT
        cu.cohort_week,
        cu.user_id,
        DATEDIFF(re.event_date, cu.signup_date) AS days_since_signup
    FROM cohort_users cu
    INNER JOIN relevant_events re ON cu.user_id = re.user_id
)
-- Step 4: 최종 집계
SELECT cohort_week,
       days_since_signup,
       COUNT(DISTINCT user_id) AS retained_users
FROM retention_data
GROUP BY cohort_week, days_since_signup
ORDER BY cohort_week, days_since_signup;

 

 

이렇게 병목 완화 쿼리를 작성했다면,

개선된 성능을 측정하는 쿼리도 작성해 확인해야 한다.

-- 성능 측정 및 모니터링 쿼리
-- 실행 계획 확인
EXPLAIN EXTENDED [위의 튜닝된 쿼리 중 하나];

-- 인덱스 사용률 확인
SHOW INDEX FROM users;
SHOW INDEX FROM user_events;

-- 테이블 통계 업데이트
ANALYZE TABLE users, user_events;

 

옵티마이저가 실행계획을 세울 때 옵티마이저에게 중요한 정보를 제공하는

카탈로그 매니저 (테이블 통계)를 반드시 업데이트 해야 한다.

 

만약 옵티마이저가 잘못된 실행 계획을 선택했을 때 최후의 수단으로 사용하는 '힌트 구'라는 것도 존재한다.

예를 들어, 옵티마이저가 user_events를 먼저 드라이빙 테이블로 선택해 풀스캔한다면,

힌트 구로 users를 먼저 읽게 강제할 수 있다.

-- LEADING(u e) -> u(users)를 먼저 읽고 e(user_events)를 조인하라는 의미
SELECT /*+ LEADING(u e) */ 
       DATE_TRUNC('week', u.signup_date) AS cohort_week, ...
FROM users u
JOIN user_events e
  ON u.user_id = e.user_id
...