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.
2 Answers
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.
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"
]
}
]
]
answered Apr 30 at 13:07
-
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