1. You are working for a retail store where you find some customers that are not shopping
with the store for some time. Now, the management has decided to give a ‘Win-Back’
offer to 100 most deserving customers and had asked you to identify those 100 customers.
Please refer to file “Q1 Data – Div A.xlsx”.
Your answer will contain the following information:
• The list of 100 customers (identified with their id).
• A clear business justification of why these 100 customers were chosen.
• The anticipated business that these 100 customers are likely to give (make suitable
assumptions).
Business Objective:
With the increase in competition day by day, the organisation observed that some of
the customers are not returning to the store to purchase again. The management
knows that not only acquiring new customers are much costlier but there is a higher
chance(60-70%) to sell a product to an existing customer as compared to a new
customer(5-20%).
(As per this report – Cost of acquisition of a new customer can be 5 times more costlier than
retaining a new one)
Keeping all this in mind, the management decided to roll out a win-back offer to
some of its customers who have not purchased from a very long time but might to do
if an offer is given to them.
The question now lies – Whom should the management give these offers to ?
Analytics Objective:
To Target those customers who have not shopped from our store from a very long
time and have higher chance of shopping again.
Also, to find an estimate of the total anticipated business that can be made by rolling
out the offer to the specific set of people.
, RFM Analysis
Objective: To find out the 500 most compelling customers who are most likely to respond to
a new offer.
PART I: Process followed to conduct RFM Analysis
The following steps are taken to get the RFM Scores:
1. All the unique customer has been identified and is named as Customer_ID (1-3000
entries)-Column G in Excel Sheet
2. Number of transactions made by each customer is calculated - Column H in Excel
Sheet
3. The Most Recent Date of purchase is calculated from the Date column - Column I in
Excel Sheet.
4. The Start Date column is calculated by finding out the First date of purchase for each
customer from the Date column – Column J in the Excel Sheet.
5. The Years Column is calculated by finding the different between current date(21-12-
2020) and the Start Date - Column K in Excel Sheet
6. The Monetary Value column is calculated by taking the average spent made by each
customer per year - Column L in Excel Sheet
7. The Frequency column is calculated by dividing Total Number of Transactions
(Column H)by total number of years (Column K). – Column M in Excel Sheet
8. The Ranks of Recency, Frequency and Monetary value is calculated using the RANK
function from the Most Recent Date, Frequency and Monetary value columns
respectively. - Column N,O,P in Excel Sheet
9. The Ranks obtained are then grouped into 1-5 score based on the following table :
Ranks Score
1 1
601 2
1201 3
1801 4
2401 5
(Legend: 1-best & 5-worst)
10. The final output is the Recency( R ), Frequency (F) and Monetary (M) scores for each
customer - Column Q, R, S in Excel Sheet
11. We have also calculated Average Value per order (per year) by dividing Monetary
Value by Frequency - Column U in Excel Sheet (i.e. Average Order Amount)
with the store for some time. Now, the management has decided to give a ‘Win-Back’
offer to 100 most deserving customers and had asked you to identify those 100 customers.
Please refer to file “Q1 Data – Div A.xlsx”.
Your answer will contain the following information:
• The list of 100 customers (identified with their id).
• A clear business justification of why these 100 customers were chosen.
• The anticipated business that these 100 customers are likely to give (make suitable
assumptions).
Business Objective:
With the increase in competition day by day, the organisation observed that some of
the customers are not returning to the store to purchase again. The management
knows that not only acquiring new customers are much costlier but there is a higher
chance(60-70%) to sell a product to an existing customer as compared to a new
customer(5-20%).
(As per this report – Cost of acquisition of a new customer can be 5 times more costlier than
retaining a new one)
Keeping all this in mind, the management decided to roll out a win-back offer to
some of its customers who have not purchased from a very long time but might to do
if an offer is given to them.
The question now lies – Whom should the management give these offers to ?
Analytics Objective:
To Target those customers who have not shopped from our store from a very long
time and have higher chance of shopping again.
Also, to find an estimate of the total anticipated business that can be made by rolling
out the offer to the specific set of people.
, RFM Analysis
Objective: To find out the 500 most compelling customers who are most likely to respond to
a new offer.
PART I: Process followed to conduct RFM Analysis
The following steps are taken to get the RFM Scores:
1. All the unique customer has been identified and is named as Customer_ID (1-3000
entries)-Column G in Excel Sheet
2. Number of transactions made by each customer is calculated - Column H in Excel
Sheet
3. The Most Recent Date of purchase is calculated from the Date column - Column I in
Excel Sheet.
4. The Start Date column is calculated by finding out the First date of purchase for each
customer from the Date column – Column J in the Excel Sheet.
5. The Years Column is calculated by finding the different between current date(21-12-
2020) and the Start Date - Column K in Excel Sheet
6. The Monetary Value column is calculated by taking the average spent made by each
customer per year - Column L in Excel Sheet
7. The Frequency column is calculated by dividing Total Number of Transactions
(Column H)by total number of years (Column K). – Column M in Excel Sheet
8. The Ranks of Recency, Frequency and Monetary value is calculated using the RANK
function from the Most Recent Date, Frequency and Monetary value columns
respectively. - Column N,O,P in Excel Sheet
9. The Ranks obtained are then grouped into 1-5 score based on the following table :
Ranks Score
1 1
601 2
1201 3
1801 4
2401 5
(Legend: 1-best & 5-worst)
10. The final output is the Recency( R ), Frequency (F) and Monetary (M) scores for each
customer - Column Q, R, S in Excel Sheet
11. We have also calculated Average Value per order (per year) by dividing Monetary
Value by Frequency - Column U in Excel Sheet (i.e. Average Order Amount)
