BigQueryとSendGridを組み合わせて、顧客データを基にパーソナライズしたメールを送信する方法を解説します。
送信したメールの効果測定・分析方法についても少し触れます。

1. SendGridテンプレートの作成

Sendgrid公式ドキュメントを参考に、Dyamic templateを作成します。

今回は以下のようなメールを送りたいとします。

A様への今週のおすすめ商品です！

  1. 商品B (1000円)
  2. 商品C (1500円)

購入はこちらから！ https://example.com

Dynamic templateは以下のように記述します。

1
2
3
4
5
6
7
8

{{customer_name}}様への今週のおすすめ商品です！

{{#each products}}
{{this.number}}. {{this.product_name}} ({{this.price}}円)
{{/each}}

購入はこちらから！
https://example.com

このテンプレートに対して、送信対象ごとに以下のようなデータを埋め込む事が目標になります。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

{
    "customer_name": "A",
    "products": [
        {
            "number": 1,
            "product_name": "商品B",
            "price": 1000
        },
        {
            "number": 1,
            "product_name": "商品C",
            "price": 1500
        },
    ]
}

2. 実装

BigQueryテーブル mydataset.mytable に顧客へのリコメンドデータが入っているとします。
本稿では割愛しますが、BigQuery MLを用いれば簡単にリコメンドデータを生成することができます。

emailcustomer_namenumberproduct_nameprice
a@example.comA1商品B1000
a@example.comA2商品C1500

このリコメンド情報をDynamic templateに埋め込んでメールを送信するサンプルコードです。
実際に運用する場合は例外処理を追加し、ワークフローエンジンで実行することになります。

main.py

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45

from google.cloud import bigquery
import sendgrid
from sendgrid.helpers.mail import Mail, To
import os
import json

SENDGRID_API_KEY = os.getenv('SENDGRID_API_KEY')
SENDGRID_TEMPLATE_ID = os.getenv('SENDGRID_TEMPLATE_ID')
sendgrid_client = sendgrid.SendGridAPIClient(SENDGRID_API_KEY)
bigquery_client = bigquery.Client()

query = """
SELECT
  email,
  ANY_VALUE(customer_name) customer_name,
  ARRAY_AGG(
    STRUCT(
      number,
      product_name,
      price
    )
    ORDER BY number
  ) AS products
FROM
  mydataset.mytable
GROUP BY email
"""

query_job = bigquery_client.query(query)
emails = [
    To(
        email=row["email"],
        dynamic_template_data=dict(row.items()) # (*1)クエリ結果を辞書型にしてDynamic templateに渡す
    ) for row in query_job
]

message = Mail(
    from_email=("sender-address@example.com", "Sender name"),
    to_emails=emails,
    is_multiple=True)
message.template_id = SENDGRID_TEMPLATE_ID

response = sendgrid_client.send(message)
x_message_id = response.headers["X-Message-Id"]
print(x_message_id) # (*2)このIDが分析に必須となるため、必要に応じてBigQueryにINSERTする等の実装を追加する

3. イベント収集

SendGridはメールが開封されたり、文中の外部リンクがクリックされたりした際にWebhookでイベントを送って来る事が可能です。
https://sendgrid.kke.co.jp/docs/API_Reference/Webhooks/event.html

このイベントをPub/Sub等で受け取り、BigQueryに入れる仕組みを用意しておきます。

POSTされるJSONデータにsmtp-idというプロパティがありますが、BigQueryではカラム名に - が使えません。
場合によってはCloudFunctions等でアンダースコアに置換するなどの処理が必要になります。

4. 分析

Pythonコードの最後に登場した X-Message-Idですが、 イベント収集のデータに含まれる sg_message_id と以下のような関係があります。

従って、とあるバッチで送信されたメールに関するイベントは以下のようなクエリで抽出することが可能です。

1
2
3
4
5
6

SELECT
  *
FROM
  mydataset.sendgrid_webhook_event
WHERE
  SPLIT(sg_message_id,".")[SAFE_OFFSET(0)] = 'X-Message-Id value'

この仕組みを利用して開封率、クリック率、Unsubscribe数などをモニタリングしつつ改善プロセスを回していきます。