Using SQL to combine Bounces, Opens, and Clicks into one DE

Question

I currently have two SQLs pushing data into one DE. One populates Opens, and the other populates Clicks. I then combined the SQLs to an automation with the last step being to enter a journey.

The problem I'm facing is when I set the SQLs to "update" instead of "overwrite", the automation throws an error saying "Query failed during execution. Error: Violation of PRIMARY KEY constraint. Cannot insert duplicate key in object 'xxx'".

My ideal situation would be to have one SQL combining all Send, Bounce, Open, Click, and Unsubscribe data. I've searched across Stackexchange for similar questions, but only one appeared that hasn't been answered.

Here are my SQL queries separated:

OPENS

select
o.subscriberKey as 'ContactID',
ja.ActivityName as 'EmailName',
o.EventDate as 'OpenDate'
from [_open] as o
join [_JourneyActivity] as ja 
on o.TriggererSendDefinitionObjectID = ja.JourneyActivityObjectID
join [_Journey] as j
on ja.VersionID = j.VersionID
where ja.ActivityType in ('EMAIL','EMAILV2')
and j.JourneyName = '19_05_landlord_reengagement_chosts'

CLICKS

select
c.subscriberKey as 'ContactID',
ja.ActivityName as 'EmailName',
c.EventDate as 'ClickDate',
c.LinkName as 'LinkName'
from [_click] as c
join [_JourneyActivity] as ja 
on c.TriggererSendDefinitionObjectID = ja.JourneyActivityObjectID
join [_Journey] as j
on ja.VersionID = j.VersionID
where ja.ActivityType in ('EMAIL','EMAILV2')
and j.JourneyName = '19_05_landlord_reengagement_chosts'

I've tried combining them into the following (based on this question), but I'm getting syntax errors:

SELECT
(
select
c.subscriberKey as 'ContactID',
ja.ActivityName as 'EmailName',
c.EventDate as 'ClickDate',
c.LinkName as 'LinkName'
from [_click] as c,
)
(
select
o.subscriberKey as 'ContactID',
ja.ActivityName as 'EmailName',
o.EventDate as 'OpenDate'
from [_open] as o
)
join [_JourneyActivity] as ja 
on o.TriggererSendDefinitionObjectID = ja.JourneyActivityObjectID
join [_Journey] as j
on ja.VersionID = j.VersionID
where ja.ActivityType in ('EMAIL','EMAILV2')
and j.JourneyName = '19_05_landlord_reengagement_chosts'

THE OBJECTIVE:

• Populate a DE with all email activity data relating to a journey.
• Use that DE to trigger another journey that updates SF Campaign Member Statuses with Sent, Bounced, Opened, Clicked, Unsub + other Contact data in Salesforce.
• Create a process that is simple and scalable, so I don't need to create too many things to achieve this basic campaign requirement.

As a side note, I'm curious to know how other B2B SFMC users handle this in their own campaigns.

Any help is greatly appreciated. Thank you in advance.

Answer 1

You can't use the ContactID as a primary key because the Data Views you are trying to query can contain multiple entries per ContactID. For example, a contact that clicked several times on an email would appear multiple times in _Click data view.

Using the the ContactID and EmailName would not solve the primary violation either for the same reason.

What I would do in this case is individual query for _Click and _Open.

An example that gets the contact's latest click in an email based on the event date:

SELECT 

SubscriberKey

FROM
(SELECT c.SubscriberKey, eventdate,
        ROW_NUMBER() OVER (PARTITION BY c.SubscriberKey ORDER BY eventdate DESC) 
        AS Row
FROM _Click c
INNER JOIN (
    SELECT 
    JobID
    ,EmailName
    FROM _Job 
) j ON j.JobID = c.JobID
INNER JOIN YOUR_DE COLL ON COLL.SubscriberKey= c.SubscriberKey
WHERE 
(EmailName = 'YOUR EMAIL NAME' OR EmailName = 'YOUR EMAIL NAME2')
 ) as s
 WHERE row=1

Hope that answers your question. Feel free to ask if you need more explanation of the query.