빅쿼리 활용법 (3) 전환 이벤트에서 유입 검색어 조회하기

💡 빅쿼리에서 전환 이벤트를 조회할 때, 유입 시점 검색어는 조회되지 않는 경우가 있습니다. 이 때는 유저 수도 아이디 (user-pseudo-id)를 기준으로 유입 이벤트와 전환 이벤트를 조인해야 합니다.

---

1. 빅쿼리 전환 이벤트 조회하기

1) 전환 이벤트 조회 쿼리 실행

지난 포스팅에서 사용한 쿼리를 다시 실행해 보겠습니다.

SELECT 
  FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', TIMESTAMP_MICROS(event_timestamp) + INTERVAL 9 HOUR) AS korean_event_timestamp,
  event_name,
  traffic_source.source, 
  traffic_source.medium,
  traffic_source.name,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'content') AS content,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'term') AS term,
  user_pseudo_id
FROM 
  `project.dataset.table`
WHERE 
  event_name = '전환체크'
ORDER BY 
  korean_event_timestamp DESC
LIMIT 1000;

테이블 영역은 실제 테이블 명을 써야 합니다. 자세한 설명은 지난 포스팅을 참고해 주세요.

• 빅쿼리 활용법 (2) GA4 타임스탬프 한국 시간으로 변환하기

콘텐츠와 검색어가 null로 나오고 있습니다. 유저 수도 ID는 남아 있으니, 이 ID가 유입되었을 시점의 콘텐츠와 키워드를 불러오도록 하겠습니다.

유저 수도 ID가 궁금하시다면 아래 외부 링크를 확인해 주세요.

• user pseudo id란?

2) 빅쿼리 이벤트 조인 쿼리 만들기

다음 쿼리를 실행해 주세요.

WITH key_events AS (
  SELECT 
    user_pseudo_id,
    FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', TIMESTAMP_MICROS(event_timestamp) + INTERVAL 9 HOUR) AS korean_event_timestamp,
    traffic_source.source, 
    traffic_source.medium,
    traffic_source.name,
    event_timestamp AS event_timestamp
  FROM 
    `project.dataset.table.events_*`
  WHERE 
    event_name = '전환체크'
),

session_events AS (
  SELECT
    user_pseudo_id,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'content') AS content,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'term') AS term,
    event_timestamp AS session_event_timestamp
  FROM 
    `project.dataset.table.events_*`
  WHERE 
    event_name = 'session_start'
),

ranked_sessions AS (
  SELECT
    se.user_pseudo_id,
    se.content,
    se.term,
    se.session_event_timestamp,
    ke.event_timestamp AS key_event_timestamp,
    ROW_NUMBER() OVER (PARTITION BY ke.user_pseudo_id, ke.event_timestamp ORDER BY se.session_event_timestamp ASC) AS row_num
  FROM
    key_events ke
  LEFT JOIN
    session_events se
  ON
    ke.user_pseudo_id = se.user_pseudo_id
  AND
    se.session_event_timestamp <= ke.event_timestamp
)

SELECT
  ke.korean_event_timestamp,
  ke.source,
  ke.medium,
  ke.name,
  rs.content,
  rs.term,
  ke.user_pseudo_id,
  'key events' AS event_name
FROM
  key_events ke
LEFT JOIN
  ranked_sessions rs
ON
  ke.user_pseudo_id = rs.user_pseudo_id
AND
  ke.event_timestamp = rs.key_event_timestamp
AND
  rs.row_num = 1
ORDER BY
  ke.korean_event_timestamp DESC
LIMIT 1000;

*테이블 영역은 실제 빅쿼리 테이블 명을 써야 합니다.

**전환체크에는 여러분이 추적하고 싶은 전환 이벤트 명을 써야 합니다.

이번에는 빅쿼리 전환체크 이벤트와 연결되는 콘텐츠와 키워드가 조회되고 있습니다.

그러면 각 쿼리 영역을 분석해 보겠습니다.

2. 이벤트 조인 쿼리 분석하기

1) Common Table Expressions (CTEs)

WITH key_events AS (
  SELECT 
    user_pseudo_id,
    FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', TIMESTAMP_MICROS(event_timestamp) + INTERVAL 9 HOUR) AS korean_event_timestamp,
    traffic_source.source, 
    traffic_source.medium,
    traffic_source.name,
    event_timestamp AS event_timestamp
  FROM 
    `project.dataset.table.events_*`
  WHERE 
    event_name = '전환체크'
),

session_events AS (
  SELECT
    user_pseudo_id,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'content') AS content,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'term') AS term,
    event_timestamp AS session_event_timestamp
  FROM 
    `project.dataset.table.events_*`
  WHERE 
    event_name = 'session_start'
),

ranked_sessions AS (
  SELECT
    se.user_pseudo_id,
    se.content,
    se.term,
    se.session_event_timestamp,
    ke.event_timestamp AS key_event_timestamp,
    ROW_NUMBER() OVER (PARTITION BY ke.user_pseudo_id, ke.event_timestamp ORDER BY se.session_event_timestamp ASC) AS row_num
  FROM
    key_events ke
  LEFT JOIN
    session_events se
  ON
    ke.user_pseudo_id = se.user_pseudo_id
  AND
    se.session_event_timestamp <= ke.event_timestamp
)

key_events:

• events_*라는 패턴을 가진 여러 테이블에서 데이터를 쿼리합니다.
• 이벤트 이름이 ‘전환체크’인 데이터만 추출합니다.
• user_pseudo_id: user_pseudo_id를 선택합니다.
• FORMAT_TIMESTAMP: 타임스탬프를 한국 시간대로 변환합니다.
• traffic_source: 소스, 매체, 이름(캠페인)을 선택합니다.
• event_timestamp: 이벤트 타임스탬프를 선택합니다.

session_events:

• events_*라는 패턴을 가진 여러 테이블에서 데이터를 쿼리합니다.
• 이벤트 이름이 ‘session_start’인 데이터만 추출합니다.
• user_pseudo_id: user_pseudo_id를 선택합니다.
• content: 콘텐츠 값을 선택합니다.
• term: 검색어 값을 선택합니다.
• event_timestamp: 세션 이벤트 타임스탬프를 선택합니다.

ranked_sessions:

• key_events와 session_events CTEs를 조인하여 세션 이벤트가 전환 이벤트보다 이전인 순서로 랭크를 매깁니다.
• user_pseudo_id: user_pseudo_id를 선택합니다.
• content: 콘텐츠 값을 선택합니다.
• term: 검색어 값을 선택합니다.
• session_event_timestamp: 세션 이벤트 타임스탬프를 선택합니다.
• key_event_timestamp: 전환 이벤트 타임스탬프를 선택합니다.
• ROW_NUMBER(): 각 사용자와 전환 이벤트 타임스탬프를 기준으로 세션 이벤트 순위를 매깁니다.

2) Main Query

SELECT
  ke.korean_event_timestamp,
  ke.source,
  ke.medium,
  ke.name,
  rs.content,
  rs.term,
  ke.user_pseudo_id,
  'key events' AS event_name
FROM
  key_events ke
LEFT JOIN
  ranked_sessions rs
ON
  ke.user_pseudo_id = rs.user_pseudo_id
AND
  ke.event_timestamp = rs.key_event_timestamp
AND
  rs.row_num = 1
ORDER BY
  ke.korean_event_timestamp DESC
LIMIT 1000;

SELECT 절:

• ke.korean_event_timestamp: 한국 시간대로 변환된 전환 이벤트 타임스탬프를 선택합니다.
• ke.source, ke.medium, ke.name: 소스, 매체, 이름(캠페인)을 선택합니다.
• rs.content, rs.term: 세션 이벤트의 콘텐츠와 검색어 값을 선택합니다.
• ke.user_pseudo_id: user_pseudo_id를 선택합니다.
• ‘key events’ AS event_name: 이벤트 이름을 ‘key events’로 지정합니다.

FROM 절:

• key_events CTE와 ranked_sessions CTE를 조인합니다.
• ON 절에서 user_pseudo_id와 전환 이벤트 타임스탬프를 기준으로 조인합니다.
• rs.row_num = 1: 가장 빠른 세션 이벤트만 선택합니다. (세션 이벤트가 여러개인 경우를 제외하기 위해서)

ORDER BY 절:

• ke.korean_event_timestamp DESC: 한국 시간대로 변환된 전환 이벤트 타임스탬프를 내림차순으로 정렬합니다.

LIMIT 절:

• 최대 1000개의 결과만 반환합니다.

마무리

이번 포스팅에서는 빅쿼리(Big Query)에서 유저 수도 ID (user pseudo id)를 기준으로 전환 이벤트와 세션 이벤트를 조인해서 유입 당시 콘텐츠와 키워드를 추적하는 방식을 알아보았습니다.

감사합니다.

• 빅쿼리 활용법 (2) GA4 타임스탬프 한국 시간으로 변환하기
• GA4 내 사이트 유입 네이버 블로그와 검색어 추적하기
• 빅쿼리 활용법 (1) 초보 마케터를 위한 GA4와 빅쿼리
• GTM 데이터 레이어 구매 전환 측정 – 전자상거래
• GA4 구글 애드센스(Google AdSense) 무효 클릭 신고 자료 수집