[GA4-빅쿼리] 세션 재정의 해보기

구글 애널리틱스에서 제공하는 데이터를 빅쿼리에 연동해보면 사용자고유 아이디와 세션아이디를 제공해준다. 

다음과 같은 여러개의 컬럼을 제공해주는데, 'user_pseudo_id'가 사용자가 이벤트를 발생시켰을때, 발생하는 인스턴스 아이디로 사용자 고유 아이디 값이고, 세션아이디는 컬럼에서 보이지 않는다. 

데이터를 살펴보면

세션아이디값인 ga_session_id 는 event_params.key에 정의되어 있으며 그 값은 

event_params.value.int_value에 존재하고 있다. 

즉.....  ARRAY 와 STRUCT 문법이 사용된 NESTED 한 구조로 존재한다. 우리에게 익숙한 컬럼으로 존재했으면 ga_session_id로 SELECT만 하면 될 일이지만 복잡한 데이터 구조로 상당한 고역이 예상된다 (ㅠㅠ)

* ARRAY /STRUCT /UNNEST 구조 참고

https://cheershennah.tistory.com/245 

[빅쿼리] ARRAY / STRUCT / UNNEST. 정체가 무엇이냐??

물론 UNNEST함수로 Flatten해서도 풀어볼 수 있겠지만 겸사겸사 세션을 재정의 해보려고 한다.

(실제로 세션정의가 되어있지 않은 경우가 많아 세션을 직접 정의해주는 경우도 많다고 함)

기존 구글에서는 세션단위를 기본값 30분으로 잡고 있다고 하니 세션 시간은 30분 단위로 잡고 세션을 재정의 해보자.  

먼저 세션시간은 직전 이벤트가 발생한 시간, 현재 이벤트 발생 시간, 다음 이벤트 발생시간을 기준으로 시간의 차가 30분 이상 날 경우 세션을 종료하고 새로운 새션을 부여하는 것으로 한다. 

 

 

1. 먼저 각 유저 별 이벤트 시간을 알아야 하는데 구글은 미국시간 기준인 UTC 기준 시간을 제공하므로 9시간을 더해준 한국기준 시간 KST 시간을 구한다.

WITH kst AS (
  SELECT user_pseudo_id
  ,event_timestamp
  ,TIMESTAMP_SECONDS(CAST(CAST(event_timestamp as INT64)/1000000 as INT64)) + interval '9' hour as event_timestamp_kst --한국시간계산 
  FROM `devlog-371715.analytics_254657717.events_*`
  WHERE PARSE_DATE('%Y%m%d', _TABLE_SUFFIX) BETWEEN DATE('2022-12-19') AND DATE('2022-12-25')
  GROUP BY user_pseudo_id, event_timestamp
)

2. KST시간 기준 userid, 이벤트 발생시간, 이전이벤트 발생시간, 다음이벤트 발생시간을 구하고 이전부터 현재이벤트 까지, 현재부터 다음 이벤트까지의 시간을 구한다. 또한 세션 재정의를 위한 세션 아이디를 부여하기 위해 row_number를 사용하여 세션 아이디로 정의하였다.

SELECT 
    user_pseudo_id -- user id 
    ,event_timestamp_kst -- 이벤트 발생 시간 
    ,LAG(event_timestamp_kst) OVER(PARTITION BY user_pseudo_id ORDER BY kst.event_timestamp_kst) last_timestamp -- 이전 이벤트 발생 시간 
    ,TIMESTAMP_DIFF(event_timestamp_kst,LAG(event_timestamp_kst) OVER(PARTITION BY user_pseudo_id ORDER BY kst.event_timestamp_kst) , SECOND ) preFromNow -- 이전부터 현재이벤트 발생하기까지 시간 
    ,LEAD(event_timestamp_kst) OVER(PARTITION BY user_pseudo_id ORDER BY kst.event_timestamp_kst) next_timestamp -- 다음 이벤트 발생 시간 
    ,TIMESTAMP_DIFF( LEAD(event_timestamp_kst) OVER(PARTITION BY user_pseudo_id ORDER BY kst.event_timestamp_kst),event_timestamp_kst, SECOND ) nowToNext -- 현재부터 다음이벤트 발생하기까지 시간 
    ,ROW_NUMBER() OVER() AS session_id -- 세션 아이디 부여 

    FROM kst
    ORDER BY  user_pseudo_id, event_timestamp_kst

다음과 같은 결과가 나왔다. 여기서 이전부터 현재 이벤트까지의 시간인 preFromNow의 값이 null이면 이전 이벤트가 존재하지 않는다는 의미이므로 세션 시작을 의미하고, 마찬가지로 현재부터 다음 이벤트까지 시간인 nowFromNext값이 null이면 다음 이벤트가 존재하지 않는다는 의미로 세션 종료를 의미한다.

또한 세션 재정의를 30분 단위로 잡았기 때문에 preFromNow값이 30분(1800초) 이상이면 세션시작, nowFromNext값이 30분 이상이면 세션 종료로 보고, 세션 시작 및 종료값만 남기기 위해 조건을 걸어준다.

 

SELECT *
    ,CASE WHEN preFromNow is null THEN session_id -- 세션 시작인 경우 새 세션 아이디 부여 
          WHEN preFromNow >= 1800 THEN session_id -- 세션 시작인 경우 새 세션 아이디 부여 
          ELSE LAG(session_id,1) OVER(partition by user_pseudo_id ORDER BY event_timestamp_kst) -- 이외 세션 종료인 경우 이전 세션 아이디 부여 
          END as session 
  FROM (
    SELECT 
    user_pseudo_id -- user id 
    ,event_timestamp_kst -- 이벤트 발생 시간 
    ,LAG(event_timestamp_kst) OVER(PARTITION BY user_pseudo_id ORDER BY kst.event_timestamp_kst) last_timestamp -- 이전 이벤트 발생 시간 
    ,TIMESTAMP_DIFF(event_timestamp_kst,LAG(event_timestamp_kst) OVER(PARTITION BY user_pseudo_id ORDER BY kst.event_timestamp_kst) , SECOND ) preFromNow -- 이전부터 현재이벤트 발생하기까지 시간 
    ,LEAD(event_timestamp_kst) OVER(PARTITION BY user_pseudo_id ORDER BY kst.event_timestamp_kst) next_timestamp -- 다음 이벤트 발생 시간 
    ,TIMESTAMP_DIFF( LEAD(event_timestamp_kst) OVER(PARTITION BY user_pseudo_id ORDER BY kst.event_timestamp_kst),event_timestamp_kst, SECOND ) nowToNext -- 현재부터 다음이벤트 발생하기까지 시간 
    ,ROW_NUMBER() OVER() AS session_id -- 이벤트 아이디 부여 

    FROM kst
    ORDER BY  user_pseudo_id, event_timestamp_kst 
     )sub 
     -- 세션 시작 및 종료값만 조건 출력 
     WHERE preFromNow >= 1800 -- 이전부터 현재 이벤트까지 30분 이상. 세션 시작 
       OR nowToNext >= 1800 -- 현재부터 다음이벤트까지 30분 이상. 세션 종료 
       OR preFromNow IS NULL -- 세션 시작 
       OR nowToNext IS NULL -- 세션 종료

결과와 같이 세션 시작 및 종료 값만 남긴 후에, 세션 시작시에는 row_number로 만들었던 새로운 새션 아이디를, 세션 종료시에는 이전 세션에 속하므로 LAG함수를 사용하여 이전 세션 아이디를 그대로 가지고 온다.

 

마지막으로 userid와 재정의한 session으로 그룹핑하여 가장 적은 시간을 세션 시작시간, 가장 늦은 시간을 세션 종료시간으로 정의하고 각 세션의 시작과 종료시간의 차이를 구하여 머문 시간을 계산하여 가장 많이 머문 시간을 구한다.

 

SELECT user_pseudo_id 
      ,session
      ,MIN(event_timestamp_kst) session_start -- 세션 시작 시간 
      ,MAX(event_timestamp_kst) session_end -- 세션 종료 시간 
      ,TIMESTAMP_DIFF(MAX(event_timestamp_kst), MIN(event_timestamp_kst), SECOND ) stay -- 세션유지시간 
FROM (
  
  SELECT *
    ,CASE WHEN preFromNow is null THEN session_id -- 세션 시작인 경우 새 세션 아이디 부여 
          WHEN preFromNow >= 1800 THEN session_id -- 세션 시작인 경우 새 세션 아이디 부여 
          ELSE LAG(session_id,1) OVER(partition by user_pseudo_id ORDER BY event_timestamp_kst) -- 이외 세션 종료인 경우 이전 세션 아이디 부여 
          END as session 
  FROM (
    SELECT 
    user_pseudo_id -- user id 
    ,event_timestamp_kst -- 이벤트 발생 시간 
    ,LAG(event_timestamp_kst) OVER(PARTITION BY user_pseudo_id ORDER BY kst.event_timestamp_kst) last_timestamp -- 이전 이벤트 발생 시간 
    ,TIMESTAMP_DIFF(event_timestamp_kst,LAG(event_timestamp_kst) OVER(PARTITION BY user_pseudo_id ORDER BY kst.event_timestamp_kst) , SECOND ) preFromNow -- 이전부터 현재이벤트 발생하기까지 시간 
    ,LEAD(event_timestamp_kst) OVER(PARTITION BY user_pseudo_id ORDER BY kst.event_timestamp_kst) next_timestamp -- 다음 이벤트 발생 시간 
    ,TIMESTAMP_DIFF( LEAD(event_timestamp_kst) OVER(PARTITION BY user_pseudo_id ORDER BY kst.event_timestamp_kst),event_timestamp_kst, SECOND ) nowToNext -- 현재부터 다음이벤트 발생하기까지 시간 
    ,ROW_NUMBER() OVER() AS session_id -- 이벤트 아이디 부여 

    FROM kst
    ORDER BY  user_pseudo_id, event_timestamp_kst 
     )sub 
     -- 세션 시작 및 종료값만 조건 출력 
     WHERE preFromNow >= 1800 -- 이전부터 현재 이벤트까지 30분 이상. 세션 시작 
       OR nowToNext >= 1800 -- 현재부터 다음이벤트까지 30분 이상. 세션 종료 
       OR preFromNow IS NULL -- 세션 시작 
       OR nowToNext IS NULL -- 세션 종료 
  )se
  GROUP BY se.user_pseudo_id, se.session
  ORDER BY stay DESC

다음과 같이 각 유저 별 재정의한 새션이 부여되었고, 세션 시작 및 종료 시간을 구했다.

그리고 세션 당 유지시간을 추가적으로 구해보았다. 

결과는 굉장히 의외였다 ?? 

블로그 특성상 길어야 30분 이내로 세션이 끊길 것이라 예상했는데 약 3000초, 즉 50분 이상을 한 세션에서 유지되는 계정도 존재했다.

이쯤되니 총 3485초로 가장 오래 머문 분은 과연 어떤 페이지에서 머물렀을까? 하는 의문이 생긴다.

유저아이디를 기준으로 어떤 페이지를 봤는지 살펴보자. 

SELECT user_pseudo_id
  ,TIMESTAMP_SECONDS(CAST(CAST(event_timestamp as INT64)/1000000 as INT64)) + interval '9' hour as kst_date 
  ,event_name
  ,event_params.key 
  ,event_params.value.string_value --페이지명 
 FROM `devlog-371715.analytics_254657717.events_*` 
   ,UNNEST(event_params) AS event_params
  WHERE PARSE_DATE('%Y%m%d', _TABLE_SUFFIX) BETWEEN DATE('2022-12-19') AND DATE('2022-12-25')
  AND key = 'page_title'
  AND user_pseudo_id = '999769459.1665271577'

ㅎㅎㅎㅎㅎㅎ!!! 의문이 풀렸다. 

일반적으로 궁금한것을 검색하고 해답을 얻어가는 식의 포스팅이 아닌... 

인텔리 학생 계정 무료 라이센스 사용법에 대한 포스팅이기 때문이었다!! 

인텔리제이는 유료 개발 IDE인데, 학생을 대상으로 무료라이센스를 발급해주고 일정 기간이 지나면 연장도 해야한다. 

매번 사용할때마다 까먹어서 일일이 검색을 하는게 귀찮아서 한번 정리 해서 포스팅 해놓았는데 

그글을 보고 따라하시면서 라이센스를 발급 받으신 듯 하다. 

 

설마 2순위로 약 3324초 머문 세션도 같은 글일지 궁금해서 유저아이디만 바꿔서 쿼리해봤다. 

오호 .... 다행히(?) 스토리지에 관한 다른 포스팅을 보셨다.

해당 포스팅은 3가지의 스토리지를 비교분석해놓은 글이 없어서 직접 검색하며 분석하는 글을 썼던 기억이 난다. 

 

상대적으로 공을 들여 쓴 글들에 오래 머물러주신걸 보고 뿌듯했다 ㅋㅋㅋㅋㅋ 

그간 티스토리에서 제공해주는 통계수치만을 보면서 실데이터가 굉장히 궁금했는데 직접 데이터를 까보고 추적해보았다.

유잼 :)