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

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

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

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

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

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

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

カスタムイベントも送っているので、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

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

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

FAデータを連携する

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

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

events_* は前日までのログテーブル。events_intraday_xxxxは今日の最新時点のログテーブル。

必要なデータを洗い出す

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

イベント日付
対象ゲームの画面を見たイベント
ユーザーID
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