묻고 답해요
137만명의 커뮤니티!! 함께 토론해봐요.
인프런 TOP Writers
-
미해결BigQuery(SQL) 활용편(퍼널 분석, 리텐션 분석)
최종 과제 제출
안녕하세요. 최종 과제 노션으로 작성해서 링크 공유드립니다.감사합니다!https://staticstic.notion.site/9c91ee729d734edeb5a85cc8fa6bf2b6?pvs=4
-
미해결BigQuery(SQL) 활용편(퍼널 분석, 리텐션 분석)
퍼널 분석
강사님 안녕하세요,섹션4 리텐션 분석 들어가기 전에 app_logs 데이터 가지고 퍼널분석을 진행해 보았습니다!https://www.notion.so/de12e9d1d9fd4e9595cee4ddd5f5c6cf?pvs=4
-
해결됨BigQuery(SQL) 활용편(퍼널 분석, 리텐션 분석)
결제계정 등록했는데도 불구하고 app_logs 테이블에 데이터가 존재하지 않습니다
무료 체험판이 만료되어서 결제 계정을 활성화시켰습니다.그런데 app_logs 테이블에 데이터가 존재하지 않습니다.어떻게 해결해야 할까요 궁금합니다...!
-
해결됨초보자를 위한 BigQuery(SQL) 입문
2-6 연습문제 15번 질문있습니다!
15.트레이너가 보유한 포켓몬(trainer_pokemon)이 제일 많은 트레이너는 누구일까요? 문제에서 SELECT trainer_id, COUNT(id) AS cnt FROM basic.trainer_pokemon WHERE status != "Released" GROUP BY trainer_id ORDER BY cnt DESC; 이렇게 풀어봤는데 풀어준 포켓몬이 제외되어서 카일님과 다른 답이 도출되더라구요. 풀어준 것은 보유중이 아닐거라 생각해서 쿼리를 작성했었는데 이렇게 풀이한 것도 괜찮은 풀이방법인지 질문드립니다!
-
해결됨초보자를 위한 BigQuery(SQL) 입문
총 정리 5번 문제 질문
안녕하세요 카일님, 카일님이 푼 결과와 제가 푼 결과가 달라서 질문드립니다.저는 5번 문제에서 트레이너가 '잡았던'이라고 해서 다음과 같은 쿼리를 작성했습니다. WHERE tp.status IN ('Active', 'Training')그런데 카일님 해설 영상을 보니깐 WHERE을 따로 작성 안 하셨더라고요!혹시 제가 문제를 잘못 해석한걸까요??제가 작성한 쿼리 및 결과는 이미지에 첨부해 뒀습니다
-
해결됨초보자를 위한 BigQuery(SQL) 입문
섹션 8 문제 풀이 1번 질문 있습니다!
안녕하세요 카일님, 질문이 있습니다!trainer_pokemon 테이블에서 '트레이너가 보유한 포켓몬의 수'를 구하실 때'포켓몬 수'를 COUNT(pokemon_id)가 아닌, COUNT(id)를 사용한 이유를 구체적으로 알 수 있을까요..?아직 각 테이블 내에 있는 id에 대한 개념이 잘 안 잡혀서 질문 드립니다 ㅠ..
-
해결됨초보자를 위한 BigQuery(SQL) 입문
JOIN 4번 문제
4번 문제 같은 경우, 쿼리 효율화를 위해서는 trainer_pokemon테이블에서 status로 행을 줄여주기 보단, Trainer에 master 조건으로 행을 줄여주고 trainer_pokemon을 inner join하는게 더 좋은 방법 아닌가요?? 궁금합니다!
-
해결됨BigQuery(SQL) 활용편(퍼널 분석, 리텐션 분석)
리텐션 과제 작성
안녕하세요. 리텐션 과제 작성하여 노션 링크 첨부하였습니다.제가 다른 분야에서 업무를 하다가 앱 로그 데이터를 처음 다뤄보게 되었는데 어려운 점이 많네요.. ㅠ 과제를 진행하면서 모르겠는 점도 있고 해서 먼저 피드백을 받고 다시 진행해보려고 합니다.좋은 강의 만들어주셔서 감사합니다!https://www.notion.so/staticstic/880f80adb2d7414e8cfa417512b263d4?pvs=4
-
해결됨BigQuery(SQL) 활용편(퍼널 분석, 리텐션 분석)
3-7. Weekly 리텐션 쿼리 작성 및 질문
안녕하세요.Weekly로 리텐션 쿼리 아래와 같이 작성해 보았습니다.한가지 질문할 점은 event_week 기간 동안 한번 이상 접속을 한다고 하더라도 user_psuedo_id는 한번으로 집계되는지 궁금합니다!그리고 작성한 쿼리문을 좀 더 나은 방향으로 수정하면 좋은 방향이 있다면 언제든 말씀 부탁드릴께요! 강의 정말 잘 듣고 있습니다 :)===작성한 쿼리문===WITH base AS ( SELECT DISTINCT user_id, 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 "2022-11-03" ), diff_of_week_data AS ( SELECT *, DATE_DIFF(event_week, first_week, week) AS diff_of_week FROM( SELECT DISTINCT user_pseudo_id, MIN (DATE_TRUNC(event_date,WEEK(MONDAY))) OVER (PARTITION BY user_pseudo_id) AS first_week, DATE_TRUNC(event_date, WEEK(MONDAY)) AS event_week FROM base ) ) SELECT *, SAFE_DIVIDE(cnt, first_value_cnt) AS retention_ratio FROM( SELECT *, FIRST_VALUE(cnt) OVER (ORDER BY diff_of_week) AS first_value_cnt FROM( SELECT diff_of_week, COUNT(user_pseudo_id) AS cnt FROM diff_of_week_data GROUP BY diff_of_week ) ) ORDER BY diff_of_week
-
해결됨BigQuery(SQL) 활용편(퍼널 분석, 리텐션 분석)
리텐션 분석 과제
리텐션 분석 과제 노션 문서에 정리해서 공유드립니다~문서 링크
-
해결됨BigQuery(SQL) 활용편(퍼널 분석, 리텐션 분석)
3-13 4번 문제 풀어보았습니다
4. Core Event를 "click_payment"라고 설정하고 Weekly Retention을 구해주세요. WITH base AS( SELECT event_date, event_timestamp, user_pseudo_id, event_name, MAX(IF(ep.key = 'firebase_screen', ep.value.string_value, NULL)) AS firebase_screen, MAX(IF(ep.key = 'food_id', ep.value.int_value, NULL)) AS food_id, MAX(IF(ep.key = 'session_od', ep.value.string_value, NULL)) AS session_od FROM advanced.app_logs CROSS JOIN UNNEST(event_params) AS ep GROUP BY ALL ), payment AS( SELECT user_pseudo_id, event_timestamp, event_date, event_name, --firebase_screen, FROM base WHERE event_name = 'click_payment' ORDER BY event_date ), payment_first_week AS( SELECT *, 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( SELECT * EXCEPT(event_timestamp, event_date), DATE(DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul')) AS event_date FROM payment ) ), payment_user_cnt AS( SELECT diff_of_week, COUNT(DISTINCT user_pseudo_id) AS user_cnt FROM( SELECT *, DATE_DIFF(event_week, first_week, WEEK) AS diff_of_week FROM payment_first_week ) GROUP BY diff_of_week ORDER BY diff_of_week ) ############################### 리텐션 비율 구하기 ############################### SELECT *, SAFE_DIVIDE(user_cnt, first_week_user_cnt) AS retention_rate FROM( SELECT *, FIRST_VALUE(user_cnt) OVER(ORDER BY diff_of_week) AS first_week_user_cnt FROM payment_user_cnt ) 여기까지는 payment 테이블 WHERE절에 event_name으로 "click_payment" 조건을 걸어준 것 빼곤 강의의 코드를 거의 그대로 사용했습니다. 결과는 이렇게 나왔고, 저는 diff_of_week가 4일 때 갑자기 user_cnt가 상승했다가 5일 때 다시 이전 수치로 돌아간 것에 주목했습니다(약 18% 상승). SELECT event_week, COUNT(DISTINCT user_pseudo_id) AS user_cnt FROM( SELECT *, DATE_DIFF(event_week, first_week, WEEK) AS diff_of_week FROM payment_first_week ) WHERE diff_of_week = 4 GROUP BY event_week ORDER BY user_cnt DESC 그래서 diff_of_week가 4일 때의 "click_payment" 이벤트를 발생시킨 유저의 수를 추출해보았습니다. 결과를 보았을 때, 2022-10-31 ~ 2023-01-09의 유저 수가 많음을 확인하였고 이는, 해당 기간이 첫 주문 후 4주가 지난 사람들의 재주문 건수가 많았을 것이라는 생각이 들었습니다. ##################### 해당 날짜, 이전, 이후의 데이터 추출 #################### # target_date 테이블: 해당 날짜를 만족하는 행만 추출 , target_date AS( SELECT * FROM base WHERE user_pseudo_id IN( SELECT user_pseudo_id FROM payment_first_week WHERE first_week BETWEEN '2022-10-03' AND '2022-12-12' ) ORDER BY user_pseudo_id, event_timestamp # before_target_date 테이블: 해당 날짜 이전 ), before_target_date AS( SELECT * FROM base WHERE user_pseudo_id IN( SELECT user_pseudo_id FROM payment_first_week WHERE first_week BETWEEN '2022-08-01' AND '2022-10-02' ) ORDER BY user_pseudo_id, event_timestamp # after_target_date 테이블: 해당 날짜 이후 ), after_target_date AS( SELECT * FROM base WHERE user_pseudo_id IN( SELECT user_pseudo_id FROM payment_first_week WHERE first_week BETWEEN '2022-12-13' AND '2023-12-31' ) ORDER BY user_pseudo_id, event_timestamp ) ################ click_payment를 발생시킨 유저와 그 때의 날짜 추출 ################ # payment_users 테이블: 해당 날짜에서 event_name칼럼에 "click_payment"를 가지고 있는 user_pseudo_id와 그 때의 event_date를 추출 , payment_users_target_date AS( SELECT event_date, user_pseudo_id FROM( SELECT *, CASE WHEN event_name = 'click_payment' THEN 1 ELSE 0 END AS payment_user FROM target_date ) WHERE payment_user = 1 ) # payment_users_before 테이블: 해당 날짜 이전의 payment user의 정보 ,payment_users_before AS( SELECT event_date, user_pseudo_id FROM( SELECT *, CASE WHEN event_name = 'click_payment' THEN 1 ELSE 0 END AS payment_user FROM before_target_date ) WHERE payment_user = 1 ) # payment_users_after 테이블: 해당 날짜 이후의 payment user의 정보 ,payment_users_after AS( SELECT event_date, user_pseudo_id FROM( SELECT *, CASE WHEN event_name = 'click_payment' THEN 1 ELSE 0 END AS payment_user FROM after_target_date ) WHERE payment_user = 1 ) 그래서 전체 날짜를 위의 해당 날짜와 그 전, 후 3개로 나누어 해당 기간의 정보만을 담는 테이블을 생성하고, 각 기간에 "click_payment" 이벤트를 가지고 있는 유저의 user_pseudo_id와 그 때의 event_date를 추출하였습니다.################ 3개의 기간동안 사람들이 주문한 음식의 food_id 추출 ################ # 해당 기간동안 사람들이 주문한 food_id , food_cnt_target_date AS( SELECT food_id, COUNT(*) AS food_cnt FROM target_date td JOIN payment_users_target_date pu ON td.user_pseudo_id = pu.user_pseudo_id AND td.event_date = pu.event_date WHERE event_name = "click_cart" GROUP BY food_id ORDER BY food_cnt DESC --ORDER BY td.user_pseudo_id, event_timestamp ), # 해당 기간 이전 사람들이 주문한 food_id food_cnt_before_target AS( SELECT food_id, COUNT(*) AS food_cnt FROM before_target_date td JOIN payment_users_before pu ON td.user_pseudo_id = pu.user_pseudo_id AND td.event_date = pu.event_date WHERE event_name = "click_cart" GROUP BY food_id ORDER BY food_cnt DESC ), # 해당 기간 이후 사람들이 주문한 food_id food_cnt_after_target AS( SELECT food_id, COUNT(*) AS food_cnt FROM after_target_date td JOIN payment_users_after pu ON td.user_pseudo_id = pu.user_pseudo_id AND td.event_date = pu.event_date WHERE event_name = "click_cart" GROUP BY food_id ORDER BY food_cnt DESC ) 위에서 구한 id와 date를 이용해 세 기간동안 사람들이 주문한 음식과 음식 별 건수를 추출하였습니다. 전체 데이터를 살펴본 결과, "click_payment"는 항상 주문 마지막에 발생하는 이벤트이며, 이 row에는 food_id는 없고, "click_cart" 이벤트를 발생시켰을 때 해당 주문건에 대한 food_id가 row에 포함되어 있어 event_name을 "click_cart"로 지정하였습니다. ################ 각 food_id 별 해당 기간, 이전, 이후의 주문 건수 카운트 ################ , total_cnt AS( SELECT td.food_id, btd.food_cnt AS food_cnt_before_target_date, td.food_cnt AS food_cnt_target_date, atd.food_cnt AS food_cnt_after_target_date FROM food_cnt_target_date td JOIN food_cnt_before_target btd ON td.food_id = btd.food_id JOIN food_cnt_after_target atd ON td.food_id = atd.food_id ) ################ 각 food_id 별 해당 기간, 이전, 이후의 주문 건수 비율 ################ SELECT food_id, food_cnt_before_target_date, ROUND(SAFE_DIVIDE(food_cnt_before_target_date, SUM(food_cnt_before_target_date) OVER()), 4) AS rate_before, food_cnt_target_date, ROUND(SAFE_DIVIDE(food_cnt_target_date, SUM(food_cnt_target_date) OVER()), 4) AS rate_target, food_cnt_after_target_date, ROUND(SAFE_DIVIDE(food_cnt_after_target_date, SUM(food_cnt_after_target_date) OVER()), 4) AS rate_after FROM total_cnt ORDER BY rate_target DESC세 기간동안 사람들이 주문한 food_id와 그 횟수, 비율을 구해보았습니다. 각 음식의 비율은 매우 적지만 target_date를 기준으로 정렬해 보았을 때, 확실히 해당 기간동안 주문량이 높고 나머지 기간에는 주문량이 낮은 음식들이 존재했습니다(ex. 1438, 1516 등). 하지만, 이것이 4주전 주문자들의 실제 재주문 결과인지, 단지 해당기간 동안 신규 유저가 늘었기 때문인지 확실하지 않다고 생각문자와 그 외 기간동안의 신규 주문자를 비교해 보았습니다. SELECT new_user_target_date, COUNT(*) AS new_user_cnt FROM( SELECT CASE WHEN first_week BETWEEN '2022-10-31' AND '2023-01-09' THEN 1 ELSE 0 END AS new_user_target_date FROM payment_first_week ) GROUP BY new_user_target_date 해당 기간의 신규 유저수는(new_user_target_date = 1) 6261명, 그 외 기간의 신규 유저수는 6898명으로, 전체 기간 대비 해당 기간의 비율을 고려해보았을 때, 신규 유저가 크게 늘었다고 보기 어려웠습니다. 따라서, 2022~10-03 ~ 2022-12-12일이 포함된 주차에 첫 주문을 한 고객들의 만족도가 높았거나, 그 당시에 시행했던 첫 주문 관련 마케팅, 광고 효과가 좋았을 것이라고 판단할 수 있었습니다. ** 앱 로그 데이터를 처음 접했기 때문에 코드 오류나 논리적인 비약이 있을 것이라고 생각하지만, 단지 Weekly Retention에 WHERE 조건을 추가하는 것에 그치기 아쉬워서(?) 의식의 흐름대로 분석해보았습니다.
-
해결됨초보자를 위한 BigQuery(SQL) 입문
JOIN 5번 문제 질문 있습니다!
안녕하세요, 카일님! 수업 잘 듣고 있습니다!문제 해설 보기 전 혼자 풀었을 때, 저는 아래와 같이 쿼리를 작성했어요. 서브쿼리 안에 필요한 컬럼만 넣고 보면서 쓰는 게 편하더라고요. 제 쿼리로 쓰면 데이터를 더 많이 처리하게 되는 건지 궁금해서 질문합니다!
-
해결됨BigQuery(SQL) 활용편(퍼널 분석, 리텐션 분석)
3-7. 리텐션 쿼리 작성해보기(Weekly, Montly)
Weekly 리텐션 분석#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" ), retain_base as ( select user_pseudo_id, event_week, first_week, date_diff(event_week, first_week, week) as diff_of_week from ( select distinct user_pseudo_id, date_trunc(event_date, week(monday)) as event_week, date_trunc(min(event_date) over(partition by user_pseudo_id),week(monday)) as first_week, -- 유저의 첫 접속 주차 from base ) ) select diff_of_week, user_cnt, first_value(user_cnt) over(order by diff_of_week) as first_user_cnt from ( select diff_of_week, count(distinct user_pseudo_id) as user_cnt from retain_base group by diff_of_week ) order by diff_of_week Montly 리텐션 분석#Montly 리텐션 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" ), retain_base as ( select user_pseudo_id, event_month, first_month, date_diff(event_month, first_month, month) as diff_of_month from ( select distinct user_pseudo_id, date_trunc(event_date, month) as event_month, date_trunc(min(event_date) over(partition by user_pseudo_id),month) as first_month, -- 유저의 첫 접속 주차 from base ) ), month_retain as ( select diff_of_month, user_cnt, first_value(user_cnt) over(order by diff_of_month) as first_user_cnt from ( select diff_of_month, count(distinct user_pseudo_id) as user_cnt from retain_base group by diff_of_month ) ) select diff_of_month, user_cnt, first_user_cnt, round(safe_divide(user_cnt, first_user_cnt),3) as retention_rate from month_retain order by diff_of_month 위클리랑 먼슬리 리텐션 쿼리 작성했습니다~
-
해결됨BigQuery(SQL) 활용편(퍼널 분석, 리텐션 분석)
FRAME 연습 마지막 AVG 문제 소수점에 대하여 여쭤볼게 있습니다!
문제풀이를 해가던 도중마지막 ROWS BETWEEN을 이용한 5가지 AMOUNT의 AVG를 구할때 소수점이 나오는데SELECT절에 적힌 WINDOW 함수 쿼리를ROUND, FLOOR 을 사용해서 감싸줘도 작동은 하지만 소수점이 사라지지 않는데 혹시 그 이유가 뭔가요???
-
해결됨BigQuery(SQL) 활용편(퍼널 분석, 리텐션 분석)
디스코드 접속 관련
질문을 남겨주실 때 아래 부분을 같이 남겨주시면 더 빠르게 상황을 파악하고 답변드릴 수 있어요 🙂어떤 강의를 들으면서 발생했나요? BigQuery 활용편어떤 문제가 생겼나요? 디스코드 접속 불가어떤 시도를 해보셨나요? 강의 중 제공되는 링크로 디스코드 접속시 무한 로딩 발생어떤 쿼리를 사용했나요? - 환경이 어떻게 되나요?(윈도우, 맥 중 택1, 크롬 같은 웹브라우저 환경) 윈도우
-
해결됨초보자를 위한 BigQuery(SQL) 입문
5-6. 2번 연습문제 질문입니다!
안녕하세요. 강의에서는LEFT JOIN ...ON ...하고 나서 WHERE type1='Grass'으로 먼저 필터링을 하신 것 같은데요!아래 처럼 GROUP BY - HAVING으로 해도 결과는 동일한데,GROUP BY 보다 WHERE를 먼저 했을 때 수행 속도 등의 이득이 있는지 궁금합니다!SELECT type1, COUNT(type1) AS pokemon_cnt FROM ( SELECT id, trainer_id, pokemon_id, status FROM `basic.trainer_pokemon` WHERE status IN ('Active', 'Training') ) AS tp LEFT JOIN `basic.pokemon` AS p ON tp.pokemon_id = p.id GROUP BY type1 HAVING type1 = 'Grass'
-
해결됨BigQuery(SQL) 활용편(퍼널 분석, 리텐션 분석)
강의 중간에 쿼리 복사 붙여넣기 해야하는 부분들
안녕하세요!기본편에 이어 활용편을 열심히 듣고 있는 학생입니다. 제 브라우저나 pc의 문제인지는 모르겠지만, 강의 중간에 쿼리를 복사 붙여넣기 해야 되는 부분들이 있을 때, 인프런 플레이어에서 문자인식 후 복사하는 기능을 제공하지 않습니다..그래서 따로 캡쳐를 따서 문자를 인식한 후, 복사 붙여넣기를 해야 하는데요. 이럴 때도 정확도가 좋지 않아서 일일이 수기로 다 수정을 해야합니다. 혹시 강의 중간에 복사 붙여넣어 사용해야할 쿼리가 있다면 혹시 해당 강의에 강의 자료 부분에 텍스트로 올려놔주실 수 있을까요? 그렇다면 많은 학생들이 쿼리를 쉽게 복사, 붙여넣어 강의를 빠르게 들을 수 있을 것 같습니다. 항상 열심히 강의 해주시고 답변해주셔서 감사합니다!
-
해결됨초보자를 위한 BigQuery(SQL) 입문
battle 테이블 오류
battle 파일을 반디집으로 압축 풀기하고 업로드 했는데 다음과 같은 오류가 뜹니다 어떻게 해야할까요?
-
해결됨초보자를 위한 BigQuery(SQL) 입문
2-6. 연습문제 12번
연습문제 풀던 중에 궁금한 게 생겨서 여쭤봅니다!12번에서 단일 포켓몬 중 많은 타입 1을 구할 때, 지금 데이터에선 type2가 null인 경우만 있지만, type1도 null인 경우도 있다고 가정하면,where (type2 is null) or (type1 is null) 처럼 or 조건으로 표현하면 될까요?
-
해결됨BigQuery(SQL) 활용편(퍼널 분석, 리텐션 분석)
Retention 쿼리를 작성하면서.. 궁금한점이 있습니다.
Weekly Retention은 diff_of_week 을 활용하여, 시간의 경과에 따른 리텐션 변화를 구합니다. 따라서, 제가 생각했을 때, Weekly Retention을 구하는 쿼리에서는 다음과 같은 가정을 하고 진행한 것이 아닐까? 라는 생각이 들었습니다.주차별로(시간에 따라) 활동 중인 사용자 수는 달라질 것이다.user_type에 따라 활동 중인 사용자 수는 차이가 있을 것이다.2.의 경우는 만약의 신규/복귀... 유저를 구분한다면, 해당 가설을 기반으로, Weekly Retention을 구하는 행위를 한 것이 아닐까? 라는 생각이 들었습니다.(추가 궁금증)Retention에 영향을 주는 인자를 분석하는 경우도 있을까요? (실무에서) 저는 Retention을 분석하기 전에, Retention과 관련이 높은 것이 무엇일지, 가설을 세우고 검정을 해보았습니다. 가설: 방문일수는 Retention에 높은 상관관계를 가진다.데이터 범위: 2022-08-01 ~ 2022-11-01D7_retention : (bool) 사용자의 첫번째 이벤트 시점 ~ 7일 이후에도 활동을 하면, 1 아니면 0D30_retention : (bool) 사용자의 첫번째 이벤트 시점 ~ 30일 이후에도 활동을 하면, 1아니면 0import os from google.cloud import bigquery from google.oauth2 import service_account import pandas as pd import statsmodels.api as sm from scipy.stats import pointbiserialr import numpy as npos.environ['GOOGLE_APPLICATION_CREDENTIALS'] = './service_account.json' key_path = './service_account.json' credentials = service_account.Credentials.from_service_account_file( key_path, scopes = ["https://www.googleapis.com/auth/cloud-platform"], ) client = bigquery.Client(credentials=credentials, project=credentials.project_id, location="US")query = """ WITH user_visits AS ( SELECT user_pseudo_id, COUNT(DISTINCT event_date) AS visit_days FROM advanced.app_logs WHERE event_date BETWEEN '2022-08-01' AND '2022-11-01' GROUP BY user_pseudo_id ), retention_data AS ( SELECT user_pseudo_id, MIN(event_date) AS first_event_date, MAX(event_date) AS last_event_date, CASE WHEN MAX(event_date) >= DATE_ADD(MIN(event_date), INTERVAL 7 DAY) THEN 1 ELSE 0 END AS D7_retention, CASE WHEN MAX(event_date) >= DATE_ADD(MIN(event_date), INTERVAL 30 DAY) THEN 1 ELSE 0 END AS D30_retention FROM advanced.app_logs WHERE event_date BETWEEN '2022-08-01' AND '2022-11-01' GROUP BY user_pseudo_id ), combined_data AS ( SELECT v.user_pseudo_id, v.visit_days, r.D7_retention, r.D30_retention FROM user_visits v JOIN retention_data r ON v.user_pseudo_id = r.user_pseudo_id ) SELECT * FROM combined_data; """df = client.query(query).to_dataframe() df['visit_days'] = pd.to_numeric(df['visit_days'], errors='coerce').astype(np.float64) df['D7_retention'] = pd.to_numeric(df['D7_retention'], errors='coerce').astype(np.float64) df['D30_retention'] = pd.to_numeric(df['D30_retention'], errors='coerce').astype(np.float64) # 결측치가 있는지 확인하고 제거 df = df.dropna(subset=['visit_days', 'D7_retention', 'D30_retention']) # 상수항 추가 X = sm.add_constant(df[['visit_days']]) # D7_retention에 대한 로지스틱 회귀 모델 적합 y_D7 = df['D7_retention'] logit_model_D7 = sm.Logit(y_D7, X).fit() print(logit_model_D7.summary()) # D30_retention에 대한 로지스틱 회귀 모델 적합 y_D30 = df['D30_retention'] logit_model_D30 = sm.Logit(y_D30, X).fit() print(logit_model_D30.summary())visit_days_range = np.linspace(df['visit_days'].min(), df['visit_days'].max(), 100) prob_D7 = logit_model_D7.predict(sm.add_constant(visit_days_range)) prob_D30 = logit_model_D30.predict(sm.add_constant(visit_days_range)) plt.plot(visit_days_range, prob_D7, label='D7 Retention Probability') plt.plot(visit_days_range, prob_D30, label='D30 Retention Probability', linestyle='--') plt.xlabel('Visit Days') plt.ylabel('Retention Probability') plt.title('Retention Probability vs Visit Days') plt.legend() plt.show()따라서, 방문일수는 Retention과 상관성을 보인다. 가설2. 방문일 수는 user_type에 따라 각기 다른 상관성을 보일 것이다.결론: user_type은 new_user, current_user는 통계적으로 유의하며, 높은 상관성을 가지나, 휴면 유저, 복귀 유저는 통계적으로 유의미하지 않으며, 낮은 상관성을 띈다. 이렇게 결론을 내놓는 방식이, 적합한 방식인지 궁금합니다.