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.

3 Answers 3


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.




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

enter image description here

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" }

  • And being synopsis you should then be able to search those fields, filter, add in reports etc. probably even make smart groups
    – petednz - fuzion
    Commented Jun 9, 2022 at 23:30

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

  • good to know - hmm actually i do not see 'when was mail opened' in my settings. perhaps i am on old version?
    – petednz - fuzion
    Commented Jun 13, 2022 at 6:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.