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

최근에, 면접이 있어서 공부를 제대로 못했네요. 오늘부터 매일 매일 못했던 공부를 다시 해보려고 합니다.

문제: core_event를 'click_payment'라고 설정하고, Weekly Retention을 구하여라.

저는 click_payment 까지의 사용자 여정 별로 세그먼트를 나누려는 시도를 해보았습니다. 문제에 대한 고민사항은 길어지니, 최종적으로 나누었던 세그먼트를 먼저 소개해드리겠습니다.

click_payment 까지의 여정은 크게 다음과 같이 구성할 수 있습니다.

• click_search(검색) -> click_payment

• click_banner(배너 클릭) -> click_payment

• click_food_category(음식 카테고리 클릭) -> click_payment

• click_restaurant_nearby( 내 위치 기반 주변 레스토랑) -> click_payment

• click_recommend_food(추천) -> click_payment

크게 다음과 같이 5개의 세그먼트로 나누어보고, 각 세그먼트 별로, Count를 해보았습니다. 이 때, click_login -> click_search -> .... -> click_payment -> click_search .... 이런 경우를 대비해서, 각 이벤트마다 제일 처음 이벤트가 발생한 시간 을 기준으로 구분하도록 하였습니다.

이제 각 퍼널별로 리텐션을 구해보겠습니다.

WITH user_events AS (
    SELECT 
        user_pseudo_id,
        event_name,
        event_date,
        DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_timestamp,
        ROW_NUMBER() OVER (PARTITION BY user_pseudo_id, event_name ORDER BY event_timestamp) AS r_num
    FROM advanced.app_logs
    WHERE event_name IN ('click_search', 'click_banner', 'click_food_category', 'click_restaurant_nearby', 'click_recommend_food', 'click_payment')
      AND event_name NOT IN ('screen_view', 'click_login')
),
FIRST_EVENTS AS (
    SELECT 
        *,
        DATE_DIFF(event_week, first_week, WEEK) AS diff_of_week
    FROM (
        SELECT 
            user_pseudo_id,
            event_date,
            DATE_TRUNC(MIN(event_date) OVER (PARTITION BY user_pseudo_id), WEEK(MONDAY)) AS first_week,
            DATE_TRUNC(event_date, WEEK(MONDAY)) AS event_week,
            MIN(CASE WHEN event_name = 'click_search' THEN event_timestamp END) OVER (PARTITION BY user_pseudo_id, event_date) AS first_search_time,
            MIN(CASE WHEN event_name = 'click_payment' THEN event_timestamp END) OVER (PARTITION BY user_pseudo_id, event_date) AS first_payment_time,
            MIN(CASE WHEN event_name = 'click_banner' THEN event_timestamp END) OVER (PARTITION BY user_pseudo_id, event_date) AS first_banner_time,
            MIN(CASE WHEN event_name = 'click_food_category' THEN event_timestamp END) OVER (PARTITION BY user_pseudo_id, event_date) AS first_food_category_time,
            MIN(CASE WHEN event_name = 'click_restaurant_nearby' THEN event_timestamp END) OVER (PARTITION BY user_pseudo_id, event_date) AS first_restaurant_nearby_time,
            MIN(CASE WHEN event_name = 'click_recommend_food' THEN event_timestamp END) OVER (PARTITION BY user_pseudo_id, event_date) AS first_recommend_food_time
        FROM user_events
    )
),
FUNNEL_CLASSIFICATION AS (
    SELECT DISTINCT
        *,
        CASE
            WHEN first_search_time IS NOT NULL AND first_payment_time IS NOT NULL AND first_search_time < first_payment_time THEN 'click_search -> click_payment'
            WHEN first_banner_time IS NOT NULL AND first_payment_time IS NOT NULL AND first_banner_time < first_payment_time THEN 'click_banner -> click_payment'
            WHEN first_food_category_time IS NOT NULL AND first_payment_time IS NOT NULL AND first_food_category_time < first_payment_time THEN 'click_food_category -> click_payment'
            WHEN first_restaurant_nearby_time IS NOT NULL AND first_payment_time IS NOT NULL AND first_restaurant_nearby_time < first_payment_time THEN 'click_restaurant_nearby -> click_payment'
            WHEN first_recommend_food_time IS NOT NULL AND first_payment_time IS NOT NULL AND first_recommend_food_time < first_payment_time THEN 'click_recommend_food -> click_payment'
            ELSE 'Other'
        END AS funnel
    FROM FIRST_EVENTS
    WHERE first_payment_time IS NOT NULL
),
PIVOTED_ANALYSIS AS (
    SELECT 
        diff_of_week,
        COUNT(DISTINCT CASE WHEN funnel = 'click_search -> click_payment' THEN user_pseudo_id END) AS user_cnt_search_payment,
        COUNT(DISTINCT CASE WHEN funnel = 'click_banner -> click_payment' THEN user_pseudo_id END) AS user_cnt_banner_payment,
        COUNT(DISTINCT CASE WHEN funnel = 'click_food_category -> click_payment' THEN user_pseudo_id END) AS user_cnt_food_category_payment,
        COUNT(DISTINCT CASE WHEN funnel = 'click_restaurant_nearby -> click_payment' THEN user_pseudo_id END) AS user_cnt_restaurant_nearby_payment,
        COUNT(DISTINCT CASE WHEN funnel = 'click_recommend_food -> click_payment' THEN user_pseudo_id END) AS user_cnt_recommend_food_payment
    FROM FUNNEL_CLASSIFICATION
    GROUP BY diff_of_week
),
INITIAL_USERS AS (
    SELECT
        FIRST_VALUE(user_cnt_search_payment) OVER (ORDER BY diff_of_week) AS first_user_cnt_search_payment,
        FIRST_VALUE(user_cnt_banner_payment) OVER (ORDER BY diff_of_week) AS first_user_cnt_banner_payment,
        FIRST_VALUE(user_cnt_food_category_payment) OVER (ORDER BY diff_of_week) AS first_user_cnt_food_category_payment,
        FIRST_VALUE(user_cnt_restaurant_nearby_payment) OVER (ORDER BY diff_of_week) AS first_user_cnt_restaurant_nearby_payment,
        FIRST_VALUE(user_cnt_recommend_food_payment) OVER (ORDER BY diff_of_week) AS first_user_cnt_recommend_food_payment
    FROM PIVOTED_ANALYSIS
    LIMIT 1
)
SELECT
    pa.diff_of_week,
    pa.user_cnt_search_payment AS search_active_user,
    pa.user_cnt_banner_payment AS banner_active_user,
    pa.user_cnt_food_category_payment AS category_active_user,
    pa.user_cnt_restaurant_nearby_payment AS nearby_active_user,
    pa.user_cnt_recommend_food_payment AS recommend_active_user,
    iu.first_user_cnt_search_payment AS search_cohort_user,
    iu.first_user_cnt_banner_payment AS banner_cohort_user,
    iu.first_user_cnt_food_category_payment AS category_cohort_user,
    iu.first_user_cnt_restaurant_nearby_payment AS nearby_cohort_user,
    iu.first_user_cnt_recommend_food_payment AS recommend_cohort_user,
    ROUND(SAFE_DIVIDE(pa.user_cnt_search_payment, iu.first_user_cnt_search_payment), 3) AS retention_week_rate_search_payment,
    ROUND(SAFE_DIVIDE(pa.user_cnt_banner_payment, iu.first_user_cnt_banner_payment), 3) AS retention_week_rate_banner_payment,
    ROUND(SAFE_DIVIDE(pa.user_cnt_food_category_payment, iu.first_user_cnt_food_category_payment), 3) AS retention_week_rate_food_category_payment,
    ROUND(SAFE_DIVIDE(pa.user_cnt_restaurant_nearby_payment, iu.first_user_cnt_restaurant_nearby_payment), 3) AS retention_week_rate_restaurant_nearby_payment,
    ROUND(SAFE_DIVIDE(pa.user_cnt_recommend_food_payment, iu.first_user_cnt_recommend_food_payment), 3) AS retention_week_rate_recommend_food_payment
FROM PIVOTED_ANALYSIS pa, INITIAL_USERS iu
ORDER BY pa.diff_of_week;

1000자 이내로 작성해야 게시글 하나를 쓸 수 있음..

screen_view -> screen_view -> click_login의 경우, 사용자가 로그인을 하지 않고, 앱이 잠시 백그라운드 상에 동작중인 상태에서, 다시 앱을 켰을 때, screen_view 로그가 찍히는 것을 확인했습니다.

현재 집중해야 할 부분은 사용자가 상품을 들여다보는 시간이나, item을 찾을 때, UI/UX 적으로 개선할 부분이 있는지 찾기보다, click_payment를 하기까지의 주요 이벤트 여정을 세그먼트로 분류하는 작업을 하고 있기 때문에, screen_view 이벤트는 제외해야겠다는 생각을 했습니다.

또한, screen_view -> click_login -> screen_view -> screen_view -> click_login 처럼, 이전 이벤트와 현재 이벤트가 같은 경우를 제외한 다른 경우만을 필터링해서, 굵직한 이벤트만을 필터링해보자! 라는 생각을 가졌었습니다.

--- 1000자 이내로 작성해야 글이 올ㅠ
        

퍼널의 수가 열 몇개로 나오지 않을까? 하는 예상과 다르게 총 433개의 단계가 나왔습니다.

click_login -> click_food_category -> click_restaurant -> click_food -> click_cart -> view_recommend_extra_food -> click_payment

와 같이 click_login ----- > click_payment까지의 여정의 가짓수가 너무 많아, 단계를 단순화할 필요성이 있어보였습니다. 그래서, 위의 결과와 같이 총 5개로 퍼널을 나누었습니다.