目的

株式会社primeNumberでテクニカルサポートエンジニアとして働いている@kimura34です。

弊社が提供しているサービスTROCCO®︎ではEssentialプラン以上のお客様にはslackを利用したサポートを提供しており、
エラーなどのトラブル発生時や、利用方法などに関する相談があるときはslack上でコミュニケーションを取っています。

入社したばかりの頃はTROCCO®︎のことを知るための教科書として、過去の問い合わせ履歴から学んでいました。
今でも問い合わせを受けた時はSlackやSalesforceで検索をかけて、過去に同じような事例がないか確認しています。
しかし自分が参加していないSlackチャンネルの履歴までは確認できないので過去の事例に辿り着かないこともよくありました。
チャンネルへの参加有無関係なく、過去のチャット履歴を検索することができたらハッピー！なのに、と何度思ったことか。。。

ナレッジをCS内で共有するために
Slackのチャンネルからチャット情報（お客様からの問い合わせ履歴）を取得して、
過去の問い合わせを資産として扱い、今後の問い合わせ対応の効率化に結びつけていくことが今回の施策の目的です。

３部作でお送りします〜！
第一弾 TROCCO®︎を使ってSlackからチャット情報を抽出しBigQueryに集約する
第二弾（仮）TROCCO®のワークフローからDataformを動かす
第三弾（仮）TROCCO®とBigqueryで作る簡単LLM

第一弾 TROCCO®︎を使ってslackからチャット情報を抽出しBigQueryに集約する

SlackAPIを利用してチャット情報を取得し、BigQueryに転送
転送したレスポンスの生データを扱いやすいように加工

を行なっていきます！

事前準備

Slack APIを利用するための準備をしておきます。

Botアプリを指定のワークスペースに作成、追加

ここからアクセスします。
"Create New App"をクリックします。
情報を入れて"Create App"をクリックします。

適切なスコープを付与

"OAuth & Permissions"を開きます。
"Scopes"の"Add an OAuth Scope"にて、必要なScopeを付与します。
今回利用するAPIに必要なスコープはchannels:historyとgroups:history
channels:history：パブリックチャンネルのチャット情報取得時に必要
groups:history：プライベートチャンネルのチャット情報取得時に必要

Botを取得対象のチャンネルに追加

該当チャンネルの左上にあるチャンネル名をクリックします。
"インテグレーション"タブの"アプリを追加する"にて、作成したアプリを追加します。

Bot User OAuth Access Tokenを取得

"OAuth & Permissions"を開きます。
"Scopes"の"Add an OAuth Scope"にて、必要なScopeを付与します。
※ SlackAPI上で適切なScopeの付与が先に行われている必要があります
"Install to Workspace"をクリックします。
右下の"許可する"をクリックします。

※ Scopeを上記手順の後新たに追加した場合、Bot User OAuth TokenをReinstallする必要があります。上記3、4の手順を再度行っていただくだけ問題ございませんが、Reinstall忘れにご注意ください。

転送設定を作成

ここからTROCCO®︎を利用してslack -> BigQueryにデータを転送していきます。
TROCCO®︎の転送元コネクタにslackはあるのですが、取得可能なデータがusers.listのみなので、今回取得予定のconversations.historyとconversations.repliesは転送元slackでは利用できません。
TROCCO®︎で提供しているコネクタ以外も転送元として利用したい・・・。そんな時に試して欲しいのが転送元コネクタHTTP・HTTPSです！！

転送元HTTP・HTTPSはAPIの提供がある外部サービスだと利用が可能となります。
※ 利用実績や検証の相談のれますのでいつでもご連絡ください〜
Slack APIは転送元HTTP・HTTPSでデータ取得ができたので、HowToを説明していきます。

STEP1

まず転送元HTTP・HTTPS → 転送先BigQueryの転送設定を作成します。
カスタム変数を使って動的にパラメータの値を変更できるようにしたいので、まずカスタム変数を作成します。今回動的に変化させたいのがチャンネルIDと取得する期間の始まりと終わりです。SlackAPIでは取得する期間を絞ることができるのですが、Unix timestampでの指定が必須なのでデータ型は文字列で作成します。
URLにエンドポイントを入力します。今回はチャットの情報を取得するconversations.historyのAPIを利用するので、https://slack.com/api/conversations.historyを入力しました。
HTTPメソッドはPOSTなのでPOSTと入力します。
入力ファイル形式にはJSON Linesを指定します。
パラメータにはSlack APIの仕様書に載っているパラメータを設定することができます。ここで先ほど作成したカスタム変数を埋め込んでおり、実行時に動的に展開されます。パラメータの名前はAPIの仕様書に載っているキー名を入力してください。
HTTPヘッダに認証情報などを定義します。SlackAPIの場合は事前準備で取得したトークンをBearer 発行したトークンの形式で入力します。マスキングにチェックをつけると画面上では***で表示されるので安全です。
転送先BigQueryの設定に関しては割愛します。

STEP2

設定に問題がない場合、STEP2のデータ設定タブ内「カラム定義」がこんな感じで自動作成され、レスポンスのキーごとに一つずつのカラムとして取得できます。
必要なデータだけ取得するための絞り込みを行います。今回はmesssagesのJSON内に含まれているts（チャットのタイムスタンプ）のデータだけ取得、後続でconversations.repliesを使ってチャットに紐づくスレッド情報を取得する際にchannel_idも必要なためtsと合わせて管理しておきたいので、取得したデータ内にはないですが、先ほど作成したカスタム変数を埋め込み取得します。
プレビューを実行すると必要なデータのみを抽出することができました。
実際にジョブを実行してBigQueryにデータを転送してみました。こんな感じで転送されます。

tsには該当するデータがすべてJSON配列の形式で転送されるので、次のデータマート機能を使ってJSON配列の文字列を展開していきます。

データマートを作成

TROCCO®︎では、DWH上に蓄積されたデータを利用してデータマートの作成・管理を行うことができます。対応しているDWHは以下です。

Google BigQuery
Snowflake
Amazon Redshift
Azure Synapse Analytics

では実際にデータマートを作成していきます。

クエリ実行モードは任意のクエリを記述し簡単に転送が可能なデータ転送モードにします。
転送設定で転送した配列形式の値をアンネストしてそれぞれのレコードとして保持するクエリを書きました。
JSON_EXTRACT_ARRAY()でJSON文字列になっている値を配列型に変換し、UNNEST()で配列を一レコードずつに展開しています。
```
WITH tmp AS (
    SELECT
        *
    FROM
        data_set.ts_from_slack
)
SELECT
    channel_id,
    REPLACE(unnest_ts, '"', '') AS unnest_ts
FROM
    tmp,
    UNNEST(JSON_EXTRACT_ARRAY(ts)) AS unnest_ts
```
作成したデータマートを実行した結果はこんな感じです。JSON配列の文字列だったデータを扱いやすいデータに加工することができました。

これまでは手動でそれぞれ実行していましたが、最後にワークフロー機能を使って順番に実行していく処理を作成していきます。

ワークフローを作成

先ほど作成した転送設定とデータマートを繋げて保存しました。
転送設定作成時にカスタム変数をリクエストのパラメータにカスタム変数を埋め込んでいたのですが、ここで動的に展開することができます。該当の転送設定をクリックして編集します。
カスタム変数でループ実行にチェックをつけると、ループの種類を選択することができます。今回はBigQuery上に用意しておいたチャンネルIDのリストからチャンネルIDを取得してループ実行したかったので、「Google BigQueryのクエリ結果でループ」を選択しました。対象のカスタム変数に展開したいカスタム変数を選択します。

2年分のチャット情報を取得したいと考えたので以下のようなクエリを書きました。一回の転送設定で取得する期間は半年分にしました。対象カスタム変数で選択した順番に合わせて展開したいのでSELECT文の順番に注意です。合わせてください。

WITH tmp AS (
  SELECT
    *
  FROM
    `data_set.channel_id_sample`
)

SELECT
  channel_id,
  UNIX_SECONDS(TIMESTAMP(date, 'Asia/Tokyo')) as oldest,
  UNIX_SECONDS(TIMESTAMP_SUB(TIMESTAMP(DATE_ADD(date, INTERVAL 6 month), 'Asia/Tokyo'), INTERVAL 1 second)) as latest
FROM
  UNNEST(GENERATE_DATE_ARRAY('2022-01-01', '2023-12-31', INTERVAL 6 month)) AS date
CROSS JOIN
  tmp
ORDER BY
  date

プレビューを実行するとこんな感じになりました。対象のチャンネルは２チャンネル、2年分を半年ずつで取得するので実行予定回数は8回。この組み合わせで展開され、実行されます。また並列実行をONにすることで同時にジョブを起動させることができます。
※ ワークフロー作成時にタスク同時実行上限数を設定しておいてください。

最後に

今回紹介したのはチャットのタイムスタンプを取得するところまでですが、チャンネルIDとtsを使ってチャットに紐づいているスレッド情報を取得する転送設定を作成し、データマート機能でJSON形式の文字列をアンネストする設定も作成、それらをワークフローの続きに繋げることでスレッドの情報の取得が可能になります。
2年分のチャット情報を取得する方法をお伝えしましたが、弊社では現在、毎週末に1週間分のチャットのタイムスタンプを取得し、90日分のチャットを対象に、チャットに紐づく新規に更新されたスレッド情報を取得するというワークフローを定期実行しています。

これにて第一弾は終了です〜〜！
TROCCO®︎を使った良いところは、一回設定してしまえば定期実行でデータを貯めていけるので、とても楽にデータを抽出、管理することができるところですね。

第二弾は上のワークフローの最後に繋がってるワークフローで何をやっているのか説明していくかもです！！お楽しみに〜〜！

[第一弾] TROCCO®︎を使ってSlackからチャット情報を抽出しBigQueryに集約する

目的