チャットボットにおけるユーザーの行動分析に使えるSQL Tips

今回のテーマ

SQLを使用したチャットボット内のユーザーの行動分析

前提条件

• 使用するデータウェアハウスはBigquery
• チャットボットで記録されるデータは
  • session id：チャットボット起動時に記録される一意のID（String型）
  • createtime：データが作成された日時（datetime型）
  • scenario text：チャットボット内でどのボタンが押下されたか判別できるString型データ

今回分析したい内容

• シナリオごとのユーザー数

シナリオごとのユーザー数

• 方法１：シナリオ単位でグループ化をし、session idをdistinctでcountしてあげる
• 方法２：STRING_AGGを使用してセッションごとにscenario textをまとめる方法

方法１については基礎的な部分なので割愛する。
方法２については以下のSQLで取得可能

with A as(
    select 
        session_id
        ,STRING_AGG(scenario_text) as scenario
    from xx /*チャットボット内の行動が記録されているテーブル*/
    group by 1
)
select 
    case
        when REGEXP_CONTAINS(scenario, "シナリオ１") IS TRUE THEN "シナリオ１"
        when REGEXP_CONTAINS(scenario, "シナリオ２") IS TRUE THEN "シナリオ２"
        when ...
    end as scenario
    ,count(distinct session_id)
from A
group by 1

Aテーブルは以下のようになる

session_id	scenario
a	シナリオ１,シナリオ２,...,シナリオｎ
b	シナリオ１,シナリオ２,...,シナリオｍ
...	...

REGEXP_CONTAINSで含まれているシナリオを抽出しグループ化を行う。

方法２のメリットとしては、方法１とは違い複数のシナリオを経由したユーザーを把握することが可能である。

例えば、シナリオ１→シナリオ２→シナリオ４の行動を行ったユーザー数などを簡単に絞り込むことができる。
この場合、SQLは以下の通りになる。

with A as(
    select 
        session_id
        ,STRING_AGG(scenario_text) as scenario
    from xx /*チャットボット内の行動が記録されているテーブル*/
    group by 1
)
select 
    case
        when 
            REGEXP_CONTAINS(scenario, "シナリオ１") IS TRUE 
            AND REGEXP_CONTAINS(scenario, "シナリオ２") IS TRUE 
            AND REGEXP_CONTAINS(scenario, "シナリオ４") IS TRUE 
        THEN "シナリオ１→２→４"
    end as scenario
    ,count(distinct session_id)
from A
group by 1