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

Firebase のデータを BigQuery にエクスポートすると、そのデータに対するクエリを実行して特定のオーディエンスを抽出することができます。

この記事では、クエリの雛型として使用できるテンプレートをいくつかご紹介します。実際に使用する際は、必要なデータに合わせてクエリ内の必要な箇所(たとえばテーブル名や期間)を書き換えてください。

これらのクエリで得られるのは、該当オーディエンスに含まれるユーザーの数です。該当ユーザーの数ではなく、該当ユーザーの ID 一覧を取得するには、コードの最も外側にある COUNT() 関数を削除します(例: COUNT(DISTINCT user_id) → DISTINCT user_id)。

この記事のクエリは標準 SQL で記述されています。クエリ実行の際は、標準 SQL を使用する設定を有効にしてください。

これらのクエリで得られるオーディエンス データは、現在のところ参照用であり、データから直接アクションを実行することはできません。

ご紹介したクエリサンプルがお役に立ったかどうか、ぜひお聞かせください。また、クエリで抽出したいオーディエンスが他にもありましたら、ご要望をお待ちしています。ご意見は Firebase サポートから機能リクエストとしてお送りいただけます。

 

この記事の内容:

購入者

  /**
 * 購入者のユーザーリストを計算します。
 *
 * 購入者とは、in_app_purchase または
 * ecommerce_purchase が記録されているユーザーを指します。
 */
SELECT
  COUNT(DISTINCT user_id) AS purchasers_count
FROM
  -- 実際のテーブル名に書き換えてください
  `YOUR_TABLE.events_*`
WHERE
  event_name IN ('in_app_purchase', 'ecommerce_purchase')
  -- 目的の期間に書き換えてください
  AND _TABLE_SUFFIX BETWEEN '20180501' AND '20240131';
  

N 日間アクティブ ユーザー

  /**
 * N 日間アクティブ ユーザーのユーザーリストを作成します。
 *
 * アクティブ ユーザーとは、過去 N 日間に user_engagement イベントが
 * 1 件以上記録されているユーザーを指します。
*/
SELECT
  COUNT(DISTINCT user_id) AS n_day_active_users_count
FROM
  -- 実際のテーブル名に書き換えてください
  `YOUR_TABLE.events_*`
WHERE
  event_name = 'user_engagement'
  -- 過去 N 日間(ここでは 20 日間)に発生したイベントを抽出
  AND event_timestamp >
      UNIX_MICROS(TIMESTAMP_SUB(CURRENT_TIMESTAMP, INTERVAL 20 DAY))
  -- 目的の期間に書き換えてください
  AND _TABLE_SUFFIX BETWEEN '20180521' AND '20240131';
  

N 日間非アクティブ ユーザー

  /**
 * N 日間非アクティブ ユーザーのユーザーリストを作成します。
 *
 *  N 日間非アクティブ ユーザーとは、過去 M 日間のユーザーのうち、
 *   過去 N 日間に user_engagement イベントが記録されていないユーザーを指します(M は N よりも大きいものとします)。
 */
SELECT
  COUNT(DISTINCT MDaysUsers.user_id) AS n_day_inactive_users_count
FROM
  (
    SELECT
      user_id
    FROM
      /* 実際のテーブル名に書き換えてください */
      `YOUR_TABLE.events_*`
    WHERE
      event_name = 'user_engagement'
      /* 過去 M 日間(ここでは 7 日間)にエンゲージメント発生 */
      AND event_timestamp >
          UNIX_MICROS(TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY))
      /* 目的の期間に書き換えてください */
      AND _TABLE_SUFFIX BETWEEN '20180521' AND '20240131'
  ) AS MDaysUsers
-- EXCEPT ALL はまだ BigQuery に実装されていません。当面は LEFT JOIN を使用してください
LEFT JOIN
  (
    SELECT
      user_id
    FROM
      /* 実際のテーブル名に書き換えてください */
      `YOUR_TABLE.events_*`
    WHERE
      event_name = 'user_engagement'
      /* 過去 N 日間(ここでは 2 日間)にエンゲージメント発生 */
      AND event_timestamp >
          UNIX_MICROS(TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 2 DAY))
      /* 目的の期間に書き換えてください */
      AND _TABLE_SUFFIX BETWEEN '20180521' AND '20240131'
  ) AS NDaysUsers
  ON MDaysUsers.user_id = NDaysUsers.user_id
WHERE
  NDaysUsers.user_id IS NULL;
  

頻繁にアクティブなユーザー

  /**
 * 頻繁にアクティブなユーザーのユーザーリストを作成します。
 *
 * 頻繁にアクティブなユーザーとは、「user_engagement」イベントが記録された日が
 * 過去 M 日間のうち N 日以上あるユーザーを指します(M は N よりも大きいものとします)。
 */
SELECT
  COUNT(DISTINCT user_id) AS frequent_active_users_count
FROM
  (
    SELECT
      user_id,
      COUNT(DISTINCT event_date)
    FROM
      -- 実際のテーブル名に書き換えてください
      `YOUR_TABLE.events_*`
    WHERE
      event_name = 'user_engagement'
      -- 過去 M 日間(ここでは 10 日間)のユーザー エンゲージメント
      AND event_timestamp >
          UNIX_MICROS(TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 10 DAY))
      -- 目的の期間に書き換えてください。最適なパフォーマンスを得るには、
      -- _TABLE_SUFFIX の範囲と上記の INTERVAL 値を一致させる必要があります
      AND _TABLE_SUFFIX BETWEEN '20180521' AND '20240131'
    GROUP BY 1
    -- エンゲージメントが発生した日が N 日(ここでは 4 日)以上
    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
      -- 実際のテーブル名に書き換えてください
      `YOUR_TABLE.events_*` AS T
    CROSS JOIN
      T.event_params
    WHERE
      event_name = 'user_engagement'
      -- 過去 M 日間(ここでは 10 日間)のユーザー エンゲージメント
      AND event_timestamp >
          UNIX_MICROS(TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 10 DAY))
      AND event_params.key = 'engagement_time_msec'
      -- 目的の期間に書き換えてください
      AND _TABLE_SUFFIX BETWEEN '20180521' AND '20240131'
    GROUP BY 1, 2
    HAVING
      -- N 分(ここでは 0.1 分)以上にわたるエンゲージメントが発生
      SUM(event_params.value.int_value) > 0.1 * 60 * 1000000
  );

  

獲得ユーザー

  /**
 * 獲得ユーザーのユーザーリストを作成します。
 *
 * 獲得ユーザーとは、なんらかの参照元、メディア、キャンペーンを介して得られたユーザーです。
 */
SELECT
  COUNT(DISTINCT user_id) AS acquired_users_count
FROM
  -- 実際のテーブル名に書き換えてください
  `YOUR_TABLE.events_*`
WHERE
  traffic_source.source = 'google'
  AND traffic_source.medium = 'cpc'
  AND traffic_source.name = 'VTA-Test-Android'
  -- 目的の期間に書き換えてください
  AND _TABLE_SUFFIX BETWEEN '20180521' AND '20240131';
  

フィルタ付きコホート

  /**
 * 獲得ユーザーのユーザーリストを作成します。
 *
 * 獲得ユーザーとは、なんらかの参照元、メディア、キャンペーンを介して得られたユーザーです。
 */
SELECT
  COUNT(DISTINCT user_id) AS acquired_users_count
FROM
  -- 実際のテーブル名に書き換えてください
  `YOUR_TABLE.events_*`
WHERE
  traffic_source.source = 'google'
  AND traffic_source.medium = 'cpc'
  AND traffic_source.name = 'VTA-Test-Android'
  -- 目的の期間に書き換えてください
  AND _TABLE_SUFFIX BETWEEN '20180521' AND '20240131';
  
この情報は役に立ちましたか?
改善できる点がありましたらお聞かせください。