Adobe Campaignクエリー道場(4)〜HAVING句でブラボー！ #SQL

前回の最後に、以下のような宿題をお出ししました:

初めてCampaignからのメールを受け取ったのが特定の日付(たとえば2022年4月1日)以降であるアドレスと、その初回受信日時とを抽出してください。

じつはこれ、実際のお客様からいただいたご質問の一部¹。現実のビジネス要件をAdobe Campaignのクエリーが解決した好例としてぜひご紹介したかったケースです。

みなさんお考えいただけたでしょうか？　本シリーズをずっとお読みだったら余裕だったかもしれませんね。でも、できなかったとしても大丈夫。いっしょに以下、見ていきましょう。

まずいつものように「クエリ」アクティビティの作成。これまでと同様、アドレスに対するメール履歴について関心があるのでディメンジョンは「受信者配信ログ」です。
フィルター条件を設定するのですが、ここに特定日付を指定しようとしちゃった方、いませんか？

はいアウト！　要件が「特定日付以降に受け取ったメールのうちの最初」であればこれでいいんですが、題意は「最初に受け取ったメールが特定日付以降」です。たとえばあなたが3月30日に初メールを、4月3日に次メールを受け取っていたとします。上掲図条件だとあなたが受けた次メールがマッチしてしまいますが、3月に初メールを受け取っているのでほんとはマッチさせたくないんですよね。というわけで日付を指定するのはここじゃありません。フィルター条件としては前回と同じく、ステータスが送信済みであるとだけしておきましょう:

ではどこで日付を指定するのか。ここで前々回を思い出してください。どうしたかというと──画面下部の「データグループ化句を追加」をチェック:

「グループ化の条件」で「追加」をクリック。「式」の上をクリックし、「Min(＠eventDate)」と手入力します²:

「初回受信日付」がほしいので、集計関数「Min()」により日付の最小値を取ろうというわけです。
続いて「演算子」で「次より大きいか等しい」を選択³:

「値」に条件の特定日付を与えます。ここでは2022年4月1日とするので文字列で「'2022-04-01'」と入力しましょう⁴:

えっ文字列でいいの？　そう思ったあなたは鋭い！　その感覚、大事にしてください。でもこの場合、Campaignがよしなに判断してくれるので、「'2022-04-01'」とか「'2022/04/01'」とかでＯＫなのです⁵。ただ１点、単一引用符「'」で囲むことを注意してくださいね。
以上により「グループ化の条件」が指定できたら「完了」をクリックして「クエリ」画面に戻ります。そうしたら例によって、「データを追加...」をクリック:

「追加データ」画面まで遷移して「使用可能フィールド」で「アドレス」をダブルクリックし「出力列」に追加。「グループ」のチェックボックスをチェックして「はい」にします:

「イベントの日付」もダブルクリックして「出力列」に追加したら、「式」カラムをクリックして「Min(＠eventDate)」と手編集します:

あれ、「Min(＠eventDate)」ってさっきもやったじゃん──はい、そうですね。でも、やるんだよ！　理由はあとでわかります。
いったん「完了」で戻り「追加データを編集...」をクリック:

やはり例によって「詳細設定パラメーター...」をクリック:

いつものように「重複行を削除(DISTINCT)」をアンチェック、「ターゲティングディメンジョンのプライマリキーの自動追加を無効にする」をチェック:

「OK」を続けてダイアログを閉じていきワークフローキャンバスに戻って完成！今回も一時的に「2つの実行間の中間母集団の結果を保持」を有効化すると:

初めてCampaignからのメールを受け取ったのが2022年4月1日以降であるアドレスとその初回受信日時とを抽出できました！

ここで毎度の、生SQLを見てみましょう:

SELECT   B0.sAddress, Min(B0.tsEvent) FROM NmsBroadLogRcp B0 WHERE (B0.iStatus = 1) AND ((B0.iBroadLogId > 0 OR B0.iBroadLogId < 0)) GROUP BY B0.sAddress HAVING (Min(B0.tsEvent) >= N'2022-04-01')

何をやってるかというと:

アドレスごとにデータを束ね(GROUP BY B0.sAddress)
最小の日付を取り(SELECT B0.sAddress, Min(B0.tsEvent))
それが特定日付以降のものを選ぶ(HAVING (Min(B0.tsEvent) >= N'2022-04-01'))

１番の「GROUP BY B0.sAddress」は「追加データ」画面で「アドレス」の「グループ」をチェックしたグループ化の効果です。２番のSELECT句の「Min(B0.tsEvent)」は「追加列」画面で手編集した「Min(＠eventDate)」。３番の「HAVING (Min(B0.tsEvent) >= N'2022-04-01')」は、フィルター条件指定画面で「データグループ化句を追加」して指定したものです。手順中「Min(＠eventDate)」を２回も指定して変じゃね？　と思われたかもしれませんが、SQL上SELECT句とHAVING句との両方に「Min(B0.tsEvent)」が必要だったからでした。

SQLの組み立て順とクエリー作成ステップの流れとが合っていなかったり、「HAVING」という言葉がインターフェイス上あらわれていなかったりと、直観的にはいかないところがなかなか曲者でしたね。ぜひクエリー手順とSQLとを対照させて熟読ガン見してください。

いかがでしょうか。「データグループ化句を追加」や集計関数など、本シリーズですでにご紹介済みのテクニックの総まとめで与えられたビジネス要件を解決できることがおわかりになったかと思います。

自力で正解だった方、ＧＪ！　残念だった方、Ｗ杯日本チームと同様、健闘を讃えます。挑戦が明日の糧になる──次回の栄光に向けて、Campaignクエラーへの道をさらに共に歩んでいきましょう!!

本稿の内容は筆者のオンプレミス型デモ環境(Adobe Campaign Classic 9342@6583a8a・PostgreSQL 11.14)上で実施した検証に基づきます。別環境における同様の動作を保証するものではありません。またデータは架空のものであり、既存の配信や実在の組織とはいっさい関係がありません。

もちろん特定できないよう、具体的なデータや条件は改変しています。 ↩
前々回のように「式を編集」ボタンをクリックして画面画から「最小値」集計関数を選んでもＯＫです。 ↩
「式」が日付型を返すとCampaignがいったん認識すれば、「演算子」の選択肢表示が「以降」に変化します。表示は異なりますが意味は同じです。 ↩
注３の状況では「値」カラムにカレンダーコントロールが表示され、最初から日付型として指定可能になります。 ↩
本稿のデータソースPostgreSQL環境では、「'20220401'」「'April 1, 2022'」「'01-Apr-22'」などでもＯＫでした。とはいえお勧めは「'2022-04-01'」です。 ↩