Can we lookup System Data using AmpScript?

Question

I would like to query system data in my email using Ampscript to perform certain actions, can this be done? If yes, what function would be used?

An example would be to see if the current sender has any records in the _sent System Data view view.

Answer 1

There are a number of functions to lookup data in a data extension using Ampscript.

https://developer.salesforce.com/docs/atlas.en-us.noversion.mc-programmatic-content.meta/mc-programmatic-content/lookuprows.htm

But if you are wanting to lookup the system tables like _sent using Ampscript then it wouldn't be advisable to do this with AMPScript as these are pretty heavy dataa tables and probably not best querying them at send time with Ampscript.

Instead, you should use SQL to pre-build your audience/data extension and set the variables you want in the sending data extension. You can then use the LookupRows() function to lookup the values in the data extension, or if you are saving the result in the Send Data extension, then you can use the personalisation string of the field e.g. %%FieldName%%.

Pre-process the result using SQL and join on the _sent table and add your result to the sending data extension (or other data extension).

More information on SQL the tables here :

https://help.marketingcloud.com/en/documentation/automation_studio/using_automation_studio_activities/using_the_query_activity/sql_reference/