Project: Designing a Bank Marketing Database

Designing a Bank Marketing Database

Personal loans are a lucrative revenue stream for banks. The typical interest rate of a two year loan in the United Kingdom is around 10%. This might not sound like a lot, but in September 2022 alone UK consumers borrowed around £1.5 billion, which would mean approximately £300 million in interest generated by banks over two years!

You have been asked to work with a bank to clean and store the data they collected as part of a recent marketing campaign, which aimed to get customers to take out a personal loan. They plan to conduct more marketing campaigns going forward so would like you to set up a PostgreSQL database to store this campaign's data, designing the schema in a way that would allow data from future campaigns to be easily imported.

They have supplied you with a csv file called "bank_marketing.csv", which you will need to clean, reformat, and split, in order to save separate files based on the tables you will create. It is recommended to use pandas for these tasks.

Lastly, you will write the SQL code that the bank can execute to create the tables and populate with the data from the csv files. As the bank are quite strict about their security, you'll save SQL files as multiline string variables that they can then use to create the database on their end.

You have been asked to design a database that will have three tables:

client

column	data type	description	original column in dataset
`id`	`serial`	Client ID - primary key	`client_id`
`age`	`integer`	Client's age in years	`age`
`job`	`text`	Client's type of job	`job`
`marital`	`text`	Client's marital status	`marital`
`education`	`text`	Client's level of education	`education`
`credit_default`	`boolean`	Whether the client's credit is in default	`credit_default`
`housing`	`boolean`	Whether the client has an existing housing loan (mortgage)	`housing`
`loan`	`boolean`	Whether the client has an existing personal loan	`loan`

campaign

column	data type	description	original column in dataset
`campaign_id`	`serial`	Campaign ID - primary key	N/A - new column
`client_id`	`serial`	Client ID - references `id` in the `client` table	`client_id`
`number_contacts`	`integer`	Number of contact attempts to the client in the current campaign	`campaign`
`contact_duration`	`integer`	Last contact duration in seconds	`duration`
`pdays`	`integer`	Number of days since contact in previous campaign (`999` = not previously contacted)	`pdays`
`previous_campaign_contacts`	`integer`	Number of contact attempts to the client in the previous campaign	`previous`
`previous_outcome`	`boolean`	Outcome of the previous campaign	`poutcome`
`campaign_outcome`	`boolean`	Outcome of the current campaign	`y`
`last_contact_date`	`date`	Last date the client was contacted	A combination of `day`, `month`, and the newly created `year`

economics

column	data type	description	original column in dataset
`client_id`	`serial`	Client ID - references `id` in the `client` table	`client_id`
`emp_var_rate`	`float`	Employment variation rate (quarterly indicator)	`emp_var_rate`
`cons_price_idx`	`float`	Consumer price index (monthly indicator)	`cons_price_idx`
`euribor_three_months`	`float`	Euro Interbank Offered Rate (euribor) three month rate (daily indicator)	`euribor3m`
`number_employed`	`float`	Number of employees (quarterly indicator)	`nr_employed`

Importing Libraries and Data and Prepping Data

#Importing Libraries
import pandas as pd
import numpy as np

#Importing Data
bank_data = pd.read_csv('bank_marketing.csv')
bank_data

#Splitting bank_data into the client, campaign, and economics tables
client = bank_data[['client_id','age','job','marital','education','credit_default','housing','loan']]

campaign = bank_data[['client_id','campaign','duration','pdays','previous','poutcome', 'y', 'day','month']]

economics = bank_data[['client_id','emp_var_rate','cons_price_idx','euribor3m','nr_employed']]

#Looking at the three created tables and renaming columns
client.rename(columns = {'client_id':'id'}, inplace = True)
client

Renaming Columns

campaign.rename(columns = {'duration':'contact_duration','previous':'previous_compaign_contacts','y':'campaign_outcome','poutcome':'previous_outcome','campaign':'number_contacts'}, inplace = True)
campaign

economics.rename(columns = {'euribor3m':'euribor_three_months','nr_employed':'number_employed'}, inplace = True)
economics

Cleaning the data in the tables

Client data

#Changing "." to "_" in the education column
client['education'] = client['education'].str.replace('.','_')

#Replacing "Unknown" with NaN in the education column
client['education'].replace('unknown',np.NaN, inplace = True)

#Removing "." from job column
client['job'] = client['job'].str.replace('.','')

client

Campaign Data

#Replacing 'success' and 'failure' in the "_outcome" columns, with "1" and "0", respectively
campaign['previous_outcome'] = campaign['previous_outcome'].str.replace('success','1')
campaign['previous_outcome'] = campaign['previous_outcome'].str.replace('failure','0')
campaign['previous_outcome'] = campaign['previous_outcome'].astype('bool')

campaign['campaign_outcome'] = campaign['campaign_outcome'].str.replace('success','1')
campaign['campaign_outcome'] = campaign['campaign_outcome'].str.replace('failure','0')
campaign['campaign_outcome'] = campaign['campaign_outcome'].astype('bool')


#Replacing 'nonexistent' in 'previous_outcome' column with null
campaign['previous_outcome'].replace('unkown',np.NaN, inplace = True)

#Creating an 'id' column populated with 1, since this is the first campaign
campaign.insert(0, 'id', '1')

#Creating a data column called 'last_contact_date' combining the day and month column, with 2022 for the year the campaing was ran

#Creating 'year' column
campaign['year'] = '2022'

#Capitalizing values in the 'month' column
campaign['month'] = campaign['month'].str.capitalize()

#Converting 'day' to a string 
campaign['day'] = campaign['day'].astype('str')

#Combining all date columns
campaign['last_contact_date'] = campaign['year'] + '-' + campaign['month'] + '-' + campaign['day']

#Converting 'last_contact_date' to a date 
campaign['last_contact_date'] = pd.to_datetime(campaign['last_contact_date'])

#Removing redudant columns
campaign = campaign.drop(columns = ['year','month','day'])

campaign

‌
‌
‌