0

I have SQL running from a Data extension where I have all marketing opted in contacts, now I need to add a field to the SQL with the device level Opt in and Out options as I will further use these in a journey, where customers who opted in will receive a push and those who opted out will get an email. I'm not sure how to write this query. I wanted to join it with _PushAddress data view table but it doesn't have a Subscriber key to match it with the Subscriber key. I could perhaps join it with a Filtered Mobile List but do I make it using 2 filters: OptedIn=2 and OptedIn=1? I'd appreciate any suggestions as to what source I can use to join with the current data extension. My SQL query looks like this for the time being:

SELECT a.Email a.Campaign a.Subscriberkey b.OptIn b.OptOut FROM DataExtension1 INNER JOIN ... on a.Subscriberkey=...

Improve this question

1 Answer 1

Reset to default
0

_PushAddress does not have a subscriberKey field, please check these two posts for complete schema

https://sfmarketing.cloud/2019/08/22/data-views-in-salesforce-marketing-cloud/ https://salesforce.stackexchange.com/a/174180/28187

I am not sure if _PushAddress._ContactID is the same as subscriberKey/contactkey, so you can try left join to check if you can get any matching records. by using left join you can include all email subscribers regardless whether they have a _Push optin status.

SELECT a.Email
,a.Campaign
,a.Subscriberkey
,b.[_OptInStatusID] as 'DeviceOptin'
FROM DataExtension1 a LEFT JOIN (_PushAddress) b 
on a.Subscriberkey=b.[_ContactID]
Improve this answer
4
  • Hi, thank you for your reply, If I use only OptInStatus as deviceOptIn, it will not include any OptedOut contacts in the data extension and I need them to send them an email.
    – Anar Men
    Apr 21 at 10:30
  • I guess in that case I can add a field called 'DeviceOptOut' as well to this SQL then
    – Anar Men
    Apr 21 at 10:39
  • SubscriberK and contactID cannot be matched, so I need to use a mobile Filtered List. Just not sure if I have add 2 filters to my list: opted in - true and opted out-true? So that means that I have to add both optIn and OptOut fields to my query which is not what I was asked to do, I need one field only .
    – Anar Men
    Apr 22 at 22:30
  • Mobile filtered list also doesn't have a Subscriber Key so I cannto match it...
    – Anar Men
    Apr 24 at 10:04

You must log in to answer this question.

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