Understand linking of tracking and recipient log

[KD1]

Hi @ParthaSarathy 

1. In the Tracking Log (nms:trackingLogRcp), the relationship between the recipient and the tracking log appears as 1-1.

2. However, while analyzing the reports for a specific delivery, I noticed that a single recipient opened the email multiple times. For each of these opens, a unique tracking primary key seems to be generated for the same delivery. This behavior suggests a 1-many relationship between the recipient and tracking logs.

Could you please clarify:

Why does the tracking log schema imply a 1-1 relationship between recipients and tracking logs when the actions of a single recipient (e.g., multiple opens) generate multiple unique entries?

How does Adobe Campaign handle this relationship internally, and how does it ensure data consistency in reporting?

Thank you for your assistance. I look forward to your response.

[ParthaSarathy · Accepted solution]

Hi @KD1 ,

The relationship between tracking log schema and recipient is one to Many.

1 tracking log will be mapped with 1 recipient and 1 recipient may be mapped with multiple tracking logs.

Means that, lets say when a recipient_A opened a delivery, clicked a URL_1, again clicked another URL_2, then 3 rows will get created in tracking log for this recipient_A. Each individual record in tracking log will be mapped with recipient_A. And from recipient perspective, recipient_A will be linked with 3 records of tracking log.

You can check this link definition in tracking log schema by expanding recipient link as below,

[KD1]

Hi @ParthaSarathy 

Thank you for clearing the doubt.

Can you guide similarly for delivery log and recipient table, it will be really helpful.

[ParthaSarathy]

@KD1 , same with broadLogRcp and recipient table - 1 to Many.

One recipient can be targeted in multiple deliveries. So for each delivery depends on how many times a recipient received a delivery, that many rows will be created in delivery log schema.