인프런 커뮤니티 질문&답변

1)WeeklyRetention을구하는쿼리를바닥부터스스로작성해보세요

WITH event_log_base AS (
    SELECT DISTINCT
        DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_datetime,
        DATE(DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul')) AS event_date,
        DATE_TRUNC(DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul'), WEEK(MONDAY)) AS event_week,
        DATE_TRUNC(DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul'), MONTH) AS event_month,
        user_id,
        user_pseudo_id,
        event_name,
        platform,
        event_params
    FROM advanced.app_logs
),

user_first_visit AS (
    SELECT
        user_pseudo_id,
        MIN(event_week) OVER (PARTITION BY user_pseudo_id) AS first_visit_week,
        event_week
    FROM event_log_base
),


weekly_retention_base AS (
    SELECT
        first_visit_week,
        DATE_DIFF(event_week, first_visit_week, WEEK) AS week_diff,
        COUNT(DISTINCT user_pseudo_id) AS active_users
    FROM user_first_visit
    GROUP BY first_visit_week, week_diff
)


SELECT
    first_visit_week,
    week_diff,
    active_users,
    SAFE_DIVIDE(active_users, 
                FIRST_VALUE(active_users) OVER (PARTITION BY first_visit_week ORDER BY week_diff ASC)) AS retention_rate
FROM weekly_retention_base
ORDER BY first_visit_week, week_diff;

2)RetainUser를New+Current+Resurrected+DormantUser로나누는쿼리를 작성해보세요.

신규 사용자: 첫 방문 후 30일 이내에 활동한 사용자. 첫 방문 날짜와 마지막 방문 날짜 간의 차이가 30일 이하일 경우

현재 사용자: 첫 방문 후 30일이 지난 사용자 중, 최근 30일 이내에 접속한 사용자. 첫 방문과 마지막 방문 간 차이가 30일을 넘고, 가장 최근 활동으로부터 30일 이내에 접속한 경우

휴면 사용자: 마지막 방문 이후 30일 이상 비활동 상태에 있는 사용자. 마지막 방문이 가장 최근 이벤트로부터 30일 이상 경과한 경우

복귀 사용자: 30일 이상 비활동 상태였다가 다시 활동을 시작한 사용자. 이전 방문 이후 30일 동안 비활동이 이어지다 다시 방문한 경우

WITH event_data AS (
    SELECT DISTINCT
        DATE(DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul')) AS event_date,
        user_pseudo_id
    FROM advanced.app_logs
),

-- 사용자별 첫 방문 및 마지막 방문 날짜 계산
user_first_last_visit AS (
    SELECT
        user_pseudo_id,
        MIN(event_date) AS first_visit_date,
        MAX(event_date) AS last_visit_date
    FROM event_data
    GROUP BY user_pseudo_id
),

-- 전체 이벤트 데이터에서 가장 최근의 이벤트 날짜를 계산
latest_event_date AS (
    SELECT MAX(event_date) AS current_date
    FROM event_data
),

-- 사용자의 이벤트 로그를 통해 비활동 기간을 기록하여 휴면 여부 판단
user_activity AS (
    SELECT
        user_pseudo_id,
        event_date,
        LEAD(event_date) OVER (PARTITION BY user_pseudo_id ORDER BY event_date) AS next_event_date,
        CASE
            WHEN DATE_DIFF(LEAD(event_date) OVER (PARTITION BY user_pseudo_id ORDER BY event_date), event_date, DAY) > 30 THEN 1
            ELSE 0
        END AS dormant_history
    FROM event_data
),

-- 사용자 유형 분류: 신규, 현재, 휴면, 복귀 사용자
user_category AS (
    SELECT
        u.user_pseudo_id,
        u.first_visit_date,
        u.last_visit_date,
        led.current_date,
        MAX(ua.dormant_history) AS dormant_history,
        CASE 
            WHEN DATE_DIFF(led.current_date, u.last_visit_date, DAY) > 30 THEN 'inactive_user'
            WHEN DATE_DIFF(u.last_visit_date, u.first_visit_date, DAY) <= 30 THEN 'first_month_user'
            WHEN MAX(ua.dormant_history) = 1 THEN 'returning_user'
            ELSE 'active_user'
        END AS user_type
    FROM user_first_last_visit AS u
    CROSS JOIN latest_event_date AS led
    LEFT JOIN user_activity AS ua ON u.user_pseudo_id = ua.user_pseudo_id
    GROUP BY u.user_pseudo_id, u.first_visit_date, u.last_visit_date, led.current_date
),

-- 사용자 유형별 첫 방문 주차와 현재 주차 간 차이 계산
user_weekly_retention AS (
    SELECT
        uc.user_type,
        ed.user_pseudo_id,
        ed.event_date,
        DATE_DIFF(DATE_TRUNC(ed.event_date, WEEK(MONDAY)), DATE_TRUNC(uc.first_visit_date, WEEK(MONDAY)), WEEK) AS week_difference
    FROM event_data AS ed
    JOIN user_category AS uc ON ed.user_pseudo_id = uc.user_pseudo_id
),

-- 사용자 유형과 주차별 활성 사용자 수 집계
user_count_by_type_and_week AS (
    SELECT
        user_type,
        week_difference,
        COUNT(DISTINCT user_pseudo_id) AS user_count
    FROM user_weekly_retention
    GROUP BY user_type, week_difference
),

-- 유지율 계산을 위한 초기 사용자 수와 현재 주차별 유지율 계산
retention_calculation AS (
    SELECT
        user_type,
        week_difference,
        user_count,
        FIRST_VALUE(user_count) OVER (PARTITION BY user_type ORDER BY week_difference) AS initial_user_count
    FROM user_count_by_type_and_week
)

SELECT
    user_type,
    week_difference,
    ROUND(SAFE_DIVIDE(user_count, initial_user_count), 2) AS retention_rate
FROM retention_calculation
ORDER BY user_type, week_difference;

3)주어진데이터에서어떤사람들이리텐션이그나마높을까요?찾아보세요

2번을 참고해서 고객을 4개의 타입으로 분류하였을 때

first_month_user: 첫 주 이후 리텐션이 높지만 이후 감소하는 모습을 보인다.

active_user: first_month_user와 비슷하게 첫 주 이후 빠르게 감소한다.

returning_user: 2주부터 0.08로 떨어지지만 이후 완만하게 리텐션이 감소한다.

오랜기간 10주차 이후에도 일부 사용자가 유지되고 있는 모습을 보인다.

inactive_user: 리텐션이 다른 유저에 비해 높다고는 할 수 없지만 20주차까지 꾸준하게 소수의사용자가 유지되고 있는 모습을 보인다.

--> 따라서 returning_use이 가장 완만하게 리텐션이 감소, 가장 오랫동안 리텐션이 지속했기때문에 가장 높은 리텐션을 유지했다고 판단하였습니다.

4)CoreEvent를“click_payment”라고설정하고WeeklyRetention을구해주세요

WITH event_data AS (
    SELECT DISTINCT
        user_pseudo_id,
        DATE(DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul')) AS event_date,
        DATE_TRUNC(DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul'), WEEK(MONDAY)) AS event_week,
        event_name
    FROM advanced.app_logs
    WHERE event_name = 'click_payment'  -- Core event 필터링
),

-- 사용자별 첫 방문 주차와 현재 이벤트 주차 간의 차이 계산
user_first_week_data AS (
    SELECT
        user_pseudo_id,
        MIN(event_week) OVER (PARTITION BY user_pseudo_id) AS first_visit_week,
        event_week AS current_week,
        DATE_DIFF(event_week, MIN(event_week) OVER (PARTITION BY user_pseudo_id), WEEK) AS week_difference
    FROM event_data
),

-- 주차별 사용자 수 집계
user_count_by_week AS (
    SELECT
        first_visit_week,
        week_difference,
        COUNT(DISTINCT user_pseudo_id) AS user_count
    FROM user_first_week_data
    GROUP BY first_visit_week, week_difference
    ORDER BY first_visit_week, week_difference
),

-- 유지율 계산을 위한 초기 사용자 수와 주차별 유지율 계산
retention_base AS (
    SELECT
        first_visit_week,
        week_difference,
        user_count,
        FIRST_VALUE(user_count) OVER (PARTITION BY first_visit_week ORDER BY week_difference) AS initial_user_count
    FROM user_count_by_week
)

-- 최종 리텐션 비율 계산 및 결과 출력
SELECT
    first_visit_week,
    week_difference,
    ROUND(SAFE_DIVIDE(user_count, initial_user_count), 2) AS retention_rate
FROM retention_base
ORDER BY first_visit_week, week_difference;