Beyond the Coffee Order: Unlocking Customer Insights with RFM Analysis
Understanding Customer Behavior with RFM Analysis to Drive Strategic Business Growth
Background
A coffee shop wants to analyze customer behavior and enhance customer retention by understanding purchasing patterns using RFM Analysis. The business specifically wants to focus on customers who use cards for payment (excluding cash or other methods).
Business Goals
• Identify loyal customers who frequently purchase coffee using their cards.
• Detect customers at risk of churning (not purchasing for a long time).
• Segment new or infrequent customers to target them with promotions.
• Personalize marketing strategies based on customer spending behavior.
Impact
• Identify high-value customers and encourage repeat purchases.
• Detect and re-engage customers at risk of churning.
• Optimize promotions to maximize retention and revenue.
Context:
• In this case, I used Google Sheets to process the data since the dataset is not very large.
• The start date is 01/03/2024, and the end date is 01/22/2025.
1) Dataset:
I extracted the number of days from the “date” column to calculate the elapsed time since the customer’s last purchase, which will be used later for the recency score. Additionally, I extracted the hour from the timestamp to analyze the coffee shop’s peak activity hours, rounding up the hour if the minutes are more than 50 minutes.
2) Summarize Each Customer Data:
I created another sheet with a pivot table based on the previous sheet to summarize each customer’s data.
3) RFM Score:
There are several ways to calculate the RFM score. In this case, I manually binned the scores using the logic shown in the attached image. Manual binning allows for customization based on business logic rather than relying solely on statistical distributions.
Get Sabrina.’s stories in your inbox
Join Medium for free to get updates from this writer.
Previously, I used the PERCENTRANK() function to rank values from the pivot table, but I found that the ranking didn’t accurately reflect customer segmentation for this business objective. Alternatively, you can use the PERCENTILE() function to determine percentile-based thresholds for segmenting the data.
4) Create Customer Segmentation:
Use the UNIQUE() function to extract distinct RFM score values from the previous sheet. Then, segment customers based on your business objectives and the type of customer groups you want to define.
For this analysis, I created five customer segments: Top Customer, Potential Customer, Lapsed Customer, Hibernating Customer, New Customer, and Lost Top Customer.
5) Match The Segmentation with The RFM Score:
Back to the previous sheet, then use XLOOKUP() function to match the range of the RFM score with the Segmentation list created before. You can create a bar chart to visually compare different customer segmentations.
6) Create a Dashboard to Analyze Customer Behavior:
Build a dashboard to provide an overview of customer behavior and business performance. I also included a cohort analysis to visualize customer lifetime value.
For the interactive dashboard, you can visit my Tableau
