【第175回】 Marketing Cloud SQL 超入門（3）- IN（NOT IN）、IS NULL

これまでに Markeitng Cloud SQL の基本 と SELECT、FROM、WHERE 、そして、Dataview の使用 や 日付関数 について学習しました。

そこでは Marketing Cloud で SQL を使用する目的 について触れました。以下のような事例に沿って学習すると良いという話でしたね。

そして、これまでの記事では ①② まで学習しました。今回は ③「データビューを活用して、別の配信リストを作りたい」について学習します。

例えば、あるジャーニーのあるメールアクティビティで開封やクリックした人たちを集めて、配信リストに転化させたいということがありますよね。

このように、データビューから対象者を取得して、配信マスタと組み合わせて行くということが 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 ('神奈川県','千葉県')

さて、これをサブクエリに応用すると、以下のようになります。少し読みづらくなるので、段落をつけて可読性を上げました。

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 では、この頭の中で行う「流れ」のイメージを持てるかが重要になります。

余談ですが、上記のようなクリックの履歴を使用した配信リストの実装であれば、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 の技術を学んでいきたいと思います。

今回は以上です。

次の記事はこちら

前回の記事はこちら

私の note のトップページはこちら