Adobe Campaignクエラーのみなさん、今日も元気にクエってますか。
さっそく今日のお題です。Campaignの受信者には、これまでに何度も配信を送っていることでしょう。では、そのアドレスごとに、いちばん最近で送ったのがいつか。それを調べたい！
なるほど。施策ごとに条件を考えてターゲットを選んでるはずですが、結果として最近あんまりリーチできてないな、なんて受信者もいるかもしれませんね。Campaignマーケターとしては気になるところかと思います。
クエり甲斐あるお題です。やってみましょう！
まずクエリー対象ディメンジョン。配信の実績を見たいのですから、受信者配信ログを選びます:
フィルター条件。配信が成功しているものに関心があるので、ステータスが送信済みであるものを抽出しましょう:
「クエリ」画面に戻ったら「補足情報」の「データを追加...」をクリック:
「フィルタリングディメンジョンにリンクされたデータ」>「フィルタリングディメンジョンのデータ」:
「追加するデータ」画面の「使用可能フィールド」で「アドレス」をダブルクリックして「出力列」に追加します:
ここまでは前々回とおおよそ同じなので、迷われた方は併せてご参照くださいね。で、ポイントはここから。「使用可能フィールド」の「イベントの日付」をダブルクリックして「出力列」に追加します:
配信がいつかを問うているので、受信者配信ログの「イベントの日付」を見ようというわけです。しかし単なる配信時刻ではなく、アドレスに対し「いちばん最近」配信したときを知りたいのでした。
そこで「イベントの日付」行の「式」をクリックしましょう。すると入力フィールドになり「＠eventDate」と表示されます:
そこに「Max(＠eventDate)」と手入力してください:
Enterすると「最大 イベントの日付」と表示が変化します:
ここで入力した「Max()」は前々回・前回と続けて紹介している「集計関数」のひとつ。最大値を得る関数です。「いちばん最近」の日付を知りたいので、「イベントの日付」の最大値を取ったのでした。
でも最大というからには、何のなかで最大なのかが問題ですよね。この場合、あるアドレスへ送られた配信のなかで日付が最大値のものが知りたい。つまりアドレスごとに「集計」したいのです。
というわけで、「出力列」の「アドレス」行にある「グループ」のチェックボックスをチェックして「はい」にします:
これが前回も使用したグループ化。アドレスごとにデータを束ね、そのなかでの最大値つまり「いちばん最近」の日付を得るというわけです。
あとは忘れずに最後の仕上げをします。「完了」で「クエリ」画面に戻り、「追加データを編集...」をクリック:
「詳細設定パラメーター...」をクリック:
「重複行を削除(DISTINCT)」をアンチェック、「ターゲティングディメンジョンのプライマリキーの自動追加を無効にする」をチェック。このへんは前々回・前回といっしょです:
「OK」を続けてダイアログを閉じていきワークフローキャンバスに戻って完成！
今回も一時的1にワークフロープロパティで「2つの実行間の中間母集団の結果を保持」にして結果を見てみます。「クエリ」からの遷移矢印を右クリック→「ターゲットを表示...」を選択して「最大 イベントの日付」を繰り返しクリック:
「▲」が表示されると昇順ソートになります:
...という感じで、アドレスごとに「いちばん最近」配信された日付を古い方から見ることができました。施策の向上に使えそうですね！
今回もいちおう、バックエンドで実行される生SQLを見てみましょう:
SELECT Max(B0.tsEvent), B0.sAddress FROM NmsBroadLogRcp B0 WHERE (B0.iStatus = 1) AND ((B0.iBroadLogId > 0 OR B0.iBroadLogId < 0)) GROUP BY B0.sAddress
細かいところはいいとして、注目なのは「Max(B0.tsEvent)」「GROUP BY B0.sAddress」です。前者は手入力した集計関数Maxですね。後者は「グループ」のチェックボックスをチェックしたことによるグループ化。対応するSQLがこの「GROUP BY」です。このように集計関数は原則、グループ化とセットで考えてください2。
最大値・最小値を知りたいというニーズはほんとにしょっちゅう出てくるんじゃないでしょうか。今回の「いちばん最近」という要件も、まさに日付の最大値として得られるものでした。そんなときは本稿のように、集計関数＋グループ化で瞬殺してしまいましょう！
では──ここまでの知識を踏まえて、応用編にトライしてみませんか。
初めてCampaignからのメールを受け取ったのが特定の日付(たとえば2022年4月1日)以降であるアドレスと、その初回受信日時とを抽出してください。
このシリーズ「Adobe Campaignクエリー道場」をお読みになっていれば余裕のクエリーだと思います。宿題にしますので、次回までに考えておいてくださいね。
宿題やったか！ 歯みがけよ！ 風邪ひくなよ！ また来週〜！3
本稿の内容は筆者のオンプレミス型デモ環境(Adobe Campaign Classic 9342@6583a8a・PostgreSQL 11.14)上で実施した検証に基づきます。別環境における同様の動作を保証するものではありません。またデータは架空のものであり、既存の配信や実在の組織とはいっさい関係がありません。