Record Date when Scorecard Changes / Accounts Red over xx Days

  • 1
  • Question
  • Updated 2 months ago
  • Answered
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 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!

Photo of Allison Elmer

Allison Elmer

  • 402 Points 250 badge 2x thumb

Posted 2 months ago

  • 1
Photo of Tim Schukar

Tim Schukar, Employee

  • 1,420 Points 1k badge 2x thumb
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?
Photo of Allison Elmer

Allison Elmer

  • 402 Points 250 badge 2x thumb
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.
Photo of Tim Schukar

Tim Schukar, Employee

  • 1,420 Points 1k badge 2x thumb
Ah...  Perhaps visualize via a Column chart first and then show all of details upon drill down?
Photo of Allison Elmer

Allison Elmer

  • 402 Points 250 badge 2x thumb
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. 
Photo of Tim Schukar

Tim Schukar, Employee

  • 1,420 Points 1k badge 2x thumb
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!
Photo of Allison Elmer

Allison Elmer

  • 402 Points 250 badge 2x thumb
I appreciate the input!
Photo of Dan Ahrens

Dan Ahrens, Official Rep

  • 24,642 Points 20k badge 2x thumb
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. 
Photo of Allison Elmer

Allison Elmer

  • 402 Points 250 badge 2x thumb
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!