Photo by
akisuke0925
【第330回】 Not Sent（未送信）レコードを検索する Marketing Cloud アプリ
以前、Not Sent（未送信） のレコードをデータ抽出して、データエクステンションに格納する方法をご紹介しました。
この記事の内容に対して、ブラジルの Fernando Prates（フェルナンド・プラテス）さん が LinkedIn で私の記事を引用し、Not Sent のデータエクステンションを CloudPages 上で検索できる仕組みを投稿しました。そのアイデアが非常に興味深かったため、今回あらためて皆さんにも紹介したいと思います。
この記事を書くにあたり、私自身も実装を見直し、より使いやすい形に改善を加えました。以下の 2 つのバージョンを用意しています。
「ログインなし」でアクセスできるバージョン
「ログインあり」で制限をかけたバージョン
「ログインなし」でアクセスできるバージョン
まず、以下の記事の手順に従って、Not Sent のデータエクステンションを作成してください。作成時は、データエクステンション名や項目名を正確に設定することが重要です。
データエクステンション名：NotSent
購読者キー項目名：SubscriberKey
メールアドレス項目名：EmailAddress
トリガー送信キー項目名：TriggeredSendExternalKey
これらを正しく設定した上で、以下のコードを CloudPages の HTML ブロックに貼り付け、公開すれば、誰でも Not Sent レコードを検索できるページが完成します。
<title>未送信レコード検索</title>
<h2>未送信レコードの検索</h2>
<form method="post">
<label for="subscriberKey">購読者キー：</label><br>
<input type="text" id="subscriberKey" name="subscriberKey"><br><br>
<label for="email">メールアドレス：</label><br>
<input type="text" id="email" name="email"><br><br>
<label for="triggeredSendExternalKey">トリガー送信キー：</label><br>
<input type="text" id="triggeredSendExternalKey" name="triggeredSendExternalKey"><br><br>
<button type="submit">検索</button>
</form><br>
※ 上記検索窓を使って、複合検索による絞り込みはできません。<br>
※ 複数入力された場合は、①「購読者キー」②「メールアドレス」③「トリガー送信キー」の順で優先されます。<br>
※ Reason が Account Level Opt Out のレコードは Unsubscribed Master のレコードと重複して表示されるため除外しています。<br>
※ Reason が Held のレコードは、バッチ ID 違いで重複して表示されるため「0」以外のものを除外しています。<br>
※ 送信日時を日本時間で取得するには、事前にデータ抽出時のタイムゾーンを「(GMT+09:00) Osaka, Sapporo, Tokyo」に変更してください。<br>
※ 検索結果は送信日時が新しいレコードから表示されます。<br><br>
・Held：バウンスを経て配信停止<br>
・Unsubscribed Master：購読解除済み<br>
・Send Failure：何らかの理由で送信失敗（詳細なし）<br>
・Build Email Error：メール生成時にエラー（詳細なし）<br>
・Invalid Email Address：メールアドレスが不正<br>
・Excluded by Send Time Filter：除外スクリプトで配信対象外<br>
・Domain Exclusion：ドメイン除外で配信対象外<br>
・Suppression List Exclusion：（自動）連絡禁止リストにより配信対象外<br>
・List Detective Exclusion：List Detective により配信対象外<br><br>
<hr>
<div id="resultado">
<script runat="server">
Platform.Load("Core", "1.1.1");
var email = Request.GetFormField("email");
var subscriberKey = Request.GetFormField("subscriberKey");
var triggeredSendExternalKey = Request.GetFormField("triggeredSendExternalKey");
var searchField = "";
var searchValue = "";
var rows = [];
var myDE = DataExtension.Init("NotSent");
if (subscriberKey) {
rows = myDE.Rows.Lookup("SubscriberKey", subscriberKey);
searchField = "購読者キー";
searchValue = subscriberKey;
} else if (email) {
rows = myDE.Rows.Lookup("EmailAddress", email);
searchField = "メールアドレス";
searchValue = email;
} else if (triggeredSendExternalKey) {
rows = myDE.Rows.Lookup("TriggeredSendExternalKey", triggeredSendExternalKey);
searchField = "トリガー送信キー";
searchValue = triggeredSendExternalKey;
}
if (rows && rows.length > 0) {
// "Account Level Opt Out" を除外して新しい配列に入れる
var filteredRows = [];
for (var i = 0; i < rows.length; i++) {
var row = rows[i];
var reason = row.Reason;
var batchId = row.BatchID;
if (reason == "Account Level Opt Out") {
continue; // 除外
}
if (reason == "Held" && batchId != "0") {
continue; // Held かつ BatchID ≠ 0 は除外
}
filteredRows.push(row); // 表示対象に追加
}
// 降順でソート（EventDateが新しい順）
filteredRows.sort(function(a, b) {
var dateA = new Date(a.EventDate);
var dateB = new Date(b.EventDate);
return dateB - dateA;
});
Write("<h3>" + filteredRows.length + " 件が見つかりました：</h3>");
Write("<p>検索条件：「" + searchField + "」= " + searchValue + "</p><br>");
for (var j = 0; j < filteredRows.length; j++) {
var reasonText = filteredRows[j].Reason;
// 日付を0時間加算して整形。タイムゾーンを CST で抽出している場合は、15 に変更してください。
var originalDate = new Date(filteredRows[j].EventDate);
originalDate.setHours(originalDate.getHours() + 0);
function pad(n) {
return n < 10 ? '0' + n : n;
}
var formattedDate =
originalDate.getFullYear() + '-' +
pad(originalDate.getMonth() + 1) + '-' +
pad(originalDate.getDate()) + ' ' +
pad(originalDate.getHours()) + ':' +
pad(originalDate.getMinutes()) + ':' +
pad(originalDate.getSeconds());
Write("<div>");
Write("<p><strong>送信 ID：</strong> " + filteredRows[j].SendID + "</p>");
Write("<p><strong>購読者キー：</strong> " + filteredRows[j].SubscriberKey + "</p>");
Write("<p><strong>メールアドレス：</strong> " + filteredRows[j].EmailAddress + "</p>");
Write("<p><strong>トリガー送信キー：</strong> " + filteredRows[j].TriggeredSendExternalKey + "</p>");
Write("<p><strong>送信日時（日本時間）：</strong> " + formattedDate + "</p>");
Write("<p><strong>理由：</strong> " + reasonText + "</p>");
Write("</div><hr />");
}
} else if (email || subscriberKey || triggeredSendExternalKey) {
Write("<p>該当するデータは見つかりませんでした。</p>");
Write("<p>検索条件：「" + searchField + "」= " + searchValue + "</p>");
}
</script>
</div>
「ログインあり」で制限をかけたバージョン
上記の CloudPages では誰でもアクセスできてしまうため、情報漏洩や不正利用のリスクがあります。そこで、以前ご紹介した「不正アクセスから保護する技術」の活用が重要になります。こちらは Mateusz Dąbrowski（マテウシュ・ドンブロフスキ）さん が紹介しているもので、CloudPages にログイン認証の仕組みを組み込むことができます。
すべてを一から説明するのは長くなってしまいますので、詳細は以下の記事をご参考ください。
ご利用に当たっては、認証ログを保存するためのデータエクステンション「AUTHENTICATION_DATA_EXTENSION」と、エラー情報を記録するための「ERROR_DATA_EXTENSION」を作成しましょう。あわせて、Web アプリで認証を行うために必要な「クライアント ID」と「クライアントシークレット」も取得しておいてください。
ログイン認証付きのコードについては、以下にサンプルコードをご用意していますので、そのままご利用いただけます。
<title>未送信レコード検索</title>
<h2>未送信レコードの検索</h2>
<form method="post">
<label for="subscriberKey">購読者キー：</label><br>
<input type="text" id="subscriberKey" name="subscriberKey"><br><br>
<label for="email">メールアドレス：</label><br>
<input type="text" id="email" name="email"><br><br>
<label for="triggeredSendExternalKey">トリガー送信キー：</label><br>
<input type="text" id="triggeredSendExternalKey" name="triggeredSendExternalKey"><br><br>
<button type="submit">検索</button>
</form><br>
※ 上記検索窓を使って、複合検索による絞り込みはできません。<br>
※ 複数入力された場合は、①「購読者キー」②「メールアドレス」③「トリガー送信キー」の順で優先されます。<br>
※ Reason が Account Level Opt Out のレコードは Unsubscribed Master のレコードと重複して表示されるため除外しています。<br>
※ Reason が Held のレコードは、バッチ ID 違いで重複して表示されるため「0」以外のものを除外しています。<br>
※ 送信日時を日本時間で取得するには、事前にデータ抽出時のタイムゾーンを「(GMT+09:00) Osaka, Sapporo, Tokyo」に変更してください。<br>
※ 検索結果は送信日時が新しいレコードから表示されます。<br><br>
・Held：バウンスを経て配信停止<br>
・Unsubscribed Master：購読解除済み<br>
・Send Failure：何らかの理由で送信失敗（詳細なし）<br>
・Build Email Error：メール生成時にエラー（詳細なし）<br>
・Invalid Email Address：メールアドレスが不正<br>
・Excluded by Send Time Filter：除外スクリプトで配信対象外<br>
・Domain Exclusion：ドメイン除外で配信対象外<br>
・Suppression List Exclusion：（自動）連絡禁止リストにより配信対象外<br>
・List Detective Exclusion：List Detective により配信対象外<br><br>
<hr>
<div id="resultado">
<script runat="server">
Platform.Load("Core", "1.1.1");
var email = Request.GetFormField("email");
var subscriberKey = Request.GetFormField("subscriberKey");
var triggeredSendExternalKey = Request.GetFormField("triggeredSendExternalKey");
if (!email && !subscriberKey && !triggeredSendExternalKey) {
// Initialization
var debugging = true;
var appName = '*********************'; // アプリの名前（監査DEに保存されます）
var appURL = '*********************'; // Cloudpages URL
var clientID = '*********************'; // WebApp用に作成したクライアントID
var clientSecret = '*********************'; // WebApp用に作成したクライアントシークレット
var clientBase = '*********************'; // アカウントのクライアントベース
var authDE = 'AUTHENTICATION_DATA_EXTENSION';
var errorDE = 'ERROR_DATA_EXTENSION';
var errorURL = 'https://note.com/nobuyukiwatanabe';
// Handle query parameters
var state = Platform.Request.GetQueryStringParameter('state');
var errorMessage = Platform.Request.GetQueryStringParameter('error');
var errorDescription = Platform.Request.GetQueryStringParameter('error_description');
function debugValue(description, value) {
// Uncomment for debugging
// Write(description + ': ' + (typeof value == 'object' ? Stringify(value) : value) + '<br><br>');
}
function handleError(error) {
if (debugging) {
debugValue('Found error', error);
} else {
Platform.Function.InsertData(
errorDE,
['id', 'appName', 'errorMessage', 'errorDescription'],
[GUID(), appName, error.message, error.description]
);
Platform.Response.Redirect(errorURL + '?error=' + error.message + '&error_description=' + error.description);
}
}
if (!state && !errorMessage) {
// Initial authorization redirect
state = GUID();
Platform.Response.Redirect(
'https://' + clientBase + '.auth.marketingcloudapis.com/v2/authorize' +
'?response_type=code&client_id=' + clientID +
'&redirect_uri=' + appURL +
'&state=' + state
);
} else if (state) {
// Handle OAuth response
var code = Platform.Request.GetQueryStringParameter('code');
var payload = {
grant_type: 'authorization_code',
code: code,
client_id: clientID,
client_secret: clientSecret,
redirect_uri: appURL
};
var response = HTTP.Post(
'https://' + clientBase + '.auth.marketingcloudapis.com/v2/token',
'application/json',
Stringify(payload)
);
if (response.StatusCode == 200) {
var parsedResponse = Platform.Function.ParseJSON(response.Response[0]);
var accessToken = parsedResponse.access_token;
var tokenExpire = Platform.Function.SystemDateToLocalDate(Platform.Function.Now());
tokenExpire.setMinutes(tokenExpire.getMinutes() + 18);
response = HTTP.Get(
'https://' + clientBase + '.auth.marketingcloudapis.com/v2/userinfo',
['Authorization'],
['Bearer ' + accessToken]
);
if (debugging) {
debugValue('UserInfo Response', response);
}
var userInfo = Platform.Function.ParseJSON(response.Content).user;
var userName = userInfo.name;
var userEmail = userInfo.email;
Platform.Function.UpsertData(
authDE,
['session'], [state],
['appName', 'token', 'tokenExpire', 'userName', 'userEmail'],
[appName, accessToken, tokenExpire, userName, userEmail]
);
} else {
handleError({
message: 'Authentication Failed',
description: 'Status: ' + response.StatusCode
});
}
} else {
// If error parameters exist
handleError({
message: errorMessage,
description: errorDescription
});
}
} else {
var searchField = "";
var searchValue = "";
var rows = [];
var myDE = DataExtension.Init("NotSent");
if (subscriberKey) {
rows = myDE.Rows.Lookup("SubscriberKey", subscriberKey);
searchField = "購読者キー";
searchValue = subscriberKey;
} else if (email) {
rows = myDE.Rows.Lookup("EmailAddress", email);
searchField = "メールアドレス";
searchValue = email;
} else if (triggeredSendExternalKey) {
rows = myDE.Rows.Lookup("TriggeredSendExternalKey", triggeredSendExternalKey);
searchField = "トリガー送信キー";
searchValue = triggeredSendExternalKey;
}
if (rows && rows.length > 0) {
var filteredRows = [];
for (var i = 0; i < rows.length; i++) {
var row = rows[i];
var reason = row.Reason;
var batchId = row.BatchID;
if (reason == "Account Level Opt Out") {
continue;
}
if (reason == "Held" && batchId != "0") {
continue;
}
filteredRows.push(row);
}
filteredRows.sort(function(a, b) {
var dateA = new Date(a.EventDate);
var dateB = new Date(b.EventDate);
return dateB - dateA;
});
Write("<h3>" + filteredRows.length + " 件が見つかりました：</h3>");
Write("<p>検索条件：「" + searchField + "」= " + searchValue + "</p><br>");
for (var j = 0; j < filteredRows.length; j++) {
var reasonText = filteredRows[j].Reason;
var originalDate = new Date(filteredRows[j].EventDate);
originalDate.setHours(originalDate.getHours() + 0);
function pad(n) {
return n < 10 ? '0' + n : n;
}
var formattedDate =
originalDate.getFullYear() + '-' +
pad(originalDate.getMonth() + 1) + '-' +
pad(originalDate.getDate()) + ' ' +
pad(originalDate.getHours()) + ':' +
pad(originalDate.getMinutes()) + ':' +
pad(originalDate.getSeconds());
Write("<div>");
Write("<p><strong>送信 ID：</strong> " + filteredRows[j].SendID + "</p>");
Write("<p><strong>購読者キー：</strong> " + filteredRows[j].SubscriberKey + "</p>");
Write("<p><strong>メールアドレス：</strong> " + filteredRows[j].EmailAddress + "</p>");
Write("<p><strong>トリガー送信キー：</strong> " + filteredRows[j].TriggeredSendExternalKey + "</p>");
Write("<p><strong>送信日時（日本時間）：</strong> " + formattedDate + "</p>");
Write("<p><strong>理由：</strong> " + reasonText + "</p>");
Write("</div><hr />");
}
} else if (email || subscriberKey || triggeredSendExternalKey) {
Write("<p>該当するデータは見つかりませんでした。</p>");
Write("<p>検索条件：「" + searchField + "」= " + searchValue + "</p>");
}
}
</script>
</div>このコードの中で、修正の検討が必要なものは以下の 5 つです。
・アプリの名前
・Cloudpages URL
・WebApp 用に作成したクライアント ID
・WebApp 用に作成したクライアントシークレット
・クライアントベース
※クライアントベースとは、以下の ************************ の箇所です。
https://************************.auth.marketingcloudapis.com/v2/token
表示される未送信検索数とメール分析の「未送信」数の関係
ちなみに今回の条件を使って「トリガー送信キー」で検索したときの検索数は、Journey Builder メール分析における「未送信」の数と、基本的には一致するはずです。
今回の条件とは以下の 2 つです。
Reason が Account Level Opt Out のレコードは Unsubscribed Master のレコードと重複して表示されるため除外しています。
※ Reason が Held のレコードは、バッチ ID 違いで重複して表示されるため「0」以外のものを除外しています。
いかがでしたでしょうか。
今回紹介した内容は、未送信時のトラブルシューティングを容易にするという点で、すべての Marketing Cloud Engagement アカウントに標準装備したくなるような有用なテクニックだったのではないでしょうか。
ちなみにこの方法を応用すれば、Not Sent DE に限らず、様々なデータエクステンションから検索することができると思います。ぜひ、ご自身のユースケースに合わせてカスタマイズしながら試してみてください。
今回は以上です。