Querying Recipient Delivery Logs based on eventDate

[TylerQu1]

Hello,

I have a workflow that runs every 30min and collects the total # of recipients for a delivery. Currently, it queries the broadLogRcp table for recipients with an eventDate on or after the last time the workflow ran, with the purpose of not counting recipients for a delivery twice.

However, whenever a recipients' status changes from 'Pending' to 'Sent', etc., their eventDate timestamp updates. This can result in double counting, as the workflow will count the recipient once when their status is 'Pending' and then again if their status changes to 'Sent' some time after the workflow has run.

To fix this, I've changed the query to exclude recipients with a 'Pending' status. My question is - are there any other recipient statuses I need to exclude to prevent counting more than once? Or is 'Pending' the only status where the eventDate is not final?

Thanks,

Tyler

[ParthaSarathy]

Hi @TylerQu1 ,

For this case you can use an Incremental Query.

An incremental query lets you periodically select a target based on a criterion, while excluding the people already targeted for this criterion.

[TylerQu1]

Hi @ParthaSarathy  is there any estimates regarding the data this will require? E.g., if we are processing millions records daily with a 30d incremental query history.

Can we expect all broadlogRCP entries eventDate values to be final for a delivery after the 'delivery duration' validity period ends?

[TylerQu1]

also, what is the name of the default history SQL table?

[ParthaSarathy]

@TylerQu1 , As per the Adobe documentation, only information available is that the population already targeted is stored in the memory by workflow instance.

On the other hand, For each run of the workflow you can store the primary key of broadLog in a list using List update activity. And in parallel use a read list and call the same list which you store already executed broadLog records. Now use exclusion activity to exclude already executed records.

[TylerQu1]

Hi @ParthaSarathy ,

Thank you for your help. Back to the original question, in broadLogRcp "status" column, can the eventDate be considered final/will not change for any statuses != "pending" or "prepared"?

Thanks

Tyler

[ParthaSarathy]

@TylerQu1 , Event date will get updated each time for the delivery retries for a particular recipient but the primary key will remains constant.