読者です 読者をやめる 読者になる 読者になる

inFablic

Fablic開発者ブログ

アプリの地域別アクセス数を10分単位で集計する（Firebase + BigQuery）

こんにちは。shobyです。

現在、フリルでは全国でCMを放送しており、各CMの効果計測をする必要が生じました。

今回はCMの効果計測という題材を元に、FirebaseとBigQueryを用いて、アプリの地域別アクセス数を10分単位で集計する方法をお伝えします。

CMの効果計測

CMの効果計測には様々な指標があると思いますが、「CM放送により増加したアクセス数」は、効果計測に有効な指標の一つです。

単純なアクセス数を計測するのであれば難しくありませんが、どの地域、どの番組におけるCMの効果が高かったかを調査しようと思うと、1時間以下の細かい単位で、地域別のアクセス数を正確に計測する必要が出てきます。

以下の表は実際のCM放送スケジュールの一部です。1時間の番組中に15秒しか流れないCMの影響を測るのは、通常の計測ツールでは難しいことがお分かりいただけるかと思います。*1

f:id:shoby:20170607105357p:plain

そのため、今回はFirebaseの生のイベントログを、BigQueryで集計することで、アプリの地域別アクセス数を10分単位で集計できるようにしました。

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

以前こちらの記事にも書いたようにFirebseとBigQueryを組み合わせることで、アプリの全イベントログの生データが参照できるようになります。

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

フィールド名 データ型 説明
user_dim.geo_info RECORD ユーザーの地域情報の記録。
user_dim.geo_info.continent STRING IP アドレスに基づく、イベントのレポート元の大陸。
user_dim.geo_info.country STRING IP アドレスに基づく、イベントのレポート元の国。
user_dim.geo_info.region STRING IP アドレスに基づく、イベントのレポート元の地域。
user_dim.geo_info.city STRING IP アドレスに基づく、イベントのレポート元の市区町村。

BigQuery Export のスキーマより一部抜粋

また、Firebaseでは session_start というセッション開始イベント自動で計測されているため、これを元にアプリの起動回数が測れます。

今回は、「10分以内にアプリを起動したユニークデバイス数」を「アクセス数」として集計することにしました。

実際の集計クエリは以下のような形です。

SELECT
  CONCAT(SUBSTR(STRFTIME_UTC_USEC(DATE_ADD(event_dim.timestamp_micros, 9, 'HOUR'), '%Y-%m-%d %H:%M'),1, 15), '0') AS access_datetime,
  user_dim.geo_info.region AS region,
  EXACT_COUNT_DISTINCT(user_dim.device_info.resettable_device_id) as access_count
FROM
  your_table_name
WHERE
  user_dim.geo_info.country = 'Japan'
  AND event_dim.name = 'session_start'
GROUP BY access_datetime, region
ORDER BY access_datetime ASC, count DESC

Firebaseのイベントログの時刻はUTCで保存されており、DATE_ADDという関数でJSTに変換する必要があります。

また、ミリ秒単位のtimestampで保存されているため、STRFTIME_UTC_USECという関数を使うと任意のフォーマットの文字列に変換することができます。

集計結果の可視化

上記のクエリで集計した結果を元に、Redashで以下のようなグラフを生成し、可視化しました。

f:id:shoby:20170606214759p:plain

RedashのグラフにはStackingという機能がついており、同一時間帯のアクセスを、重ねて表示することができます。

これにより、全アクセス数に占める各地域のアクセス数を可視化することができます。

集計により得られたこと

地域別アクセス数を集計することにより、番組レベルでCMの影響を計測できるようになりました。 これにより、フリルと相性の良い視聴者が多い番組はどれか、といった詳細な分析を行えるようになりました。

まとめ

フリルでは全国放送されるCMの効果計測のために、1時間以下の細かい単位で地域別のアクセス数を計測する必要がありました。

FirebaseとBigQuery用いることで、10分といった細かい単位で、地域別のアクセス数を集計することができます。

これにより、番組レベルでCMの効果計測を行うことができるようになりました。

*1:Google Analyticsでも地域別のアクセス数を確認することはできますが、1日以下の細かい単位での集計はすることができません