ANS-0003 · SAVED SEARCHES & FORMULAS
How to Calculate Months Between a Customer’s Last and Second-to-Last Sale
Calculate the time elapsed between a customer's last and second-to-last sales to support marketing reactivation analysis.
Short answer
To calculate the months between a customer's last and second-to-last sale, use the MONTHS_BETWEEN function. This involves identifying the second-to-last transaction date using DENSE_RANK() partitioned by customer name and ordered by transaction date descending, then comparing it with the last sale date.
Scenario
Organizations often require a method to identify reactivated customers for marketing initiatives. Reactivation is defined as a new sale occurring after a period of 24 months or more with no prior sales activity. To determine this, it is necessary to calculate the time difference between a customer's last sale date and their second-to-last sale date.
Solution
To calculate the number of months between a customer's last sale and their second-to-last sale, utilize the following formula. This formula leverages MONTHS_BETWEEN to compare the customer's last sale date with the date of their second-to-last transaction, identified using DENSE_RANK().MONTHS_BETWEEN({customer.lastsaledate}, (CASE WHEN (DENSE_RANK() OVER(PARTITION BY {name} ORDER BY {trandate} DESC))=2 THEN {trandate} END))
Expert NetSuite Support
Need help with this NetSuite issue?
Saved Searches & Formulas consulting and configuration support