inFablic | Fablic, inc. Developer's Blog.

フリマアプリ フリル (FRIL) を運営する Fablic の公式開発者ブログです。Fablic のデザイナー・エンジニア・ディレクターが情報発信していきます。

特定地域からのアプリインストール数を1時間単位で集計する(Firebase + BigQuery)

こんにちは。shobyです。

フリルでは全国で定期的にCMを放送しており、各CMの効果計測をする必要が生じています。

今回はFirebaseとBigQueryを利用して、特定地域からのアプリインストール数を1時間単位で集計する方法をご紹介します。

特定地域からのアプリインストール数を集計する

以前、CMの効果計測のために、地域別アクセス数を集計する方法をご紹介しましたが、アクセス数と同様に、インストール数もCMの効果計測に有効な指標の1つです。

しかし、1日に何度も放送されるCMの効果を測るには、CMを放映している地域ごとのインストール数を1時間以下の細かい単位で計測、集計をする必要があります。

そのような細かい単位で、地域を限定した集計を行うのは、多くの計測ツールではサポートされていません。

そのため今回は、Firebaseの生のイベントログをBigQueryで集計することで、特定地域からのアプリインストール数を1時間単位で集計できるようにしました。

FirebaseとBigQueryによる地域別アクセスの集計

CMの効果計測のため、東京からのインストール数を1時間単位で集計することを考えます。

Firebaseでは first_open というアプリの初回起動イベントが自動計測されているため、これを元にアプリのインストール数を測ることができます。

イベント名 トリガーされるタイミング
first_open ユーザーがアプリをインストールまたは再インストールした後に、初めて起動したとき。

Firebaseヘルプ 自動的に収集されるイベント より一部抜粋

また、Firebaseのイベントログには、アクセス元地域の情報が含まれており、大陸、国、地域、市区町村、という4つの段階で地域を判定することができます。 詳細はこちらの記事をご覧ください。

ここでは、「1時間以内にアプリを初回起動したユニークデバイス数」を「インストール数」として集計します。*1

実際に、東京からのインストール数を1時間単位で集計するクエリは以下のような形です。

SELECT
  STRFTIME_UTC_USEC(DATE_ADD(event_dim.timestamp_micros, 9, 'HOUR'), '%Y-%m-%d %H:00') AS install_datetime,
  user_dim.geo_info.region AS region,
  EXACT_COUNT_DISTINCT(user_dim.device_info.resettable_device_id) as install_count
FROM
  your_table_name
WHERE
  user_dim.geo_info.country = 'Japan'
  AND user_dim.geo_info.region = 'Tokyo'
  AND event_dim.name = 'first_open'
GROUP BY install_datetime, region
ORDER BY install_datetime ASC, install_count DESC

上記のクエリにより、1時間ごとのインストール数が得られます。

集計により得られるもの

CM前とCM後のインストール数を比較することで、地域ごとのCMの影響度を測ることができ、次回以降のCM放映地域の最適化に活用できます。

f:id:shoby:20170628103922p:plain

まとめ

FirebaseとBigQueryを利用することで、特定地域からのアプリインストール数を1時間単位で集計することができます。

これにより、特定地域のCM影響度を測ることができ、CM放映地域の最適化に活用できるようになりました。

*1:再インストールも含まれるため、厳密にはインストール数ではないですが、CMの効果計測という用途では問題ありません