【第173回】 Marketing Cloud SQL 超入門（1）- SELECT、FROM、WHERE
私の note では、これまでに SQL を使った記事を数多く書いてきましたが、Marketing Cloud SQL の入門版のような記事は書いてきませんでした。この note を読んでいる人の中には、SQL に苦手意識を持っている方もいると思いますので、いくつかの連載形式で Marketing Cloud SQL の基本的な使い方を書いてみようと思います。
■ Marketing Cloud SQL の基本
まず、SQL について調べると、いくつかの SQL の種類に遭遇すると思うのですが、代表的な種類に Oracle、SQL Server、PostgreSQL、MySQL などがある中で、Marketing Cloud SQL は「SQL Server」の機能に基づいています。
今後、SQL の関数を探す場合は、検索ワードに「SQL Server」を含めて検索するようにして下さい。SQL の種類によって、関数の書き方や、使用できる・使用できないが変わる場合があります。
また、その関数が SQL Server で使用できるとされている場合でも、一部の関数は Marketing Cloud SQL で使用できない場合があります。その場合は、実行するとエラーになりますので、エラー内容の指示に従ってください。
続いて、いくつかの Marketing Cloud SQL の特徴を述べます。
■ サポートされているのは、SELECT のみです。INSERT、UPDATE、DELETE は使えません。この SELECT とは、レコードの取得になります。つまり、基本的には、データの中身を何も変えずに、条件に合ったレコードを取得して終わりという機能になりますが、SQL クエリアクティビティの機能として、その SELECT で取得された結果を使って、別のデータエクステンション内のレコードの追加・更新・上書きを行うことができます。
■ アクセス可能なデータは「データエクステンション」と「データビュー」に限られます。データエクステンションに関しては説明不要かと思いますが、データビューとは、SFMC が標準機能として、システムで一定のデータを自動保管する機能になります。どのようなデータビューがあるかに関しては、公式のヘルプページを見て頂く形になりますが、「dataviews.io」という便利なサイトがありますので、そちらを見た方が早いかもしれません。サイト内の「Display details」というボタンを押せば、各項目の説明まで分かるようになっています。こちらのサイトは、Salesforce MVP / Marketing Cloud スペシャリストのズザンナ・ヤルチンスカさんが作成してくれたものです。
■ SQL では「値」や「項目名」の大文字と小文字は区別しません。非常にラフに記述しても動きます。
■ 文の最後にセミコロン（ ; ）は不要です。SQL の参考書などによっては、最後にセミコロン（ ; ）を付けるように書かれているものがありますが、Marketing Cloud SQL では付けてしまうとエラーが発生します。
■ システム日付を取得する際、そのタイムゾーンは CST（米国中部標準時）で動作しますので、日本時間に対して 15 時間マイナスで表示されます。これは、あくまでシステム日付に限られますので、データエクステンションに格納されているカスタム日付を取得する際は、特に注意は不要です。ここでいうシステム日付とは、例えば、データビューから取得できる「Eventdate」（送信日、開封日、クリック日）だったり、「今日」という日時データを取得する関数「Getdate()」 を使用する場合などです。では、このシステム時間が 15 時間マイナスで表示されっぱなしで良いというわけではないので、15 時間をプラスする処理が必要になるわけですが、それは、また次の記事で取り上げます。
■「予約語」と呼ばれるクエリ内で使用できない単語がいくつかあります。例えば「FROM」は代表的なものです。この「FROM」はデータエクステンション名や項目名に混ざりがちですので、そのような「予約語」が混ざる場合は、データエクステンション名や項目名を [ ]（角かっこ）で囲むことで使用できるようになります。
＜重要＞
「予約語」の他にも、
① 名前が「数字」で始まる場合
② 名前に「半角スペース」や「ハイフン」を含む場合
③ 名前を「日本語」（かな漢字）で命名している場合
なども [ ]（角かっこ）で囲むようにして下さい。
// 項目名に「予約語」の「from」が混ざる場合
SELECT Id, [Days_from_CreateDate]
FROM MasterSubscribers
// データエクステンション名の始まりが「数字」の場合
SELECT Id, Email
FROM [2024_Master_Subscribers]
// データエクステンション名に「半角スペース」が含まれる場合
SELECT Id, Email
FROM [Master Subscribers]
// データエクステンション名が「日本語」（かな漢字）で命名されている場合
SELECT Id, Email
FROM [マスタ配信リスト]例えば、Days_from_CreateDate という項目名には「FROM」という予約語が含まれるので、[ ]（角かっこ）で囲んであげないと、FROM 句自体の FROM と Days_from_CreateDate 内の FROM が、正しく認識されず、以下のようなエラーが発生します。
■ SQL クエリアクティビティは実行開始から 30 分で自動的にエラー（オートキル）となります。よって、より健全な SQL で書くということが大事になりますが、これに関して、私が以前に「多くの処理時間がかかっている SQL クエリアクティビティを発見する方法」についての記事を書いていますので、そちらも参考にしてください。
■ SQL が使用されるツール
① SQL クエリアクティビティ（Automation Studio）
SQL が使用される代表的なツールが Automation Studio の SQL クエリアクティビティです。Automation Studio で SQL クエリアクティビティの設定を開始すると、SQL クエリを入力する画面が登場します。
入力後のページでは、格納先のデータエクステンションを選択し、データアクションの中から「追加」「更新」「上書き」のいずれかを選択します。
ここでのポイントは、名前同士でマッピングが行われるということです。クエリの SELECT で記述した項目名と、格納先データエクステンションの項目名の「完全一致」（大文字・小文字は区別しない）でマッピングが行われます。そして、このマッピングは事前検証が行われないので注意が必要です。つまり、格納先データエクステンションに一致する名前が存在しない場合、単純にスルーされて終わるだけとなります。
② Query Studio（AppExchange）
次に AppExchange の Query Studio です。もしかしたら一度も触ったことが無いという方もいるかもしれませんが、このツールは非エンジニアの管理者の方であっても、是非使用してみて下さい。
アプリスイッチャーの AppExchange から Query Studio に移動できます。場合によっては、権限が付与されておらず、表示されない場合がありますので、主管理者の方にアクセス権を請求してください。
Query Studio が開いたら、SQL クエリを入力して「Run」ボタンをクリックします。
使用上のポイントは、5 回に 1 回くらいの割合で気まぐれのエラーが発生しますので、何度も「Run」ボタンを押すようにしてください。但し、単純な構文エラーの可能性もありますので、エラー説明文を確認してください。
また、Query Studio で取得した結果を日本語（かな漢字）のまま表示をしたい場合は、項目名の後ろに、以下を追加する必要があります。
COLLATE Japanese_CS_AS_KS_WS as [項目名]
この Query Studio は AppExchange で提供されるツールのため、機能の不具合や質問があっても、テクニカルサポートの対象外で、問い合わせができません。また、Marketing Cloud と同じサーバーで動いているものではなく、AWS 上で動く外部ツールのため、Marketing Cloud で IP ホワイトリストを利用している場合は利用できなくなります。この辺りも注意してください。
③ SFMC Query Saver（Google Chrome 拡張機能）
以前に、別の記事で紹介した SFMC Query Saver です。これは Query Studio で実行したクエリを自動で保存してくれるツールになります。このツールに対して、直接クエリを書いていく類のものではありませんが、一応、ここでも紹介しておきます。
■ Marketing Cloud で SQL を使用する目的
さて、SQL の勉強を開始するに当たって、「SQL を使用する目的」をしっかりと認識しておくことが大事になるかと思います。闇雲に参考書の端から勉強しても、なかなか理解が進みません。
Markeitng Cloud で SQL を使用する目的は、主に以下のようなものです。
① データエクステンション内のデータを調査したい
② データビューを使用して、エンゲージ（関与）した顧客を知りたい
③ データビューを活用して、別の配信リストを作りたい
④ 2 つ以上のデータエクステンションを組み合わせて、別のデータエクステンションを作成したい
私が Markeitng Cloud SQL に取り組んできた経験から、これらの事例をベースに学習をスタートした方が良いと思います。
「Marketing Cloud SQL の基本」のところでも述べた通り、結局、アクセス可能なデータは「データエクステンション」と「データビュー」に限られるため、Marketing Cloud SQL でやれることは限定的ですし、具体的な事例からスタートした方が、参考書の事例よりも理解し易いと思います。
それでは、まず、第 1 回目のテーマとして「① データエクステンション内のデータを調査したい」について学びましょう。
■ SELECT、FROM、WHERE を使う
① SELECT 「どの項目を取得するか？」
まずは SELECT です。SELECT では、そのデータエクステンションやデータビューに存在する項目から「どの項目を取得するか？」を決めます。
取得する項目を決めたら、それらを「カンマ区切り」で記載します。
以下のように書いた場合は、データエクステンションから「Id」「Email」「Name」を取得することができるようになります。
※ 最後の項目の後ろには、カンマを入れないように注意してください。
SELECT Id, Email, Nameここでのポイントは、格納先データエクステンションの項目名と、SELECT で記載した項目名が、まったく同じである必要があります。格納先データエクステンションとの項目のマッピングは、項目名の完全一致で行われます。（大文字・小文字は区別されません。）
ここで、格納先データエクステンションの項目名が異なっている場合は「AS」を使用して、別名（エイリアス）に変換してください。
少し、極端な例になりますが
・（操作中の DE の項目名）Id → CustomerId（格納先 DE の項目名）
・（操作中の DE の項目名）Email → EmailAddress（格納先 DE の項目名）
・（操作中の DE の項目名）Name → FullName（格納先 DE の項目名）
というように、すべての項目名が異なる場合であれば、以下のように別名（エイリアス）を設定します。
SELECT
Id AS CustomerId,
Email AS EmailAddress,
Name AS FullNameまた、以下のように SELECT で アスタリスク「*」を使用すると、ソースデータエクステンション内のすべての項目を取得できます。
SELECT *Salesforce は アスタリスク「*」の使用を推奨しておらず、すべての項目名をしっかり記述するように推奨しています。とは言え、使用した方が便利な場合もありますので、そこは臨機応変に対応をお願いします。
※ Query Studio ではアスタリスク「*」は使用できません。
② FROM「どのデータエクステンションから取得するか？」
続いて、FROM は「どのデータエクステンションから取得するか？」を決定します。ここでは、データエクステンションだけではなく、データビューからもデータを取得可能です。
記述方法は、FROM の後ろにデータエクステンション名を入力します。
FROM MasterSubscribersこれで、「MasterSubscribers」というデータエクステンションから「Id」「Email」「Name」という項目を取得するクエリが完成しました。
SELECT Id, Email, Name
FROM MasterSubscribersクエリを書く時の「改行」はどのような扱いになるのか？と気になっている方もいるかもしれませんが、「改行」は入れても入れなくても、結果に影響はありません。
以下のように、SELECT と FROM の間に 2 行分の行間が開いてしまっていても問題ないです。
SELECT Id
, Email
, Name
FROM MasterSubscribersまた、前述の通り、Query Studio で実行する場合に「かな漢字」を表示したい場合は、COLLATE Japanese_CS_AS_KS_WS as [項目名] を付ける必要がありますので、日本語の値が含まれる「Name」の後ろに付けておきます。
SELECT Id
, Email
, Name COLLATE Japanese_CS_AS_KS_WS as [Name]
FROM MasterSubscribersそれでは、実際にこちらのクエリを Query Studio で実行してみます。
これで、データエクステンション「MasterSubscribers」に格納されている、3 レコード分のすべての「Id」「Email」「Name」を取得することができました。「MasterSubscribers」のデータエクステンションには以下が格納されていました。
③ WHERE「どのような条件で取得するか？」
それでは、最後に WHERE です。仮に WHERE を指定しない場合は、すべてのレコードを取得します。一方で例えば、Prefecture（県）が「神奈川県」のレコードだけを取得したい場合は、以下のように WHERE で条件を指定します。
WHERE Prefecture = '神奈川県'
ここで「'神奈川県'」のように、文字列がクォーテーション（ ' ）で囲まれていますが、テキスト型や日付型の場合は、クォーテーション（ ' ）で囲む必要があります。一方、数字型の場合はクォーテーション（ ' ）は囲む必要はは無いです。（囲んであっても動きます。）
細かい話、テキスト型でも数字が格納されている場合があると思いますが、その場合はクォーテーション（ ' ）が必要となります。よって、初心者の方であれば、一旦すべてにクォーテーション（ ' ）を付けるという方針で問題ないと思います。
それでは、WHERE の条件を加えた内容で Query Studio で実行してみます。
SELECT Id
, Email
, Name COLLATE Japanese_CS_AS_KS_WS as [Name]
FROM MasterSubscribers
WHERE Prefecture = '神奈川県'すると、下記の通り、Prefecture（県）が「神奈川県」の方 1 レコードのみが取得できました。
■ !=（ノット・イコール）について
「神奈川県」の方を取得する場合は、=（イコール）を使用しましたが、もし、「神奈川県」以外の方を取得する場合は、!=（ノット・イコール）を使用して下さい。
以上です。
いかがでしたでしょうか？
これでデータエクステンション内のデータが調べられるようになったかと思います。SQL では、SELECT、FROM、WHERE を使うことが、まずは基本になってきます。お手持ちのデータエクステンションを使って、色々とデータを取得してみてください。
取得されたデータを CSV ファイルでエクスポートして、エクセルを使って確認したい場合は、1 日のデータ保持ポリシーを持ったデータエクステンションが自動的に生成されていますので、以下の「QueryStudioResults」というデータエクステンションフォルダを確認してみてください。
この自動生成のデータエクステンションは、すべての項目が「テキスト型」で生成されますので、使用するときは注意してください。
それでは、しばらくこの Marketing Cloud SQL 超入門シリーズの連載を続けてみたいと思います。
今回は以上です。
次の記事はこちら
前回の記事はこちら
私の note のトップページはこちら
この記事が気に入ったらサポートをしてみませんか？