この記事は Lancers Advent Calendar 2022 の16日目の記事です。

こんにちは！プロダクト開発部エンジニアの日吉（@yosukehiyoshi）です。
昨日は、光速改善クワッスこと高橋さん（@sazanami5）の Redash × BigQueryのデータ抽出・分析で使えるtips をお送りしました！
その中でも紹介されていた通り、ランサーズではデータ分析基盤として BigQuery と Redash を使用しています。
また、各種メールの送信では SendGrid を使用しています。

本日は小ネタですが、この SendGrid でメール送信したユーザのアクションを BigQuery に送り、分析できるようにするための方法をご紹介します！

※ 今回のネタは、10月に開催したランサーズ開発合宿2022 にて検証したものです。ご協力いただきましたチームメンバーの皆様、ありがとうございました！

概要

SendGrid には、送信ユーザがアクションを起こした時に Webhook を送る機能（Event Webhook）があります。これを BigQuery に送るようにすれば、メール施策の成果分析に使えるじゃないか！というシンプルなお話です。

構成は下記の通りです。サッと試せるよう GCP 中心の構成にしています。

準備するもの

なにはなくとも GCP アカウント
- BigQuery – 言わずもがなですが
- CloudFunctions – SendGrid から送信された EventWebhook を BigQuery に受け流す
なにはなくとも SendGrid アカウント
- テストアカウントを作成する場合は、登録から利用可能になるまで1営業日かかるのでご注意を！

BigQuery のデータセットとテーブルを作成

まずはデータを格納する BigQuery のデータセットとテーブルを作成します。

※ 例として、データセット ID は sendgrid_webhook_tutorial とし、その中に user_events テーブルを作成しています。

テーブル user_events のフィールド

フィールド名	種類	モード	概要
sg_message_id	STRING	NULLABLE	メッセージID（SendGrid側で自動生成される）
email	STRING	NULLABLE	メールアドレス ⚠️ 個人情報なのでハッシュ化必須！
event	STRING	NULLABLE	イベント名 ※ 配信系イベントやエンゲージ系イベントで定義されている event パラメータ
category	STRING	NULLABLE	SendGrid 側で指定可能なカテゴリ ※ カテゴリは複数指定可能なので ARRAY 型にするべきだが、今回は単純に STRING としておく
timestamp	TIMESTAMP	NULLABLE	イベント発生日時 ※ UNIX タイムスタンプ

CloudFunctions の設定

続いて、CloudFunctions の設定をしていきます。

関数の作成

下記のキャプチャの通り、CloudFunctions に関数を新規作成します。

※ 例として、関数名を sendgridWebhook としています。
※ 初めて関数を作成する場合、必要なAPI（Cloud Build API、Cloud Functions API、Cloud Logging API、Cloud Pub/Sub API）を有効化するか確認されるので、適宜有効にします。

コードの追加

エントリーポイントはデフォルトの helloWorld でも良いですが、今回は関数名と同じく sendgridWebhook としておきます。

package.json

BiqQuery のライブラリを追加します。

{
  "name": "sample-http",
  "version": "0.0.1",
  "dependencies": {
    "@google-cloud/bigquery": "^2.1.0"
  }
}

index.js

受け取ったデータを BigQuery に INSERT する JS を書きます。

const {createHash} = require('crypto');
const {BigQuery} = require('@google-cloud/bigquery');

// GCPのプロジェクトIDを指定
const projectId = 'my-gcp-project-id';
// BigQueryデータセットIDを指定
const datasetId = 'sendgrid_webhook_tutorial';
// データセットのテーブル名を指定
const tableName = 'user_events';

// BigQueryインスタンスを生成
const bigQuery = new BigQuery({ projectId });

// エントリーポイントと同じく、sendgridWebhookにする
exports.sendgridWebhook = (req, res) => {
  const data = req.body;
  const rows = [];
  let timestamp, categories;
  for (let i=0; i<data.length; i++) {
    timestamp = new Date(data[i]['timestamp'] * 1000);
    categories = data[i]['category'] || [];
    rows.push({
      'sg_message_id': data[i]['sg_message_id'],
      'email': encryptSha256(data[i]['email']), // 個人情報なのでハッシュ化
      'event': data[i]['event'],
      'category': categories.join(','),
      'timestamp': timestamp,
    });
  }

  insertBigQuery(rows);
  res.status(200).send(req.body);
};

// BigQueryにINSERT
const insertBigQuery = (rows) => {
  bigQuery
    .dataset(datasetId)
    .table(tableName)
    .insert(rows) 
    .then(data => {
      console.log(`Inserted rows : `, rows.length);
    })
    .catch(e => {
      console.error(e);
    });
};

// SHA256暗号化
const encryptSha256 = (str) => {
  const hash = createHash('sha256');
  hash.update(str);

  return hash.digest('hex');
};

動作確認

試しに curl を使用して CloudFunctions にデータを送信してみます。

$ curl -X POST \
  https://asia-northeast1-XXXXXXXX-XXXXXXXX.cloudfunctions.net/sendgridWebhook \
  -H "Content-Type: application/json" \
  -d '[{
    "sg_message_id" : "ABC1234567890",
    "email" : "hoge@lancers.co.jp",
    "event" : "click",
    "category" : [
      "category-1",
      "category-2"
    ],
    "timestamp" : 1671159600
  }]'

BigQuery にデータが入っています。うまくいきました！

SendGrid の設定

続いて SendGrid の EventWebhook 設定をしていきます。
公式のドキュメントに記載されている手順に従って進めます。

※ 今回は Authorization Method を None にしていますが、実際の利用では OAuth 2.0 による認証をおこなうことをおすすめします。

動かしてみる

一連の流れが動作するか、確認しましょう！

まずは SendGrid API を利用してメールを送ってみます。
クリックイベントが動作するか確認するため、メール本文にはリンクを入れています。

$ curl -X POST \
  https://api.sendgrid.com/v3/mail/send \
  -H "Authorization: Bearer {{SendGrid APIキー}}" \
  -H 'Content-Type: application/json' \
  -d '{
    "personalizations" : [
      {
        "to" : [ { "email" : "{{送信先メールアドレス}}" } ]
      }
    ],
    "from" : { "email" : "{{送信元メールアドレス}}" },
    "subject" : "Test mail.",
    "content" : [
      {
        "type" : "text/html",
        "value" : "<html>This is test mail.<br><a href=\"https://www.lancers.jp/\">Visit Lancers</a></html>"
      }
    ],
    "categories" : [
      "category-3",
      "category-4"
    ]
  }'

メールが届きました！

本文内のリンクをクリックした後に、BigQuery を見てみると・・・！

レコードが追加されています！成功です！

さいごに

いかがでしたか。
単純に到達率・開封率などを見るのであれば SendGrid のダッシュボードで完結できますが、BigQuery と連携することにより様々なデータと結合して分析することが可能になります。
（ただし、メールアドレスで結合したい場合はハッシュ化のアルゴリズムを統一する必要はあります）

今回は CloudFunctions を Webhook の受け口として組みましたが、AWS 構成であれば、Lambda を使用しても良いかもしれないですね。

ということで、あす17日は我らがワカコねぇさんこと若林さん（@wakabayashiayako）です！どうぞお楽しみに！