Is it possible to access SendLog and DataView data i.e. _Click via AMPScript to populate email content? Something like:
%%[SET EmailContent = _Click(linkName);]%%
I know how to run SQL query and save this as a Data Extension but I'd like to use it on the fly. Does anyone know how this can be done?
Thanks
Dan
Yes, it's possible using one of the AMPScript lookup functions. Here's a simple example:
%%[
var @DEColumn1, @lookupValue
set @lookupValue = "whee"
set @DEColumn1 = Lookup("DataExtensionName", "ReturnColumn", "LookupColumn", @lookupValue)
]%%
DEColumn1 is %%=v(@DEColumn1)=%%
I have a few more examples of the different lookup types are here on my blog.
UPDATE: Example retrieving _Click data:
%%[
var @rows, @row, @rowCount, @numRowsToReturn, @lookupValue, @i
set @lookupValue = "[email protected]"
set @numRowsToReturn = 0 /* 0 means all */
set @rows = LookupOrderedRows("_Click",@numRowsToReturn,"EventDate desc","SubscriberKey", @lookupValue)
set @rowCount = rowcount(@rows)
if @rowCount > 0 then
for @i = 1 to @rowCount do
var @jobID, @batchID
set @row = row(@rows,@i) /*get row based on loop counter */
set @jobID= field(@row,"jobID")
set @batchID= field(@row,"batchID")
]%%
Row %%=v(@i)=%%, jobID: %%=v(@jobID)=%%, batchID: %%=v(@batchID)=%%<br>
%%[
next @i ]%%
%%[ else ]%%
No rows found
%%[ endif ]%%
Reference: System data views