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

  • 2
  • Question
  • Updated 2 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,642 Points 3k badge 2x thumb

Posted 3 weeks ago

  • 2
Photo of Tim Schukar

Tim Schukar, Employee

  • 892 Points 500 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,642 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,084 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, Gainster (employee)

  • 22,434 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 Dan Ahrens

Dan Ahrens, Gainster (employee)

  • 22,434 Points 20k badge 2x thumb
That's our goal with Bionic reporting. 

The challenge with reporting on absence of data in a data set is that within that data set alone you don't know what you're missing if the identifier you're trying to use as your primary field in that report (like Account ID) is not present for all possible Accounts in the object you're reporting off of. 

It's essentially the kind of problem that you'd tackle in Excel using a VLOOKUP function to start with a list of all Account IDs and then use VLOOKUP to pull in activities from a second list that pertain to each Account ID and where VLOOKUP couldn't find a match, you'd use that for your filtered list to show missing data. 

But even in Excel, if you didn't have the master list of all Account IDs, you'd have the same challenge on being unable to report on missing data.
Photo of Tim Schukar

Tim Schukar, Employee

  • 892 Points 500 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,642 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, Gainster (employee)

  • 22,434 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,642 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 Sagan Sherlin

Sagan Sherlin

  • 2,732 Points 2k 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...