0

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

dan123
  • 43
  • 1
  • 1
  • 5

1 Answers1

1

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 = "aspriggs@degdigital.com"
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

Adam Spriggs
  • 626
  • 8
  • 23
  • Thanks Adam but it isn't exactly what I expected to hear. Or you answered half of my question. I know how to use lookup when data is in ET, but how do you access _Click or _Send data in Salesforce without running sequel? I have just checked SendLog and this is not there – dan123 Mar 03 '15 at 16:03
  • The system data view data is in ET and you can treat it just like any other data extension. I've updated my answer with an example. – Adam Spriggs Mar 03 '15 at 17:52
  • Brilliant. This helps a lot. I will teak this for to my requirements. Amazing, thanks for your input Adam! – dan123 Mar 04 '15 at 12:36