Skip to main content
Our overall goal is to have a report that shows Accounts that have had an overall score of Red for 60+ days. My first approach was to use Our "User Adoption" object, where the scorecard rating is being written each week. The issue here is that there are multiple records for each account; for example, Acme Corp will have 12 lines on the report for 12 weeks, each stating the account was "red." I can't figure out a way to get the report to show only those accounts where it's been red [i]continuously for 60+ days.





My next approach was to use Account Scorecard History, but I hit the same problem; there's no easy way I can see to look at the snapshot date and tell the report, "only show me accounts that have been continuously red in the snapshot for 8+ weeks." See screenshot for details (each colored block is the same customer, appearing multiple times).





I know I can do this in Salesforce with a workflow, but I'd rather not (we wouldn't get historical data). Is there a way to maybe write a date when the score changes, but also have it work for historical data? Thanks!





Hey Allison!





What about if you grouped by Account name and count the number of entries over the past 9 weeks.  If you time box this to always have a trailing 9 weeks, you could further restrict the report to only accounts that have 9 entries.  Anything less would mean they were had a score higher than an F at some point in that timeframe.  Make sense?
Hi Tim! I actually do have a report that's doing exactly this, but management didn't love it. I figured I'd ask for some potential other solutions.
Ah...  Perhaps visualize via a Column chart first and then show all of details upon drill down?
That could work. I think the biggest issue is that there's no easy way to see how many weeks the account has been red, just if it's been red over 60+ days. 
Yeah, it sounds like you could pivot the data and write into a new MDA object to get that kind of reporting visibility.  I'm not sure how you can do that with regular reporting on those objects...  Sorry I can't be of more help!
I appreciate the input!
Allison, if you have scorecard history enabled, you could report on the AVG of the score with a filter on the date of the update for the last 60 days. This would give you a sense if that customer was considered red over the 60 day period. 
Thanks, everyone. What I ended up doing was sticking to my original solution that mirrors Tim's suggestion, and made duplicates; one each for 90, 120, and 180 days, with the filters adjusted accordingly. It's not the best method, but it works 🙂





Meanwhile, my SFDC admins are making a workflow that writes the date to a field in Customer Info when the scorecard value changes. It's simple enough, but we won't be able to get useful data out of it for a bit as it needs to run for awhile.





I'd love to see a more native/intuitive way to do this in Gainsight, as we can't be the only company who's interested in this data. Thanks!

Reply