1

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.

0

2 Answers 2

2

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"
      ]
    }
  ]
]
2
  • 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. Commented Apr 30 at 14:46
  • Go to searchkit page and click on Import and paste the above code Commented Apr 30 at 15:55
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.

0

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.