การค้นหาสําหรับการส่งออกข้อมูลผู้ใช้ Google Analytics 4

ตัวอย่างการค้นหาในหน้านี้ใช้กับการส่งออกข้อมูลผู้ใช้ BigQuery สำหรับ Google Analytics 4 การส่งออกข้อมูลผู้ใช้ BigQuery จะสร้าง 2 ตารางสำหรับแต่ละวัน ดังนี้

1. ตาราง users_YYYYMMDD ซึ่งมีแถวสําหรับ User-ID ทั้งหมดที่เปลี่ยนแปลง
2. ตาราง pseudonymous_users_YYYYMMDD ซึ่งมีแถวสำหรับตัวระบุที่ไม่ระบุตัวบุคคลทั้งหมดที่เปลี่ยนแปลง

ดูรายละเอียดเพิ่มเติมได้ที่สคีมาข้อมูลผู้ใช้ของ BigQuery Export

เคล็ดลับ: หากต้องการใช้การค้นหาตัวอย่างในคู่มือนี้ให้เกิดประโยชน์สูงสุด ให้ตั้งค่าตัวแปร PROJECT_ID และ PROPERTY_ID ด้วยค่าสำหรับโปรเจ็กต์และพร็อพเพอร์ตี้ของคุณ ดึงข้อมูล PROJECT_ID จากแดชบอร์ดโปรเจ็กต์ และ PROPERTY_ID จากตัวเลือกพร็อพเพอร์ตี้ของ Google Analytics

ค้นหาช่วงวันที่ที่ต้องการ

หากต้องการค้นหาช่วงวันที่ที่เจาะจงจากชุดข้อมูลการส่งออกข้อมูลผู้ใช้ BigQuery ให้ใช้คอลัมน์จำลอง _TABLE_SUFFIX ในอนุประโยค WHERE ของการค้นหา

ตัวอย่างเช่น การค้นหาต่อไปนี้จะนับจำนวนผู้ใช้ที่ไม่ซ้ำซึ่งอัปเดตระหว่างวันที่ 1 สิงหาคม 2023 ถึง 15 สิงหาคม 2023 โดยมีการมีส่วนร่วมตลอดอายุอย่างน้อย 5 นาที

ผู้ใช้

-- Example: Query a specific date range for users meeting a lifetime engagement criterion.
--
-- Counts unique users that are in the BigQuery user-data exports for a specific date range and have
-- a lifetime engagement of 5 minutes or more.

SELECT
  COUNT(DISTINCT user_id) AS user_count
FROM
  -- Uses a table suffix wildcard to define the set of daily tables to query.
  `PROJECT_ID.analytics_PROPERTY_ID.users_202308*`
WHERE
  -- Filters to users updated between August 1 and August 15.
  _TABLE_SUFFIX BETWEEN '01' AND '15'
  -- Filters by users who have a lifetime engagement of 5 minutes or more.
  AND user_ltv.engagement_time_millis >= 5 * 60 * 1000;

pseudonymous_users

-- Example: Query a specific date range for users meeting a lifetime engagement criterion.
--
-- Counts unique pseudonymous users that are in the BigQuery user-data exports for a specific date
-- range and have a lifetime engagement of 5 minutes or more.

SELECT
  COUNT(DISTINCT pseudo_user_id) AS pseudo_user_count
FROM
  -- Uses a table suffix wildcard to define the set of daily tables to query.
  `PROJECT_ID.analytics_PROPERTY_ID.pseudonymous_users_202308*`
WHERE
  -- Filters to users updated between August 1 and August 15.
  _TABLE_SUFFIX BETWEEN '01' AND '15'
  -- Filters by users who have a lifetime engagement of 5 minutes or more.
  AND user_ltv.engagement_time_millis >= 5 * 60 * 1000;

แต่ละตัวอย่างจะจํากัดข้อมูลไว้ตั้งแต่วันที่ 1 สิงหาคม 2023 จนถึงวันที่ 15 สิงหาคม 2023 โดยใช้ 2 ฟีเจอร์ต่อไปนี้

1. ไวลด์การ์ด 202308* ในวรรค FROM
2. เงื่อนไข _TABLE_SUFFIX ในวลี WHERE ที่กรองตารางตามส่วนไวลด์การ์ดของชื่อตาราง สําหรับไวลด์การ์ดของ 202308* ส่วนไวลด์การ์ดคือวันของเดือน

คุณใช้วิธีที่คล้ายกันนี้เพื่อค้นหาข้อมูลหลายเดือนได้ เช่น สำหรับการค้นหาตั้งแต่เดือนมกราคมถึงตุลาคม 2023 ให้แก้ไขการค้นหาให้มีลักษณะดังนี้

1. ไวลด์การ์ด 2023*
2. เงื่อนไข _TABLE_SUFFIX ของ _TABLE_SUFFIX BETWEEN '0101' AND '1031'

และคุณยังค้นหาข้อมูลเป็นเวลาหลายปีได้ด้วย ตัวอย่างเช่น ในการค้นหาเดือนตุลาคม 2022 ถึงกุมภาพันธ์ 2023 ให้แก้ไขการค้นหาให้มีลักษณะดังนี้

1. ไวลด์การ์ด 202*
2. เงื่อนไข _TABLE_SUFFIX ของ _TABLE_SUFFIX BETWEEN '21001' AND '30331'

รหัสผู้ใช้สำหรับการเปลี่ยนแปลงพร็อพเพอร์ตี้ผู้ใช้ล่าสุด

คำค้นหาต่อไปนี้แสดงวิธีดึงข้อมูล user_id และ pseudo_user_id ของผู้ใช้ทั้งหมดที่เพิ่งเปลี่ยนพร็อพเพอร์ตี้ผู้ใช้รายการใดรายการหนึ่ง

ผู้ใช้

-- Example: Get the list of user_ids with recent changes to a specific user property.
DECLARE
  UPDATE_LOWER_BOUND_MICROS INT64;

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

-- Sets the variable for the earliest update time to include. This comes after setting
-- the REPORTING_TIMEZONE so this expression can use that variable.
SET UPDATE_LOWER_BOUND_MICROS = UNIX_MICROS(
    TIMESTAMP_SUB(
      TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY, REPORTING_TIMEZONE),
      INTERVAL 14 DAY));

-- Selects users with changes to a specific user property since the lower bound.
SELECT
  users.user_id,
  FORMAT_TIMESTAMP('%F %T',
    TIMESTAMP_MICROS(
      MAX(properties.value.set_timestamp_micros)),
      REPORTING_TIMEZONE) AS max_set_timestamp
FROM
  -- Uses a table prefix to scan all data for 2023. Update the prefix as needed to query a different
  -- date range.
  `PROJECT_ID.analytics_PROPERTY_ID.users_2023*` AS users,
  users.user_properties properties
WHERE
  properties.value.user_property_name = 'job_function'
  AND properties.value.set_timestamp_micros >= UPDATE_LOWER_BOUND_MICROS
GROUP BY
  1;

pseudonymous_users

-- Example: Get the list of pseudo_user_ids with recent changes to a specific user property.
DECLARE
  UPDATE_LOWER_BOUND_MICROS INT64;

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

-- Sets the variable for the earliest update time to include. This comes after setting
-- the REPORTING_TIMEZONE so this expression can use that variable.
SET UPDATE_LOWER_BOUND_MICROS = UNIX_MICROS(
    TIMESTAMP_SUB(
      TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY, REPORTING_TIMEZONE),
      INTERVAL 14 DAY));

-- Selects users with changes to a specific user property since the lower bound.
SELECT
  users.pseudo_user_id,
  FORMAT_TIMESTAMP('%F %T',
    TIMESTAMP_MICROS(
      MAX(properties.value.set_timestamp_micros)),
      REPORTING_TIMEZONE) AS max_set_timestamp
FROM
  -- Uses a table prefix to scan all data for 2023. Update the prefix as needed to query a different
  -- date range.
  `PROJECT_ID.analytics_PROPERTY_ID.pseudonymous_users_2023*` AS users,
  users.user_properties properties
WHERE
  properties.value.user_property_name = 'job_function'
  AND properties.value.set_timestamp_micros >= UPDATE_LOWER_BOUND_MICROS
GROUP BY
  1;

สรุปการอัปเดต

ใช้คำค้นหานี้เพื่อทำความเข้าใจว่าเหตุใดการส่งออกข้อมูลผู้ใช้จึงรวมหรือยกเว้นผู้ใช้หมวดหมู่ต่างๆ

ผู้ใช้

-- Summarizes data by change type.

-- Defines the export date to query. This must match the table suffix in the FROM
-- clause below.
DECLARE EXPORT_DATE DATE DEFAULT DATE(2023,6,16);

-- Creates a temporary function that will return true if a timestamp (in micros) is for the same
-- date as the specified day value.
CREATE TEMP FUNCTION WithinDay(ts_micros INT64, day_value DATE)
AS (
  (ts_micros IS NOT NULL) AND
  -- Change the timezone to your property's reporting time zone.
  -- List at https://en.wikipedia.org/wiki/List_of_tz_database_time_zones.
  (DATE(TIMESTAMP_MICROS(ts_micros), 'America/Los_Angeles') = day_value)
);

-- Creates a temporary function that will return true if a date string in 'YYYYMMDD' format is
-- for the same date as the specified day value.
CREATE TEMP FUNCTION SameDate(date_string STRING, day_value DATE)
AS (
  (date_string IS NOT NULL) AND
  (PARSE_DATE('%Y%m%d', date_string) = day_value)
);

WITH change_types AS (
SELECT user_id,
  WithinDay(user_info.last_active_timestamp_micros, EXPORT_DATE) AS user_activity,
  WithinDay(user_info.user_first_touch_timestamp_micros, EXPORT_DATE) AS first_touch,
  SameDate(user_info.first_purchase_date, EXPORT_DATE) as first_purchase,
  (EXISTS (SELECT 1 FROM UNNEST(audiences) AS aud
           WHERE WithinDay(aud.membership_start_timestamp_micros, EXPORT_DATE))) AS audience_add,
  (EXISTS (SELECT 1 FROM UNNEST(audiences) AS aud
           WHERE WithinDay(aud.membership_expiry_timestamp_micros, EXPORT_DATE))) AS audience_remove,
  (EXISTS (SELECT 1 FROM UNNEST(user_properties) AS prop
           WHERE WithinDay(prop.value.set_timestamp_micros, EXPORT_DATE))) AS user_property_change
FROM
  -- The table suffix must match the date used to define EXPORT_DATE above.
  `project_id.analytics_property_id.users_20230616`
)
SELECT
  user_activity,
  first_touch,
  first_purchase,
  audience_add,
  audience_remove,
  user_property_change,
  -- This field will be true if there are no changes for the other change types.
  NOT (user_activity OR first_touch OR audience_add OR audience_remove OR user_property_change) AS other_change,
  COUNT(DISTINCT user_id) AS user_id_count
FROM change_types
GROUP BY 1,2,3,4,5,6,7;

pseudonymous_users

-- Summarizes data by change type.

-- Defines the export date to query. This must match the table suffix in the FROM
-- clause below.
DECLARE EXPORT_DATE DATE DEFAULT DATE(2023,6,16);

-- Creates a temporary function that will return true if a timestamp (in micros) is for the same
-- date as the specified day value.
CREATE TEMP FUNCTION WithinDay(ts_micros INT64, day_value DATE)
AS (
  (ts_micros IS NOT NULL) AND
  -- Change the timezone to your property's reporting time zone.
  -- List at https://en.wikipedia.org/wiki/List_of_tz_database_time_zones.
  (DATE(TIMESTAMP_MICROS(ts_micros), 'America/Los_Angeles') = day_value)
);

-- Creates a temporary function that will return true if a date string in 'YYYYMMDD' format is
-- for the same date as the specified day value.
CREATE TEMP FUNCTION SameDate(date_string STRING, day_value DATE)
AS (
  (date_string IS NOT NULL) AND
  (PARSE_DATE('%Y%m%d', date_string) = day_value)
);

WITH change_types AS (
SELECT pseudo_user_id,
  WithinDay(user_info.last_active_timestamp_micros, EXPORT_DATE) AS user_activity,
  WithinDay(user_info.user_first_touch_timestamp_micros, EXPORT_DATE) AS first_touch,
  SameDate(user_info.first_purchase_date, EXPORT_DATE) as first_purchase,
  (EXISTS (SELECT 1 FROM UNNEST(audiences) AS aud
           WHERE WithinDay(aud.membership_start_timestamp_micros, EXPORT_DATE))) AS audience_add,
  (EXISTS (SELECT 1 FROM UNNEST(audiences) AS aud
           WHERE WithinDay(aud.membership_expiry_timestamp_micros, EXPORT_DATE))) AS audience_remove,
  (EXISTS (SELECT 1 FROM UNNEST(user_properties) AS prop
           WHERE WithinDay(prop.value.set_timestamp_micros, EXPORT_DATE))) AS user_property_change
FROM
  -- The table suffix must match the date used to define EXPORT_DATE above.
  `PROJECT_ID.analytics_PROPERTY_ID.pseudonymous_users_20230616`
)
SELECT
  user_activity,
  first_touch,
  first_purchase,
  audience_add,
  audience_remove,
  user_property_change,
  -- This field will be true if there are no changes for the other change types.
  NOT (user_activity OR first_touch OR audience_add OR audience_remove OR user_property_change) AS other_change,
  COUNT(DISTINCT pseudo_user_id) pseudo_user_id_count
FROM change_types
GROUP BY 1,2,3,4,5,6,7;