GameWith Developer Blog

GameWith のエンジニア、デザイナーが技術について日々発信していきます。

Google BigQueryとData StudioでアプリユーザーのDAU内訳を見える化 #GameWith #TechWith

こんにちは!ルルアのアトリエでルルアちゃんの可愛さに癒やされながら、笑いが耐えないストーリーを堪能しているiOSエンジニアのchuymasterです。

今回はGoogle Big QueryとData Studioを使ってのアプリデータ分析に取り組んだので、そのやり方を紹介したいと思います。読み手は、この2つのサービスの初心者を想定しています。SQLクエリも載せます!

目次

GameWithアプリのユーザー分析の課題

各ゲームのユーザー状況が分からない

GameWithアプリはiOS/AndroidともにFirebase Analytics(FA)を導入しています。アプリチームはFAのダッシュボードからユーザー数の推移を見ています。

f:id:gwchai:20190418174920j:plain
Firebase Analyticsのダッシュボード

しかし、ゲーム攻略サービスの特性上、アプリ内のイベント数はゲームのイベント開催によってかなり左右されます。

プロダクトの施策を打ってユーザーが増加した場合、それは施策の結果なのか、それともゲームイベントの影響なのかが把握しにくい状態です。

カスタムイベントも送っているので、FAのAudience機能を使えば、ユーザー属性に絞ったデータをある程度見ることができますが、取り扱っているゲームは50タイトル以上あるため、一つずつ確認するのでは手間がかかりすぎます。

そこで、各ゲームユーザーの状況が一目で分かるように、Google BigQueryとGoogle Data Studioで専用のダッシュボードを作ることにしました。

Google BigQueryとは

BigQueryはグーグルが提供しているビッグデータ解析サービスです。SQLクエリで大量のログデータから、欲しい情報を取り出すことが簡単にできます。

データのクエリは課金されます。最初の1TBは無料です。

cloud.google.com

Google Data Studioとは

Data Studioはグーグルが提供しているBI(ビジネスインテリジェンス)ツールです。様々なデータベースに接続して、簡単にデータをまとめたり、グラフで表示したりできます。 datastudio.google.com

ビッグデータから情報を取り出す

f:id:gwchai:20190419115105j:plain
完成したゲーム別DAUダッシュボード

今回の要件は、まずは各ゲームのDAU(1日当りアクティブユーザー数)を一目で分かるようにしたいことなので、クエリを下記の手順で作ります。

FAデータを連携する

FAのデータをBigQueryのテーブルに連携します。手順は公式サイトから support.google.com

連携したら、BigQueryのコンソールでテーブル名を確認できます

f:id:gwchai:20190419105247j:plain
events_* は前日までのログテーブル。events_intraday_xxxxは今日の最新時点のログテーブル。

必要なデータを洗い出す

DAUの定義は、一日で対象ゲームの攻略画面を見たユーザーの数とします。 これに対して必要なイベントデータを洗い出します。下記の通りです。

  1. イベント日付
  2. 対象ゲームの画面を見たイベント
  3. ユーザーID
  4. OS(iOSかAndroid)

それぞれがテーブルのどのコラムに当たるか、公式サイトのイベント一覧から確認できます。 support.google.com

クエリを組み込む

集計するためのSQLクエリを作成します。下記が完成したクエリです。

WITH 
-- すべてのイベント
all_events AS (
  SELECT 
    user_pseudo_id, -- ユーザーID
    event_name, -- イベント名
    PARSE_DATE('%Y%m%d', event_date) as event_date, -- イベント日付
    device.operating_system as operating_system, -- OS
    param.key AS event_key, -- イベントパラメーターのキー
    param.value.string_value AS event_value -- イベントパラメーターの値
  FROM 
   `xxxxxxxxxx.events_*`, -- データベース名.テーブル名
    UNNEST(event_params) as param -- イベントパラメータを展開
WHERE
    -- 前日のイベントテーブルのみを対象
    _TABLE_SUFFIX BETWEEN FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)) 
                      AND FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
  ), 
-- ゲーム攻略画面を見るイベント
game_view_events AS (
  SELECT DISTINCT * FROM all_events -- ユニークイベントに絞る
  WHERE
  event_name = 'screen_view' -- 画面イベントのみ
  AND event_key = 'firebase_screen' -- 画面名のキー
  AND (event_value LIKE '攻略_%') -- 画面名が【攻略_ゲーム名】にハマるイベントのみ
),
-- OS別、ゲーム別DAUの集計
game_dau_by_operating_system AS (
    SELECT 
        event_date, -- イベント日付
        event_value, -- イベントパラメーターの値(攻略_ゲーム名)
        operating_system, -- OS
        COUNT(DISTINCT user_pseudo_id) users -- ユーザー数の集計
    FROM game_view_events
    GROUP BY 
        event_date, 
        operating_system,
        event_value
)

-- DAUテーブルからデータをSELECT
SELECT * FROM game_dau_by_operating_system 
ORDER BY 
event_date, 
event_value,
operating_system

クエリ結果を確認する

上記を実行すると、このような集計結果になります。

event_date event_value operating_system users
2019-04-17 攻略_FGO ANDROID xxx
2019-04-17 攻略_FGO IOS xxx
2019-04-17 攻略_モンスト ANDROID xxx
2019-04-17 攻略_モンスト IOS xxx
2019-04-17 攻略_ポケモンGO ANDROID xxx
2019-04-17 攻略_ポケモンGO IOS xxx

対象テーブルは上記では前日のイベントテーブルのみにしていますが、期間を伸ばすと、日別の集計結果が返ってきて、時系列で分析できるようになります。

クエリ結果を保存する

想定の結果ができたら、Data Studioで分析するために別のBigQueryテーブルに保存します。

f:id:gwchai:20190418173636j:plain
BigQuery Tableに保存

f:id:gwchai:20190419134031j:plain
テーブル名を入れて完了

保存ボタンの右に、「Data Studioで見る」ボタンもありますが、それを使うとData Studioで見る度に毎回このSQLを実行することになります。

データ量が少ない場合は大丈夫ですが、GameWithアプリの場合、一日分のクエリ実行で3GBほど消費するので、場合によっては高額な請求金額になります。

なので、別テーブルに集計結果を保存して、Data Studioで読み取った方が費用が安く済みます。

Google Data Studioでデータを見える化する

集計データができたところで、誰でも意味が分かるように、Data Studioで見える化します。

Data StudioからBigQueryのテーブルを参照する

下記のData Studioのダッシュボードにログインします。

datastudio.google.com

レポート作成後、データソースを作成し、BigQueryと連携させます。

f:id:gwchai:20190419113455p:plain
新しいレポートの作成

f:id:gwchai:20190419113116j:plain
データソースを作成

f:id:gwchai:20190419113143p:plain
BigQueryを選ぶ
  先ほど作ったDAUのテーブルと、レポートに欲しい項目を選びます。

f:id:gwchai:20190419113302j:plain
DAUテーブルを選ぶ

f:id:gwchai:20190419113608p:plain
欲しい項目を選ぶ

Data Studioでグラフを作成する

データが連携できたら、後はグラフを作って、ゲーム名でフィルターを適用していけば完成です!

f:id:gwchai:20190419113552p:plain
グラフの例

なお、手順はかなりざっくりとした説明なので、不明点があれば、公式のチュートリアルをご参照ください。

cloud.google.com

スケジューラーで自動化する

Data Studioでは、直近28日分のデータを表示させています。なので、データがテーブルにさえあれば、グラフ表示は自動的に行われます。

クエリ作業を手動で毎日やるのはきついので、BigQueryに毎日自動で集計させて、結果を連携させます。

BigQueryでSQLクエリを作ったら、スケジュール作成をします。

f:id:gwchai:20190419114823j:plain
BigQueryからスケジュールを作成

f:id:gwchai:20190419114849j:plain
スケジュールの設定

設定が完了したら、翌日に集計用テーブルの中身を確認して、正しく前日のデータが入れば完了です。

最後に

Firebase Analytics x BigQuery x Data Studioのトリプルコンボはいかがだったでしょうか?これらを組み合わせれば、今まで見えなかったユーザー状況が、簡単に見えるようになりました。

GameWithアプリチームでは、ユーザーにより高い価値を届けるために、データに基づいた検証を日々行っています。

今回は単純なDAU計算をしましたが、BigQueryでは本当にたくさんのデータが集まっているので、もっと深掘りしていきたいと思います。

このように、GameWithのエンジニアはサービス開発のみならず、リリース後のデータ分析・活用も行っています。

そんなGameWithではエンジニアを大募集しているので、興味のある方は是非、話を聞きに来てみてください!

www.wantedly.com

またGameWithのDeveloper向けTwitterアカウントも開設しました。
もくもく会の告知やブログの更新情報などを発信するので良かったらフォロー宜しくお願いします!

twitter.com