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

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

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

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

クエリ例 1: 発話された言葉ランキング

ユーザーの発話内容ログから、
どんな言葉でアプリを使っているかがわかるようにします。

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つに分解できます。

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

使いたいデータだけ抜き出し

クエリの最初のブロックの部分です

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 です。

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

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

集計しやすいようにデータを整形

集計には直接関係ない部分を分離するために
先にまとめてフォーマットします。

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	発話した日付

集計

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

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: 時間帯ごとのアプリ呼び出し回数

今度はどんな時間帯にアプリを使っているかがわかるようにします。

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などに格納している他のデータと組み合わせれば可能性はもっと広がります。

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