SELECT user_id , event_date , event_name , event_timestamp , user_pseudo_id , MAX(IF(par.key='firebase_screen', par.value.string_value, NULL)) AS firebase_screen , MAX(IF(par.key='food_id', par.value.int_value, NULL)) AS food_id , MAX(IF(par.key='session_id', par.value.string_value, NULL)) AS session_id FROM `advanced.app_logs` CROSS JOIN UNNEST(event_params) AS par WHERE event_date = '2022-08-01' GROUP BY ALL LIMIT 100; app_logs 피벗하는 문제인데 food_id가 null 밖에 없는 건 아니지만 보여주신 화면과 그 값이 많이 상이한 거 같아서요.. 이유가 뭔지를 알 수 있을까요 ㅜㅜ
스프레드시트는 혼자 쓸 수도 있지만, 협업과 공유가 핵심 인것 같습니다. 혹시 팀이나 조직 단위에서 시트를 관리하실 때 도움이 되는 운영 노하우나 베스트 프랙티스 가 있을까요? 기능 설명 외에도, 실제 업무에서 시트를 체계적으로 관리하는 방법이 궁금해져서 혹시 해주실 말씀이 있으신지 문의드립니다.
카일스쿨님 매번 친절한 답변 감사드립니다 정말 많이 배웁니다!! 🙇 N day 리텐션 쿼리에 관한 질문이 3가지 있습니다. 1. 맨 처음에 base 테이블 만들 때 DISTINCT를 왜 써야하는 건지 궁금합니다. (B방법 기준) first_date_and_diff 임시테이블을 만들 때 DISTINCT를 쓰는 것은, '동일 유저가 하루에 여러번 접속한 것을 중복 제거하기 위해' 사용하는 것으로 이해했습니다. 그런데 맨 처음에 base 테이블은 timestamp 컬럼이 있는데 어떻게 중복이 있을 수 있는 건지 헷갈립니다. 완전히 동일한 마이크로초에 여러번의 행동 로그가 기록될 수 있는 건지, 아니면 전산오류로 로그가 중복 기록이 될 수 있다는 건지,, 이해가 잘 안 됩니다 😢 2. 강의에서 보여주신 결과 테이블(diff_of_day | user_cnt)은 교안 330p와 다른 것이라고 이해했는데, 제가 이해한 것이 맞는지 확인 부탁드립니다! a) 강의 결과 테이블: 유저마다 시작일은 다를 수 있지만, 어쨌든 궁금한 건 각 유저들이 첫 접속 이후에 계속 쓰는지가 궁금한 것 → 가입일 코호트별로 그룹화하지 않아도 그자체로 의미가 있음 b) 교안 330p 테이블 : 강의 결과에서 한 단계 더 나아가서, 가입일에 따라 리텐션 추이에 차이가 있는지 더 쪼개보는 것 c) 만약 교안 330p 테이블처럼 결과를 출력하고 싶으면 , COUNT 집계할 때 GROUP BY와 SELECT에 first_date만 추가로 넣어주면 된다. (아래 쿼리 및 사진 참고) -- a) 강의 결과 SELECT diff_of_day, COUNT(DISTINCT user_pseudo_id) AS user_cnt FROM first_date_and_diff GROUP BY diff_of_day ORDER BY diff_of_day -- c) 교안처럼 출력하고 싶을 경우 SELECT first_date, diff_of_day, COUNT(DISTINCT user_pseudo_id) AS user_cnt FROM first_date_and_diff GROUP BY first_date, diff_of_day ORDER BY first_date, diff_of_day 3. Weekly 리텐션 파트에서 설명해주신 WEEK 함수는 어떻게 쓰는 걸까요? 아래 쿼리처럼 썼더니 WEEK 함수가 없다고 오류가 나던데, EXTRACT를 말씀하시려던 걸까요? EXTRACT는 제대로 나오는 거 같긴 합니다..! SELECT user_pseudo_id, event_name, event_date, DATE_TRUNC(event_date, WEEK(MONDAY)) AS event_week, EXTRACT(WEEK FROM event_date) AS event_week2, WEEK(event_date) AS event_week3 FROM( SELECT DISTINCT -- event_timestamp 기반으로 중복 제거 user_id, event_name, -- event_date, -- Firebase의 형태(ex. '20220813')와 다르니까, 아래처럼 직접 추출 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") 항상 감사드립니다!! 완강까지 달려보겠습니다!
윈도우 함수 연습문제 6번 질문입니다. 카일스쿨님 쿼리와 다른 건 다 동일하게 작성했었는데, OVER() 안에 order by를 모르고 깜빡했었습니다. 그런데도 결과가 정답( moving_avg2 )과 같게 정상 출력이 되었습니다. (QUALIFY로 검증해봤는데 moving_avg 와 전부 같다고 나오더라구요) 원래 ORDER BY를 안 써줘도 프레임 설정이 자동으로 가능한 걸까요? ORDER BY에도 기본값 같은 게 있는지 궁금합니다. -- 윗부분은 생략 SELECT date, number_of_orders, AVG(num2) OVER(ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg, AVG(num2) OVER(ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg2 FROM filled_data -- QUALIFY moving_avg != moving_avg2 ORDER BY date
실행 결과는 동일하게 나왔는데, COUNT를 다르게 쓴 것이 괜찮은지 궁금해서 질문 남깁니다! 상황 같은 날짜에 여러 번 쿼리 실행한 사용자들이 있어서 COUNT(*) 을 해야겠다고 판단했습니다. (어차피 Distinct가 없으면 같은 날짜라도 각각 카운트한다는 것을 깜빡했었습니다ㅠ) 카일스쿨님은 COUNT(query_date) 를 쓰셨고, 결과만 보면 제 쿼리와 동일하긴 합니다. 궁금한 점 제 쿼리처럼 COUNT(*)을 쓰는 경우에 문제가 될까요? 혹시 실무적으로 COUNT(*)은 잘 쓰지 않는지 궁금합니다! (Ex. 어떤 컬럼의 개수를 세는 것인지 가독성이 좋지 않다든가..) 제 쿼리는 아래와 같습니다. SELECT *, COUNT(*) OVER(PARTITION BY user) AS total_query_cnt FROM advanced.query_logs ORDER BY query_date, user
array, struct 연습문제 4번에 대한 질문입니다. 위 사진처럼 카일스쿨님과 동일한 쿼리를 작성했는데, 출력 결과가 다르게 나와서 문의드립니다. 질문1: 사진에 보시듯이 user_id 컬럼에 NULL이 들어간 행들이 있는데, 제가 뭔가 잘못한 걸까요? 만약 NULL이 나오는 게 정상이라면 그 이유도 궁금합니다! 질문2: 만약 NULL이 나오는 게 정상이라면, 실무에서도 이렇게 id 컬럼에 null이 허용되는지 궁금합니다. 보통 이런 id 값은 primary key로 쓰는 줄 알았어서요..! 질문3: 연습문제 뒤에 추가로 COUNT 출력해보는 것도 결과(cnt)가 다른데, 이것도 정상인지 확인부탁드립니다. 혹시 카일스쿨님이 강의 찍으실 때 쓰신 데이터셋과 변동사항이 있나요?
3-13 리텐션 과제 제출합니다! https://www.notion.so/13-22f60e878b2580c097eaf0e1eb15d73c?source=copy_link 혼자 쿼리를 쓰다가 고객 정의를 내리고 나니 너무 어려워 AI의 도움도 받고, 여러 방법으로 좀 접근을 해봤습니다만.. 정의를 너무 어렵게 내린 것 같아 시간이 오래 걸렸네요ㅠㅠ 서비스나 업종마다 다르겠지만, 일반적으로 신규, 활성, 복귀, 휴면 유저에 대한 정의는 어떻게 내리고, 분석을 어떤 식으로 진행하는 건가요? 과제를 진행하긴 했으나 쿼리가 제대로 작성된 게 맞는지 분석을 올바르게 한 게 맞는지 너무 어렵군여ㅠ
안녕하세요, 로그를 분석해서 글로 작성해보았습니다. 혹시 가능하시다면 한번 보시고 개선하면 좋을 점을 알려주시면 공부를 이어가는 데 많은 도움이 될 것 같습니다! 🙇♂ 노션 페이지 링크 남깁니다. https://www.notion.so/inflearn-app_logs-24eceb39ae70805f81abfee9aa86ebc2?source=copy_link 고민되었던 부분 장황하게 이것저것 설명은 했지만, 결국 도출한 결론은 '결제 경험이 있는 사용자의 재방문을 발생시켜서 결제 건수를 높여보자 ' 입니다. 재방문 사용자가 유입되도록 하고 행동을 지켜보자는 것인데,, 이러한 액션이 특별히 효과적일 것이라는 것을 로그로부터 미리 확인할 수 있는 방법이 있을까요..? 구체적으로 어떤 집단에 액션을 수행하자는 결론을 도출하고 싶었는데 잘 되지 않은 것 같습니다
weekly retention 구하기 과제 해 봤습니다. 처음에 알려주신대로 두 가지 방법 1) EXTRACT 과 2) DATA_TRUNC 을 써서 각각 해 봤는데 일을 자르는 기준이 달라서 그런지 첫 데이터는 값이 같은데 1주가 차이나는 시점부터 데이터들의 값이 미세하게 차이가 나더라구요. 그리고 차이를 계산할 때도 INT 타입이라 DIFF_DATE 대신 그냥 컬럼끼리 빼는 방법으로 계산해야 됐습니다. 만약 이게 동일 년도 내에서만 해야하는 분석이라면 어떤 방법이 더 좋은지 궁금합니다. 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 `advance.app_logs` WHERE event_date BETWEEN '2022-08-01' AND '2022-11-03' ) , first_week_data AS( SELECT *, MIN(week_date) OVER(PARTITION BY user_pseudo_id) AS first_week FROM ( SELECT user_pseudo_id, event_date, -- DATETIME_TRUNC(DATE(event_date), WEEK(MONDAY)) AS week_date, EXTRACT(WEEK FROM event_date) AS week_date FROM base ) ), week_diff_data AS ( SELECT *, -- DATE_DIFF(week_date, first_week, WEEK) AS week_diff (week_date-first_week) AS week_diff FROM first_week_data ), first_week_value_data AS( SELECT *, FIRST_VALUE(user_cnt) OVER (ORDER BY week_diff) AS first_week_value FROM( SELECT week_diff, COUNT(DISTINCT user_pseudo_id) AS user_cnt, FROM week_diff_data GROUP BY ALL) ORDER BY week_diff ) SELECT *, ROUND(SAFE_DIVIDE(user_cnt, first_week_value),3) AS retention FROM first_week_value_data
안녕하세요. 수업 잘 듣고 있습니다. 말씀 해 주신대로 풀이 보기 전에 풀어보고 다시 영상보고하니 재미도 있고 실력도 빨리 느는 것 같아요. 감사합니다! 1-9 강의에서 주신 피벗 쿼리 작성해 봤습니다! WITH funnel_base AS ( SELECT event_date, event_timestamp, event_name, -- params.key AS key, -- params.value.string_value AS string_value, -- params.value.int_value AS int_value, user_id, user_pseudo_id, platform, MAX(IF(params.key="firebase_screen",params.value.string_value,NULL))AS firebase_screen, MAX(IF(params.key="session_id",params.value.string_value,NULL))AS session_id, -- MAX(IF(params.key="food_id",params.value.int_value,NULL))AS food_id FROM `advance.app_logs` CROSS JOIN UNNEST (event_params) AS params WHERE event_date BETWEEN "2022-08-01" AND "2022-08-18" GROUP BY ALL ), filter_event AS ( -- event_name+screen SELECT * EXCEPT(event_name, firebase_screen,event_timestamp), CONCAT(event_name,'-', firebase_screen) AS event_name_with_screen, DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_timestamp FROM funnel_base WHERE event_name IN ('screen_view', 'click_payment') ), funnel_step AS ( SELECT event_date, filter_event.event_name_with_screen, CASE WHEN filter_event.event_name_with_screen = 'screen_view-welcome' THEN 1 WHEN filter_event.event_name_with_screen = 'screen_view-home' THEN 2 WHEN filter_event.event_name_with_screen = 'screen_view-food_category' THEN 3 WHEN filter_event.event_name_with_screen = 'screen_view-restaurant' THEN 4 WHEN filter_event.event_name_with_screen = 'screen_view-cart' THEN 5 WHEN filter_event.event_name_with_screen = 'click_payment-cart' THEN 6 ELSE NULL END AS step_number, COUNT(user_pseudo_id) AS cnt FROM filter_event WHERE event_date BETWEEN "2022-08-01" AND "2022-08-15" GROUP BY ALL HAVING step_number IS NOT NULL) --3. event_name_with_screen 컬럼으로, 일자별로 count SELECT event_date, SUM(IF(funnel_step.event_name_with_screen = "screen_view-welcome", cnt, null)) AS `screen_view-welcome`, SUM(IF(funnel_step.event_name_with_screen = "screen_view-home", cnt, null)) AS `screen_view-home`, SUM(IF(funnel_step.event_name_with_screen = "screen_view-food_category", cnt, null)) AS `screen_view-food_category`, SUM(IF(funnel_step.event_name_with_screen = "screen_view-restaurant", cnt, null)) AS `screen_view-restaurant`, SUM(IF(funnel_step.event_name_with_screen = "screen_view-cart", cnt, null)) AS `screen_view-cart`, SUM(IF(funnel_step.event_name_with_screen = "click_payment-cart", cnt, null)) AS `click_payment-cart` FROM funnel_step GROUP BY ALL ORDER BY event_date
안녕하세요, 수업 잘 듣고 있습니다. 실습 하려고 데이터셋 로드 하는 도중에 문제가 발생해서 혼자 해결해 보고자 했는데 잘 되지 않아 문의 드립니다. 어떤 강의를 들으면서 발생했나요? BigQuery (활용편) 어떤 문제가 생겼나요? 강의대로 데이터 로드한 후 테이블 파티션 기준으로 나눠 새로운 테이블을 생성했지만 컬럼 명은 생성이 되었는데 테이블 내용 전체가 비어있습니다. 어떤 시도를 해보셨나요? 문제 해결을 위해 ARRAY+STRUCT형태가 아닌 다른 컬럼들만 뽑아 새로운 테이블을 생성했을 때는 제대로 새 테이블이 생성 됐는데 event_params 컬럼을 포함시키면 데이터 전체가 딸려오지 않았습니다. 위 그림처럼요, 어떤 쿼리를 사용했나요? 환경이 어떻게 되나요?(윈도우, 맥 중 택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_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_count 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 ), division_before AS( SELECT *, FIRST_VALUE(user_cnt) OVER(ORDER BY diff_of_week) AS first FROM user_count ) SELECT *, ROUND(SAFE_DIVIDE(user_cnt, first),2) AS retention_rate FROM division_before
강의 다 듣고, 마지막에 주신 계산하기 좋은 형태로 만드는 피벗테이블을 만들어 보았습니다. 코드에 대한 피드백이 있으시다면 알려주세요! 감사합니다. 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 = 'food_id', event_param.value.int_value, NULL)) AS food_id, 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 "2022-08-18" GROUP BY ALL ), filter_event_and_concat_event_and_screen AS ( SELECT * EXCEPT(event_name, firebase_screen, event_timestamp), CONCAT(event_name, "-", firebase_screen) AS event_name_with_screen, DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_datetime FROM base WHERE event_name IN ("screen_view", "click_payment") ), pivot1 AS( SELECT event_date, 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 event_date, step_number ASC ) # 피벗 한번 더 돌려서 계산 쉽게 할 수 있는 테이블 형태 만들기 SELECT event_date, MAX(IF(event_name_with_screen = 'screen_view-welcome', cnt, NULL)) AS `screen_view-welcome`, MAX(IF(event_name_with_screen = 'screen_view-home', cnt, NULL)) AS `screen_view-home`, MAX(IF(event_name_with_screen = 'screen_view-food_category', cnt, NULL)) AS `screen_view-food_category`, MAX(IF(event_name_with_screen = 'screen_view-restaurant', cnt, NULL)) AS `screen_view-restaurant`, MAX(IF(event_name_with_screen = 'screen_view-cart', cnt, NULL)) AS `screen_view-cart`, MAX(IF(event_name_with_screen = 'click_payment-cart', cnt, NULL)) AS `click_payment-cart` FROM pivot1 GROUP BY event_date ORDER BY 1;
