I have a question regarding query of logs. Is it possible to query profiles who have received for example 10 emails (have 10 delivery log ID's on the sending/delivery log) or clicked in 5 emails (have 5 tracking logs) etc in ACS? I can't find any way to do it without having to extract the data into excel and then do some work in there. But it would be much easier if there happened to be a way in the system, maybe with advanced expression were it could be done.
A few options exist in ACS that will allow us to 'aggregate' records on a table, specifically the delivery and tracking log tables.
- The first method is to Enrich your query using Additional Data. Here you will query the profile table to return the target profiles you would like to review and configure 'Additional Data'. Under additional data, create an element and select the log table (delivery or tracking). After this selection you will land on a screen that allows you to select aggregate or collection - in this case you want to choose aggregate. Configure the aggregate as a distinct count and choose an attribute on the log table, such as event date. Once you have your aggregate values you will then need to filter/segment for profiles that have 10 or greater.
- The second method is to write a query on the profile resource. The condition should select the log table and the filter should be changed from Exists to Count. Then you can add the logical operator to get the 'count' that you need.