빅쿼리 윈도우 함수 LEAD와 LAG

GA4 데이터를 사용하여 특정 페이지를 기준으로 사용자가 이전에 방문했던 페이지와 다음으로 이동한 페이지를 분석할 때는 BigQuery의 윈도우 함수(Window function)인 LEAD()와 LAG()를 사용하면 편리합니다.

-- GA4 BigQuery 데이터에서 특정 페이지 기준 이전/다음 페이지 분석하기
WITH user_pageviews AS (
  SELECT
    user_pseudo_id,
    event_timestamp,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_location
  FROM `your_project.analytics_123456789.events_*`
  WHERE event_name = 'page_view'
),

ordered_pageviews AS (
  SELECT
    user_pseudo_id,
    TIMESTAMP_MICROS(event_timestamp) AS event_time,
    page_location,
    -- 이전 페이지
    LAG(page_location) OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp) AS prev_page,
    -- 다음 페이지
    LEAD(page_location) OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp) AS next_page
  FROM user_pageviews
)

SELECT
  user_pseudo_id,
  event_time,
  page_location,
  prev_page,
  next_page
FROM ordered_pageviews
WHERE page_location = 'https://yourwebsite.com/specific-page-url'
ORDER BY event_time DESC
LIMIT 100;

✅ 1. 데이터 준비 (user_pageviews CTE)

• GA4 이벤트 데이터(events_*)에서 페이지뷰(page_view) 이벤트만 추출합니다.
• 페이지 URL을 가져오기 위해 event_params에서 'page_location'을 뽑아냅니다.

✅ 2. 순서 및 이전/다음 페이지 지정 (ordered_pageviews CTE)

• user_pseudo_id (사용자 구분 ID)로 파티션을 나누어 각 사용자의 페이지뷰 순서를 정리합니다.
• 시간 순서로 정렬하여, 이전 페이지(LAG)와 다음 페이지(LEAD)를 구합니다.

윈도우 함수 설명

• LAG(column) : 현재 행을 기준으로 이전 행의 컬럼 값을 가져옵니다.
• LEAD(column) : 현재 행을 기준으로 다음 행의 컬럼 값을 가져옵니다.
• PARTITION BY user_pseudo_id : 각 사용자 별로 윈도우(분석의 범위)를 나눕니다.
• ORDER BY event_timestamp : 사용자의 페이지뷰를 시간 순서대로 정렬하여 이전/다음 페이지를 확인합니다.

✅ 3. 특정 페이지 기준으로 데이터 필터링

• 마지막 쿼리에서 분석 대상인 특정 페이지(https://yourwebsite.com/specific-page-url)만 추출하여 확인합니다.

✅ 4. 최종 결과 확인

• 특정 페이지 방문 시, 사용자의 직전 페이지(prev_page)와 다음 이동 페이지(next_page)를 함께 볼 수 있습니다.
• LIMIT 100으로 빠르게 데이터를 확인할 수 있습니다.

이 쿼리를 활용하면 사용자가 특정 페이지를 방문하기 전에 주로 어디서 오는지, 또 방문 이후에는 어떤 페이지로 이동하는지를 쉽게 분석할 수 있습니다.