はじめに
株式会社CastingONEでバックエンドエンジニアをしている村上です。
弊社では派遣会社向けのCRMを開発しており、求職者の情報を管理したり、メールやLINEで求人の紹介ができる配信機能を提供しています。
先日、この配信機能の効果を測る「アナリティクス」という機能を追加することになり、それに必要な集計テーブルを設計しました。
今回は、そのときのことを振り返って「◯◯しておいて良かった」と「◯◯しておけば良かった」点をまとめます。
作った機能の概要
一例として、アナリティクスでは下記のようなことを分析できます。
- 何人に求人紹介のメールを配信したか
- 何人がそのメールを開封したか
- 何人がメール経由で求人ページを開いたか
- 何人がメール経由で求人に応募したか
これらの集計結果を月次や週次でどのように推移しているか確認したり、曜日別にグルーピングして効果を比較したりします。
↓月次で推移を確認したり
↓週次で推移を確認したり
↓曜日別にグルーピングして効果を比較したり
◯◯しておいて良かった
そもそもの話として、正規化の観点から考えると「集計テーブルを作らずにリアルタイムで計算するのが理想なんだろうな...」と考えていました。既存のテーブルから計算できるものを別のテーブルに保存すると、二重管理になるためです。
ただ、リアルタイムで計算するにはデータ量が多すぎたので、その理想は早々に諦めました。
と言うわけで集計テーブルを作ることになったのですが、アナリティクスでは月次集計と週次集計をはじめ多角的な集計を行います。同じ期間を集計するとしても、下記のように集計結果が異なります。
月次の場合
| 期間 | 配信人数 |
|---|---|
| 2024/09 | 10 |
| 2024/10 | 2 |
週次の場合
| 期間 | 配信人数 |
|---|---|
| 2024/09/01~2024/09/07 | 5 |
| 2024/09/08~2024/09/14 | 1 |
| 2024/09/15~2024/09/21 | 3 |
| 2024/09/22~2024/09/28 | 1 |
| 2024/09/29~2024/10/05 | 2 |
曜日別の場合
| 期間 | 配信人数 |
|---|---|
| 月曜日 | 5 |
| 火曜日 | 1 |
| 水曜日 | 2 |
| 木曜日 | 1 |
| 金曜日 | 1 |
| 土曜日 | 0 |
| 日曜日 | 0 |
素直に考えるなら月次・週次・曜日別の集計テーブルを別々に作る方法がありますが、集計元のテーブルと合わせて四重管理になってしまいます。
- 集計元のテーブル
- 月次の集計テーブル
- 週次の集計テーブル
- 曜日別の集計テーブル
パフォーマンスとの兼ね合いで一元管理は不可能だったとしても、せめて二重管理くらいには収めたいです。少しだけ工夫して、月次・週次・曜日別の集計テーブルを配信別の集計テーブルにまとめることにしました。
- 集計元のテーブル
- 配信別の集計テーブル
配信別の集計テーブルには配信日時のカラムを持たせて、1日1回のバッチで 集計元のテーブル から 配信別の集計テーブル に集計結果を保存。APIにリクエストが来たら、配信別の集計テーブル から月次・週次・曜日別に再集計しクライアントに返す方法にしました。
例えば、曜日別の再集計は下記のように行います。
SELECT
EXTRACT(DOW FROM 配信日時) dow,
SUM(配信人数)
FROM 配信別の集計テーブル
GROUP BY dow
APIで再集計するので多少パフォーマンスは劣化しますが、バッチによって 配信別の集計 までは事前に済んでいるので、弊社のプロダクトにおいては無視できるレベルでした。データも二重管理で収めることができ、良い加減の落としどころだったと思います。
◯◯しておけば良かった
月次や週次、曜日別の集計に加えて、アナリティクス機能では配信先ユーザーの年代別でも集計することができ、どの年代の反応が多かったか（あるいは少なかったか）を分析できます。
このために作成したテーブルは下記の構成でした
age_typeのカラムに10歳ごとの区分値(10代,20代,30代...)を登録しているのですが、今後この区分を5歳ごと(10代前半,10代後半,20代前半...)にする等の変更が入ると、過去の集計データを使い回せません。
この場合も 年代別 ではなく、年齢別 で1歳ごとに集計しておいた方が変更に強かったなと反省しています。
終わりに
集計テーブルを設計してみて、パフォーマンスとデータの冗長性を天秤にかけて落とし所を見つける過程はとても楽しく感じました。
今回はデータの冗長性を避けることを重視しましたが、データ量などの前提条件が異なるとまた別の判断になったと思います。何かの参考になれば嬉しいです。
Discussion