Firebase Analytics+データポータルでダッシュボードをつくる

Firebase Analytics のデータを
Google データポータル (旧: Data Studio) で表示したい場合、
まず Firebase Analytics のデータを BigQuery にインポートして加工する必要があります。

自由度は高いのですが、Google Analytics のようにはいかず、ちょっとややこしい SQL を書く必要があるので、
データポータルでデータソースを追加してポンというわけにはいかなくなりました。

なので、スクリーンビューやセッション数のようなよくある値を毎日集計してダッシュボード化するやりかたを紹介します。

<img width="596" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/359439/7702dbd3-58d5-fadf-387c-daf8fafeaef7.png">

# 前提

まず Firebase の `プロジェクトの設定` > `統合` から BigQuery とリンクしておくことが必要です。

<img width="237" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/359439/1b63bb6d-8940-9d31-1ac6-c5727cd60388.png">

これで `analytics_<ID>` というデータセットに毎日 Analytics の生イベントデータが BigQuery のテーブルとしてインポートされるようになります。 

<img width="987" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/359439/858142ab-1ba8-d4fc-30b5-ea4240d98142.png">


# 日毎に集計する SQL

よくあるプラットホームごとの昨日のスクリーンビュー数やその他主要 KPI を集計する SQL を書いてみました:

```sql:collect_yesterday_events.sql
#standardSQL
WITH

-- 集計範囲 (この例では昨日分; 変更可)
event_range AS (
  SELECT
    DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY) AS from_date,
    DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY) AS to_date
),

-- 範囲の全イベント
all_events AS (
  SELECT 
    event_date,
    platform,
    user_pseudo_id,
    event_name
  FROM 
   `analytics_<あなたの Analytics の ID>.events_*`,
   event_range
WHERE
    _TABLE_SUFFIX
        BETWEEN
            FORMAT_DATE("%Y%m%d", event_range.from_date) 
        AND
            FORMAT_DATE("%Y%m%d", event_range.to_date)
), 

-- スクリーンビュー
screen_views AS (
SELECT
  event_date,
  platform,
  COUNT(*) screen_views
FROM
  all_events
WHERE
  event_name = 'screen_view'
GROUP BY
  event_date,
  platform
),

-- ユニークユーザー
unique_users AS (
SELECT
  event_date,
  platform,
  COUNT(DISTINCT user_pseudo_id) unique_users
FROM
  all_events
WHERE
  event_name = 'user_engagement'
GROUP BY
  event_date,
  platform
),

-- 新規ユーザー
new_users AS (
SELECT
  event_date,
  platform,
  COUNT(DISTINCT user_pseudo_id) new_users
FROM
  all_events
WHERE
  event_name = 'first_open'
GROUP BY
  event_date,
  platform
),

-- セッション
sessions AS (
SELECT
  event_date,
  platform,
  COUNT(*) sessions
FROM
  all_events
WHERE
  event_name = 'session_start'
GROUP BY
  event_date,
  platform
)

-- 結合
SELECT
  sv.event_date,
  sv.platform,
  sv.screen_views,
  uu.unique_users,
  nu.new_users,
  s.sessions
FROM
  screen_views sv
  INNER JOIN unique_users uu
        ON   sv.event_date = uu.event_date
        AND  sv.platform   = uu.platform
  INNER JOIN new_users nu
        ON   sv.event_date = nu.event_date
        AND  sv.platform   = nu.platform
  INNER JOIN sessions s
        ON   sv.event_date = s.event_date
        AND  sv.platform = s.platform
;
```

`<あなたの Analytics の ID>` の部分はインポートされた BigQuery テーブルを見て自分のを入れます。

これを単に実行すれば昨日のスクリーンビュー, ユニークユーザー, 新規ユーザー, セッションの数がプラットホームごとに集計されます:

<img width="741" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/359439/799e6a43-6d81-044c-ebe1-1a518be2c479.png">

他にイベントを取りたい場合は `event_name` あたりの条件を変更して真似して追加すれば OK です。

# クエリの定期実行とテーブル作成

BigQuery ではクエリの定期実行をスケジュールし、自動でテーブルに結果を出力できます。今回は日毎の集計結果を日別テーブルに毎日保存することにします。

こうしておくと、データポータルで扱いやすくなりますし、毎回集計のクエリを発行するよりも転送量も節約することができます (一ヶ月分とかやるときは特に)。

まず Firebase Analytics でエクスポートされたデータセットと同じリソースと同じリージョンで集計用のデータセットを作成しておきます (たぶんリージョンをまたぐことができないので):

<img width="693" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/359439/c4708c43-d480-5cfa-1a1e-050dd82efdc6.png">

次にさきほどのクエリエディタ下にある `クエリのスケジュール` ボタンから `スケジュールされたクエリの新規作成` でクエリを定期実行するようにしてみます:

<img width="795" alt="" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/359439/1d67e7c1-1c0b-b2eb-d7c2-0c79bf058772.png">

`クエリ結果の書き込み先` の `テーブル名` を `daily_{run_time|"%Y%m%d"}` としておくと、
テーブルを `daily_20191006` のように実行時の日付で日別テーブルを作成することができます。

<img width="325" alt="" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/359439/9ee0eff6-ca31-bc40-33ab-eca1ce17293f.png">

<img width="461" alt="" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/359439/205767a6-dc87-563f-970c-c7d88d36fb05.png">

上の設定の場合だと前日のデータのテーブルを作成しているので、`run_time-24h` して前日の日付で作成します。
(例: 実行日時が `2019-10-06` なら `20191005`)

<img width="758" alt="" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/359439/09bd4b26-84b2-2bb1-566a-5c553f8558b3.png">

かくして日毎の集計テーブルができました。

# データポータルでの表示

データポータルでダッシュボードを作る場合は、データソースとしてさっき作成した BigQuery テーブルを指定します:

<img width="1068" alt="" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/359439/2a65a751-916c-fa97-1dd3-18c64fb96703.png">

このデータソースを使ってイベントの日にち (`event_date`) をディメンションに、評価したい値を指標にすると時系列のグラフを作成できます:

<img width="624" alt="" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/359439/791137d0-1377-085a-bbc0-37fc08b9ff10.png">

内訳ディメンションを `platform` にすれば iOS と Android のように、プラットホームごとの指標を並べて比較することもできます:

<img width="303" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/359439/8aca67b4-e6cb-4201-e28b-6a7fbd354d8f.png">

これでよくあるダッシュボードを作ることができますね。
必要な KPI を取得しておくようにすれば値を手軽にウォッチできるので便利です。

# Slack で通知

あとは Slack で値を通知したりしても便利ですね。

![](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/359439/4b889d71-dc10-bd7f-c02f-d089a3cfd1f9.png)

このやり方についてはちょっと長くなるので別記事で紹介することにします

👉 つづく: https://qiita.com/mangano-ito/items/07993bbfea54988701ec