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

민영 김님의 프로필 이미지
민영 김

작성한 질문수

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

3-6. 리텐션 SQL 쿼리 작성하기 : N Day Retention

[빠짝스터디] 3주차 과제 제품 현황 분석 : 리텐션 분석

작성

·

73

0

과제. 직접 retention 구해보기(주별 weekly)

WITH base AS (
	SELECT
		DISTINCT
			user_id,
			user_pseudo_id,
			event_name,
			DATE(DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul')) AS event_date,
			DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_datetime
	FROM advanced.app_logs
	WHERE
		event_date BETWEEN "2022-08-01" AND "2022-11-03"
), first_week_and_diff AS(
SELECT
	*,
	-- DATE_DIFF(event_date, first_date, DAY) AS diff_of_day,
	DATE_DIFF(event_week, first_week, WEEK) AS diff_of_week
FROM (
	SELECT
		DISTINCT
			user_pseudo_id,
			DATE_TRUNC(MIN(event_date) OVER(PARTITION BY user_pseudo_id), WEEK(MONDAY)) AS first_week,
			event_date,
			DATE_TRUNC(event_date, WEEK(MONDAY)) AS event_week
	FROM base
	)
), user_cnt_and_diff AS(
SELECT
	diff_of_week,
	COUNT(DISTINCT user_pseudo_id) AS user_cnt
FROM first_week_and_diff
GROUP BY ALL
ORDER BY diff_of_week
), retain_base AS (
SELECT
	*,
	FIRST_VALUE(user_cnt) OVER (ORDER BY diff_of_week) AS first_user_cnt
FROM user_cnt_and_diff
)

SELECT
	diff_of_week,
	ROUND(SAFE_DIVIDE(user_cnt, first_user_cnt), 2) AS retention
FROM retain_base

과제. 월별 리텐션 쿼리 작성해보기

WITH base AS (
	SELECT
		DISTINCT
			user_id,
			user_pseudo_id,
			event_name,
			DATE(DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul')) AS event_date,
			DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_datetime
	FROM advanced.app_logs
	WHERE
		event_date BETWEEN "2022-08-01" AND "2022-11-03"
), first_month_and_diff AS(
SELECT
	*,
	-- DATE_DIFF(event_date, first_date, DAY) AS diff_of_day,
	-- DATE_DIFF(event_week, first_week, WEEK) AS diff_of_week
	DATE_DIFF(event_month, first_month, MONTH) AS diff_of_month
FROM (
	SELECT
		DISTINCT
			user_pseudo_id,
			DATE_TRUNC(MIN(event_date) OVER(PARTITION BY user_pseudo_id), MONTH) AS first_month,
			event_date,
			DATE_TRUNC(event_date, MONTH) AS event_month
	FROM base
	)
), user_cnt_and_diff AS(
SELECT
	diff_of_month,
	COUNT(DISTINCT user_pseudo_id) AS user_cnt
FROM first_month_and_diff
GROUP BY ALL
ORDER BY diff_of_month
), retain_base AS (
SELECT
	*,
	FIRST_VALUE(user_cnt) OVER (ORDER BY diff_of_month) AS first_user_cnt
FROM user_cnt_and_diff
)

SELECT
	diff_of_month,
	user_cnt
	SAFE_DIVIDE(user_cnt, first_user_cnt) AS retention
FROM retain_base

 

리텐션 연습문제

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

     

# 답
-- Weekly Retention을 구하자! (Retention이란, 시간이 흐르면서 다시 제품을 사용하는지 측정하는 지표)
-- 첫 방문 후 매주 재방문 비율(weekly retention)을 계산하자!
WITH base AS (
	SELECT
		DISTINCT ######
			DATE(DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul')) AS event_date,
			DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_datetime,
			event_name,
			user_id, ######
			user_pseudo_id
	FROM advanced.app_logs
	WHERE event_date BETWEEN "2022-08-01" AND "2023-08-31"
), event_week_and_first_week AS (
SELECT
	DISTINCT
		DATE_TRUNC(event_date, WEEK(MONDAY)) AS event_week, 
		user_pseudo_id,
		DATE_TRUNC(MIN(event_date) OVER(PARTITION BY user_pseudo_id ORDER BY event_date), WEEK(MONDAY)) AS first_week
FROM base
), retention_base AS (
SELECT
	*, 
	FIRST_VALUE(user_cnt) OVER(ORDER BY day_of_week) AS total_user
FROM(
	SELECT
		DATE_DIFF(event_week, first_week, WEEK) AS day_of_week,
		COUNT(DISTINCT user_pseudo_id) AS user_cnt
	FROM event_week_and_first_week
	GROUP BY ALL
)
-- ORDER BY day_of_week
)
SELECT
	day_of_week,
	SAFE_DIVIDE(user_cnt, total_user) AS retention
FROM retention_base

  1. Retain User를 New + Current + Resurrected + Dormant User로 나누는 쿼리를 작성해보세요

# 답
-- 신규 유저: 첫 활동일 기준 7일 이내
-- 기존 유저: 첫 활동일 기준 7일 이후, 마지막 활동으로부터 90일 이내
-- 휴면 유저: 마지막 활동일 이후 90일 이상 경과
-- 복귀 유저: 90일 이상 비활성화 이후 다시 돌아온 유저

WITH base AS (
	SELECT
		DISTINCT
			DATE(DATETIME(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-08-31"
),user_first_last AS (
	SELECT
		user_pseudo_id,
		MIN(event_date) AS first_date, #OVER(PARTITION BY user_pseudo_id)를 넣으면 윈도우함수사용과 GROUPBY졸 사용 비교했을 때처럼 중복행이 생긴다
		MAX(event_date) AS last_date
	FROM base
	GROUP BY user_pseudo_id
), last_event_date AS (
	-- 원래라면 CURRENT_DATE()로 해야하지만, 앱로그 데이터의 마지막 날로 설정.
	SELECT 
		MAX(event_date) AS current_date
	FROM base
), user_activity AS (
	-- 90일 이상 제품을 사용하지 않았다면 1, 아니면 0
	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) > 90 
			THEN 1
			ELSE 0
		END AS dormant_history
	FROM base
)
-- user_types AS (
SELECT
	DISTINCT
		u.user_pseudo_id,
		u.first_date,
		u.last_date,
		led.current_date,
		us.dormant_history,
		CASE 
			WHEN DATE_DIFF(led.current_date, u.last_date, DAY) > 90 THEN 'dormant_user'
			WHEN DATE_DIFF(u.last_date, u.first_date, DAY) <= 7 THEN 'new_user'
			WHEN dormant_history = 1 THEN 'resurrected_user'
			ELSE 'current_user'
		END AS user_type
	FROM user_first_last AS u
	CROSS JOIN last_event_date AS led
	LEFT JOIN user_activity AS us
	ON u.user_pseudo_id = us.user_pseudo_id

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

WITH base AS (
    SELECT
        DISTINCT
            DATE(DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul')) AS event_date,
            user_pseudo_id
    FROM advanced.app_logs
), user_first_last AS (
    SELECT
        user_pseudo_id,
        MIN(event_date) AS first_date,
        MAX(event_date) AS last_date
    FROM base
    GROUP BY user_pseudo_id
), last_event_date AS (
    SELECT 
        MAX(event_date) AS current_date
    FROM base
), 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) > 90 
            THEN 1 ELSE 0
        END AS dormant_history
    FROM base
), user_types AS (
    SELECT
        u.user_pseudo_id,
        u.first_date,
        u.last_date,
        led.current_date,
        MAX(us.dormant_history) AS dormant_history,
        CASE 
            WHEN DATE_DIFF(led.current_date, u.last_date, DAY) > 90 THEN 'dormant_user'
            WHEN DATE_DIFF(u.last_date, u.first_date, DAY) <= 7 THEN 'new_user'
            WHEN MAX(us.dormant_history) = 1 THEN 'resurrected_user'
            ELSE 'current_user'
        END AS user_type
    FROM user_first_last AS u
    CROSS JOIN last_event_date AS led
    LEFT JOIN user_activity AS us ON u.user_pseudo_id = us.user_pseudo_id
    GROUP BY u.user_pseudo_id, u.first_date, u.last_date, led.current_date
), first_week_and_diff AS (
    SELECT
        ut.user_type,
        fw.user_pseudo_id,
        fw.event_date,
        DATE_DIFF(DATE_TRUNC(fw.event_date, WEEK(MONDAY)), DATE_TRUNC(ut.first_date, WEEK(MONDAY)), WEEK) AS diff_of_week
    FROM base AS fw
    JOIN user_types AS ut ON fw.user_pseudo_id = ut.user_pseudo_id
), user_cnt_by_type_and_week AS (
    SELECT
        user_type,
        diff_of_week,
        COUNT(DISTINCT user_pseudo_id) AS user_cnt
    FROM first_week_and_diff
    GROUP BY user_type, diff_of_week
), retention_base AS (
    SELECT
        user_type,
        diff_of_week,
        user_cnt,
        FIRST_VALUE(user_cnt) OVER (PARTITION BY user_type ORDER BY diff_of_week) AS first_user_cnt
    FROM user_cnt_by_type_and_week
)

SELECT
    user_type,
    diff_of_week,
    ROUND(SAFE_DIVIDE(user_cnt, first_user_cnt), 2) AS retention_rate
FROM retention_base
ORDER BY user_type, diff_of_week
스크린샷 2024-11-10 오전 3.29.35.png스크린샷 2024-11-10 오전 3.31.10.png

 

스크린샷 2024-11-10 오전 3.30.45.png

current user의 1주차 리텐션은 0.11부터 시작해서 5~6주차에는 0.15까지 오릅니다.

new user의 경우 1주차까지 밖에 리텐션을 구하지 못하고, 0.06입니다.

resurrected user의 경우 1주차 리텐션은 0.04부터 시작해서 7주차에는 0으로 빠르게 감소합니다.

dormant user의 경우 휴면고객의 리텐션을 구하는 것이 의미가 있는지 잘 모르겠지만, 1주차 리텐션은 0.05부터 시작해서 11주차까지 0으로 감소합니다.

리텐션이 그나마 높은 유저는 current user이고, 리텐션이 주마다 서서히 증가하거나 감소하는데 2주차에 오른 것으로 보아 기능 배포나 이벤트가 있지 않았을까 예상해봅니다.

  1. Core Event를 “click_payment”라고 설정하고 Weekly Retention을 구해주세요

WITH base AS (
	SELECT
		DISTINCT
			DATE(DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul')) AS event_date,
			DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_datetime,
			event_name,
			user_id,
			user_pseudo_id
	FROM advanced.app_logs
	WHERE 
		event_name = "click_payment"
), event_week_and_first_week AS (
	SELECT
		DISTINCT
			DATE_TRUNC(event_date, WEEK(MONDAY)) AS event_week, 
			user_pseudo_id,
			DATE_TRUNC(MIN(event_date) OVER(PARTITION BY user_pseudo_id ORDER BY event_date), WEEK(MONDAY)) AS first_week
	FROM base
), retention_base AS (
	SELECT
		*, 
		FIRST_VALUE(user_cnt) OVER(ORDER BY day_of_week) AS total_user
	FROM (
		SELECT
			DATE_DIFF(event_week, first_week, WEEK) AS day_of_week,
			COUNT(DISTINCT user_pseudo_id) AS user_cnt
		FROM event_week_and_first_week
		GROUP BY day_of_week
	)
)
SELECT
	day_of_week,
	ROUND(SAFE_DIVIDE(user_cnt, total_user), 2) AS retention
FROM retention_base
ORDER BY day_of_week

app_logs 테이블에서 event_name이 click_payment인 것만 필터 걸어서 리텐션 구하기를 진행했는데 이렇게 푸는 것이 맞을까요..?

sqlGoogle-Analyticsfirebasegoogle-sheetsbigquery

답변 1

0

인프런 AI 인턴님의 프로필 이미지
인프런 AI 인턴

안녕하세요, 인프런 AI 인턴입니다. 민영 김님의 리텐션 분석 과제에 대해 몇 가지 요점과 이전 해결 방법을 공유하려 합니다.

리텐션 쿼리를 작성하는 과정에서 중요한 것은 유저들이 특정 이벤트, 이 경우 ‘click_payment’, 이후 주기적으로 다시 돌아오는 비율을 계산하는 것입니다. 주별 리텐션을 분석하려면 데이터의 시계열적 특성과 유저 기준으로 데이터를 추적하는 것이 필요합니다.

기존의 유사한 질의에 대한 답변으로 리텐션 과제_연습문제 2번에서, 리텐션 계산시 사용되는 SQL 쿼리에 대한 예시와 데이터 집계 방법이 논의되었습니다. 이 답변에서는 쿼리가 어떻게 작성되어야 하고, 어떤 식으로 데이터를 그룹화하여 리텐션 비율을 계산할 수 있는지에 대한 정보가 포함되어 있습니다.

민영 김님이 사용하신 방식 또한 유사하며, ‘click_payment’ 이벤트를 기준으로 주차별 리텐션을 구했는데, 주의할 점은 데이터의 중복을 방지하기 위해 DISTINCT 절을 사용하는 부분입니다. 데이터의 유효성을 확보하고, 중복된 상호작용을 제거하는 것이 중요합니다.

주어진 SQL 쿼리가 올바르며, 민영 김님의 접근 방법도 적합합니다. 다만, 리텐션 그래프 및 결과 해석에서 발생하는 변화가 왜 발생했는지에 대한 요인을 분석하거나, 특정 시점에 이벤트가 있었는지를 살펴보는 것도 리텐션 분석에서 중요한 부분입니다.

저는 질문자님의 이해를 돕고자 지식공유자님의 기존 답변을 참고하여 유사한 답변 링크를 추천해드리고 있습니다.
현재 베타 기능으로 답변이 만족스럽지 않을 수 있는 점 양해 부탁드립니다. 🙏
추가적으로 궁금한 점이 있으시면, 이어서 질문해 주세요. 곧 지식공유자께서 답변해 주실 것입니다.

민영 김님의 프로필 이미지
민영 김

작성한 질문수

전체 Q&A
질문하기