【第175回】 Marketing Cloud SQL 超入門（3）- IN（NOT IN）、IS NULL
これまでに Markeitng Cloud SQL の基本 と SELECT、FROM、WHERE 、そして、Dataview の使用 や 日付関数 について学習しました。
そこでは Marketing Cloud で SQL を使用する目的 について触れました。以下のような事例に沿って学習すると良いという話でしたね。
① データエクステンション内のデータを調査したい
② データビューを使用して、エンゲージ（関与）した顧客を知りたい
③ データビューを活用して、別の配信リストを作りたい
④ 2 つ以上のデータエクステンションを組み合わせて、別のデータエクステンションを作成したい
そして、これまでの記事では ①② まで学習しました。今回は ③「データビューを活用して、別の配信リストを作りたい」について学習します。
例えば、あるジャーニーのあるメールアクティビティで開封やクリックした人たちを集めて、配信リストに転化させたいということがありますよね。
このように、データビューから対象者を取得して、配信マスタと組み合わせて行くということが Marketing Cloud SQL の醍醐味になってきますので、この機会にしっかり学習しましょう。
■ サブクエリについて
まず、この「サブクエリ」という言葉自体は覚える必要はありません。サブクエリとは「一時的に使われる使い捨てのデータテーブル」のようなものと思ってください。実際にデータエクステンションは作らないけど、頭の中でデータエクステンションを作成するイメージです。
今回の手順は、まず、① データビューを使って、あるジャーニーのあるメールアクティビティをクリックした人の購読者キーを取得します。（ ← この部分がサブクエリに該当します。）そして、② 配信マスタの中から、① の購読者キーと一致した人を取得して、配信リストに転化させます。
それでは、まず、あるジャーニーのあるメールアクティビティをクリックした人の購読者キーを取得してみます。これについては、以下の記事を参照してください。
SELECT SubscriberKey
FROM _Click
WHERE TriggeredSendCustomerkey = '88597'
このデータビューから取得されるデータには、残念ながら、配信マスタに含まれるような「メールアドレス」「オプトイン/アウトの情報」「顧客の名前」などが含まれていません。
よって、配信リストとして必要な項目を揃えるには、そのようなデータを持っている「配信マスタ」と組み合わせる必要があるわけです。
そこで使用されるのが、IN（NOT IN）になってきます。
■ IN（NOT IN）について
IN は WHERE 句において、=（イコール）の代わりに使用されます。=（イコール）は「多 対 1」の関係で使用されますが、IN は「多 対 多」の関係で使用されます。
超入門（1）の記事で「神奈川県」の人を取得した（多 対 1 の関係）時に、=（イコール）を使用しました。
SELECT Id
, Email
, Name COLLATE Japanese_CS_AS_KS_WS as [Name]
FROM MasterSubscribers
WHERE Prefecture = '神奈川県'一方、「神奈川県」または「千葉県」の人を取得したい（多 対 多 の関係）場合は、以下のように IN を使用します。
SELECT Id
, Email
, Name COLLATE Japanese_CS_AS_KS_WS as [Name]
FROM MasterSubscribers
WHERE Prefecture IN ('神奈川県','千葉県')■ IN では、カッコ内において、カンマ区切りで文字列を記述して、○ または ○ であるものを取得します。このカンマ区切りの意味は、AND ではなく OR の意味になります。
■ NOT IN の場合は、○ または ○ 以外であるものを取得します。つまり、上記の例であれば、神奈川県と千葉県以外の県の人たちを取得します。
※ WHERE Prefecture IN ('神奈川県') のように、IN の ( ) の中に条件を 1 つだけ指定した場合は =（イコール）を使用した時と同じ意味になりますが、パフォーマンスの面を考えると =（イコール）の使用が推奨されます。
さて、これをサブクエリに応用すると、以下のようになります。少し読みづらくなるので、段落をつけて可読性を上げました。
SELECT
Id,
Email,
Name COLLATE Japanese_CS_AS_KS_WS AS [Name]
FROM
MasterSubscribers
WHERE
Id IN (
SELECT
SubscriberKey
FROM
_Click
WHERE
TriggeredSendCustomerkey = '88597'
)
ここでは WHERE に注目して下さい。配信マスタ（MasterSubscribers）の Id が、サブクエリを実行した際の SELECT で取得される Click データビューの Subscriberkey と一致した場合に、レコードを取得しようとしています。
最初のうちは、この記述が理解できないかもしれませんが、これが理解できるようになることが、SQL を理解する一歩になります。
まず、データビューで必要な購読者キーを取得してきて、その後、配信マスタの購読者キーとマッチングさせる、この「流れ」をしっかりとイメージしてください。SQL では、この頭の中で行う「流れ」のイメージを持てるかが重要になります。
以前に、私が書いた「ジャーニー上のメールアクティビティでメールを開封しなかった人を取得する方法」という記事でも、同じように IN や NOT IN を使って実装しています。この記事では TriggeredSendCustomerkey ではなく、ActivityID を使用してメールアクティビティを特定していますので、その方法も試してみてください。
余談ですが、上記のようなクリックの履歴を使用した配信リストの実装であれば、Marketing Cloud の標準機能である「メジャー」を使用することで、実装できることも認識しておくべきです。
メジャーの場合は、以下のように構成します。SQL 同様、メールアクティビティの外部キーを「Triggered Send External Key」で選択します。
以前に、イベントソースでは「一意のクリック」を選択しない方が良いと指摘しました。詳細は下記の記事を参考にしてください。
メジャーを構成後、フィルター設定時に条件の一つとして使用できるようになります。左メニューに、構成したメジャーが表示されていますね。
SQL の条件同様、何らかクリックをしたことがあるという条件であれば、「値あり」の設定になります。今回は、この状態で保存してビルドします。
すると、以下のように、SQL と同じ内容の配信リストが作成されました。
■ IS NULL について
それでは、最後に、=（イコール）や IN、NOT IN を説明してきたので、IS NULL についても触れておきます。
SQL において、値が NULL の場合は、=（イコール）で取得できません。NULL の場合は、 =（イコール）ではなく、IS を使用することになります。
SELECT Id
, Email
, Name COLLATE Japanese_CS_AS_KS_WS as [Name]
FROM MasterSubscribers
WHERE Name IS NULL上記のように、IS NULL と記述すると Name に値が入っていない人を取得することができます。逆に、Name に何らかの値が入っている人を取得したい場合は、IS NOT NULL と記述します。
SELECT Id
, Email
, Name COLLATE Japanese_CS_AS_KS_WS as [Name]
FROM MasterSubscribers
WHERE Name IS NOT NULLデータを調査している際に、予想に反して値が少なく取得されてくるなど、WHERE で使用している項目の値が NULL となっており、思った数にならない場合に、IS NULL を使って、値が入っているか調査できますね。
また、名前が入っている人だけを配信リストに組み込みたい場合は、IS NOT NULL を使用して、%%Name%% で値が空にならないように工夫できます。
この IS NULL、IS NOT NULL も、=（イコール）や IN、NOT IN に合わせて覚えておいてください。
いかがでしたでしょうか。
ここまでで、データビューで得られた情報で、配信リストに転化させることが可能になりました。次の記事では、データエクステンションやデータビューを複雑に組み合わせる JOIN という技術や、WHERE の条件を複雑に設ける AND や OR の技術を学んでいきたいと思います。
今回は以上です。