RFM Customer Segmentation

Decoding Customer Behavior 

In the realm of data-driven decision-making, understanding your customers' behavior is crucial. One powerful technique for this is RFM analysis, which stands for Recency, Frequency, and Monetary analysis.

 

RFM is a data-driven customer segmentation method that helps identify different customer groups based on their transaction behaviors. 

​

Data Preparation

This dataset contains valuable insights about our customers, their orders, sales records, and transaction dates, the foundation of our RFM analysis. Together, we'll dive deep into this data to uncover the secrets it holds about our customers.

 

Recency Calculation—Unlocking Insights with Subqueries

Recency, the measure of how recently a customer has shopped, is a critical factor. We calculate it using subqueries, where we find the latest order date for each customer and compute the days since their last purchase. A lower recency score implies a more recent engagement—a goldmine of customer insights.

 

Frequency Calculation—Getting to Know Our Regulars

Frequency reveals how often our customers make purchases. It's a simple yet revealing metric. By counting the number of orders placed by each customer, we gain a deeper understanding of their shopping habits and loyalty.

 

Monetary Value Calculation—Measuring Customer Worth

Monetary value quantifies the total amount a customer has spent. It's where we sum up the sales values tied to each customer, offering a comprehensive view of their financial contributions to our business.

 

Crafting RFM Segments—The Power of CTE

With recency, frequency, and monetary value scores in hand, we embark on creating RFM segments. Common Table Expressions (CTE) guide us through this journey, aiding in the formation of unique customer segments.

 

Each RFM metric was divided into quartiles using the NTILE function. The result? A scale of 1-4 for each metric. This provided a lens to view each customer's engagement level and value in a nuanced manner.

​

Tagging Customers into Defined Buckets

Combining the quartile values, each customer got a unique RFM score, like a fingerprint of their behavior. Based on this, customers were grouped into categories:

•  Lost Customers

Those with high recency, low frequency, and low monetary values. These were once active but haven't engaged for a while.

​

•  Slipping Away:

These are customers who've made significant purchases but haven't been active recently. They once were loyal and might need a nudge to re-engage.

​

•  New Faces:

Recently onboarded customers, who haven't had much time to display frequency or significant monetary values.

​

•  On the Fence:

Regular customers who've been recent but haven't spent much. They might need enticing deals to spend more.

​

•  Engaged Shoppers:

Consistent in their purchases and are recent, but they don’t spend a lot per transaction. Offering them bundled deals might be effective.

​

•  Brand Loyalists:

They purchase regularly, recently, and spend a lot. They can be targeted for premium services or loyalty programs.

​

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Unlocking Product Affinities: Discovering What Your Customers Love

The final query explores which products are most frequently sold together. By scrutinizing orders where three items were purchased together and skillfully merging their product codes, we gain valuable insights.

​

​

​

​

​

​

​

​

​

​

​

​​

​

• Why Three Products?

While single and double item purchases are common, trios offer a unique look into product combinations that have a strong pull on the customer. By pinpointing these trios, businesses can strategize on how to create compelling bundle deals or promotions.

​

•  Concatenation of Product Codes: 

Instead of seeing the products as separate entries, the STUFF and FOR XML PATH functions in our SQL query let us view them as a combined entity. This ensures that we see these products as a 'set', making it easier to visualize which products often share the shopping cart.

 

•  Ensuring Accuracy with Joins:

The data join within the query assures that we are capturing accurate relationships. This means that every combination you see in the results is a verified trio of products that customers have chosen together.

​

•  Prioritizing the Most Loved Combos:

Through the ORDER BY clause, the results are sorted to spotlight the most popular product combinations right at the top. This hierarchy enables businesses to focus on the most impactful product pairs when making marketing or inventory decisions.

​

Unlocking product affinities is akin to tapping into the collective psyche of your customer base. When you understand what they love and how they prefer to shop, you're not just optimizing sales, but enhancing the overall customer experience.

​

As we wrap up this exploration, it's time to take a step back and see the bigger picture. Our journey through the data wouldn't be complete without a clear visualization to encapsulate our findings. So, here's our Tableau visualization—a crystallized representation of all our hard work and analysis. Dive in, and see the story the numbers tell.

​

Click on the dashboard to interact and delve deeper into the insights.

​

​

​

​

​

​

​

​

​

​

​

​

​