0

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

1
  • 1
    joining _sent and _unsub on just jobid is an issue this is a N:N you should join on jobid/listid/batchid/subid. the only time you should join on jobid is when joining to _jobs which would be a 1:N
    – EazyE
    Commented Sep 1, 2021 at 12:57

1 Answer 1

0

Your queries are complex and not ideal as pointed out but are not the curl-pit.

System Data Views are built-in backend Data Extensions that can be queried directly. Because they are holding real-time data, you will find that read requests from your SQL can become deadlocked for write requests from other processes such as sent.

I would not recommend to query these type of Data Extensions directly but instead create a copy and use them instead.

You could use WITH (NOLOCK) which is the equivalent of using READ UNCOMMITED as a transaction isolation level. You stand the risk of reading an uncommitted row that is subsequently rolled back, i.e. data that never made it into the database. While it can prevent your read queries being deadlocked by other operations, it comes with the risk of wrong data.

If you do not want to make a copy (which I would recommend) you can try this:

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 [_Sent] s WITH (NOLOCK)

JOIN [_Unsubscribe] u WITH (NOLOCK)
    ON u.JobID = s.JobID

JOIN [_JourneyActivity] ja WITH (NOLOCK)
    on ja.TriggererSendDefinitionObjectID = s.JourneyActivityObjectID

JOIN [_Journey] j WITH (NOLOCK)
    ON j.VersionID = ja.VersionID

JOIN ENT._Subscribers su WITH (NOLOCK)
    ON su.SubscriberID = s.SubscriberID

WHERE 
    s.EventDate > DATEADD(dd,-19,GETDATE())
3
  • 2
    As a note, NOLOCK is not necessary for SFMC (help.salesforce.com/s/…) query activities use SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED. This syntax is equivalent to WITH (NOLOCK). Using the NOLOCK query hint is unnecessary. Commented Sep 1, 2021 at 14:45
  • 1
    Your recommendation to use 'local' versions is usually best practice if possible though. This is because your 'local' DE is indexable and the Data View is not. This can greatly affect performance and increase timeout issues. Commented Sep 1, 2021 at 14:48
  • 1
    Thank you @Gortonington I was not aware that SFMC is using SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED by default - only option would be to reduce the query complexity or use a indexable table
    – shd.lux
    Commented Sep 1, 2021 at 15:48

You must log in to answer this question.

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