0

in our Existing System we have 31M Contacts. NOw we wanted to Delete The Contacts who did not received any of communication from our end. like email or sms or mobile push. And we are trying to query the contacts <= 06/30/2022.

i dont know how to do it with mobile push but i queried for email, SMS

code is below

> SELECT s.DateUndeliverable as [sub_DateUndeliverable], e.EventDate as [sent_Evdate], sms.ActionDateTime, 

s.SubscriberKey as [sub_subkey],e.SubscriberKey as [sent_subkey],sms.SubscriberKey as [sms_subkey], 

s.SubscriberID,s.Status,s.EmailAddress,s.DateJoined,s.DateUndeliverable, 

s.DateUnsubscribed,s.BounceCount,s.SubscriberType,s.Locale,s.Domain  

FROM _Subscribers s 

LEFT JOIN _Sent e 

    ON s.SubscriberKey = e.SubscriberKey 

    AND e.EventDate  <= '2022/06/30' 

LEFT JOIN _SMSMessageTracking sms 

    ON s.SubscriberKey = sms.SubscriberKey 

    AND sms.ActionDateTime  <= '2022/06/30' 

WHERE e.SubscriberKey IS NULL 

  AND sms.SubscriberKey IS NULL

as per my understanding _Sent, _SMSMessageTracking will provide the 6 months of data.

Please try to help me the best Approach ASAP and Correct me the Query with email/sms/mobile push

Thank you

Improve this question

1 Answer 1

Reset to default
0

I would suggest you run the contacts without channel extract

This will list all contacts who have not been added to a messaging channel

Improve this answer
2
  • Hi @eazyE Can you please Brief on this, because iam new to SFMC, SQL. Please help me out from this :( >> also i need to query the Contacts with channels, but not receive any one (email/sms/mobile push) more than 2 years.
    – Raj2302
    Commented Oct 18 at 13:58
  • please Reply with the approach
    – Raj2302
    Commented Oct 21 at 9:48

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .