您将 Firebase 数据导出到 BigQuery 之后，便可以查询特定受众群体的此类数据。
本文提供了许多模板，供您在此基础上进行查询。请务必修改查询示例，以反映您的数据的具体内容，例如更改表格名称以及修改日期范围。
这些查询会返回受众群体中的用户数。如果您想获取受众群体中的用户 ID 列表，请删除最外侧的 COUNT() 函数，例如，将 COUNT(DISTINCT user_id) 更改为 DISTINCT user_id。
这些查询使用标准 SQL，因此请确保先选择该选项，然后再运行查询。（在“BigQuery”>“SQL 工作区”下，依次点击更多 > 查询设置。在“其他设置”>“SQL 方言”下，选择标准。）
目前，您只能查看此受众群体数据，不可进行操作。
我们很想知道，这些查询示例是否对您有所帮助，以及您是否想查询其他类型的受众群体。您可以通过在 Firebase 支持表单中提交功能请求来进行回复。
本文包含的主题：
购买者
/**
* 计算“购买者”受众群体。
*
* 购买者 = 已记录 in_app_purchase 或
* purchase 的用户。*/
SELECT
COUNT(DISTINCT user_id) AS purchasers_count
FROM
-- PLEASE REPLACE WITH YOUR TABLE NAME.
`YOUR_TABLE.events_*`
WHERE
event_name IN ('in_app_purchase', 'purchase')
-- PLEASE REPLACE WITH YOUR DESIRED DATE RANGE
AND _TABLE_SUFFIX BETWEEN '20180501' AND '20240131';
N 天活跃用户
/**
* 构建“N 天活跃用户”受众群体。
*
* N 天活跃用户 = 在过去 N 天内记录了至少一次参数为
* engage_time_msec > 0 的事件的用户。
*/
SELECT
COUNT(DISTINCT user_id) AS n_day_active_users_count
FROM
-- PLEASE REPLACE WITH YOUR TABLE NAME.
`YOUR_TABLE.events_*` AS T
CROSS JOIN
T.event_params
WHERE
event_params.key = 'engagement_time_msec' AND event_params.value.int_value > 0
-- Pick events in the last N = 20 days.
AND event_timestamp >
UNIX_MICROS(TIMESTAMP_SUB(CURRENT_TIMESTAMP, INTERVAL 20 DAY))
-- PLEASE REPLACE WITH YOUR DESIRED DATE RANGE.
AND _TABLE_SUFFIX BETWEEN '20180521' AND '20240131';
N 天非活跃用户
/**
* 构建“N 天非活跃用户”受众群体。
*
* N 天非活跃用户 = 过去 M 天中的最近 N 天内未记录过任何
* 参数为 engagement_time_msec > 0 的事件的用户
* M > N。*/
SELECT
COUNT(DISTINCT MDaysUsers.user_id) AS n_day_inactive_users_count
FROM
(
SELECT
user_id
FROM
/* PLEASE REPLACE WITH YOUR TABLE NAME */
`YOUR_TABLE.events_*` AS T
CROSS JOIN
T.event_params
WHERE
event_params.key = 'engagement_time_msec' AND event_params.value.int_value > 0
/* Has engaged in last M = 7 days */
AND event_timestamp >
UNIX_MICROS(TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY))
/* PLEASE REPLACE WITH YOUR DESIRED DATE RANGE */
AND _TABLE_SUFFIX BETWEEN '20180521' AND '20240131'
) AS MDaysUsers
-- EXCEPT ALL is not yet implemented in BigQuery. Use LEFT JOIN in the interim.
LEFT JOIN
(
SELECT
user_id
FROM
/* PLEASE REPLACE WITH YOUR TABLE NAME */
`YOUR_TABLE.events_*`AS T
CROSS JOIN
T.event_params
WHERE
event_params.key = 'engagement_time_msec' AND event_params.value.int_value > 0
/* Has engaged in last N = 2 days */
AND event_timestamp >
UNIX_MICROS(TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 2 DAY))
/* PLEASE REPLACE WITH YOUR DESIRED DATE RANGE */
AND _TABLE_SUFFIX BETWEEN '20180521' AND '20240131'
) AS NDaysUsers
ON MDaysUsers.user_id = NDaysUsers.user_id
WHERE
NDaysUsers.user_id IS NULL;
经常活跃用户
/**
* 构建“经常活跃用户”受众群体。
*
* 经常活跃用户 = 在过去 M 天中的 N 天内，记录了至少一次
* 参数为 engagement_time_msec > 0 的事件的用户
* M > N。
*/
SELECT
COUNT(DISTINCT user_id) AS frequent_active_users_count
FROM
(
SELECT
user_id,
COUNT(DISTINCT event_date)
FROM
-- PLEASE REPLACE WITH YOUR TABLE NAME.
`YOUR_TABLE.events_*` AS T
CROSS JOIN
T.event_params
WHERE
event_params.key = 'engagement_time_msec' AND event_params.value.int_value > 0
-- User engagement in the last M = 10 days.
AND event_timestamp >
UNIX_MICROS(TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 10 DAY))
-- PLEASE REPLACE YOUR DESIRED DATE RANGE. For optimal performance
-- the _TABLE_SUFFIX range should match the INTERVAL value above.
AND _TABLE_SUFFIX BETWEEN '20180521' AND '20240131'
GROUP BY 1
-- Having engaged in at least N = 4 days.
HAVING COUNT(event_date) >= 4
);
高度活跃用户
/**
* 构建“高度活跃用户”受众群体。
*
* 高度活跃用户 = 过去 M 天内活跃时间超过 N 分钟的
* 用户（此处，M 大于 N）。
*/
SELECT
COUNT(DISTINCT user_id) AS high_active_users_count
FROM
(
SELECT
user_id,
event_params.key,
SUM(event_params.value.int_value)
FROM
-- PLEASE REPLACE WITH YOUR TABLE NAME.
`YOUR_TABLE.events_*` AS T
CROSS JOIN
T.event_params
WHERE
-- User engagement in the last M = 10 days.
event_timestamp >
UNIX_MICROS(TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 10 DAY))
AND event_params.key = 'engagement_time_msec'
-- PLEASE REPLACE YOUR DESIRED DATE RANGE.
AND _TABLE_SUFFIX BETWEEN '20180521' AND '20240131'
GROUP BY 1, 2
HAVING
-- Having engaged for more than N = 0.1 minutes.
SUM(event_params.value.int_value) > 0.1 * 60 * 1000000
);
获取的用户
/**
* 构建“获取的用户”受众群体。
*
* 获取的用户 = 通过某些来源/媒介/广告系列获取的用户。
*/
SELECT
COUNT(DISTINCT user_id) AS acquired_users_count
FROM
-- PLEASE REPLACE WITH YOUR TABLE NAME.
`YOUR_TABLE.events_*`
WHERE
traffic_source.source = 'google'
AND traffic_source.medium = 'cpc'
AND traffic_source.name = 'VTA-Test-Android'
-- PLEASE REPLACE YOUR DESIRED DATE RANGE.
AND _TABLE_SUFFIX BETWEEN '20180521' AND '20240131';
使用过滤条件的同类群组
/**
* 通过 Google 广告系列构建由上周获取的用户
*（即使用过滤条件的同类群组）组成的受众群体。
*
* 同类群组定义为上周（即 7 - 14 天前）获取的
* 用户。同类群组过滤条件适用于通过直接广告
* 系列获取的用户。
*/
SELECT
COUNT(DISTINCT user_id) AS users_acquired_through_google_count
FROM
-- PLEASE REPLACE WITH YOUR TABLE NAME.
`YOUR_TABLE.events_*`
WHERE
event_name = 'first_open'
-- Cohort: opened app 1-2 weeks ago. One week of cohort, aka. weekly.
AND event_timestamp >
UNIX_MICROS(TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 14 DAY))
AND event_timestamp <
UNIX_MICROS(TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY))
-- Cohort filter: users acquired through 'google' source.
AND traffic_source.source = 'google'
-- PLEASE REPLACE YOUR DESIRED DATE RANGE.
AND _TABLE_SUFFIX BETWEEN '20180501' AND '20240131';