Truy vấn cơ bản để xuất dữ liệu sự kiện Google Analytics

Các truy vấn mẫu trong trang này áp dụng cho dữ liệu xuất sự kiện BigQuery cho Google Analytics.

Truy vấn tập dữ liệu của bạn thay vì tập dữ liệu mẫu

Trừ phi có ghi chú khác, tất cả truy vấn được liệt kê ở đây đều sử dụng tập dữ liệu mẫu và sẽ tạo ra kết quả hợp lệ. Cách sử dụng BigQuery của tài sản Google Analytics dữ liệu xuất sự kiện, hãy tìm nhận xét -- Replace table trong mỗi truy vấn và thay thế bảng mẫu. Cách sao chép tên bảng từ tập dữ liệu:

  1. Chuyển đến giao diện người dùng BigQuery rồi chọn dự án chứa tập dữ liệu của bạn.
  2. Xác định vị trí bảng trong Explorer.
  3. Nhấp vào biểu tượng ba dấu chấm dọc ở bên phải bảng, rồi nhấp vào Sao chép mã nhận dạng.
  4. Dán tên bảng vào vị trí của bảng mẫu trong truy vấn.
  5. Thay thế phần ngày của bảng bằng *.

Ví dụ: nếu Sao chép mã nhận dạng đã sao chép tên bảng BigQuery my-first-gcp-project:analytics_28239234.events_20240718, sau đó thay thế:

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

bằng:

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

Truy vấn một phạm vi ngày cụ thể

Để truy vấn một phạm vi ngày cụ thể từ tập dữ liệu xuất sự kiện BigQuery, hãy sử dụng phương thức Cột giả _TABLE_OFFSET trong mệnh đề WHERE của truy vấn. Để biết thêm thông tin, xem Lọc bảng đã chọn bằng cách sử dụng _TABLE_FILENAME.

Ví dụ: truy vấn sau tính sự kiện duy nhất theo ngày và theo tên sự kiện cho khoảng thời gian cụ thể gồm các ngày và sự kiện đã chọn:

-- 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;

Số lượng người dùng và số người dùng mới

  • Để biết tổng số người dùng, hãy đếm số lượng user_id riêng biệt. Tuy nhiên, nếu khách hàng Google Analytics của bạn không gửi lại user_id với mỗi lượt truy cập hoặc nếu bạn không chắc chắn, hãy đếm số lượng user_pseudo_id riêng biệt.
  • Đối với người dùng mới, bạn có thể sử dụng cùng một phương pháp tính số lượng mô tả ở trên nhưng đối với các giá trị sau của event_name: 
-- Example: Get 'Total User' count and 'New User' count.

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 user_count,
  SUM(is_new_user) AS new_user_count
FROM UserInfo;

Số giao dịch trung bình trên mỗi người mua

Truy vấn sau đây cho biết số giao dịch trung bình trên mỗi người mua.

-- 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';

Giá trị cho tên sự kiện cụ thể

Truy vấn sau hiển thị event_timestamp cho tất cả Sự kiện purchase và thông số sự kiện liên quan giá trị:

-- 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';

Bạn có thể sửa đổi truy vấn trước đó để hiện tổng số thông số sự kiện thay vì một danh sách:

-- 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 mặt hàng hàng đầu được thêm vào giỏ hàng

Truy vấn sau đây cho biết 10 mặt hàng hàng đầu được thêm vào giỏ hàng theo nhiều người dùng nhất.

-- 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_web_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;

Số lượt xem trang trung bình theo kiểu người mua (người mua so với người không mua)

Truy vấn sau hiển thị số lượt xem trang trung bình mà kiểu người mua hàng người dùng (người mua so với người không mua):

-- 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;

Trình tự lượt xem trang

Truy vấn này cho biết trình tự số lượt xem trang do mỗi người dùng thực hiện. Truy vấn sắp xếp kết quả bằng cách sử dụng các trường sau để các sự kiện được liệt kê theo thứ tự chúng xảy ra cho người dùng, ngay cả khi các sự kiện được gửi trong cùng một lô:

  • user_pseudo_id
  • user_id
  • batch_page_id
  • batch_ordering_id
  • batch_event_index

Mặc dù mẫu này giới hạn kết quả chỉ ở các sự kiện page_view, nhưng bạn có thể sử dụng cùng một mệnh đề ORDER BY để sắp xếp chính xác tất cả các sự kiện bằng cách xoá Điều kiện mệnh đề WHERE cho event_name.

Truy vấn này cũng chỉ ra cách sử dụng mã truy vấn do người dùng xác định hàm GetParamString và GetParamInt để giảm trùng lặp và tạo truy vấn của bạn dễ hiểu và dễ duy trì hơn.

-- 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;

Danh sách thông số sự kiện

Truy vấn sau đây liệt kê tất cả các thông số sự kiện xuất hiện trong tập dữ liệu của bạn:

-- 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;

Tham gia bằng Google Ads

Để truy xuất dữ liệu Google Ads bổ sung cho các sự kiện Google Analytics, hãy thiết lập Dịch vụ chuyển dữ liệu của BigQuery dành cho Google Ads, sau đó kết hợp collected_traffic_source.gclid từ dữ liệu sự kiện Google Analytics với gclid trường ads_ClickStats_customer_id trong quy trình chuyển Google Ads.

Xin lưu ý rằng tính năng xuất dữ liệu sự kiện của Google Analytics sẽ tạo một bảng cho mỗi ngày, trong khi thì dữ liệu chuyển Google Ads sẽ điền sẵn ads_ClickStats_customer_id bảng cho mỗi khách hàng.

Trước
Bắt đầu
Tiếp
Truy vấn sự kiện nâng cao