[인프런 빅쿼리 빠짝스터디 3주차] 제품 현황 분석 : 리텐션 분석

이상해씨·2024년 11월 1일

BigQuery sql 빅쿼리 빠짝스터디 인프런

SQL

목록 보기

19/22

리텐션 (RETENTION)

📌 리텐션

시간에 따른 제품 사용을 측정하는 지표. customer가 제품을 재이용하는 단계.

기존 고객의 유지하는 것이 신규고객의 구매유도 보다 최대 25배의 비용을 줄일 수 있다. 하버드 비즈니스 인터뷰
특히, 고객유치가 되면, 입소문이 나게 되고 신규고객을 위한 마케팅 비용을 줄일 수 있다는 장점!

리텐션 분석

제품 특성(자주 사용하는 제품이 있고, 1회성으로 사용하는 제품/ 유료, 무료 인 제품)에 따라 리텐션을 분석할 때, 고려해야하는 부분이 달라짐. 예를 들어, 1회성으로 사용할 경우 리텐션이 적음
PMF(Product Market Fit) : 제품과 시장의 적합성. 시장의 요구와 고객의 필요를 충족시키는 정도. 리텐션 커브에서 평평한 구간!. 어떻게 하면 PMF을 유지할 수 있을 지 생각해보는 것이 리텐션의 목표!

리텐션 분석시 생각해보면 좋은 부분들!

리텐션 정의
: CORE를 접속으로 볼 것인가? 다른 EVENT로 볼 것인가?
주기에 따라 관점이 달라진다.
: 여러 페르소나가 있는 경우, 주기에 따라 관점이 달라짐
예상 주기가 실제와 동일한 지 파악
: 예상과 실제와 다를 경우, 잘 이야기하는 능력. 결과 지표가 많을 경우, 구체적으로 잘게 쪼개서 작업.
고객의 입장에서 재방문 요소 생각해보기
가설을 업데이트하기

처음 리텐션 접근시, 접속기록 > 핵심 이벤트처럼 점점 구체적으로 파악하는 것이 좋음.
히스토그램을 그리면 제품의 사용주기 확인이 쉬움
: 오른쪽일 수록 긴 주기

리텐션 지표

1. CLASSIC 리텐션(N DAY 리텐션)
: 유저가 최조로 참여한 DAY0 이후에 DAY N 별로 참여했는 지 계산. 낮은 값이 나올 수 있음. 보수적. 정기적이거나 하루단위
2. RANGE 리텐션(BRACKER 리텐션)
: 특정 RANGE 내에서 또 참여했는지 계산. 유용하고 실용적. 주차별.
3. ROLLING 리텐션(UNBOUNDED 리텐션)
: DAY N 이후 한 번이라도 참여했을 경우 그 동안 모두 참여한 것으로 간주. 높은 값이 나올 수 있음. 유저의 최근 참여에 따라 데이터 결과가 달라짐. 장기적, 사용주기가 긴경우
4. 리텐션 커브 (Retention Curve)
: 코호트 리텐션을 차트로 시각화한 것. 시각적으로 표현해서 직관적.
5. 코호트 리텐션(Cohert Retention)
: 동질적 특성을 가진 사람들의 집합. 보통, 동일한 시기에 가입한 사용자들을 지칭하는 용어로 사용됨.

리텐션 커브

커브를 전체덕으로 위로 올리되, 평평한 그래프가 좋음. (customer가 유지되고 있다는 뜻)
평평한 리텐션은 언제 생기는지 확인
리텐션을 면적관점으로 생각하기

⭐ 리텐션 SQL로 구현하기

1. CLASSIC 리텐션(N DAY 리텐션)

n day retention은 특정 날짜, n일 뒤에도 서비스를 사용했는 지 확인
쿼리 구성
1. 가입 또는 첫 이용일 계산
: min()을 사용하여 최초일 계산
2. n일 뒤에도 이용 중인지 계산
: where을 통해 특정 기간의 데이터 중
3. 재이용까지 걸린 날짜 계산
: join, datediff로 재이용 또는 재접속 까지 걸린 기간을 계산
예시
: n을 7일으로 뒀을 경우의 예시

WITH first_activity AS (
    SELECT
        user_id,
        MIN(activity_date) AS first_activity_date
    FROM
        user_activity
    GROUP BY
        user_id
)
SELECT
    COUNT(DISTINCT user_id) AS day_7_retention
FROM
    first_activity f
JOIN
    user_activity a ON f.user_id = a.user_id
WHERE
    DATEDIFF(a.activity_date, f.first_activity_date) = 7;

2. RANGE 리텐션(BRACKER 리텐션)

특정 범위내에서 사용자가 활동했는지 확인
쿼리 구성
1. 가입 또는 첫 이용일 계산
: min()을 사용하여 최초일 계산
2. 특정 기간에서의 사용자 수 계산
: count, case절, datediff를 통해 특정 기간애 서비스를 이용한 사용자의 수 계산
예시
: 1~3, 4~7일인 두 경우에 대한 계산

WITH first_activity AS (
    SELECT
        user_id,
        MIN(activity_date) AS first_activity_date
    FROM
        user_activity
    GROUP BY
        user_id
)
SELECT
    f.user_id,
    f.first_activity_date,
    -- 첫 이용 이후 1일~3일 범위에 활동한 사용자
    COUNT(CASE WHEN DATEDIFF(a.activity_date, f.first_activity_date) BETWEEN 1 AND 3 THEN 1 END) AS day_1_to_3_retention,
    -- 첫 이용 이후 4일~7일 범위에 활동한 사용자
    COUNT(CASE WHEN DATEDIFF(a.activity_date, f.first_activity_date) BETWEEN 4 AND 7 THEN 1 END) AS day_4_to_7_retention
FROM
    first_activity f
JOIN
    user_activity a ON f.user_id = a.user_id
GROUP BY
    f.user_id, f.first_activity_date

3. ROLLING 리텐션(UNBOUNDED 리텐션)

첫 활동 이후 재접속의 지속성 추적. 재접속을 했는지에 대한 여부 (서비스 이용의 지속성)
range와 달리, 날짜에 제한이 없고, 다시 돌아오는지 추적
첫 접속이후, 3일 7일 15일 등 특정 기간에 최소 한 번 재접속했는지 확인
사용자가 얼마나 빨리 돌아오는지, 자주 오는 지 측정
쿼리 구성
1. 가입 또는 첫 이용일 계산
: min()을 사용하여 최초일 계산
2. 특정 기간에서의 사용자 수 계산
: count, case절, datediff를 통해 특정 기간애 서비스를 이용한 사용자의 수 계산

WITH first_activity AS (
    SELECT
        user_id,
        MIN(activity_date) AS first_activity_date
    FROM
        user_activity
    GROUP BY
        user_id
)
SELECT
    f.user_id,
    f.first_activity_date,
    -- 첫 이용 이후 1일 이내에 활동한 사용자 수
    COUNT(CASE WHEN DATEDIFF(a.activity_date, f.first_activity_date) <= 1 THEN 1 END) AS day_1_retention,
    -- 첫 이용 이후 7일 이내에 활동한 사용자 수
    COUNT(CASE WHEN DATEDIFF(a.activity_date, f.first_activity_date) <= 7 THEN 1 END) AS day_7_retention,
    -- 첫 이용 이후 30일 이내에 활동한 사용자 수
    COUNT(CASE WHEN DATEDIFF(a.activity_date, f.first_activity_date) <= 30 THEN 1 END) AS day_30_retention
FROM
    first_activity f
JOIN
    user_activity a ON f.user_id = a.user_id
GROUP BY
    f.user_id, f.first_activity_date

RANGE와 ROLLING의 차이

range와 rolling은 sql 쿼리적으로 동일할 수 있으나, 측정하는 관점에 따라 나뉨
range는 정해진 날짜 범위에 활동을 했는 지 여부. 단 고정된 날짜 범위 내에서 각각의 기간이 끝날 때, 그 날짜를 기준으로 활동한 사용자 수 계산
rolling은 정해진 날짜 범위에 사용자가 기간 내에 들어왔는 지 누적적으로 계산

예로 1일, 7일, 30일에 다시 접속했는지 여부를 추적

Range Retention
사용자 A: 첫 사용일 2024-10-01
1일 리텐션: 2024-10-02에 다시 접속
7일 리텐션: 2024-10-08에 다시 접속
30일 리텐션: 2024-10-31에 다시 접속
Rolling Retention
사용자 A: 첫 사용일 2024-10-01
1일 리텐션: 첫 사용일 이후 1일 동안 재접속한 사용자
7일 리텐션: 첫 사용일 이후 7일 이내 재접속한 사용자
30일 리텐션: 첫 사용일 이후 30일 이내 재접속한 사용자