0

In my use-case, I need to define a primary key in the data view extracts from sent, open & click

I have tried different combination of fields as primary key but somehow getting duplicate values and query keeps on failing. Any recommendation on what is the ideal combination of field that should be unique and act as a primary key?

Here is my sent data view query:

SELECT AccountID,
OYBAccountID,
JobID,
ListID,
BatchID,
SubscriberID,
SubscriberKey,
EventDate,
Domain,
TriggererSendDefinitionObjectID,
TriggeredSendCustomerKey,
CONCAT(AccountID, OYBAccountID, SubscriberID, JobID, EventDate, BatchID, FORMAT (getdate(), 'dd/MM/yyyy, hh:mm:ss')) AS PKey
FROM _Sent WITH (NOLOCK)

2 Answers 2

1

When defining a primary key for a Data Extension in Marketing Cloud, it's important to use a combination of fields that will be unique for each record. In your case, the PKey field that you've created seems like it would be a good choice for the primary key, as it combines several different fields.

One thing to keep in mind is that the PKey field should be defined as a text or string data type since it is a concatenation of several different fields.

In general, the ideal combination of fields to use for the primary key will depend on the specific requirements of your use case and the data that is available in the data view. It's best to experiment with different combinations and test the results to determine which combination works best for your needs.

1
  • Hi @zuzannamj yes in my case, Pkey is defined at a primary key but the combination that I have seems to be getting duplicate values and failing (may be transactional email where de-dup is not enabled by design may have same subscriber more than once) so I am wondering what combination is always going to be unique?
    – kl2
    Dec 12, 2022 at 15:43
0

This is pretty close to a duplicate to: SELECT from _sent dataview - duplicate value error occuring.

with the addition that in _click and _open you do have the isUnique field.

These two tables seem to be made to accept and include exact duplicates. So if you are willing to lose some (sometimes inexplicable) duplicates - you could leverage the isUnique field (WHERE isUnique = 'true' ) in the click and open tables, and all you'd lose is the same exact row occuring more than once, but your query should mostly work.

If it still fails and for more detail, refer to the linked answer.

You must log in to answer this question.

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