Step 1: import csv into mysql

Download data from kaggle:

https://www.kaggle.com/barelydedicated/bank-customer-churn-modeling

create database in MySQL

CREATE DATABASE Projects;

Create table within database

Use Projects;

CREATE TABLE Churn (
    RowNumber INT NOT NULL AUTO_INCREMENT,
    CustomerId DECIMAL(10) NULL,
    Surname VARCHAR(255) NOT NULL,
    CreditScore DECIMAL(4) NULL,
    Geography VARCHAR(255) NOT NULL,
    Gender VARCHAR(255) NOT NULL,
    Age DECIMAL(3) NULL,
    Tenure DECIMAL(3) NULL,
    Balance DECIMAL(10 , 2 ) NULL,
    NumOfProducts DECIMAL(3) NULL,
    HasCrCard DECIMAL(3) NULL,
    IsActiveMember DECIMAL(3) NULL,
    EstimatedSalary DECIMAL(10 , 2 ) NULL,
    Exited DECIMAL(3) NULL,
    PRIMARY KEY (RowNumber)
);

Import csv into table. Replace what is between < >.

LOAD DATA INFILE '<directories>/Churn_Modelling.csv' 
INTO TABLE Churn 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

If you get the following error “The MySQL server is running with the –secure-file-priv option so it cannot execute this statement”:

use SHOW VARIABLES LIKE "secure_file_priv"; to see the directory that has been configured. Then move your file to the directory specified by secure-file-priv

Then run the import query again.

View the table:

SELECT * FROM Churn;

step 2: data preparation

in MySQL

Some of the surnames have question marks, so remove them.

SET SQL_SAFE_UPDATES = 0;

Use Projects;

UPDATE Churn set Surname = replace(Surname, '?', '');

Can drop irrelevant columns

Use Projects;

ALTER TABLE Churn DROP RowNumber;
ALTER TABLE Churn DROP CustomerId;

See if there are any null values

Use Projects;

SELECT COUNT(*) AS null_values FROM Churn WHERE Exited IS NULL;

Remove rows where Exited column is empty or null, since churn is the dependent variable or what is being predicted.

DELETE FROM Churn WHERE Exited = '';

DELETE FROM Churn WHERE Exited IS NULL;

For any null or empty cells for numeric columns, fill in with mean values

<insert here>

Converting columns with numbers to numerical features. We cannot calculate anything with categorical string values, we have to convert these values intro numeric ones.

step 3

Make pivot table in MySQL – eda in mysql

step 4 – import data from MySQL to jupyter notebook and continue data prep

import data from mysql into Jupyter notebook. Replace text between < >.

import mysql.connector
import pymysql
import pandas as pd

mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  password="<insert password here>",
  database="Projects",
  auth_plugin="mysql_native_password"
)

df = pd.read_sql('SELECT * FROM Churn', con=mydb)

More data prep

Get overview of dataset

df.head(5)

df.shape

split data into test and training set

df["Exited"] = df["Exited"].astype(int)
Y = df["Exited"].values
X = df.drop(labels = ["Exited"],axis = 1)
# Create Train & Test Data
from sklearn.model_selection import train_test_split
X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.2, random_state=101)

step 5 – Eda in jupyter notebook. pie chart, histogram, scatter plot, etc.

In detail we have a look at the target feature, the actual “Churn” (in the column Exited). Therefore we plot it accordingly and see that 26,5% Of the total amount of customer churn. This is important to know because we need to make sure to have the same proportion of Churned Customers to Non-Churned Customers in our training data.

import matplotlib.pyplot as plt

# Data to plot
sizes = df['Exited'].value_counts(sort = True)
colors = ["lightblue","red"] 
my_labels = 'Not Churned','Churned'
# Plot
plt.pie(sizes, labels=my_labels, colors=colors,
        autopct='%1.1f%%', shadow=True, startangle=270,)
plt.title('Percentage of Churn in Dataset')
plt.show()

step 6

feature engineering and selection

step 7 – models train and test

logistic regression model train

from sklearn.linear_model import LogisticRegression
model = LogisticRegression()
result = model.fit(X_train, y_train)

logistic regression model test

from sklearn import metrics
prediction_test = model.predict(X_test)
# Print the prediction accuracy
print (metrics.accuracy_score(y_test, prediction_test))

step 8

evaluate models

Step 9 – improve models

step 10

compare improved models

step 11 – use best model to make predictions. can also figure out model weights or which features impacted the churn prediction the most and least.

find model weights

# To get the weights of all the variables
weights = pd.Series(model.coef_[0], index=X.columns.values)
weights.sort_values(ascending = False)

Now the sales team can contact the customers who have been found to be most likely to churn.