0

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.

  • 1
    Why not just have each individual query target the same DE and just use the action type of Update (assuming that SubscriberKey and 'EmailName' are the primary keys). This way you do not need to worry about the query timing out and it remains in a form that you know already works. This, once completed, will give you a single DE that holds all your info in it. – Gortonington May 28 '19 at 13:05
  • @Gortonington - I did try this, but it still serves up the error: "Query failed during execution. Error: Violation of PRIMARY KEY constraint. Cannot insert duplicate key in object 'xxx'" :( – Greg Beazley May 28 '19 at 14:00
  • That sounds like an issue with the primary keys you have defined in your DE and your query. Basically, your Query is telling the processor to 'add' a record, when according to the DE pkeys, it should be 'update' which is causing the error. What are your DE pkeys? – Gortonington May 28 '19 at 14:03
  • @Gortonington the DE pkey was originally ContactID. I updated it after your comment to make EmailName pkey as well, but I received the same error message. – Greg Beazley May 28 '19 at 14:05
  • Looking closer at your queries, I see a bunch of inconcistencies on Pkeys. You would need to better define what you want as primary keys, or store these as separate DEs. (E.g. LinkName appears to be one in Click, but doesn't exist in Open.) – Gortonington May 28 '19 at 15:35
0

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.

| improve this answer | |
  • Thanks @Rachid - what would you suggest the primary key be in this case? I need to contain all the data in one DE so that I can use it as a journey entry source. This journey's purpose is to update the Contact's Campaign Member Status in Salesforce and create Tasks based on clicks. Also, in your SQL example, is there a way to reference the Journey Name instead of listing each Email Name, like in my original SQL? – Greg Beazley May 30 '19 at 12:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.