인프런 커뮤니티 질문&답변

안녕하세요. 저는 이번에 마지막 문제를 응용해서, 퍼널별로 유저들의 새로운 세션 시작 횟수의 총합을 계산해보았습니다.

[결과 그래프]

• 새로운 세션 시작 기준: 이전 세션 시간 대비 20초 이상이 지나면, 새로운 세션을 활성화했다고 가정 (강의 내용과 동일)

1. 첫번째 강의에서 배운, UNNEST 문법과 PIVOT 을 하는 방법을 활용해서, event_params에 있는 firebase_screen의 string_value 값을 새로운 열인 firebase_screen 이라고 만듭니다. 전체 데이터를 조회하면, 쿼리 비용이 많이 들기 때문에, where 조건 절로 값을 작게 명시해줍니다.

WITH base AS (
SELECT
  event_date,
  DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_timestamp,
  event_name,
  user_pseudo_id,
  MAX(IF(ep.key = 'firebase_screen', ep.value.string_value, NULL)) AS firebase_screen
FROM advanced.app_logs
CROSS JOIN UNNEST(event_params) AS ep
WHERE
  event_date = '2022-08-18'
  AND user_pseudo_id = '5464646449.4088767327'
GROUP BY ALL
)

2. LAG() 함수를 사용하여, 이전 날짜 값을 가져옵니다. 이 때, 퍼널별로 구해야하기 때문에, PARTITION BY에 firebase_screen 을 써줍니다. 그 후, DATETIMEDIFF 함수를 사용해서, 현재값과 이전값의 차이를 계산합니다.

WITH base AS (
SELECT
  event_date,
  DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_timestamp,
  event_name,
  user_pseudo_id,
  MAX(IF(ep.key = 'firebase_screen', ep.value.string_value, NULL)) AS firebase_screen
FROM advanced.app_logs
CROSS JOIN UNNEST(event_params) AS ep
WHERE
  event_date = '2022-08-18'
  AND user_pseudo_id = '5464646449.4088767327'
GROUP BY ALL
), diff_funnel_time AS (
SELECT
  *,
  DATETIME_DIFF(event_timestamp, prev_event_timestamp, SECOND) AS second_diff
FROM (
  SELECT
    *,
    LAG(event_timestamp, 1) OVER(PARTITION BY firebase_screen ORDER BY event_timestamp) AS prev_event_timestamp
  FROM base
  )
)

SELECT * FROM diff_funnel_time

3. ROW_NUMBER() 함수를 사용해서, 퍼널별로 순서를 매깁니다. 그 후, second_diff 의 값이 NULL인 경우는 해당 퍼널의 첫 시작 위치를 가리키므로 1을 넣어주고, rn > 1이고 second_diff >= 20인 경우는 1을 넣어줍니다. 이 때 funnel_per_session_start 컬럼은 퍼널별로 새로운 세션이 시작했는지 여부를 가리킵니다.

, funnel_start AS (
  SELECT
    *,
    CASE
      WHEN second_diff IS NULL THEN 1
      WHEN rn > 1 AND second_diff >= 20 THEN 1
      ELSE 0
    END AS funnel_per_session_start
  FROM (
    SELECT
      *,
      ROW_NUMBER() OVER(PARTITION BY firebase_screen ORDER BY event_timestamp) AS rn 
    FROM diff_funnel_time
  )
)

4. 이제, 퍼널별로 세션 시작 여부를 SUM()하고, GROUP BY를 해줍니다.

SELECT
  firebase_screen,
  SUM(funnel_per_session_start) AS funnel_per_session_start_cnt
FROM funnel_start
GROUP BY ALL
ORDER BY 2 DESC;

5. WHERE 조건절을 주석처리하고, 전체 데이터에 대해서, 집계해봅니다.

WITH base AS (
SELECT
  event_date,
  DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_timestamp,
  event_name,
  user_pseudo_id,
  MAX(IF(ep.key = 'firebase_screen', ep.value.string_value, NULL)) AS firebase_screen
FROM advanced.app_logs
CROSS JOIN UNNEST(event_params) AS ep
-- WHERE
--   event_date = '2022-08-18'
--   AND user_pseudo_id = '5464646449.4088767327'
GROUP BY ALL
), diff_funnel_time AS (
SELECT
  *,
  DATETIME_DIFF(event_timestamp, prev_event_timestamp, SECOND) AS second_diff
FROM (
  SELECT
    *,
    LAG(event_timestamp, 1) OVER(PARTITION BY firebase_screen ORDER BY event_timestamp) AS prev_event_timestamp
  FROM base
  )
)

, funnel_start AS (
  SELECT
    *,
    CASE
      WHEN second_diff IS NULL THEN 1
      WHEN rn > 1 AND second_diff >= 20 THEN 1
      ELSE 0
    END AS funnel_per_session_start
  FROM (
    SELECT
      *,
      ROW_NUMBER() OVER(PARTITION BY firebase_screen ORDER BY event_timestamp) AS rn 
    FROM diff_funnel_time
  )
)

SELECT
  firebase_screen,
  SUM(funnel_per_session_start) AS funnel_per_session_start_cnt
FROM funnel_start
GROUP BY ALL
ORDER BY 2 DESC;