Skip to content
Designing a Bank Marketing Database
  • AI Chat
  • Code
  • Report
  • Piggy bank

    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!

    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. 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_defaulttextWhether the client's credit is in default
    housingtextWhether the client has an existing housing loan (mortgage)
    loantextWhether the client has an existing personal loan

    campaign

    columndata typedescription
    campaign_idintegerCampaign ID
    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_outcometextOutcome of the previous campaign
    campaign_outcomeintegerOutcome 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)
    # Start coding...
    import pandas as pd
    import numpy as np
    # Store and print database_design
    # Load the data
    df = pd.read_csv("bank_marketing.csv")
    
    # Split the data
    client = df[["client_id", "age", "job", "marital", 
                 "education", "credit_default", "housing", "loan"]]
    campaign = df[["client_id", "campaign", "month", "day_of_week", 
                   "duration", "pdays", "previous", "poutcome", "y"]]
    economics = df[["client_id", "emp_var_rate", "cons_price_idx", 
                    "euribor3m", "nr_employed"]]
    
    # Rename, clean, create, and delete columns
    ## Renaming columns
    client.rename(columns={"client_id": "id"}, 
                           inplace=True)
    campaign.rename(columns={"duration": "contact_duration", 
                             "previous": "previous_campaign_contacts",
                             "y": "campaign_outcome", 
                             "campaign": "number_contacts", 
                             "poutcome": "previous_outcome"}, 
                             inplace=True)
    economics.rename(columns={"euribor3m": "euribor_three_months", 
                              "nr_employed": "number_employed"}, 
                              inplace=True)
    
    ## Cleaning columns
    client["education"] = client["education"].str.replace(".", "_")
    client["education"] = client["education"].replace("unknown", np.NaN)
    client["job"] = client["job"].str.replace(".", "")
    campaign["campaign_outcome"] = campaign["campaign_outcome"].replace("yes", 1).replace("no", 0)
    campaign["previous_outcome"] = campaign["previous_outcome"].replace("non_existent", np.NaN).replace("failure", 0).replace("success", 1)
    
    ## Creating new columns
    campaign["month"] = campaign["month"].str.capitalize()
    campaign["day_of_week"] = campaign["day_of_week"].str.capitalize()
    campaign["year"] = "2022"
    campaign["last_contact_date"] = campaign["year"] + "-" + campaign["month"] + "-" + campaign["day_of_week"]
    campaign["last_contact_date"] = pd.to_datetime(campaign["last_contact_date"], 
                                                   format="%Y-%b-%a")
    campaign["campaign_id"] = 1
    
    ## Deleting columns
    campaign.drop(columns=["month", 
                           "year", 
                           "day_of_week"], 
                           inplace=True)
    
    # Saving the data
    ## Store the DataFrames
    client.to_csv("client.csv", index=False)
    campaign.to_csv("campaign.csv", index=False)
    economics.to_csv("economics.csv", index=False)
    
    ## Structuring the dictionary
    database_design = {"client": client,
                       "campaign": campaign,
                       "economics": economics}
    print(database_design)