marketing cloud journey - check field value to send email only if updated since last run

Question

we have set up journey that updates customers of their product's production and shipment status. the source data extension for the journey has a field called "orderstatus" that gets updated by status code values. each code represents a step such as 1 = production complete , 2 = being processed for shipping.

the journey is intended to send emails at every ste and is scheduled to run daily.

is there a way for the journey as part of its daily run to check the status field and send the appropriate email. and if the value hasnt changed since last run to do nothing.

this way status email isnt sent out to the same person every time it meets criteria. only if it is changed

Answer 1

Utilize the Update Contact activity in the Journey after each send to write to a separate data extension (UpdateContactDE) the subscriber ID and "Status_Sent" field.

Update the query that feeds the entry event with:

FROM
JOIN UpdateContactDE AS UpdateContactDE ON StatusDE.ID = UpdateContactDE.ID

WHERE
StatusDE.OrderStatus > UpdateContactDE.Status_Sent
 

Answer 2

I see two additional solutions here:

1. You are most probably using SQL to populate the journey source DE on a daily basis with new data. You could modify the SQL so that it only picks the subscribers whose status has changed since the last run. That way in your DE you will only have subscribers who should get an email on that day.

2. Second option would be to add a column, OldStatus, to your journey source DE and again modify the SQL used for populating the data to write the old status to the column along with the new status in your current Status column. Then, by comparing the two, you could determine if the status changed and if the person should get an email. You could, for example, use this info in an exclusion script:

iif(attributevalue(OldStatus) == attributevalue(Status), 1, 0)