Amazon Kinesis Data Firehose を用いた Amazon SES 送信イベントの解析
こんにちは
株式会社 POL にてエンジニアをしている山田高寛です。
今日は Amazon SES の送信イベントを解析して、SES から送信したメールの開封数やクリック数を計測する方法について紹介します。
背景
SES は送信、配信、オープン、クリックなどといったタイプの E メール送信イベントを発行し、Amazon CloudWatch や Amazon Kinesis Data Firehose にイベントを送信することができます [1]。
例えば、これを利用して開封数やクリック数といったメトリクスを CloudWatch メトリクスからグラフ化して確認することができます [2]。
E メール送信イベントは、Configuration Set を作成して SES 経由でメールを送信する際に作成した Configuration Set を指定することでを発行できます。
Configuration Set にはタグをつけられるので、タグに E メール種別を埋め込んでおくことで、下記のようにメール種別ごとのメトリクスを計測することが可能です。
ただ、CloudWatch メトリクスは同一人物が開封、クリックを実施してもそれぞれのイベントをユニークなものとしてカウントされてしまいます [3]。
例えば example@pol.co.jp 宛に送ったメールに含まれる labbase.jp というリンクを別の時間帯に 2 回踏んだ場合は、それぞれの時間帯にクリック数のメトリクスが 1 カウントされてしまいます。
したがって、メールアドレスごとの開封数やクリック率を計測したい、といったようなユースケースには不適切です。
そこで、E メール送信イベントの発行先を Amazon Kinesis Data Firehose にして、Parquet 形式で S3 に保存し、保存した Parquet ファイルを Amazon Athena を用いて集計できるようにしました。
その知見について共有できればと思います。
なお、今回の話については AWS ブログ [4] にても公開されておりますが、こちらで紹介されているものとは下記の点が異なります。
・Lambda 関数を必要としない
・JSON ではなく Parquet 形式で S3 に保存する
アーキテクチャ
SES の Configuration Set にて設定した Event types と一致する E メール送信イベントが発生すると Kinesis Data Firehorse に対してイベントの詳細が格納された JSON 形式のレコードが発行されます。Kinesis Data Firehorse では入力データ形式を JSON から Apache Parquet へと変換して S3 へと保存します。
以下にて、上記アーキテクチャを構築する手順を記載していきます。
構築手順
S3 に Parquet 形式で E メール送信イベントを格納するまで
まず、AWS Glue Data catalog にて SES から発行される E メール送信イベントの JSON 形式でのスキーマを定義しておきます。
例えば、送信、開封、クリックイベントのみを解析したい場合は下記のような DDL を Athena から実行することでスキーマを定義できます。
CREATE EXTERNAL TABLE `sesmaster`(
`eventtype` string,
`complaint` struct<arrivaldate:string,complainedrecipients:array<struct<emailaddress:string>>,complaintfeedbacktype:string,feedbackid:string,timestamp:string,useragent:string>,
`bounce` struct<bouncedrecipients:array<struct<action:string,diagnosticcode:string,emailaddress:string,status:string>>,bouncesubtype:string,bouncetype:string,feedbackid:string,reportingmta:string,timestamp:string>,
`mail` struct<timestamp:string,source:string,sourceArn:string,sendingAccountId:string,messageId:string,destination:string,headersTruncated:boolean,headers:array<struct<name:string,value:string>>,commonHeaders:struct<`from`:array<string>,to:array<string>,messageId:string,subject:string>,tags:struct<ses_operation:array<string>,Dim:array<string>,ses_configurationset:array<string>,ses_source_ip:array<string>,ses_outgoing_ip:array<string>,ses_from_domain:array<string>,ses_caller_identity:array<string>>>,
`send` string,
`delivery` struct<processingtimemillis:int,recipients:array<string>,reportingmta:string,smtpresponse:string,timestamp:string>,
`click` struct<ipaddress:string,link:string,linktags:string,timestamp:string,useragent:string>,
`open` struct<ipaddress:string,timestamp:string,userAgent:string>)
ROW FORMAT SERDE
'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES (
'mapping.ses_caller_identity'='ses:caller-identity',
'mapping.ses_configurationset'='ses:configuration-set',
'mapping.ses_from_domain'='ses:from-domain',
'mapping.ses_operation'='ses:operation',
'mapping.ses_source_ip'='ses:source-ip')
STORED AS INPUTFORMAT
'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
's3://bucketname/databasename/raw_ses_open_click_log'AWS ブログ [5] にて、hive-json-schema [6] というツールを用いて JSON ファイルからスキーマを作成する方法がまとめてありますので、必要に応じて参照してください。
次に、Kinesis Data Firehorse にて配信ストリームを作成します。
Step 2: Process records の Convert record format の箇所にて、Record format conversion のチェックを Enable にして、Output format を Apache Parquet にし、先程作成したスキーマ (Glue Data catalog 上のテーブル) を設定します。
その後、SES で E メール送信イベントの送信先を先ほど作成した Kinesis Data Firehorse の配信ストリームに設定した Configuration Set を作成します。
この際、Event types のチェックボックスにて、E メール送信イベントの種類を設定することができます。
送信、開封、クリックイベントのみで良い場合は Send, Click, Open を選択します。
以上の設定を実施することで、S3 に E メール送信イベントの詳細が格納された Parquet ファイルが格納されていきます。
次に、Athena でのE メール送信イベントのクエリをするまでの手順を紹介します。
Athena で Parquet 形式の E メール送信イベントをクエリする
まず、Glue Data catalog で Parquet 形式のスキーマを定義する必要があります。
これには Glue の Crawler を利用します。
Crawler を定期的に実行することで、SES から流れてくるデータの日付パーティションが増えても対応することができます。
Crawler の作成方法については AWS ブログ [4] に記載があるので参考にして下さい。
Crawler が無事実行されると、Parquet 形式に対応したテーブルが Glue Data catalog に作成されるので、Athena でクエリを実施できます。
例えば、送信先アドレスを絞ったクエリを実施したい場合は下記のようになります。
SELECT *
FROM "database"."parquet_ses_open_click_log"
where mail.destination = '["example@pol.co.jp"]'
eventtype というカラムに対象のレコードが送信先イベントなのか、開封もしくはクリックイベントなのかが格納されています。
また、mail というカラムが struct データタイプになっており、mail.xxxxx という形式でドットを使って struct データタイプの配下の要素にアクセスすることができます。
例えば、mail struct の中には送信先の他にも、メールに対して一意に割り当てられる messageId や メールの件名である subject が含まれております。
messageId を元に集約するクエリを実行することで、開封イベント・クリックイベントを重複なくカウントすることができます。
また、その他の要素の意味についてはドキュメント [7] に記載があります。
クエリ比較
JSON の形式のままクエリした場合と、Parquet 形式に変換した状態でクエリした場合を比較してみました。
JSON の場合
SELECT *
FROM "labbase_log"."sesmaster"
where mail.destination = '["example@pol.co.jp"]'
Parquet の場合
SELECT *
FROM "labbase_log"."parquet_ses_open_click_log"
where mail.destination = '["example@pol.co.jp"]'
クエリ速度はそこまで変化がありません (おそらく、全体をスキャンしているためだと思われます)。
しかし、スキャンしたデータ容量は JSON 形式に比べて Parquet 形式では 1/4 倍程度に減っています。
Athena の課金体系はスキャンされたデータ容量に対して実施されるので、Parquet 形式のほうがコストを抑えて解析することができます。
まとめ
Amazon Kinesis Data Firehose を使って、Amazon SES の送信イベントを Parquet 形式に変換して S3 に保存することで、コストを抑えてより詳細に SES の送信イベントを Amazon Athena にて解析することができました。
今後は Quick Sight を用いて、グラフィカルに送信イベントを確認できるダッシュボードを作成していきたいと思います。
POL プロダクト部について
株式会社 POL ではエンジニアを募集しております！
今回のように、各データを解析してサービスの成長に貢献したい人も Welcome です！
お気軽にご相談ください！
Forkwell
https://jobs.forkwell.com/pol/jobs/6372
Wantedly
https://www.wantedly.com/projects/522893
参考資料
・[1] Amazon SES イベント発行を使用して E メール送信をモニタリンスする - Amazon Simple Email Service
https://docs.aws.amazon.com/ja_jp/ses/latest/DeveloperGuide/monitor-using-event-publishing.html
・[2] CloudWatch からの Amazon SES イベントデータの取得 - Amazon Simple Email Service
https://docs.aws.amazon.com/ja_jp/ses/latest/DeveloperGuide/event-publishing-retrieving-cloudwatch.html
・[3] Amazon SES email sending metrics FAQs - Amazon Simple Email Service
https://docs.aws.amazon.com/ses/latest/DeveloperGuide/faqs-metrics.html
・[4] Analyze and improve email campaigns with Amazon Simple Email Service and Amazon QuickSight | AWS Messaging & Targeting Blog
https://aws.amazon.com/jp/blogs/messaging-and-targeting/analyze-and-improve-email-campaigns-with-amazon-simple-email-service-and-amazon-quicksight/
・[5] Create Tables in Amazon Athena from Nested JSON and Mappings Using JSONSerDe | AWS Big Data Blog
https://aws.amazon.com/jp/blogs/big-data/create-tables-in-amazon-athena-from-nested-json-and-mappings-using-jsonserde/
・[6] quux00/hive-json-schema: Tool to generate a Hive schema from a JSON example doc
https://github.com/quux00/hive-json-schema
・[7] Amazon SES が Kinesis Data Firehose に発行するイベントデータのコンテンツ - Amazon Simple Email Service
https://docs.aws.amazon.com/ja_jp/ses/latest/DeveloperGuide/event-publishing-retrieving-firehose-contents.html
この記事が気に入ったらサポートをしてみませんか？