리텐션 과제 연습문제 4번(core_event)

BigQuery(SQL) 활용편(퍼널 분석, 리텐션 분석)

해결된 질문

작성

119

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

문제: 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개로 퍼널을 나누었습니다.

sql Google-Analytics firebase google-sheets bigquery

답변 2

카일스쿨

지식공유자

minsubrother님 열심히 학습하고 계시네요.

쿼리를 보다가 아래 내용을 확인해보고 싶어 질문드려요(문제 정의하는 영역과 유사합니다)

퍼널 별 리텐션이라는 지표가 어떤 것을 의미하나요? 왜 이 지표를 뽑게 되셨을지 궁금해요
- 강의의 문제는 "click_payment"를 기반으로 Weekly 리텐션을 구하는 것인데 퍼널 별 리텐션으로 바뀌게 된 맥락이 궁금하네요(일단 전체적인 지표를 뽑는 것을 의도했어요)
- 현재 구하신 것은 퍼널 별 구매 전환율이라고 봐야하지 않을까 싶어요. 구매를 click_payment로 설정하고 본 것이지요
- 지금 리텐션과 퍼널 분석이 혼재된 느낌..? 입니다(밑에선 퍼널에 대해 가지수를 파악하신 것을 봤을 때 든 생각이에요)
리텐션 그래프를 그리고, 그 지표를 해석하는 것도 포함해주시면 좋을 것 같아요. 현업에서는 이 분석하는 것이 더 어렵고, 오래 걸립니다. 단순히 한두줄로 끝이 아닌, 이 그래프, 지표를 보고 해석하는 것까지 꼭 해보길 바라는 마음에 데이터를 만든 것이라 이 내용도 추가해보면 어떨까요?
분석 과정에 대해서는 노션에 작성하셔서 공유해주셔도 괜찮아요. 글자수 제한은 링크를 공유해주시면 그 부분을 가지고 말씀드릴게요!

minsubrother

질문자

강의의 문제는 "click_payment"를 기반으로 Weekly 리텐션을 구하는 것인데 퍼널 별 리텐션으로 바뀌게 된 맥락이 궁금하네요(일단 전체적인 지표를 뽑는 것을 의도했어요)

코어 이벤트가 'click_payment' 이고, 이는 사용자가 결제버튼을 누르는 시점을 의미합니다.

따라서, 제일 처음에 분석을 하고자 했던 부분은, 전체 고유 유저에 대해서 click_payment(결제)를 한 유저는 몇명일까?를 분석하고 싶었습니다.

그 이유는, weekly retention을 구하는 시점에서, 전체 유저는 결제 유저 + 비 결제 유저가 포함되어 있는 것인데, 만약에, 비 결제 유저가 많으면, 리텐션의 왜곡이 발생해서, 리텐션을 잘못 분석할 확률이 높다고 판단했습니다.

물론, 가정을 세운 것이기 때문에, 검증 절차를 거쳤어야 했는데, 제가 아래의 과정에 대한 데이터 검증은 거치지 않았습니다. 다시 한번 해봐야할 것 같네요.

가설1: 결제 유저 대비 비결제 유저는 재참여율가 낮다면, 리텐션이 실제보다 낮게 나올 소지가 있다.

가설2: 비결제 유저의 재참여율이 높다면, 리텐션이 실제보다 높게 나올 소지가 있다.

가설1과 가설2에 대한 검증은 한번 해봐야겠네요.

-- 결제O
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 = 'click_payment')
SELECT
    COUNT(DISTINCT user_pseudo_id) AS count_of_users
FROM user_events

SELECT
    COUNT(DISTINCT user_pseudo_id) AS original_users_cnt
FROM advanced.app_logs

전체 고유 유저는 52823명인 반면, 결제를 한 고유 유저는 11467로 1/5 수준입니다.

따라서, 실질적으로 결제를 한 유저를 중심으로 Retention을 볼 필요가 있겠다. 라는 생각이 들었습니다.

---

현재 구하신 것은 퍼널 별 구매 전환율이라고 봐야하지 않을까 싶어요. 구매를 click_payment로 설정하고 본 것이지요
지금 리텐션과 퍼널 분석이 혼재된 느낌..? 입니다(밑에선 퍼널에 대해 가지수를 파악하신 것을 봤을 때 든 생각이에요)

맞습니다.. 저도 이 부분을 놓고 생각해보니, 제가 한 분석은 구매 전환율에 더 표현이 가깝겠네요.

저는 core_event가 중요한 사용자 행동이라고 생각해서, 사용자가 core_event까지의 여정을 분석해볼 필요가 있지 않을까? 라는 생각에 분석을 시도했던것 같습니다.

카일스쿨

지식공유자

넵 제가 디스코드로 메시지 하나 보냈는데 확인해주시겠어요?

minsubrother

질문자

1000자 이내라서, 글이 일부를 지워야해서, 답변에 추가로 글을 올립니다.

(퍼널을 단순화하게 된 이유에 해당되는 쿼리)

WITH user_events AS (
	SELECT user_pseudo_id,
	       event_name,
         DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_timestamp,
         ROW_NUMBER() OVER (PARTITION BY user_pseudo_id, event_date ORDER BY event_timestamp) AS event_order
  FROM advanced.app_logs
), user_paths AS (
    SELECT
        user_pseudo_id,
        STRING_AGG(event_name, ' -> ' ORDER BY EVENT_ORDER) AS event_path
    FROM user_events
    GROUP BY user_pseudo_id
)
SELECT
    DISTINCT EVENT_PATH
FROM USER_PATHS
WHERE EVENT_PATH LIKE '%click_payment%'

WITH USER_EVENTS AS (
    SELECT
        user_pseudo_id,
        event_name,
        event_date,
        DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_timestamp,
        LAG(event_name) OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp) AS prev_event_name
    FROM advanced.app_logs
    WHERE event_name != 'screen_view'
),
FILTERED_EVENTS AS (
    SELECT
        user_pseudo_id,
        event_name,
        event_date,
        event_timestamp,
        ROW_NUMBER() OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp) AS event_order
    FROM USER_EVENTS
    WHERE event_name != prev_event_name OR prev_event_name IS NULL
),
USER_EVENTS_WITH_PAYMENT_ORDER AS (
    SELECT *,
        MIN(CASE WHEN event_name = 'click_payment' THEN event_order END)
            OVER (PARTITION BY user_pseudo_id) AS first_payment_order
    FROM FILTERED_EVENTS
)
, UNIQUE_EVENTS AS (
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY user_pseudo_id, event_name ORDER BY event_order) AS event_rank
    FROM USER_EVENTS_WITH_PAYMENT_ORDER
    WHERE event_order <= first_payment_order OR first_payment_order IS NULL
), temp AS (
SELECT
    *
FROM (SELECT user_pseudo_id,
             STRING_AGG(event_name, ' -> ' ORDER BY event_order) AS event_path
      FROM UNIQUE_EVENTS
      WHERE event_rank = 1
      GROUP BY user_pseudo_id)
WHERE event_path LIKE '%click_payment%'
ORDER BY user_pseudo_id)
SELECT
    DISTINCT event_path
    FROM temp

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

리텐션 과제 연습문제 4번(core_event)

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