app logs 데이터로 쿼리 연습

안녕하세요 카일님, 잘 듣고 있습니다.

강의 말미에서 app logs 데이터로 쿼리 연습 해보라고 말씀주셔서 아래와 같이 몇 가지 연습해 보았습니다.

더 정확한, 효율적인 쿼리가 있는지,

틀린 부분이 있다면 피드백 부탁드립니다.

1. Screen 탐색량이 가장 많은 유저 찾기

질문: 어떤 유저가 앱 내 screen을 가장 많이 탐색했는가?

목적: 유저별 screen 조회 횟수를 집계하여 heavy user 탐색.

SELECT

  user_id,

  -- IF(event_params.key="firebase_screen", event_params.value.string_value, null) as screen,

  COUNT(*) as cnt_screen

FROM advanced.app_logs

CROSS JOIN UNNEST(event_params) as event_params

WHERE event_params.key='firebase_screen' 

GROUP BY ALL

HAVING user_id is not null

ORDER BY cnt_screen DESC

2. 날짜별 Screen 조회량 및 Screen 구성

질문: 날짜별 전체 screen 조회량은 어떻게 변화하며, 각 screen은 얼마나 조회되었는가?

목적: Screen View의 시계열 변화와 screen별 조회량을 동시에 분석. double check컬럼을 통해 정의한 screen들의 합계도 검증.

WITH unnest_table as (

SELECT

  *,

  IF(event_params.key="firebase_screen", event_params.value.string_value, null) as screen

FROM advanced.app_logs

CROSS JOIN UNNEST(event_params) as event_params

GROUP BY ALL

HAVING screen is not null)

SELECT

event_date,

count(screen) as total_screen_view,

COUNTIF(screen IN ('cart','welcome', 'food_category', 'home', 'search_result', 'food_detail', 'search','restaurant')) as double_check, --검증용 컬럼

 COUNTIF(screen='cart') as cart,

 COUNTIF(screen='welcome') as welcome,

 COUNTIF(screen='food_category') as food_category,

 COUNTIF(screen='home') as home,

 COUNTIF(screen='search_result') as serach_result,

 COUNTIF(screen='food_detail') as food_detail,

 COUNTIF(screen='search') as serach,

 COUNTIF(screen='restaurant') as restaurant

FROM unnest_table

GROUP BY ALL

3. Heavy User의 실제 Screen Journey 추적

질문: 특정 heavy user는 실제로 어떤 순서로 screen을 탐색하는가?

목적: Timestamp를 기준으로 사용자의 screen 이동 순서를 확인하고, firebaase screen 퍼널 순서 정의를 위한 탐색 수행.

SELECT

   TIMESTAMP_MICROS(event_timestamp) as timestamp,

  if(event_params.key="firebase_screen", event_params.value.string_value, null) as screen

FROM advanced.app_logs

CROSS JOIN UNNEST(event_params) as event_params

WHERE user_Id=57410

and

event_date between '2022-08-01' and '2023-12-30'

GROUP BY ALL

HAVING screen is not null

ORDER BY timestamp

limit 100

4. Cart 도달 유저 vs Non-Cart 유저의 Screen 탐색량 비교

질문: Cart를 한 번이라도 조회한 유저는 그렇지 않은 유저보다 평균적으로 더 많은 screen을 탐색하는가?

목적: 사용자를 has_cart 여부로 segmentation하고 두 그룹의 평균 screen 탐색량 비교.

WITH basic AS (

SELECT

  user_id,

  COUNT(*) AS screen_cnt_per_user,

  COUNTIF(event_params.value.string_value='cart') >0 AS has_cart

FROM advanced.app_logs

CROSS JOIN UNNEST(event_params) as event_params

WHERE event_params.key='firebase_screen'

GROUP BY user_id)

SELECT

  AVG(IF(has_cart, screen_cnt_per_user,NULL )) as avg_view_cart,

  AVG(IF( not has_cart, basic.screen_cnt_per_user, null)) as avg_view_non_cart

FROM basic

unnest 와 집계함수를 연속적으로 쓰려고 할 때나 unnest+집계함수+having 쓰려고 할 때는 반드시 with문으로 분리해서 사용해야 오류가 안 나는 것 같더라고요. 그러다 보니 쿼리가 길어지는데 더 가독성 좋은 쿼리 작성법이 있을 것 같습니다. ㅎㅎ

WITH screen_events AS (
  SELECT
    event_date,
    event_timestamp,
    user_id,
    user_pseudo_id,
    event_param.value.string_value AS screen
  FROM advanced.app_logs
  CROSS JOIN UNNEST(event_params) AS event_param
  WHERE event_name = 'screen_view'
    AND event_param.key = 'firebase_screen'
)

SELECT
  user_pseudo_id,
  COUNT(*) AS screen_cnt
FROM screen_events
GROUP BY user_pseudo_id
ORDER BY screen_cnt DESC;