0

Based on who clicks a certain element in a mail, I'd like to add these subscribers to a journey. So, I have the following in place:

This query populates a data extension hourly (on the full hour)

SELECT c.SubscriberKey, m.EmailAddress, m.Country, m.Language, m.Gender, m.FirstName, m.LastName, m.Store, m.StoreId, c.LinkName
FROM _Click c
JOIN MasterSubscribers m on c.SubscriberKey = m.SubscriberKey
WHERE c.LinkName = 'WeLove_BornholmTable' AND m.Consent_status = 'Confirmed' AND c.EventDate > dateadd(hh,-24,getdate())

The journey is using that data extension for running hourly (on the 30th minute of the hour) The journey consists of both sends and waiting steps meaning that the journey will last 19 days (the last 12 days are pure waiting) which is the amount of time I find apropriate for having the same content sent again.

So, 1) The query is overwriting the data extension 2) The journey has Contact entry - re-entry only after exiting 3) The journey has Contact evaluation - evaluate new records only

Would you suggest seetings differently for 1), 2) or 3) and why? Setting up a journey like this is quite new to me and any advice would really, really come in handy. Thank you!

3

I would approach this more or less as you describe, with one little adjustment. My recommendation would be to put data retention policy on your entry data extension, with length equaling your "quarantine period" - e.g. 19 days:

enter image description here

It will require a simple addition to your query, ensuring the contact you are about to inject into journey don't exist in the data extension. It is covered by the two last lines below:

SELECT 
c.SubscriberKey, 
m.EmailAddress, 
m.Country, 
m.Language, 
m.Gender, 
m.FirstName, 
m.LastName, 
m.Store, 
m.StoreId, 
c.LinkName
FROM _Click c
JOIN 
MasterSubscribers m 
on c.SubscriberKey = m.SubscriberKey
WHERE 
c.LinkName = 'WeLove_BornholmTable' 
AND m.Consent_status = 'Confirmed' 
AND c.EventDate > dateadd(hh,-24,getdate())
AND c.SubscriberKey not in 
(select j.subscriberkey from journeyentry j)

As records remain in data extension for 19 days after initially being added to the journey, this query will ensure that a contact will first be inserted after 19 days. Managing your re-entry settings in the journey itself does not make much difference - as this setup ensures that a contact can enter more than once, and cannot re-enter before exiting (+ whatever is set in your data retention settings).

When it comes to evaluating all records vs. evaluating new records only: Each record in this data extension has an internal numerical ID, which is automatically incremented for each record added (and is not visible in the UI). You should be aware of what is called a high watermark. It is a value which is updated each time a journey, being based on the data extension above, is running. It designates the ID of the last row processed. Hence when new records are added to the data extension, they get a higher ID, and are evaluated for the journey.

If you update an existing record (e.g. when using Contact Key as primary key) this will not make the record be re-evaluated, as the internal ID is not updated. Hence only completely new records are evaluated. This is why using data retention settings is a clever solution here.

A complete overwrite of the data extension resets the high watermark to 0, which means all the records are evaluated - regardless the setting in journey builder.

| improve this answer | |
  • I've added the retention policy, and thank you, it really makes sense! I've also added the last part of the query you suggested me, but having done so, I need to change the data action to update. And now, when running the query, I get this error: 'Violation of PRIMARY KEY constraint. Cannot insert duplicate key.' Any idea why these changes are causing this error? Thank you – user40290 Mar 23 at 17:35
  • I suspect you return multiple rows with same SubscriberKey, in cases where the same subscriber has multiple clicks. You would like to GROUP BY SubscriberKey, to deduplicate your results. It will probably require you to wrap most of the other fields your query returns with MAX(). – Lukas Lunow Mar 23 at 17:55

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.