Question

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


Badge
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! 

13 replies

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
Badge
Thanks for the quick reply Tim! I will give that a try
Userlevel 4
Badge +1
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.
Userlevel 7
Badge +2
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. 
Userlevel 7
Badge +3
I wish there was an easier way to report on accounts that are missing data that doesn't require creating custom fields, etc.  Will the Report Builder evolution ("Bionic" Reports) accomodate this?
Badge +3
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...
Userlevel 7
Badge +2
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.
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.
Badge
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?
Userlevel 7
Badge +2
Hi Manmeet, sorry, I meant to say "Count" instead of "Sum". My mistake. Let me know if that makes more sense. 🙂
Badge
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!
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.
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.

Reply