How to populate FromName and FromEmail in EmailSendLog with both regular and dynamic Sender Profiles

Question

Context: I've setup a Dynamic Sender Profile solution using a Code Snippet block call with AMPScript variables and a Configuration DE.

Solution works perfectly fine BUT I've come across a limitation of this solution that I was not aware until now: _Job data view does not populate "FromName" and "FromEmail" fields if AMPScript is used in the Sender Profile -> https://help.salesforce.com/s/articleView?id=000394057&type=1

There's a need to store these values for reporting and debugging purposes. So, to work-around this issue and as recommended by SF, I've extended the our EmailSendLog to include "FromName", "FromEmail". This worked as expected since we're setting the AMPScript variables with the corresponding names of the EmailSendLog:

%%[
SET @market = AttributeValue("MarketCode")

SET @rows = LookupRows("MT_SenderProfiles_Configuration", "MarketCode", @market, "IsVerified", 1, "IsApproved", 1)
SET @rowCount = rowCount(@rows)

IF @rowCount > 0 THEN
   SET @row = row(@rows, 1)
   SET @fromName = field(@row,"FromName")
   SET @fromEmail = field(@row,"FromEmail")
   SET @replyName = field(@row,"ReplyName")
   SET @replyEmail = field(@row,"ReplyEmail")
ELSE
    /** Fallbacks **/
ENDIF 
]%%

Issue: Not all our campaigns will use Dynamic Sender Profile solution, some will still use a regular Sender Profile which EmailSendLog is not able to populate the fields of "FromName" and "FromEmail" because these are not being set anywhere or present in the Sendable DE. This has led us to a non-optimal scenario of:

• When using Dynamic Sender Profile - EmailSendLog is populated and _Job is empty
• When using Regular Sender Profile - EmailSendLog is empty and _Job is populated

Limitations: Sendable DE with the 2 extra fields is not an option at this point in time & there's no AMPscript Personalization strings to retrieve From Name and From Email from the backend (as far as documentation states)

Question: Having in mind the limitations stated, any suggestions on how to successfully capture FromName and FromEmail in the EmailSendLog when using a regular Sender Profile?

Much appreciated!

Answer 1

You can make your SendLog Data Extension as one source of truth for that matter by setting up an Automation with a SQL Query Activity that will find records from _Job Data View where "FromName" and "FromEmail" is not empty and also join it with _Sent Data View to make sure you will update the corresponding row in the SendLog.

The SQL would look something like:

SELECT
j.FromName,
j.FromEmail
FROM _Job j
INNER JOIN SendLog s ON j.JobId = s.JobId
INNER JOIN _Sent c ON s.SubID = c.SubscriberID AND s.JobID = c.JobID AND s.ListID = c.ListID AND s.BatchID = c.BatchID
WHERE
j.FromName IS NOT NULL AND j.FromEmail IS NOT NULL AND
s.FromName IS NULL AND s.FromEmail IS NULL

and then target SendLog Data Extension with Update Data Action.

This might get tricky if we are talking about big volumes in the mentioned DEs but then you can also update the SendLog with small deltas by adding DateTime filtering.