0

I have a Sendable Data Extension with thousands of records. Each record represents an email request and its properties values. I was looking for a way to get a unique id for each row, if any exists. I saw this and I thought it might be useful Autonumber in Marketing Cloud?

However as the existence of a row is not a guarantee that the email has been sent, how can I match or confirm if an email request that exists in the sendable Data Extension was really sent or not? Is there any other SQL query I can run to filter the sent from the unsent? Is there any column referring to the send status?

  • 3
    I guess this answers my question. salesforce.stackexchange.com/questions/147756/… – mario ruiz Jul 8 at 22:06
  • On top of that unique ID I would require as well to separate the sent from the unsent – mario ruiz Jul 9 at 20:15
  • 1
    Are you looking to see for a specific send, from a TSD, sent to in general,...? I am not sure I understand your exact request as it talks about row numbering and recognizing sent/unsent - but not the specific end goal. If you could give a bit more explicit direction into what is your expected result, It may help me better understand your request. – Gortonington Jul 12 at 14:13
  • 1
    but what sent emails? all of them? If so, then as shown in the answer, a Send Log is your best bet. If you are looking for specific sends, you can look against the _Sent DV and query against your DE, etc. etc. Need more info to provide an appropriate solution. – Gortonington Jul 12 at 14:39
  • 1
    Then I highly recommend the send log as this will house every sent email from your account and is easier to customize/reference then the _Sent DataView as @JonasLamberty answered. – Gortonington Jul 12 at 15:49
2
+50

I recommend a send log. Ask support to enable the functionality, then create a data extension "from template" / SendLog.

Every record that is actually sent from your BU is inserted into this data extension at send time. here is the documentation for this. https://help.salesforce.com/articleView?id=mc_es_send_logging.htm&type=5

Since every one of your sent events needs an ID, either you have an external system set such an ID, or you create such a "Send event key" from the default parameters tracked in the send log: jobID, BatchID,ListID, SubID.

The combination of these four constitutes a unique send event. BatchID and listId become relevant in journey builder. JB doesn't change the JobID even if you get the same message twice.

The _CustomObjectKey is great, but will not help you much, because it has no relationship to the system tracking in the data views.

For your usecase, you want: all unique send events which are in:

a) the system tracking table for sent (data view "_Sent")

b) the send log. (theoretically the send log alone would be enough, but it doesnt have the subscriberKey, which we can find in "_sent")

c) your data extension.

You can use an SQL inner join to find these. The SQL would look something like that (untested)

SELECT 
s.SubscriberKey
FROM
"_Sent" s
/*get every subscriber key in the _Sent data view, which holds the Subscriber Key ...*/

INNER JOIN
"yourSendlogDE" sl
on sl.SubID = s.SubscriberID
    AND sl.JobID = s.JobID
    AND sl.BatchID = s.BatchID
    AND sl.ListId = s.ListId
/*... that matches a send event in your send log data extension. 
finding a send event must happen on the four unique parameters subscriberId, jobid, batchid and listid. */

INNER JOIN

"yourDE" de
on s.SubscriberKey = de.SubscriberKey

/*now just filter the above to retrieve only those subscriber keys who are also in your source DE, otherwise you have ALL sends from the BU, not just
your sendable DE in the result.*/

The resulting DE has only the entries from your DE which have been sent something. You could include a WHERE clause to limit the timeframe.

Hope this helps.

  • I have enabled SendLog and trying to get into it. Thanks @Jonas Lamberty – mario ruiz Jul 12 at 15:16
  • Still figuring out but Your answer has helped me definitely! @jonas-lamberty. Thanks. – mario ruiz Jul 15 at 14:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.