Contact Report For Follow Up

Question

I am trying to create a report of contacts in our system that have not been contacted or worked with, in quite some time. Lets say 6 months. I was hoping that when an email was sent, it would "modify" the contact and the report would be a simple matter of finding contacts whose records had not been modified but sending an email or adding to a quote doesnt seem to modify the contact record at all. Ideally, these would be the customers that we have not emailed in 6 months or have not been a part of any quotes in that time. Essentially, they would not have been part of being a "Bill to" contact, a "ship to Contact" or an email in the past 6 months. Is there an easy way to build this? 
 Thanks!

Norm Bonenfant · Answer

In my limited experience, reporting on what is NOT there is hard, if not impossible, straight out of reports unless you use Advanced Reports with a Query or write your own Scheduler to send a report by filtering through beans/code etc...

Also, Emails are a very tricky beast in Sugar as they are not always related to the Contact that the address belongs to, there are implicit and explicit relationships:

Email<->Contact relationship only exists when you compose the email from the Contact record.

If you compose the email from the Cases module, for example, then the Email will be related to the Case and NOT the Contact, the relationships you can work with in this example are:

Email<->Case<->Contact

Email<->Email Address<-> Contact

Similarly if you compose an email from the Opportunity it will be related to the Opportunity NOT the Contact.

So, leveraging the Contact/Email relationships will not catch everything and your most reliable measure of an email being sent is through the email addresses related to the Contact.

Hence, the only way I can think of to get contacts that have not been emailed in a reliable way is a query that would look at all the emails sent in the last six months and their email addresses and then look for all contacts who don't have any email addresses related to those emails....

This needs testing but what I think you need is and Advanced report with a query

Group 1) Find all the contacts who received an email sent to any of their related email addresses in the last 6mo

select distinct(contacts_with_emails.id) 
from contacts as contacts_with_emails
join email_addr_bean_rel eabr on contacts_with_emails.id = eabr.bean_id and eabr.bean_module = 'Contacts' and eabr.deleted = 0 /* look at the the contact's related email addressess ids*/
where contacts_with_emails.deleted = 0
	and eabr.email_address_id in 
	(select eear.email_address_id 
		from emails_email_addr_rel eear 
		where date(eear.date_entered) >= DATE_SUB(now(), INTERVAL 6 MONTH) /*emails logged in the last six months*/
        and eear.address_type != 'from' /* emails we sent as to the contact's address, cc, bcc but not emails received from the contact */ 
	)

Group 2) Find all the Contacts who had calls within the last 6mo

select distinct(calls_contacts.contact_id)
	from calls_contacts
	where calls_contacts.deleted = 0 
	and calls_contacts.date_modified >= DATE_SUB(now(), INTERVAL 6 MONTH) /*calls logged in the last six months*/

Now put those together to get all your Contacts MINUS the ones that are in group 1 or 2

select *
from contacts
join contacts_cstm on id = id_c
where contacts.deleted = 0
and contacts.id not in 
/* exclude the contacts who had calls*/
	(select distinct(calls_contacts.contact_id)
	from calls_contacts
	where calls_contacts.deleted = 0 
	and calls_contacts.date_modified >= DATE_SUB(now(), INTERVAL 6 MONTH) /*calls logged in the last six months*/
	) 
and contacts.id not in
/* exclude the contacts who had emails */
	(select distinct(contacts_with_emails.id) 
	from contacts as contacts_with_emails
	join email_addr_bean_rel eabr on contacts_with_emails.id = eabr.bean_id and eabr.bean_module = 'Contacts' and eabr.deleted = 0 /* look at the the contact's related email addressess ids*/
	where contacts_with_emails.deleted = 0
		and eabr.email_address_id in 
		(select eear.email_address_id 
			from emails_email_addr_rel eear 
			where date(eear.date_entered) >= DATE_SUB(now(), INTERVAL 6 MONTH) /* emails in last six months */
			and eear.address_type != 'from' /* emails we sent as to the contact's address, cc, bcc but not emails received from the contact */ 
		)
	)

Note that these queries are not fully tested/verified so you will need to do some testing here.

Also note that I'm reducing the number of table joins by making some somewhat-logical assumptions:

that the relationship between the email address and the email itself (emails_email_addr_rel. date_entered) has the same date stamp and the date the email record was created (emails.date_entered) and it's not too far removed from the date the email was sent (emails.date_sent)
that the date the relationship between the contact and the Call was created (calls_contacts.date_modified) is the same as the date the Call record was created (calls.date_entered) and not far from the date the call was held (calls.date_start)
that if the call was logged, it was also actually held.

If you don't like those assumptions you will need to join the emails and calls tables respectively change the criteria you use to determine emails sent and calls held.

FrancescaS