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

การค้นหาขั้นสูงในหน้านี้ใช้กับข้อมูลการส่งออกเหตุการณ์ 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รายการ

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