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