The credit card division has been experiencing fluctuations in customer spending, payment behaviors, and default rates. Current reporting methods are fragmented and lack the ability to provide actionable insights. This project aims to consolidate data from various sources, enabling service providers to make informed decisions regarding marketing strategies, risk assessment, and customer engagement.
This project will enable the credit card division to make data-driven decisions, enhance customer satisfaction, and optimize financial performance.
- Step 1 : Create tables in the SQL according to the csv file. Import the CSV file to SQL
- Step 2 : Connect the SQL database to the Power Bi editor.
- Step 3 : Also since by default, profile will be opened only for 1000 rows so you need to select "column profiling based on entire dataset".
- Step 4 : Visual filters (Slicers) were added for four fields named "Quater", "Gender", "Income Type" , "Card Category" & "Week Start Date".
- Step 5 : Four card visuals were added to the canvas,representing "Sum of Revenue" , "Total Interest", "Total Income" & "Average Customer".
- Step 6 : Multiple Bar Charts were added to represent different measures such as Top 5 states, Age Group, Education Level & Revenue generation through many types.
- Step 7 : Tables and Line Chart for other important calculations that can be filtered through slicers or filters.
- Step 8 : Calculated column was created in which, customers were grouped into various age groups.
for creating new column following DAX expression was written;
Age Group = SWITCH(
True(),
customer_detail[Customer_Age]<30,"20-30",
customer_detail[Customer_Age]>=30 && 'customer_detail'[Customer_Age]<40,"30-40",
customer_detail[Customer_Age]>=40 && 'customer_detail'[Customer_Age]<50,"40-50",
customer_detail[Customer_Age]>=50 && 'customer_detail'[Customer_Age]<60,"50-60",
customer_detail[Customer_Age]>=60,"60+",
"unknown"
)
Snap of new calculated column ,
-
Step 9 :Calculated column was created in which, customers were grouped into three income groups Following DAX expression was written for the same,
Income Group = SWITCH(TRUE(), customer_detail[Income]<35000,"Low Income", customer_detail[Income]>=35000 && customer_detail[Income]<70000,"Med Income", customer_detail[Income]>=70000,"High Income", "unknown" )
Snap of new calculated column
Step 10 : Calculated column was created in which we got week number.
Following DAX expression was written for the same,
Week num2 = WEEKNUM(credit_card_detail[Week_Start_Date])
Snap of new calculated column
Step 11 : A new measure is created to calculate sum of revenue.
Following DAX expression was written to find total revenue,
Revenue = credit_card_detail[Annual_Fees]+credit_card_detail[Total_Trans_Amt]+credit_card_detail[Interest_Earned]
A card visual was used to represent this value
Step 12 : A new measure is created to get the Week On Week revenue(WOW) generation percentage.
Following DAX expression was written to find the percentage of WOW,
WoW_Revenue = DIVIDE([Current_week_revenue]-[Previous_week_revenue],[Previous_week_revenue])
A card visual was used to represent this value
- Step 13 : Two new measures were added for generation of Week On Week revenue. These two are used for the calculation of WOW.
Following DAX expression was written to find out the Current Week Revenue,
Current_week_revenue = CALCULATE(
SUM(credit_card_detail[Revenue]),
ALL(credit_card_detail),
credit_card_detail[Week num2]=MAX(credit_card_detail[Week num2])
)
Now the DAX expression for the Previous Week Revenue,
Previous_week_revenue = CALCULATE(
SUM(credit_card_detail[Revenue]),
FILTER(
ALL(credit_card_detail),
credit_card_detail[Week num2]=MAX(credit_card_detail[Week num2])-1
))
Double page report was created on Power BI Desktop & it was then published to Power BI Service.
Following inferences can be drawn from the dashboard;
Total Revenue generation (Male) = $30M (54.64 %)
Total Revenue generation (Female) = $25M (45.36 %)
Blue & Silver credit card are contributing to 93% of overall transactions.
Total transaction amount is $46M
TX, NY & CA is contributing to 68%.
1.1) 30.67 % customers pay by Chip.
1.2) 63.12 % customers pay by swipe.
1.3) 6.21 % customers pay by online method.
thus, maximum customers pay by swipe method.
2.1) 2.13 % customers belong to '20-30' age group.
2.2) 18.19 % customers belong to '30-40' age group.
2.3) 44.81 % customers belong to '40-50' age group.
2.4) 29.61 % customers belong to '50-60' age group.
2.5) 5.26 % customers belong to '60+' age group.
thus, maximum customers belong to '40-50' age group.
- Overall Delinquent rate is 6.06%
- Overall Activation rate is 57.5%
- Blue Card Category has the highest percentage of Average Utilization Ratio among others.
