Answers.Body: <p>Can you do the opposite? e.g. if the mailing group is called &quot;A&quot;, then make another group called B that is all contacts in the group who HAVE opened the mailing in X months. Then compute all contacts in A not in B.</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2024-04-30T12:54:04.403Z
Answers.LastActivityDate: 2024-04-30T12:54:04.403Z
Answers.UserDisplayName: Demerit
Answers.Body: <p>The snippet below should give you list of contacts with mailing name, date delivered and date opened and diff days</p>
  <pre><code>[
    [
      &quot;SavedSearch&quot;,
      &quot;save&quot;,
      {
        &quot;records&quot;: [
          {
            &quot;name&quot;: &quot;test&quot;,
            &quot;label&quot;: &quot;test&quot;,
            &quot;form_values&quot;: null,
            &quot;mapping_id&quot;: null,
            &quot;search_custom_id&quot;: null,
            &quot;api_entity&quot;: &quot;Mailing&quot;,
            &quot;api_params&quot;: {
              &quot;version&quot;: 4,
              &quot;select&quot;: [
                &quot;id&quot;,
                &quot;name&quot;,
                &quot;Mailing_MailingJob_mailing_id_01_MailingJob_MailingEventQueue_job_id_01.contact_id.display_name&quot;,
                &quot;Mailing_MailingJob_mailing_id_01_MailingJob_MailingEventQueue_job_id_01_MailingEventQueue_MailingEventDelivered_event_queue_id_01.time_stamp&quot;,
                &quot;Mailing_MailingJob_mailing_id_01_MailingJob_MailingEventQueue_job_id_01_MailingEventQueue_MailingEventOpened_event_queue_id_01.time_stamp&quot;,
                &quot;DATEDIFF(Mailing_MailingJob_mailing_id_01_MailingJob_MailingEventQueue_job_id_01_MailingEventQueue_MailingEventOpened_event_queue_id_01.time_stamp, Mailing_MailingJob_mailing_id_01_MailingJob_MailingEventQueue_job_id_01_MailingEventQueue_MailingEventDelivered_event_queue_id_01.time_stamp) AS DATEDIFF_Mailing_MailingJob_mailing_id_01_MailingJob_MailingEventQueue_job_id_01_MailingEventQueue_MailingEventOpened_event_queue_id_01_time_stamp_Mailing_MailingJob_mailing_id_01_MailingJob_MailingEventQueue_job_id_01_MailingEventQueue_MailingEventDelivered_event_queue_id_01_time_stamp&quot;
              ],
              &quot;orderBy&quot;: [],
              &quot;where&quot;: [
                [
                  &quot;scheduled_date&quot;,
                  &quot;&lt;&quot;,
                  &quot;now&quot;
                ],
                [
                  &quot;Mailing_MailingJob_mailing_id_01_MailingJob_MailingEventQueue_job_id_01_MailingEventQueue_MailingEventDelivered_event_queue_id_01.time_stamp&quot;,
                  &quot;IS NOT EMPTY&quot;
                ]
              ],
              &quot;groupBy&quot;: [],
              &quot;join&quot;: [
                [
                  &quot;MailingJob AS Mailing_MailingJob_mailing_id_01&quot;,
                  &quot;LEFT&quot;,
                  [
                    &quot;id&quot;,
                    &quot;=&quot;,
                    &quot;Mailing_MailingJob_mailing_id_01.mailing_id&quot;
                  ]
                ],
                [
                  &quot;MailingEventQueue AS Mailing_MailingJob_mailing_id_01_MailingJob_MailingEventQueue_job_id_01&quot;,
                  &quot;LEFT&quot;,
                  [
                    &quot;Mailing_MailingJob_mailing_id_01.id&quot;,
                    &quot;=&quot;,
                    &quot;Mailing_MailingJob_mailing_id_01_MailingJob_MailingEventQueue_job_id_01.job_id&quot;
                  ]
                ],
                [
                  &quot;MailingEventOpened AS Mailing_MailingJob_mailing_id_01_MailingJob_MailingEventQueue_job_id_01_MailingEventQueue_MailingEventOpened_event_queue_id_01&quot;,
                  &quot;LEFT&quot;,
                  [
                    &quot;Mailing_MailingJob_mailing_id_01_MailingJob_MailingEventQueue_job_id_01.id&quot;,
                    &quot;=&quot;,
                    &quot;Mailing_MailingJob_mailing_id_01_MailingJob_MailingEventQueue_job_id_01_MailingEventQueue_MailingEventOpened_event_queue_id_01.event_queue_id&quot;
                  ]
                ],
                [
                  &quot;MailingEventDelivered AS Mailing_MailingJob_mailing_id_01_MailingJob_MailingEventQueue_job_id_01_MailingEventQueue_MailingEventDelivered_event_queue_id_01&quot;,
                  &quot;LEFT&quot;,
                  [
                    &quot;Mailing_MailingJob_mailing_id_01_MailingJob_MailingEventQueue_job_id_01.id&quot;,
                    &quot;=&quot;,
                    &quot;Mailing_MailingJob_mailing_id_01_MailingJob_MailingEventQueue_job_id_01_MailingEventQueue_MailingEventDelivered_event_queue_id_01.event_queue_id&quot;
                  ]
                ]
              ],
              &quot;having&quot;: []
            },
            &quot;expires_date&quot;: null,
            &quot;description&quot;: null
          }
        ],
        &quot;match&quot;: [
          &quot;name&quot;
        ]
      }
    ]
  ]
  </code></pre>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2024-04-30T14:46:28.613Z
Answers.Text: Thanks @Pradeep, but how do I add that query to searchkit to run? Or can you do a screenshot of the settings you used to create the search.
Answers.UserDisplayName: Mike Wilson
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2024-04-30T15:55:54.350Z
Answers.Text: Go to searchkit page and click on Import and paste the above code
Answers.UserDisplayName: Pradeep Nayak
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2024-04-30T13:07:57.857Z
Answers.LastActivityDate: 2024-04-30T13:07:57.857Z
Answers.UserDisplayName: Pradeep Nayak
Body: <p>I'm trying but failing miserably to create a searchkit report that shows contacts in a particular mailing group who haven't opened a mailing in a specific number of months.
  Any help on how to build this greatly appreciated.</p>
  
Tags: <civireport><mailing>
Title: Report on opened emails
ThreadText: # Report on opened emails
  
  (Asked by: username_0 on 2024-04-30)
  
  I'm trying but failing miserably to create a searchkit report that shows contacts in a particular mailing group who haven't opened a mailing in a specific number of months.
  Any help on how to build this greatly appreciated.
  
  **Tags:** civireport, mailing
  
  
  ---
  
  ## 2 answers
  
  ### Answer by username_1 on 2024-04-30. Score: 1
  
  Can you do the opposite? e.g. if the mailing group is called "A", then make another group called B that is all contacts in the group who HAVE opened the mailing in X months. Then compute all contacts in A not in B.
  
  
  ---
  
  ### Answer by username_2 on 2024-04-30. Score: 0
  
  The snippet below should give you list of contacts with mailing name, date delivered and date opened and diff days
  
  ```
  [
    [
      "SavedSearch",
      "save",
      {
        "records": [
          {
            "name": "test",
            "label": "test",
            "form_values": null,
            "mapping_id": null,
            "search_custom_id": null,
            "api_entity": "Mailing",
            "api_params": {
              "version": 4,
              "select": [
                "id",
                "name",
                "Mailing_MailingJob_mailing_id_01_MailingJob_MailingEventQueue_job_id_01.contact_id.display_name",
                "Mailing_MailingJob_mailing_id_01_MailingJob_MailingEventQueue_job_id_01_MailingEventQueue_MailingEventDelivered_event_queue_id_01.time_stamp",
                "Mailing_MailingJob_mailing_id_01_MailingJob_MailingEventQueue_job_id_01_MailingEventQueue_MailingEventOpened_event_queue_id_01.time_stamp",
                "DATEDIFF(Mailing_MailingJob_mailing_id_01_MailingJob_MailingEventQueue_job_id_01_MailingEventQueue_MailingEventOpened_event_queue_id_01.time_stamp, Mailing_MailingJob_mailing_id_01_MailingJob_MailingEventQueue_job_id_01_MailingEventQueue_MailingEventDelivered_event_queue_id_01.time_stamp) AS DATEDIFF_Mailing_MailingJob_mailing_id_01_MailingJob_MailingEventQueue_job_id_01_MailingEventQueue_MailingEventOpened_event_queue_id_01_time_stamp_Mailing_MailingJob_mailing_id_01_MailingJob_MailingEventQueue_job_id_01_MailingEventQueue_MailingEventDelivered_event_queue_id_01_time_stamp"
              ],
              "orderBy": [],
              "where": [
                [
                  "scheduled_date",
                  "<",
                  "now"
                ],
                [
                  "Mailing_MailingJob_mailing_id_01_MailingJob_MailingEventQueue_job_id_01_MailingEventQueue_MailingEventDelivered_event_queue_id_01.time_stamp",
                  "IS NOT EMPTY"
                ]
              ],
              "groupBy": [],
              "join": [
                [
                  "MailingJob AS Mailing_MailingJob_mailing_id_01",
                  "LEFT",
                  [
                    "id",
                    "=",
                    "Mailing_MailingJob_mailing_id_01.mailing_id"
                  ]
                ],
                [
                  "MailingEventQueue AS Mailing_MailingJob_mailing_id_01_MailingJob_MailingEventQueue_job_id_01",
                  "LEFT",
                  [
                    "Mailing_MailingJob_mailing_id_01.id",
                    "=",
                    "Mailing_MailingJob_mailing_id_01_MailingJob_MailingEventQueue_job_id_01.job_id"
                  ]
                ],
                [
                  "MailingEventOpened AS Mailing_MailingJob_mailing_id_01_MailingJob_MailingEventQueue_job_id_01_MailingEventQueue_MailingEventOpened_event_queue_id_01",
                  "LEFT",
                  [
                    "Mailing_MailingJob_mailing_id_01_MailingJob_MailingEventQueue_job_id_01.id",
                    "=",
                    "Mailing_MailingJob_mailing_id_01_MailingJob_MailingEventQueue_job_id_01_MailingEventQueue_MailingEventOpened_event_queue_id_01.event_queue_id"
                  ]
                ],
                [
                  "MailingEventDelivered AS Mailing_MailingJob_mailing_id_01_MailingJob_MailingEventQueue_job_id_01_MailingEventQueue_MailingEventDelivered_event_queue_id_01",
                  "LEFT",
                  [
                    "Mailing_MailingJob_mailing_id_01_MailingJob_MailingEventQueue_job_id_01.id",
                    "=",
                    "Mailing_MailingJob_mailing_id_01_MailingJob_MailingEventQueue_job_id_01_MailingEventQueue_MailingEventDelivered_event_queue_id_01.event_queue_id"
                  ]
                ]
              ],
              "having": []
            },
            "expires_date": null,
            "description": null
          }
        ],
        "match": [
          "name"
        ]
      }
    ]
  ]
  
  ```
  
  > **Comments:**
  > **username_0**: Thanks @username_2, but how do I add that query to searchkit to run? Or can you do a screenshot of the settings you used to create the search.  
  > **username_2**: Go to searchkit page and click on Import and paste the above code  
  
  ---
  
  