Cara Analisis AWS SES Logs
Nasib baik ada AWS Athena
SES log adalah log event yang merakam email event (AWS SES). Jadi setiap interaksi antara pengguna email dengan email dapat dirakam oleh log ini. Berikut merupakan events yang dirakam oleh logs ini:
Send – Pemintaan untuk menghantar email diterima dan AWS SES akan menghantar email tersebut ke email server penerima.
Rendering Failure – Email tidak dihantar kerana ada template rendering issue.
Reject – Email tersebut tidak dihantar ke mail server mungkin disebab oleh virus (contoh).
Delivery – Amazon SES berjaya menghantar email tersebut ke email server penerima.
Hard bounce – Mail server penerima menolak email yang dihantar AWS SES
Complaint – Email tersebut berjaya sampai ke email server penerima, tetapi penerima report email tersebut sebagai SPAM (OMG)!
Delivery Delay – Tidak tidak dapat dihantar buat sementara waktu atasa sebab yang tidak kekal. Contohnya email inbox user penuh.
Subscription – Email berjaya ke email server penerima tetapi penerima email itu tekan unsubscribe (kasihannya).
Open – Penerima emaila buka email dekat email client (e.g. gmail apps)
Click – Penerima click link dalam email tersebut.
Untuk maklumat lanjut tentang event ini anda boleh dapatkannya di sini: SES Events
Saya menggunakan AWS Kinesis Firehose untuk menghantar logs SES ini ke S3 bucket. Log yang dihantar menggunakan Kinesis Firehose ini akan menggunakan format logs seperti di ini AWS Kinesis Firehose Logs Content
Jadi data sudah ada dalam S3, jadi bagaimana hendak buat analysis? Takkan perlu create python script dan baca data json dalam S3 ini? (Sebenarnya boleh pun, tetapi saya orang agak malas).
AWS Athena datang menyelamat!!!
Bagaimana hendak pastikan Athena tahu S3 bucket yang mana satu perlu di access? Untuk ini anda perlu memetakan lokasi S3 ke table dalam Athen. Sebelum itu anda perlu memahami SES logs dahulu sebelum membuat pemetaan ini. Contoh di bawah ini dalah SES logs untuk 3 events yang berbeza
Open
{
"eventType": "Open",
"mail": {
"commonHeaders": {
"from": [
"sender@example.com"
],
"messageId": "EXAMPLE7c191be45-e9aedb9a-02f9-4d12-a87d-dd0099a07f8a-000000",
"subject": "Message sent from Amazon SES",
"to": [
"recipient@example.com"
]
},
"destination": [
"recipient@example.com"
],
"headers": [
{
"name": "X-SES-CONFIGURATION-SET",
"value": "ConfigSet"
},
{
"name":"X-SES-MESSAGE-TAGS",
"value":"myCustomTag1=myCustomValue1, myCustomTag2=myCustomValue2"
},
{
"name": "From",
"value": "sender@example.com"
},
{
"name": "To",
"value": "recipient@example.com"
},
{
"name": "Subject",
"value": "Message sent from Amazon SES"
},
{
"name": "MIME-Version",
"value": "1.0"
},
{
"name": "Content-Type",
"value": "multipart/alternative; boundary=\"XBoundary\""
}
],
"headersTruncated": false,
"messageId": "EXAMPLE7c191be45-e9aedb9a-02f9-4d12-a87d-dd0099a07f8a-000000",
"sendingAccountId": "123456789012",
"source": "sender@example.com",
"tags": {
"myCustomTag1":[
"myCustomValue1"
],
"myCustomTag2":[
"myCustomValue2"
],
"ses:caller-identity": [
"IAM_user_or_role_name"
],
"ses:configuration-set": [
"ConfigSet"
],
"ses:from-domain": [
"example.com"
],
"ses:source-ip": [
"192.0.2.0"
]
},
"timestamp": "2017-08-09T21:59:49.927Z"
},
"open": {
"ipAddress": "192.0.2.1",
"timestamp": "2017-08-09T22:00:19.652Z",
"userAgent": "Mozilla/5.0 (iPhone; CPU iPhone OS 10_3_3 like Mac OS X) AppleWebKit/603.3.8 (KHTML, like Gecko) Mobile/14G60"
}
}Click
{
"eventType": "Click",
"click": {
"ipAddress": "192.0.2.1",
"link": "http://docs.aws.amazon.com/ses/latest/DeveloperGuide/send-email-smtp.html",
"linkTags": {
"samplekey0": [
"samplevalue0"
],
"samplekey1": [
"samplevalue1"
]
},
"timestamp": "2017-08-09T23:51:25.570Z",
"userAgent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/60.0.3112.90 Safari/537.36"
},
"mail": {
"commonHeaders": {
"from": [
"sender@example.com"
],
"messageId": "EXAMPLE7c191be45-e9aedb9a-02f9-4d12-a87d-dd0099a07f8a-000000",
"subject": "Message sent from Amazon SES",
"to": [
"recipient@example.com"
]
},
"destination": [
"recipient@example.com"
],
"headers": [
{
"name": "X-SES-CONFIGURATION-SET",
"value": "ConfigSet"
},
{
"name":"X-SES-MESSAGE-TAGS",
"value":"myCustomTag1=myCustomValue1, myCustomTag2=myCustomValue2"
},
{
"name": "From",
"value": "sender@example.com"
},
{
"name": "To",
"value": "recipient@example.com"
},
{
"name": "Subject",
"value": "Message sent from Amazon SES"
},
{
"name": "MIME-Version",
"value": "1.0"
},
{
"name": "Content-Type",
"value": "multipart/alternative; boundary=\"XBoundary\""
},
{
"name": "Message-ID",
"value": "EXAMPLE7c191be45-e9aedb9a-02f9-4d12-a87d-dd0099a07f8a-000000"
}
],
"headersTruncated": false,
"messageId": "EXAMPLE7c191be45-e9aedb9a-02f9-4d12-a87d-dd0099a07f8a-000000",
"sendingAccountId": "123456789012",
"source": "sender@example.com",
"tags": {
"myCustomTag1":[
"myCustomValue1"
],
"myCustomTag2":[
"myCustomValue2"
],
"ses:caller-identity": [
"ses_user"
],
"ses:configuration-set": [
"ConfigSet"
],
"ses:from-domain": [
"example.com"
],
"ses:source-ip": [
"192.0.2.0"
]
},
"timestamp": "2017-08-09T23:50:05.795Z"
}
}
Delivery
{
"eventType": "Delivery",
"mail": {
"timestamp": "2016-10-19T23:20:52.240Z",
"source": "sender@example.com",
"sourceArn": "arn:aws:ses:us-east-1:123456789012:identity/sender@example.com",
"sendingAccountId": "123456789012",
"messageId": "EXAMPLE7c191be45-e9aedb9a-02f9-4d12-a87d-dd0099a07f8a-000000",
"destination": [
"recipient@example.com"
],
"headersTruncated": false,
"headers": [
{
"name": "From",
"value": "sender@example.com"
},
{
"name": "To",
"value": "recipient@example.com"
},
{
"name": "Subject",
"value": "Message sent from Amazon SES"
},
{
"name": "MIME-Version",
"value": "1.0"
},
{
"name": "Content-Type",
"value": "text/html; charset=UTF-8"
},
{
"name": "Content-Transfer-Encoding",
"value": "7bit"
}
],
"commonHeaders": {
"from": [
"sender@example.com"
],
"to": [
"recipient@example.com"
],
"messageId": "EXAMPLE7c191be45-e9aedb9a-02f9-4d12-a87d-dd0099a07f8a-000000",
"subject": "Message sent from Amazon SES"
},
"tags": {
"ses:configuration-set": [
"ConfigSet"
],
"ses:source-ip": [
"192.0.2.0"
],
"ses:from-domain": [
"example.com"
],
"ses:caller-identity": [
"ses_user"
],
"ses:outgoing-ip": [
"192.0.2.0"
],
"myCustomTag1": [
"myCustomTagValue1"
],
"myCustomTag2": [
"myCustomTagValue2"
]
}
},
"delivery": {
"timestamp": "2016-10-19T23:21:04.133Z",
"processingTimeMillis": 11893,
"recipients": [
"recipient@example.com"
],
"smtpResponse": "250 2.6.0 Message received",
"reportingMTA": "mta.example.com"
}
}Daripada 3 contoh-contoh ini saya dapati ada 3 komponen utama:
EventType - Tetap
Mail - Tetap
Event (delivery, open, click)- Tidak Tepat bergantung kepada jenis EventType
Anda boleh menggunakan cara seperti yang disarankan oleh AWS. Namun saya mengambil jalan lain (saya rasa lebih mudah) dengan terus memetakan 3 komponen tersebut sebagai string.
Ini adalah script yang saya gunakan untuk CREATE TABLE
CREATE EXTERNAL TABLE `tab_ses_logs`(
`eventtype` string COMMENT 'from deserializer',
`mail` string COMMENT 'from deserializer',
`complaint` string COMMENT 'from deserializer',
`bounce` string COMMENT 'from deserializer',
`send` string COMMENT 'from deserializer',
`delivery` string COMMENT 'from deserializer',
`open` string COMMENT 'from deserializer',
`reject` string COMMENT 'from deserializer',
`click` string COMMENT 'from deserializer',
`deliverydelay` string COMMENT 'from deserializer',
`subscription` string COMMENT 'from deserializer')
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
LOCATION 's3://sample-ses-logs/'Baik. Kita sudah petakan S3 (s3://sample-ses-logs/) kepada Athena table (tab_ses_logs). Disebabkan kita petakan setiap field dalam json log tadi sebagai string, kita perlu gunakan json_extract_scalar untuk extract/memerah maklumat daripada column tersebut.
SELECT eventType,
CASE lower(eventType)
WHEN 'complaint' then json_extract_scalar(complaint, '$.timestamp')
WHEN 'bounce' then json_extract_scalar(bounce, '$.timestamp')
WHEN 'delivery' then json_extract_scalar(delivery, '$.timestamp')
WHEN 'open' then json_extract_scalar(open, '$.timestamp')
WHEN 'click' then json_extract_scalar(click, '$.timestamp')
WHEN 'deliverydelay' then json_extract_scalar(deliverydelay, '$.timestamp')
WHEN 'subscription' then json_extract_scalar(subscription, '$.timestamp')
ELSE json_extract_scalar(mail, '$.timestamp')
END AS event_timestamp,
json_extract_scalar(mail, '$.destination[0]') mail_destination,
json_extract_scalar(mail, '$.timestamp') mail_timestamp,
-----
json_extract_scalar(complaint, '$.timestamp') complaint_timestamp,
json_extract_scalar(bounce, '$.timestamp') bounce_timestamp,
json_extract_scalar(send, '$.timestamp') send_timestamp, ----no timestamp
json_extract_scalar(delivery, '$.timestamp') delivery_timestamp,
json_extract_scalar(open, '$.timestamp') open_timestamp,
json_extract_scalar(reject, '$.timestamp') reject_timestamp, ----no timestamp
json_extract_scalar(click, '$.timestamp') click_timestamp,
json_extract_scalar(deliverydelay, '$.timestamp') deliverydelay_timestamp,
json_extract_scalar(subscription, '$.timestamp') subscription_timestamp
-----
FROM tab_ses_logsSQL di atas ini akan menghasilkan senarai eventype dan timestamp event tersebut. Ia juga akan menunjukkan email penerima.
Itu sahaja. Jika ada maklum balas boleh tinggalkan komen ya.
Rujukkan:
https://aws.amazon.com/blogs/messaging-and-targeting/analyzing-amazon-ses-event-data-with-aws-analytics-services/
https://docs.aws.amazon.com/ses/latest/dg/event-publishing-retrieving-sns-contents.html