バウンスリストを整理するときに気をつけること

届かないアドレスに送り続けると、配信ドメインの評価が下がります。その結果、届くはずのメールまで迷惑メール扱いになります。だからバウンス（不達）したアドレスは配信対象から外す必要があります。

作業としては「バウンスしたアドレスを集めて、配信停止にする」だけに見えます。ただし会員IDとメールアドレスは1対1ではないため、素朴に突き合わせると、止めるべきでないものを止めたり、止めるべきものを止め損ねたりします。

実際に踏みやすい落とし穴と、散らばったリストを1本化する手順を整理します。

前提：バウンスの種類を区別する

まず、すべてのバウンスを同じ扱いにしません。

苦情と自主停止をバウンスと混ぜないでください。 性質が違うため、後で解除ルールを決めるときに判断できなくなります。配信ツールによっては同じ画面に並んでいるので、取り込む段階で分けておきます。

落とし穴1：同じIDでもアドレスが違う

会員はメールアドレスを変更します。しかし過去の配信ログには、その時点のアドレスが記録されています。

会員ID: U0001
  2024年の配信ログ  → [email protected]   ← ここがバウンス
  現在の会員マスタ  → [email protected]   ← 有効

このとき、IDで配信停止にしてはいけません。バウンスしたのは使われなくなった旧アドレスであって、現在のアドレスは生きています。ID単位で止めると、届くはずの顧客に一切届かなくなります。

対処は、抑止の判断をアドレス単位で行うことです。

-- バウンスしたアドレスが、その会員の「現在のアドレス」と一致するかを確認する
SELECT
  m.member_id,
  m.email          AS current_email,
  b.email          AS bounced_email,
  b.last_bounce_at,
  CASE
    WHEN LOWER(TRIM(m.email)) = b.email_norm THEN '現在のアドレスがバウンス'
    ELSE '過去のアドレス。会員は停止しない'
  END AS judgement
FROM `プロジェクト.mart.会員マスタ` AS m
JOIN `プロジェクト.mart.バウンス集約` AS b
  ON b.member_id = m.member_id;

判定が「過去のアドレス」であれば、そのアドレスは抑止リストに入れますが、会員そのものは配信対象に残します。

落とし穴2：違うIDで同じアドレス

逆のパターンです。1つのアドレスに複数の会員IDが紐づきます。

• 家族で1つのアドレスを共有しています
• 退会後に同じアドレスで再登録しました
• 法人窓口の代表アドレスを複数担当者が使っています

この場合、1件バウンスしたら、そのアドレスを持つすべてのIDが影響を受けます。1つのIDだけ止めても、他のIDから同じアドレスへ送られ続けます。

対処は、抑止リストの主キーをアドレスにすることです。会員IDではありません。配信直前に、宛先アドレスが抑止リストに含まれるかを毎回確認します。

正規化はやりすぎない

突き合わせる前にアドレスを正規化します。ただしやりすぎると別人を同一視します。

+ やドットの除去は、Gmail など一部のサービスの挙動であって、メールアドレスの一般的な仕様ではありません。除去すると、本来別人のアドレスを同一視することになります。正規化は「小文字化・トリム・全角半角」までに留めます。

落とし穴3：入退会を繰り返しているユーザー

退会して再入会すると、新しい会員IDが振られることが多いです。このとき2つの問題が起きます。

過去のバウンス履歴が引き継がれません。 新IDには何の記録もないため、また同じアドレスに送って、またバウンスします。

逆に、止めっぱなしになります。 過去にバウンスしたアドレスで再入会した場合、抑止リストに残っていると一通も届きません。本人は登録したつもりなのに何も来ない状態になります。

ここで効いてくるのが、バウンス記録に「いつ」を必ず持たせることです。

そのうえで、最終バウンス日より後に本人の操作があったかを見ます。

• 再入会しました
• 会員情報でアドレスを再設定しました
• そのアドレスでログインしました

いずれかがあれば、アドレスが復活している可能性が高いです。解除候補として扱います。

-- 最終バウンス以降に本人操作があるアドレスを、解除候補として抽出
SELECT
  b.email_norm,
  b.last_bounce_at,
  a.last_activity_at,
  b.worst_type
FROM `プロジェクト.mart.バウンス集約`   AS b
JOIN `プロジェクト.mart.会員行動サマリ` AS a
  ON a.email_norm = b.email_norm
WHERE a.last_activity_at > b.last_bounce_at
  AND b.worst_type <> 'complaint';   -- 苦情は解除対象外

苦情による停止は解除しません。本人が明確に拒否の意思表示をしているため、性質が違います。

散らばったリストをまとめる手順

バウンス情報は、たいてい複数の場所に散らばっています。配信ツール、過去の手動CSV、サポート窓口への申告などです。これを1本化する手順です。

1. 棚卸し

どこに何があるかを、先に一覧にします。

ここを飛ばして統合を始めると、途中で「もう1箇所あった」が出てきます。

2. 生データのまま1箇所に集める

加工せずに縦積みします。 取得元を示す列を足すだけです。

CREATE OR REPLACE TABLE `プロジェクト.raw.バウンス統合` AS
SELECT 'ツールA' AS source, email, bounce_type, bounced_at FROM `プロジェクト.raw.ツールA_バウンス`
UNION ALL
SELECT 'ツールB' AS source, address, reason, occurred_at FROM `プロジェクト.raw.ツールB_バウンス`
UNION ALL
SELECT '手動CSV' AS source, mail, kind, date        FROM `プロジェクト.raw.手動_バウンス`;

この段階で整形すると、後で「元はどうだったか」を確認できなくなります。加工は次の段階で行います。

3. 正規化と除外

小文字化、トリム、全角半角の統一です。あわせて、形式として成立していない行を除外します。

SELECT
  source,
  LOWER(TRIM(email)) AS email_norm,
  bounce_type,
  bounced_at
FROM `プロジェクト.raw.バウンス統合`
WHERE email IS NOT NULL
  AND REGEXP_CONTAINS(TRIM(email), r'^[^@\s]+@[^@\s]+\.[^@\s]+$');

除外した行は捨てずに、件数と内容を確認します。大量に除外されるなら、取り込み方が間違っています。

4. 種別の名称を揃える

ツールごとに呼び名が違います。hard_bounce / Permanent / 恒久エラー が全部同じ意味、ということが起きます。

対応表を1枚作ります。 元の名称と、統一後の種別を並べたテーブルを用意し、それを結合して変換します。コードに CASE WHEN を書き並べると、ツールが増えるたびに書き換えることになります。

5. アドレス単位に集約する

CREATE OR REPLACE TABLE `プロジェクト.mart.バウンス集約` AS
SELECT
  email_norm,
  MAX(bounced_at)                        AS last_bounce_at,
  COUNT(*)                               AS bounce_count,
  -- 重い種別を優先して残す
  MIN(CASE bounce_type
        WHEN 'complaint' THEN 1
        WHEN 'hard'      THEN 2
        WHEN 'soft'      THEN 3
      END)                               AS worst_rank,
  STRING_AGG(DISTINCT source, ',')       AS sources
FROM `プロジェクト.stg.バウンス正規化`
GROUP BY email_norm;

重い種別が残るように集約します。 同じアドレスにソフトとハードの両方の記録があるなら、ハードとして扱います。

6. 抑止リストを作り、適用ルールを明示する

集約結果から、実際に配信を止めるアドレスを決めます。ルールは表にして残します。

数字は事業の性質で変わります。 重要なのは、その数字が明文化され、誰が見ても同じ判断ができることです。

個人情報としての扱い

バウンスリストはメールアドレスの集合であり、個人情報にあたります。統合作業では扱いに注意します。

• 作業用に手元のスプレッドシートへ落とさないでください。共有範囲が追えなくなります
• 閲覧権限を絞ります。データセット単位で権限を分けます
• 保持期間を決めます。無期限に持ち続けません
• 目的外に使いません。配信抑止のために集めたものを、他の分析に流用しません

作業の便宜でローカルに落としたファイルが、いちばん漏れやすいです。

まとめ

• 会員IDとアドレスは1対1ではありません。抑止の主キーはアドレス
• 過去のアドレスがバウンスしただけなら、会員は停止しません
• 正規化は小文字化・トリム・全角半角までにします。+ やドットは除去しません
• バウンス記録には必ず日時を持たせます。解除判断の基準になります
• 統合は、棚卸し → 生のまま集約 → 正規化 → 種別の統一 → アドレス単位に集約 → ルール適用の順
• 苦情による停止は解除しません
• バウンスリストは個人情報です。手元に落とさないでください

止めるべきものを止めるのと同じくらい、止めなくてよいものを止めないことが効きます。後者は誰も気づかないまま、機会損失として静かに積み上がります。