인프런 커뮤니티 질문&답변
작성한 질문수
[빠짝스터디 4주차 과제] 강의 6-1 최종과제
해결된 질문
작성
·
125
·
수정됨
0
제품 자체는 앱의 기본 flow만 갖춰진 상태다.
앱 스플래시 - 로그인 - 홈 진입 후
검색 / 즐겨찾기 / 주문내역 / 카테고리 페이지를 볼 수 있고
restaurant -> food_detail -> cart -> payment -> order_success로 이어지는 funnel을 갖고있다.
WITH login_data AS (
SELECT DISTINCT user_pseudo_id, FORMAT_DATE('%Y-%m-01', event_date) AS login_month
FROM advanced.app_logs
WHERE event_date BETWEEN '2022-03-01' AND '2023-01-31'
)
SELECT login_month, COUNT(DISTINCT user_pseudo_id)
FROM login_data
GROUP BY login_month
ORDER BY login_month
MAU를 계산해보면,
7,500에서 시작하여 23,373까지 성장했다.
23년 1월 데이터는 절반 정도까지만 있으므로, 지속적인 성장세를 보여주고 있다고 판단할 수는 있겠다.
하지만 런칭 후 +2달 이후로는 계속 진전 없는 모습을 보여준다.
배달 앱이기 때문에, 평일-주말에는 다른 접속률을 보여줄 것이다. 그러므로 weekly retention 지표를 활용한다.
WITH base AS(
SELECT
DISTINCT
user_id,
event_name,
DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_datetime,
DATE(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_date,
user_pseudo_id
FROM advanced.app_logs
WHERE event_date BETWEEN '2022-08-01' AND '2022-11-03'
AND event_name = 'click_payment'
)
, dates AS(
SELECT DISTINCT user_pseudo_id,
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
FROM base
)
, first_week_and_diff AS (
SELECT *,
DATE_DIFF(event_week, first_week, week) AS diff_of_week
FROM dates
)
, user_counts AS (
SELECT diff_of_week,
COUNT(DISTINCT user_pseudo_id) AS user_cnt
FROM first_week_and_diff
GROUP BY diff_of_week
ORDER BY diff_of_week
)
, first_week_user_count AS (
SELECT diff_of_week,
user_cnt,
FIRST_VALUE(user_cnt) OVER(ORDER BY diff_of_week) AS first_week_user_cnt
FROM user_counts
)
SELECT *, SAFE_DIVIDE(user_cnt, first_week_user_cnt) AS retain_rate
FROM first_week_user_count
weekly retention으로 살펴보면, 첫 주에 1%의 고객이 남고 그 후로는 어느정도 플래튜가 형성되는 모습이다. 따라서 아직은 PMF를 찾지 못했다고 할 수 있다.
그렇다면 재접속을 하는 유저는 그렇지 않은 유저와 어떻게 다를까?
총 기간동안의 퍼널 이벤트 수는 아래와 같다.
WITH base AS (
SELECT event_date
, event_timestamp
, event_name
, user_id
, user_pseudo_id
, platform
, MAX(IF(event_param.key = 'firebase_screen', event_param.value.string_value, NULL)) AS firebase_screen
, MAX(IF(event_param.key = 'session_id', event_param.value.string_value, NULL)) AS session_id
FROM `advanced.app_logs`
CROSS JOIN UNNEST(event_params) AS event_param
WHERE event_date BETWEEN '2022-08-01' AND '2023-01-31'
GROUP BY ALL
)
, filter_event_and_concat_event_and_screen AS (
SELECT * EXCEPT(event_name, firebase_screen, event_timestamp)
, DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_datetime
, CONCAT(event_name, '-', firebase_screen) AS event_name_with_screen
FROM base
WHERE event_name IN ('screen_view', 'click_payment')
)
SELECT event_name_with_screen
, CASE
WHEN event_name_with_screen = 'screen_view-welcome' THEN 1
WHEN event_name_with_screen = 'screen_view-home' THEN 2
WHEN event_name_with_screen = 'screen_view-food_category' THEN 3
WHEN event_name_with_screen = 'screen_view-restaurant' THEN 4
WHEN event_name_with_screen = 'screen_view-cart' THEN 5
WHEN event_name_with_screen = 'click_payment-cart' THEN 6
ELSE NULL END AS STEP_NUMBER
, COUNT(DISTINCT user_pseudo_id) AS cnt
FROM filter_event_and_concat_event_and_screen
GROUP BY ALL
HAVING step_number IS NOT NULL
ORDER BY step_number
홈에서 food category를 선택하는 유저의 비율은 65%
카테고리에서 식당을 고르는 유저의 비율은 91%
식당에서 장바구니에 담는 유저의 비율은 58%
장바구니로부터 결제하는 유저의 비율은 67% 정도이다.
일단 탐색을 시작하면 카테고리, 식당에 대해 잘 찾아보는 편이고,
장바구니에 담아 결제까지 하는 것에 대해서는 event 수가 급감한다.
재접속을 하는 유저들의 퍼널을 페이지 기준으로 나눠보았다.
WITH base AS(
SELECT
DISTINCT
user_id,
event_name,
DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_datetime,
DATE(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_date,
user_pseudo_id
FROM advanced.app_logs
WHERE event_date BETWEEN '2022-08-01' AND '2023-01-31'
)
, dates AS(
SELECT DISTINCT user_pseudo_id,
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
FROM base
)
, first_week_and_diff AS (
SELECT *,
DATE_DIFF(event_week, first_week, week) AS diff_of_week
FROM dates
)
, retain_user AS (
SELECT DISTINCT user_pseudo_id
FROM first_week_and_diff
WHERE diff_of_week >= 1
)
, base2 AS (
SELECT event_date
, event_timestamp
, event_name
, user_id
, user_pseudo_id
, platform
, MAX(IF(event_param.key = 'firebase_screen', event_param.value.string_value, NULL)) AS firebase_screen
, MAX(IF(event_param.key = 'session_id', event_param.value.string_value, NULL)) AS session_id
FROM `advanced.app_logs`
CROSS JOIN UNNEST(event_params) AS event_param
WHERE event_date BETWEEN '2022-08-01' AND '2023-01-31'
AND user_pseudo_id IN (
SELECT user_pseudo_id
FROM retain_user
)
GROUP BY ALL
)
, filter_event_and_concat_event_and_screen AS (
SELECT * EXCEPT(event_name, firebase_screen, event_timestamp)
, DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_datetime
, CONCAT(event_name, '-', firebase_screen) AS event_name_with_screen
FROM base2
WHERE event_name IN ('screen_view', 'click_payment')
)
SELECT event_name_with_screen
, CASE
WHEN event_name_with_screen = 'screen_view-welcome' THEN 1
WHEN event_name_with_screen = 'screen_view-home' THEN 2
WHEN event_name_with_screen = 'screen_view-food_category' THEN 3
WHEN event_name_with_screen = 'screen_view-restaurant' THEN 4
WHEN event_name_with_screen = 'screen_view-cart' THEN 5
WHEN event_name_with_screen = 'click_payment-cart' THEN 6
ELSE NULL END AS STEP_NUMBER
, COUNT(DISTINCT user_pseudo_id) AS cnt
FROM filter_event_and_concat_event_and_screen
GROUP BY ALL
HAVING step_number IS NOT NULL
ORDER BY STEP_NUMBER
한번이라도 재접속한 사람들은 웰컴 -> 홈을 넘어가는 비율이 거의 100%에 가깝다. 아무래도 login이란 허들때문일 것이다.
그렇다면, login 허들을 좀 더 잘 넘기 위해 제품 온보딩을 잘 해줘야하지 않을까?
확실히 프로덕트에 대한 차별점 설명이 부족하므로, 이 부분을 강화하여 '어떤 core value를 가진 배달앱'인지를 어필할 필요가 있겠다.
그리고 홈에서 카테고리로 넘어가는 비율이 73%,
카테고리에서 식당 페이지로 92%,
식당 페이지에서 장바구니로는 55%
장바구니로부터 결제까지 이어지는 비율은 67%이다.
재접속한 유저들은 홈에서 food category를 선택하는 데에 좀 더 높은 전환율을 보였다.
그렇다면 이들이 다른 유저들 대비하여 추가적으로 느낀 이점은 무엇일지 고민할 필요가 있을 것이다.
재접속 기준만으로는 차이가 별로 없어서
click payment 경험이 있고 재접속을 한 유저들의 퍼널을 구해봤다.
비교적 매우 높은 전환율을 보여준다.
특히 카테고리를 굳이 선택하지 않고 바로 식당을 선택하는데,
카테고리가 비교적 메리트있게 설계되지 않아서 그런 것인지? 라는 생각이 든다.
이벤트 데이터가 더 있었다면, 식당으로 전환되는 페이지들의 비율을 살펴보면서 홈의 비중이 높지 않을까 하고 보겠지만, 지금은 그냥 그렇지 않을까 하고 추측해본다.
확실히 구매를 했고, 2번 이상 접속한 유저들의 퍼널 전환율은 매우 좋다.
해당 제품이 직접 보기에는 배달 주문에 필수적인 기능들만 있지만,
해당 1만 4천명 정도의 유저들이 느끼는 분명한 메리트가 있을 것이다.
그 메리트로부터 core value를 찾고, 고객들이 좋아하는 기능 중심으로 발전시켜 나가야할 것이다.
답변 1
0
안녕하세요, 인프런 AI 인턴입니다. Min Choi님께서 질문 주신 내용을 바탕으로 유사한 기존의 사례를 찾아보았습니다.
Min Choi님의 질문은 주로 데이터 분석 과정 중 사용자를 유지시키기 위한 전략적 통찰을 얻기 위해 다양한 쿼리와 분석을 시도하는 것에 대한 부분입니다. 특히 Weekly Retention을 분석하고, 퍼널 분석을 통해 사용자의 세션 활동을 이해하고자 하신 것으로 보입니다.
이에 대한 유사한 내용으로 참고하실 수 있는 몇 가지 자료를 안내드립니다:
리텐션 과제_연습문제 2번: 이 게시물에서는 데이터 분석과 관련된 다양한 쿼리를 제공하며, 사용자의 유형을 분류하고 각 유형별로 리텐션을 계산하는 방법을 다룹니다. 이 과정에서 얻어진 통찰은 사용자 세분화 및 타겟 마케팅 전략을 수립하는 데 도움을 줄 수 있습니다.
1-9. 퍼널 SQL 쿼리 작성하기: 퍼널 분석을 통해 사용자의 앱 내 여정을 분석하는 게시물입니다. 퍼널의 단계별 전환율을 계산하여 각 단계에서의 이탈률을 확인하고, 어떻게 개선할 수 있을지를 논의합니다.
퍼널별 유저들의 새로운 세션 시작 횟수의 총합 _ 윈도우 함수 마지막 문제 응용: 이 게시물은 유저들의 세션 시작 패턴에 대한 분석을 돕는 쿼리 작성 사례를 제공합니다. 데이터 분석 시 피봇 테이블 및 윈도우 함수의 활용 방법을 배울 수 있습니다.
위의 자료들은 Min Choi님의 분석 과정에서 영감을 얻어 추가적인 데이터를 분석하고 더 깊은 인사이트를 도출하는 데 도움을 줄 수 있습니다. 특히 퍼널 분석과 리텐션 분석을 통해 사용자의 행동 패턴을 명확히 파악하고, 그에 맞는 개선책을 모색하는 데 유용할 것입니다.
저는 질문자님의 이해를 돕고자 지식공유자님의 기존 답변을 참고하여 유사한 답변 링크를 추천해드리고 있습니다.
현재 베타 기능으로 답변이 만족스럽지 않을 수 있는 점 양해 부탁드립니다. 🙏
추가적으로 궁금한 점이 있으시면, 이어서 질문해 주세요. 곧 지식공유자께서 답변해 주실 것입니다.