Firebase イベントデータ分析のための Google Cloud 活用方法 (4) BigQuery でのデータの探索

Published in

google-cloud-jp

11 min readApr 21, 2020

本連載について

モバイルやウェブアプリケーションの KPI を改善させるためには、ユーザーの日々の利用状況や行動を適切に把握した上で、施策を検討して実施することが重要となります。特に、実際に KPI 改善施策を検討して実施する企画者やマーケターが、自分自身でデータを分析し、その結果にもとづいて施策を検討できるようになることは、「現状把握 → 施策検討 → 施策実施 → 効果測定」という KPI 改善のサイクルを迅速に回すために必要不可欠となっています。

Firebase SDK で開発されているアプリケーションについては、Google Analytics for Firebase を利用することで、ユーザーのイベントデータを簡単に Google Analytics で収集できるようになっています。さらに、収集したイベントデータを BigQuery へエクスポートすることにより、企画者やマーケターが BigQuery やデータポータルなどを活用して、自分自身でデータの可視化やレポーティング、詳細なユーザー行動の分析などを行い、データ分析にもとづいた KPI 改善施策の検討と実施を迅速に行うことができるようになります。

本連載では、KPI 改善施策を検討して実施されるサービスの企画者やマーケターの方へむけて、Firebase イベントデータを分析するための Google Cloud のプロダクトの活用方法をご紹介します。

連載記事一覧

本記事について

第 4 回目の本記事では、BigQuery で SQL を実行することで、データを探索する方法をご紹介します。

前回の第 3 回目の記事「データポータルでのレポート作成」より、2018 年 8 月における「1 日後の継続率」は、11% 〜 39% の間で推移していることがわかりました。これは、「ある日の新規ユーザーのうち、11% 〜 39% のユーザーは、翌日もそのゲームをプレイし、残りの 61% 〜 89% のユーザーは、翌日はそのゲームをプレイしない」ということを意味しています。

この「1 日後の継続率」を KPI として、この KPI を改善する施策を検討するために、過去の Firebase イベントデータ、つまり、過去のユーザーの行動をもとに、「各日の新規ユーザーのうち、翌日もゲームをプレイするユーザーと、そうではないユーザーには、何か違いがあるのか？あるとすれば、どのような違いがあるのか？」を調査する方法をご紹介します。

1. データ探索方針の検討

新規ユーザーのうち、「翌日もゲームをプレイするユーザー」と「翌日はゲームをプレイしないユーザー」にもし違いがあるとすれば、どのような要因が考えられるでしょうか？ゲームをインストールしてプレイを開始した初日のゲーム体験が、もしかすると「翌日もゲームをプレイするかどうか」に影響しているのかもしれません。それでは、「初日のゲーム体験」とは、Firebase イベントデータを使用して、どのように計測できるのでしょうか？今回使用する Firebase イベントデータの BigQuery 公開サンプルデータ（パズルゲーム「Flood-It!」のサンプルイベントデータ）では、以下のような指標を、「初日のゲーム体験」を構成する要素として使用できそうです。

初日のアプリケーション起動時間
初日のゲームプレイ回数
初日の 1 ゲームプレイあたりの所要時間
初日のゲームクリア率（ゲームプレイ回数に占めるゲームクリア回数の割合）
初日のエラーやクラッシュの発生回数

ここでは、「初日のアプリケーション起動時間」に着目して、「翌日もゲームをプレイする新規ユーザー」と「翌日はゲームをプレイしない新規ユーザー」の間で、この指標の値に違いがあるか否かを確認することにします。

2. 初日のアプリケーション起動時間

「翌日もゲームをプレイする新規ユーザー」と「翌日はゲームをプレイしない新規ユーザー」の間で、「初日のアプリケーション起動時間」に違いがあるかを否かを確認します。

以下のクエリを実行します。

select
  retention_day1
, count(1) as users
, round(avg(mins), 1) as mins_avg
, round(stddev(mins), 1) as mins_stddev
, max(mins_min) as mins_min
, max(mins_pctl25) as mins_pctl25
, max(mins_median) as mins_median
, max(mins_pctl75) as mins_pctl75
, max(mins_max) as mins_max
from
  (
    select
      retention_day1
    , mins
    , percentile_cont(mins, 0) over(partition by retention_day1) as mins_min
    , percentile_cont(mins, 0.25) over(partition by retention_day1) as mins_pctl25
    , percentile_cont(mins, 0.5) over(partition by retention_day1) as mins_median
    , percentile_cont(mins, 0.75) over(partition by retention_day1) as mins_pctl75
    , percentile_cont(mins, 1) over(partition by retention_day1) as mins_max
    from
      (
        select
          f.event_date
        , f.user_pseudo_id
        , cast(coalesce(e.engagement_time_msec, 0) / 1000 / 60 as int64) as mins
        , n.user_pseudo_id is not null as retention_day1
        from
          (
            select distinct
              parse_date('%Y%m%d', event_date) as event_date
            , user_pseudo_id
            from
              `firebase-public-project.analytics_153293282.events_*`
            where
              _table_suffix between '20180801' and '20180831'
            and event_name = 'first_open'
          ) f
            left outer join
              (
                select
                  parse_date('%Y%m%d', event_date) as event_date
                , user_pseudo_id
                , sum(p.value.int_value) as engagement_time_msec
                from
                  `firebase-public-project.analytics_153293282.events_*` e
                    cross join
                      unnest(e.event_params) p
                where
                  e._table_suffix between '20180801' and '20180831'
                and p.key = 'engagement_time_msec'
                group by
                  event_date
                , user_pseudo_id
              ) e
            on
              e.event_date = f.event_date
            and e.user_pseudo_id = f.user_pseudo_id
            left outer join
              (
                select distinct
                  parse_date('%Y%m%d', event_date) as event_date
                , user_pseudo_id
                from
                  `firebase-public-project.analytics_153293282.events_*`
                where
                  _table_suffix between '20180802' and '20180901'
              ) n
            on
              n.user_pseudo_id = f.user_pseudo_id
            and n.event_date = date_add(f.event_date, interval 1 day)
      )
  )
group by
  retention_day1
order by
  retention_day1 desc

このクエリは、2018 年 8 月における、「翌日もゲームをプレイする新規ユーザー」と「翌日はゲームをプレイしない新規ユーザー」のそれぞれの、「初日のアプリケーション起動時間」の平均値、標準偏差、最小値、第一四分位数、中央値、第三四分位数、最大値を算出するものです。また、それぞれのユーザー数もあわせて算出しています。このクエリの実行結果は、以下のようになります。

このクエリ実行結果により、以下のようなことがわかります。

2018 年 8 月において、「翌日もゲームをプレイする新規ユーザー」と「翌日はゲームをプレイしない新規ユーザー」の数はそれぞれ、295 人と 1,003 人であった
2018 年 8 月において、「翌日もゲームをプレイする新規ユーザー」と「翌日はゲームをプレイしない新規ユーザー」の「初日のアプリケーション起動時間」の平均値はそれぞれ、12.8 分と 6.0 分であった
同様に、2018 年 8 月において、「翌日もゲームをプレイする新規ユーザー」と「翌日はゲームをプレイしない新規ユーザー」の「初日のアプリケーション起動時間」の中央値はそれぞれ、6.0 分と 2.0 分であった
2018 年 8 月において、「翌日もゲームをプレイする新規ユーザー」の方が、「翌日はゲームをプレイしない新規ユーザー」よりも、「初日のアプリケーション起動時間」の平均値と中央値が高い

また、「翌日もゲームをプレイする新規ユーザー」と「翌日はゲームをプレイしない新規ユーザー」それぞれの、「初日のアプリケーション起動時間」ごとのユーザー数の割合をデータポータルで描画すると、以下のようになります。

翌日継続する新規ユーザーの初日のアプリケーション起動時間（分）別のユーザー数の割合

翌日継続しない新規ユーザーの初日のアプリケーション起動時間（分）別のユーザー数の割合

以上のことから、「アプリケーションインストール日のゲームプレイ時間が長い方が、翌日も継続してプレイする可能性が高くなるのではないか？」というような仮説が考えられるかもしれません。この仮説を検証するためには、たとえば、「チュートリアルを一層充実させる」、「初回プレイ時のゲームの難易度を下げる」、「初回プレイ時の主な離脱ポイントをさらに Firebase イベントデータをもとに調査して、その部分で離脱しないようにゲームを修正する」などの施策を検討して実施し、前回の第 3 回目の記事「データポータルでのレポート作成」で作成したレポートを用いて「1 日後の継続率」が改善されているかを確認する、というような作業を行うことが考えられます。

おわりに

第 4 回目の本記事では、BigQuery で SQL を実行することで、データを探索する方法をご紹介しました。次回の第 5 回目の記事「AutoML Tables での特徴量の重要度の確認」では、AutoML Tables の「特徴量の重要度」の機能を用いて、KPI の値の決定に寄与していると考えられる指標のうち、特に重要であると思われるものを確認する方法をご紹介します。

Firebase イベント データ分析のための Google Cloud 活用方法 (4) BigQuery でのデータの探索