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

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

1. ตาราง users_YYYYMMDD ซึ่งมีแถวสําหรับรหัสผู้ใช้ทุกรหัสที่ มีการเปลี่ยนแปลง
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;