Edited at

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

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