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

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

作業としては「バウンスしたアドレスを集めて、配信停止にする」だけに見える。ただし会員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ではない。抑止の主キーはアドレス
• 過去のアドレスがバウンスしただけなら、会員は停止しない
• 正規化は小文字化・トリム・全角半角まで。+ やドットは除去しない
• バウンス記録には必ず日時を持たせる。解除判断の基準になる
• 統合は、棚卸し → 生のまま集約 → 正規化 → 種別の統一 → アドレス単位に集約 → ルール適用の順
• 苦情による停止は解除しない
• バウンスリストは個人情報。手元に落とさない

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