การค้นหาขั้นสูง

การค้นหาขั้นสูงในหน้านี้ใช้กับข้อมูลการส่งออกเหตุการณ์ BigQuery สําหรับ Google Analytics ดูตำราอาหาร BigQuery สำหรับ Universal Analytics หากคุณ ต้องการแหล่งข้อมูลเดียวกันนี้สำหรับ Universal Analytics ลองใช้การค้นหาพื้นฐาน ก่อนที่จะลองใช้แบบขั้นสูง

ผลิตภัณฑ์ที่ซื้อโดยลูกค้าที่ซื้อผลิตภัณฑ์บางรายการ

คำค้นหาต่อไปนี้แสดงผลิตภัณฑ์อื่นๆ ที่ลูกค้าซื้อ ซื้อผลิตภัณฑ์ที่เฉพาะเจาะจง ตัวอย่างนี้ไม่ได้ถือว่าผลิตภัณฑ์ มีการซื้อในคำสั่งซื้อเดียวกัน

ตัวอย่างที่มีการเพิ่มประสิทธิภาพนี้ใช้ฟีเจอร์การเขียนสคริปต์ของ BigQuery เพื่อกำหนดตัวแปร ที่ระบุว่าต้องกรองสินค้าใดบ้าง แม้ว่าการดำเนินการนี้ไม่ได้ปรับปรุงประสิทธิภาพ นี่เป็นวิธีการกำหนดตัวแปรที่อ่านง่ายกว่าเมื่อเทียบกับการสร้าง ตารางค่าเดี่ยวที่ใช้อนุประโยค WITH ส่วนคำค้นหาแบบง่ายจะใช้แบบหลัง โดยใช้วรรค WITH

คำค้นหาแบบง่ายจะสร้างรายการ "ผู้ซื้อผลิตภัณฑ์ A" แยกต่างหาก และทำกิจกรรม รวมกับข้อมูลนั้น คำค้นหาที่เพิ่มประสิทธิภาพแล้ว จะสร้างรายชื่อรายการทั้งหมด ผู้ใช้ทำการซื้อข้ามคำสั่งซื้อโดยใช้ฟังก์ชัน ARRAY_AGG จากนั้นการใช้ เงื่อนไข WHERE ภายนอก รายการซื้อสำหรับผู้ใช้ทั้งหมดจะถูกกรองสำหรับ target_itemและแสดงเฉพาะรายการ��ี่เกี่ยวข้องเท่านั้น

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

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

จำนวนเงินที่ใช้โดยเฉลี่ยต่อเซสชันการซื้อของผู้ใช้

คำค้นหาต่อไปนี้แสดงจำนวนเงินโดยเฉลี่ยที่ใช้ต่อเซสชันโดยแต่ละเซสชัน ผู้ใช้ ซึ่งพิจารณาเฉพาะเซสชันที่ผู้ใช้ทำการซื้อ

-- Example: Average amount of money spent per purchase session by user.

WITH
  events AS (
    SELECT
      session.value.int_value AS session_id,
      COALESCE(spend.value.int_value, spend.value.float_value, spend.value.double_value, 0.0)
        AS spend_value,
      event.*

    -- Replace table name
    FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*` AS event
    LEFT JOIN UNNEST(event.event_params) AS session
      ON session.key = 'ga_session_id'
    LEFT JOIN UNNEST(event.event_params) AS spend
      ON spend.key = 'value'

    -- Replace date range
    WHERE _TABLE_SUFFIX BETWEEN '20201101' AND '20210131'
  )
SELECT
  user_pseudo_id,
  COUNT(DISTINCT session_id) AS session_count,
  SUM(spend_value) / COUNT(DISTINCT session_id) AS avg_spend_per_session_by_user
FROM events
WHERE event_name = 'purchase' and session_id IS NOT NULL
GROUP BY user_pseudo_id

รหัสเซสชันและหมายเลขเซสชันล่าสุดสำหรับผู้ใช้

คำค้นหาต่อไปนี้แสดงรายการ ga_session_id ล่าสุดและ ga_session_number จาก 4 วันที่ผ่านมาสำหรับรายชื่อผู้ใช้ คุณสามารถระบุ รายการuser_pseudo_idหรือ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_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);

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