Truy vấn nâng cao

Các truy vấn nâng cao trong trang này áp dụng cho dữ liệu xuất sự kiện BigQuery cho Google Analytics. Để xem các ví dụ đơn giản hơn, hãy xem trang Truy vấn cơ bản.

Sản phẩm được mua bởi khách hàng đã mua một sản phẩm nhất định

Truy vấn sau đây cho biết những sản phẩm khác mà khách hàng đã mua khi mua một sản phẩm cụ thể. Ví dụ này không giả định rằng các sản phẩm được mua trong cùng một đơn đặt hàng.

Ví dụ được tối ưu hoá dựa vào các tính năng tập lệnh BigQuery để xác định một biến khai báo các mặt hàng cần lọc. Mặc dù không cải thiện hiệu suất, nhưng đây là một phương pháp dễ đọc hơn để xác định các biến so với việc tạo một bảng giá trị duy nhất bằng mệnh đề WITH. Truy vấn đơn giản hoá sử dụng phương pháp sau bằng mệnh đề WITH.

Truy vấn đơn giản hoá tạo một danh sách riêng biệt gồm "Người mua sản phẩm A" và thực hiện thao tác kết hợp với dữ liệu đó. Thay vào đó, truy vấn được tối ưu hoá sẽ tạo một danh sách tất cả các mặt hàng mà người dùng đã mua trên các đơn đặt hàng bằng hàm ARRAY_AGG. Sau đó, bằng cách sử dụng mệnh đề WHERE bên ngoài, truy vấn sẽ lọc danh sách mua hàng trên tất cả người dùng cho target_item và chỉ những mặt hàng có liên quan mới được hiển thị.

Đã đơn giản hóa

-- Example: Products purchased by customers who purchased a specific product.
--
-- `Params` is used to hold the value of the selected product and is referenced
-- throughout the query.

WITH
  Params AS (
    -- Replace with selected item_name or item_id.
    SELECT 'Google Navy Speckled Tee' AS selected_product
  ),
  PurchaseEvents AS (
    SELECT
      user_pseudo_id,
      items
    FROM
      -- Replace table name.
      `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
    WHERE
      -- Replace date range.
      _TABLE_SUFFIX BETWEEN '20201101' AND '20210131'
      AND event_name = 'purchase'
  ),
  ProductABuyers AS (
    SELECT DISTINCT
      user_pseudo_id
    FROM
      Params,
      PurchaseEvents,
      UNNEST(items) AS items
    WHERE
      -- item.item_id can be used instead of items.item_name.
      items.item_name = selected_product
  )
SELECT
  items.item_name AS item_name,
  SUM(items.quantity) AS item_quantity
FROM
  Params,
  PurchaseEvents,
  UNNEST(items) AS items
WHERE
  user_pseudo_id IN (SELECT user_pseudo_id FROM ProductABuyers)
  -- item.item_id can be used instead of items.item_name
  AND items.item_name != selected_product
GROUP BY 1
ORDER BY item_quantity DESC;

Đã tối ưu hoá

-- Optimized Example: Products purchased by customers who purchased a specific product.

-- Replace item name
DECLARE target_item STRING DEFAULT 'Google Navy Speckled Tee';

SELECT
  IL.item_name AS item_name,
  SUM(IL.quantity) AS quantity
FROM
  (
    SELECT
      user_pseudo_id,
      ARRAY_AGG(STRUCT(item_name, quantity)) AS item_list
    FROM
      -- Replace table
      `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`, UNNEST(items)
    WHERE
      -- Replace date range
      _TABLE_SUFFIX BETWEEN '20201201' AND '20201210'
      AND event_name = 'purchase'
    GROUP BY
      1
  ),
  UNNEST(item_list) AS IL
WHERE
  target_item IN (SELECT item_name FROM UNNEST(item_list))
  -- Remove the following line if you want the target_item to appear in the results
  AND target_item != IL.item_name
GROUP BY
  item_name
ORDER BY
  quantity DESC;

Mức chi tiêu trung bình trên mỗi phiên mua hàng

Các truy vấn sau đây tính toán số tiền trung bình được chi tiêu trên mỗi phiên, chỉ xem xét các phiên mà người dùng đã mua hàng. Cả hai truy vấn đều sử dụng Biểu thức bảng chung (CTE) để tính toán tổng mức chi tiêu cho từng phiên mua hàng riêng biệt.

1. Số tiền trung bình được chi tiêu trên mỗi phiên mua hàng THEO NGƯỜI DÙNG:

Truy vấn này cho biết mức chi tiêu trung bình trên mỗi phiên cho từng người dùng:

-- Calculates the average session spend per user.
WITH
  session_spend AS (
    SELECT
      user_pseudo_id,
      (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS session_id,
      SUM(
        COALESCE(
          (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'value'),
          (SELECT value.float_value FROM UNNEST(event_params) WHERE key = 'value'),
          (SELECT value.double_value FROM UNNEST(event_params) WHERE key = 'value'),
          0.0)
      ) AS total_session_spend
    FROM
      -- Replace table name.
      `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
    WHERE
      event_name = 'purchase'
      -- Replace date range.
      AND _TABLE_SUFFIX BETWEEN '20201101' AND '20210131'
      AND EXISTS(SELECT 1 FROM UNNEST(event_params) WHERE key = 'ga_session_id' AND value.int_value IS NOT NULL)
    GROUP BY
      user_pseudo_id, session_id
  )
SELECT
  user_pseudo_id,
  COUNT(session_id) AS number_of_purchase_sessions,
  AVG(total_session_spend) AS avg_spend_per_session_by_user
FROM
  session_spend
GROUP BY
  user_pseudo_id
ORDER BY
  avg_spend_per_session_by_user DESC;

2. Số tiền trung bình được chi tiêu TRÊN TẤT CẢ các phiên mua hàng:

Truy vấn này tính toán mức chi tiêu trung bình tổng thể trên mọi phiên mua hàng riêng biệt của tất cả người dùng:

-- Calculates the overall average session spend across all users and sessions.
WITH
  session_spend AS (
    SELECT
      user_pseudo_id,
      (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS session_id,
      SUM(
        COALESCE(
          (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'value'),
          (SELECT value.float_value FROM UNNEST(event_params) WHERE key = 'value'),
          (SELECT value.double_value FROM UNNEST(event_params) WHERE key = 'value'),
          0.0)
      ) AS total_session_spend
    FROM
      -- Replace table name.
      `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
    WHERE
      event_name = 'purchase'
      -- Replace date range.
      AND _TABLE_SUFFIX BETWEEN '20201101' AND '20210131'
      AND EXISTS(SELECT 1 FROM UNNEST(event_params) WHERE key = 'ga_session_id' AND value.int_value IS NOT NULL)
    GROUP BY
      user_pseudo_id, session_id
  )
SELECT
  COUNT(session_id) AS total_purchase_sessions,
  AVG(total_session_spend) AS overall_avg_spend_per_session
FROM
  session_spend;

Mã phiên và số phiên mới nhất cho người dùng

Truy vấn sau đây cung cấp danh sách ga_session_id và ga_session_number mới nhất trong 4 ngày qua cho danh sách người dùng. Bạn có thể cung cấp danh sách user_pseudo_id hoặc danh sách user_id.

user_pseudo_id

-- Get the latest ga_session_id and ga_session_number for specific users during last 4 days.

-- Replace timezone. List at https://en.wikipedia.org/wiki/List_of_tz_database_time_zones.
DECLARE REPORTING_TIMEZONE STRING DEFAULT 'America/Los_Angeles';

-- Replace list of user_pseudo_id's with ones you want to query.
DECLARE USER_PSEUDO_ID_LIST ARRAY<STRING> DEFAULT
  [
    '1005355938.1632145814', '979622592.1632496588', '1101478530.1632831095'];

CREATE TEMP FUNCTION GetParamValue(params ANY TYPE, target_key STRING)
AS (
  (SELECT `value` FROM UNNEST(params) WHERE key = target_key LIMIT 1)
);

CREATE TEMP FUNCTION GetDateSuffix(date_shift INT64, timezone STRING)
AS (
  (SELECT FORMAT_DATE('%Y%m%d', DATE_ADD(CURRENT_DATE(timezone), INTERVAL date_shift DAY)))
);

SELECT DISTINCT
  user_pseudo_id,
  FIRST_VALUE(GetParamValue(event_params, 'ga_session_id').int_value)
    OVER (UserWindow) AS ga_session_id,
  FIRST_VALUE(GetParamValue(event_params, 'ga_session_number').int_value)
    OVER (UserWindow) AS ga_session_number
FROM
  -- Replace table name.
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE
  user_pseudo_id IN UNNEST(USER_PSEUDO_ID_LIST)
  AND RIGHT(_TABLE_SUFFIX, 8)
    BETWEEN GetDateSuffix(-3, REPORTING_TIMEZONE)
    AND GetDateSuffix(0, REPORTING_TIMEZONE)
WINDOW UserWindow AS (PARTITION BY user_pseudo_id ORDER BY event_timestamp DESC);

user_id

-- Get the latest ga_session_id and ga_session_number for specific users during last 4 days.

-- Replace timezone. List at https://en.wikipedia.org/wiki/List_of_tz_database_time_zones.
DECLARE REPORTING_TIMEZONE STRING DEFAULT 'America/Los_Angeles';

-- Replace list of user_id's with ones you want to query.
DECLARE USER_ID_LIST ARRAY<STRING> DEFAULT ['<user_id_1>', '<user_id_2>', '<user_id_n>'];

CREATE TEMP FUNCTION GetParamValue(params ANY TYPE, target_key STRING)
AS (
  (SELECT `value` FROM UNNEST(params) WHERE key = target_key LIMIT 1)
);

CREATE TEMP FUNCTION GetDateSuffix(date_shift INT64, timezone STRING)
AS (
  (SELECT FORMAT_DATE('%Y%m%d', DATE_ADD(CURRENT_DATE(timezone), INTERVAL date_shift DAY)))
);

SELECT DISTINCT
  user_pseudo_id,
  FIRST_VALUE(GetParamValue(event_params, 'ga_session_id').int_value)
    OVER (UserWindow) AS ga_session_id,
  FIRST_VALUE(GetParamValue(event_params, 'ga_session_number').int_value)
    OVER (UserWindow) AS ga_session_number
FROM
  -- Replace table name.
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE
  user_id IN UNNEST(USER_ID_LIST)
  AND RIGHT(_TABLE_SUFFIX, 8)
    BETWEEN GetDateSuffix(-3, REPORTING_TIMEZONE)
    AND GetDateSuffix(0, REPORTING_TIMEZONE)
WINDOW UserWindow AS (PARTITION BY user_pseudo_id ORDER BY event_timestamp DESC);
Trước
Truy vấn cơ bản về sự kiện
Tiếp
Truy vấn dữ liệu người dùng