Cleaning contacts

Question

How do I identify the contacts who haven't opened any mailings in the last 3 years?

I'm unsure which are the appropriate fields in the Advanced Search to make this process as easy as possible.

Answer 1

Welcome to SE civicrm. There isn't an easy way to do it, but you do it with some extra clicks.

Step 1:

You can use Mailing Open report(/civicrm/report/Mailing/opened?reset=1), find the mailing opened in the last 3 years, and Add all those contacts into a group using the action drop-down appearing on left side of the report.

Step 2: Using Adv Contact Search(/civicrm/contact/search/advanced?reset=1), find for contacts with Activity type => Bulk Email, Activity date => last three years =>> Search =>> Add contact to Smart group

Using Include / Exclude Search(Search >> Custom Search) => Find for contacts from group created at step2 and excluding from group created at step 3.

HTH

Pradeep

Answer 2

We have used the Synopsis extension to generate a tab like this

An example of the json involved is

{ "Label": "Total Emails opened in last 90 days", "MName": "Total_Emails_opened_in_last_90_days", "DataType": "Integer", "SelectRef": "none", "existing_field": "0", "Query": "SELECT count(*) as opened\n FROM\n civicrm_mailing_event_queue q\n INNER JOIN civicrm_mailing_event_opened o ON o.event_queue_id = q.id\n WHERE\n q.contact_id = {contact_id}\n\t\t\tand CAST(o.time_stamp AS DATE) BETWEEN DATE_SUB(NOW(), INTERVAL 90 DAY) AND NOW()\n\n", "Optgroup": "undefined", "Remove": false, "Hidefield": false, "CFID": 200, "Weight": "9", "column_name": "total_emails_opened_in_last_90_d_200", "table_name": "civicrm_value_synopsis_28" }

Answer 3

You can easily do this using GDPR extension https://github.com/veda-consulting-company/uk.co.vedaconsulting.gdpr

Navigate to Contacts >> GDPR Dashboard or navigate to civicrm/gdpr/settings