Skip to content
New Workbook
Sign up
Project: 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 provide the database design script as a .sql file that they can then run.

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

client

columndata typedescription
idserialClient ID - primary key
ageintegerClient's age in years
jobtextClient's type of job
maritaltextClient's marital status
educationtextClient's level of education
credit_defaultbooleanWhether the client's credit is in default
housingbooleanWhether the client has an existing housing loan (mortgage)
loanbooleanWhether the client has an existing personal loan

campaign

columndata typedescription
campaign_idserialCampaign ID - primary key
client_idserialClient ID - references id in the client table
number_contactsintegerNumber of contact attempts to the client in the current campaign
contact_durationintegerLast contact duration in seconds
pdaysintegerNumber of days since contact in previous campaign (999 = not previously contacted)
previous_campaign_contactsintegerNumber of contact attempts to the client in the previous campaign
previous_outcomebooleanOutcome of the previous campaign
campaign_outcomebooleanOutcome of the current campaign
last_contact_datedateLast date the client was contacted

economics

columndata typedescription
client_idserialClient ID - references id in the client table
emp_var_ratefloatEmployment variation rate (quarterly indicator)
cons_price_idxfloatConsumer price index (monthly indicator)
euribor_three_monthsfloatEuro Interbank Offered Rate (euribor) three month rate (daily indicator)
number_employedfloatNumber of employees (quarterly indicator)
import pandas as pd
import numpy as np

df = pd.read_csv('bank_marketing.csv')

# Importing and exploring the Data
print(df.shape)
print(df.info())
df.head()
# Split the dataFrame into 3
client = df.loc[:,
    ['client_id', # rename to id
     'age',
     'job',
     'marital',
     'education',
     'credit_default',
     'housing',
     'loan']
]

campaign = df.loc[:,
    [
        # 'campaign_id', //
     'client_id',
     'campaign', # name to number_contacts
     'duration', # contact_duration
     'pdays',
     'previous', # previous_campaign_contacts
     'poutcome', # previous_outcome
     'y'] # campaign_outcome
     # 'last_contact_date' 
]

economics = df.loc[:,
   [
    'client_id',
    'emp_var_rate',
    'cons_price_idx',
    'euribor3m', # euribor_three_months
    'nr_employed', # number_employed
   ]
]

print(client.columns, campaign.columns, economics.columns, sep='\n')
# Rename columns
client_n_map = {'client_id': 'id'}
client = client.rename(columns=client_n_map)

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

economics_n_map = {
    "euribor3m": "euribor_three_months",
    "nr_employed": "number_employed"
}
economics = economics.rename(columns=economics_n_map)

# checking columns new names
print(client.columns, campaign.columns, economics.columns, sep='\n')
# reformating client
# checking current state
print(client['education'].value_counts(dropna=False))

client['education'] = client['education'].str.replace('.', '_')
client.loc[client['education'] == 'unknown', 'education'] = np.NaN
# reformating client -> job
client['job'] = client['job'].str.replace('.', '')

# asserting cleaning result
print(client['education'].value_counts(dropna=False))
# reformating campaign
# checking current state
campaign['previous_outcome'].value_counts(dropna=False)

campaign = campaign.replace({'previous_outcome': {'success': 1, 'failure': 0, 'nonexistent': np.nan}})
campaign["campaign_outcome"] = campaign["campaign_outcome"].map({"yes": 1, "no": 0})
campaign['campaign_id'] = 1 

df['month_num'] = df['month']
df.replace({'month_num':{
    'jun':1,
    'feb':2,
    'mar':3,
    'apr':4,
    'may':5,
    'jun':6,
    'jul':7,
    'aug':8,
    'sep':9,
    'oct':10,
    'nov':11,
    'dec':12
}}, inplace=True)
campaign['last_contact_date'] =  pd.to_datetime(
    dict(year='2022', month= df.month_num, day= df.day)
)
df.drop('month_num', axis=1, inplace=True)

# asserting result
campaign['last_contact_date'].value_counts(dropna=False)
#saving to csv files
client.to_csv('client.csv', index=False)
campaign.to_csv('campaign.csv', index=False)
economics.to_csv('economics.csv', index=False)
client_table = """
    CREATE Table client
    (
        id	SERIAL PRIMARY KEY,
        age	integer,
        job	text,
        marital	text,
        education	text,
        credit_default	boolean,
        housing	boolean,
        loan	boolean
    );
    
    \copy client from 'client.csv' DELIMITER ',' CSV HEADER
"""
campaign_table = """
CREATE TABLE campaign
(
    campaign_id	serial PRIMARY KEY,
    client_id serial REFERENCES client (id),
    number_contacts	integer,
    contact_duration integer,
    pdays integer,
    previous_campaign_contacts integer,
    previous_outcome boolean,
    campaign_outcome boolean,
    last_contact_date date
);
    
\copy campaign from 'campaign.csv' DELIMITER ',' CSV HEADER
"""
economics_table = """CREATE TABLE economics
(
    client_id SERIAL references client (id),
    emp_var_rate FLOAT,
    cons_price_idx FLOAT,
    euribor_three_months FLOAT,
    number_employed FLOAT
);
\copy economics from 'economics.csv' DELIMITER ',' CSV HEADER
"""