Marketing & Retail Analytics.

 Milestone 1.
Agenda -

Agenda of this project is to find the underlying buying patterns of the customers
of an automobile part manufacturer. based on the past 3 years of the
Company's transaction data and recommend them customized marketing
strategies for different segments of customers.

Executive Summary of the data-

We have received the 3 years data of automobile part manufacture . Consisting

  Problem Statement.

  Data Summary .

  Exploratory Analysis and Inferences.

Univariate analysis.
Bivariate analysis.
Multivariate analysis.
Time series & Trends in Sales.

  Customer Segmentation using RFM analysis.

  KNIME Workflow image .

  Output table head For RFM Analysis.

  Inferences from RFM Analysis and identified segments.

  Recommendation
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.

ORDERNUMBER : Order Number CUSTOMERNAME : customer

QUANTITYORDERED : Quantity ordered PHONE : Phone of the customer

PRICEEACH : Price of Each item ADDRESSLINE1 : Address of customer

ORDERLINENUMBER : order line CITY : City of customer

SALES : Sales amount POSTALCODE : Postal Code of customer

ORDERDATE : Order Date COUNTRY : Country customer

DAYS_SINCE_LASTORDER : Days_ Since_Lastorder CONTACTLASTNAME : Contact person customer

STATUS : Status of order like Shipped or not CONTACTFIRSTNAME : Contact person customer

PRODUCTLINE : Product line – CATEGORY DEALSIZE : Size of the deal based on Quantity and Item Price

MSRP : Manufacturer's Suggested Retail Price

PRODUCTCODE : Code of Product

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.
 Exploratory Analysis and Inferences.
Univariate analysis.

Using boxplot on sales & quantity order variable we have plotted univariate analysis. We can clearly see that outlier is present
there.
Also using histogram on sales variable we did univariate analysis.
For Categorical variable like product line we also did univariate analysis using bar plot.
We have noticed that the sales of classic cars products are high followed by vintage car product sales
Bivariate analysis.

Using boxplot on sales & product line variables we have plotted bivariate analysis. We can clearly see that outlier is present in
each product line category .
Using boxplot on sales & deal size variables we have plotted bivariate analysis. We can clearly see that outlier is present in Large
deal size.
In Pie chart we can see the larger portion of classic cars followed by vintage cars were as trains has the least demand.
Multivariate analysis.

MSRP, Price Each, status, sales & product line using these variables we did multivariate analysis. For this we used horizontal bar, tree map,
stack bar , scatter plot respectively.
As sales are high for classic cars the company has even sold below MSRP, there might be a chances that the company has given more
discounts to its customers. And vice versa for vintage cars were the company has sold above MSRP.
Ship, vintage car & train are been sold above the MSRP. By looking at the given data almost all the transactions are been shipped.
Time series & Trends in Sales.

Yearly, Quarterly, monthly, Weekly time series analysis & its trend are been shown. We observed that in Last quarter sales are
high as compared to other quarters. There is a seasonality seen.
 Summary of the inferences
 Using histogram on sales variable we did univariate analysis.

 For Categorical variable like product line we also did univariate analysis using bar plot.

 Using boxplot on sales , product line, deal size variables we have plotted bivariate analysis.

 And using MSRP, Price Each, status, sales & product line variables we did multivariate analysis
 After deriving univariate, bivariate & Multivariate analysis we can see there is a high demand of classic cars
followed by vintage cars and least is for trains.

 The sale are high for the last quarter of the year & we can see seasonality in it.

 The demand for classic cars are so high that the company has also sold the products below MSRP giving the
customers a good discount. However for vintage cars they have sold above the MSRP too.
 Customer Segmentation using RFM analysis ->

• Which tool used?

-> KNIME Tool is used here.

• What all parameters used and assumptions made?

-> As per your suggestion about ignoring the column "Days Since last order"  and create new column name Recency
as "[Max(order date) - order date)]"

We have assumed “01-06-2020“ as a reference date and created recency column.

If we can see the data there are same order number repeated for different product Code. So we can assume count of each
order number as frequency of an order number.

In SALES column we get sales amount for each transaction. We can use SALES parameter and using an assumption of sum of
aggregation we created a new column as Monetary .

Then created four different bin for each Recency, frequency & Monetary using percentile range(0,0.10,0.40,0.70,100).
Based on above 4 bin assumption we have considered 4 segments like High , Medium , Low and Churn.
KNIME Workflow image
Output table head For RFM Analysis.
 Inferences from RFM Analysis and identified segments

1. Our 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.
2. Our 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.
3.Customers on verge of churning :

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.
4.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.
Recommendation

• 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.