I'm trying to get all the unsubs in MC with Unsub Data view, the query that I'm using :
select
j.JourneyName,
ja.ActivityName as 'EmailName',
su.EmailAddress,
u.SubscriberKey as 'ContactKey',
u.SubscriberID as 'ContactID' ,
FORMAT(u.EventDate
AT TIME ZONE 'Central Standard Time'
AT TIME ZONE 'E. Europe Standard Time', 'yyyy-MM-dd HH:mm:ss') as 'DateUnsubscribed',
u.JobID,
u.BatchID
from [_Unsubscribe] u
join [_Sent] s
on u.JobID = s.JobID
join [_JourneyActivity] ja
on s.TriggererSendDefinitionObjectID = ja.JourneyActivityObjectID
join [_Journey] j
on ja.VersionID = j.VersionID
join ENT._Subscribers su
on s.SubscriberID = su.SubscriberID
where s.EventDate > dateadd(dd,-19,getdate())
In fact, this query doesn't work because it's timed out due to its complexity. I decided to split it like this :
select
s.TriggererSendDefinitionObjectID,
s.SubscriberID,
u.SubscriberKey as 'ContactKey',
u.SubscriberID as 'ContactID' ,
FORMAT(u.EventDate
AT TIME ZONE 'Central Standard Time'
AT TIME ZONE 'E. Europe Standard Time', 'yyyy-MM-dd HH:mm:ss') as 'DateUnsubscribed',
u.JobID,
u.BatchID
from [_Unsubscribe] u
join [_Sent] s
on u.JobID = s.JobID
where s.EventDate > dateadd(dd,-19,getdate())"
=> To ABC Data extension
select
j.JourneyName,
ja.ActivityName as 'EmailName',
su.EmailAddress,
t.ContactKey,
t.ContactID ,
t.DateUnsubscribed,
t.JobID,
t.BatchID
from [ABC] t
join [_JourneyActivity] ja
on t.TriggererSendDefinitionObjectID = ja.JourneyActivityObjectID
join [_Journey] j
on ja.VersionID = j.VersionID
join ENT._Subscribers su
on t.SubscriberID = su.SubscriberID
When I try the queries above, the first one is timed out. I tried an other way of split :
select
j.JourneyName,
ja.ActivityName as EmailName,
s.EventDate as SendTime,
su.EmailAddress,
su.SubscriberKey as ContactKey,
s.SubscriberID as ContactID ,
s.JobID,
s.BatchID
from [_Sent] s
join [_JourneyActivity] ja
on s.TriggererSendDefinitionObjectID = ja.JourneyActivityObjectID
join [_Journey] j
on ja.VersionID = j.VersionID
join ent._Subscribers su
on s.SubscriberID = su.SubscriberID
where
s.EventDate > dateadd(dd,-19,getdate())
=> to ABC data extension
select
t.JourneyName,
t.ActivityName as EmailName,
FORMAT(u.EventDate
AT TIME ZONE 'Central Standard Time'
AT TIME ZONE 'E. Europe Standard Time', 'yyyy-MM-dd HH:mm:ss') as 'DateUnsubscribed',
t.EmailAddress,
t.SubscriberKey as ContactKey,
t.SubscriberID as ContactID ,
t.JobID,
t.BatchID
from [abc] t
join [_Unsubscribe] u
on u.JobID = t.JobID
When I run the queries above, the first one is running successfully (2 million rows) but the second is timed out! This is so strange.
Can you help me with other solutions please? Thank you in advance!
Ilyass