押忍！ Adobe Campaignクエリー道場である!!
ヘルプを見ただけではわからないクエリーの実践ワザを伝授するぞ！
びしびししごくからそのつもりでおれ！ 押忍！ 声が小さい!! 押忍!!!
...小芝居はこのへんにして本題に入りましょう。
Adobe Campaignのクエリーで、一意なデータを数えたいことってありがちですよね。たとえばCampaignの活用指標としてアクティブプロファイル数というものがあります1。厳密にはいろいろ計算しなくちゃですが、ざっくり目安としては、特定期間(1年)にCampaignから配信したユニークな宛先アドレス2の総数と考えてください。これを「クエリ」アクティビティで取得したい。どうしたらいいでしょうか。
ユニーク値を列挙
まずクエリーの対象となるスキーマを選びます。ここではＥメール配信に限定して考えましょう。すると配信受信者の履歴は製品標準では「受信者配信ログ」(nms:broadLogRcp)ですので、アクティビティのターゲティングディメンジョンとして受信者配信ログを指定してください。フィルタリングディメンジョンも自動的に同スキーマになります:
上掲画面で「フィルター条件」をダブルクリックするか「次へ」でフィルター指定画面に遷移します。フィルター条件として受信者配信ログの「イベントの日付」に対し期間を指定してください:
ここまではむずかしいところはありませんね。本番はここから。いったん「完了」をクリックし戻った「クエリ」画面で、「データを追加...」をクリックします:
「フィルタリングディメンジョンにリンクされたデータ」をチェックし「次へ」:
「フィルタリングディメンジョンのデータ」をチェックし「次へ」:
「使用可能フィールド」の「アドレス」をダブルクリックすると「出力列」へ追加されます。ここでいったん「完了」:
また戻った「クエリ」画面に「[アドレス]」が追加されてますね。ここで「追加データを編集...」をクリックします:
遷移した「追加列」画面で「詳細設定パラメーター...」をクリックします:
「詳細設定パラメーター」画面。本稿でお伝えしたかったポイントはここです。「ターゲティングディメンジョンのプライマリキーの自動追加を無効にする」をチェックしてください。そして「重複行を削除(DISTINCT)」がデフォルトですでにチェックされていることを確認したら「OK」:
「追加列」画面で「OK」、戻った「クエリ」画面でまた「OK」でクエリーが完成です。実行してみましょう:
画面の例ではユニークなアドレスが108,984件あったことがわかりました。
このときサーバー側では、以下のようなSQLが発行されています:
SELECT DISTINCT B0.sAddress FROM NmsBroadLogRcp B0 WHERE (B0.tsEvent >= TIMESTAMP WITH TIME ZONE '2021-10-01 00:00:00.000+09') AND ((B0.iBroadLogId > 0 OR B0.iBroadLogId < 0))
この「DISTINCT」が「ユニーク」なアドレスを取得するための必須要素。「詳細設定パラメーター」画面の「重複行を削除(DISTINCT)」がこれでした。
また同画面で「ターゲティングディメンジョンのプライマリキーの自動追加を無効にする」をチェックしましたね。もしそれがないと、上SQL文はこんな感じになります:
SELECT DISTINCT B0.sAddress, B0.iBroadLogId FROM NmsBroadLogRcp B0 WHERE (B0.tsEvent >= TIMESTAMP WITH TIME ZONE '2021-10-01 00:00:00.000+09') AND ((B0.iBroadLogId > 0 OR B0.iBroadLogId < 0))
select句に「B0.iBroadLogId」というのが入っちゃってますね。「ターゲティングディメンジョンのプライマリキーの自動追加」が意味するところです。これだと抽出した受信者配信ログの全行がプライマリーキーで区別され、DISTNCTによるアドレス重複排除が打ち消されてしまいます。それを避けるため自動追加無効を選択することが、今回のポイントなのでした。
ユニーク件数を取得
ここまででユニークなアドレス数はとりあえず得られたのですが、出力件数を見るというのは応用が効かないやり方です。というのもその数は、アクティビティ遷移間の中間テーブル行数に過ぎないから。どこにも保存されておらず揮発してしまうものなので、厳密には「結果」とはいえません。後続アクティビティに読み込んでメールしたりなど、データとして活用できませんしね。
ではどうするか。さきほどのアクティビティをダブルクリックし、「クエリ」画面でまた「追加データを編集...」をクリックしてください:
「追加列」画面、「出力列」の「アドレス」行で「式」をクリックし、次いで「式の編集」をクリックします3:
「式を選択」画面で「詳細選択」をクリック:
「数式のタイプ」で「集計関数のプロセス」「カウント」をそれぞれクリック、「ユニーク」をチェックします。そして「次へ」:
「アドレス」をダブルクリックします:
「式」の表示が「Countdistinct(＠address)」と変化したことを確認し、「詳細設定パラメーター...」をクリック:
「重複行を削除(DISTINCT)」はもう不要なのでチェックを外します4。「ターゲティングディメンジョンのプライマリキーの自動追加を無効にする」のチェックはそのままに「OK」:
「出力列」表示が「アドレスのユニーク数」と変化していることを確認して「OK」:
戻った「クエリ」画面で「OK」して完成です。これを実行すると、画面例では108,984という値を後続アクティビティに渡せるかたちで出力してくれます。
このとき発行されるSQLはこんな感じ:
SELECT Count(Distinct(B0.sAddress)) FROM NmsBroadLogRcp B0 WHERE (B0.tsEvent >= TIMESTAMP WITH TIME ZONE '2021-10-01 00:00:00.000+09') AND ((B0.iBroadLogId > 0 OR B0.iBroadLogId < 0))
「集計関数のプロセス」「カウント」「ユニーク」の指定で「Countdistinct(＠address)」という関数を呼び出した結果がこのSQLです。
「Countdistinct()」はCampaignが提供する「集計関数」のひとつ。集計関数はヘルプ記事「関数のリスト」の「集計」にまとめられています。
集計関数は使いこなせれば強力な武器になるものです。本連載でもどしどし紹介していきますので、お楽しみに。
ともあれ、以上でアドレスのユニークな件数をSQLで取得することができました。お疲れさま!
いかがでしたか。SQLを知っているひとはむしろイラッとするかもしれないCampaignのインターフェイスですが、なにげにいろいろ仕掛けが隠れています。慣れてくるとパズル的な楽しさも出てくるはず。ぜひこの機に、親しんでやってください。
以上、道場主、跡部官辺であった！ 次回もまたこの道場で、押忍!!
本稿の内容は筆者のオンプレミス型デモ環境(Adobe Campaign Classic 9342@6583a8a・PostgreSQL 11.14)上で実施した検証に基づきます。別環境における同様の動作を保証するものではありません。またデータは架空のものであり、既存の配信や実在の組織とはいっさい関係がありません。