0

I am attempting to pull in people who have unsubscribed from a Journey Builder Email Activity.

Is there anything in my query that will cause the following error in query studio?

Query failed during execution. Error: Conversion failed when converting the nvarchar value '[email protected]' to data type int.

SELECT
  s.SubscriberKey as EMAIL_ADDRESS
, s.jobid
, s.TriggererSendDefinitionObjectID
, sub.EmailAddress
, j.EmailName
, j2.JourneyName
, j2.VersionNumber
FROM _Sent s 
join _Job j on s.jobid = j.jobid
join _Subscribers sub on s.SubscriberKey = sub.SubscriberID
join _JourneyActivity j1 on j1.JourneyActivityObjectID = s.TriggererSendDefinitionObjectID 
join _Journey j2 on j2.VersionID = j1.VersionID
WHERE j.EmailName = 'EMAIL_NAME' 
AND j2.JourneyName = 'JOURNEY_NAME'
AND Status = 'Unsubscribed'
0

1 Answer 1

1

Try this:

SELECT
  s.SubscriberKey as EMAIL_ADDRESS
, s.jobid
, s.TriggererSendDefinitionObjectID
, sub.EmailAddress
, j.EmailName
, j2.JourneyName
, j2.VersionNumber
FROM _Sent s 
join _Job j on s.jobid = j.jobid
join _Subscribers sub on s.SubscriberKey = sub.SubscriberKey
join _JourneyActivity j1 on j1.JourneyActivityObjectID = s.TriggererSendDefinitionObjectID 
join _Journey j2 on j2.VersionID = j1.VersionID
WHERE j.EmailName = 'EMAIL_NAME' 
AND j2.JourneyName = 'JOURNEY_NAME'
AND sub.Status = 'Unsubscribed'

You had s.SubscriberKey = sub.SubscriberID while they are not the same IDs.

Also, sub. alias was missing in the Status field.

5
  • Thanks Zuzanna, that looks fine. My querying is system erroring. I think this is because I am querying multiple data views, so I will look breaking it down to multiple queries. Feb 16, 2023 at 13:25
  • What error does it give you now? I tested it in query studio and worked fine for me
    – zuzannamj
    Feb 16, 2023 at 16:52
  • The error is "Operand type clash: uniqueidentifier is incompatible with int". Normally, this type of error is associated with how one of the tables is designed. However, in this case we are pulling from data views. Feb 17, 2023 at 15:01
  • Have you checked if the data types in your target DE match the data types in the data views?
    – zuzannamj
    Feb 17, 2023 at 15:53
  • It seems to be working now! Nothing changed beside logging in and out of SFMC. Thanks as always :) Feb 20, 2023 at 9:44

You must log in to answer this question.

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