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.