【第26回】 ジャーニー上のメールアクティビティでメールを開封しなかった人を取得する方法

以前に書いた「Journey Builder のアクティビティ ID の簡単な取得方法」の記事の中でデータビューの使用例を紹介したのですが、ジャーニー上のメールアクティビティでメールを開封しなかった人たちの取得方法が知りたいというご要望を頂きましたので紹介します。

---

データビューとは、Salesforce Marketing Cloud の標準機能であり、過去 6 ヶ月分のエンゲージメントデータ（送信・開封・クリックなど）をシステムが自動的に保存してくれる機能です。

さて、ここで少し考えてみたいのですが「メールを開封しなかった人たち」をデータビューで取得しようとする場合、「開封しなかった」という履歴データは「Open」のデータビューに保存されると思いますか？

もちろん「開封した」というデータが保存されることについては、異論ないと思います。ただ「開封しなかった」というデータの方はどうでしょう？

この答えは、残念ながら「開封しなかった」というデータは、データビューには保存されていません。そのため、Open のデータビューからは「開封していない」という人のデータを取得はできないんですね。

では、どのようにするかですが、以下の方法で取得をすることになります。

① 3 つのメールアクティビティを送信した人
     ー（マイナス）
② 3 つのメールアクティビティのいずれかを開封した人
     ＝（イコール）
③ 3 つのメールアクティビティをすべて開封しなかった人

---

この手順を順に見ていきましょう。

まず、① 3 つのメールアクティビティを送信した人です。

SELECT DISTINCT
  s.Subscriberkey
FROM
  _Sent s
JOIN
  _JourneyActivity ja
ON
  s.TriggererSendDefinitionObjectID = ja.JourneyActivityObjectID
WHERE
  ja.ActivityID IN ('ActivityID_①', 'ActivityID_②', 'ActivityID_③')

この WHERE で記述されている ActivityID は「【第15回】 Journey Builder のアクティビティ ID の簡単な取り方」の方法で取得してください。

続いて、② 3 つのメールアクティビティのいずれかを開封した人です。

SELECT
  o.Subscriberkey
FROM
  _Open o
JOIN
  _JourneyActivity ja2 ON o.TriggererSendDefinitionObjectID = ja2.JourneyActivityObjectID
WHERE
  ja2.ActivityID IN ('ActivityID_①', 'ActivityID_②', 'ActivityID_③')
  AND o.IsUnique = 1
GROUP BY
  o.Subscriberkey
HAVING
  COUNT(o.Subscriberkey) >= 1

ポイントは「1 つのメール以上開封した人」を取得している点です。これにより、以下の 3 つのパターンを同時に取得しているわけですね。

・3 つのすべてを開封した人
・2 つのメールを開封した人
・1 つのメールのみ開封した人

そして最後に、③ 3 つのメールアクティビティをすべて開封しなかった人です。これは ① から ② を引き算することになります。この引き算の方法は色々あるかと思いますが、今回は、WHERE で NOT IN（… ではない）を使用しました。

SELECT DISTINCT
  s.Subscriberkey
FROM
  _Sent s
JOIN
  _JourneyActivity ja ON s.TriggererSendDefinitionObjectID = ja.JourneyActivityObjectID
WHERE
  ja.ActivityID IN ('ActivityID_①', 'ActivityID_②', 'ActivityID_③')
  AND s.Subscriberkey NOT IN (
    SELECT
      o.Subscriberkey
    FROM
      _Open o
    JOIN
      _JourneyActivity ja2 ON o.TriggererSendDefinitionObjectID = ja2.JourneyActivityObjectID
    WHERE
      ja2.ActivityID IN ('ActivityID_①', 'ActivityID_②', 'ActivityID_③')
      AND o.IsUnique = 1
    GROUP BY
      o.Subscriberkey
    HAVING
      COUNT(o.Subscriberkey) >= 1
  )

---

いかがでしたでしょうか？

ジャーニーでエンゲージメント分岐（開封やクリックに基づいて分岐させる分岐）を使用しても、似たようなシナリオが実装できますが、もし、ジャーニーにエンゲージメント分岐を仕込み忘れても、今回の方法を使うことで、後からでも「開封」や「クリック」の履歴を使って、追加のシナリオが構築できるようになります。

是非、このやり方を押さえておいてください。

今回は以上です。

次の記事はこちら

前回の記事はこちら

私の note のトップページはこちら

Nobuyuki Watanabe｜note