Hi @SophieHurst we actually just had a similar use case to yours where some of our CSM managers were looking for a report that showed clients who have not had a meeting logged in the last 180 days. At first, I tried building a report off the Activity Timeline object as you have done and setting filters for Meeting Type = Meeting and Activity Date < Last 180 Days (among other filters but these were the main ones). To my surprise, this wasn’t going to work, because what I ended up with was all Meeting activities logged prior to the last 180 days, so 181 days ago, 182 days ago, 183 days ago, etc. This isn’t what I was looking for, since I really wanted to show a list of accounts that haven’t actually had a Meeting logged in the last 180 days.
This may not be ideal for you or exactly what you’re looking for, but to work around this, we already had an existing “Date of Last Meeting” field which basically takes the max activity date of the Meeting activity logged by account. From there, I was able to set a filter in the report for Date of Last Meeting < Last 180 Days which gave me exactly what I wanted - a list of accounts that haven’t had a Meeting in the last 180 days - with the added benefit that we also get the actual date of their last Meeting logged within the report.
I’m thinking you could build a rule where you’d take the max activity date where Milestone = Executive Business Review, to find when the most recent EBR was for each account. You can save this value to a new field called “Date of Last Executive Business Review” (or something similar) which you’d load to in your rule action, and then you can use this new field in your report to get a list of accounts that haven’t had an EBR in the last 180 days.
The only caveat to this solution is that this won’t be a pretty bar chart like you’re looking for. You could make it a KPI report where you do a count distinct of the activity ID in the Show Me section and then add your filters as required. Then, you could add the details in the drilldown, such as your new Date of Last Meeting field and any other details you’d want to provide.
Again, I recognize that this may not be super ideal for your use case, but I hope this helps even just a little!
@SophieHurst Usually when you are looking for an absence of data, Data Designer is the way to go. So, you’d pull the Company data set, and then, the Activity Timeline dataset. Then, merge them together keeping all the records from the Company data set. Then, you could filter to find the companies without an activity of that type.
Adding on to what @kstim said - we did build a field on Company called Last EBR, and then, we use a rule to grab that date and add it. That makes it easier to report on without having to do a Data Designer. It’s really admin preference depending on how many fields you have on the Company, how you want to view it, etc.
Great idea with Data Designer @heather_hansen
Oof - thank you so much both - I suspect this is sorely above my technical level of expertise in Gainsight
If you want @SophieHurst we can jump on a call at some point and I can try and walk you through my solution!
@kstim that’s really kind of you! I would really appreciate that.
Does today at 2:30pm EST work for you? If so, what’s your email so I can add you to the invite!
@kstim so sorry - I’m BST so was finishing my day. It looks like we cross over between 9-12 EST though? sophiehurst@silktide.com
@SophieHurst No worries at all! I can either do today between now and 9am EST (which I think is 2pm-3pm BST) or I can do some time next week. Monday 7/15 I am available 9:30am - 10am EST or 11am - 12pm EST as well!
Today would be fantastic if you wouldn’t mind? Any time from now works for me! Thanks so much again!
@SophieHurst Cool I just invited you, you should get an email with the Zoom link!