GA4 BigQuery vs UA BigQuery: 세션수&사용자수

GA4와 UA(Universal Analytics)는 데이터 구조와 처리 방식이 달라 BigQuery에서 사용하는 쿼리도 큰 차이가 있어요.

UA BigQuery

1. 기본 데이터 구조:
   • 테이블에서 각각의 row는 Hit(조회/이벤트) 단위.
   • 각 히트가 session_id와 연결되어 있음.
2. 쿼리 작성:
   • 특정 데이터를 조회하려면 hits 배열을 처리해야 함.
   • 세션 데이터는 상대적으로 간단하지만, 이벤트 데이터는 추가적인 처리 필요.

---

GA4 BigQuery

1. 기본 데이터 구조:
   • 테이블에서 각각의 row는 이벤트(event) 단위.
   • event_name과 event_params를 사용해 원하는 이벤트를 조회.
   • 세션 및 사용자 관련 데이터는 user_pseudo_id와 session_id를 통해 구분.
2. 쿼리 작성:
   • 더 유연한 이벤트 기반 구조.
   • 하지만 unnest로 배열 데이터를 펼쳐서 사용하는 경우가 많음.

1️⃣ 세션 수와 사용자 수 조회

UA BigQuery 버전

SELECT
  COUNT(DISTINCT CONCAT(fullVisitorId, CAST(visitId AS STRING))) AS total_sessions,
  COUNT(DISTINCT fullVisitorId) AS total_users
FROM
  `your_project_id.your_dataset_name.ga_sessions_*`
WHERE
  _TABLE_SUFFIX BETWEEN '20250101' AND '20250131'; -- 원하는 날짜 범위

• fullVisitorId: 사용자 식별자.
• visitId: 세션 식별자.
• 두 값을 합쳐 세션을 고유하게 구분합니다.

---

GA4 BigQuery 버전

SELECT
  COUNT(DISTINCT session_id) AS total_sessions,
  COUNT(DISTINCT user_pseudo_id) AS total_users
FROM
  `your_project_id.your_dataset_name.events_*`
WHERE
  _TABLE_SUFFIX BETWEEN '20250101' AND '20250131'; -- 원하는 날짜 범위

• user_pseudo_id: 사용자 식별자.
• session_id: 세션 식별자.
• GA4에서는 이미 session_id 필드가 제공됩니다.

---

2️⃣ 이탈 페이지 조회

UA BigQuery 버전

WITH last_hit_per_session AS (
  SELECT
    hits.page.pagePath AS exit_page,
    CONCAT(fullVisitorId, CAST(visitId AS STRING)) AS session_id,
    ROW_NUMBER() OVER (PARTITION BY CONCAT(fullVisitorId, CAST(visitId AS STRING)) ORDER BY hits.time DESC) AS rn
  FROM
    `your_project_id.your_dataset_name.ga_sessions_*`,
    UNNEST(hits) AS hits
  WHERE
    hits.type = 'PAGE'
)
SELECT
  exit_page,
  COUNT(*) AS exit_count
FROM
  last_hit_per_session
WHERE
  rn = 1 -- 세션의 마지막 히트만 선택
GROUP BY
  exit_page
ORDER BY
  exit_count DESC;

• hits: UA 데이터에서 페이지 정보는 hits 배열에 저장.
• 세션의 마지막 페이지(ROW_NUMBER)를 추출해 이탈 페이지를 구합니다.

---

GA4 BigQuery 버전

WITH last_event_per_session AS (
  SELECT
    event_params.value.string_value AS exit_page,
    session_id,
    ROW_NUMBER() OVER (PARTITION BY session_id ORDER BY event_timestamp DESC) AS rn
  FROM
    `your_project_id.your_dataset_name.events_*`,
    UNNEST(event_params) AS event_params
  WHERE
    event_name = 'page_view' -- 페이지 조회 이벤트
    AND event_params.key = 'page_location'
)
SELECT
  exit_page,
  COUNT(*) AS exit_count
FROM
  last_event_per_session
WHERE
  rn = 1 -- 세션의 마지막 이벤트만 선택
GROUP BY
  exit_page
ORDER BY
  exit_count DESC;

• event_params: GA4에서는 페이지 정보가 이벤트 매개변수로 제공됩니다.
• page_location은 URL 경로를 나타냅니다.

---

요약

• UA는 데이터가 히트 중심이라 UNNEST(hits)를 사용해야 하고,
  세션 정보를 **fullVisitorId와 visitId**로 결합해 처리합니다.
• GA4는 데이터가 이벤트 중심으로 event_params를 활용하며,
  session_id가 기본적으로 제공되어 편리합니다.