KPMG MODULE 2: RFM (POWERPOINT PRESENTATION)

Data analysis has become an essential aspect of marketing, especially with the increasing volume of data available to businesses. In the past, businesses used to make decisions based on their instincts or past experiences. However, with the advent of new technologies, such as RFM analysis, businesses can now make informed decisions by using data-driven insights. In this article, we will discuss how to use RFM analysis to determine the target audience using Excel.

TASK

Create a PowerPoint presentation which outlines the approach we will be taking to identify which of the 1000 customers Sprocket Central Pty Ltd should target, based on this dataset. Explain the three phases: Data Exploration; Model Development and Interpretation.

CONTENT

1. Introduction
2. Methodology
3. Model Development
4. Interpretation

INTRODUCTION

What is RFM Analysis?

RFM analysis is a statistical technique used to segment customers based on their purchasing behaviour. RFM stands for Recency, Frequency, and Monetary value. Recency refers to how recently a customer has made a purchase. Frequency refers to how often a customer makes purchases, and Monetary value refers to how much a customer spends on their purchases.

By analyzing these three metrics, businesses can group their customers into different segments and determine which customers are most valuable to them. This analysis helps businesses to target their marketing efforts and increase their revenue.

2. METHODOLOGY

Step 1: Data Preparation

The first step in RFM analysis is to prepare the data. The data should be in a tabular format, with each row representing a customer and each column representing a different attribute, such as the customer’s name, purchase date, and purchase amount.

Calculating Recency

To calculate recency, we need to determine how recently a customer has made a purchase. We can do this by subtracting the date of the customer’s last purchase from the current date. This will give us the number of days since the customer’s last purchase.

To calculate recency in Excel, we can use the DATEDIF function. The DATEDIF function calculates the number of days between two dates. We can use the TODAY function to get the current date and the MAX function to get the customer’s last purchase date.

The formula to calculate recency is as follows:

=DATEDIF(MAX(purchase_date_range),TODAY(),”d”)

Calculating Frequency

To calculate frequency, we need to determine how often a customer makes purchases. We can do this by counting the number of purchases a customer has made.

To calculate frequency in Excel, we can use the COUNTIF function. The COUNTIF function counts the number of times a value appears in a range of cells. We can use the COUNTIF function to count the number of purchases a customer has made.

The formula to calculate frequency is as follows:

=COUNTIF(purchase_date_range,customer_name)

Calculating Monetary Value

To calculate monetary value, we need to determine how much a customer spends on their purchases. We can do this by summing the purchase amounts for each customer.

To calculate the monetary value in Excel, we can use the SUMIF function. The SUMIF function sums the values in a range of cells that meet specified criteria. We can use the SUMIF function to sum the purchase amounts for each customer.

The formula to calculate monetary value is as follows:

=SUMIF(customer_name_range,customer_name,purchase_amount_range)

Data Exploration:

• Merging Customer Demographics and Transaction Sheets
• Creating a Comparison Date column for determining customer recency
• Constructing a Pivot Table to extract relevant data
• Deriving RFM values: Recency, Frequency, and Monetary value
• Creating Quartiles for segmentation
• I merged the Customer Demographics Sheet into the Transition Sheet using Vlookup. From the Gender column to Tenure.

Merged Sheet

RFM Table

Model Development:

• Utilizing Quartiles to categorize customers
• Calculating RFM scores based on weighted metrics
• Assigning customer titles (Platinum, Gold, Silver, Bronze)
• Creating a clear graphical representation of RFM segments

RFM Quartile

=QUARTILE.INC($B$4:$B$1048576,0)

• =IFS(B4>88,1,AND(B4<=88,B4>45),2,AND(B4<=45,B4>18),3,B4<=18,4)
• I used Quartile to find the difference for each category
• The RFM values were determined using =100*E4+10*F4+G4
• I assigned Customer Titles to each customer using the Quartile range as well. After this, I created an IF statement

RFM Value Quartile

=IFS(H4>411,”Platinum”,AND(H4<=411,H4>311),”Gold”,AND(H4<=311,H4>211),”Silver”,H4<=211,”Bronze”)

RFM Graph

Model Development: Crafting Insights through Statistical Models

Model development is a crucial phase in data analysis that encompasses various steps:

• Defining the problem: Identifying objectives and desired outcomes
• Data cleaning and preprocessing: Ensuring data quality and consistency
• Feature selection: Choosing the most relevant attributes for analysis
• Model selection: Opting for appropriate algorithms based on data characteristics
• Model training and testing: Iterative process to enhance predictive accuracy
• Model deployment: Implementing the model in real-world scenarios
• Performance monitoring: Continuous evaluation to maintain reliability

Interpretation

High-Value Customers:

• These clients constitute a small portion but wield significant value
• Focus on retaining these customers, as they are pivotal assets
• Implement strategies that enhance their loyalty and engagement

Mid-Value Customers:

• A substantial group with the potential to be elevated to High Value
• Invest in increasing retention and frequency
• Strengthen their connection with the brand and product

Low-Value Customers:

• The majority falls into this category
• Priority is to enhance the frequency and address any product or service-related issues
• The initial step is converting them to higher-value segments

Conclusion:

• RFM analysis provides actionable insights to guide marketing strategies
• Excel proves to be a powerful tool for conducting RFM analysis
• Accurate targeting based on Recency, Frequency, and Monetary value can lead to increased revenue and customer engagement

By effectively implementing RFM analysis and understanding its outcomes, Sprocket Central Pty Ltd can strategically optimize their marketing efforts, enhance customer relationships, and ultimately drive business growth.