1

We collect unsubscribe survey responses and store them in a DE. I want to run a query to combine the data from this DE with the Job and Sent data views to get a complete picture of what email the survey response relates to.

I want to store the results of the query in a DE with the following primary key combination:

  • SubscriberKey
  • JobID
  • BatchID
  • ListID
  • CreatedDate

I am trying the run the below query in order to achieve this. The query is of the type UPDATE and my destination DE is currently empty:

SELECT
u.SubscriberKey
, u.Preferences__c AS PreferencesID
, u.JobID
, u.BatchID
, u.ListID
, u.Date_Created AS DateCreated
, u.Unsubscribe_Reason__c AS UnsubscribeReason
, u.Other_Feedback__c AS OtherFeedback
, j.EmailName
, s.EventDate AS SendDateTime
, j.FromName
, j.FromEmail
, j.AccountID AS MID

FROM
[Unsubscribe Survey] u
LEFT JOIN _Sent s
ON u.JobID = s.JobID AND u.BatchID = s.BatchID AND u.ListID = s.ListID
LEFT JOIN _Job j
ON u.JobID = j.JobID

However, I keep getting a primary key violation error. But here is the thing - in my source DE ([Unsubscribe Survey]) I only have 8 records at the moment and I can guarantee they are unique. I have triple checked this in excel to make sure my eyes are not playing tricks. Even just the SubscriberKeys are completely unique.

However again, if I change it to SELECT DISTINCT, it works and I get all 8 records in the destination DE, which I don't understand.

My understanding was that primary key violations are caused when duplicate rows are returned in a query, but that a "duplicate" is considered a combination of all primary key fields in the destination DE? So I would not have thought the using DISTINCT would make any difference in this case?

Can someone please help me understand what's going on here? Thanks

Improve this question

1 Answer 1

Reset to default
4

You aren't joining to _Sent using the SubscriberKey, so for each row in your Unsubscribe Survey table, you'll be getting a row for everyone sent the email. The join should look like this:

[Unsubscribe Survey] u INNER JOIN
_Sent s
    ON  u.JobID = s.JobID
        AND u.ListID = s.ListID
        AND u.BatchID = s.BatchID
        AND u.SubscriberKey = s.SubscriberKey
Improve this answer
2
  • Thanks Macca. That is a very good point! I notice you changed it to an INNER JOIN. Would a LEFT JOIN still have worked in this situation?
    – Ben
    Commented Oct 9, 2020 at 0:31
  • 1
    INNER JOIN is more efficient than an outer join. It's down to the semantics of what you're trying to get. If you have unsubscribes where there's no corresponding record in _Job or _Sent, you'll get records with nulls in the fields from these tables. This could conceivably happen for older sends where the data view data's no longer available. It sounds like the report you're after is email focused... "Which emails are generating unsubscribes?". In this case, having a bunch of records returned with no email associated isn't much good and the query will run more slowly due to the outer join.
    – Macca
    Commented Oct 9, 2020 at 5:48

You must log in to answer this question.

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