提取并删除3年以上无法投递及退订联系人的SQL语句验证

一、无法投递联系人查询 #

原查询语句 #

select SubscriberKey
from _Subscribers
where Status = 'held'

问题与修正 #

这个查询能筛选出状态为held（无法投递）的联系人，但缺少3年以上的时间筛选条件，会把所有历史上变成held状态的联系人都拉取出来，不符合你要删除3年以上数据的需求。

_Subscribers表中的DateHeld字段记录了联系人变为held状态的时间，补充时间过滤条件后的修正查询如下：

select SubscriberKey
from _Subscribers
where Status = 'held'
and DateHeld <= dateadd(d, -1095, getdate()) -- 筛选3年以上的无法投递记录

二、退订联系人查询 #

原查询语句 #

select j.JobID ,u.EventDate, s.EmailAddress, s.SubscriberKey
From _job j Join _Unsubscribe u 
on j.JobID = u.JobID
Join _Subscribers s 
on u.SubscriberID = s.SubscriberID
Where u.IsUnique = 1 And u.Eventdate > dateadd (d,-1095,getdate())

问题与修正 #

1. 时间条件逻辑错误：原查询用>筛选的是3年内的退订记录，而你需要的是3年以上的，应该改为<=。
2. 冗余字段（可选）：如果仅用于删除操作，JobID和EventDate不是必需字段，但如果要在DE中保留退订事件记录，可以保留。
3. 覆盖范围补充：这个查询仅覆盖通过邮件触发的退订事件，若要包含直接在列表中手动退订的联系人，还需要结合_Subscribers表中Status = 'Unsubscribed'且DateUnsubscribed <= dateadd(d, -1095, getdate())的记录（可用UNION ALL合并）。

修正后的基础查询（仅邮件退订事件）：

select 
  j.JobID,
  u.EventDate,
  s.EmailAddress,
  s.SubscriberKey
From _job j 
Join _Unsubscribe u on j.JobID = u.JobID
Join _Subscribers s on u.SubscriberID = s.SubscriberID
Where 
  u.IsUnique = 1 
  And u.Eventdate <= dateadd(d, -1095, getdate()) -- 修正时间条件为3年以上

如果要同时包含手动退订的联系人，可使用以下查询：

-- 邮件退订事件
select 
  s.SubscriberKey,
  s.EmailAddress,
  u.EventDate as UnsubscribeDate,
  'Email Unsubscribe' as UnsubscribeType
From _Unsubscribe u 
Join _Subscribers s on u.SubscriberID = s.SubscriberID
Where 
  u.IsUnique = 1 
  And u.Eventdate <= dateadd(d, -1095, getdate())

UNION ALL

-- 手动退订记录
select 
  SubscriberKey,
  EmailAddress,
  DateUnsubscribed as UnsubscribeDate,
  'Manual Unsubscribe' as UnsubscribeType
From _Subscribers
Where 
  Status = 'Unsubscribed'
  And DateUnsubscribed <= dateadd(d, -1095, getdate())

内容的提问来源于stack exchange，提问作者Hannan S