How would you create a report to show ALL accounts that have No Timeline Activity Logged

  • 2
  • Question
  • Updated 3 weeks ago
  • Answered
I am trying to build a report for leadership that will display ALL the accounts that have never had a Timeline Activity logged for. 

I've tried to build this on the Timeline MDA object as well as the Task object from SFDC (since the TL Activities sync to that).

However, I am having a tough time getting this data to display! I am not sure on how to set the filters (or if it even is possible) so that the report will show any of the accounts without a TL Activity. 

Any help would be much appreciated! 
Photo of Manmeet Dosanjh

Manmeet Dosanjh, Champion

  • 3,874 Points 3k badge 2x thumb

Posted 3 months ago

  • 2
Photo of Tim Schukar

Tim Schukar, Employee

  • 1,420 Points 1k badge 2x thumb
Hey Manmeet - 

Build a report with Show By: Created Date (with Max Function) and then by CSM (or whatever other field).  In the filters, have Created Date < X days.  That will show you what accounts have no activity logged in the last X days.

Thanks!

Tim
Photo of Manmeet Dosanjh

Manmeet Dosanjh, Champion

  • 3,874 Points 3k badge 2x thumb
Thanks for the quick reply Tim! I will give that a try
Photo of Shiv Kumar Katiyar

Shiv Kumar Katiyar, Employee

  • 1,326 Points 1k badge 2x thumb
Just to add-on to Tim's comment- Timeline Activity object in MDA will only have records when the activity is created for it. If the account doesn't exist in that object, then that means the activity has never been created.
Photo of Dan Ahrens

Dan Ahrens, Official Rep

  • 24,642 Points 20k badge 2x thumb
Keep in mind that Activity Timeline will only have records for Accounts for which at least one activity has been logged. 

One way to solve for this is to create a custom field on the Account object that is called "Total Timeline Entries" (or similar). 

Then use a bionic rule to merge two data sets: Fetch 1 - pull all account names and their SFDC ID, Fetch 2 - pull sum of activity timeline entries by SFDC ID. Merge those two data sets together (use left join with Fetch 1 in the left position so you'll have a record for each Account even if there are no activity timeline entries for that account). 

Then use a setup action to write the numerical sum value for each SFDC ID to the field you created at the outset. Schedule the rule to run daily. 

Finally, create your report on that object. 
Photo of Tim Schukar

Tim Schukar, Employee

  • 1,420 Points 1k badge 2x thumb
I'd love to see this using some sort of null set. 

E.g. 
- Data Task 1: Pull all active Accounts in Gainsight.
- Data Task 2: Pull max date of all accounts with Timeline Activity Postings
- (Inverse) Merge: 1 & 2 to get the null set of accounts without any postings.

I think this would be tremendously helpful in many other data needs too.
Photo of Manmeet Dosanjh

Manmeet Dosanjh, Champion

  • 3,874 Points 3k badge 2x thumb
Hi Dan,

Thanks for this suggestion! I think this could work for us. 

To clarify, when you say pull the sum of Timeline activities by SFDC ID. What exactly is going into the "Show" section and what goes into the "Filters"?


How do I sum the Activity Timeline? I don't see a clear unique identifier, like a Timeline ID or something that I can use the Max function on. 
I suppose, Subject or the Created Date would still work right? Since I am looking for accounts without any subject as that would equal no entries?
(Edited)
Photo of Dan Ahrens

Dan Ahrens, Official Rep

  • 24,642 Points 20k badge 2x thumb
Hi Manmeet, sorry, I meant to say "Count" instead of "Sum". My mistake. Let me know if that makes more sense. :)
Photo of Manmeet Dosanjh

Manmeet Dosanjh, Champion

  • 3,874 Points 3k badge 2x thumb
No worries! Thanks for the clarification. Looks like I got the same results using the Count function or the Sum function on Created Date of the TL Activity. This worked out great, thanks for the help Dan!
Photo of smathew4

smathew4

  • 100 Points 100 badge 2x thumb
Hi Dan,
Can you send over screenshots of how you have this rule setup? It makes sense to me theoretically, but I cannot get the Action on the merged dataset to save.
Photo of Sagan Sherlin

Sagan Sherlin, Champion

  • 3,802 Points 3k badge 2x thumb
We've actually created this.  In Report Building, we used Customers with a filter that says Total Activity = 0.  It works for us.  

We also had a question awhile back on how to report on accounts that hadn't been touched in X days - and got a really good reply on it!  I would have suggest using Modified Date, rather than Activity Date if your users update their Timeline Entries and not create new ones.

https://community.gainsight.com/gainsight/topics/help-is-it-possible-to-create-a-report-that-shows-a...