MAX your roll-ups! Are you keeping an eye on your lapsed customers? 👀 📆

The Problem:

Wouldn’t it be great if you could easily see the date of your last won opportunity directly on the account record page, instead of sifting through your opportunities? Even better, if you could identify an account as a current or past customer at a glance.

I don’t know about your business, but in my experience an account isn’t a customer just because they bought something from you a million years ago…

For example, in some businesses, if a customer hasn’t bought anything in the past 12 months they are considered a lapsed customer.

Past or lapsed customers are worthy of a lot of attention! Attracting new business is critical for all companies but it takes a lot of time and effort. Retaining or reconnecting with existing customers is cheaper, faster and often easier.

---

The Solution:

For this tutorial we’re going to combine a roll-up field with the MAX function to display our date on the account record. We’ll then create an image formula field to visually show if the account is a current or lapsed customer.

Navigate to Setup > Object Manager > {Accounts} > Fields & Relationships and create a roll-up summary field:

• Field Label = Latest Opportunity Won Date
• Summarised Object = Opportunities
• Roll-up Type = MAX
• Field to Aggregate = Close Date

Add filter criteria to ensure we only roll-up won opportunities:

• Field = Won
• Operator = equals
• Value = TRUE

Next, create an image formula field to display the customer category:

• Data Type = Formula
• Field Label = Customer Category
• Formula Return Type = Text
• Enter a description and help text (so your users understand the definitions for active and lapsed)
• Enter the following formula but use ‘Insert’ to ensure you use the correct API name:

IF( 
AND( 
NOT(ISBLANK(Latest_Opportunity_Won_Date__c)), 
TODAY() - Latest_Opportunity_Won_Date__c > 365), 
IMAGE("/img/samples/flag_red.gif","Red") & " Lapsed", 
IF( 
AND( 
NOT(ISBLANK(Latest_Opportunity_Won_Date__c)), 
TODAY() - Latest_Opportunity_Won_Date__c > 335, 
TODAY() - Latest_Opportunity_Won_Date__c < 365), 
IMAGE("/img/samples/flag_yellow.gif","Yellow")& " 30 days until lapsed", 
IMAGE("/img/samples/flag_green.gif","Green")& " Current")
)

Helpful hints:

• To avoid errors, I only want to calculate the customer category if they have been a customer! So I included an IF statement to check the ‘Latest Opportunity Won Date’ wasn’t empty.
• For my example scenario, a customer is lapsed if it has been more than 365 days since their last won opportunity.
• In the image formula the space before the word e.g. ” Lapsed” is intentional. I find that without adding a space the word appears too close to the image. 

---

The Result:

You can see the date of our most recently won opportunity as well as a customer category. You could keep it simple and stick with Current/Lapsed or make it a bit more informative and start warning users when the account is close to becoming a lapsed customer!

Example 1:

Example 2:

Example 3:

---

Gotchas & hints:

*Using a date field works well as long as the data is accurate enough. The ‘Close Date’ is no use to you if your users can close an opportunity without entering the correct date. As an example, consider adding a validation rule that gets your users to enter a close date within 7 days of today or whatever metric suits your business needs.

---

Useful links:

Salesforce Help: Formula Operators and Functions

Salesforce Help: Roll-Up Summary Field

Trailhead: Implement Roll-Up Summary Fields

---

Resources:

Customer Category Formula (Green/Red + Current/Lapsed):

IF( 
AND( 
NOT(ISBLANK(Latest_Opportunity_Won_Date__c)), 
TODAY() - Latest_Opportunity_Won_Date__c > 365 ), 
IMAGE("/img/samples/flag_red.gif","Red")& " Lapsed", 
IMAGE("/img/samples/flag_green.gif","Green")& " Current")

Customer Category Formula (Green/Yellow/Red + Current/Lapsed/30 days until lapsed):

IF( 
AND( 
NOT(ISBLANK(Latest_Opportunity_Won_Date__c)), 
TODAY() - Latest_Opportunity_Won_Date__c > 365), 
IMAGE("/img/samples/flag_red.gif","Red")& " Lapsed", 
IF( 
AND( 
NOT(ISBLANK(Latest_Opportunity_Won_Date__c)), 
TODAY() - Latest_Opportunity_Won_Date__c > 335, 
TODAY() - Latest_Opportunity_Won_Date__c < 365), 
IMAGE("/img/samples/flag_yellow.gif","Yellow")& " 30 days until lapsed", 
IMAGE("/img/samples/flag_green.gif","Green")& " Current")
)

---