Final Project Data Modeling (Kalbe Nutritionals Data Scientist Project Based Internship Program)

As a Data Scientist at Kalbe Nutritionals, I got a new project from the inventory teamand marketing team.

1. I was asked to predict the quantity of products sold so that the inventory team could make sufficient daily inventory stocks.
2. I was asked to create customer segments so that the marketing team could provide personalized promotion and sales treatment.

Tools:

• Python
• Jupyter Notebook
• Tableau
• Dbeaver
• PostgreSQL

Challenges that I have to work on:

1. Perform Exploratory Data Analysis in dbeaver
2. Create a dashboard in tableau
3. Create a predictive time series model using ARIMA
4. Create a customer segmentation model using KMeans

Exploratory Data Analysis in dbeaver

1. Berapa rata-rata umur customer jika dilihat dari marital statusnya?

output:

2. Berapa rata-rata umur customer jika dilihat dari gendernya?

output:

3. Tentukan nama store dengan total quantity terbanyak!

output:

4. Tentukan nama produk terlaris dengan total amount terbanyak!

output:

Daily Product Quantity Prediction Using ARIMA

The steps for forecast quantity of product are

1. Merge all dataset: merges two DataFrames df_customer and df_transaction based on the CustomerID column. It means rows with the same value in the CustomerID column from both DataFrames will be merged into one row.
2. Checking missing values: there are not missing values here.
3. Spliting Data: 80% used for training and 20% used for testing
4. Stationary testing

   The Augmented Dickey-Fuller (ADF) test results are as follows:

• ADF Statistic: -19.0187 (Highly negative, indicating stationary data)
• p-value: 0.0 (Very strong evidence that the data is stationary)
• Conclusion: The data used is stationary at all examined levels of significance.

5. Forecasting daily product quantity

Customer Segmentation Using KMEANS

The steps for forecast quantity of product are

1. Merge all dataset
2. Checking missing values
3. Determining the optimal number of clusters in a KMeans clustering algorithm using Elbow Method

Best cluster is 4.

4. Clustering using KMeans

Clustering interpretation:

1. Cluster 0 - Loyal Customer

   There are 114 customers (25.5%).

   Customers in this group are those who make transactions with high quantity and total spending, namely:

   a) average transactions are around 13 transactions,

   b) average quantity of products purchased is about 49 units, and

   c) the average amount of money spent by customers in this category is around 436K.

Bussiness recommendation:

• Cross-Selling Related Products: Loyal customers can be offered special deals, such as "Buy 3 Packs of Cheese Sticks, Get 1 Pack of Choco Bars with a 15% Discount."
• Seasonal-Based Promotions: During major holidays, the company can offer exclusive discounts on their loyal customers' favorite products, such as "Save 15% on Cheese Sticks purchases during Christmas."
• Multiple Discount Programs: If loyal customers reach 10 transactions, the company can provide an extra 10% discount on their next purchase with certain terms and conditions.

2. Cluster 1 - New Customer

   There are 93 customers (20.81%)

   Customers in this group are those who make transactions with the lowest quantity and total spending, namely:

   a) average transactions are around 7 transactions,

   b) average quantity of products purchased is about 24 units, and

   c) the average amount of money spent by customers in this category is around 208K.

Bussiness recommendation:

• Budget Packages: When new customers first shop, they can purchase a "Welcome Package" containing three top-selling products at a cheaper price than the total individual product prices.

• Referral Program: Offer a 20% discount on the next purchase to new customers if they refer friends or family who make their first purchase.

• Cross-Selling Related Products: When new customers purchase one of the top three selling products, offer an additional "lowest selling product with a 20% Discount" as a related product.

3. Cluster 2 - Regular Customer

   There are 180 customers (40.27%)

   Customers in this group are those who make transactions with stable and moderate quantity and total spending, namely:

   a) average transactions are around 10 transactions,

   b) average quantity of products purchased is about 37 units, and

   c) the average amount of money spent by customers in this category is around 326K.

Bussiness recommendation:

• Cross-Selling Related Products: When regular customers purchase one of the top 5 selling products, offer a 10% discount for purchasing one of the frequently bought products together.

• Seasonal-Based Promotions: During major holidays, offer a "Budget Package" containing 2 top-selling products and 1 lowest-selling product with a specific discount.

• Multiple Discount Programs: Provide an additional 5% discount if regular customers reach the tenth transaction in a month.

4. Cluster 3 - VIP Customer

   There are 60 customers (13.42%)

   Customers in this group are those who make high quantity transactions with large purchases and high total spending, namely:

   a) average transactions are around 16 transactions,

   b) average quantity of products purchased is about 61 units, and

   c) the average amount of money spent by customers in this category is around 572K.

Bussiness recommendation:

• Cross-Selling Related Products: VIP customers who purchase one of the 3 lowest selling products can be offered exclusive deals, such as "Get a 25% Discount on purchasing one of the top selling products."

• Multiple Discount Programs: If VIP customers reach a certain total spending within a year, provide a 15% discount on their next purchase.

• Seasonal-Based Promotions: During the holiday season, offer exclusive premium Thai Tea products for VIP customers.