[jon.dray@themindgym.com]

We would like to create a saved search of summarized counts based on contacts the purpose of this is to see any campaign responses a contact may have and any events (meetings) that have been subsequently created to see the relationship between response and meeting.

The structure of the saved search then would be the following

Response Name (i.e. October Mailing)

Number of Respondents (i.e. Number of contacts linked to that Mailing)

Number of Events (i.e. Number of meetings that have subsequently been subsequently booked – This requires event.createddate to be greater than campaignresponse.responsedate

I have tried to create this and am halfway there but keep hitting a brick wall in terms of counts as it seems to be counting the same contact a number of times depending on number of events that have been created against them.

What i would like to do is bring back only one event for each contact/campaign response and that event should have event.createddate > campaignresponse.responsedate and also be the closest event.createddate to campaignresponse.responsedate.

I have tried a number of ways to calculate this using dense_rank and trying to use the summary criteria but can’t seem to limit.

Is this possible to do and what would be the best way to achieve this?
This is a cached copy. Click here to see the original post.

[Author]

Posts