Firebase イベント データ分析のための Google Cloud 活用方法 (2) SQL の基礎
本連載について
モバイルやウェブ アプリケーションの KPI を改善させるためには、ユーザーの日々の利用状況や行動を適切に把握した上で、施策を検討して実施することが重要となります。特に、実際に KPI 改善施策を検討して実施する企画者やマーケターが、自分自身でデータを分析し、その結果にもとづいて施策を検討できるようになることは、「現状把握 → 施策検討 → 施策実施 → 効果測定」という KPI 改善のサイクルを迅速に回すために必要不可欠となっています。
Firebase SDK で開発されているアプリケーションについては、Google Analytics for Firebase を利用することで、ユーザーのイベント データを簡単に Google Analytics で収集できるようになっています。さらに、収集したイベント データを BigQuery へエクスポートすることにより、企画者やマーケターが BigQuery やデータポータルなどを活用して、自分自身でデータの可視化やレポーティング、詳細なユーザー行動の分析などを行い、データ分析にもとづいた KPI 改善施策の検討と実施を迅速に行うことができるようになります。
本連載では、KPI 改善施策を検討して実施されるサービスの企画者やマーケターの方へむけて、Firebase イベント データを分析するための Google Cloud のプロダクトの活用方法をご紹介します。
連載記事一覧
本記事について
BigQuery で Firebase イベント データを集計、分析するためには、SQL を記述して実行する必要があります。第 2 回目の本記事では、BigQuery 公開サンプル データセットを使用しながら、SQL の基本的な書き方をご紹介します。BigQuery 公開サンプル データセットの表示方法や Firebase イベント データのスキーマ情報については、前回の第 1 回目の記事「Firebase イベント データのスキーマ」をご参照ください。
1. select, from, where
select
は、取得するカラムを指定するために使用するものです。select カラム1, カラム2, …
という形式で、取得するカラムを指定します。
from
は、検索対象のテーブルを指定するために使用するものです。from テーブル名
という形式で、検索対象のテーブルを指定します。
where
は、検索条件を指定するために使用するものです。where カラム1 = 値
などの形式で、検索条件を指定します。
以下のクエリは、events_20180901
テーブルから、各イベントの event_date
, event_timestamp
, user_pseudo_id
, event_name
カラムの値を取得するものです。
select
event_date
, event_timestamp
, user_pseudo_id
, event_name
from
`firebase-public-project.analytics_153293282.events_*`
where
_table_suffix = '20180901'
検索対象テーブルの events_*
の部分は、「ワイルドカード テーブル」を使用したテーブルの指定となっています。 events_YYYYMMDD
などの形式の複数のテーブルを、ひとつの記述で簡潔に検索対象テーブルとして指定する際に、この「ワイルドカード テーブル」を使用します。 where
句の _table_suffix = '20180901'
は、 events_20180901
テーブルのみを検索対象とすることを指定しています。このように、 _table_suffix
疑似列を使用することで、「ワイルドカード テーブル」の取得対象テーブルを絞り込むことができます。「ワイルドカード テーブル」と _table_suffix
疑似列の詳細については、Google Cloud の公式ドキュメント「ワイルドカード テーブルを使用した複数テーブルに対するクエリ | BigQuery | Google Cloud」をご参照ください。
前述のクエリを BigQuery Web UI で実行します。前述のクエリをコピーし、BigQuery Web UI の「クエリエディタ」へ貼り付け、「実行」ボタンをクリックします。
クエリの実行結果が、画面下部の「クエリ結果」エリアに表示されます。
このクエリ実行により、2018 年 9 月 1 日に発生した Firebase イベントの、日付とタイムスタンプ(UNIX 時間)、ユーザ ID 、イベント名を取得することができました。
2. order by
order by カラム
を指定することで、取得結果を特定のカラムの昇順で並び替えることができます。以下のクエリを見てみましょう。
select
event_date
, event_timestamp
, user_pseudo_id
, event_name
from
`firebase-public-project.analytics_153293282.events_*`
where
_table_suffix = '20180901'
order by
event_timestamp
このクエリは、前述の節「1. select, from, where」で実行したクエリとほぼ同じですが、最後に order by event_timestamp
の記述がある点だけが異なります。これにより、取得結果をイベントのタイムスタンプの昇順で並び替えることができるようになります。このクエリの実行結果は、以下のようになります。
このクエリ実行により、2018 年 9 月 1 日に発生した Firebase イベントの、日付とタイムスタンプ、ユーザ ID 、イベント名を、タイムスタンプの昇順で並び替えたものを取得することができました。
3. order by … desc
order by カラム desc
を指定することで、取得結果を特定のカラムの降順で並び替えることができます。前述の節「2. order by」のクエリでは、取得結果を特定のカラムの昇順で並び替えましたが、末尾に desc
を指定することで、並び順を降順にすることができます。なお、前述の order by カラム
は order by カラム asc
の省略形になります。以下のクエリを見てみましょう。
select
event_date
, event_timestamp
, user_pseudo_id
, event_name
from
`firebase-public-project.analytics_153293282.events_*`
where
_table_suffix = '20180901'
order by
event_timestamp desc
前述の節「2. order by」のクエリの末尾に desc
が追記されたものになります。このクエリの実行結果は、以下のようになります。
このクエリ実行により、2018 年 9 月 1 日に発生した Firebase イベントの、日付とタイムスタンプ、ユーザー ID 、イベント名を、タイムスタンプの降順で並び替えたものを取得することができました。
4. limit
limit
を指定することで、取得結果の件数を制限することができます。以下のクエリを見てみます。
select
event_date
, event_timestamp
, user_pseudo_id
, event_name
from
`firebase-public-project.analytics_153293282.events_*`
where
_table_suffix = '20180901'
order by
event_timestamp desc
limit 2
クエリの末尾に limit 2
が指定されています。さらに、このクエリでは order by event_timestamp desc
も指定されているため、このクエリは、タイムスタンプの降順で並び替えられた検索結果の、上位 2 件のみを取得するものとなります。このクエリの実行結果は、以下のようになります。
クエリの実行結果が 2 件のみに制限されていることがわかります。
5. where … and …
前述の節「1. select, from, where」で where
句をご紹介しました。 where
句では、 where ... and ...
と検索条件をつなげることで「… かつ …」という検索条件を指定することができます。以下のクエリを見てみます。
select
event_date
, event_timestamp
, user_pseudo_id
, event_name
from
`firebase-public-project.analytics_153293282.events_*`
where
_table_suffix = '20180901'
and event_name = 'screen_view'
order by
event_timestamp desc
limit 2
このクエリでは where _table_suffix = '20180901' and event_name = 'screen_view'
という検索条件が指定されています。これは、「 _table_suffix
が '20180901'
であり、かつ event_name
が 'screen_view'
である」という検索条件を表しています。このクエリの実行結果は、以下のようになります。
このクエリ実行結果より、2018 年 9 月 1 日に発生し、かつイベント名が screen_view
である Firebase イベントの、日付とタイムスタンプ、ユーザ ID 、イベント名を、タイムスタンプの降順で並び替えたものを取得することができました。
6. select distinct
select distinct
を指定することで、重複行を排除した結果を取得することができます。以下のクエリを見てみます。
select distinct
device.operating_system
from
`firebase-public-project.analytics_153293282.events_*`
where
_table_suffix = '20180901'
order by
device.operating_system
このクエリは、2018 年 9 月 1 日に発生した Firebase イベントの、端末の OS を取得するものになっています。 select distinct
が指定されているため、重複行を排除した結果が取得されます。このクエリの実行結果は、以下のようになります。
2018 年 9 月 1 日に発生した Firebase イベントは大量にあるはずですが、このクエリでは select distinct
が指定されているため、重複行が排除されて、 ANDROID
と IOS
の 2 件だけが取得されています。
それでは、この select distinct
を使用して、2018 年 9 月 1 日に発生した Firebase イベントの、イベント名の一覧を取得してみましょう。「どのような種類のイベントがあるのか」など、データの概要や全体像を把握することは、詳細なデータ分析を行う際に必要な作業となります。クエリとその実行結果は、以下のようになります。
select distinct
event_name
from
`firebase-public-project.analytics_153293282.events_*`
where
_table_suffix = '20180901'
order by
event_name
ad_reward
や app_exception
など、合計 31 種類のイベントが 2018 年 9 月 1 日に発生していたことがわかりました。
7. count
count(...)
を使用することで、検索対象のデータの件数を取得することができます。以下のクエリを見てみましょう。
select
count(1) as events
from
`firebase-public-project.analytics_153293282.events_*`
where
_table_suffix = '20180901'
select
句に count(1)
が指定されています。このクエリは、2018 年 9 月 1 日に発生したイベントの数を取得するものになっています。 count(...)
内で 1
が指定されていますが、 null
以外であればどのような値でも構いません。また、 as events
という記述があります。これは、 count(1)
の列に対して events
という別名を付与する、という指定を行うものになります。このクエリの実行結果は、以下のようになります。
このクエリ実行結果より、2018 年 9 月 1 日に発生した Firebase イベントの数が、ちょうど 50,000 であったことがわかります。
8. count distinct
count(distinct ...)
を使用することで、重複を排除した件数を取得することができます。以下のクエリを見てみましょう。
select
count(distinct user_pseudo_id) as dau
from
`firebase-public-project.analytics_153293282.events_*`
where
_table_suffix = '20180901'
このクエリは、2018 年 9 月 1 日に発生した Firebase イベントのユーザー ID の、重複を排除した件数を取得するものになっています。つまり、2018 年 9 月 1 日の DAU(デイリー アクティブ ユーザー数)を取得するものになっています。このクエリの実行結果は、以下のようになります。
このクエリ実行結果より、2018 年 9 月 1 日の DAU が 465 人であったことがわかります。
9. group by
group by カラム
を指定することで、指定したカラムの値が同じデータでグループ化し、そのグループごとに集計を行うことができます。以下のクエリを見てみましょう。
select
event_name
, count(1) as events
from
`firebase-public-project.analytics_153293282.events_*`
where
_table_suffix = '20180901'
group by
event_name
order by
event_name
このクエリは、2018 年 9 月 1 日に発生した Firebase イベントを、イベント名でグループ化し、そのグループごとにイベント数を算出するものです。このクエリの実行結果は、以下のようになります。
このクエリ実行結果より、2018 年 9 月 1 日に、どのようなイベントがどのくらい発生したのかがわかります。例えば、 ad_reward
というイベントが 12 件発生し、 app_exception
というイベントが 24 件発生したことなどがわかります。
表示しているデータの数が多くなると、文字情報だけでデータの特徴を把握することが難しくなります。BigQuery Web UI では、クエリの実行結果をデータポータルを使って可視化する機能があります。この機能を使用して、前述のクエリの実行結果を可視化してみます。
「クエリ結果」エリアの「データを探索」をクリックし、そこで展開される「データポータルで調べる」をクリックします。
以下のようなデータポータルのエクスプローラのページが表示されます。
画面右上の「グラフ」エリアで、「円グラフ」を選択します。
画面右下の「指標」で、「events」を選択します。
作成された円グラフを見ると、「2018 年 9 月 1 日において、 screen_view
のイベントが最も多く発生しており、そのイベントの発生件数は、全体のイベントの発生件数の 40.9% を占めている」ということがわかります。また、次に多く発生しているイベントが user_engagement
であり(全体の 23.1%)、その次が level_start_quickplay
イベントである(全体の 8.2%)、ということもわかります。このように、データポータルを活用して BigQuery のクエリの実行結果をグラフ化して可視化することにより、データの全体像や特性を簡単に把握することができるようになります。
10. between
where
句で、 カラム between A and B
と指定することにより、「カラムの値が A 以上かつ B 以下である」データを抽出することができます。以下のクエリを見てみましょう。
select
parse_date('%Y%m%d', event_date) as event_date
, count(distinct user_pseudo_id) as dau
from
`firebase-public-project.analytics_153293282.events_*`
where
_table_suffix between '20180901' and '20180930'
group by
event_date
order by
event_date
このクエリの where
句では _table_suffix between '20180901' and '20180930'
が指定されています。これは、 events_20180901
から events_20180930
までのテーブルを検索対象とする指定となっています。つまり、このクエリは、2018 年 9 月に発生した Firebase イベントを検索対象としています。
また、このクエリでは、イベントの日付でデータをグループ化して、イベントの日付ごとに、ユーザー ID の重複を除いた件数を取得しています。つまり、このクエリは、2018 年 9 月における、各日の DAU を取得するものになっています。
なお、 select
句の parse_date('%Y%m%d', event_date)
は、 YYYYMMDD
の形式の文字列である event_date
カラムの型を、日付型に変換するということを行っています。このクエリの実行結果をデータポータルで可視化する際に、このカラムの値が日付型であると自動的に識別されるようにするために、ここでは、クエリの方で event_date
カラムの型を文字列型から日付型へ変換しています。
このクエリの実行結果は、以下のようになります。
2018 年 9 月における、各日の DAU を取得することができました。このクエリ実行結果についても、データポータルでグラフを作成して可視化してみます。前節と同様に、「クエリ結果」エリアの「データを探索」をクリックし、そこで展開される「データポータルで調べる」をクリックし、データポータルのエクスプローラを起動します。
画面右上の「グラフ」エリアで、「時系列グラフ」を選択します。
画面右下の「指標」で、「dau」を選択します。
2018 年 9 月における、各日の DAU をグラフ化することができました。2018 年 9 月において、DAU が最も多い日が 27 日(644 人)であり、最も少ない日が 15 日(432 人)であることが、このグラフから簡単に読み取ることができます。
11. max, min, avg, テーブル サブクエリ
max(カラム)
, min(カラム)
, avg(カラム)
はそれぞれ、カラムの最大値、最小値、平均値を取得します。
クエリの from
句の中に記述されたクエリのことを、「テーブル サブクエリ」と呼びます。外側のクエリは、テーブル サブクエリの結果をテーブルとして扱うことができます。
以下のクエリを見てみましょう。
select
max(dau) as max_dau
, min(dau) as min_dau
, round(avg(dau), 1) as avg_dau
from
(
select
parse_date('%Y%m%d', event_date) as event_date
, count(distinct user_pseudo_id) as dau
from
`firebase-public-project.analytics_153293282.events_*`
where
_table_suffix between '20180901' and '20180930'
group by
event_date
)
このクエリでは、 from
句の中にテーブル サブクエリが記述されています。このテーブル サブクエリの内容は、前節「10. between」でご紹介したクエリと全く同じものになっています。外側のクエリでは、このテーブル サブクエリの結果をテーブルとして利用し、これに対して集計処理を実施して、DAU の最大値、最小値、平均値を取得しています。結果として、このクエリは、2018 年 9 月の各日の DAU の、最大値、最小値、平均値を取得するものになっています。なお、 round(avg(dau), 1)
は、DAU の平均値を小数第 2 位で四捨五入する、ということを行っています。このクエリの実行結果は、以下のようになります。
このクエリ実行結果により、2018 年 9 月の各日の DAU の、最大値、最小値、平均値はそれぞれ、644, 432, 511.8 であることがわかります。
12. unnest
unnest
を使用することで、配列を行に変換することができます。 events_YYYYMMDD
テーブルでは、 event_params
が繰り返しカラムとして定義されており、イベントに設定される複数のパラメータの情報が、全てこのカラムに格納されています。 unnest
を使用することで、 event_params
カラムに格納されている複数のパラメータの情報を行に変換し、ひとつのパラメータの情報が 1 行となるように変換することができます。以下のクエリを見てみましょう。
select
e.user_pseudo_id
, cast(sum(p.value.int_value) / 1000 / 60 as int64) as playtime_min
from
`firebase-public-project.analytics_153293282.events_*` e
cross join
unnest(event_params) p
where
e._table_suffix = '20180901'
and p.key = 'engagement_time_msec'
group by
e.user_pseudo_id
order by
playtime_min desc
このクエリでは、 unnest(event_params)
が指定されています。これは、繰り返しカラムである event_params
の複数の値を、一つ一つの行に変換するということを行っています。また、末尾に p
を指定することで、この変換結果に p
という別名を設定し、クエリ内でこの名前で変換結果を参照できるようにしています。さらに、 cross join
を指定することで、 events_YYYYYMMDD
テーブルの行と event_params
の変換結果の行を結合させて、1 行でその両方のデータが保持されるようにしています。
where
句の検索条件では、 p.key = 'engagement_time_msec'
が指定されています。これは、Firebase イベントの、 engagement_time_msec
というパラメータを抽出することを表しています。この engagement_time_msec
パラメータには、 engagement_time_msec
パラメータが含まれている前回の Firebase イベント送信時からの経過時間(ミリ秒)が格納されています。つまり、このパラメータの値をユーザー単位で集計することで、ユーザーごとのアプリケーション起動時間を算出することができます。engagement_time_msec
パラメータの詳細については、「Google Developers Japan: セッションとユーザー エンゲージメントに関わるアナリティクスの変更のお知らせ」をご参照ください。
つまり、このクエリでは、2018 年 9 月 1 日における、各ユーザーのアプリケーション起動時間(分)を算出し、そのアプリケーション起動時間が長い順に並び替えて結果を取得する、ということが行われています。このクエリの実行結果は、以下のようになります。
このクエリ実行結果により、2018 年 9 月 1 日において、ユーザー ID が 86F19DAB73E484FB7DD2F805DDD7957D
であるユーザーが最も長い時間アプリを起動し、そのアプリケーション起動時間は 435
分であったことがわかります。
13. exists
exists(サブクエリ)
を使用することで、サブクエリで結果が取得されるデータのみを抽出することができます。以下のクエリを見てみましょう。
select
day
, count(1) as uu
, round(count(1) / sum(countif(day = 0)) over(), 3) as retention
from
(
select distinct
date_diff(
parse_date('%Y%m%d', e.event_date), '2018-08-01', day
) as day
, e.user_pseudo_id
from
`firebase-public-project.analytics_153293282.events_*` e
where
e._table_suffix between '20180801' and '20180831'
and exists (
select
1
from
`firebase-public-project.analytics_153293282.events_*` o
where
o._table_suffix = '20180801'
and o.event_name = 'first_open'
and o.user_pseudo_id = e.user_pseudo_id
)
)
group by
day
order by
day
このクエリの、一番外側のクエリの from
句の中で、テーブル サブクエリが使用されています。テーブル サブクエリでは、検索条件 e._table_suffix between '20180801' and '20180831'
が指定されているため、このテーブル サブクエリは 2018 年 8 月の Firebase イベントを検索するものになっています。さらに、 exists(サブクエリ)
も検索条件として指定されており、このサブクエリで結果が取得されるデータのみが抽出されるようになっています。
サブクエリは、以下のクエリになっています。
select
1
from
`firebase-public-project.analytics_153293282.events_*` o
where
o._table_suffix = '20180801'
and o.event_name = 'first_open'
and o.user_pseudo_id = e.user_pseudo_id
first_open
イベントは、アプリケーションのインストール後の初回起動時に発生するイベントです。そのため、このサブクエリでは、「2018 年 8 月 1 日に初めてアプリケーションを起動したユーザー」を取得するものになっています。
テーブル サブクエリの select
句で、 date_diff(parse_date('%Y%m%d', e.event_date), '2018-08-01', day) as day
が指定されています。これは、2018 年 8 月 1 日から Firebase イベントの発生日までの日数を取得するものになっています。以上より、テーブル サブクエリは、「2018 年 8 月において、8 月 1 日に初めてこのアプリケーションを起動したユーザーが、その何日後にアプリケーションを起動したか」という情報を取得するものになります。
一番外側のクエリでは、テーブル サブクエリに対して day
(2018 年 8 月 1 日から Firebase イベントの発生日までの日数)単位で集計が行われています。 select
句の count(1) / sum(countif(day = 0)) over()
は、「2018 年 8 月 1 日に初めてアプリケーションを起動したユーザー数に占める、それ以降の特定の日にアプリケーションを起動したユーザー数の割合」を取得しています。
結果として、クエリ全体としては、「2018 年 8 月 1 日に初めてアプリケーションを起動したユーザーの、0 日後から 30 日後までの継続率」を算出するものになっています。このクエリの実行結果は、以下のようになります。
このクエリ実行結果より、以下のことなどがわかります。
- 2018 年 8 月 1 日に初めてアプリケーションを起動したユーザーは 54 人である
- 2018 年 8 月 1 日に初めてアプリケーションを起動したユーザーのうち、その翌日に再びアプリケーションを起動したユーザは 13 人であり、翌日継続率は 24.1% である
- 2018 年 8 月 1 日に初めてアプリケーションを起動したユーザーのうち、その 3 日後に再びアプリケーションを起動したユーザは 8 人であり、3 日後継続率は 14.8% である
14. left outer join
前節「13. exists」の「クエリ実行結果」を見ると、 day
の値が 14
や 15
であるデータが抜けていることがわかります。
これらの日に該当するデータが 1 件も存在しなかったため、これらの日のデータが取得結果から欠落しています。この節では、 left outer join
を使用して、データが存在しない日は、 uu
, retention
ともに 0
でデータが取得されるようにします。
left outer join
は 2 つテーブルを外部結合します。外部結合とは、2 つのテーブルの結合方法のひとつであり、左側のテーブルに存在するデータのうち、たとえ右側のテーブルに対応するデータが存在しなくても、必ず左側のテーブルのデータを全て取得結果に含むようにする結合方法です。以下のクエリを見てみましょう。
select
d.day
, count(e.day) as uu
, round(count(e.day) / sum(countif(d.day=0)) over(), 3) as retention
from
(
select
day
from
unnest(generate_array(0, 30)) as day
) d
left outer join
(
select distinct
date_diff(
parse_date('%Y%m%d', e.event_date), '2018-08-01', day
) as day
, e.user_pseudo_id
from
`firebase-public-project.analytics_153293282.events_*` e
where
e._table_suffix between '20180801' and '20180831'
and exists (
select
1
from
`firebase-public-project.analytics_153293282.events_*` o
where
o._table_suffix = '20180801'
and o.event_name = 'first_open'
and o.user_pseudo_id = e.user_pseudo_id
)
) e
on
e.day = d.day
group by
d.day
order by
d.day
前節「13. exists」でご紹介したクエリと似ていますが、今回は、以下のテーブル サブクエリが追加され、このテーブル サブクエリの結果に対して、前節「13. exists」のクエリのテーブル サブクエリの結果が外部結合されるようになっています。
(
select
day
from
unnest(generate_array(0, 30)) as day
) d
このテーブル サブクエリでは、 0
から 30
までの値を含む配列を生成し、それを行に変換して、各値を day
カラムとして参照できるようにしています。このテーブル サブクエリの結果に対して、前節「13. exists」のクエリのテーブル サブクエリの結果を外部結合することで、後者の方に存在しない日であっても、最終的な取得結果では uu
, retention
ともに 0
でデータが取得されるようになります。このクエリの実行結果は、以下のようになります。
このクエリ実行により、 day
の値が 0
から 30
までのデータが全て取得結果に含まれるようになりました。
このクエリ実行結果をデータポータルでグラフ化します。「クエリ結果」エリアの「データを探索」をクリックし、そこで展開される「データポータルで調べる」をクリックし、データポータルのエクスプローラを起動します。
画面右上の「グラフ」で「折れ線グラフ」を選択します。
画面右下の「指標」で「retention」を選択し、「並び替え」で「day」、「昇順」を選択します。
画面右下の「スタイル」で「ポイントを表示」をチェックします。
以上の操作で、「2018 年 8 月 1 日に初めてアプリケーションを起動したユーザーの、0 日後から 30 日後までの継続率」をグラフ化することができました。
おわりに
第 2 回目の本記事では、BigQuery 公開サンプル データセットを使用しながら、SQL の基本的な書き方をご紹介しました。次回の第 3 回目の記事「データポータルでのレポート作成」では、Google のレポート作成ツールであるデータポータルを活用して、KPI をグラフ化して表示するレポートの作成方法をご紹介します。