3

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?

3 Answers 3

2

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.

10
  • Hi @Adam Spriggs thank you for sharing your workaround solution. I have been thinking about a solution along similar lines (what you got). One of the idea is to not use update contact step to add row in DE (since I believe, update contact is intended for updating a record, not adding). I am thinking about using automation that runs prior to journey entry criteria and within automation, there is a sql step that always append a record with values supplied by sql query in which sub key, journey name, entry date etc combined can be primary key and row can be added to same DE for multiple journeys
    – kuml2
    Feb 8, 2021 at 2:57
  • The above solution will take care of the journey log for keeping a track of who entered journeys. In order to capture - exit event for contacts, I am thinking about Journey_Builder_Status DE (similar to yours) but specific to each journey. When we capture the exit event, then have an automation run every hour that pulls exist event values and UPDATE the original DE where we added the row prior to entry criteria.
    – kuml2
    Feb 8, 2021 at 3:00
  • I am interested in hearing your thoughts about the approach I shared above^. In addition to that - In your comments above you mentioned that I will have to do something special if I have multiple journeys (which I will def have). What is that something "special", could you please elaborate?
    – kuml2
    Feb 8, 2021 at 3:02
  • One additional question - Assuming, multiple journey_status DE is the solution for managing contact status in multiple journeys. Is it a necessary step to add Journey_Builder_Status DE in contact builder? If there are multiple DEs like that, adding all in contact builder may not be a feasible solution. It looks like update contact step allows selecting DE and updating attribute without adding the DE to contact builder. Why we need to add it to contact builder then?
    – kuml2
    Feb 8, 2021 at 4:02
  • 2ND Question - how this DE status approach work for journeys where contact re-entry is allows?
    – kuml2
    Feb 8, 2021 at 4:40
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.

1
  • Hi @Jonas Lamberty thank you for the detailed answer. I wanted to give you an update that I was able to implement a solution that handles log for multiple journeys (as per my update in the question). I am curious what your thoughts are regarding the update solutions and if you have any feedback? Just making sure I am not overlooking anything.
    – kuml2
    Feb 8, 2021 at 14:24
0

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.

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.