In this post, I will share my Rakamin Academy Virtual Internship final project regarding a case study related to the problem of a bank manager.

Project Description

In this project, we are expected to solve the manager's problem by finding insights from case study where customer leaving credit card service from several datasets through data engineering and processing. This project mainly used SQL for querying the data and Tableau for data visualization.

Case Study:

A manager at a bank is annoyed by the increasing number of customers leaving their credit card service. They would really appreciate it if someone could figure out a customer profile so they could know where each customer is going so they could proactively go to the customer to provide better service and turn the customer's decision in the opposite direction.

From the case study above, form three types of data processing:

• Business Objectives
• Data Exploration
• Insights Presentation
• Conclusion and Solution

Business Objectives

From my perspective, here are some business objectives that need to be carried out to solve the problems in the case study.

Identify the profiles of customers or credit card customers.

The first business objective created is to identify the profiles of our customers or credit card customers. The profile of credit card users is very important to know. This is due to the exploration and search for insights on customer profiles so we can find out about the conditions of consumers and know the background of consumers who are starting to leave credit card services. For this purpose, finding out the profile of customers is done by exploring data and also visualizing data related to customer history.

Data exploration and visualization carried out are:

• Customer based on income and average credit limit, the first exploration is related to income and average credit limit. As we know, the higher a person's income, of course, the higher the credit limit that will be given, so this needs to be explored.
• Customers based on marital status and customer dependents, the second exploration is related to marriage and the customer's own responsibilities. This is because someone who is at least single and does not have many dependents has the possibility of not using a credit card, especially if they have a fairly high income. Therefore, it is worth exploring.
• Customer based on education and income category, the third exploration is the exploration of education level and income category. Education does not directly affect our credit card limit, but education can affect one's income. So this is worth exploring.
• Customer based on credit card and income, the final exploration related to the customer profile is the type of credit card the customer has and what is his level of income. From the existing dataset, there are blue, gold, silver, and platinum credit cards. Exploration was carried out to find out the relationship between the customer's credit card and his income.

Identify the amount of shopping or transactions made by the customer.

The second business objective is to identify purchases or transactions made by customers. This is done to see the transaction activity of customers based on the type of credit card and their income and will be measured through the ratio of credit card usage. Thus, the steps that will be carried out to achieve this business objective are to explore by utilizing the features or information needed to identify customer transactions. These features are credit limits, average shopping with a credit card, number of transactions, transaction frequency, and credit card usage ratio. Then, exploration is carried out using the type of credit card and income category as benchmarks.

Identify the relationship between the customer and the bank.

The last business objective is to identify customers' relationships with the bank. Like the problems in the problem, namely the number of customers who have started leaving credit card services. Therefore, it is necessary to identify the relationship between the customer and the bank, which is divided into two parts as before, namely based on the type of credit card and income. In this business objective, the features used are the period of contact with the bank, the number of months of inactivity, the total contact with the bank, and the ratio of credit card usage.

Data Exploration

The following is data exploration using SQL to find some insights that will be visualized later.

Customer based on their income and credit limit

SELECT Income_Category, COUNT(CLIENTNUM) as Total_Customer, 
ROUND(AVG(Credit_Limit),2) as Avg_Credit_Limit
FROM customer_data_history
GROUP BY Income_Category
ORDER BY FIELD(Income_Category,"Unknown","Less than $40K","$40K - $60K","$60K - $80K","$80K - $120K","$120K +");

Customer based on their marital status and dependent

SELECT m_db.marital_status, COUNT(*) as Status_Count,
ROUND(AVG(cdh.Dependent_count)) as AVG_Dependent,
cdh.Income_Category FROM customer_data_history cdh
LEFT JOIN marital_db m_db
ON cdh.Maritalid = m_db.id
GROUP BY m_db.marital_status, cdh.Income_Category
ORDER BY FIELD(Income_Category,"Unknown","Less than $40K","$40K - $60K","$60K - $80K","$80K - $120K","$120K +"), 
marital_status ASC;

Customer based on their education status and income category

SELECT e_db.education_level, COUNT(*) as Education_Count,
cdh.Income_Category FROM customer_data_history cdh
LEFT JOIN education_db e_db
ON cdh.Educationid = e_db.id
GROUP BY e_db.education_level, cdh.Income_Category
ORDER BY FIELD(Income_Category,"Unknown","Less than $40K","$40K - $60K","$60K - $80K","$80K - $120K","$120K +"), 
education_level ASC;

Customer based on their credit card category and income category

SELECT c_db.card_category, COUNT(*) as card_owner_total, cdh.Income_Category
FROM customer_data_history cdh
LEFT JOIN category_db c_db
ON cdh.card_categoryid = c_db.id
GROUP BY c_db.card_category,cdh.Income_Category
ORDER BY FIELD(c_db.card_category,"Blue","Gold","Silver","Platinum"),
FIELD(Income_Category,"Unknown","Less than $40K","$40K - $60K","$60K - $80K","$80K - $120K","$120K +");

Customer based on their credit card category and income category

SELECT Income_Category, COUNT(*) as Total_Consumer,
ROUND(AVG(Credit_Limit),2) as AVG_Credit_Limit, 
ROUND(AVG(Avg_Open_To_Buy),2) as AVG_Buying, 
ROUND(AVG(Total_Trans_Amt),2) as AVG_Transactions, 
ROUND(AVG(Total_Trans_Ct),2) as AVG_Transac_Freq, 
ROUND(AVG(Avg_Utilization_Ratio),2) as AVG_Credit_Util_Ratio 
FROM customer_data_history
GROUP BY Income_Category
ORDER BY FIELD(Income_Category,"Unknown","Less than $40K","$40K - $60K","$60K - $80K","$80K - $120K","$120K +");

Customer based on their credit card category and income category

SELECT c_db.card_category, COUNT(*) as Card_owner,
ROUND(AVG(cdh.Credit_Limit),2) as AVG_Credit_Limit, 
ROUND(AVG(cdh.Avg_Open_To_Buy),2) as AVG_Buying, 
ROUND(AVG(cdh.Total_Trans_Amt),2) as AVG_Transactions, 
ROUND(AVG(cdh.Total_Trans_Ct),2) as AVG_Transac_Freq, 
ROUND(AVG(cdh.Avg_Utilization_Ratio),2) as AVG_Credit_Util_Ratio 
FROM customer_data_history cdh
LEFT JOIN category_db c_db
ON cdh.card_categoryid = c_db.id
GROUP BY c_db.card_category
ORDER BY FIELD(c_db.card_category,"Blue","Gold","Silver","Platinum");

Customer based on their contact and relation with bank

SELECT cdh.Income_Category, c_db.card_category, COUNT(*) as card_owner_total,  
AVG(cdh.Months_on_book) as AVG_Months_on_book,
ROUND(AVG(cdh.Total_Relationship_Count)) as AVG_Total_Rel_Count,
ROUND(AVG(cdh.Months_Inactive_12_mon)) as AVG_Months_Inactive,
ROUND(AVG(cdh.Contacts_Count_12_mon)) as AVG_Contact_Count,
ROUND(AVG(cdh.Avg_Utilization_Ratio),2) as AVG_Credit_Util_Ratio 
FROM customer_data_history cdh
LEFT JOIN category_db c_db
ON cdh.card_categoryid = c_db.id
GROUP BY c_db.card_category,cdh.Income_Category
ORDER BY FIELD(c_db.card_category,"Blue","Gold","Silver","Platinum"),
FIELD(Income_Category,"Unknown","Less than $40K","$40K - $60K","$60K - $80K","$80K - $120K","$120K +");

Insight Presentation

The following is an explanation of the insight presentation that was encountered during the previous data exploration. I also made an interactive dashboard, which was made using Tableau and can be accessed at the following page: Tableau Dashboard .

From the graph above, it is known that the majority of customers have an income of under $ 40K. While the average credit limit is the most for those whose income is above $ 120K +. Based on this visualization, the majority of customers have a fairly low income, so the credit limit that can be given is also small for customers who have an income below $ 40K, namely those with a credit limit below $ 5K.

From the graph above, it is known that the majority of customers are undergraduates, followed by high school graduates, unknown, uneducated, still in college, postgraduates, and doctorates. Based on this visualization, it is also known that customers who are graduates are also the largest group of customers who have an income of less than $40K. In addition, it can be seen that education does not guarantee how much income a person can earn; even doctors themselves have an income of under $40K.

Marital status, average dependents, and customer income are visualized in the treemap on the side. Based on this visualization, it turns out that customers who are married, have at least 2 dependents, and earn under $40K are the largest group of customers. Followed by customers who are single and have an income under $ 40K and 2 dependents.

Customers are divided by credit card and income through the visualization on the side. Based on this visualization, the Blue type of credit card is the most popular, where all income groups of customers use this type of credit card even including those with income above $ 120K. Then followed the types of silver and gold. The Platinum card is the one with the fewest fans, so few that it doesn't even show up on the visualization.

The average number of transactions made by customers based on their type of income can be seen in the image above. Based on this visualization, the average transactions made by customers are more or less close for all income categories, with a significant difference only found in the number of customers.

The average transaction made by a customer based on the type of credit card can be seen in the image above. Based on this visualization, the average transaction is made by customers who have blue credit cards, but the average number of transactions made is fewer than those made by customers who have silver, gold, and platinum credit cards.

The average customer spending can be seen from the graph above. Based on this visualization, customers with the highest income certainly have a fairly high average spending compared to customers with other income categories. From this visualization, it can also be seen that all customer categories tend to spend within the credit limit provided.

The average customer spending by credit card can be seen from the graph above. Like the previous visualization, based on this one it can be seen that customers with a platinum type credit card have the largest average spending compared to other types of credit cards, even higher than the blue type, where previously the majority of customers with an income of $120K+ used this type of card. In addition, customers also tend not to spend beyond their credit limit.

The ratio of credit card use to total products held can be seen from the graph above. Based on this visualization, it can be seen that customers with blue credit cards are far more likely to use their credit cards, the majority of which are used by customers with incomes below $40K. Then, in terms of products held, it is also dominated by customers with incomes below $40K, followed by customers with incomes above $120K.

The average contact between the bank and the customer can be seen in the graph above. Based on this visualization, it can be seen that customers with platinum-type credit cards are the most frequently contacted by the bank. Then in terms of the period of contact with the bank, customers with an income of $ 80K-120K are the most frequent. Finally, the customers with the most inactivity are those with incomes below $40K.

Conclusion and Solution

From the business objectives, data exploration, and insight presentations that were carried out, several things and solutions can be concluded to overcome the problems in the case studies.

Conclusion

• The majority of customers have an income below $40K, where they are also the majority are married or single and have at least 2 dependents. The credit limit for this category of customers is also the lowest compared to other categories.
• The majority of customers still use blue credit cards; even customers with high incomes, such as those with incomes above $120K+, use this type of credit card instead of using higher types such as silver, gold, and platinum.
• The average transactions made are more or less close for all income categories.  However, when using the credit card approach, platinum credit cards have the highest average transactions, followed by gold and silver.
• In terms of credit card usage ratio, customers with blue credit cards are the ones who use credit cards the most, which are dominated by customers with incomes below $40K. Unfortunately, customers in this income category are also the most product owners, the least contacted by the bank, and also the most inactive.

Solution

• By increasing the credit limit for customers with lower middle income, it is expected to be able to attract the desire of these customers to use credit cards again.
• Because many customers still use Blue type credit cards, especially since they also have middle to upper income, we should provide more benefits to credit cards at higher levels so that these customers want to switch to using Gold, Silver and Platinum credit cards.
• Because blue credit cards are the most common but have the fewest average transactions, we can provide rewards or promos to customers with blue credit cards in order to increase the customer's desire to make transactions with credit cards.
• Because customers with incomes below $40K are the largest group and also the largest product owners, we as banks should make frequent contact with these customers, such as by conveying promos, benefits, and other things related to our credit cards.