Designing a Bank Marketing Database
Data Engineering Project
This project involves creating a comprehensive database to store and manage customer information for a bank’s marketing campaigns. The project utilizes Python for importing, loading, and cleaning the data to ensure its quality and reliability.
Using my data cleaning and database design skills, I will also author a script that sets up tables in a PostgreSQL database for bank marketing campaigns!
Project Description
In 2020, Data Engineering grew by 40% year on year. The ability to extract, transform, and load data is highly sought after by businesses worldwide and continues to grow in demand.
In this project, you will apply your data engineering skills to clean data and design a PostgreSQL database to store information about marketing campaigns run by a bank.
You will need to modify values, add new features, and consider how data should be stored within a PostgreSQL database efficiently and clearly.
Personal loans are a lucrative revenue stream for banks. The typical interest rate on 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 that aimed to get customers to take out a personal loan. They plan to conduct more marketing campaigns going forward, so they 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 them with the data from the CSV files. As the bank is 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:
Project Tasks
- Read in
bank_marketing.csv
as a pandas DataFrame. - Split the data into three DataFrames using information provided about the desired tables as your guide: one with information about the
client
, another containingcampaign
data, and a third to store information abouteconomics
at the time of the campaign. - Rename the column
"client_id"
to"id"
inclient
(leave as-is in the other subsets);"duration"
to"contact_duration"
,"previous"
to"previous_campaign_contacts"
,"y"
to"campaign_outcome"
,"poutcome"
to"previous_outcome"
, and"campaign"
to"number_contacts"
incampaign
; and"euribor3m"
to"euribor_three_months"
and"nr_employed"
to"number_employed"
ineconomics
. - Clean the
"education"
column, changing"."
to"_"
and"unknown"
to NumPy's null values. - Remove periods from the
"job"
column. - Convert
"success"
and"failure"
in the"previous_outcome"
and"campaign_outcome"
columns to binary (1
or0
), along with the changing"nonexistent"
to NumPy's null values in"previous_outcome"
. - Add a column called
campaign_id
incampaign
, where all rows have a value of1
. - Create a
datetime
column calledlast_contact_date
, in the format of"year-month-day"
, where the year is2022
, and the month and day values are taken from the"month"
and"day"
columns. - Remove any redundant data that might have been used to create new columns, ensuring the columns in each subset of the data match the table displayed in the notebook.
- Save the three DataFrames to csv files without an index as
client.csv
,campaign.csv
, andeconomics.csv
respectively. - Create a Python variable called
client_table
, containing SQL code as a multi-line string to create a table calledclient
using values fromclient.csv
. - Create a Python variable called
campaign_table
, containing SQL code as a multi-line string to create a table calledcampaign
using values fromcampaign.csv
. - Create a Python variable called
economics_table
, containing SQL code as a multi-line string to create a table calledeconomics
using values fromeconomics.csv
. - In
client
,campaign
, andeconomic
, ensure the final line copies the data from their respective csv files using the following template code snippet:\copy table_name from 'file_name.csv' DELIMITER ',' CSV HEADER
Completing the Projects Tasks
Reading in and Splitting the Data
Cleaning and Reformatting the Data
Inspecting the Final DataFrames
Saving the Data
Designing the Database
The project tasks have finally been completed, and as directed in the project description, the bank will run the script at their end to create the tables and continuously insert the data into the database they must have created.
To view the expected output for each task and how to successfully load the data into a PostgreSQL database after cleaning and getting it ready, check out my notebook here for the detailed steps.
I will see you in my next article; until then, connect with me on LinkedIn and on X (formerly Twitter). 💡