how to make sure update contact step in journey builder honors primary key?

Question

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?

Answer 1

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.

Answer 2

You are running into a limitation of "Update Contact". It will only Upsert, and it will always use SubscriberKey as Primary Key. As far as today, there are no really great out of the box options. There are ways to achieve this, but either custom coded, paid for or hacky.

So long story short as of early 2021 is: There is no sustainable out of the box option, but you can get somewhere with custom code or money. Unfortunately, this is too long for a comment so I'm writing an answer.

custom coded: This makes a great usecase for a custom journey builder activity (with data extension choice, insert / update / upsert / delete options as well as free primary key definition) to set up a tracking infrastructure for your journeys.

paid for: There should be the option of getting a Custom Data View set up by Salesforce as alluded to here: Subscribers currently in a Journey

hacky (as hell): Read this as a case for "The situation seems dire. Maybe I should really consider option 1 or 2 despite involving some legwork". You are intending to use this as a cornerstone of your tracking infrastructure, so if I were your client, I probably wouldn't accept it. I'm just gonna outline the basics to make the point that options 1 and 2 maybe aren't so bad after all.

"Exclusion script" can act as a script container. Instead of an "Update Contact", you could use a "Send email" activity where you write some fancy exclusion AMPscript that satisfies the constraints of the functionality, updates a DE and stops the sendout. Fun to think about, but don't use this in production. There also are warnings against this in terms of scale: https://ampscript.guide/exclusion-scripts-syntax/

NOTE: You should only use exclusion scripts for exception handling, not for segmenting or excluding a large number of subscribers.

Answer 3

Another work around to this would be to add a column for each journey in one DE, say add a column for the journey name as a date data type and log the timestamp entered. Since each journey would be updating a different field, existing journey fields would get overwritten.