クエリ: 過去 30 日以内に開く

ご利用になる前に

この例を使用するには、SQL に関する実用的な Knowledge が必要です。コードサンプルをコピーして貼り付けることができますが、設定に合わせて設定する必要があります。

対象データエクステンションの作成

Contact Builder または Email Studio で、次のプロパティと項目を使用して標準データエクステンションを作成します。このデータエクステンションは、クエリのデータ出力先です。

メモ Email Studio でデータエクステンションを作成する場合、[必須] 列は [Nullable] 列に置き換えられます。フィールドが必須でない場合は、[Nullable] を選択します。項目が必須の場合は、[Nullable] を選択しないでください。

クエリ活動の作成

Automation Studio で、次のプロパティを使用して SQL クエリを作成します。

サンプルクエリ

このクエリをコピーして貼り付けます。

SELECT j.jobid,
       j.emailname,
       j.deliveredtime AS SendTime,
       o.eventdate     AS OpenTime,
       s.emailaddress,
       s.subscriberkey
FROM   [_job] AS j
       INNER JOIN [_open] AS o
               ON j.jobid = o.jobid
       INNER JOIN [_subscribers] AS s
               ON o.subscriberid = s.subscriberid
WHERE  o.isunique = 1
       AND o.eventdate > Dateadd(day, -30, Getdate())
       AND j.emailid = MY_EMAIL_ID

クエリ活動の最終決定

クエリをカスタマイズします。Automation Studio の行番号を使用して、変更する行を識別します。

• 最後の行で、 MY_EMAIL_ID をメールの ID に置き換えます。メール ID が不明な場合は、コンテンツビルダーに移動してメールを選択し、その詳細を表示します。
  
• Enterprise 2.0 アカウントの子ビジネスユニットから照会する場合は、10 行目を変更してent.プレフィックスを追加します。
  • 元： INNER JOIN [_subscribers] AS s
  • 変更日: INNER JOIN ent.[subscribers] AS s

クエリを対象データエクステンションにリンクします。

クエリ活動を保存し、その活動を使用するオートメーションを作成して有効化します。

クエリのバリエーション: 別の期間の取得

異なる期間のデータを取得するには、180 日までの任意の日数を使用します。 たとえば、このクエリの 13 行目を変更して、14 日間のデータを取得します。

SELECT j.jobid,
       j.emailname,
       j.deliveredtime AS SendTime,
       o.eventdate     AS OpenTime,
       s.emailaddress,
       s.subscriberkey
FROM   [_job] AS j
       INNER JOIN [_open] AS o
               ON j.jobid = o.jobid
       INNER JOIN [_subscribers] AS s
               ON o.subscriberid = s.subscriberid
WHERE  o.isunique = 1
       AND o.eventdate > Dateadd(day, -14, Getdate())
       AND j.emailid = MY_EMAIL_ID

特定の範囲 (30 ～ 60 日前など) を取り込むには、次のコードを使用します。

SELECT j.jobid,
       j.emailname,
       j.deliveredtime AS SendTime,
       o.eventdate     AS OpenTime,
       s.emailaddress,
       s.subscriberkey
FROM   [_job] AS j
       INNER JOIN [_open] AS o
               ON j.jobid = o.jobid
       INNER JOIN [_subscribers] AS s
               ON o.subscriberid = s.subscriberid
WHERE  o.isunique = 1
       AND o.eventdate BETWEEN Dateadd(day, -60, Getdate()) AND
                               Dateadd(day, -30, Getdate())
       AND j.emailid = MY_EMAIL_ID

取得するデータの量によっては、30 日を超えるクエリがタイムアウトして失敗する可能性があります。クエリがタイムアウトした場合は、短い期間でお試しください。

クエリのバリエーション: Capture Sends (送信のキャプチャ)

開封ではなく送信を取得するには、次のコードを使用します。

SELECT j.jobid,
       j.emailname,
       se.eventdate AS SendTime,
       s.emailaddress,
       s.subscriberkey
FROM   [_job] AS j
       INNER JOIN [_sent] AS se
               ON j.jobid = se.jobid
       INNER JOIN [_subscribers] AS s
               ON s.subscriberid = se.subscriberid
WHERE  se.eventdate > Dateadd(day, -30, Getdate())
       AND j.emailid = MY_EMAIL_ID

クエリのバリエーション: Capture Clicks (クリック数の取得)

開封数ではなくクリック数を取得するには、次のコードを使用します。また、ClickTime のフィールドを対象データエクステンションに追加する必要があります。

SELECT j.jobid,
       j.emailname,
       j.deliveredtime AS SendTime,
       c.eventdate     AS ClickTime,
       s.emailaddress,
       s.subscriberkey
FROM   [_job] AS j
       INNER JOIN [_click] AS c
               ON j.jobid = c.jobid
       INNER JOIN [_subscribers] AS s
               ON c.subscriberid = s.subscriberid
WHERE  c.isunique = 1
       AND c.eventdate > Dateadd(day, -30, Getdate())
       AND j.emailid = MY_EMAIL_ID

クエリのバリエーション: Capture Bounces (バウンスを取得)

開封ではなくバウンスを取得するには、次のコードを使用します。また、BounceTime のフィールドを対象データエクステンションに追加する必要があります。

SELECT j.jobid,
       j.emailname,
       b.eventdate AS BounceTime,
       s.emailaddress,
       s.subscriberkey
FROM   [_job] AS j
       INNER JOIN [_bounce] AS b
               ON j.jobid = b.jobid
       INNER JOIN [_subscribers] AS s
               ON s.subscriberid = b.subscriberid
WHERE  b.eventdate > Dateadd(day, -30, Getdate())
       AND j.emailid = MY_EMAIL_ID

クエリのバリエーション: Capture Complaints (苦情の収集)

開封ではなく苦情を取得するには、次のコードを使用します。また、対象データエクステンションに ComplaintTime フィールドを追加する必要があります。

SELECT j.jobid,
       j.emailname,
       c.eventdate AS ComplaintTime,
       s.emailaddress,
       s.subscriberkey
FROM   [_job] AS j
       INNER JOIN [_complaint] AS c
               ON j.jobid = c.jobid
       INNER JOIN [_subscribers] AS s
               ON s.subscriberid = c.subscriberid
WHERE  c.eventdate > Dateadd(day, -30, Getdate())
       AND j.emailid = MY_EMAIL_ID

クエリのバリエーション: Capture Unsubscribes (登録解除を取得)

開封ではなく登録解除を取得するには、次のコードを使用します。また、対象データエクステンションに UnsubscribeTime のフィールドを追加する必要があります。

SELECT j.jobid,
       j.emailname,
       u.eventdate AS UnsubscribeTime,
       s.emailaddress,
       s.subscriberkey
FROM   [_job] AS j
       INNER JOIN [_unsubscribe] AS u
               ON j.jobid = u.jobid
       INNER JOIN [_subscribers] AS s
               ON s.subscriberid = u.subscriberid
WHERE  u.eventdate > Dateadd(day, -30, Getdate())
       AND j.emailid = MY_EMAIL_ID