[BigQuery] 신규사용자가 첫 접속날짜에 남긴 이벤트만 필터링

이글에서는 user_pseudo_id 또는 user_id를 활용해서 신규사용자가 처음 접속한 당일에 발생시킨 이벤트들만 필터링하는 방법에 대해서 알아볼 것이다.

 

user_id를 수집하고 있지 않은 경우, 사용자를 식별할 수 있는 user_pesuo_id를 사용해야 한다.(user_id 관련 글) 이를 사용하여 사용자가 가장 처음 사이트를 접속한 날짜를 필터링하는 방법을 알아보자. 

 

SELECT user_pseudo_id, event_date
FROM `프로젝트ID.events_*`
WHERE  event_name = 'first_visit'

 

 

위와 같이 쿼리를 작성하면, user_pseudo_id를 기준으로 'first_visit' 이벤트를 발생시킨 날짜만 필터링되어서 출력된다.('first_visit'는 유저가 사이트를 처음 접속할 때 발생하는 이벤트) 만약 신규 유저가 처음 접속한 당일에 발생한 이벤트만 출력하고 싶다면 어떻게 해야 할까? 위 결과를 활용해서 where절에 조건을 걸어주어야 한다.

 

with FirstVisitUsers AS(
    SELECT user_pseudo_id, event_date
    FROM `프로젝트ID.events_*`
    WHERE  event_name = 'first_visit'
)


SELECT user_pseudo_id, event_date, event_name
FROM `프로젝트ID.events_*` p
WHERE EXISTS (
  SELECT 1
  FROM FirstVisitUsers fvu
  WHERE p.user_pseudo_id = fvu.user_pseudo_id
    AND p.event_date = fvu.event_date
)

 

위와 같이 쿼리를 작성해면, 원본 테이블과 First_VisitUsers 테이블과 매칭을 시켜서 user_pseudo_id와 event_date가 동시에 매칭되는 경우의 데이터들만 가져오게 된다. 즉, 신규 유저가 처음 접속한 당일에 남긴 이벤트들이 전부 출력되는 것이다.

 

아래와 같이 쿼리를 작성하면, 'first_visit' 이벤트를 2번 이상 발생시킨 사용자가 얼마나 있는지도 확인할 수 있다. first_visit 이벤트를 어떻게 여러 번 발생시키는 경우가 있는지 알아보는 것은 좀 더 연구가 필요할 것 같다.

 

SELECT user_pseudo_id, COUNT(*) as c
FROM ( 
    SELECT user_pseudo_id, event_date
    FROM `프로젝트ID.events_*`
    WHERE  event_name = 'first_visit'
    )
GROUP BY user_pseudo_id
ORDER BY c DESC;