Google 애널리틱스 이벤트 데이터 내보내기의 기본 쿼리

이 페이지의 샘플 쿼리는 Google 애널리틱스용 BigQuery 이벤트 내보내기 데이터에 적용됩니다.

샘플 데이터 세트 대신 데이터 세트 쿼리

여기에 표시된 모든 쿼리는 샘플 데이터 세트를 사용하며 유효한 결과를 생성합니다. 자체 Google 애널리틱스 속성의 BigQuery 이벤트 내보내기 데이터를 사용하려면 각 쿼리에서 -- Replace table 댓글을 찾아 샘플 테이블을 대체하세요. 데이터 세트에서 테이블 이름을 복사하려면 다음 단계를 따르세요.

  1. BigQuery UI로 이동하여 데이터 세트가 포함된 프로젝트를 선택합니다.
  2. 탐색기 에서 테이블을 찾습니다.
  3. 테이블 오른쪽에 있는 세로 점 3개 아이콘을 클릭한 다음 ID 복사 를 클릭합니다.
  4. 쿼리에서 샘플 테이블 대신 테이블 이름을 붙여넣습니다.
  5. 테이블의 날짜 부분을 *로 바꿉니다.

예를 들어 ID 복사 에서 BigQuery 테이블 이름 my-first-gcp-project:analytics_28239234.events_20240718을 복사한 경우 다음 코드를

  -- Replace table
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`

다음 코드로 교체합니다.

  -- Replace table
  `my-first-gcp-project.analytics_28239234.events_*`

특정 기간 쿼리하기

BigQuery 이벤트 내보내기 데이터 세트에서 특정 기간을 쿼리하려면 쿼리의 WHERE 절에 _TABLE_CAMPAIGNS 의사 열을 사용하세요. 자세한 내용은 _TABLE_DIFM을 사용하여 선택한 테이블 필터링을 참고하세요.

예를 들어 다음 쿼리는 특정 기간(일) 및 선택한 이벤트의 날짜 및 이벤트 이름별로 고유 이벤트 수를 계산합니다.

-- Example: Query a specific date range for selected events.
--
-- Counts unique events by date and by event name for a specifc period of days and
-- selected events(page_view, session_start, and purchase).

SELECT
  event_date,
  event_name,
  COUNT(*) AS event_count
FROM
  -- Replace table name.
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE
  event_name IN ('page_view', 'session_start', 'purchase')
  -- Replace date range.
  AND _TABLE_SUFFIX BETWEEN '20201201' AND '20201202'
GROUP BY 1, 2;

사용자 수, 신규 사용자, 재사용자

  • 총 활성 사용자 수를 가져오려면 고유 user_id 값의 수를 계산합니다. 하지만 Google 애널리틱스 클라이언트에서 각 조회와 함께 user_id를 전송하지 않거나 확실하지 않은 경우 고유 user_pseudo_id 값의 수를 계산합니다.
  • 신규 사용자의 경우 event_name이 다음과 같은 고유 사용자 수를 계산합니다.
  • 재사용자의 경우 기간 중에 활동이 있었지만 기간 중에 첫 번째 세션을 시작하거나 앱을 처음으로 실행하지 않은 사용자 수를 계산합니다 (활성 사용자 수 - 신규 사용자 수).
-- Example: Get 'Total User' (Active User), 'New User', and 'Returning User' counts.

WITH
  UserInfo AS (
    SELECT
      user_pseudo_id,
      MAX(IF(event_name IN ('first_visit', 'first_open'), 1, 0)) AS is_new_user
    -- Replace table name.
    FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
    -- Replace date range.
    WHERE _TABLE_SUFFIX BETWEEN '20201101' AND '20201130'
    GROUP BY 1
  )
SELECT
  COUNT(*) AS active_users,
  SUM(is_new_user) AS new_users,
  COUNT(*) - SUM(is_new_user) AS returning_users
FROM UserInfo;

구매자 유형당 평균 거래수

다음 쿼리는 구매자당 평균 거래 수를 보여줍니다.

-- Example: Average number of transactions per purchaser.

SELECT
  COUNT(*) / COUNT(DISTINCT user_pseudo_id) AS avg_transaction_per_purchaser
FROM
  -- Replace table name.
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE
  event_name IN ('in_app_purchase', 'purchase')
  -- Replace date range.
  AND _TABLE_SUFFIX BETWEEN '20201201' AND '20201231';

특정 이벤트 이름의 값

다음 쿼리는 모든 purchase 이벤트 및 연결된 이벤트 매개변수 값의 event_timestamp를 보여줍니다.

-- Example: Query values for a specific event name.
--
-- Queries the individual timestamps and values for all 'purchase' events.

SELECT
  event_timestamp,
  (
    SELECT COALESCE(value.int_value, value.float_value, value.double_value)
    FROM UNNEST(event_params)
    WHERE key = 'value'
  ) AS event_value
FROM
  -- Replace table name.
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE
  event_name = 'purchase'
  -- Replace date range.
  AND _TABLE_SUFFIX BETWEEN '20201201' AND '20201202';

목록 대신 이벤트 매개변수 값의 합계를 표시하도록 이전 쿼리를 수정할 수 있습니다.

-- Example: Query total value for a specific event name.
--
-- Queries the total event value for all 'purchase' events.

SELECT
  SUM(
    (
      SELECT COALESCE(value.int_value, value.float_value, value.double_value)
      FROM UNNEST(event_params)
      WHERE key = 'value'
    ))
    AS event_value
FROM
  -- Replace table name.
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE
  event_name = 'purchase'
  -- Replace date range.
  AND _TABLE_SUFFIX BETWEEN '20201201' AND '20201202';

장바구니에 추가된 상위 10개 상품

다음 쿼리는 가장 많은 사용자 수를 기준으로 장바구니에 추가된 상위 10개 상품을 보여줍니다.

-- Example: Top 10 items added to cart by most users.

SELECT
  item_id,
  item_name,
  COUNT(DISTINCT user_pseudo_id) AS user_count
FROM
  -- Replace table name.
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`, UNNEST(items)
WHERE
  -- Replace date range.
  _TABLE_SUFFIX BETWEEN '20201101' AND '20210131'
  AND event_name IN ('add_to_cart')
GROUP BY
  1, 2
ORDER BY
  user_count DESC
LIMIT 10;

구매자 유형별 평균 제품 페이지 조회수(구매자 및 비구매자)

다음 쿼리는 사용자의 평균 페이지 조회 구매자 유형(구매자 및 비구매자) 수를 보여줍니다.

-- Example: Average number of pageviews by purchaser type.

WITH
  UserInfo AS (
    SELECT
      user_pseudo_id,
      COUNTIF(event_name = 'page_view') AS page_view_count,
      COUNTIF(event_name IN ('in_app_purchase', 'purchase')) AS purchase_event_count
    -- Replace table name.
    FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
    -- Replace date range.
    WHERE _TABLE_SUFFIX BETWEEN '20201201' AND '20201202'
    GROUP BY 1
  )
SELECT
  (purchase_event_count > 0) AS purchaser,
  COUNT(*) AS user_count,
  SUM(page_view_count) AS total_page_views,
  SUM(page_view_count) / COUNT(*) AS avg_page_views,
FROM UserInfo
GROUP BY 1;

페이지 조회의 시퀀스

이 쿼리는 각 사용자가 조회한 페이지의 시퀀스를 보여줍니다. 이 쿼리는 이벤트가 동일한 일괄 처리로 전송되더라도 사용자를 위해 발생한 순서대로 이벤트가 나열되도록 다음 필드를 사용하여 결과를 정렬합니다.

  • user_pseudo_id
  • user_id
  • batch_page_id
  • batch_ordering_id
  • batch_event_index

샘플에서는 결과를 page_view 이벤트로만 제한하지만 event_name의 WHERE 절 조건을 삭제하여 동일한 ORDER BY 절을 사용하여 모든 이벤트를 올바르게 정렬할 수 있습니다.

또한 이 쿼리는 사용자 정의 함수 GetParamString 및 GetParamInt를 사용하여 중복을 줄이고 쿼리를 더 쉽게 이해하고 유지보수하는 방법을 보여줍니다.

-- Example: Sequence of pageviews.

/** Temporary function to retrieve the string_value of an event parameter by event name. */
CREATE TEMP FUNCTION GetParamString(event_params ANY TYPE, param_name STRING)
AS ((SELECT ANY_VALUE(value.string_value) FROM UNNEST(event_params) WHERE key = param_name));

/** Temporary function to retrieve the int_value of an event parameter by event name. */
CREATE TEMP FUNCTION GetParamInt(event_params ANY TYPE, param_name STRING)
AS ((SELECT ANY_VALUE(value.int_value) FROM UNNEST(event_params) WHERE key = param_name));

SELECT
  user_pseudo_id,
  user_id,
  batch_page_id,
  batch_ordering_id,
  batch_event_index,
  event_name,
  GetParamInt(event_params, 'ga_session_id') as ga_session_id,
  GetParamString(event_params, 'page_location') as page_location,
  GetParamString(event_params, 'page_title') as page_title,
FROM
  -- Replace table name.
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE
  event_name = 'page_view'
  -- Replace date range.
  AND _TABLE_SUFFIX BETWEEN '20240718' AND '20240731'
ORDER BY
  user_pseudo_id,
  user_id,
  batch_page_id,
  batch_ordering_id,
  batch_event_index;

이벤트 매개변수 목록

다음 쿼리는 데이터 세트에 표시되는 모든 이벤트 매개변수를 나열합니다.

-- Example: List all available event parameters and count their occurrences.

SELECT
  EP.key AS event_param_key,
  COUNT(*) AS occurrences
FROM
  -- Replace table name.
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`, UNNEST(event_params) AS EP
WHERE
  -- Replace date range.
  _TABLE_SUFFIX BETWEEN '20201201' AND '20201202'
GROUP BY
  event_param_key
ORDER BY
  event_param_key ASC;

Google Ads와 조인

Google 애널리틱스 이벤트의 추가 Google Ads 데이터를 가져오려면 Google Ads용 BigQuery Data Transfer Service를 설정한 다음 Google 애널리틱스 이벤트 데이터의 collected_traffic_source.gclid를 Google Ads 전송의 gclid ads_ClickStats_customer_id 필드에 조인합니다.

Google 애널리틱스 이벤트 데이터 내보내기에서는 일별로 표가 생성되지만 Google Ads 전송에서는 고객별로 단일 ads_ClickStats_customer_id 표를 채웁니다.