f:id:zacky2:20211220150627j:plain

はじめに

この記事はM&A クラウドアドベントカレンダー2021の21日目の記事です。

みなさんどうもこんにちは。エンジニアの津崎です。普段はPHPとTypeScriptを使ってマッチングプラットフォームの開発を行っています。

今日はデータ分析全然わからないけどやってみチャオ！というノリで、M&A クラウドのSlackで使われている絵文字の使用数ランキングを出してみました。分析にはAWSのデータ分析ツールであるAthenaを使っています。 Athenaを使って実際にデータを分析するのは今回が初めてだったので、SlackのメッセージデータをAthenaに格納する作業に時間の９割が持っていかれました。

結果発表🎉

まずは、集計結果をみてましょう！

(再生ボタンをクリックしてください)

動画版はこちら:

youtu.be

集計物：メッセージに対するリアクションとして使われた絵文字の使用数（メッセージ本文の絵文字を含まない）
対象チャンネル：publicチャンネルすべて
対象の絵文字：カスタム絵文字
対象期間：2021/01/01 ~ 2021/12/13 くらい
データサイズ： 800MB程度（無圧縮）
分析対象メッセージ：11万

【３位】お大事に

３位は「お大事に」でした。これは予想外。 f:id:zacky2:20211218185857p:plain

おっと、大丈夫です。M&A クラウドの社員の健康状態は良好ですよ。体調不良メッセージに対して「1 Team」な精神で部署をまたいで「お大事に」をつけているので使用数が多くなっているんじゃないかなと思います。

また、別の意味の「お大事に」でも使われているのも使用数が多い要因になっていそうです。

f:id:zacky2:20211218185748p:plain f:id:zacky2:20211218185817p:plain

【２位】1 Team (Fever)

２位は「1 Team」でした。「1 Team」はM&A クラウドのバリューの一つです。M&A クラウドの社員はみんな「1 Team」が好きなのがよくわかりますね。笑この絵文字は、シンプルに「1 Team」な行動に対してよく利用されます。一丸となって仕事を成し遂げたときや、他メンバーを思いやるメッセージなどです。

f:id:zacky2:20211218185925p:plain

f:id:zacky2:20211218190400p:plain

【１位】ナイスシェア

「ナイスシェア」がぶっちぎりの１位でした。 M&A クラウドのバリュー「1 Team」の要素として「共有・共感・共鳴」があります。「ナイスシェア」というのはまさに「1 Team」を表しているなと思います。この「ナイスシェア」は、部署間でのよい情報共有や、ニュースやネット記事などの有益な情報をシェアした時、大事なことのリマインドといった「ナイスシェア」なタイミングによく利用されています。

f:id:zacky2:20211218190902p:plain f:id:zacky2:20211218190934p:plain f:id:zacky2:20211218190923p:plain

どうやってやったかざっくり

ここからは集計結果ではなく、どのようにこれを作ったかという話をします。

Slackのエクポート機能(手動) 
　　　　　⬇️
S3にアップロード(手動)
　　　　　⬇️
Lambda （Athenaに投入する前にデータの変換処理）
　　　　　⬇️
Glue クローラでテーブル作成
　　　　　⬇️
AthenaでSQLを使ってデータ集計
　　　　　⬇️
Google SpreadSheetで表作成
　　　　　⬇️
flourishでグラフ化

Lambdaとは、AWSのサーバレスでプログラムを実行してくれるサービスです。サーバを用意せずクラウド上でプログラムを実行できるのでとても便利です。

Athenaとは、AWSのビッグデータ分析ツールです。AWSのS3というクラウドストレージに格納されたファイルをSQLで分析できます。最近はS3以外にも色々連携ができてなんでもできる感じに進化しているようです。

Glueクローラとは、S3に格納されたデータを解析してAthenaが分析できるようにテーブル化してくれるものです。

flourishとは、グラフ作成ができるWebサービスです。Bar Chart Raceを簡単に作れるということだったので利用しました。

（以下はエンジニア向けな内容となります。）

今回はAthenaを使ってみたいというモチベーションがあったため、試行錯誤しながら時間をかけて分析の準備を行いました。簡単に分析したい場合は、PHPなどのお好みのスクリプトでJSONを集計してあげれば、数十分程度で集計できるかなと思います。

Slackのエクポート機能(手動)

Slackの標準機能でメッセージのログをエクスポートします。（これには多分管理者権限クラスの権限が必要です）

S3にアップロード

S3にバケットを作ってファイルをアップロードします。 ZIPだと数十MBなのに解凍すると数百MBになってアップロードに数時間かかりました。

Lambda

SlackのメッセージはJSON形式で保存されているのですが、Athenaで読み込めるJSON形式と違ったため変換の必要がありました。（これに気づくまでかなりの時間を溶かした） Athenaでは1行1オブジェクトの形になっている必要があります。例えば以下のような形です。

{id:1, text:"taro"}
{id:2, text:"jiro"}

Slackメッセージは簡単にいうと以下のような感じになっていました。

[
  {
    id:1,
    text:"taro"
  },
  {
    id:2,
    text:"jiro"
  }
]

Lamddaの関数を作り、JSON形式を変換する処理を書きました。また、Athenaに食わせるために試行錯誤した結果、以下のような機能も追加しています。

メッセージ以外のJSONファイル（channels.json, users.jsonなど）を除外する
ディレクトリ名（Slackチャンネル名）をランダム文字列に変換する（ディレクトリ名に日本語があるとGlue クローラーが途中で落ちるっぽい）
ディレクトリをフラットにして格納する（Slackチャンネルごとにディレクトリが切られているとクローラーが別テーブルとして認識してしまい、1000個以上のテーブルに分割されてしまうので、苦肉の策として）
処理に最大処理時間の15分以上かかってしまうので、引数で処理範囲を渡して分割して実行できるようにした
- 本来こういう処理はGlue ジョブでやるべきなのかもしれません

f:id:zacky2:20211218193934p:plain 並列実行が完了した図

Glue クローラーでテーブル作成

Glue クローラーでS3内にある変換後のJSONを解析しテーブルを作ります。テーブルが1000個以上できたり、途中で処理が止まったりしてかなり苦しめられましたが、Lambda側での工夫によりなんとかなりました。

f:id:zacky2:20211218194144p:plain テーブルを単一にしたくてチェックしても全くいうことを聞いてくれなかった。本当にじゃじゃ馬。

大量にできてしまったテーブルを100個ずつ手動で頑張って消してる図（SQLでスキーマごと消せば簡単であることに途中で気づいた）

AthenaでSQLでデータ集計

待ちに待ったこの瞬間。 10万のSlackメッセージがAthena上から検索できます。 PHP スクリプトで分析すればAthenaにデータを入れるために数時間溶かすことなく分析できたのに、いろいろな苦労を経てようやく分析の準備ができました。

メッセージが単一テーブルとしてはいった・・・！

11万レコード！

元のデータがJSONなのでカラムの中に配列データがあり、配列データの中に配列データがあるといったデータ構造をしています。クエリはSQLなのでMySQLなんかと似てますが、配列の分析をするところでちょっと苦労しました。

いつどの絵文字が何回使われたかを日別で出力します。

WITH reaction_record AS (
    WITH reactions_table AS (
        SELECT client_msg_id,
            reactions,
            ts
        FROM "slack-data"."dest2"
        where reactions is not null
    )
    SELECT date_format(from_unixtime(cast (ts as double)), '%Y/%m/%d') AS date_ymd,
        reaction.name,
        reaction.users,
        reaction.count,
        ts
    FROM reactions_table,
        UNNEST(reactions_table.reactions) AS t(reaction)
)
SELECT reaction_record.date_ymd,
    reaction_record.name,
    sum(count)
FROM reaction_record
group by date_ymd,
    name
order by 
    date_ymd

UNNEST()という関数で配列データを展開してテーブル化したりしています。調べながらやればなんとかなりました。

f:id:zacky2:20211218200201p:plain

11万レコードありますが、わずか2.7秒で処理が終わっています。まあ普通のRDSで実行した場合に11万レコードがどのぐらい時間がかかるかはちょっとわかりません。（このデータ量じゃ大差ないかも）

スキャンしたレコードが150MBとなっていますが、無圧縮のJSONだと800MB程度あったはずなので、WHERE句の絞り込みによって余計なデータの読み込みを抑えてくれていそうです。

GoogleSpreadsheetでさらに集計

flourishでBar Chart Raceを作るには以下のような表が必要です。

f:id:zacky2:20211218200841p:plain

AthenaのSQLで上記のような表を作る方法を思いつかなかったので、GoogleSpreadSheetで加工します。

ステップとしては以下です。

2021/1/1 ~ 2021/12/31までの列を作る
Athenaで出力した表から特定日の絵文字使用数を取ってくる
上記の表を累積の表にする（前日までの累積+今日の値）
後述のemojiの画像URLを表に追加する
カスタム絵文字以外を除外する

カスタム絵文字以外は絵文字の画像URLを取得するのが難しかったため捨てました。ちなみに、本当に一番使われてる絵文字は「+1 (👍)」でした。

emojiのkeyと画像URLのマッピングを取得する

APIを叩く

curl -H "Authorization: Bearer {token}" https://slack.com/api/emoji.list > emoji.json

csvに変換する

 jq -r ".emoji|to_entries|.[]|[.key, .value]|@csv" emoji.json > emoji.csv

Flourishでグラフ化

app.flourish.studio

簡単にサインインでき、使い方も適当にやったらできました。簡単です。グラフのグループ化や速度、画像の表示など調整して完了です。

まとめ

以前PHPのスクリプトでシンプルなランキングの集計をしたことがあったのですが、 Athenaで分析作業をすること自体が初めてだったので、かなりトライアンドエラーで進めていきました。

Lambda ほとんど初めて使った件
- 実行してログ確認して修正するのめっちゃ地道
- 15分の実行上限を超えるので、レンジを区切って手動で並列実行
Glue クローラじゃじゃ馬問題
- 実行完了したはいいものの、JSONがぶっ壊れていてAthenaで読めない（1行のJSONにしなくてはいけない）
- Glue クローラなぜか途中で落ちる
- 単一のテーブルではなく大量のテーブル(1000テーブル)として生成される
jqわからんw
Google Spreadsheet上でゴリゴリ表を作る(最終的にflourishに食わせるデータを作るために)

Thank you for reading !

次のアドベントカレンダーは営業部の南戸さんです。お楽しみに！

M&Aクラウド開発者ブログ

【Athena】M&Aクラウドの2021年を絵文字で振り返る【使ってみた】

はじめに

結果発表🎉

【３位】お大事に

【２位】1 Team (Fever)

【１位】ナイスシェア

ランキング振り返り

どうやってやったかざっくり

Slackのエクポート機能(手動)

S3にアップロード

Lambda

Glue クローラーでテーブル作成

AthenaでSQLでデータ集計

GoogleSpreadsheetでさらに集計

emojiのkeyと画像URLのマッピングを取得する

Flourishでグラフ化

まとめ

はじめに

結果発表🎉

【３位】お大事に

【２位】1 Team (Fever)

【１位】 ナイスシェア

ランキング振り返り

どうやってやったかざっくり

Slackのエクポート機能(手動)

S3にアップロード

Lambda

Glue クローラーでテーブル作成

AthenaでSQLでデータ集計

GoogleSpreadsheetでさらに集計

emojiのkeyと画像URLのマッピングを取得する

Flourishでグラフ化

まとめ

【１位】ナイスシェア