Create campaignmember record in Sales Cloud and add email send log to a Marketing Cloud data extension?

Question

Need help writing an AMPscript to create a campaignmember record AND also upsert a record to a data extension from an email send. This is the AMPscript I used.

%%[
var @contactId, @campaignId, @id, @contactRows, @limitRow
set @id = Id
set @campaignId = '70190000000sgS9'
set @contactRows = RetrieveSalesforceObjects('Account','PersonContactId','Id','=', @id)
set @limitRow = Row(@contactRows, 1)
set @contactId= Field(@limitRow, 'PersonContactId')

IF _messagecontext == "SEND" Then
CreateSalesforceObject(
'CampaignMember', 3,
'CampaignId',@campaignId,
'ContactId',@contactId,
'Status','Sent'
)
EndIF   
]%%

When I perform a guided email send, the create campaign member record step works.

However, as I also want to create a send log Data extension and add subscribers that were sent the email to my data extension, I amended AMPscript to this and this causes the email send to fail.

%%[
var @contactId, @campaignId, @id, @contactRows, @limitRow,  
set @id = Id
set @campaignId = '70190000000sgS9'
set @contactRows = RetrieveSalesforceObjects('Account', 'PersonContactId','Id',     '=', @id)
set @limitRow = Row(@contactRows, 1)
set @contactId= Field(@limitRow, 'PersonContactId')
if_messagecontext != "SEND" then
CreateSalesforceObject(
'CampaignMember', 3,
'CampaignId',@campaignId,
'ContactId',@contactId,
'Status','Sent'
)
upsertDE 
(
'EmailTrackingDE',1,
'SubscriberKey',_SubscriberKey,
'JobID',jobid,
'EmailName',emailname_,
'DateSent',NOW()
)
endif
]%%

Answer 1

Upserts aren't allowed in email sends, but if you simply create an AMPScript variable that has a matching column in a SendLog templated data extension, then it'll get recorded automatically.

For example, in your email you have something like this:

%%[

var @firstName
set @firstName = "Moonbeam"

]%%

In the Send Log Data Extension:

• JobID
• ListID
• BatchID
• SubID
• TriggeredSendID
• ErrorCode
• FirstName <- the value at send time will get recorded in this column

More details can be found here:

Send Logging