GA4 BigQuery

https://support.google.com/analytics/answer/9267735?hl=ko

[GA4] 추천 이벤트 - 애널리틱스 고객센터

- SQL 유저와 세션 수 추출해보기 (distinct, group by, 서브쿼리)

SELECT COUNT(user_pseudo_id)
FROM  
(
  SELECT user_pseudo_id, count(user_pseudo_id)
  FROM `{project_name}.{dataset_name}.{table_name}`
  GROUP BY user_pseudo_id
)

 

WITH A AS
(
  SELECT user_pseudo_id, count(user_pseudo_id)
  FROM `{project_name}.{dataset_name}.{table_name}`
  GROUP BY user_pseudo_id
)
SELECT count(A.user_pseudo_id) FROM A

 

 

- Unnest 이해하기

 

GA4 수집 데이터를 보면, 사진과 같이, 필드 아래 서브필드가 존재하는 칼럼들이 있다.

privacy_info의 경우 NULLABLE이고 event_params, user_properties의 경우 REPEATED 타입을 가진다.

두 타입의 차이는 아래와 같은 차이가 있다.

 

일반적으로는 한 행 안에 데이터가 나열되어있을 것이라고 기대한다.

하나의 event_name안에 여러개의 parameter를 수집하게 된다. GA4에서는 사용자 지정 커스텀 파라미터를 추가하여

데이터를 수집할 수 있는데, BigQuery 테이블에서 그에 대한 모든 필드를 만들어 준비해 둘 수 없기 때문에,

배열 형태로 params에 대한 key와 value를 저장하게 된다.

parameter가 들어와도 유연하게 처리할 수 있다.

 

서브필드를 가지는 칼럼들 중, privacy_info처럼 NULLABLE 타입의 경우 privacy_info.analytics_storage 형식으로

해당 데이터만 추출이 가능하다.

하지만 event_params 칼럼과 같은 REPEATED 타입의 경우, 데이터들이 배열 형태로 저장되어있기 때문에 이 값들을 UNNEST 명령어로 풀어준 후, 데이터를 추출해야 한다.

그 방법에 대해 알아보자.

 

REPEATED 데이터는 UNNEST라는 명령어를 사용해 테이블을 가공할 수 있는데,

이때 나올 수 있는 테이블의 형태는 아래와 같이 2가지로 나뉜다.

 

우선 UNNEST를 적용하지 않은 테이블의 형태는 아래와 같음을 확인하자.

SELECT
  event_name, event_params
FROM `{project_name}.{dataset_name}.{table_name}`
WHERE event_name = 'view_cart'

 

 

1) 기존 테이블에서 파라미터의 개수에 맞게 나머지 데이터의 개수를 늘려주는 형식이다.

페이지뷰의 개수가 늘어나게 된다.

SELECT
  event_name, param
FROM `{project_name}.{dataset_name}.{table_name}`, UNNEST(event_params) AS param
WHERE event_name = 'view_cart'

 

2) parameter를 column으로 변경하는 형식이다. 1)번처럼 데이터의 수가 뻥튀기되는 경우가 없다.

event_params 아래에 있는 여러 key값들을 칼럼으로 펼쳐주는 것이다.

SELECT
  event_name, 
  (select value.string_value from unnest(event_params) where key='firebase_screen_class') AS fb_screen_class,
  (select value.int_value from unnest(event_params) where key='firebase_screen_id') AS fb_screen_id
FROM `{project_name}.{dataset_name}.{table_name}`
WHERE event_name = 'view_cart'

 

 

 

- SQL로 Ecommerce 데이터 활용하기

예제로 각 상품별 총 수익을 계산하는 쿼리를 하나 더 작성해보자.

이때, item은 REPEATED 타입이므로, item을 UNNEST 명령어로 풀어준 후, 각 item의 서브필드들을 가져와야 한다.

SELECT
  item.item_name, sum(item.price * if(item.quantity is not null, item.quantity, 1)) AS total_price
FROM `{project_name}.{dataset_name}.{table_name}`, unnest(items) as item
WHERE event_name = 'view_cart'
group by item.item_name

 

 

- 소스 매체 등 접속정보 추출하기

GA4에서 추출한 event_timestamp 값을 아래 unix timestamp에 찍어보면 시간(년/월/일/시/분/초)을 확인할 수 있다.

https://www.unixtimestamp.com/

Unix Time Stamp - Epoch Converter

쿼리문에서는 TIMESTAMP_MICROS 함수를 사용하면, event_timestamp를 사람이 이해할 수 있는 시간으로

변환할 수 있다.

SELECT EXTRACT(HOUR FROM DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul')) as HOUR
, count(DISTINCT user_pseudo_id) 
FROM `{project_name}.{dataset_name}.{table_name}`
GROUP BY HOUR

 

 

시간별 유저 수와 세션 수를 조회하는 쿼리

WITH users as (
  SELECT EXTRACT(HOUR FROM DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul')) as     Hour, count (distinct user_pseudo_id) as users
  FROM `{project_name}.{dataset_name}.{table_name}`
  GROUP BY Hour
), sessions as (
  SELECT
  EXTRACT (HOUR FROM DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul')) as Hour,
  count(distinct concat(user_pseudo_id, CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key='ga_session_id') AS STRING))) as sessions 
  FROM `{project_name}.{dataset_name}.{table_name}`
  GROUP BY Hour

)
SELECT users.Hour, * EXCEPT(Hour) FROM users JOIN sessions ON users.Hour = sessions.Hour

 

 

- 소스 매체 등 접속정보 추출하기

* event_params

현 세션의 접속 정보

* collected_traffic_source

현 세션의 접속 정보

* session_traffic_source_last_click

 

 

평균 체류시간 구하는 쿼리

step_1) user_pseudo_id, session_id, event_timestamp에 대해 event_timestamp를 기준으로 내림차순으로 정렬한다.
step_2) step_1의 테이블에서 동일한 user_pseudo_id, session_id 기준으로 event_timestamp들을 배열로 묶는다.

step_3) step_2 테이블에서 event_timestamp 배열 안에 있는 최솟값, 최댓값을 분리해 저장한다.

step_4) event_timestamp의 최댓값과 최솟값의 diff 값을 1000000으로 나눠 체류시간을 구한다.

최종적으로, step_4 테이블에서 구한 체류시간에 대해 평균을 구해 전체 평균 체류시간을 구한다.

WITH step_1 AS (
  SELECT 
  user_pseudo_id,
  (
    SELECT value.int_value FROM UNNEST(event_params) WHERE key='ga_session_id') as session_id,
    event_timestamp
    FROM `{project_name}.{dataset_name}.{table_name}`
    ORDER BY event_timestamp
  ), step_2 AS (
    SELECT 
    user_pseudo_id,
    session_id,
    ARRAY_AGG(
      event_timestamp
    ) AS event_timestamp
    FROM step_1
    GROUP BY user_pseudo_id, session_id
  ), step_3 AS (
    SELECT
    user_pseudo_id,
    session_id,
    event_timestamp[SAFE_OFFSET(0)] AS first_event_timestamp,
    event_timestamp[SAFE_OFFSET(ARRAY_LENGTH(event_timestamp) - 1)] AS last_event_timestamp
    FROM step_2
  ), step_4 AS (
    SELECT
    user_pseudo_id,
    session_id,
    (last_event_timestamp - first_event_timestamp) / 1000000 AS session_time
    FROM step_3
)
SELECT 
  AVG(session_time) as avg_session_time
FROM step_4 
WHERE session_time > 0