SQL を使用したデータのクエリ

学習の目的

この単元を完了すると、次のことができるようになります。

• SQL とその使用目的を定義する。
• Marketing Cloud Engagement 内で SQL を使用する。
• SQL 使用のベストプラクティスを挙げる。

SQL とは

Marketing Cloud Engagement への引っ越しが終わり、荷解きをして、場所の整理も始めました。ここでは、最後の仕上げをします。前述したように、Marketing Cloud Engagement データエクステンションと Contact Builder ではリレーショナルデータベースが使用されています。そのため、それらの充実したデータを取得する良い方法が必要です。そこで登場するのが Structured Query Language (SQL) です。SQL は、それを実行できるドメイン特化言語です。Marketing Cloud Engagement では、SQL クエリアクティビティは、レポートまたはオーディエンスのセグメント化のためにクエリを実行し、データを取得するために使用されます。 

Marketing Cloud Engagement で SQL を使用する

まず、Marketing Cloud Engagement で、いくつかの具体的な SQL コマンドがどのように使用されるかを確認しましょう。

• SELECT: データベースのデータを特定するコマンド。
• FROM: Marketing Cloud Engagement 内でデータが存在する場所 (通常はデータエクステンション)。
• JOIN: クエリが複数のテーブルやデータエクステンションを検索できるようにします。
• WHERE: 必要/不要なデータを絞り込むために使用されます。

次に例を示します。

SELECT emailaddress as ‘Email_Address’, favoritecolor as ‘Favorite_Color’
FROM [MasterData Extension] m
INNER JOIN JuneMarketingSend j
ON m.emailaddress = j.emailaddress
WHERE m.emailaddress is not NULL

解説が必要ですか? このクエリを実行すると、マスターデータエクステンションからメールと好きな色を取得し、それを 6 月のマーケティング送信からの一致するメールアドレスと結合します。さらに、欠損していないメールのみを返します。また、m と j にも注目してください。これらは、テーブルまたはテーブル内のフィールドに一時的な名前を与えるために使用される SQL エイリアスです。これは特に、複数のデータエクステンション間で共通するフィールド名 (CustomerID や EmailAddress など) がある場合に便利です。記述例は次のようになります。

SELECT o.OrderID, o.OrderDate, c.CustomerName
FROM Customers AS c, Orders AS o
WHERE c.CustomerID=o.CustomerID

SQL コマンドでは大文字と小文字が区別されません。select と SELECT は同じです。

SQL データビュー

システムデータビューは、Salesforce によって作成されたクエリで、購読者に関する情報を検索するために使用できます。ただし、これらの事前作成されたデータビューは変更できません。特によく使用されるビューを次に挙げます。

クリックと開封のトラッキングデータは中央標準時間で表示され、夏時間は適用されず、最も近い秒に丸められます。

では、これらのデータビューをどのように使用すればよいのでしょうか? まず、これらのビューからの必要なデータを保存するデータエクステンションを作成し、次に Automation Studio に移動して目的のデータビューテーブルに基づくクエリアクティビティを作成します。オートメーションを完了して実行すると、出力は作成したデータエクステンションに保存されます。

Complaint データビューから過去 6 か月間のデータを取得して、SubscriberKey、JobID、BounceReason を提供するクエリの例を次に示します。 

SELECT SubscriberKey,JobID,SMTPBounceReason
FROM _Complaint

SQL を使用したデータの結合

データビュー以外にも、さまざまなビジネスニーズを解決するために SQL クエリをカスタマイズすることができます。たとえば、送信ログに保存されているデータと顧客データエクステンションに存在するデータを含むメールを送信したいとします。これらのクエリを作成するには、結合の概念を理解する必要があります。これは、1 つのソースからのデータを別のソースからのデータと結合することです。

SQL 結合は複雑になることもあるため、ベン図を使用していくつかの結合のシナリオを見てみましょう。 

上の行から始めましょう。これらは左右の結合と呼ばれます。

図 1: 左外部結合

データエクステンション A からのすべてのデータと、データエクステンション B で一致するレコードを取得します。

SELECT *
FROM DataExtension A
LEFT JOIN DataExtension B
ON A.Field = B.Field

結果の例

図 2: 除外ありの左外部結合

この結合では、データエクステンション B からの不要なレコードを除外します。

SELECT *
FROM DataExtension A
LEFT JOIN DataExtension B
ON A.Field = B.Field
WHERE B.Field IS NULL

結果の例

図 3 および 4: 右結合

右結合は、前のシナリオの逆です。

SELECT *
FROM DataExtension A
RIGHT JOIN DataExtension B
ON A.Field = B.Field

SELECT *
FROM DataExtension A
RIGHT JOIN DataExtension B
ON A.Field = B.Field
WHERE A.Field IS NULL

次は、内部結合と外部結合を見ていきましょう。 

図 5: 内部結合

次のクエリは、両方のデータエクステンションに一致する値があるレコードを返します。 

SELECT *
FROM DataExtension A
INNER JOIN DataExtension B
ON A.Field = B.Field

結果の例

図 6: 完全外部結合

完全外部結合は、両方のデータエクステンションからの一致するすべてのレコードを返します。

SELECT *
FROM DataExtension A
FULL OUTER JOIN DataExtension B
ON A.Field = B.Field

結果の例

図 7: 外部結合 

両方のデータエクステンションからすべての一致するレコードを返しますが、不要なレコードは除外されます。

SELECT *
FROM DataExtension A
FULL OUTER JOIN DataExtension B
ON A.Field = B.Field
WHERE A.Field IS NULL OR B.Field IS NULL

結果の例

外部結合では、非常に大きな結果セットが返されることがあるため、注意してください。

SQL の動作

Cloud Kicks は、過去 6 か月間にメールを開封したことがあり、その期間中に購入を行った顧客限定のオファーを送信することにしました。ソリューションアーキテクトの Maggie Quinn は、SQL クエリを使用してこのタスクを実行する必要があります。それでは見てみましょう。 

まず Maggie は、この条件を満たす顧客に関する情報を保存するデータエクステンションを作成します。次に、Automation Studio に移動して、次の手順に従います。

1. [新しいオートメーション] をクリックします。
2. [開始ソース] で [スケジュール] または [ファイルドロップ] を選択します。
3. ワークフローに [SQL クエリ] をドラッグし、[選択] をクリックします。(既存のクエリを選択するか、新しいクエリを作成できます。)
4. [新しいクエリアクティビティの作成] を選択します。
5. クエリのパラメーター (名前、外部キー、フォルダーの場所、説明) を追加します。
6. 次に、SQL クエリを作成します。

Maggie は次のクエリを使用します。 

SELECT c.EmailAddress, c.CustomerID, c.First_Name
FROM Customers c
INNER JOIN Purchases p
ON c.CustomerID = p.CustomerID
WHERE [Purchase Date] > DateAdd(month, -6, GetDate()) AND [Open Date] > DateAdd(month, -6, GetDate())

スペースを含むデータエクステンションとフィールド名は [ ] 括弧で囲む必要があります。

完了したら、残りの手順に従います。

1. [構文の検証] をクリックします。
2. クエリ結果を保存するために作成されたデータエクステンションを選択します。
3. クエリアクティビティが実行するデータアクションを、[追加]、[更新]、[上書き] から選択します。
4. [Save (保存)] をクリックします。

準備ができたら、オートメーションを実行できます。 

一般的な SQL クエリ

親アカウント

クエリ内のデータエクステンション名にプレフィックス ent. を追加すれば、親アカウントの共有データエクステンションフォルダー内のデータエクステンションを照会できます。 

SELECT EmailAddress
FROM ent.MasterDataExtension

すべてのデータ

すべてが必要になることもあります。

SELECT *
FROM MasterDataExtension

SELECT * は慎重に使用してください。大量のデータを処理するためにシステムが遅くなることがあります。データセットが大きいほど、システムがデータを取得するための時間と負荷が大きくなります。クエリは 30 分でタイムアウトし、処理するデータが多いほどクエリがタイムアウトする可能性が高くなります。これを避けるには、求めるフィールドのみをクエリで指定すると効果的です。 

SELECT field1, field2, field3
FROM MasterDataExtension

SQL の習得には何年もかかることもありますが、とりあえず基本は学習できました。皆さんとデータが新しい家で落ち着けることを願っています。また、皆さんが Marketing Cloud Engagement データを利用して何を実現したかを見るのを楽しみにしています。 

リソース

• Salesforce ヘルプ: データビュー
• Salesforce ヘルプ: SQL のリファレンス
• Salesforce ヘルプ: SQL クエリアクティビティの使用