To develop a comprehensive credit card weekly PowerBI dashboard that provides real time insights into key-performance-metrics and trends, enabling stakeholders to monitor and analyze credit card operations iffectively.
- Prepare/Download the given CSV files
- CREATE TABLES in SQL (MySQL / PostgreSQL)
- IMPORT CSV File into SQL.
![Loading_csv_data_into_MySQL](https://private-user-images.githubusercontent.com/157014747/326156120-2370db43-dd65-4ea5-908f-7cfe13a14dd2.png?jwt=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJnaXRodWIuY29tIiwiYXVkIjoicmF3LmdpdGh1YnVzZXJjb250ZW50LmNvbSIsImtleSI6ImtleTUiLCJleHAiOjE3MjYwOTU0MTIsIm5iZiI6MTcyNjA5NTExMiwicGF0aCI6Ii8xNTcwMTQ3NDcvMzI2MTU2MTIwLTIzNzBkYjQzLWRkNjUtNGVhNS05MDhmLTdjZmUxM2ExNGRkMi5wbmc_WC1BbXotQWxnb3JpdGhtPUFXUzQtSE1BQy1TSEEyNTYmWC1BbXotQ3JlZGVudGlhbD1BS0lBVkNPRFlMU0E1M1BRSzRaQSUyRjIwMjQwOTExJTJGdXMtZWFzdC0xJTJGczMlMkZhd3M0X3JlcXVlc3QmWC1BbXotRGF0ZT0yMDI0MDkxMVQyMjUxNTJaJlgtQW16LUV4cGlyZXM9MzAwJlgtQW16LVNpZ25hdHVyZT04NjU0YzM2M2FmODM4YTkyZDc0Y2FiNjcyN2M3YjA3NzExN2MzM2EzYmQxNTViOWZkNzVhMjY3NzA3ZjBiZjk5JlgtQW16LVNpZ25lZEhlYWRlcnM9aG9zdCZhY3Rvcl9pZD0wJmtleV9pZD0wJnJlcG9faWQ9MCJ9.7Dn4x0TVFBdM-Pr2cwzZGmyvj2gNoB5Rv8gq_4E0x88)
![Loading_csv_data_into_postgresql](https://private-user-images.githubusercontent.com/157014747/326156134-23916091-f6f9-4389-9cc1-ff21722f1061.png?jwt=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJnaXRodWIuY29tIiwiYXVkIjoicmF3LmdpdGh1YnVzZXJjb250ZW50LmNvbSIsImtleSI6ImtleTUiLCJleHAiOjE3MjYwOTU0MTIsIm5iZiI6MTcyNjA5NTExMiwicGF0aCI6Ii8xNTcwMTQ3NDcvMzI2MTU2MTM0LTIzOTE2MDkxLWY2ZjktNDM4OS05Y2MxLWZmMjE3MjJmMTA2MS5wbmc_WC1BbXotQWxnb3JpdGhtPUFXUzQtSE1BQy1TSEEyNTYmWC1BbXotQ3JlZGVudGlhbD1BS0lBVkNPRFlMU0E1M1BRSzRaQSUyRjIwMjQwOTExJTJGdXMtZWFzdC0xJTJGczMlMkZhd3M0X3JlcXVlc3QmWC1BbXotRGF0ZT0yMDI0MDkxMVQyMjUxNTJaJlgtQW16LUV4cGlyZXM9MzAwJlgtQW16LVNpZ25hdHVyZT00MGU2MmY1ZWQ0ODUxOGY2M2Y4NmM0MTYwOWQyZmQyZmJiOTIxNGEzM2ZkNTMwNWU4NDU1Y2YxZmQxOGYyN2JjJlgtQW16LVNpZ25lZEhlYWRlcnM9aG9zdCZhY3Rvcl9pZD0wJmtleV9pZD0wJnJlcG9faWQ9MCJ9.lolcygU2Cq3Smhgw8mh9ZZ_x5Y3Z5LbHYseQ28flcGQ)
-
Created (AgeGroup) column in (cust_detail) table
AgeGroup = SWITCH( TRUE(), 'credit_card_db cust_detail'[Customer_Age] < 30, "20-30", 'credit_card_db cust_detail'[Customer_Age] >= 30 && 'credit_card_db cust_detail'[Customer_Age] < 40, "30-40", 'credit_card_db cust_detail'[Customer_Age] >= 40 && 'credit_card_db cust_detail'[Customer_Age] < 50, "40-50", 'credit_card_db cust_detail'[Customer_Age] >= 50 && 'credit_card_db cust_detail'[Customer_Age] < 60, "50-60", 'credit_card_db cust_detail'[Customer_Age] >= 60, "60+", "unknown" )
-
Created (IncomeGroup) column in (cust_detail) table
IncomeGroup = SWITCH( TRUE(), 'credit_card_db cust_detail'[Income] < 35000, "Low", 'credit_card_db cust_detail'[Income] >= 35000 && 'credit_card_db cust_detail'[Income] < 70000, "Med", 'credit_card_db cust_detail'[Income] >= 70000, "High", "unknown" )
-
Created (week_num2) column in (cc_detail) table, for to get sorted whole number for original (week_num) column
week_num2 = WEEKNUM('credit_card_db cc_detail'[Week_Start_Date])
-
Created (Revenue) column in (cc_detail) table
Revenue = 'credit_card_db cc_detail'[Annual_Fees] + 'credit_card_db cc_detail'[Total_Trans_Amt] + 'credit_card_db cc_detail'[Interest_Earned]
-
Created (Current_Week_Revenue) and (Previous_Week_Revenue) MEASURE in (cc_detail) table
Current_Week_Revenue = CALCULATE( SUM('credit_card_db cc_detail'[Revenue]), FILTER( ALL('credit_card_db cc_detail'), 'credit_card_db cc_detail'[week_num2] = MAX('credit_card_db cc_detail'[week_num2])))
Previous_Week_Revenue = CALCULATE( SUM('credit_card_db cc_detail'[Revenue]), FILTER( ALL('credit_card_db cc_detail'), 'credit_card_db cc_detail'[week_num2] = MAX('credit_card_db cc_detail'[week_num2])-1))
-
Revenue Increased by around 28.8% ($55M to $57M) in last 53th week (clearly visible from below chart
-
Total Transaction Amount Increased from $45M -> $46M
-
Total Interest Earned Increased from $7.8M -> $8.0M
-
Male Customers are contributing more in Revenue in comparision to Female customers
-
Blue and Silver Card Holder Customers Contributing to
93%
of overall transactions
-
TX(Texas), NY(New York) and CA(California) states are contributing
72%
of overall transactions
-
Overall Activation Rate is
57.5%
-
Overall Delinquent Rate(the amount of debt/loan that is past due, i.e customers who are unable to pay their loan) is
6.06%
-
Customers who are graduated have contributed $22M / $55M =
40%
of overall transactions
-
Similary customers who are businessman have contributed $17.4M / $55M =
31.6%
of overall transactions