0

I have data that is going to drop weekly but this will need to fire to different content. Week1, then Week2, then Week1 again...

My solution to this was to utilize the recurring schedule and set it to repeat weekly, every 2nd week on a Tuesday. This would be for Week1.

Then next week, duplicate that journey and call it Week2.

The target data extension will be the same for Week1 and Week2.

If I run a clear to clear out the data extension after the journey completes, Can I safely assume that the alternate journey won't queue the data since it wont be set to run until the following week?

1 Answer 1

1

I would create one journey and use decision split to send different versions

  1. Lets say you have dataExtensionA to store source data that get updated daily/weekly
  2. Create a dataExtensionB and copy the fields from dataExtensionA but add one more text field called emailVersion
  3. Create an automation job, first step add a Query activity that copy new data from dataExtensionA to dataExtensionB and calculate emailVersion base on the weeks elapsed since start date
SELECT a.*, 
CASE WHEN (DATEDIFF(week, '2023/03/12', GETDATE()) % 2) = 0 THEN 'week1' ELSE 'week2'
END AS 'emailVersion'
FROM dataExtensionA a
WHERE a.contactID NOT IN (select contactID from dataExtensionB)

This query populate new records from dataExtensionA into dataExtensionB and calculate which email version should be used, it calculate elapsed weeks since start date, then mod by 2, if remaining is 0 which means even weeks; if remaining is 1 means even weeks. replace '2023/03/12' with your start date.

  1. Now you can use dataExtensionB for journey entry source DE, create a journey use this DE as entry source, create a decision split, check if journeyData attribute emailVersion field is week1, flow into week 1 path and send week 1 email, otherwise flow into another path to send week 2 email.
  2. Setup the journey to be triggered by automation job you created in step #3, it should automatically add a journey trigger activity in that automation job
  3. schedule the automation job to run every week on Tuesday.

I feel this approach is more easy to maintain, and you can reuse the same DE for entry source and you only need one journey to manage all email versions and paths. If in the future you need to send more email variants, you just need to update the SQL query that populate into emailVersion field and add more decision splits.


Updated SQL for 4x email versions, rotate every 2 weeks, to build this logic you need to mod by 8, if remaining is 0 or 1, that is "week1" email, if it is 2 or 3, that is "week2" email etc....

SELECT a.*, 
CASE WHEN (DATEDIFF(week, '2023/03/12', GETDATE()) % 8) = 0 OR (DATEDIFF(week, '2023/03/12', GETDATE()) % 8) = 1 THEN 'week1' 
WHEN (DATEDIFF(week, '2023/03/12', GETDATE()) % 8) = 2 OR (DATEDIFF(week, '2023/03/12', GETDATE()) % 8) = 3 THEN 'week2' 
WHEN (DATEDIFF(week, '2023/03/12', GETDATE()) % 8) = 4 OR (DATEDIFF(week, '2023/03/12', GETDATE()) % 8) = 5 THEN 'week3' 
WHEN (DATEDIFF(week, '2023/03/12', GETDATE()) % 8) = 6 OR (DATEDIFF(week, '2023/03/12', GETDATE()) % 8) = 7 THEN 'week4' 
ELSE 'week1'
END AS 'emailVersion'
FROM dataExtensionA a
WHERE a.contactID NOT IN (select contactID from dataExtensionB)
2
  • Thanks, didn't think about that approach. I have found out now that there are 4 versions that go out 2 weeks apart and after the 4th mail, 2 weeks then the cycle repeats from week1. , so need to figure out the sql to do that.
    – lyslexic
    Apr 19, 2023 at 13:51
  • @lyslexic please see updated sql for 4 versions scenario
    – Bo Hu
    Apr 19, 2023 at 22:57

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .