Firebase イベントデータ分析のための Google Cloud 活用方法 (2) SQL の基礎

Published in

google-cloud-jp

31 min readApr 20, 2020

本連載について

モバイルやウェブアプリケーションの 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 をグラフ化して表示するレポートの作成方法をご紹介します。

Firebase イベント データ分析のための Google Cloud 活用方法 (2) SQL の基礎