素敵クエリの紹介と解説 - Google Assistantアプリのログを集計 -

Google Assistant アプリのログは Stackdriver Logging に入っているので
BigQuery にシンクさせれば好きにクエリを叩くことができます。

とはいえ、どんなデータをどんな風に取れるかわからないとイメージしづらいと思います。
そこで、Actions on Googleの生ログを叩く
具体的なクエリの例を使いながら解説していきます。

BigQueryにシンクする方法はこのスライドをどうぞ ↓
https://speakerdeck.com/sesta/liberty-of-analytics

# クエリ例 1: 発話された言葉ランキング
ユーザーの発話内容ログから、
どんな言葉でアプリを使っているかがわかるようにします。

```sql
WITH formated_json as (
  SELECT
    SUBSTR(textPayload, 33, LENGTH(textPayload) - 33) as json,
    timestamp,
    resource.labels.action_id as action
  FROM `log-demo.log_demo_request.actions_googleapis_com_actions_*`
  WHERE
    _TABLE_SUFFIX >= '20181101'
    AND _TABLE_SUFFIX <= '20181107'
),
data as (
  SELECT
    JSON_EXTRACT_SCALAR(json, "$.conversation.conversationId") as conversationId,
    JSON_EXTRACT_SCALAR(json, "$.user.userId") as userId,
    JSON_EXTRACT_SCALAR(json, "$.inputs[0].rawInputs[0].query") as query,
    JSON_EXTRACT_SCALAR(json, "$.isInSandbox") as isSandbox,
    action,
    FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', timestamp , 'Asia/Tokyo') as date
  FROM formated_json
)

SELECT query, COUNT (*) as count
FROM data
WHERE isSandbox IS NULL
  AND query IS NOT NULL
GROUP BY query
ORDER BY count DESC
```

クエリの結果はこんな感じです。

| query | count |
| --- | --- |
| xxxアプリにつないで | 30 |
| xxxの意味を調べて | 24 |
| ... | ... |

`WITH`句を使っていて強そうに見えますが分解するとシンプルです。
クエリの中でやっていることは大きく3つに分解できます。

* 使いたいデータだけを抜き出し
* 集計しやすいようにデータを整形
* 集計

## 使いたいデータだけ抜き出し
クエリの最初のブロックの部分です

```sql
WITH formated_json as (
  SELECT
    SUBSTR(textPayload, 33, LENGTH(textPayload) - 33) as json,
    timestamp,
    resource.labels.action_id as action
  FROM `log-demo.log_demo_request.actions_googleapis_com_actions_*`
  WHERE
    _TABLE_SUFFIX >= '20181101'
    AND _TABLE_SUFFIX <= '20181107'
),
```

このクエリでは Actions on Google が Dialogflow にリクエストを送った時のログである
`Sending request` の方を利用しています。

生ログの中には `textPayload` というカラムがあり、
ここにアプリ特有の情報が色々入っています。
しかし、中身を除いて見ると以下のような文字列が入っています。

```
Sending request with post data: {"user": ... }
```

残念ながら、jsonがそのまま入ってるわけではないんですよね。
このままだと BigQuery の Standard SQL の恩恵を受けにくいので、
いらない部分を切り取ります。
その処理をしているのがクエリの `SUBSTR` です。

本当は正規表現でやった方がクールだし、形式が変わった時に壊れにくそうなので気になる人は書き換えてみてください。

残りの部分は後で使いそうなタイムスタンプやアクション名に絞っているだけです。

## 集計しやすいようにデータを整形
集計には直接関係ない部分を分離するために
先にまとめてフォーマットします。

```sql
data as (
  SELECT
    JSON_EXTRACT_SCALAR(json, "$.conversation.conversationId") as conversationId,
    JSON_EXTRACT_SCALAR(json, "$.user.userId") as userId,
    JSON_EXTRACT_SCALAR(json, "$.inputs[0].rawInputs[0].query") as query,
    JSON_EXTRACT_SCALAR(json, "$.isInSandbox") as isSandbox,
    action,
    FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', timestamp , 'Asia/Tokyo') as date
  FROM formated_json
)
```

Standard SQL の `JSON_EXTRACT_SCALAR` は非常に便利ですね。
JSONから好きにデータを抜き出せます。

フォーマット後のカラムはそれぞれ以下のような意味を持ちます

| カラム名 | 意味 |
| --- | --- |
| conversationId | アプリの呼び出しごとに個別のID |
| userId | ユーザーID、アカウント連携していない場合 NULL |
| query | 発話内容 |
| isSandbox | 開発アカウントかどうか、ベータテスターやレビューアカウントもTrueになる |
| action | Actions on Googleで認識できたアクション名、Dialogflow のインテント名ではない |
| date | 発話した日付 |

## 集計
あとは好きにクエリを書くだけです。

```sql
SELECT query, COUNT (*) as count
FROM data
WHERE isSandbox IS NULL
  AND query IS NOT NULL
GROUP BY query
ORDER BY count DESC
```

一般的な sql ですね。

`query IS NOT NULL` の部分は、
アカウントリンキングなどActions on Googleのヘルパー関数を考慮したもので
queryが空になっているログを弾いています。

# クエリ例 2: 時間帯ごとのアプリ呼び出し回数
今度はどんな時間帯にアプリを使っているかがわかるようにします。

```sql
WITH formated_json as (
  SELECT
    SUBSTR(textPayload, 33, LENGTH(textPayload) - 33) as json,
    timestamp,
    resource.labels.action_id as action
  FROM `log-demo.log_demo_request.actions_googleapis_com_actions_*`
  WHERE
    _TABLE_SUFFIX >= '20181101'
    AND _TABLE_SUFFIX <= '20181107'
),
data as (
  SELECT
    JSON_EXTRACT_SCALAR(json, "$.conversation.conversationId") as conversationId,
    JSON_EXTRACT_SCALAR(json, "$.user.userId") as userId,
    JSON_EXTRACT_SCALAR(json, "$.inputs[0].rawInputs[0].query") as query,
    JSON_EXTRACT_SCALAR(json, "$.isInSandbox") as isSandbox,
    action,
    FORMAT_TIMESTAMP('%H', timestamp , 'Asia/Tokyo') as hour
  FROM formated_json
)

SELECT hour, COUNT (DISTINCT conversationId) as count
FROM data
WHERE isSandbox IS NULL
  AND query IS NOT NULL
GROUP BY hour
ORDER BY hour
```

クエリの結果はこんな感じです。

| hour | count |
| --- | --- |
| 0 | 100 |
| 1 | 10 |
| ... | ... |

大体例1と一緒です。
以下の部分が変わっています。

* 日付のフォーマットを変更して、時間だけを `hour` に格納
* 発話回数ではなくアプリが使われた回数を知りたいので、`DISTINCT conversationID` でカウント

フォーマットの部分を分離しているので、
集計の仕方は最後の `SELECT` 文だけを見れば大体わかると思います。

# まとめ
BigQuery を使えば好きにデータを集計できるようになります。
今回はActions on Googleの生ログだけを使いましたが、
Firestoreなどに格納している他のデータと組み合わせれば可能性はもっと広がります。

開発したアプリのデータを見て、もっといいアプリにしていきましょう。