Designing a Bank Marketing Database

Data Engineering Project

Image from Freepik.com

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 containing campaign data, and a third to store information about economics at the time of the campaign.
• Rename the column "client_id" to "id" in client (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" in campaign; and "euribor3m" to "euribor_three_months" and "nr_employed" to "number_employed" in economics.
• 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 or 0), along with the changing "nonexistent" to NumPy's null values in "previous_outcome".
• Add a column called campaign_id in campaign, where all rows have a value of 1.
• Create a datetime column called last_contact_date, in the format of "year-month-day", where the year is 2022, 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, and economics.csv respectively.
• Create a Python variable called client_table, containing SQL code as a multi-line string to create a table called client using values from client.csv.
• Create a Python variable called campaign_table, containing SQL code as a multi-line string to create a table called campaign using values from campaign.csv.
• Create a Python variable called economics_table, containing SQL code as a multi-line string to create a table called economics using values from economics.csv.
• In client, campaign, and economic, 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

Client Data

Campaign Data

Economics Data

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). 💡