Answers.Body: <p>We have used the Synopsis extension to generate a tab like this</p>
  <p><a href="https://i.sstatic.net/xdd4c.png" rel="nofollow noreferrer"><img src="https://i.sstatic.net/xdd4c.png" alt="enter image description here" /></a></p>
  <p>An example of the json involved is</p>
  <p>{
  &quot;Label&quot;: &quot;Total Emails opened in last 90 days&quot;,
  &quot;MName&quot;: &quot;Total_Emails_opened_in_last_90_days&quot;,
  &quot;DataType&quot;: &quot;Integer&quot;,
  &quot;SelectRef&quot;: &quot;none&quot;,
  &quot;existing_field&quot;: &quot;0&quot;,
  &quot;Query&quot;: &quot;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&quot;,
  &quot;Optgroup&quot;: &quot;undefined&quot;,
  &quot;Remove&quot;: false,
  &quot;Hidefield&quot;: false,
  &quot;CFID&quot;: 200,
  &quot;Weight&quot;: &quot;9&quot;,
  &quot;column_name&quot;: &quot;total_emails_opened_in_last_90_d_200&quot;,
  &quot;table_name&quot;: &quot;civicrm_value_synopsis_28&quot;
  }</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2022-06-09T23:30:26.503Z
Answers.Text: And being synopsis you should then be able to search those fields, filter, add in reports etc. probably even make smart groups
Answers.UserDisplayName: petednz - fuzion
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2022-06-09T20:23:26.787Z
Answers.LastActivityDate: 2022-06-09T20:23:26.787Z
Answers.UserDisplayName: petednz - fuzion
Answers.Body: <p>Welcome to SE civicrm. There isn't an easy way to do it, but you do it with some extra clicks.</p>
  <p>Step 1:</p>
  <p>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.</p>
  <p>Step 2:
  Using Adv Contact Search(/civicrm/contact/search/advanced?reset=1), find for contacts with Activity type =&gt; Bulk Email, Activity date =&gt; last three years =&gt;&gt; Search =&gt;&gt; Add contact to Smart group</p>
  <p>Using Include / Exclude Search(Search &gt;&gt; Custom Search) =&gt; Find for contacts from group created at step2 and excluding from group created at step 3.</p>
  <p>HTH</p>
  <p>Pradeep</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2022-06-09T17:52:14.810Z
Answers.LastActivityDate: 2022-06-09T17:52:14.810Z
Answers.UserDisplayName: Pradeep Nayak
Answers.Body: <p>You can easily do this using GDPR extension <a href="https://github.com/veda-consulting-company/uk.co.vedaconsulting.gdpr" rel="nofollow noreferrer">https://github.com/veda-consulting-company/uk.co.vedaconsulting.gdpr</a></p>
  <p>Navigate to Contacts &gt;&gt; GDPR Dashboard or navigate to civicrm/gdpr/settings</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2022-06-13T06:04:12.763Z
Answers.Text: good to know - hmm actually i do not see 'when was mail opened' in my settings. perhaps i am on old version?
Answers.UserDisplayName: petednz - fuzion
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2022-06-13T02:07:50.113Z
Answers.LastActivityDate: 2022-06-13T02:07:50.113Z
Answers.UserDisplayName: Tapash
Body: <p>How do I identify the contacts who haven't opened any mailings in the last 3 years?</p>
  <p>I'm unsure which are the appropriate fields in the Advanced Search to make this process as easy as possible.</p>
  
Tags: <contacts><database>
Title: Cleaning contacts
ThreadText: # Cleaning contacts
  
  (Asked by: username_0 on 2022-06-09)
  
  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.
  
  **Tags:** contacts, database
  
  
  ---
  
  ## 3 answers
  
  ### Answer by username_1 on 2022-06-09. Score: 2
  
  We have used the Synopsis extension to generate a tab like this
  
  [![enter image description here](https://i.sstatic.net/xdd4c.png)](https://i.sstatic.net/xdd4c.png)
  
  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"
  }
  
  > **Comments:**
  > **username_1**: And being synopsis you should then be able to search those fields, filter, add in reports etc. probably even make smart groups  
  
  ---
  
  ### Answer by username_2 on 2022-06-09. Score: 2
  
  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
  
  username_2
  
  
  ---
  
  ### Answer by username_3 on 2022-06-13. Score: 1
  
  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
  
  > **Comments:**
  > **username_1**: good to know - hmm actually i do not see 'when was mail opened' in my settings. perhaps i am on old version?  
  
  ---
  
  