【第174回】 Marketing Cloud SQL 超入門（2）- Dataview の使用、日付関数
前の記事では、Markeitng Cloud SQL の基本と SELECT、FROM、WHERE について学習しました。
そこでは Marketing Cloud で SQL を使用する目的について触れました。以下のような事例に沿って学習すると良いという話でしたね。
① データエクステンション内のデータを調査したい
② データビューを使用して、エンゲージ（関与）した顧客を知りたい
③ データビューを活用して、別の配信リストを作りたい
④ 2 つ以上のデータエクステンションを組み合わせて、別のデータエクステンションを作成したい
そして、① については学習済みですので、今回は ②「データビューを使用して、エンゲージ（関与）した顧客を知りたい」について学習します。
■ Dataview（データビュー）とは
データビューとは、Marketing Cloud の標準機能で、現時点の購読者の情報（メールアドレスや購読者ステータス）、ジャーニー情報（ジャーニー名やアクティビティ名）、また、各連絡先の送信・開封・クリック・バウンス・購読取り消しなどの履歴が自動的に保存されるシステムデータテーブルのことです。
現時点の購読者の情報やジャーニー情報などについては保存期限はありませんが、各連絡先の送信・開封・クリック・バウンス・購読取り消しなどの履歴については、6 か月間という保存期限があり、その期間を過ぎたデータに関しては、自動的に削除されます。
■ Dataview の Help ページはこちら
また、前の記事でも紹介しましたが、Salesforce MVP のズザンナ・ヤルチンスカさんが作成してくれた「dataviews.io」というサイトがとても便利ですので、ブックマークに入れておきましょう。「Display details」というボタンを押せば、各項目の説明も分かります。
■ エンゲージ（関与）した顧客を知る
まず、エンゲージ（関与）した顧客を知る方法としては、Email Studio のトラッキングから確認ができます。
但し、ここでは階層的に深いページまで探っていかないと、そのデータが取得できないのと、それらをすぐに配信リストとして活用することが難しい点で、イマイチ使いづらい感じがあります。
そこで SQL とデータビューを活用してみます。まず、以下の 5 つのデータビューを確認しましょう。
① 送信 ・・・ _Sent
② 開封 ・・・ _Open
③ クリック ・・・ _Click
④ バウンス ・・・ _Bounce
⑤ 購読取り消し ・・・ _Unsubscribe
※ 最大 6 ヶ月分のデータのみ保存されます。
※ データービュー名は、大文字・小文字は区別されません。
データビューでは、アンダースコア（ _ ）プレフィックスを付ける必要があります。このアンダースコア（ _ ）が付いていることで、通常のデータエクステンションとの区別を付けています。
前述の通り、エンゲージメントデータは、自動的に 6 ヶ月分のデータが保存されますので、以下のように、条件を何も指定しないで取得した場合は、最大枠（期間）での 6 ヶ月分のデータがすべて取得されます。
SELECT SubscriberKey, EventDate
FROM _Sent実際、Query Studio で試してみますと、下図の通り、6 ヶ月分のアカウントから送信されたすべての送信履歴が取得できました。しかし、実際の運用で使用する場合では「どのメールから送信されたものであるか？」の条件で指定してあげる必要がありそうです。
どのメールから送信されたかを知るには、以下の項目を使用します。
■ Email Studio 送信の場合：JobID を使用
■ Journey Builder 送信の場合：TriggeredSendCustomerkey を使用
※ 今回紹介している 5 つのデータビューうち、_Unsubscribe に関しては、TriggeredSendCustomerkey の項目が存在しないので、JobID を使用して取得することになります。
さて、それでは JobID や TriggeredSendCustomerkey の値は、どこで取得できるか？についてですが、これらは、両方ともトラッキングの画面から取得できます。
■ JobID の表示場所
JobID とは「メール送信ごとに付与される ID」のことです。Email Studio 送信の場合は、以下のトラッキングの画面から取得できます。
SQL クエリで JobID を指定する場合、以下の通り、WHERE で指定します。
SELECT SubscriberKey, EventDate
FROM _Sent
WHERE JobID = '284295'
※ ID は数字型のため、クォーテーションは付けても付けなくてもどちらでも問題ありません。これで、このメール配信の購読者キー（顧客 ID）が取得できましたね。
■ TriggeredSendCustomerkey の場所
Journey Builder 送信でも JobID は存在していますが、Email Studio 送信の場合と仕組みが少し異なり、そのメールアクティビティ上のメールコンテンツの内容が変わるまで、同じ JobID が使用されます。
この「メールコンテンツの内容が変わるまで」とは、例えば、
① 同じジャーニーのバージョン内でメールコンテンツの編集を行ったり
② 実行中のジャーニーを新しいバージョンに変えてアクティブ化した場合
に JobID が変更となります。
この時、メールコンテンツの編集をしただけで JobID が変更されることは、ユーザーにとって不本意であることが多いため、このケースでは、JobID ではなく、TriggeredSendCustomerkey を使用します。
この TriggeredSendCustomerkey も、Email Studio のトラッキング画面から取得できます。以下の通り、Journey Builder Sends の トラッキングレポートにおいて、External Key と表示されている箇所になります。
SQL クエリで TriggeredSendCustomerkey を指定する場合も、以下の通り、WHERE で指定します。
SELECT SubscriberKey, EventDate
FROM _Sent
WHERE TriggeredSendCustomerkey = '84505'
※ Key は数字型のため、クォーテーションは付けても付けなくてもどちらでも問題ありません。これで、このジャーニーのメールアクティビティで配信された購読者キーが取得できましたね。
ちなみに TriggeredSendCustomerkeyは、ジャーニービルダーのメールアクティビティ上からも探すことができます。Journey Builder のメールアクティビティを開いて、アクティビティ・サマリーに移動したら、一番下にある「詳細オプション」の箇所に表示されています。
さて、これで、基本的なデータビューへのアクセス方法が学べましたね。ここで取得できたエンゲージメントデータを使って、メールアドレスを持った配信リストに転化する方法については、SQL を学ぶ目的の ③「データビューを活用して、別の配信リストを作りたい」へ繋がりますので、また次回の記事で書いてみたいと思います。
それでは、今回の記事では、最後に SQL の日付関数について触れておきたいと思います。
■ SQL の日付関数
① DATEADD（日時の加算）
実は、先ほどから データビューで取得している EventDate（送信日、開封日、クリック日など）は、15 時間前（CST タイムゾーン）の時間で取得されています。つまり、日本時間でデータを取得するは 15 時間を足してあげる必要があります。
この時間の加算は、すべての日付項目に対して行う必要はありません。通常のデータエクステンションに格納されている日付データは、すでに、日本時間で表記されていると思いますので 15 時間プラスの作業は不要です。
主に覚えておくべきは、以下の 2 つについてです。これらを「システム時間」と呼んだりします。
■ データビューの EventDate ・・・ 送信日、開封日、クリック日など
■ GETDATE() ・・・ 現在の日時を返す関数
この 2 つに対しては、15 時間プラスすると覚えておいてください。これらがすべてではありませんが、この 2 つが頻出します。
余談になりますが、Marketing Cloud Connect で DateTime 型で連携されている項目も、Salesforce CRM 側で表示されている時間に対して、15 時間マイナスで連携されます。これらを Marketing Cloud で使用するには、15 時間プラスする必要があります。（※ Date 型の場合は問題になりません。）
■ Marketing Cloud Connect における 日付型と日付時間型の扱いについて
さて、この 15 時間をプラスするには、DATEADD() という関数を使います。
※ DATEADD() を使用する際は、以下のように AS を使用して「エイリアス」（別名）を付けてください。
DATEADD(hh, 15, EventDate) AS [EventDate]DATEADD(hh, 15, GETDATE()) AS [GetDate]ここでは「引数」という関数に値を渡すための情報のようなものを ( ) 内にカンマ区切りで記載するのですが、以下のような形となります。
DATEADD(①加算する時間の単位,②加算する数字,③どの項目に加算するか)
① の「加算する時間の単位」に関しては、書き方はいくつかありますが、一旦以下で覚えてみてください。
■ 年 ・・・ yyyy
■ 月 ・・・ mm
■ 日 ・・・ dd
■ 時 ・・・ hh
これで日時の補正ができるようになりました。
SELECT SubscriberKey
, DATEADD(hh, 15, EventDate) AS [EventDate]
FROM _Sent
WHERE JobID = '284295'② CONVERT（日時の時間部分の丸め）
続いて紹介するのが、時間の丸め作業です。この「時間の丸め」とは、日時のデータから時間のデータを切り捨てて、日付のみのデータにすることだと思ってください。
データビューの EventDate や GETDATE() 関数で取得される現在の日時には、時間のデータが含まれます。Marketing Cloud においては、この時間のデータは、あまり良い意味を持ちません。そのため、時間のデータを切り捨ててしまうことが推奨されます。
もし「時間の丸め」を行っていない場合、WHERE で EventDate = '2024-06-26' と指定した場合は、時間が 0:00 のデータしか取得されてきません。
この時間の丸め作業をするには、CONVERT() という関数を使用します。引数は以下のような形です。今回は日付に関連しますので、①は date になります。
CONVERT(①date,②変換する項目,③日付形式)
注意点としては、②については、①で説明した 15 時間プラスの補正した後の値を入れる必要があります。よって下記のようになります。
CONVERT(date, DATEADD(hh, 15, EventDate), ③日付形式) AS [EventDate]③日付形式 に関しては、様々な日付形式があるわけですが、「111」で覚えてください。ちなみに「111」とは「yyyy/mm/dd」という日付形式です。
よって、最終的には以下の形になります。
CONVERT(date, DATEADD(hh, 15, EventDate), 111) AS [EventDate]Date と DateTime を並べて取得すると、以下のような形です。
SELECT SubscriberKey
, CONVERT(date, DATEADD(hh, 15, EventDate), 111) AS [EventDate]
, DATEADD(hh, 15, EventDate) AS [EventDateTime]
FROM _Sent
WHERE JobID = '284295'
③ DATEDIFF（日時の差）
①② でおおよその処理はできるようになったかと思いますが、DATEDIFF() という日時の差を出す関数を知っておくと便利です。
例えば、今日から過去 30 日分のデータを取得する場合に使えます。
DATEDIFF(dd, CONVERT(date, DATEADD(hh, 15, EventDate), 111), CONVERT(date, DATEADD(hh, 15, GETDATE()), 111)) <= 30DATEDIFF(①調査する時間の単位,②開始時間の項目,③終了時間の項目)
この引数の ①「調査する時間の単位」に関しても、DATEADD() の時同様、以下で覚えてください。
■ 年 ・・・ yyyy
■ 月 ・・・ mm
■ 日 ・・・ dd
■ 時 ・・・ hh
この DATEDIFF() の時でも、CONVERT() を使って時間を丸めておかないと、中途半端な時間で日付の範囲が設定されてしまいますので、CONVERT() を入れるようにすることと、もしシステム時間の日時項目を使う場合は 15 時間プラスしておくことも忘れずにお願いします。
それでは、以下の SQL クエリを実行してみます。
SELECT SubscriberKey
, CONVERT(date, DATEADD(hh, 15, EventDate), 111) AS [EventDate]
, DATEADD(hh, 15, EventDate) AS [EventDateTime]
FROM _Sent
WHERE DATEDIFF(dd, CONVERT(date, DATEADD(hh, 15, EventDate), 111), CONVERT(date, DATEADD(hh, 15, GETDATE()), 111)) <= 30これで過去 30 日間で送信された人を取得できました。
ここで、_Sent の部分を _Open に変えれば、過去 30 日間に何らかのメールを開封した人が取得できますし、_Click にすれば、過去 30 日間にクリックした人が取得できます。
いかがでしたでしょうか？
だいぶ SQL の使い方を掴めて来たのではないでしょうか。次の記事では、ここで取得できたデータを配信リストに転化する方法について書いてみます。
今回は以上です。