【BigQuery】特定期間のイベントparamsを取得する方法

2019年5月30日

この記事はFirebaseをバックエンドサービスとして使っていて、本格的にデータの収集・分析を開始しようと思いFirebase Analyticsを使って色んなイベントを設定したは良いが今度は以下の問題に突き当たっている方が対象読者になります：

・Firebase Analyticsを設定したはいいが、管理画面からは合計で10個のパラメータ（*イベントではない）しか見ることができない。

・さらに１つのイベントに複数のパラメータを設定している場合、管理画面からだとパラメータ同士がまとまっていない。

・地域やユーザー情報もパラメータと紐づいていない

上記はBigQueryをFirebaseにリンクすると解決できます。
この記事ではBigQueryを使って設定した期間中のイベントのパラメータをクエリする方法を書いていきます。

下準備

BigQueryをFirebaseにリンクするにはFirebaseを従量制のプラン にアップグレードする必要があります。
以下の手順 にしたがって管理画面からリンクすることができます。

  1. Firebase にログインします。
  2. 歯車マークのボタンをクリックして、「プロジェクトの設定」 を選択します。
  3. 「プロジェクトの設定」 ページで 「統合」タブをクリックします。
  4. BigQuery カードで、「リンク」 をクリックします。
    プロジェクトで課金が有効になっていない場合は、「アップグレードしてリンク」をクリックします。Google 標準の課金設定プロセスに沿って操作します。
  5. 広告 ID をデータセットの一部としてエクスポートするかどうかを選択します。

BigQuery Export のスキーマ

イベントデータを出す前に、まずはAnalytics SDKがどんな情報を取得しているのかを理解しましょう。Firebaseの公式ドキュメント によると、Analytics SDKは以下の2種類の情報をロギングしています：

  • イベント: ユーザーの操作、システム イベント、エラーなど、アプリで起こっていること。
  • ユーザー プロパティ: 言語や地域など、ユーザー層を示す属性。自由に定義できます。

具体的なスキーマ は以下のようになっています：

フィールド名 データ型 説明
アプリ
app_info RECORD アプリについての情報を格納するレコード。
app_info.id 文字列 アプリのパッケージ名またはバンドル ID。
app_info.firebase_app_id 文字列 アプリに関連付けられている Firebase アプリ ID。
app_info.install_source 文字列 アプリをインストールしたストア。
app_info.version 文字列 アプリの versionName（Android の場合）または short bundle version。
端末
device RECORD 端末の情報を格納するレコード。
device.category 文字列 端末のカテゴリ（モバイル、タブレット、PC）。
device.mobile_brand_name 文字列 端末のブランド名。
device.mobile_model_name 文字列 端末のモデル名。
device.mobile_marketing_name 文字列 端末のマーケティング名。
device.mobile_os_hardware_model 文字列 OS から直接取得した端末のモデル情報。
device.operating_system 文字列 端末の OS。
device.operating_system_version 文字列 OS のバージョン。
device.vendor_id 文字列 IDFV（IDFA を収集していない場合にのみ使用）。
device.advertising_id 文字列 広告 ID / IDFA。
device.language 文字列 OS の言語。
device.time_zone_offset_seconds INTEGER GMT との時差（秒単位）。
device.is_limited_ad_tracking BOOLEAN 端末の「広告トラッキング抑制」が有効になっているかどうか。
ストリームとプラットフォーム
stream_id 文字列 ストリームの数値 ID。
platform 文字列 アプリケーションが構築されているプラットフォーム。
ユーザー
user_first_touch_timestamp INTEGER ユーザーが初めてアプリを開いた時刻（ミリ秒単位）。
user_id 文字列 setUserId API によって設定されるユーザー ID。
user_pseudo_id 文字列 ユーザーの仮の ID（アプリ インスタンス ID など）。
user_properties RECORD setUserProperty API によって設定される、ユーザー プロパティの繰り返しレコード。
user_properties.key 文字列 ユーザー プロパティの名前。
user_properties.value RECORD ユーザー プロパティの値を格納するレコード。
user_properties.value.string_value 文字列 ユーザー プロパティの文字列値。
user_properties.value.int_value INTEGER ユーザー プロパティの整数値。
user_properties.value.double_value FLOAT ユーザー プロパティの倍精度値。
user_properties.value.float_value FLOAT このフィールドは現在使用されていません。
user_properties.value.set_timestamp_micros INTEGER ユーザー プロパティが最後に設定された時刻（ミリ秒単位）。
user_ltv RECORD ユーザーのライフタイム バリューに関する情報を格納するレコード。このフィールドは当日テーブルでは使用されません。
user_ltv.revenue FLOAT ユーザーのライフタイム バリュー（収益）。このフィールドは当日テーブルでは使用されません。
user_ltv.currency 文字列 ユーザーのライフタイム バリュー（通貨）。このフィールドは当日テーブルでは使用されません。
キャンペーン 注: traffic_source のアトリビューションは、クロスチャネルのラストクリック に基づいています。traffic_source の値は、ユーザーがインストール後に次のキャンペーンを操作しても変更されません。
traffic_source RECORD ユーザーを最初に獲得したトラフィック ソースの名前。このフィールドは当日テーブルでは使用されません。
traffic_source.name 文字列 ユーザーを最初に獲得したマーケティング キャンペーンの名前。このフィールドは当日テーブルでは使用されません。
traffic_source.medium 文字列 ユーザーを最初に獲得したメディアの名前（有料検索、オーガニック検索、メールなど）。このフィールドは当日テーブルでは使用されません。
traffic_source.source 文字列 ユーザーを最初に獲得したネットワークの名前。このフィールドは当日テーブルでは使用されません。
地域
geo RECORD ユーザーの位置情報を格納するレコード。
geo.continent 文字列 イベントが報告された大陸（IP アドレス ベース）。
geo.sub_continent 文字列 イベントが報告された亜大陸（IP アドレスベース）。
geo.country 文字列 イベントが報告された国（IP アドレス ベース）。
geo.region 文字列 イベントが報告された地域（IP アドレス ベース）。
geo.metro 文字列 イベントが報告された大都市圏（IP アドレスベース）。
geo.city 文字列 イベントが報告された都市（IP アドレス ベース）。
イベント
event_date 文字列 イベントが記録された日付（アプリの登録タイムゾーンにおける日付を YYYYMMDD 形式で示したもの）。
event_timestamp INTEGER 該当クライアントでイベントが記録された時刻（ミリ秒単位、UTC）。
event_previous_timestamp INTEGER 該当クライアントで前回イベントが記録された時刻（ミリ秒単位、UTC）。
event_name 文字列 イベントの名前です。
event_params RECORD このイベントに関連付けられたパラメータを格納する繰り返しレコード。
event_params.key 文字列 イベント パラメータのキー。
event_params.value RECORD イベント パラメータの値を格納するレコード。
event_params.value.string_value 文字列 イベント パラメータの文字列値。
event_params.value.int_value INTEGER イベント パラメータの整数値。
event_params.value.double_value FLOAT イベント パラメータの倍精度値。
event_params.value.float_value FLOAT イベント パラメータの浮動小数点値。このフィールドは現在使用されていません。
event_value_in_usd FLOAT イベントの「値」パラメータの通貨換算値（米ドル単位）。
event_bundle_sequence_id INTEGER これらのイベントをまとめたアップロード用バンドルのシーケンシャル ID。
event_server_timestamp_offset INTEGER データの収集時とアップロード時のタイムスタンプの間隔（マイクロ秒単位）。
ウェブ
web_info RECORD ウェブデータの情報のレコード。
web_info.hostname 文字列 ログに記録されたイベントに関連付けられたホスト名。
web_info.browser 文字列 ユーザーがコンテンツを閲覧したブラウザ。
web_info.browser_version 文字列 ユーザーがコンテンツを閲覧したブラウザのバージョン。

特定期間のイベントパラメータの取得

冒頭でも書いた通り、特定期間のイベントパラメータを取得することを目標とします。例えば、Youtubeのような動画再生アプリの場合はどの動画が再生されたのか、どのアカウントが再生したかなどのデータが取られていると思います。

掻い摘んだ例として、以下のようなイベントが設定されていたとしましょう：

event name = “user_played_video” //動画が再生されたら発火されるイベントの名称
param A key = “video_id” //動画のID
param B key = “account_id” //再生したユーザーのアカウントID

先ほどのデータスキーマをもう一度見てみましょう。取得したいデータは「イベント」項目の中にあります。

フィールド名 データ型 説明
event_name 文字列 イベントの名前です。
event_params RECORD このイベントに関連付けられたパラメータを格納する繰り返しレコード。
event_params.key 文字列 イベント パラメータのキー。
event_params.value RECORD イベント パラメータの値を格納するレコード。
event_params.value.string_value 文字列 イベント パラメータの文字列値。
event_params.value.int_value INTEGER イベント パラメータの整数値。
event_params.value.double_value FLOAT イベント パラメータの倍精度値。
event_params.value.float_value FLOAT イベント パラメータの浮動小数点値。このフィールドは現在使用されていません。
まず、イベント名はそのまま「event_name」ですね。
次に、パラメータの情報は全て「event_params」というレコードの中に格納されています。

この「event_params」の中にはさらに

  1. key //今回の例でいうと「video_id」や「account_id」にあたります。
  2. value //今回の例でいうと「再生された動画のid」や「再生したユーザーのアカウントid」に当たります。

があり、「value」の中にはそれぞれ

  1. string_value
  2. int_value
  3. double_value
  4. float_value

が格納されています。これは実際にイベントのパラメータの値の型に合わせて取得する必要があります。

「動画のID」をintとして送信しているのであれば、event_params.value.int_valueを取得する必要があります。
*文字列として送信しているのであればstring_valueになります。

それでは実際のクエリ構文を見ていきましょう。上記の例に当てはめると、以下のようになります：

読み砕いていきましょう。

ここでは各イベントパラメータの値をキーごとに取得しています。

UNNEST演算子は配列の要素が1行となる値テーブルを返します。そしてサブクエリでWHEREを使って返ってきたテーブルをフィルタリング出来るので、event_params.keyが「video_id」のint_valueをSELECTしています。

つまり、以下のようなイベントデータがあった場合：

event_name event_params.key event_params.value.string_value event_params.value.int_value event_params.value.float_value event_params.value.double_value
user_played_video video_id null 3 null null
account_id null 77 null null

返ってくる値テーブルは以下のような形になります：

+——————–+———————+
| video_id        | account_id     |
+——————–+———————+
| 3                  | 77                 |
+——————–+———————+

続くクエリ構文は説明いらずかもしれませんが、

の「`」が少し特殊で「’」だとエラーになります。以前、コピペでメモ帳などに貼り付けた時に勝手に変換されてエラーになってハマったことがあったので、この部分でエラーが出ている方は公式が出しているクエリのサンプルページ からコピペしてみてください。

イベント以外のデータ

②ではイベントのパラメータのみを取得しましたが、Firebaseが自動で収集しているデータと組み合わせると分析に役立つかもしれません。

▼自動で収集している情報の例

・地域情報
-この値はイベントが送信された時のIPアドレスがベースになっているので、基地局の設定次第では位置情報に大きなブレがあったりします。
・プラットフォーム
-iOSやAndroidなど
・デバイスの情報
-デバイスのモデル、ブランド、OSなど

先ほどのクエリ構文に、自動で収集している情報の一部を組み合わせた例が下記です：

クエリの保存

毎回クエリ構文をコピペするのも面倒なので、割と最近クエリの保存機能が追加されました。「実行」ボタンの隣の「クエリを保存」ボタンから名前付きで複数のクエリを保存することができます。

保存したクエリは左の「保存したクエリ」メニューの「個人用クエリ」か「プロジェクトクエリ」から開くことができます。

BigQueryの料金

BigQuery(asia-northeast1)の料金表 は以下です：

オペレーション 料金 詳細
アクティブ ストレージ $0.023 per GB 毎月 10 GB まで無料。詳細については、ストレージの料金 をご覧ください。
長期保存 $0.016 per GB 毎月 10 GB まで無料。詳細については、ストレージの料金 をご覧ください。
BigQuery Storage API Unavailable BigQuery Storage API は無料枠 に含まれません。
ストリーミング挿入 $0.012 per 200 MB 挿入に成功した行が課金対象になります。最小サイズ 1 KB で各行が計算されます。詳細については、ストリーミングの料金 をご覧ください。
クエリ（オンデマンド） $8.55 per TB 毎月 1 TB まで無料。詳細はオンデマンド料金 をご覧ください。
クエリ（月定額） $12,000 per 500 slots 追加のスロットは 500 スロット単位でご購入いただけます。詳細については、月定額 料金をご覧ください。
クエリ（年定額） $10,200 per 500 slots 追加のスロットは 500 スロット単位でご購入いただけます。請求は月単位で行われます。詳細については、年定額 料金をご覧ください。

毎日大量のデータをクエリしない限りは大体無料枠に収まると思います。

とはいえ、何も確認せずに使い続けると痛い目を見ることも・・・

「まぁ、多分大丈夫だろう」なんて高を括らずデータ処理量に応じた費用を計算することが大事です。

BigQueryにはdry run というオプションが用意されており、実際にクエリを走らせる前に走査バイト数を知ることが出来ます。これで費用の大体の予測は可能ですね。BigQueryの料金面に関しては下記の記事が参考になります。

以上、BigQueryを使って特定期間のイベントparamsを取得する方法でした。少しでもご参考になりましたら幸いです。

Firebase