0

I'm trying to use search kit to find contacts who have had no contact with our organisation in the last 18 months.

Building this as a contact search, and then searching on contact activities

So date of last activity was more than 18 months ago.

What's the best way to build the query?


edit


Think I've figured it, I've searched for 2 contact activities

enter image description here

2 Answers 2

2

You've basically got it. You want Contacts without Contact Activities if Activity Date < 18 months ago and Contact Role = Activity Target and with (required) Contact Activities if Contact Role = Activity Target. This gives you contacts who had at least one activity more than 18 months ago, but haven't had one in the last 18 months.

You can also, if this is something you do regularly and don't want to update the dates for each time, add an option to the relative date filter option group for the 'Last 18 months' (it should be ending_18.month, but test to confirm it works). Then you can use that as your date range for the without (Activity Date = Date Range Last 18 months) and for the with you can use Activity Date is between nothing and Last 18 months, which will find all activities up to 18 months ago.

4
  • To confirm. People we had an activity with at some point in the past. But who have not had an activity in the last 18 months. Wanted to see if I could use Searchkit to build it rather than rely on smart groups, and include/ exclude searches. Jun 22, 2023 at 6:52
  • 1
    Edited answer above for this.
    – Lars SG
    Jun 22, 2023 at 20:06
  • I am trying to do something very similar, with Event Dates, but it doesn't work (for me). The Join effectively returns a row for every Event linked to the Contact, excluding the recent ones. Just the recent Event is excluded, NOT all the rows related to that Contact. Or am I missing something? I can do it by Grouping on the Contact, adding a field containing the MAX Date, and then a HAVING clause to exclude on that maximum (latest) value. Unfortunately the UI for the HAVING clause does not allow the 'Before Now' construction for dates which I need to package it for my users. (v5.60)
    – user6274
    Jul 25, 2023 at 14:06
  • Maybe post another question with the specific search you are doing as I'm not quite sure I follow.
    – Lars SG
    Jul 25, 2023 at 15:06
2

I have done the equivalent for Contacts participating in events, based on the above but there are a couple more steps that weren't initially obvious.

  1. Set up the JOINs as in the original question, but with the second join as With(Optional). Using Without does not work as noted earlier.: enter image description here

  2. GROUP BY Contact Id: enter image description here

  3. Add Fields as required for the first Join, and one field returning the Event Date (in my case) for the Second Join. I used a MAX field here. enter image description here

  4. Add a HAVING condition to only include result rows - i.e. Contacts - where the second Join Date field is empty. This excludes the whole contact record, including those with any earlier dated Events, which is the requirement. enter image description here

This both solves the problem as stated, and allows use of relative dates which is a rider that I have added for my use-case, since the comparison occurs in the Join rather than the Having which does not support relative dates.

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.