Is there a Data View of Email Content Tied to the Job Data View?

Question

The Data View for Job has an EmailID as one of the columns. Is there a Data View for Email that can be associated with the job using that EmailID column? Documentation of Data Views doesn't have anything regarding email content.

The only other option I can think of is making "my own email data view" using AMPscript in the email at the time of send:

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

Has anyone done anything like this before? Any other implications with this? I imagine I can just keep the code in a Content Area and drop that in the Admin HTML Header.

Answer 1

I assume that you are trying to store a copy of the sent email. This is a great question. There are several considerations with your proposed approach.

1. Send Performance

While all AMPscript calls to insert, update or delete data are made in a single call, after all emails have been sent, OMM will still check the DE used to perform the Insert when the email is sent (I assume to validate that the record can be inserted). I've noticed a significant performance issue, where if you have many records (like 1m+) in the DE used by the InsertDE function, then this can have a big impact on send performance and slow your send down to ~1k records per hour.

2. Database Performance

While you can create unbounded columns in Data Extension fields (e.g. not set a field length value), Marketing Cloud uses Microsoft SQL Server for Data Extensions. And SQL Server has a row limit of 8,060 bytes. Columns that don't fit within the limit are placed off-row, in a separate internal table. As a result, the platform has to execute multiple queries in order to return a single row for Data Extension records that exceed this row limit.

So if you store an entire HTML message in a single DE field, the platform has to make several queries to retrieve a single record (in order to retrieve all the separate fragments).

If you will only retrieve a single record (e.g. to display HTML on a CloudPage), then you won't really notice a performance impact, but if you need to query a large recordset, then you may experience issues.

3. Open Tracking

Just remember that when you retrieve the HTML using the view_email_url, this will count as an 'open'. So make sure you wrap your open tracking pixel in a conditional statement. For example:

%%[ if _messagecontext != "VAWP" then ]%%
<custom name="opencounter" type="tracking"></custom>
%%[ endif ]%%

My Recommended Approach

When I come across a requirement to store HTML at send time, I typically do the following:

1. Create a Send Log DE with a Retention Policy (like 1 month)
2. Add a nullable text field named to the Send Log named view_email_url
3. Add a nullable boolean field to the Send Log named Processed with a default value of false

When emails are sent, the VAWP link will automatically be added to the view_email_url field in your Send Log DE.

Then, either using a Script Activity and an Automation, or an external app which retrieves DE records using the Marketing Cloud API (I'd recommend the latter if you have a choice, so you can run this process this asynchronously), perform the following tasks:

1. Retrieve all records in the Send Log where Processed equals false
2. Next, get the HTML from the view_email_url
3. Store it (somewhere) and then update the Processed field to true.

I've tried using Script Activity in an Automation, but the process to retrieve the VAWP HTML using SSJS is slow, as you can only loop through records consecutively. I've found that I can't process more than 3,000 records at a time in a Script Activity (any longer and the Script takes more than 30 mins to run and times out).

Instead, I generally run this asynchronously from a Heroku app and store the HTML in MongoDB GridFS which is a more appropriate data store for this type of data.