I have some emails that has been sent to the end customers and I want to track their interactions with the email which I have to send it back to the Sales cloud (CRM).

Following are the steps:

Step 1) Sending an email.

Step 2) Tracking the email using SQL query automation and insert the data into a DE in Automation studio (Runs every 1 hour)

Step 3) Send the records created in the data extension to the Sales cloud (CRM). (Runs every 1 hour and picks only the new records from the data extension)

Problem: When I am trying to get the data using SQL in step 2, the data that has been retrieved (From _clicks) in before cycle is also being retrieved again which is leading to the duplicate in the records. If I opt for just updating the data in SQL query automation, I don't have any Primary key which distinguishes each record. When I go for overwrite, the whole records are being overwritten and in Step 3, the journey builder is picking up all the records in the data extension that has already been sent to Sales cloud (CRM). (As shown in the picture below)

enter image description here

My Questions:

  1. Is there any primary key that I am missing? Is there any unique data that I have for each _click?
  2. How do I get the data that has not been retrieved in the previous cycle of SQL query? In other words, how do I distinguish between the records that has already been retrieved and not retrieved.

PS: This is the SQL query that I am using currently.

j.VersionNumber AS JourneyVersion,
s.EventDate AS SentDate,
FROM _Sent s
LEFT JOIN _Job job ON s.JobID = job.JobID
LEFT JOIN _Open o ON s.JobID = o.JobID
AND s.ListID = o.ListID
AND s.BatchID = o.BatchID
AND s.SubscriberID = o.SubscriberID
LEFT JOIN _Click c ON s.JobID = c.JobID
AND s.ListID = c.ListID
AND s.BatchID = c.BatchID
AND s.SubscriberID = c.SubscriberID
LEFT JOIN _Bounce b ON s.JobID = b.JobID
AND s.ListID = b.ListID
AND s.BatchID = b.BatchID
AND s.SubscriberID = b.SubscriberID
LEFT JOIN _Unsubscribe u ON s.JobID = u.JobID
AND s.ListID = u.ListID
AND s.BatchID = u.BatchID
AND s.SubscriberID = u.SubscriberID
LEFT JOIN _JourneyActivity ja ON s.TriggererSendDefinitionObjectID = ja.JourneyActivityObjectID
LEFT JOIN _Journey j ON ja.VersionID = j.VersionID
LEFT JOIN _Subscribers sub ON s.SubscriberKey = sub.SubscriberKey
LEFT JOIN welcomeemail de
ON s.SubscriberKey = de.[CampaignMember:Contact:Id]
WHERE j.JourneyName = 'Email Campaign Template (Copy) '

1 Answer 1


There may be other better solutions, but this is how I would tackle it.

  • Create a boolean field called IsSentToCRMin your Journey entry DataExtension.
  • Keep the default value as FALSE
  • Add a UpdateContact activity after your Object activity (which u using to send data to CRM. In this update the value for IsSentToCRM = TRUE This helps you to distinguish your processed rows
  • Connect your Journey entry to Contact Model.
  • Now you apply a Filter Contacts in your journey entry to process only records with the value IsSentToCRM = FALSE from your Journey entry Data Extension.
  • You may have to change the update type to Update instead of Overwrite
  • You have to add PrimaryKey [but it depends on data view, pls setup composite PrimaryKeys such as SubscriberKey and JobId etc]

enter image description here

You must log in to answer this question.

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