Marketing & Retail Analytics.

 Milestone 1.
Agenda
Problem Statement
Executive Summary
Exploratory Analysis
Customer Segmentation using RFM
List of Contents
List of tables slides List of figures slides
Executive summary 5 Outliers check 8
RFM results 34 Distribution plot 10
Who are best customers 36 Year wise-month wise sale 12
Customer at verge of churn 37 Monthly sale w.r.t countries 13
Lost customers 38 Country wise sale 14
Loyal customers 39 Country wise yearly sale 15
Quarterly sale 16
List of figures slides Country wise quarterly sale 17
Order vs status count 24 Sale vs product line 18
Sale trend 25 Sale wise top customers 19
Quarter month wise top customer 26 Yearly sale wise top customers 20
Sale forecast 27 Deal size wise top customers 21
Heatmap 28 Monthly active customers 22
Pair plot 29 Deal size vs country 23
Problem Statement: An automobile parts manufacturing company has collected data of transactions for 3
years. They do not have any in-house data science team; thus they have hired you as their consultant. Your
job is to use your magical data science skills to provide them with suitable insights about their data and their
customers.
Agenda & Executive Summary of the data
• Contents of the presentation
• Problem statement
• About Data (Info, Shape, Summary Stats, your assumptions about data)
• Exploratory Analysis and Inferences
• Univariate, Bivariate, and multivariate analysis using data visualization
• Weekly, Monthly, Quarterly, Yearly Trends in Sales
• Sales Across different Categories of different features in the given data
• Summarize the inferences from the above analysis
• Customer Segmentation using RFM analysis (make 4 segments)
• Which tool used?
• What all parameters used and assumptions made
• Output table head
• If KNIME used, Workflow image to be put
• Inferences from RFM Analysis and identified segments
• Who are your best customers? (give at least 5)
• Which customers are at the verge of churning? (give at least 5)
• Who are lost customers? (give at least 5)
• Who are your loyal customers? (give at least 5).
Executive summary of Data

The data is about an automobile parts manufacturing company. They have provided the data collected of transactions for 3 years.

The data has 2747 entries (0 To 2746) of rows and 20 columns. The data has 1 datetime64 , 2 float64, 5 int64, and 12 Object data
types. There is no missing values present in the data set.

This data more or less reflects the purchasing behavior of customers in different categories . The company is into automobile part
manufacture, and they have different product line like Classic car , Motorcycle, plane, train, ship, Bus truck, vintage cars etc.

The data maintained each transactions entry as order number and for each order number maintained all required information like
customer identity details , and product details like price , quantity , product code, and sales for each customer.

We noticed that one order number has many different entries with different product codes.

Manufacturer's Suggested Retail Price(MSRP) for each product code is decided but we found that this is not matching with Price of
Each item & is inconsistent with MSRP.
The following are features:
Sales

From sale distribution we can see

sale is right tale skewed and may
have outliers
Exploratory Data Analysis

Outliers check:

Sale,quantity order priceeach and MSRP have outliers

right above the whisker
Percentage of outliers are very small
Distribution plot:
• Majority of the customers have ordered quantity between 20-40
• Price variable has a tail on the right side.. Price ranges from 26 to 252.
Data seems not be normally distributed in both the variables but there is no outliers in these
variables
Year wise monthly sale

Revenue generated for last 3 months for both 2018 and

2019 is quite high and November is a peak for both the
year
Monthly sale w.r.t countries
Monthly trend is observed across all countries. November
is showing peak sales for all 19 countries combined
Country wise sale
USA has highest sales followed by Spain , France, Australia, UK , Italy, Finland,
Singapore and Denmark Country wise Sales.
Country wise yearly sale

• Sales for USA, Spain, France and Canada have increased in

2019 when compared with 2018
• Australia, Norway and Singapore has a decreasing trend for
year 2019 when compared with 2018
• Sales for Switzerland, Japan and Ireland were 0 in 2018.
Quarterly sale

Highest sale for 4th quarter for both 2018 and 2019 years
Country wise quarterly sale

• Australia, Denmark, France, Germany, Norway and UK had a large difference

between sales in any other quarter when compared with Q4
• No sale observed in quarter 3 for Denmark ,Japan ,phillipines and UK.
• Quarter 4 sale is high in USA, France, Norway and Spain.
Sale vs product line

Classic Cars has highest share of total sales about 39%

Sale wise top customers

Top 5 Customers w.r.t Sales

are: 1. Euro Shopping Channel
2. Mini Gifts Distributors Ltd.
3. Australian Collectors, Co.
4. Muscle Machine Inc.
5. La Rochelle Gifts
Yearly sale wise top customers
• Top 10 customers are
different for each
year.
• 2 customers are same
for each year
Deal size wise top customers

As we can see Euro Shopping

Channel and Mini Gifts
Distributors Ltd are the best
customers for all deal size
product.
Monthly active customers
• September, October, November have been the high
months for both 2018 and 2019.
• Year 2020 has high number of unique customers for
Jan, March, and May.
 Deal size vs country

• Medium size products have highest sale.

• Large items have least sale.
Status vs orders

• Most of the items are shipped.

• Cancelled items are high in 2019.
• On hold products are high in 2020.
Sale trend

Sale trend:
Sale is at its peak value in November month of every
year
 Top customer quarter month wise

Euro shopping channel is the premium customer

Sale forecast

From sale forecasting we can see forecasting also estimate highest sale in
November month.
Heatmap

Heatmap:
From heatmap we can see
some of variables are highly
correlated
Pair plot

• Pair plot: sale is increased with price of each item.

• As order quantity increased sale also increased
 Observation from Data

• Medium deal has highest sale share and large deal has least share.
• Sale in November is in peak for both year.
• USA is the country which have highest sale followed by spain and France UK and etc.
• Sales Figures for USA, Spain, France and Canada have increased in 2019 when compared with 2018,Australia, Norway and Singapore has a decreasing trend for year 2019 when compared with 2018.Sales Figures for Switzerland, Japan and Ireland were 0 in 2018.
• Most of the customers have ordered quantity between 20-40. Price ranges from 26 to 252
• In USA, sale is highest in quarter 4.
• In sapin, France ,UK and Denmark sale is high in quarter 4.
• USA is the only country where sale increased from q1 to q4.
• No sale was observed in Quarter 3 for Denmark, Japan, Philippines and UK .
• Top 2 customers are same for each deal.
Observation
• Top 10 customers are different for each year.
• Top 2 customers are same for each year.
• These 89 unique customers are from 19 Countries.
• Shipped is highest for all years & High number of cancellations were seen in
2019.
• September, October, November have been the high months for both 2018 and
2019.. However Year 2020 has high number of unique customers for Jan,
March, and May. In fact this unique count is higher or equal to some of the
high months of 2018 like September and November of 2019.
• Classic cars has highest share in sale that is 39%.
• For Spain Q1 and Q4 have recorded highest sales
RFM ANALYSIS
 CUSTOMER SEGMENTATION USING RFM

• What is R FM : It stands for Recency, Frequency & Monetary value. It’s a marketing technique used to
quantitatively rank and group customers based on the recency, frequency and monetary total of their
recent transactions to identify the best customers and perform targeted marketing campaigns. The
system assigns each customer numerical scores based on these factors to provide an objective analysis.
Customer segmentation using RFM
• Parameters Used & Assumption Made: Following parameters were used.
• Customer Name
• Quantity Order
• Price of Each Item
• Order Date
• Sales in Amount
• KNIME Tool is used for RFM Analysis & Consumer Segmentation
• Assumption Made:
• Recency is the difference of Order Date from Current Date(6-2020 from order
date)
• Frequency is how order is placed from the customer by capturing variable
Days_Since_Last_Order
• Monetary Value is the product of Quantity Ordered x Price of Each Item
• Created 4 segment each for R,F & M with below distribution
• Sum Sales is used for calculating monetary value.
• All data has been summarized by customer name.
Workflow diagram
TOP BEST CUSTOMERS
 On basis on Recency, frequency & monetary we have grouped our top
customers. We have given the most significance to recency parameter as
these customers has recently purchased our products. Also according to RFM
model the most importance is given to recency. Hence we have kept it as our
first parameter for selecting top customers.
 For eg. Customer name -Euro Shopping Channel, they have recently made a
purchase, also has high frequency with a high monetary transaction
Customer RFM Score M (Sum Sales) F (Orders) R (Recency in days)
Euro Shopping Channel 444 912294.1 26 0
Mini Gifts Distributors Ltd. 444 654858.1 17 2
La Rochelle Gifts 444 180124.9 4 0
The Sharp Gifts Warehouse 444 160010.3 4 39
Souveniers And Things Co. 444 151571 4 2
Salzburg Collectables 444 149798.6 4 14
Danish Wholesale Imports 444 145041.6 5 46
Reims Collectables 444 135042.9 5 62
Diecast Classics Inc. 444 122138.1 4 1
 CUSTOMER ON VERGE OF CHURNING
HAVING LOW RECENCY
On basis on Recency, frequency & monetary we have grouped our Customers who are
on verge of churning. We should definitely focus on this group before we lose them
and try to convert them into our regular customers.

For e.g. Customer name Saveley & Henriot,Co – Their frequency is good with good
monetary value, but low recency made them stand in this group. If the company pays
more attention and fulfil their requirement, then we can easily turn them into our
regular customer and we can save them from churning out.

Customer name RFM score Monetary Frequency Recency

Land of Toys Inc. 244 164069.4 4 198
AV Stores, Co. 224 157807.8 3 196
Saveley & Henriot, Co. 124 142274.2 3 456
Rovelli Gifts 224 137955.7 3 201
Enaco distributor 222 13409.11 2 201
 WHO are lost customers?

On basis on Recency, frequency & monetary parameters we have grouped our Customers
who we’d lost. Their recency is very low and hasn’t made any purchase since long. So we
can say these are our lost customers. If taken feedback from them and fulfill their demand
we might bring them back to been a good customer.

CUSTOMER NAME RFM SCORE Monetary Frequency Recency

Double Decker Gift Stores, Ltd 111 36019.04 2 495

West Coast Collectables Co 111 46084.64 2 488
Signal Collectibles Ltd. 111 50218.51 2 476
Daedalus Designs Imports 111 69052.41 2 465
CAF Imports 111 49642.05 2 439
Who are loyal customers?

On basis on Recency, frequency & monetary we have grouped our loyal customers.
These customers have purchased multiple times with good monetary value. If we focus
more on this segment of customers, we can easily turn them into out top best
customers too. Also, in this segment we can see the customers for product line - classic
cars are many.
Customer name RFM Score Monetary Frequency Recency
Euro Shopping Channel 444 912294.1 26 0
Mini Gifts Distributors Ltd. 444 654858.06 17 2
Australian Collectors, Co. 344 200995.4 5 184
Muscle Machine Inc 344 197736.94 4 182
La Rochelle Gifts 344 180124.90 4 0
Recommendations:

• Using Recency, frequency & monetary parameters we have grouped our top , loyal, on the verge of churning and
lost customers. Customers with good recency has been our top customers were as we also have lost customer
lists.
• Customers on verge of churning can be saved and can be converted into a good buyer.

• RFM model is used for deriving the customers types like Loyal, top or best, on verge of churning & lost
customers.

• Recency, frequency & monetary parameters were widely used to bifurcate the types of customers.

• This
model can be very helpful to the company to maintain its sales and customers and can focus on how the
company has lost the customers & can take various actions to bring back them.

• It
is vital for the company to convert the customers who are on verge of churning into a regular customer or
atleast maintain them.

• And also how to increase the sales ratio can be identified.

Thank You