Customer Account Health Indicators

      No Comments on Customer Account Health Indicators

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,, 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.

Automated Error Alerts and Mass Delete Error Records

I have seen people posting about their scheduled rollups failing and them not finding out about it until later.  Well DLRS has this nice section called Lookup Rollup Summary Logs and it is just a table that holds records of failed scheduled rollups.  This is good news it means we can create a custom report type and then build a exception report so that whenever one of these fails it will trigger and alert us.

First go to Setup and look for Report Types

Then click on New Report Type

Now we need to name the new report type, give it a description and then where we want it to be stored.  I store it under Admin Reports as I am most likely the only person in the Org who will care about DLRS errors 😉  (Pro-Tip make sure you deploy it once you have it made.  You can do that now with the radio buttons at the bottom or you can do it after you save the report type. Just don’t forget to Deploy it!)

Next we only need the main table so we don’t need to set up any table joins so we just click on Save.

Now when we go to Reports we can look for the DLRS Error Report Type

Click to create a new report.  And then build your Report. I keep my report pretty bare bones with just the Log Name so I can go directly to the records.  Feel free to set up as much or little information in your report.  Then I want it to show me ALL records as anytime there are DLRS errors and I fix the problem I clear the table so it doesn’t end up taking up storage space.  If you don’t clear your table you could say that it should only look at records created Yesterday or some specific time frame.

Next is we want to subscribe to the report.

For me I set it up to run every weekday at 8am and if the record count is ever greater than or equal to 1 it will shoot me an email and I will know that one of my scheduled DLRS’s failed.  Click save and you are good to go.

The second part is the two ways I clear my DLRS Error Log Table.  First is I built a small Javascript button (RIP JS Buttons in Lightning.  I think this can be done with Quick Actions but I have not had time to look into it.  I’ll update this post later once I figure out how to do this same trick in a Lightning Friendly way) and added it to the list view of the DLRS Error Page.  To do this you’ll want to go to Setup > Create > Objects and look for Lookup Rollup Summary Log.  Go into the object and scroll down to the buttons and click on create a new button.  Below is the code I use.


var url = parent.location.href;

var records = {!GETRECORDIDS($ObjectType.dlrs__LookupRollupSummaryLog__c)};

if (records[0] == null) { //if the button was clicked but there was no record selected

alert(“Please select at least one record to Delete.”); //alert the user that they didn’t make a selection

} else { //otherwise, there was a record selection
alert(“Deleting ” + records.length + ” Records. Press Ok and wait for the page to reload.”);
var result = sforce.connection.deleteIds(records);
parent.location.href = url; //refresh the page

Once you have your new button you’ll just need to click on the Search Results under Search Layouts and add the button.  Then it should appear and you can use the button to delete many at a time in a list view.

The other way I clear the table if there are TONS of error log records is export all of the record ID’s with Dataloader then load them back in as a delete command with dataloader.  You can use the Bulk API so it can clear the table pretty quickly.  Until next time when I hope to finish writing up how to create a simple account health warning system with Process Builder, DLRS and some workflow rules.