SELECT DISTINCT LEFT(FORMAT_DATE('%Y%m%d', event_date), 6) AS login_month, COUNT(DISTINCT user_pseudo_id) as count FROM advanced.app_logs GROUP BY LEFT(FORMAT_DATE('%Y%m%d', event_date), 6) 2022년 8월부터 2023년 1월까지의 MAU를 살펴보니(MAU기준은 로그인 기록), 첫 달인 2022년 8월에는 미진했지만 그 후로 점차 증가하는 것을 볼 수 있습니다. 특히 9월은 8월 대비 2배에 가까운 성장률을 보였고 10월에는 1.5배 이상의 성장률을 보이면서 점차 사용자가 확대되고 있는 것을 알 수 있습니다. WITH base AS ( SELECT DISTINCT user_pseudo_id, DATE_TRUNC(DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul'), WEEK(MONDAY)) AS event_week, DATE_TRUNC(MIN(DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul')) OVER(PARTITION BY user_pseudo_id), WEEK(MONDAY)) AS first_week FROM `advanced.app_logs` ), first_week_diff AS ( SELECT user_pseudo_id, DATE_DIFF(event_week, first_week, WEEK) AS diff_of_week FROM base ), user_counts AS ( SELECT diff_of_week, COUNT(DISTINCT user_pseudo_id) AS user_count FROM first_week_diff GROUP BY diff_of_week ) SELECT diff_of_week, user_count, ROUND(SAFE_DIVIDE(user_count, FIRST_VALUE(user_count) OVER (ORDER BY diff_of_week ASC)), 2) AS retention_rate FROM user_counts ORDER BY diff_of_week; 주 리텐션율을 살펴보면, 첫 주에는 당연히 1%이지만 그 후로 확연히 주마다 접속하는 사용자가 줄고 있습니다. Foodie Express는 아직 Product Market Fit을 발견하지 못한 것으로 보입니다. 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-12-31' -- 데이터 길게보기 ), first_week_and_diff AS ( SELECT *, DATE_DIFF(event_week,first_week, WEEK) AS weeks_after_first_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 ) ), active_users AS( SELECT first_week, weeks_after_first_week, COUNT(DISTINCT user_pseudo_id) AS avtive_users FROM first_week_and_diff GROUP BY first_week, weeks_after_first_week ) SELECT first_week, weeks_after_first_week, avtive_users, FIRST_VALUE(avtive_users) OVER(PARTITION BY first_week ORDER BY weeks_after_first_week) AS cohort_users FROM active_users AS a ORDER BY first_week, weeks_after_first_week; 첫 주에 1,211의 사용자가 있었으나 1주차에 32명으로 급감했습니다 (약 2.6% 유지) 2-8주차에는 비교적으로 안정적이었고, 9주차에서 20주에는 사용자가 좀 더 증가하였습니다. 특히 14주차에 142명으로 가장 많은 사용자가 있었습니다. 이를 살펴보면, 초기에 감소 후에 오히려 사용자 수가 증가하는 패턴을 보이고 오히려 앱을 사용하면 사용할 수록 더 활발하게 앱을 사용하는 것을 알 수 있습니다. 따라서 어떻게든 사용자들을 앱에 유입시켜서 사용하게 만들어야합니다. 특히 주말, 공휴일 또는 음식 주문이 많은 근무 시간 이후에 비활성 사용자에게 정기적인 푸시 알림이나 메시지를 보내면 좋을 것 같습니다. (음식을 많이 시켜먹는 주말이나, 공휴일 또는 퇴근시간때쯤 맞춰서 푸시알림을 준다) 공휴일에 좀 더 사용자가 증가하는 경향을 보이는 것 같은데, 이 때는 사용자 유입이 많으므로 소액 할인 쿠폰을 제공하는 것이 효과적일 것이라고 판단됩니다. 무엇보다도 정기적인 알림을 통해 앱 사용 습관을 들이는 것이 중요해 보입니다!
1번 WITH -- 1단계: 필수 데이터만 추출하기 base_events AS ( SELECT user_pseudo_id, DATE(TIMESTAMP_MICROS(event_timestamp), "Asia/Seoul") AS event_date FROM advanced.app_logs ), -- 2단계: 사용자별 첫 방문 주차와 각 활동 주차 구하기 user_weeks 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 visit_week FROM base_events ), -- 3단계: 첫 방문 이후 몇 주차인지 계산하기 week_numbers AS ( SELECT user_pseudo_id, -- 첫 방문 이후 경과된 주차 계산 DATE_DIFF(visit_week, first_week, WEEK) AS week_number FROM user_weeks -- 최대 12주까지만 분석 WHERE DATE_DIFF(visit_week, first_week, WEEK) <= 12 ), -- 4단계: 주차별 총 사용자 수 계산하기 weekly_users AS ( SELECT week_number, COUNT(DISTINCT user_pseudo_id) AS user_count FROM week_numbers GROUP BY week_number ) -- 5단계: 최종 리텐션 계산하기 SELECT week_number, user_count as active_users, FIRST_VALUE(user_count) OVER(ORDER BY week_number) as first_week_users, ROUND(100.0 * user_count / FIRST_VALUE(user_count) OVER(ORDER BY week_number), 2) as retention_rate FROM weekly_users ORDER BY week_number; 2번 WITH -- 1단계: 사용자별 주차 데이터 준비 user_weeks AS ( SELECT DISTINCT user_pseudo_id, -- 첫 방문 주차 DATE_TRUNC(MIN(DATE(TIMESTAMP_MICROS(event_timestamp), "Asia/Seoul")) OVER(PARTITION BY user_pseudo_id), WEEK(MONDAY)) AS first_week, -- 활동 주차 DATE_TRUNC(DATE(TIMESTAMP_MICROS(event_timestamp), "Asia/Seoul"), WEEK(MONDAY)) AS event_week FROM advanced.app_logs ), -- 2단계: 사용자 상태 확인 user_status AS ( SELECT user_pseudo_id, event_week, first_week, -- 이전 방문 주차 LAG(event_week) OVER(PARTITION BY user_pseudo_id ORDER BY event_week) AS prev_week FROM user_weeks ), -- 3단계: 상태 분류 weekly_status AS ( SELECT event_week, user_pseudo_id, CASE WHEN event_week = first_week THEN 'New' WHEN DATE_DIFF(event_week, prev_week, WEEK) = 1 THEN 'Current' WHEN DATE_DIFF(event_week, prev_week, WEEK) > 1 THEN 'Resurrected' END as user_type FROM user_status ), -- 4단계: 각 주차별 전체 유저 수 total_users AS ( SELECT fwd.event_week, COUNT(DISTINCT user_pseudo_id) AS total_user_count FROM user_status CROSS JOIN (SELECT DISTINCT event_week FROM user_weeks) AS fwd WHERE first_week <= fwd.event_week GROUP BY event_week ), -- 5단계: 주차별 활성 유저 수 계산 active_users AS ( SELECT event_week, COUNTIF(user_type = 'New') AS new_users, COUNTIF(user_type = 'Current') AS current_users, COUNTIF(user_type = 'Resurrected') AS resurrected_users, COUNT(DISTINCT user_pseudo_id) AS retain_users FROM weekly_status GROUP BY event_week ) -- 6단계: 최종 결과 SELECT FORMAT_DATE('%Y-%m-%d', a.event_week) as week_start, new_users, current_users, resurrected_users, (t.total_user_count - a.retain_users) as dormant_users, ROUND(100.0 * current_users / NULLIF(LAG(new_users) OVER(ORDER BY a.event_week), 0), 1) as retention_rate, retain_users as active_users, t.total_user_count as total_users FROM active_users a JOIN total_users t ON a.event_week = t.event_week ORDER BY a.event_week; 3번 WITH -- 1단계: 기본 데이터 준비 user_weeks AS ( SELECT DISTINCT user_pseudo_id, DATE_TRUNC(MIN(DATE(TIMESTAMP_MICROS(event_timestamp), "Asia/Seoul")) OVER(PARTITION BY user_pseudo_id), WEEK(MONDAY)) AS first_week, DATE_TRUNC(DATE(TIMESTAMP_MICROS(event_timestamp), "Asia/Seoul"), WEEK(MONDAY)) AS event_week FROM advanced.app_logs ), -- 2단계: 사용자별 방문 주차 수 계산 user_visit_frequency AS ( SELECT user_pseudo_id, COUNT(DISTINCT event_week) as total_visit_weeks, DATE_DIFF(MAX(event_week), MIN(event_week), WEEK) + 1 as weeks_since_first, MIN(event_week) as first_visit_week FROM user_weeks GROUP BY user_pseudo_id ), -- 3단계: 사용자별 리텐션 점수 계산 user_retention_score AS ( SELECT user_pseudo_id, total_visit_weeks, weeks_since_first, ROUND(100.0 * total_visit_weeks / weeks_since_first, 2) as visit_rate, FORMAT_DATE('%Y-%m', first_visit_week) as cohort_month FROM user_visit_frequency WHERE weeks_since_first >= 4 -- 최소 4주 이상 경과된 사용자만 ), -- 4단계: 사용자 행동 데이터 분석 user_behavior AS ( SELECT r.user_pseudo_id, r.visit_rate, r.cohort_month, COUNT(DISTINCT DATE(TIMESTAMP_MICROS(l.event_timestamp), "Asia/Seoul")) as active_days, COUNT(DISTINCT l.event_timestamp) as total_events, COUNT(DISTINCT l.event_name) as unique_event_types FROM user_retention_score r JOIN advanced.app_logs l ON r.user_pseudo_id = l.user_pseudo_id GROUP BY r.user_pseudo_id, r.visit_rate, r.cohort_month ), -- 5단계: 리텐션 세그먼트별 행동 패턴 분석 retention_segments AS ( SELECT cohort_month, CASE WHEN visit_rate >= 75 THEN 'Very High (75%+)' WHEN visit_rate >= 50 THEN 'High (50-74%)' WHEN visit_rate >= 25 THEN 'Medium (25-49%)' ELSE 'Low (<25%)' END as retention_segment, COUNT(DISTINCT user_pseudo_id) as user_count, ROUND(AVG(visit_rate), 2) as avg_retention_rate, ROUND(AVG(active_days), 1) as avg_active_days, ROUND(AVG(total_events), 1) as avg_total_events, ROUND(AVG(unique_event_types), 1) as avg_unique_events FROM user_behavior GROUP BY cohort_month, CASE WHEN visit_rate >= 75 THEN 'Very High (75%+)' WHEN visit_rate >= 50 THEN 'High (50-74%)' WHEN visit_rate >= 25 THEN 'Medium (25-49%)' ELSE 'Low (<25%)' END ) -- 최종 결과 SELECT cohort_month, retention_segment, user_count, avg_retention_rate, avg_active_days, avg_total_events, avg_unique_events, ROUND(100.0 * user_count / SUM(user_count) OVER (PARTITION BY cohort_month), 2) as segment_percentage FROM retention_segments ORDER BY cohort_month, CASE retention_segment WHEN 'Very High (75%+)' THEN 1 WHEN 'High (50-74%)' THEN 2 WHEN 'Medium (25-49%)' THEN 3 WHEN 'Low (<25%)' THEN 4 END; 4번 WITH -- 1단계: 결제 이벤트 기본 데이터 payment_base AS ( SELECT DISTINCT user_pseudo_id, DATE(TIMESTAMP_MICROS(event_timestamp), "Asia/Seoul") AS event_date FROM advanced.app_logs WHERE event_name = "click_payment" ), -- 2단계: 사용자별 첫 결제일과 주차 계산 user_weeks AS ( SELECT 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 payment_base ), -- 3단계: 주차별 상태 계산 week_status AS ( SELECT user_pseudo_id, event_week, first_week, -- 첫 주차와의 차이 DATE_DIFF(event_week, first_week, WEEK) AS week_number FROM user_weeks WHERE DATE_DIFF(event_week, first_week, WEEK) <= 12 -- 최대 12주까지만 분석 ), -- 4단계: 주차별 활성 사용자 수 계산 weekly_users AS ( SELECT week_number, COUNT(DISTINCT user_pseudo_id) AS active_users FROM week_status GROUP BY week_number ), -- 5단계: 첫 주 사용자 수 (코호트 크기) 구하기 first_week_users AS ( SELECT COUNT(DISTINCT user_pseudo_id) AS cohort_size FROM week_status WHERE week_number = 0 ) -- 최종 결과 SELECT week_number as week, active_users, cohort_size, ROUND(100.0 * active_users / cohort_size, 2) as retention_rate FROM weekly_users CROSS JOIN first_week_users ORDER BY week_number; 서비스 성장세 총 사용자: 52,823명까지 증가 8월부터 12월까지 꾸준한 성장 10월에 가장 높은 신규 유저 유입 (4,048명) 리텐션 개선 전체 리텐션: 8월 2.6% → 1월 48%로 큰 폭 개선 12월부터 40% 이상의 안정적인 리텐션 유지 재방문 사용자 비중이 지속적으로 증가 개선 필요점 결제 리텐션이 매우 낮음 (1% 수준) 신규 사용자 유입이 감소 추세 휴면 사용자가 지속적으로 증가
활성사용자 주간 트렌드로 확인하면 최근 두 달은 감소세이나 그 이후에도 감소할지는 아직 알 수 없습니다. 주중 VS 주말 큰 차이는 아니지만, 주말의 활성사용자가 더 많습니다. 주중에만 참여가능한 프로모션을 실행 해볼 수 있다고 판단됩니다. 신규사용자 2022년 40주차에 신규유저 유치용 프로모션을 진행한 것으로 보이며, 그 시기를 기점으로 신규유저 유입이 지속적으로 감소세에 있습니다. 22년 40주차에 잘된 원인을 분석하여 그에 맞는 액션아이템을 도출 해야 합니다. 40주차에 진행한 프로모션과 유사한 프로모션을 진행 하거나, 40주차에 특정 페이드마케팅(광고)를 진행했다면 비슷한 수준의 예산과 매체로 캠페인을 다시 진행 할 필요가 있습니다. 리텐션, 코호트 월간 리텐션 코호트 월간(30일) 기준으로 리텐션과 코호트를 보았는데, 더 긴 기간의 데이터를 들여다봐야 한다고 생각합니다. 데이터를 잘못 만졌을 가능성이 있다고 생각하는데, +2개월차나 +3개월차에 리텐션이 오히려 상승하는 경우 가 있었습니다. 데이터 테이블을 첨부해봅니다. 만약 데이터 추출결과가 문제가 없다면, 월간기준으로 리텐션이 다시 높아지는 원인을 찾고 그에 맞게 액션아이템을 도출해야합니다. 2022년 10월~12월의 리텐션은 30%대 를 유지하고 있는것으로 보아, 리텐션 유지를 위한 프로모션을 해당 기간에 진행했을 가능성이 있습니다. 12월 신규유입 유저가 1월에 잔존율이 20%초반대로 급감 한 것으로 보아 프로모션이 없었거나, 명절연휴의 영향이 있었을 가능성이 있습니다. 23년 1월의 명절 연휴가 평일 기준 이틀 뿐이어서 이건 가능성이 없는 것 같고, 리텐션용 프로모션이 23년 1월부터 진행되지 않았을 가능성이 높다고 봅니다. 리텐션용 프로모션을 부활 시켜야 합니다. 이벤트, 전환율 분석 검색 후 결제전환율과 추천메뉴 클릭 후 결제전환율의 트렌드 입니다. 2022년 35주차 에 두 전환율이 모두 높았는데, 추천메뉴 클릭 후 결제전환율이 특히 높았던 것으로 보아 관련 이벤트가 있던 것으로 생각 됩니다. 아마 아주 비싼 고급 음식을 매우 합리적인 가격에 추천하는 이벤트를 진행하지 않았나 싶습니다. 다만 모든 유저에게 노출되는 추천메뉴는 아니었을 것 같습니다. 이유는 후술하겠습니다. 추천 메뉴 -> 결제전환율은 평균 30.99%로 매우 높은 반면, 검색 -> 결제전환율은 평균 5.68%로 현저히 낮습니다. 검색결과의 품질이나 UX가 그렇게 만족스럽지 않을 가능성이 있으므로 개선이 필요 합니다. 추천메뉴 관련 경험을 계속 유지하거나 더 좋게 만드는 액션아이템을 개발 할 필요가 있습니다. 결제건수 트렌드 입니다. 결제건수는 2022년 40주차에 단연 가장 높습니다. 위에 언급했던 신규유저수가 튀었던 주차와 동일 합니다. 앞서 언급한 35주차의 결제건수는 40주차에 비하면 그렇게 높지 않습니다. 그렇기 때문에 35주차는 모든 유저 대상이 아니었다라고 판단하게 되었습니다. 다만 이것이 단순히 기존 전체 유저 대상이었는지(그럴 가능성이 높다고 보지만), 기존 유저 중 특정 유저만 대상인지는 조금 더 살펴봐야 합니다. 40주차 이후에 결제건수가 36주차 이전보다 확연히 늘어난 상태로 유지가 되고 있는 것으로 보아 신규유저의 대량 유입이 결제건수 상승에 기여했다고 판단됩니다. 장바구니 -> 결제 전환율 트렌드 입니다. 추천메뉴->결제전환율이 튀었던 35주차에 장바구니-> 결제전환율은 거의 100% 입니다. 무조건 결제를 한 수준입니다. 신규유저가 대폭 유입된 40주차의 결제전환율도 매우 높은 편입니다. 트렌드 그래프로만 보면 이벤트가 없을때의 장바구니-> 결제전환율이 낮을 것으로 보일 수 있으나 거의 항상 80% 이상의 전환율을 유지 하고 있습니다. 가설(액션아이템) 주중 프로모션을 진행하면 일간 활성사용자수가 주말만큼 상승할 것이다. 검색관련 UX를 개선하면 검색 후 결제전환율이 5%보다 상승할 것이다. 기존유저 대상으로 정기적인 혜택을 부여하면 리텐션이 30%대로 높게 유지될 것이다. 신규유저를 적극적으로 유치하는 프로모션이나 광고캠페인을 적극적으로 진행하면 신규유저 유입수가 크게 증가할 것이다. 결제건수가 이전보다 높은 수준으로 상승하여 유지될 것이다.
2 issues were found when checking AAR metadata: 1. Dependency 'androidx.core:core-ktx:1.15.0' requires libraries and applications that depend on it to compile against version 35 or later of the Android APIs. :app is currently compiled against android-34. Also, the maximum recommended compile SDK version for Android Gradle plugin 8.3.0 is 34. Recommended action: Update this project's version of the Android Gradle plugin to one that supports 35, then update this project to use compileSdk of at least 35. Note that updating a library or application's compileSdk (which allows newer APIs to be used) can be done separately from updating targetSdk (which opts the app in to new runtime behavior) and minSdk (which determines which devices the app can be installed on). 2. Dependency 'androidx.core:core:1.15.0' requires libraries and applications that depend on it to compile against version 35 or later of the Android APIs. :app is currently compiled against android-34. Also, the maximum recommended compile SDK version for Android Gradle plugin 8.3.0 is 34. Recommended action: Update this project's version of the Android Gradle plugin to one that supports 35, then update this project to use compileSdk of at least 35. Note that updating a library or application's compileSdk (which allows newer APIs to be used) can be done separately from updating targetSdk (which opts the app in to new runtime behavior) and minSdk (which determines which devices the app can be installed on). 이렇게 떠요 ㅠㅠ
선생님, 수업 잘 들었습니다. 퇴근 후에 아무래도 시간이 많지는 않아서 조금 더 간단하게 풀이할 수 있게 준비를 하고 싶은데 피처 엔지니어링을 할 때 항상 아래 두 가지 방법을 동시에 사용해서 수치형과 범주형 컬럼들을 동시에 사용해도 문제가 없을까요? 수치형 : 스케일링 범주형 : 인코딩
용량(x)이 13이고 병에 들어있는 용량(arr[e])이 13일 때, 즉 arr[e] == x: 일 때 cnt += 1 하고 e 부분만 빼기 1을 해주는 부분에서 이해가 되지 않았습니다. 문제에서 "두 개를 반납해야 새로운 용량을 준다고 했는데" 선생님께서는 최대용량(13)일 때 하나만 반납했기 때문에 이해가 잘안되네요.. 이 부분 한번 설명 해주실 수 있으실까요?
1번 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 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 diff_of_week, user_cnt, FIRST_VALUE(user_cnt) OVER(ORDER BY diff_of_week ASC) AS first_week_user_cnt FROM user_counts ) 2번 WITH base AS ( SELECT DISTINCT user_id, user_pseudo_id, event_name, DATETIME(TIMESTAMP_MICROS(event_timestamp),'Asia/Seoul') AS event_datetime, DATE(DATETIME(TIMESTAMP_MICROS(event_timestamp),'Asia/Seoul')) AS event_date, FROM advanced.app_logs) , weekly_user_active AS ( SELECT user_pseudo_id, DATE_TRUNC(event_date, WEEK) AS event_week, MIN(DATE_TRUNC(event_date, WEEK)) OVER (PARTITION BY user_pseudo_id) AS first_active_week, LAG(DATE_TRUNC(event_date, WEEK)) OVER (PARTITION BY user_pseudo_id ORDER BY DATE_TRUNC(event_date, WEEK)) AS pre_active_week FROM base ) , user_group AS ( SELECT user_pseudo_id, event_week, DATE_DIFF(event_week, pre_active_week, WEEK(MONDAY)) AS diff_prior_week, DATE_DIFF(event_week, first_active_week, WEEK(MONDAY)) AS diff_first_week, CASE WHEN event_week = first_active_week THEN 'NEW' WHEN DATE_DIFF(event_week, pre_active_week, WEEK) = 1 THEN 'Current' WHEN DATE_DIFF(event_week, pre_active_week, WEEK) > 1 THEN 'Resurrected' ELSE 'Dormant ' END AS user_seg FROM weekly_user_active ) SELECT event_week, user_seg, COUNT(DISTINCT user_pseudo_id) AS user_cnt, FROM user_group GROUP BY ALL ORDER BY 1 ; 4번 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" AND 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 diff_of_week AS week, user_cnt, FIRST_VALUE(user_cnt) OVER(ORDER BY diff_of_week ASC) AS first_week_user_cnt, ROUND(SAFE_DIVIDE(user_cnt, FIRST_VALUE(user_cnt) OVER(ORDER BY diff_of_week ASC)) * 100, 2) AS retention_rate FROM user_counts ORDER BY diff_of_week
개요 Domain : 배달음식 어플리케이션 Data set : app_logs 테이블 데이터기간: 2022.08~2022.12 목적: Foodie 현황 데이터분석 데이터분석 1. 월별 활성 사용자 수 목적: 월별 활성 고객 파악을 통해 서비스의 전반적인 흐름 파악 1-1 지표정의 MAU (Monthly Active Users) 한 달 동안 앱을 사용한 순수 고유 사용자 수 중복을 제거한(COUNT DISTINCT) user_id 기준으로 집계 서비스의 전반적인 성장과 규모를 보여주는 핵심 지표 Cart Users 한 달 동안 장바구니에 상품을 담은 순수 고유 사용자 수 event_name = 'click_cart'인 이벤트의 고유 사용자 수 실제 구매 의도를 가진 잠재 고객의 규모를 파악할 수 있는 지표 Payment Users 한 달 동안 실제 결제를 진행한 순수 고유 사용자 수 event_name = 'click_payment'인 이벤트의 고유 사용자 수 실제 매출 발생에 기여한 고객의 규모를 나타내는 지표 Cart to Payment Rate 장바구니 사용자 중 실제 결제로 이어진 비율 (%) (Payment Users / Cart Users) * 100으로 계산 주요 의미: 구매 전환율을 나타내는 핵심 지표 장바구니 단계에서의 이탈률 파악 가능 높을수록 구매 전환이 잘 이루어짐을 의미 쿼리 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, event_params.key AS param_key, event_params.value.string_value AS string_value, event_params.value.int_value AS int_value FROM advanced.app_logs CROSS JOIN UNNEST(event_params) as event_params WHERE event_date BETWEEN "2022-01-01" AND "2022-12-31" ) # 월별 활성 사용자 분석 ,monthly_active AS ( SELECT FORMAT_DATE('%Y-%m', event_date) as year_month, COUNT(DISTINCT user_id) as MAU, COUNT(DISTINCT CASE WHEN event_name = 'click_cart' THEN user_id END) as cart_users, COUNT(DISTINCT CASE WHEN event_name = 'click_payment' THEN user_id END) as payment_users, ROUND(COUNT(DISTINCT CASE WHEN event_name = 'click_payment' THEN user_id END) * 100.0 / NULLIF(COUNT(DISTINCT CASE WHEN event_name = 'click_cart' THEN user_id END), 0), 2) as cart_to_payment_rate FROM base GROUP BY year_month ORDER BY year_month ) SELECT * FROM monthly_active 시각화 결론 8월~10월 사이 사용자 확대 특시 10월에 MAU가 증가하여 서비스의 성장 사용자 수가 크게 증가했음에도 구매전환율이 안정적으로 유지 MAU증가와 함께 실제 구매자 수도 증가 ⇒ 서비스가 8~10월 사이 급격한 성장세를 통해 사용자가 증가하였으며 일시적인 효과가 아닌 안정적으로 성장세에 맞는 고객을 확보하고 있는 중 Action Item MAU 증가 원인 파악하기 신규고객, 기존고객 중 어떤 고객이 활성화가 된건지 (신규, 기존에 따른 프로모션 등 확인하기) 신규고객이라면 어떤 채널로 유입이 된건지 유입 근거 찾아보기 기존고객이라면 기존고객 대상 프로모션을 한게 있는지, 특정페이지 개선이 되었거나, 변경사항이 있는지 확인해보기 2. Funnel 분석 쿼리 WITH base AS ( SELECT DISTINCT user_id, event_name, event_timestamp, DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_datetime, DATE(DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul')) AS event_date, user_pseudo_id FROM advanced.app_logs WHERE event_date BETWEEN "2022-01-01" AND "2022-12-31" ), user_journey AS ( SELECT user_id, event_date, event_name, event_datetime, -- 사용자별 이벤트 발생 순서 ROW_NUMBER() OVER (PARTITION BY user_id, event_date ORDER BY event_datetime) as event_sequence FROM base ), funnel_steps AS ( SELECT event_date, COUNT(DISTINCT CASE WHEN event_name = 'screen_view' THEN user_id END) as view_users, COUNT(DISTINCT CASE WHEN event_name = 'click_cart' THEN user_id END) as cart_users, COUNT(DISTINCT CASE WHEN event_name = 'click_payment' THEN user_id END) as payment_users FROM user_journey GROUP BY event_date ), funnel_rates AS ( SELECT event_date, view_users, cart_users, payment_users, ROUND(cart_users * 100.0 / NULLIF(view_users, 0), 2) as view_to_cart_rate, ROUND(payment_users * 100.0 / NULLIF(cart_users, 0), 2) as cart_to_payment_rate, ROUND(payment_users * 100.0 / NULLIF(view_users, 0), 2) as view_to_payment_rate FROM funnel_steps ) #SELECT * FROM funnel_rates #ORDER BY event_date SELECT event_date, view_users as view_users_count, cart_users as cart_users_count, payment_users as payment_users_count, ROUND((cart_users * 100.0 / view_users), 2) as view_to_cart_rate, ROUND((payment_users * 100.0 / cart_users), 2) as cart_to_payment_rate, ROUND((payment_users * 100.0 / view_users), 2) as total_conversion_rate FROM funnel_rates ORDER BY event_date 시각화 ⇒ 공휴일 등 특정 날짜에 고객 유입 변동이 큼 8월부터 10월까지 고객이 급격하게 늘었으나 이탈율은 9월에 높게 나타났음 그에 비해 10월은 고객이 크게 늘어났음에도 이탈율은 큰폭으로 보이지 않음 view > cart는 20% 수준의 이탈율이나, cart > payment가 60% 수준의 큰 이탈율 발생 ⇒ 구간별 고객수가 전체적으로 같이 확대되어 이탈율은 월별 특이점 없음 Action Item cart에서 payment 이탈 고객에게 쿠폰지급 프로모션 또는 결제단계 최소화를 통해 이탈율 개선 방법 모색 필요
