<jupyter_start><jupyter_text>Churn PredictionSource    : https://www.dqlab.id/Typed by  : <NAME>Case     : Predict the customers churn in past 6 months.  Import Libraries<jupyter_code>import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns
import numpy as np
from sklearn.model_selection import train_test_split
from sklearn.linear_model import LogisticRegression
from sklearn.metrics import confusion_matrix, classification_report
from sklearn.metrics import accuracy_score, precision_score, recall_score<jupyter_output><empty_output><jupyter_text>Load data<jupyter_code>df = pd.read_csv('data_retail.csv',sep=';')
df.head()
df.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 100000 entries, 0 to 99999
Data columns (total 8 columns):
 #   Column                      Non-Null Count   Dtype 
---  ------                      --------------   ----- 
 0   no                          100000 non-null  int64 
 1   Row_Num                     100000 non-null  int64 
 2   Customer_ID                 100000 non-null  int64 
 3   Product                     100000 non-null  object
 4   First_Transaction           100000 non-null  int64 
 5   Last_Transaction            100000 non-null  int64 
 6   Average_Transaction_Amount  100000 non-null  int64 
 7   Count_Transaction           100000 non-null  int64 
dtypes: int64(7), object(1)
memory usage: 6.1+ MB<jupyter_text>Exploratory Data Analysis The data type of columns of First_Transaction and Last_Transaction are int64. This type have to be converted to date format.<jupyter_code>df['First_Transaction'] = pd.to_datetime(df['First_Transaction']/1000, unit='s', origin='1970-01-01')
df['Last_Transaction'] = pd.to_datetime(df['Last_Transaction']/1000, unit='s', origin='1970-01-01')
df.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 100000 entries, 0 to 99999
Data columns (total 8 columns):
 #   Column                      Non-Null Count   Dtype         
---  ------                      --------------   -----         
 0   no                          100000 non-null  int64         
 1   Row_Num                     100000 non-null  int64         
 2   Customer_ID                 100000 non-null  int64         
 3   Product                     100000 non-null  object        
 4   First_Transaction           100000 non-null  datetime64[ns]
 5   Last_Transaction            100000 non-null  datetime64[ns]
 6   Average_Transaction_Amount  100000 non-null  int64         
 7   Count_Transaction           100000 non-null  int64         
dtypes: datetime64[ns](2), int64(5), object(1)
memory usage: 6.1+ MB<jupyter_text>To identify the customers churn, we have to check the past 6 months of their transaction. We can get this information from the last transaction date from the whole data.<jupyter_code>df['Last_Transaction'].max()<jupyter_output><empty_output><jupyter_text>The last transaction has recorded in this data that is on 2019-02-01. We can identify, the past 6 months from that time is on 2018-08-01. That means, we have to classify the customers who still transaction above 2018-08-01 (not churn/False) and the customers who didn't transaction from 2018-08-01 to the 2019-02-01 (churn/True).<jupyter_code>df.loc[df['Last_Transaction'] <= '2018-08-01', 'is_churn'] = True
df.loc[df['Last_Transaction'] > '2018-08-01', 'is_churn'] = False
df.head()<jupyter_output><empty_output><jupyter_text>Remove unecessary features<jupyter_code>del df['no']
del df['Row_Num']<jupyter_output><empty_output><jupyter_text>Check missing value<jupyter_code>df.isnull().sum()<jupyter_output><empty_output><jupyter_text>The missing value is not exists in this data We calculate the number of customers transaction for each year<jupyter_code>df['Year_First_Transaction'] = df['First_Transaction'].dt.year
df['Year_Last_Transaction'] = df['Last_Transaction'].dt.year
df.head()<jupyter_output><empty_output><jupyter_text>Plot the number of customers at first transaction and last transaction for each year<jupyter_code>df_first_year = df.groupby(['Year_First_Transaction'])['Customer_ID'].count()
df_last_year = df.groupby(['Year_Last_Transaction'])['Customer_ID'].count()
df_first_year.plot(
    x='Year_First_Transaction',
    y='Customer_ID',
    kind='line',
    marker='.',
    title='Customer Acquisition',
    label='First Transaction'
    )

df_last_year.plot(
    x='Year_Last_Transaction',
    y='Customer_ID',
    kind='line',
    marker='.',
    title='Customer Acquisition',
    color='red', 
    label='Last Transaction'
    )
plt.ylabel('Number of Customer')
plt.xlabel('Years')
plt.legend()
plt.tight_layout()
plt.show()<jupyter_output><empty_output><jupyter_text>The number of customers of the last transaction from 2013 to 2017 are lower than to the first transaction. However, in 2017 to 2018, the number of customers of the last transaction are increase significantly, but it was going down in 2018 to 2019. Unfortunately, in 2017 to 2019 the number of customers was going down of the first transaction. Calculate number of total transaction each year<jupyter_code>df_first_year = df.groupby(['Year_First_Transaction'])['Count_Transaction'].sum()
df_last_year = df.groupby(['Year_Last_Transaction'])['Count_Transaction'].sum()

df_first_year.plot(
    x='Year_First_Transaction',
    y='Count_Transaction',
    kind='line',
    marker='.',
    title='Total Transaction Customer per Year',
    label='First Transaction'
    )
df_last_year.plot(x='Year_Last_Transaction',
y='Count_Transaction', 
kind='line', 
marker='.',
title='Total Transaction Customer per Year', 
label='Last Transaction', 
color='red')

plt.xlabel('Years')
plt.ylabel('Number of Total Transactions')
plt.legend()
plt.tight_layout()
plt.show()<jupyter_output><empty_output><jupyter_text>The number of total transactions of the first transaction in 2013 to 2017 are higher than to the last transaction where its number of total transactions are lower. In 2017 to 2018, the number of total transactions were growing up significantly for the last transaction. However, in 2018 to 2019 this numbers were going down slowly. Unfortunately, for number of transactions of the first transaction were going down significantly in 2017 to 2019. Average transaction for each sold product<jupyter_code>mean_first_transaction = df.groupby(['Product', 'Year_First_Transaction']).mean().reset_index()
mean_last_transaction = df.groupby(['Product', 'Year_Last_Transaction']).mean().reset_index()

first_year_data = df.groupby(['Product', 'Year_First_Transaction']).mean().reset_index()
last_year_data = df.groupby(['Product', 'Year_Last_Transaction']).mean().reset_index()
residual_amount = last_year_data['Average_Transaction_Amount'] - first_year_data['Average_Transaction_Amount']
product_list = first_year_data['Product']
year_list = first_year_data['Year_First_Transaction']
join_data = pd.DataFrame([product_list, year_list, residual_amount], index=['Product', 'Year', 'Residual_Amount']).T

fig1, ax1 = plt.subplots(3,1, figsize=(10,7))
sns.pointplot(data = mean_first_transaction, 
              x='Year_First_Transaction', 
              y='Average_Transaction_Amount', 
              hue='Product',
              palette="Set1",
              ax=ax1[0]
              )

sns.pointplot(data = mean_last_transaction, 
              x='Year_Last_Transaction', 
              y='Average_Transaction_Amount', 
              hue='Product',
              palette="Set1",
              ax=ax1[1]
              )

sns.pointplot(data = join_data, 
              x='Year', 
              y='Residual_Amount', 
              hue='Product',
              palette="Set1",
              ax=ax1[2]
              )

labels, locations = plt.yticks()
plt.yticks(labels, (labels/100000))
ax1[0].grid()
ax1[1].grid()
ax1[2].grid()
ax1[0].legend(loc='center left', title="Product",bbox_to_anchor=(1.05, 0.65))
ax1[1].legend().remove()
ax1[2].legend().remove()

ax1[0].set_xlabel('Years')
ax1[0].set_ylabel('Average Transaction\nAmount (100000)')
ax1[0].set_title('First Transaction')

ax1[1].set_xlabel('Years')
ax1[1].set_ylabel('Average Transaction\nAmount (100000)')
ax1[1].set_title('Last Transaction')

ax1[2].set_xlabel('Years')
ax1[2].set_ylabel('Average Transaction\nAmount (100000)')
ax1[2].set_title('Residual Amount')
plt.tight_layout()
plt.show()<jupyter_output><empty_output><jupyter_text>The residual average transaction amount between last transaction and first transaction of:1. clothes product was decreased in 2018 to 2019, but it was increase in 2017 to 2018. 2. jacket product was decreased in 2014 to 2019, 3. shoes was increase in 2014 to 20194. bag product was decreased significantly in 2017 to 2019.   Identify Customers Churn Set 'is_churn' as index<jupyter_code>df_piv = df.pivot_table(index='is_churn', 
                        columns='Product',
                        values='Customer_ID', 
                        aggfunc='count', 
                        fill_value=0)
df_piv
name_product = df_piv.count().sort_values(ascending=False).head(5).index
name_product
df_piv = df_piv.reindex(columns=name_product)
df_piv
df_piv = df_piv.reindex(columns=name_product)
df_piv.plot.pie(subplots=True,
                figsize=(10, 5),
                autopct='%1.0f%%',
                title='Proportion Churn by Product',
                legend=None)
plt.tight_layout()
plt.show()<jupyter_output><empty_output><jupyter_text>Based on propotion churn by product, the jacket has high percentage of churn and the shoes is following behind. It indicates, there are may be another jacket product from another competition that has good quality. It is also represented by residual amount between last transaction and firts transaction in 2017 to 2019, where the jacket product was going down to minus. Otherwise, although the churn rate of shoes product is the second higher and the residual amount of shoes product in 2013 to 2017 was minus, but this product can revoke the customers to picked this item in 2017 to 2019, where the residual amount were growing up significantly. It means, the shoes product is good demand at this market currently. Write function to classify number of transactions<jupyter_code>def func(row):
    if row['Count_Transaction'] == 1:
        val = '1. 1'
    elif (row['Count_Transaction'] > 1 and row['Count_Transaction'] <= 3):
        val ='2. 2-3'
    elif (row['Count_Transaction'] > 3 and row['Count_Transaction'] <= 6):
        val ='3. 4-6'
    elif (row['Count_Transaction'] > 6 and row['Count_Transaction'] <= 10):
        val ='4. 7-10'
    else:
        val ='5.>10'
    return val
df['Count_Transaction_Group'] = df.apply(func, axis=1)
ctg_costumerid = df.groupby(['Count_Transaction_Group'])['Customer_ID'].count().reset_index()
ctg_costumerid.head()
ctg_costumerid.plot(x='Count_Transaction_Group', y='Customer_ID', kind='bar', title='Customer Distribution by Count Transaction Group')
plt.xlabel('Count Transaction Group')
plt.ylabel('Number of Customer')
plt.tight_layout()
plt.show()<jupyter_output><empty_output><jupyter_text>There are 49255 customers who transaction only once, 21457 customers who transaction more than 10 times, and only 2890 customers who transaction 7-10 times. We create the function for classify the average transaction amount<jupyter_code>def f(row):
    if (row['Average_Transaction_Amount'] >= 100000 and row['Average_Transaction_Amount'] <= 200000):
        val ='1. 100.000 - 250.000'
    elif (row['Average_Transaction_Amount'] > 250000 and row['Average_Transaction_Amount'] <= 500000):
        val ='2. >250.000 - 500.000'
    elif (row['Average_Transaction_Amount'] > 500000 and row['Average_Transaction_Amount'] <= 750000):
        val ='3. >500.000 - 750.000'
    elif (row['Average_Transaction_Amount'] > 750000 and row['Average_Transaction_Amount'] <= 1000000):
        val ='4. >750.000 - 1.000.000'
    elif (row['Average_Transaction_Amount'] > 1000000 and row['Average_Transaction_Amount'] <= 2500000):
        val ='5. >1.000.000 - 2.500.000'
    elif (row['Average_Transaction_Amount'] > 2500000 and row['Average_Transaction_Amount'] <= 5000000):
        val ='6. >2.500.000 - 5.000.000'
    elif (row['Average_Transaction_Amount'] > 5000000 and row['Average_Transaction_Amount'] <= 10000000):
        val ='7. >5.000.000 - 10.000.000'
    else:
        val ='8. >10.000.000'
    return val
df['Average_Transaction_Amount_Group'] = df.apply(f, axis=1)
df.head()<jupyter_output><empty_output><jupyter_text>Grouping average transaction amount by year<jupyter_code>df_year = df.groupby(['Average_Transaction_Amount_Group'])['Customer_ID'].count().reset_index()
df_year.head()
df_year.plot(
    x='Average_Transaction_Amount_Group',
    y='Customer_ID', 
    kind='bar', 
    title='Customer Distribution by Average Transaction Amount Group',
    figsize=(10,5)
    )
plt.xlabel('Average Transaction\nAmount Group')
plt.ylabel('Number of Customer')
plt.tight_layout()
plt.show()<jupyter_output><empty_output><jupyter_text>There are 32819 customers who transaction in range of amount 1.000.000 - 2.500.000, where this is the highe number. Only 4912 customers transaction in range of amount 100.000 - 250.000.  Generate Model Calculate range of year<jupyter_code>df['Year_Diff'] = df['Year_Last_Transaction'] - df['Year_First_Transaction']
feature_columns = ['Average_Transaction_Amount', 'Count_Transaction', 'Year_Diff']
# Features variable
X = df[feature_columns] 
# Target variable
y = df['is_churn'].astype('int64')<jupyter_output><empty_output><jupyter_text>Split data to train and test data<jupyter_code>X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.3, random_state=0)<jupyter_output><empty_output><jupyter_text>Logistic Regression<jupyter_code>logreg = LogisticRegression()
logreg = logreg.fit(X_train, y_train)
y_pred = logreg.predict(X_test)<jupyter_output><empty_output><jupyter_text>calculate the confusion matrix<jupyter_code>cnf_matrix = confusion_matrix(y_test, y_pred)

sns.heatmap(pd.DataFrame(cnf_matrix), annot=True, cmap='YlGnBu', fmt='g')
plt.title('Confusion matrix', y=1.1)
plt.ylabel('Actual')
plt.xlabel('Predicted')
plt.tight_layout()
plt.show()
print('Number of Test Data:', len(y_test))
print('Accuracy :', accuracy_score(y_test, y_pred) * 100,'%')
print('Precision:', precision_score(y_test, y_pred) * 100,'%')
print('Recall   :', recall_score(y_test, y_pred) * 100,'%')<jupyter_output>Number of Test Data: 30000
Accuracy : 66.44333333333333 %
Precision: 66.45107517919652 %
Recall   : 99.97993579454254 %
{'fraction_non_alphanumeric': 0.11689384551838083, 'fraction_numerical': 0.05727660746248107, 'mean_word_length': 4.171591313634745, 'pattern_counts': {'":': 0, '<': 88, '<?xml version=': 0, '>': 96, 'https://': 1, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 2, 'word_list_counts': {'cursed_substrings.json': 2, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '19', 'dir': 'jupyter-structured-clean-dedup', 'id': '336590', 'license': 'MIT', 'n_tokens_mistral': 5150, 'n_tokens_neox': 4394, 'n_words': 1157, 'path': 'business_decision_curhn_prediction.ipynb', 'repo_name': 'auliakhalqillah/customers-churn', 'size': '14536'}