この記事では、Google アナリティクス 4 プロパティについて説明します。ユニバーサル アナリティクス プロパティを使用している場合は、ヘルプセンターのユニバーサル アナリティクスのセクションをご覧ください。

[GA4] BigQuery のデータからオーディエンスを抽出するクエリのサンプル

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';
  
  
この情報は役に立ちましたか?
改善できる点がありましたらお聞かせください。
検索
検索をクリア
検索終了
Google アプリ
メインメニュー
ヘルプセンターを検索
true
69256
false