“Yes, I do already have a CRM, what else do you want me to do uh?”
That’s exactly the point. Having a CRM does not mean you know who your customers are. CRM is a simple, basic and non-exhaustive collection of data. It can tell you who purchased what and when, the client’s contact info and points of contacts. That’s it. But there’s nothing else to expect from it if you do not roll up your sleeves a little bit. Those data are the main ingredients of your success recipe.
Let’s see what we can do with them to cook our today’s special: Who are my most revenue-driving customers and who’s willing to come back to purchase?
“Ok, but what if I don’t have a CRM?” You’ll still be able to move on cos we won’t need a CRM but simple data every business has. Again, as mentioned, CRM is a tool to aggregate data and make them nice and clean for further usage.
Step 1 : Prepare your ingredients
Make sure to collect everything before getting started. We’d need those data to proceed:
- Customer ID or name (if you don’t have IDs yet you can go with the names, however we highly suggest having IDs instead for GDPR concerns if you had to transfer this analysis to someone else)
- Invoice Number : linked to a specific purchase the client made
- Invoice Date
- SKU Number or StockCode (SKU identification)
- Country, where the purchase has been made
- SKU description
- Quantity
- Unit Price
Not sure what we’re talking about? Here you go with an example right below.
No time to do it manually? Take a look at our free ready-to-use segmentation analysis:
Those are commonly called “sales data”. Yep. That’s right. Nothing linked to a CRM. And you have those somewhere if you’re selling something 😊
Furthermore, as a tremendous marketer, communication guru and social-holic, you’ve been multiplying communication media to share that your company is being the best in this world. And, of course, you know exactly whom client received which kind of communication method (who did I call? Who did I send a message to?), i.e. who has been targeted by your multiple campaigns. If that is not recorded on our side, this is not a problem, just skip the last step then. If you do have them, lucky you – you’ll get an even more accurate result. Simply add extra columns to the previous file (“phone”, “mail”,”event” etc.) and add “yes” or “no” if targeted.
Now take your data. You need to clean them. And for this, since we know you’re lazy we’ve prepared the pivot table for you to copy paste your dataset. Make sure to keep the same format.
Step 2 : Understand the method from the chef
Simple techniques are usually the best ones. In this example, we’ll be using the so-called RFM technique to sort it out. RFM stands for “Recency, Frequency and Money” which basically regroups three main characteristics:
- Recency - last time your client purchased from observation date
- Frequency - number of purchases
- Money - average basket value
Now, take your data. Add 7 new columns and name them as below (in our file, we already added them):
- R value
- R score
- F value
- F score
- M value
- M score
- Total score
We’ll be working on the recency first. In the recency cell, enter the following formula:
=DAYS(TODAY();D1)
In our example file column D is where you have the last purchase date for every customer.
By applying this formula, it will give the number of days between today’s date and the last purchase date. You can of course change “TODAY()” by any other date.
Now you have this information, sort those data ascending – the lowest is the number the better. It means the customer went shopping not so far ago.
Based on that, divide your total list into 5 groups – in our example, we have 4375 unique customers meaning there will be 875 customers per group. We call those “quintiles”.
The first quintile are the top 20% customers with the shortest recency. They deserve the highest score of 5. The next 20% (from line 876 to line 1750) will be granted a score of 4 and so on… here you have the recency scores.
Do the same for frequency (sum of all purchase invoices/unique baskets) and Money (total spending). In our example file, F and M values are being calculated using the pivot table but you can do that manually if needed. Then repeat the grouping/quintiles process and you’ll get the F and M scores.
Now that things have been sorted, sum up all those three scores and sort them, descending. You finally have the list of your top customers!!!
Step 3 : The final touch with extra-ingredients
Wanna put some extra toppings? Yes you can.
Remember those communication end points data? Add those interaction figures to your RFM table.
Then, sum them up for every customer.
Finally, perform a correlation analysis in Excel between:
- Total number of interactions VS RFM score
OR
- A single communication type VS RFM score
OR
- A single communication type VS R, F or M score only
To perform this analysis, Excel is making life easy – simply use the below formula :
=CORREL(table;table)
What does this result even mean?
The returned result is either positive and close to 1 (high correlation, positive impact), neutral around 0 (no impact) or negative and close to -1 (opposite correlation, negative impact)
In a business perspective, a positive impact between a communication mean and a consumer behavior can give you extra information to decide whether this method is useful.
Step 4 : Now what?
Congratulations. You made it through the recipe. And the result is pretty good.
However, you’ve just realized you had to make it over…. And over again. Cos yeah, clients come and go, and your database is evolving. So do the FRM analysis. You may do this every time your product lifecycle is coming to an end.
Furthermore, the manual calculation of the RFM has some additional drawbacks a regular human bran cannot tackle – the recency as we did here considers the lowest lag (time between the observation date and the last purchase date) as a positive criteria, leading to the highest score of 5. What if those customers were only one-timers? What about my repeaters coming every year, once? Frequency score is helping them but not that much.
Finally, this “human-made” RFM analysis will NOT tell you whena customer is willing to purchase again. It’s only giving you a global picture of who’s the most attractive for your business at the observation time. And not those who will be in the near future.
That’s why Ryax offers these analyses for free…
