[kaj710]

Good Afternoon Fellow Power Users!

Thus far the forum has been helpful so I am going to try and get info on something that I have been totally unsuccessful at in the past.

I would like to create a customer saved search that uses details of the most recent email logged as part of the criteria. Long story short…. We had some terrible sales reps that were not taking care of their leads/prospects correctly. I need the criteria to pull any lead/prospect whose last email logged was FROM the customer/prospect TO us (either a specific email address or contains our domain). There are many other areas where we could apply this method in similar fashions so I would like a very detailed answer as to how to write the criteria. I imagine it is something like… (the formula below is just me brainstorming possibilities of format, I don’t know how to write it correctly) I am also not even sure if a formula is how this can be done, IF it can be done.

(text formula) CASE WHEN max/min (not sure which) {entity.tomessage} contains ‘@domain.com’ AND min/max (not sure wich) from message contains {entity.email} THEN 1 ELSE 0 END

Any guidance would be a huge help!

Thanks
This is a cached copy. Click here to see the original post.

[michoel]

MAX({emailfield}) would just return the maximum alphabetically sorted email address without taking dates into consideration.

I think the easiest way to do this would be to have a Saved Customer Search output all customers and their last logged message, which you could then export to CSV/Excel and filter there. This is quite simple to do using the ‘When Ordered By’ field in the results (see the documentation here).

Basically you would create a Saved Customer Search, and make sure you have at least the following result fields:ID (Summary Type = Group)
Message fields… Author Email (Summary Type – Maximum, When Ordered By – Message fields.. Date)
Message fields… Recipient Email (Summary Type – Maximum, When Ordered By – Message fields.. Date)
If you need to filter in the Saved Search, you may be able to do this using Summary Search Filters (see the documentation here). It seems the that the ‘When Ordered By’ field is not available in the UI, but you should be able to construct it yourself in a formula using Oracle analytic functions..

[Author]

Posts