The quicker your system tells you about a problem or an improvement, the quicker you can take action. In this post, you’re going to learn how to create/install an early warning system for your business (like a canary in a coal mine). The underlying framework is called Recency | Frequency | Monetary modeling or RFM, and it is the foundation for database marketing. While it’s possible to do this for any business, NetSuite saved searches make it an insanely great platform for applying the principles of RFM. Understanding your business through this lens will help you make smarter, quicker marketing decisions to either curb a decrease or spark/accelerate an increase in business. To get started, we’ll focus on the recency component of RFM.
Let’s start with a definition:
Active customers are those who’ve bought within the most recent time frame that’s natural for your products or services (for simplicity, I’ll focus on products in this post). So, if you sell products for incontinence, your active customers are probably on a monthly cycle. However, if you sell consumer electronics, an annual cycle makes more sense (I’ll focus on an annual cycle).
What kinds of questions need answers?
- Do you know how many active customers (those that bought within the preceding 365 days) you had at the end of last month?
- Did that number go up or down for the last 365 days ending today?
- What percentage of your active customers are brand new buyers (the lifeblood for growth)?
- What percentage of your active customers were old or inactive customers that hadn’t bought for more than a year but less than two, and are buying once again? Or hadn’t bought for more than two, but less than three years – and have purchased in the last 365 days?
- If the number of active customers has been going down consistently over the last several months, do you know why? Conversely, if the numbers are going up, can you explain?
- Was there a month where you saw a large reduction or increase in active customers? Do you know why? Has it happened before in that same month in prior years?
- If a routine increase, could you make it happen this month by performing an specific set of actions?
- If a routine decrease, what could you do to prevent it this year?
How do I get the answers?
You have to pull the answers from your customer database. And, you need to do this on a routine basis (for an annual cycle, I recommend monthly). Pulling the numbers once, will do little to no good. So, you need to automate the process as much as possible to ensure your own compliance. All of the questions asked above can be answered with six basic database queries (keep in mind, clues leading to actual solutions lie in the patterns/trends you’ll discover):
Here they are:
- Total Active customers (purchased within the last 365 days)
- New active customers (purchased for the first time in the last 365 days)
- Second year customers (purchased between 730 and 366 days ago)
- Third year customers (purchased between 1,095 and 731 days ago)
- Fourth year customers (purchased between 1,460 and 1,096 days ago)
- Catch-all customers (purchased more than 1,460 days ago)
NetSuite Saved Searches:
For all 6 saved searches, you’ll need to follow these steps to get started. Also, depending on your role, you may have to look around for “Saved Searches”, and for some roles, the option won’t be available. In the Web Store Manager role – hover over “Web Site” in the upper navigation bar – then hover over “Search/Updates” – then “Saved Searches” – then click “New”. From a Sale Manager role – hover over “Reports” – then “Saved Searches”, then “All Saved Searches”, then “New”. You’ll be given a choice of saved search types – select “Customer”.
Saved Search: Total Active Customers
Use the “Purchase Dates” filter and set the query string to “within” “relative” from 365 “days ago” to 0 “days ago”.
Saved Search: New Active Customers
Use the filter of “Purchase Dates” and set the query to “within” “relative” from 365 “days ago” to 0 “days ago”. Then, add another filter. Again, use “Purchase Dates” and set the query to “not before” “relative” 365 “days ago” (see example below). That will tell you how many new customers you had in the most recent annual cycle.
Saved Search: For Year Two and Beyond
The remaining saved searches follow the exact same patter, just replace the day ranges with those listed above. Again, use the “Purchase Dates” filter and set the query string to “within” “relative” from x “days ago” to n “days ago” (see ranges listed above). Then, add an additional query string. Using the “Purchase Dates” filter, set the second filter to “not after” “relative” n “days ago”. For example, the second year saved search should be “within” “relative” 730 “days ago” and 366 “days ago”. And, “not after” “relative” 366 “days ago”.
Keeping Track to Reveal Patterns
Now that you can pull the data – you might want to go back a year or two, pulling the same data for each month on the same day – So if it’s November 23rd – pull for October 23rd, September 23rd, and so on. If you put this information into a spreadsheet – you can use an overlay graphic to help you visualize the patterns. It’s actually fun!
If you liked this post, please share this on LinkedIn, Google+, Facebook, or Twitter – see the buttons above or just below. It takes very little time, but rewards us for our effort.
How to Create an Early Warning System to Protect Your Business by John-Scott Dixon is licensed under a Creative Commons Attribution-NonCommercial-NoDerivs 3.0 Unported License.