Feed a Send Log / Send History Data Extension automatically

Whatever the reason is, you may want to save the Sends history in a data extension. This may allow you to extract this data for an external BI tool, or save the HTML message associated to each user for each send, etc…

So the idea would be to place an AMPSCRIPT code in each of your emails. A best practice would be to create an HTML content, and re-use it in all your templates / messages, by referring to its ID:

%%=ContentArea("your-id")=%%

Prepare your data extension

First, you should create the data extension dedicated to save the Send data. No need to explain this part, unless you ask for this :)

Create your script

The AMPSCRIPT code to be written should:

  • launch only when the email is sent (not previewed)
  • save the wanted fields to the data extension

Here it is, commented:

%%[
if _messagecontext == "SEND" and jobid > 0 then
     /* the message context is an actual deployment, not a test or preview */
     InsertDE("sendLog", /* the name of the data extension */
     "SUBSCRIBERKEY", _subscriberkey, /* map your DE field with the value */
     "EMAILADDRESS", Email,
     "SENT_EMAIL", emailname_,
     "SENT_DATE", Now()
     )
EndIf
]%%

In order to create such a script, you may need information about:

How to save the HTML message for each user ?

Now, you can save any information you may want into your Data Extension. In other terms, you may use this data extension to save the HTML of the email that will be received by each recipient, including personalization. The ideal way to perform this is to retrieve the code of the web-page (aka ‘mirror’ page):

%%[
IF _messagecontext == "SEND" AND jobid > 0 THEN
set @HTML = HTTPGet(view_email_url)
InsertDE("sendHistory",
"JobID",jobid,
"SubscriberKey",_subscriberkey,
"EventDate",NOW(),
"HTML",@HTML
)
ENDIF
]%%

Here, we set an @HTML variable, that will retrieve the web page content via an HTTPGET function. This variable is then saved in the « HTML » field of the data extension.

Solve the « open count » issue

The drawback of using the HTTPGET function on the web page is that an OPEN will be counted at the time of the send. The code of the page is executed, so the open tag will be launched. In order to avoid counting an OPEN when the mirror page is called, you can use some Message Context conditions in order to only show the open counter when the context is the SEND or the VAWP (View As Web Page). This code has to be applied to all your emails containing the Send history feature:

%%[ IF _messagecontext == "SEND" OR _messagecontext == "VAWP" THEN ]%%
<custom name="opencounter" type="tracking">
%%[ ENDIF ]%%

This conditional OPEN counter will make the trick !

11 réflexions au sujet de « Feed a Send Log / Send History Data Extension automatically »

  1. hello Mathieu

    tres interressant, j’ai un question sur la code ?

    mais eje continue en anglais, because i want to spare you from my halting french!
    I have been including this script in all my email sends in exacttarget but i miss knowing what is its full function? And what exactly is the difference if you have it or you dont ?

    Does it enable you to track exactly when an email is being opened without having the need to click to download image ?

    thank you very much
    rab

    1. Hi Rabiah,
      no the sendlog only keeps track of the sends, not the opens not clicks. By itself, the sendlog is useless, but you can use it via queries in order to aggregate send information per period, for example, and calculate some frequency metrics about how often eacu user receives emails.

    1. Thanks Adam. The fact is that you cannot choose the Send Log data extension (only one for the Business Unit) when you activate the Send Logging via SFMC. Also, if you need to send additional fields to the existing ones, ampscript variables with the same name as your Send log data extension columns should be present in the email, so Ampscript may be necessary in this case.
      Let’s say the « default » Send Logging is perfect when you do not need additional information in your send log.

  2. Thanks, Mathieu for this trick.
    I have had the same issue and I thought exactly about this workaround. ( thanks for the trick of HTML content ;) )

    I want to share some assumptions :

    – When you left the length as blank the 4000 characters limitation is bypassed.

    – We definitely need to include the custom tag to get the tracking of the emails.

    I still have one problem with this solution when the individual email result record is created the field date sent is not containing the exact datetime as stored in my DE using the now() function. I have a problem with seconds which is not always exact.

    I really need to have the same DateTime since I am using it as a key to get the content of the email from the DE via REST API call from service cloud when individual email result record is created.

    Anyone can help here?

  3. Hi all,

    I am figuring out if this method works of inserting HTML & Amscript code would work on emails created with drag and drop method. I have to insert these codes on all the emails sent from MarketingCloud even those created with drag & drop method

    1. Hi Leila,

      this is definitely possible, you can add an html block in your email and write your code in it. Once done, this block can be saved to be reused in other emails.

  4. Hi all and Mathieu,

    I have a question about the procedure I am quite new on MarketingCloud and I am not that qualified on Ampscript code. I would like to know on the first step (create the script) for these fields :
    « SUBSCRIBERKEY », _subscriberkey
    « EMAILADDRESS », Email,
    « SENT_EMAIL », emailname_,
    « SENT_DATE », Now()
    Does my DE have to contain the field name SUBSCRIBERKEY and EMAILADDRESS and SENT_EMAIL and SENT_DATE ?

    For the second step (How to save HTML for each user ?), do I have to insert these fields in my Sendlog DE :
    « sendHistory »,
    « JobID »,
    « SubscriberKey »
    « EventDate »
    « HTML »
    and do I have to does the « send history » in the is the name should be the name of the DE ?

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.