Firebase のデータを BigQuery にエクスポートすると、そのデータに対するクエリを実行して特定のオーディエンスを抽出できます。
この記事では、クエリのベースとして使用できるテンプレートをいくつかご紹介します。実際に使用する際は、必要なデータに合わせてクエリ内の必要な箇所（たとえばテーブル名や期間）を書き換えてください。
これらのクエリで得られるのは、該当オーディエンスに含まれるユーザーの数です。該当ユーザーの数ではなく、該当ユーザーの ID 一覧を取得するには、コードの最も外側にある COUNT() 関数を削除します（例: COUNT(DISTINCT user_id) → DISTINCT user_id）。
この記事のクエリは標準 SQL で記述されています。クエリ実行の際は、標準 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 日間にイベント（イベント パラメータ
* engagement_time_msec > 0）が 1 件以上記録されているユーザーを指します。
*/
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）が 1 つも記録されていないユーザーを指します
* （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;
頻繁にアクティブなユーザー
/**
* 頻繁にアクティブなユーザーのオーディエンスを作成します。
*
* 頻繁にアクティブなユーザーとは、イベント
* （イベント パラメータ engagement_time_msec > 0）が 1 回以上記録された日が
* 過去 M 日間に N 日以上あったユーザーを指します（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 分以上
* アクティブだったユーザーを指します。
*/
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 キャンペーンを通して
* 獲得したユーザーのオーディエンスを作成します。
*
* 前の週（当日の 14 日前～7 日前）に獲得したユーザーを
* コホートとして定義します。コホート フィルタは、直接キャンペーンを通して
* 獲得したユーザーを抽出するためのものです。
*/
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';