One day I was chatting with one of our Customer Success Managers on HOW he does his job of tracking customers to engage with. He told me through many reports and talking with our TAM’s and AE’s. I thought that was silly as while we don’t have a dedicated CSM tool the basics of it didn’t seem to hard to build into Salesforce.
We did a bit of BPR over a delicious Reuben (I would argue is the best sandwich ever made) to pinpoint down some of the key account health indicators. He came to the conclusion that in our Org these things were key
- Active Contacts With A Key Role – He saw it as a problem if we had an account with no active contacts with a Key Role set.
- Days Since Last Activity Logged (Not by an automated system [ex Marketo, Pardot, Outreach.io, Etc…]) – If there had been no logged communication within the last 90 days this was an issue as the AE/CSM/TAM should be reaching out at least once a quarter.
- Escalated Cases in the last 90 Days – He saw this as a problem if there were more than a set number of Escalated Cases in the last 90 Days.
- Cases in the last 60 Days – He saw this as a double sided issue, if there were to FEW cases or to MANY cases this could indicate a problem. No cases as they maybe were not using the product enough or too many cases as they were having issues with the product.
- Utilization (of our product) – This was how much actual usage / allowed usage. If this number was too low anytime after 6 months from the start date this would be an issue.
- Adoption (of our product) – This was user adoption of the platform more or less how many users were using the product. Too little would show that there were adoption issues and a combination of Utilization and Adoption can show a variety of different issues.
These were all built to be updated in real-time so when one of them flipped it could alert the correct person and once they did what needed to be done they would auto-unflip. This allowed the CSM to run exception reporting of looking for accounts that had one of these 6 fields out of the range they were looking for, in place of running reports and putting them together in excel trying to FIND accounts that were having issues. Now the System will find those accounts and mark them for us =)
To solve for the Active Contracts with a Key Role I used DLRS. I created this DLRS to rollup a count to a numeric field, then built a formula field that would be TRUE if that number field was 0 and FALSE if it was above 0. This allowed for a checkbox to display on the page layout for a simpler experience for the user (Problem or No Problem), but still allowed me to have workflow rules fire off the numeric value that DLRS rolled up.
Then we create the formula field to hold our business logic. In this case it is easy, if it is 0 then we want the box to be checked and if it is any other value we don’t want the box to show as true.
For the days since last activity logged I use process builder to populate the date of the last activity onto the Account. The trick is we want to exclude all automated activity logs like Marketing Automation or Automated Sales Enablement.
Here is where we check to see if the Task was created by an automated system. We also only want to calculate this for Customer Accounts so we first check to make sure the Account is a Customer. Then we make sure the record wasn’t created by an Automated System, then we also check to make sure the owner of the task wasn’t an Automated System. Next is to make sure the activity is Closed and that the ActivityDate is less than Jan 1, 3000 this is due to a weird bug with Salesforce for Outlook somehow inserting really weird out of range values so this prevents PB from erroring out when those random records get inserted.
Then for updating of the records we just grab the Account from the Task and stamp a date field with the tasks ActivityDate. In this case the field is named DLRS Last Activity Date as I originally planned to solve this with DLRS but PB ended up being the better tool for this use case.
To make it simple for people to see how many days ago that Actiivity Date was a Formula field is used.
The next two are handled with DLRS and they just roll directly into the field we display.
Then we just expose these direct fields as there is no need to mask them. The one thing we have to be sure of is to set up a Schedule Calculate so that way if no new cases or escalations are created in 60 or 90 days the roll-up will re calculate.
The last 2 fields we have created an Object called Utilization and Master-Detailed to Contracts that is then attached to Accounts. We then via Informatica from our BI Warehouse bring over the calculated Adoption and Utilization values into it. We then just use DLRS to bring the most recent values onto the Account.
For ease of use I used 2 formula fields to convert the proportion to a percentage as 25% is easier to read than 0.2514. I also added a long text field for No Longer At Risk Reason this way an account rep or CSM could enter why the account might have at risk factors but not really be at risk then in the reports you can say you only want to pull accounts where that field is Null.
Then the final result ends up looking like this.