SendGrid の送信ログを無料で7日間以上見られるように、BigQuery にリアルタイムで保存する

これはなに？

イマチケというAmazon Payでサクッと買える有料ネットライブのチケット販売サービスを開発・運営しています。

そこでは、SendGridを使って購入確認メールを送っているのですが、携帯キャリア系メールアドレスの迷惑メールブロックやら RFC違反のメールアドレスでやらで購入確認メールが送れないことがままあり、お客様からの問い合わせが来たときに調査が必要でした。

調査のために SendGrid のメールアクティビティを確認しますが、とりあえず無料のプランではじめているため、管理UI上で確認できるメール送信ログが7日間しか残りません。しかし、お客様からの問い合わせはチケットを買ってから、2週間後のライブ配信直前などに来ることがあり、調べるころには履歴が残っておらず、メールが届かない原因がわからないということがありました。

SendGrid には メール送信イベントごとの Webhook があるので、手が空いたらこれを Cloud Functions for Firebase で受けて、Firestore を経由して BigQuery に残すようにしました。その手順とコードを残しておきます。

1. Webhook を受ける Cloud Function を用意する

SendGrid から Webhook で POST が来るので、それを Cloud Functions で受けて Firestore に1送信イベントを1ドキュメントとして追加します。

Cloud Functions 受け口のURLは以下のようになります。
https://asia-northeast1-<projectid>.cloudfunctions.net/postMailLog

実際に SendGrid の Webhook URLに設定するURLはBASIC 認証設定を入れた以下のようなURLになります
https://<ユーザ名>:<パスワード>@asia-northeast1-<projectid>.cloudfunctions.net/postMailLog

以下そのソースコードです。typescript で書きました。

import * as functions from 'firebase-functions'
import * as admin from 'firebase-admin'

admin.initializeApp(functions.config().firebase)
const firestore = admin.firestore()

const BASIC_USERNAME = '***'
const BASIC_PASSWORD = '***'

// BASIC 認証
function basicAuth(authorization: string): boolean {
  const parts = Buffer.from(authorization.replace('Basic ', ''), 'base64')
    .toString()
    .split(':')
  if (parts[0] !== BASIC_USERNAME || parts[1] !== BASIC_PASSWORD) {
    return false
  } else {
    return true
  }
}

// Webhook を受けるエントリポイント
export const postMailLog = functions
  .region('asia-northeast1') // 東京リージョン
  .https.onRequest(async (request, response) => {
    try {
      functions.logger.info('postMailLog', request.body)

      if (!basicAuth(request.get('authorization') || '')) {
        throw new Error('basicAuth failed')
      }

      const logRef = firestore.collection('maillogs')
      const events = request.body || []
      for (const event of events) {
        // イベントごとに firestore にドキュメントとして保存
        event.createdAt = admin.firestore.FieldValue.serverTimestamp()
        await logRef.add(event)
        functions.logger.info('maillog added', { event })
      }

      functions.logger.info('postMailLog completed')
      response.status(200).end()
    } catch (err) {
      functions.logger.info('error', err)
      response.status(500).send(err)
    }
  })

2. SendGrid の Webhook を設定する

SendGrid の管理UIにログインして、左側のメニューから Settings -> Mail Settings を選び、出てくる画面の一番上にある "Event Webhook" の鉛筆アイコンをクリックして、Webhook 設定画面を開きます。

SendGrid / Mail Settings

出てくる設定画面に、受け口のWebhook URLを設定し、記録したいイベントにチェックをつけて、保存します。(黒塗りにしてるところは BASIC認証のユーザ名、パスワードです。)

これで一旦 Firestore にほぼリアルタイムで保存されるようになりました。
次は、これを BigQuery に保存します。

3. Firebase Extensionを設定し、BigQuery に連動で自動保存。

Firebase の拡張機能、Export Collections to BigQuery を設定し Firestore に保存されたタイミングで BigQuery に保存します。

拡張機能のカタログ画面から、Export Collections to BigQuery を探して「Install]ボタンをクリックします。

Export Collections to BigQuery

4ステップの設定画面がでてきます。入力項目がでてくる最後のステップまで、そのまま「次へ」で進みます。最後の設定画面では出力する先の BigQuery 上の設定を入力します。

拡張機能を構成

設定項目は以下のとおりに設定しました。

項目	設定	説明
Cloud Functions location	Tokyo (asia-northeast1)	この拡張機能が動作するリージョンを設定します。あとで変更できません。
Colleciton path	maillogs	同期元の Firestore 上にあるコレクションのパスを設定
Dataset ID	firestore_export	同期先 BigQuery 上の Dataset を設定
Table ID	maillogs	同期先 BigQuery 上のテーブル名を設定

これで、「拡張機能をインストール」をクリックするとインストールが始まります。4分程度待つと完了です。この拡張機能は Firestore のイベントトリガで起動して、BigQuery にドキュメントを保存してくれるものです。

以上で完了です。最終的に SendGrid でメールを送信してから、数秒程度で送信ログが BigQuery に反映される形になります。

4. Datastudio で表示させるために、BigQuery でビューを作成する

あとは、これまた無料のGoogle Dataportalでデータソースにこの BigQuery テーブルを指定して、画面に出してやります。ただ、BigQuery には JSON形式で保存されてしまって取り扱いづらいです。そこで、BigQuery 上にビューを作ってそれをデータソースにすることにしました。ついでにタイムスタンプも日本に設定して見やすくします。

CREATE VIEW imaticket.maillogs AS
SELECT 
  FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', timestamp, 'Asia/Tokyo') as timestamp,
  json_extract_scalar(data, "$.event") as event,
  json_extract_scalar(data, "$.email") as email,
  ifnull(json_extract_scalar(data, "$.response"),json_extract_scalar(data, "$.reason")) as response
FROM imaticket.maillogs_raw_changelog
WHERE
  json_extract_scalar(data, "$.event") is not null

Firestore の拡張機能で保存されるテーブル名には suffix として _raw_changelog というのがついています。それを元に maillogs という VIEW を作りました。

datastudio からはこのビューをデータソースにして、ダッシュボードを作ればokです。

無料になる範囲

• Cloud Functions for Firebase: 月200万回まで無料です。
• Firestore: 1日2回まで書き込み無料です。
• BigQuery: ストレージ毎月10GBまで無料、クエリは毎月1TBまで無料

1回のメール送信で、だいたい平均して2回ちょいの書き込みが行われます (processed, deliveredの2イベント)。上記から、SendGrid の無料枠1万2000通/月なので、その範囲内ならすべて無料でつかえますね。