Updating click and open data in salesforce from journeys

Question

I have a journey in marketing cloud and few email steps in them each one week apart. I would like to check the open and clicks of email sent before sending the next email so that the data can be sent to a custom object in sales cloud. The problem I have now is figuring out the process of finding the clicks and opens of a user in journey through scripts or query. It should be part of the journey. Any help would be great . Thank you

Answer 1

You can do either of the 2 below approach:

• You can create an engagement split after some wait activity and check if they have opened and another engagement split for click. If the contact enter in these path, add a salesforce custom object activity to update the record in salesforce and send the next email.
• You can use the approach mentioned by @Gortonington. Add a scheduled query (like 1 hour interval each) check against the data view for each email for both open and click. Update the same data extension. Post that add a decision split if opened or Clicked and add a salesforce custom object activity.

Sample Query to find Open:

SELECT a.SubscriberKey,
Opened = 1
FROM [myDE] a
WHERE EXISTS (
  SELECT TOP 1 o.SubscriberKey from [_Open] o
  WHERE o.SubscriberKey = a.SubscriberKey
  AND o.TriggererSendDefinitionObjectID = {{"Interaction ID"}}
  AND o.EventDate > (CAST(GETDATE() - 1 as Date))
)

Sample Query to find Click:

SELECT a.SubscriberKey,
Clicked = 1
FROM [myDE] a
WHERE EXISTS (
  SELECT TOP 1 o.SubscriberKey from [_Click] o
  WHERE o.SubscriberKey = a.SubscriberKey
  AND o.TriggererSendDefinitionObjectID = {{"Interaction ID"}}
  AND o.EventDate > (CAST(GETDATE() - 1 as Date))
)

Let me know if this helps!