نمونه پرس و جوهای موجود در این صفحه برای صادر کردن داده‌های کاربر BigQuery برای Google Analytics 4 اعمال می‌شود. صادرات داده‌های کاربر BigQuery دو جدول برای هر روز ایجاد می‌کند:
- یک جدول
users_YYYYMMDDکه شامل یک ردیف برای هر شناسه کاربری که تغییر کرده است. - یک جدول
pseudonymous_users_YYYYMMDDکه حاوی یک ردیف برای هر شناسه مستعار تغییر یافته است..
برای جزئیات بیشتر ، طرح داده‌های کاربر صادرات BigQuery را بررسی کنید.
یک محدوده تاریخی خاص را پرس و جو کنید
برای پرس و جو از یک محدوده تاریخی خاص از مجموعه داده صادر شده کاربر BigQuery، از ستون شبه _TABLE_SUFFIX در عبارت WHERE درخواست خود استفاده کنید.
برای مثال، عبارت زیر تعداد کاربران منحصربه‌فردی را که بین 1 اوت 2023 و 15 اوت 2023 به‌روزرسانی شده‌اند، با مدت زمان فعالیت حداقل پنج دقیقه محاسبه می‌کند.
کاربران
-- 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;
کاربران_ مستعار
-- 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 محدود می‌کند:
- عام
202308*در عبارتFROM. - یک شرط
_TABLE_SUFFIXدر عبارتWHEREکه جداول را بر اساس قسمت عام نام جدول فیلتر می کند. برای علامت عام202308*، بخش عام، روز ماه است.
شما می توانید از یک رویکرد مشابه برای پرس و جو از داده های چند ماهه استفاده کنید. به عنوان مثال، برای پرس و جو از ژانویه تا اکتبر 2023، پرس و جو را به گونه ای تغییر دهید که:
- عام
2023*. - یک شرط
_TABLE_SUFFIXاز_TABLE_SUFFIX BETWEEN '0101' AND '1031'.
همچنین می توانید داده های چند ساله را پرس و جو کنید. به عنوان مثال، برای پرس و جو از اکتبر 2022 تا فوریه 2023، پرس و جو را به گونه ای تغییر دهید که:
-
202*. - یک شرط
_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;
کاربران_ مستعار
-- 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;
کاربران_ مستعار
-- 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;