2

I'm working on a setup of individual journeys triggered by clicks on certain furniture aliases.

At the moment I have journeys starting when either of these aliases; 'WeLove_AlbaSidetable', 'WeLove_BornholmTable' or 'WeLove_AarhusChair' have clicks registrered.

Ie. a click on the alias 'WeLove_AlbaSidetable' starts the journey presenting that side table in depth, a click on the alias 'WeLove_AarhusChair' starts the journey presenting that chair in depth, etc.

Typically, I'm adding subscribers from the click data view into the data extension for the journey using this query:

select 
  m.SubscriberKey
, m.EmailAddress
, m.Country
, m.Language
, m.Gender
, m.FirstName
, m.LastName
, m.Store
, m.StoreId
, 'WeLove_BornholmTable' LinkName
from MasterSubscribers m 
where 
m.Consent_status = 'Confirmed' 
and exists (

  select top 1 c.*
  from _Click c
  where c.EventDate > convert(date, getDate()-1)
  and c.subscriberkey = m.subscriberkey
  and c.LinkName = 'WeLove_BornholmTable'
  and c.isunique = 1

)
and not exists (
    select top 1 j.*
    from JourneyDE_WeLove_BornholmTable j
    where j.subscriberkey = m.subscriberkey
)

This query is populating the JourneyDE_WeLove_BornholmTable data extension.

select 
  m.SubscriberKey
, m.EmailAddress
, m.Country
, m.Language
, m.Gender
, m.FirstName
, m.LastName
, m.Store
, m.StoreId
, 'WeLove_AlbaSidetable' LinkName
from MasterSubscribers m 
where 
m.Consent_status = 'Confirmed' 
and exists (

  select top 1 c.*
  from _Click c
  where c.EventDate > convert(date, getDate()-1)
  and c.subscriberkey = m.subscriberkey
  and c.LinkName = 'WeLove_AlbaSidetable'
  and c.isunique = 1

)
and not exists (
    select top 1 j.*
    from JourneyDE_WeLove_AlbaSidetable j
    where j.subscriberkey = m.subscriberkey
)

This query is populating the JourneyDE_WeLove_AlbaSidetable data extension.

select 
  m.SubscriberKey
, m.EmailAddress
, m.Country
, m.Language
, m.Gender
, m.FirstName
, m.LastName
, m.Store
, m.StoreId
, 'WeLove_AarhusChair' AS LinkName
from MasterSubscribers m 
where 
m.Consent_status = 'Confirmed' 
and exists (

  select top 1 c.*
  from _Click c
  where c.EventDate > convert(date, getDate()-1)
  and c.subscriberkey = m.subscriberkey
  and c.LinkName = 'WeLove_AarhusChair'
  and c.isunique = 1

)
and not exists (
    select top 1 j.*
    from JourneyDE_WeLove_AarhusChair j
    where j.subscriberkey = m.subscriberkey
)

This query is populating the JourneyDE_WeLove_AarhusChair data extension.

But while testing I've found that my ie. test email addresses with SubscriberKey: 0031t00000wPVETAA4 is only added to the 'WeLove_BornholmTable' journey data extension, and not the others. The data extensions for the journeys are populated by individual automations running smoothly btw.

So, I did some further investigation in Query Studio trying to see if my missing SubscriberKey was available in the click data view.

SELECT c.SubscriberKey
FROM _Click c
WHERE c.EventDate > convert(date, getDate()-1)
AND c.LinkName = 'WeLove_AlbaSidetable'

Result (among others): 0031t00000wPVETAA4

SELECT c.SubscriberKey
FROM _Click c
WHERE c.EventDate > convert(date, getDate()-1)
AND c.LinkName = 'WeLove_BornholmTable'

Result (among others): 0031t00000wPVETAA4

SELECT c.SubscriberKey
FROM _Click c
WHERE c.EventDate > convert(date, getDate()-1)
AND c.LinkName = 'WeLove_AarhusChair'

Result (among others): 0031t00000wPVETAA4

So, to keep this question short and focusing on one problem only:

How do I need to change my queries to make sure subscribers are added to all journeys and not just some?

Thank you

  • Did you change the DE in the last not exists subquery to match your new Journey DE? – Adam Spriggs Mar 29 at 12:46
  • Yes, edited the description above to include all 3 queries. – FelixSanchez Mar 29 at 13:48
1

I believe it is because of the isUnique conditioal inside your click dataview part. Only the first click per job is unique all others are not. If you remove that part it should work as expected.

| improve this answer | |
  • Actually the isUnique conditional was to make sure I would not end up with my query erroring with the 'Violation of PRIMARY KEY constraint. Cannot insert duplicate key.' error. So, I sent the the mail with the alias to myself again, clicked the link 4 times and ran the query. I ended up in the data extension. Hooray! Seems like the same subscriber still can have multiple clicks and the query can still run without erroring in this new version. Thank you, this is super helpful! – FelixSanchez Mar 30 at 6:43
  • Yeah, this is because you are not joining (also your subqueries have TOP 1 which limits the results to 1). You are only grabbing the single record in your Master DE. – Gortonington Mar 30 at 14:03

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.