쿼리
WITH base AS (
SELECT
DISTINCT
user_id,
user_pseudo_id,
DATE(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_date,
DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_datetime,
event_name
FROM advanced.app_logs
WHERE
event_name = 'click_payment'
), first_week_and_diff AS (
SELECT
*,
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,
DATE_TRUNC(event_date, WEEK(MONDAY)) AS event_week
FROM base
)
), 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
)
SELECT
*,
ROUND(SAFE_DIVIDE(user_cnt, first_week_user_cnt), 2) AS retention_rate
FROM (
SELECT
*,
FIRST_VALUE(user_cnt) OVER(ORDER BY diff_of_week) AS first_week_user_cnt
FROM user_counts
)
쿼리 결과
| diff_of_week |
user_cnt |
first_week_user_cnt |
retention_rate |
| 0 |
11467 |
11467 |
1 |
| 1 |
156 |
11467 |
0.01 |
| 2 |
130 |
11467 |
0.01 |
| 3 |
113 |
11467 |
0.01 |
| 4 |
134 |
11467 |
0.01 |
| 5 |
115 |
11467 |
0.01 |
| 6 |
118 |
11467 |
0.01 |
| 7 |
106 |
11467 |
0.01 |
| 8 |
94 |
11467 |
0.01 |
| 9 |
91 |
11467 |
0.01 |
| 10 |
86 |
11467 |
0.01 |
| 11 |
67 |
11467 |
0.01 |
| 12 |
77 |
11467 |
0.01 |
| 13 |
65 |
11467 |
0.01 |
| 14 |
61 |
11467 |
0.01 |
| 15 |
55 |
11467 |
0 |
| 16 |
43 |
11467 |
0 |
| 17 |
33 |
11467 |
0 |
| 18 |
18 |
11467 |
0 |
| 19 |
16 |
11467 |
0 |
| 20 |
15 |
11467 |
0 |
| 21 |
8 |
11467 |
0 |
| 22 |
4 |
11467 |
0 |
| 23 |
1 |
11467 |
0 |
| 24 |
2 |
11467 |
0 |
시각화
해석
- 첫 결제 후 1주차부터 retention rate가 0.01로 급감하며, 15주차부터는 0에 수렴한다.
- 방문 기반 리텐션 대비 초기부터 리텐션이 매우 낮은 것을 볼 수 있다.
- 한 번 결제한 사용자가 반복 결제로 이어지지 않고 있음을 의미하며, 재구매율을 높이기 위한 전략이 필요하다.