[SalesForce] how to make sure update contact step in journey builder honors primary key

I am trying to create one Data Extension that can be used to log all contacts going into multiple journeys and then use update contact event within journey to update row when a contact exist journey.

In my data extension, my primary key is the combination of: JourneyName + EmailAddress (subscriber key) + AddDate (considering a contact can enter multiple, the combination of my primary keys means, the combined values will be unique).

Based on some testing, it looks like once I start adding rows (records) to Data Extensions then use Update Contact for making update, the update contact step doesn’t respect the primary keys of your Data Extension. Update Contact will update any row that has a Subscriber Key match, even if the primary key is Subscriber Key plus something else.

Any suggestions?

UPDATE:

I was above to implement a solution based on this approach: https://sprignaturemoves.com/journey-logging-using-update-contact/

Overall, it works great. I did some modifications since I am looking to log multiple journeys. The solution that I was able to make it work is:

Each journey will have a dedicated DE to capture the contacts like this:

  • Journey_Builder_Status_Journey1
  • Journey_Builder_Status_Journey2

Each journey contacts are being added as a new row to the respective journey status DE and the automation sql script pushed those contacts to a single DE:

  • Journey_Builder_Log

*Subscriber key and EventDate being the primary key (as combined), the log is able to handle contacts from multiple journey along with contacts who are allowed to re-enter same journey since the EventDate will be different for them each time.

Question: In my case, I am not adding Journey_Builder_Status data extensions like Journey_Builder_Status_Journey1, Journey_Builder_Status_Journey2 to contact builder since adding multiple journey DEs is not feasible. But, without managing Journey_Builder_Status DE in contact builder, everything seems to be working fine. Can anyone please explain why Journey_Builder_Status would be needed in contact builder anyway? Is there any drawback that I am missing out?

Best Answer

Here's my workaround for the primary key issue.

TL;DR Use an hourly query to enforce the primary key handling.

Journey_Builder_Status Data Extension

The Update Contact activity writes to this Data Extension.

  • SubscriberKey, Text (254), Primary Key
  • Journey, Text (100), nullable
  • Event, Text (100), nullable
  • EventDate, Date, nullable, defaulted to the current date

Journey_Builder_Log Data Extension

This is the final destination of the Journey log data.

  • SubscriberKey, Text (254), Primary Key
  • EventDate, Date, Primary Key
  • Journey, Text (100), nullable
  • Event, Text (100), nullable

Journey_Builder_Log Query Activity

This sweeps rows from Journey_Builder_Status to Journey_Builder_Log.

select
  s.SubscriberKey
, s.Event
, s.EventDate
, s.Journey
from Journey_Builder_Status s
/* name: Journey_Builder_Log */
/* target: Journey_Builder_Log */
/* action: Update */

Journey_Builder_Log Automation

Runs hourly, ends never.

  • Step 1, Query Activity: Journey_Builder_Log

Once that’s scheduled and running, drag as many Update Contact Activities that you need to your Journey canvas for recording events. In those activities, specify these field values: Event, EventDate, and Journey.

NOTE: You'll have to do something special if you have journey steps that could occur more frequently than an hour or if you have multiple Journeys writing to the same log.

Related Topic