About the Dataset:

• It contains 9 columns and 26052 rows of credit card transactions.
  The 9 rows are as follows :
• Index : Serial No for each transaction.
• City : Place where the transaction took place
• Date : Which Day the transaction took place (Start Date is 4th Oct'2013 and End Date is 26th May 2015)
• Card Type: Which Category/Type of Card (Silver, Signature, Platinum, Gold)
• Exp Type : Which type of expense was the card used for (Fuel, Food, Entertainment, Grocery, Bills, Travel)
• Gender : F stands for Female and M stands for Male
• Amount : Amount spent on each transaction
• Year : Year of the transaction
• Month : Month of the transaction

The entire data cleaning and analysis has been done in MySQL:
Database name is creditcards and Table name is cc. The CSV file was imported into MySQL.

Data Cleaning :
There were no null values or missing values. Two changes were made to the column names.
Column name Card Type was changed to Card_Type and Exp Type was changed to Exp_Type for SQL analysis purpose.

Data Analysis:
The idea behind doing the analysis was to find some patterns or links which could help the credit card company in formulating certain strategies for decision making purposes. The questions have been formulated based on this.

1. Which city has spent the highest amount over the years?

Greater Mumbai, India

2. Which card type has the highest amount over the years?

Silver

3. Which expense type has the highest amount over the years?

Bills

4.What is the total amount spent between males and females in numbers and percentage?

Female - 2,20,53,11,030 (54.1203%) , Male - 1,86,95,22,343 (45.8797%)

5. What is the total amount of spend by females vis a vis Card_Type?

Silver - 60,24,33,469
Signature - 54,80,05,149
Platinum - 53,19,40,229
Gold - 52,29,32,183

6. Which are the top 5 cities which has the highest spend for men?

Greater Mumbai, India - 57,67,51,476
Bengaluru, India - 57,23,26,739
Ahmedabad, India - 56,77,94,310
Delhi, India - 55,69,29,212
Kolkata, India - 11,54,66,943

7. List the top 5 cities with the maximum transactions

Bengaluru, India - 3552
Greater Mumbai , India - 3493
Ahmedabad, India - 3491
Delhi, India - 3482
Hyderabad, India - 784

8.Show the month wise spend across the years in the descending order

January - 43,12,09,556
October - 41,98,46,007
December - 41,69,35,415
April - 41,63,94,734
March - 41,00,54,446
November - 40,41,07,968
February - 38,35,12,624
May - 37,54,55,609
August - 21,84,53,126
September - 20,95,61,433
July - 19,79,81,416
June - 19,13,21,039

9. Show the total amount spent by men via expense type

Fuel - 39,68,53,400
Food - 37,19,06,730
Entertainment - 36,77,74,203
Grocery - 35,25,60,925
Bills - 32,70,37,004
Travel - 5,33,90,081

Conclusion & Recommendation:

1. Maximum amount of spent took place in Greater Mumbai. But there is not a huge difference between Greater Mumbai and the other 3 cities (Bengaluru, Ahmedabad, Delhi) which are immediately behind Greater Mumbai.
   These 4 cities account for 56% of the total spend. It is also corroborated as the maximum number of transactions are between these 4 cities with Bangalore topping the charts.
2. Females have a preference for Silver Type of Card whereas it is Platinum for Males. Gold is at the bottom of the pyramid. In the case of males, we see that there is not a huge difference in all the 4 categories. We can think of promoting features of the gold scheme for men to increase more awareness through targeted emails and calls.
3. In terms of type of expenses, Bills are the highest spend for females and Fuel is for males and food is at the 2nd place. While we don't have data for food in terms of offline and online spend, we can think of partnering with food delivery apps , restaurants etc to increase the spend here by offering discounts, rewards, cash back etc.
4. The month wise spend indicates as January being the highest. One disclaimer has to be made here. The data is from 4th Oct'2013 to 26th May'2015. June to September appear only once in the dataset whereas the other months appear twice due to the date range of the dataset. Therefore it could be a bit misleading. January is leading the pack.

Based on the above information, we can derive more strategies of how to target our customers based on their place of residence, gender , Type of card etc.