CODE:LIFE

ExcelPowerQuery

Slackのログ(JSONファイル)をExcel PowerQuery だけでなるべく楽に解析

avatar

Slackのログを分析するときは管理画面から一括ダウンロードするのが早い。しかし、出てくるデータはチャンネルごとにフォルダがバラバラかつ日付ごとにバラバラのJSONファイルとして出てくる。

CSVならあまり頭を使わなくてもそのまま開いたり、日付ごとのファイルでもbatで統合するなりすればいいがJSONファイルをExcelで扱うにはPowerQueryが最適VBA-JSONを使う手もあるが、初心者向けではない。

手順概要

  1. Slackからごそっとデータをダウンロード
  2. ZIPファイルを解凍
  3. Excel のデータタブでデータの取得→ファイルから→フォルダから
  4. 自動生成された関数を手直し
  5. シートに読み込む

 

1. Slackからごそっとデータをダウンロード

Slackの管理画面https://<ワークスペース名>.slack.com/admin/settings にアクセスし、画面右上の「データのインポート/エクスポート」をクリック。

An image from Notion

 

エクスポートタブへ移動。対象期間を選択して「エクスポート開始」。

An image from Notion

 

データ量にもよるが、しばらく待つとSlackbotから準備完了の通知が届く。

An image from Notion

 

再度設定画面のエクスポートタブへ移動して 過去のエクスポート 内からダウンロードを開始するをクリックしてダウンロード。

An image from Notion

 

2. ZIPファイルを解凍

ZIPファイルを解凍するとチャンネルごとにフォルダが分かれており、中に日付ごとのJSONファイルが並んでいる。

1つのチャンネルだけを分析する場合は必要なフォルダのみ解凍すればOK。

An image from Notion

 

3. Excel のデータタブでデータの取得→ファイルから→フォルダから

Excelを起動して「データ」タブ→「データの取得」→「ファイルから」→「フォルダーから」を選択。

ここで一つ上に「JSONから」というのがあるが、こっちを選べば1ファイルだけを読み込み可能。

An image from Notion

 

分析対象チャンネルのフォルダを選択。統合ボタンから「データの統合と変換」をクリック。

An image from Notion

 

4. 自動生成された「ファイルの変換」関数を手直し

エラーになっている「サンプルファイルの変換」→適用したステップ内「ソース」を選択。

An image from Notion

 

変換タブ内の「テーブルへの変換」をクリック。

An image from Notion

 

そのままOK

An image from Notion

 

Column1の展開アイコンをクリック→「さらに読み込む」→「OK」

An image from Notion

 

×アイコンをクリックして「テーブルに変換済み1」を削除

An image from Notion

 

5. シートに読み込む

閉じて読み込む でシートに読み込む

An image from Notion

 

これでフォルダ内のJSONファイルが全て結合された状態でシートに読み込まれる!

ちなみにベイマックスは検証用bot。botだとuserフィールドが空になるらしい。

An image from Notion

 

あとは煮るなり焼くなり。ユーザ別発言数をグラフ化してみたり。形態素解析でキーワードの出現率を調べてみたりなどなど。このチャンネルはbot専用だから何も面白くないけどね。

ユーザやチャンネルの詳細データは「users.json」や「channels.json」に入っているので、それを別途読み込んでリレーションすることになる。

このへんの記事が比較的やさしく解説していて参考になりそう。

Power Queryで2つのデータの共通項目を関連付け(マージ)して取り込む

https://hamachan.info/excel2019-powerquery-kyotu/