해결된 질문
24.07.22 18:34 작성
·
114
0
안녕하세요. 저는 이번에 마지막 문제를 응용해서, 퍼널별로 유저들의 새로운 세션 시작 횟수의 총합을 계산해보았습니다.
[결과 그래프]
새로운 세션 시작 기준: 이전 세션 시간 대비 20초 이상이 지나면, 새로운 세션을 활성화했다고 가정 (강의 내용과 동일)
첫번째 강의에서 배운, 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
)
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
)
)
이제, 퍼널별로 세션 시작 여부를 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;
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;
답변 2
0
2024. 07. 23. 17:19
minsubrother님 안녕하세요! 쿼리를 자세히 보면서 어떻게 하셨는지 봤어요. 이렇게 복습하시고 계신 부분 참 좋네요.
시작하기 전에 질문드리고 싶은 부분은 "이 쿼리는 무엇을 알고 싶어 작성했나요?", "그것을 알게 되면 무엇을 할 수 있을까요?"
단순히 쿼리를 뽑기 전에 "어떤 문제를 해결하고 싶어 쿼리를 작성했다!"라는 문제 정의가 필요합니다. 이런 문제 정의가 없으면 결과를 해석할 때 어렵게 됩니다. 그렇기 때문에 문제 정의를 잘 진행하고, 그 후에 쿼리를 작성하는 것이 필요합니다.
현재 <퍼널별로 유저들의 새로운 세션 시작 횟수의 총합을 계산>하겠다고 하셨는데, 이 부분에 대한 정의를 자세히 하고 시작하면 좋습니다.
지금 작성해주신 쿼리를 보면, 퍼널(정확히는 firebase_screen) 별로 첫 접근을 새로운 세션 시작이라고 해주신 것 같아요. 강의에서 말씀드린 것처럼 세션은 일련의 과정이기 때문에 화면 별로 세션이 새롭게 시작된다는 개념은 어색합니다. 각 화면에 처음 접근한 횟수를 구했다고 보는게 더 맞는 표현일 것 같네요. screen_view의 페이지를 COUNT하면 값이 비슷하게 나올 것 같네요.
그리고 diff_funnel_time에서 PARTITION BY에 firebase_screen이 들어가는데, 위에 말씀드린 것처럼 스크린 기반으로 세션 여부를 판단하는 것은 어색합니다. 그렇지만 만약 이게 어떤 의도가 있다고 가정하고 이 부분은 논외로 치면, 여기서 user_pseudo_id를 PARTITION BY에 같이 넣어줘야 합니다. 현재 예시에선 WHERE 조건에 특정 유저를 필터링했기 때문에 이슈가 되진 않지만, PARTITION BY에 user_pseudo_id를 넣지 않으면 다른 유저 중에 같은 화면에 접근한 시간을 가지고 올거에요.
확인하기 위해 다음 쿼리를 작성해보았어요(user_pseudo_id 조건을 주석 처리)
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
WHERE firebase_screen = 'welcome'
ORDER BY event_timestamp
실행하면 다음과 같은 데이터를 볼 수 있습니다. 같은 색으로 네모로 한 것이 같은 값입니다. 근데 user_pseudo_id를 보면 다른 유저인 것을 확인할 수 있지요. 그래서 지금 데이터는 그냥 시간이 제일 빠른 경우나 유저가 바뀐 경우를 COUNT 했을거에요(유저가 바뀌었지만 접속 시간의 간격이 짧은 유저는 제외되었겠지요) 이 데이터는 쿼리를 실수해서 잘못된 데이터가 나왔다고 볼 수 있을 것 같네요
정리하면 다음과 같습니다
쿼리를 작성하기 전에 문제 정의, 이 지표를 토대로 무엇을 할 수 있을지 생각해보기(이 부분은 PM을 위한 데이터 리터러시나 초보자를 위한 BigQuery 입문에서 계속 말씀드리는 부분입니다)
단순히 쿼리를 작성하는 것이 아닌, 쿼리를 작성한 후에 이 데이터를 어떻게 해석할 것인가 생각해보기. 단순히 쿼리만 작성하는 역할이 데이터 분석가가 아니고, 데이터 분석가는 자신이 추출한 데이터를 기반으로 해석을 할 수 있어야 합니다
윈도우 함수에서 PARTITION BY에 user_pseudo_id를 명시해야 같은 유저 내의 데이터를 추출합니다
현재 말씀하신 세션의 의미가 제가 공유드린 의미와 다르게 사용되는 것 같아 이 부분은 항상 쿼리 작성 시작할 때 정의를 작성하는 것을 추천드려요. 회사에서도 정의없이 작성하는 경우가 있는데, 항상 용어에 대한 구체적인 정의부터 작성하는 습관을 가지시는게 좋습니다
쿼리를 다시 작성해보시고, 그 후에 그 데이터를 해석해보는 것도 해보시지요!
0
2024. 07. 23. 02:39
minsubrother님 안녕하세요!
쿼리 작성해주신 것을 보니 잘 학습하셨네요! 관련해서 또 생각해보면 좋은 부분에 대해서 내일 공유드릴게요.
먼저 떠오르는 것 하나는.. "이 퍼널 데이터를 보고 어떤 생각이 드시나요? 이 퍼널 데이터를 해석해주세요. 추가적으로 궁금한 내용이나 가설이 떠오르면 그 부분에 대해서도 같이 작성해주세요"
2024. 07. 24. 02:03
쿼리를 작성하기 전에, 문제 정의 단계가 매우 중요하다는 것을 배웠습니다. 또한 세션은 사용자의 행동을 분석하는데 도움을 주는 일련의 과정이기 때문에, 뚝뚝 끊어서, 화면별로 세션을 정의하는 건 좋지 못하다는 생각이 들었습니다.
또한, 스크린(화면) 기반으로 세션 여부를 판단하는 것을 생각해보니, 인사이트를 주기도 어려울 뿐더러, 문제점이 있다는 것을 생각할 수 있었습니다.
예제의 데이터는 사용자가 대체로 정해진 순서로 화면을 이동하고 있습니다. 그렇기 때문에, 고유 사용자별로 비슷하게 집계가 될 것이고, 스크린 전환을 세션으로 정의를 했기 때문에, 사용자가 어떤 목표를 가지고 앱을 사용하는지에 대한 파악을 어렵게 만든다. 라는 생각이 들었습니다.
다시 데이터를 들여다보고, 문제를 정의하고, 용어에 대한 구체적인 정의를 하는 연습을 해보겠습니다.