In [6]:
#import libraries
import numpy as np 
import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns 
import sqlite3
In [8]:
# 1. import database
print("Hello")

Hello
In [9]:
conn = sqlite3.connect(
    r'C:\Users\Hp\Downloads\customer_churn.db'
)

sql_query = """
        SELECT name
        FROM sqlite_master
        WHERE type ='table'
        """

tables = pd.read_sql(sql_query, conn)

#create dataframe for each table
for table_name in tables['name']:
    df = pd.read_sql(f"SELECT * FROM {table_name}",conn)
    
    globals()[f"df_{table_name}"] = df
    
    print(f"Created dataframe: df_{table_name}")

conn.close()

print(tables)

Created dataframe: df_db_customer
Created dataframe: df_db_subscription
Created dataframe: df_db_support
              name
0      db_customer
1  db_subscription
2       db_support
In [20]:
#print table names and cloumns name
conn = sqlite3.connect(
    r'C:\Users\Hp\Downloads\customer_churn.db'
)

for table_name in tables['name']:
    print(f"\nTable Name: {table_name}")
    #get column information
    columns_query = f"PRAGMA table_info({table_name});"
    columns = pd.read_sql(columns_query, conn)
    print("Columns:")
    print (columns['name'].tolist())

conn.close()

Table Name: db_customer
Columns:
['customerid', 'name', 'country', 'state', 'gender', 'dob', 'interests', 'pincode']

Table Name: db_subscription
Columns:
['customerid', 'subscription_start_date', 'subscription_type', 'renewal_date', 'plan_type', 'contract_type', 'cancellation_date', 'cancellation_reason', 'monthly_charges', 'cltv', 'churn_score']

Table Name: db_support
Columns:
['customerid', 'complaint_date', 'escalations', 'csat_score', 'col_1', 'comment']
In [21]:
# 2. data cleaning
In [22]:
df_db_customer.head()
#df_db_customer.tail()
Out[22]:
<style scoped> .dataframe tbody tr th:only-of-type { vertical-align: middle; } .dataframe tbody tr th { vertical-align: top; } .dataframe thead th { text-align: right; } </style> <thead> <th></th> <th>customerid</th> <th>customer_name</th> <th>country</th> <th>state</th> <th>gender</th> <th>dob</th> <th>interests</th> <th>pincode</th> </thead> <tbody> <th>0</th> <th>1</th> <th>2</th> <th>3</th> <th>4</th> </tbody> <tbody></tbody>
0002-ORFBO keshav India Maharashtra Male 1982-04-12 00:00:00 travel None
0003-MKNFE raghav India Karnataka Male 1995-11-23 00:00:00 NaN None
0004-TLHLJ lalita India Delhi Female 1978-02-15 00:00:00 movie None
0011-IGKFF mohan India Nagaland Male 2001-08-30 00:00:00 NaN None
0013-EXCHZ mira India Delhi Female 1990-05-05 00:00:00 drama None
In [23]:
df_db_customer.info()

<class 'pandas.DataFrame'>
RangeIndex: 21 entries, 0 to 20
Data columns (total 8 columns):
 #   Column         Non-Null Count  Dtype 
---  ------         --------------  ----- 
 0   customerid     21 non-null     str   
 1   customer_name  21 non-null     str   
 2   country        18 non-null     str   
 3   state          21 non-null     str   
 4   gender         21 non-null     str   
 5   dob            21 non-null     str   
 6   interests      4 non-null      str   
 7   pincode        0 non-null      object
dtypes: object(1), str(7)
memory usage: 1.4+ KB
In [24]:
# a. drop columns interest and pincode
# b. rename col - name 
# c. change the data type of date 
# d. fix missing values - country
# e. data standardization - gender
In [25]:
# a. renmae col - name 

df_db_customer.rename(columns = {'name': 'customer_name'}, inplace = True)
df_db_customer
Out[25]:
<style scoped> .dataframe tbody tr th:only-of-type { vertical-align: middle; } .dataframe tbody tr th { vertical-align: top; } .dataframe thead th { text-align: right; } </style> <thead> <th></th> <th>customerid</th> <th>customer_name</th> <th>country</th> <th>state</th> <th>gender</th> <th>dob</th> <th>interests</th> <th>pincode</th> </thead> <tbody> <th>0</th> <th>1</th> <th>2</th> <th>3</th> <th>4</th> <th>5</th> <th>6</th> <th>7</th> <th>8</th> <th>9</th> <th>10</th> <th>11</th> <th>12</th> <th>13</th> <th>14</th> <th>15</th> <th>16</th> <th>17</th> <th>18</th> <th>19</th> <th>20</th> </tbody> <tbody></tbody>
0002-ORFBO keshav India Maharashtra Male 1982-04-12 00:00:00 travel None
0003-MKNFE raghav India Karnataka Male 1995-11-23 00:00:00 NaN None
0004-TLHLJ lalita India Delhi Female 1978-02-15 00:00:00 movie None
0011-IGKFF mohan India Nagaland Male 2001-08-30 00:00:00 NaN None
0013-EXCHZ mira India Delhi Female 1990-05-05 00:00:00 drama None
0013-MHZWF durga NaN Delhi Women 1988-12-10 00:00:00 NaN None
0013-SMEOE mina India Meghalaya Female 1976-09-21 00:00:00 NaN None
0014-BMAQU madan India Rajasthan Male 1999-03-14 00:00:00 NaN None
0015-UOCOJ maya NaN Kathmandu Women 1985-07-07 00:00:00 NaN None
0016-QLJIS arjun Nepal Kathmandu Male 1993-10-29 00:00:00 NaN None
0017-DINOC shiva India Maharashtra Men 1997-01-22 00:00:00 NaN None
0017-IUDMW rangadevi India Karnataka Female 1981-06-18 00:00:00 NaN None
0018-NYROU chitra NaN Telangana Female 2004-12-01 00:00:00 NaN None
0019-EFAEP raju India Meghalaya Female 1992-04-25 00:00:00 NaN None
0019-GFNTW Madhav India Uttar Pradesh Men 1979-11-11 00:00:00 NaN None
0020-INWCK parvati India Delhi Female 1986-02-28 00:00:00 job None
0020-JDNXP rikim India Meghalaya Female 1994-08-19 00:00:00 NaN None
0021-IKXGC vishakha India Rajasthan Female 2000-09-02 00:00:00 NaN None
0022-TCJCI raghvendra India Telangana Male 1983-12-30 00:00:00 NaN None
0023-HGHWL rishabh India Uttar Pradesh Men 1991-05-14 00:00:00 NaN None
0023-UYUPN sudevi India Maharashtra Women 1977-10-06 00:00:00 NaN None
In [26]:
#b. drop columns interest and pincode
df_db_customer.drop(columns = ['interests','pincode'] , inplace = True ,errors='ignore')
In [27]:
df_db_customer.info()

<class 'pandas.DataFrame'>
RangeIndex: 21 entries, 0 to 20
Data columns (total 6 columns):
 #   Column         Non-Null Count  Dtype
---  ------         --------------  -----
 0   customerid     21 non-null     str  
 1   customer_name  21 non-null     str  
 2   country        18 non-null     str  
 3   state          21 non-null     str  
 4   gender         21 non-null     str  
 5   dob            21 non-null     str  
dtypes: str(6)
memory usage: 1.1 KB
In [28]:
#c. change the date datatype

df_db_customer['dob'] = pd.to_datetime(df_db_customer['dob'])
In [29]:
# Convert selected columns to object
cols_to_object = [
    'customerid',
    'customer_name',
    'country',
    'state',
    'gender'
]

df_db_customer[cols_to_object] = df_db_customer[cols_to_object].astype('object')

# Convert dob to datetime
df_db_customer['dob'] = pd.to_datetime(df_db_customer['dob'])
In [30]:
df_db_customer.info()

<class 'pandas.DataFrame'>
RangeIndex: 21 entries, 0 to 20
Data columns (total 6 columns):
 #   Column         Non-Null Count  Dtype         
---  ------         --------------  -----         
 0   customerid     21 non-null     object        
 1   customer_name  21 non-null     object        
 2   country        18 non-null     object        
 3   state          21 non-null     object        
 4   gender         21 non-null     object        
 5   dob            21 non-null     datetime64[us]
dtypes: datetime64[us](1), object(5)
memory usage: 1.1+ KB
In [31]:
# d. standardization
df_db_customer['gender'].unique()
df_db_customer['gender'] = df_db_customer['gender'].replace({'Men' : 'Male' , 'Women' : 'Female'})
In [32]:
# e. missing values fixing 
df_db_customer[df_db_customer['country'].isna()]
Out[32]:
<style scoped> .dataframe tbody tr th:only-of-type { vertical-align: middle; } .dataframe tbody tr th { vertical-align: top; } .dataframe thead th { text-align: right; } </style> <thead> <th></th> <th>customerid</th> <th>customer_name</th> <th>country</th> <th>state</th> <th>gender</th> <th>dob</th> </thead> <tbody> <th>5</th> <th>8</th> <th>12</th> </tbody> <tbody></tbody>
0013-MHZWF durga NaN Delhi Female 1988-12-10
0015-UOCOJ maya NaN Kathmandu Female 1985-07-07
0018-NYROU chitra NaN Telangana Female 2004-12-01
In [33]:
#df_db_customer[['country','state']]
#country and state unique value pair
state_country_mapping = df_db_customer.dropna(subset = ['country']).set_index('state')['country'].to_dict()

df_db_customer['country'] = df_db_customer['country'].fillna(df_db_customer['state'].map(state_country_mapping))
In [34]:
df_db_customer[df_db_customer['country'].isna()]
Out[34]:
<style scoped> .dataframe tbody tr th:only-of-type { vertical-align: middle; } .dataframe tbody tr th { vertical-align: top; } .dataframe thead th { text-align: right; } </style> <thead> <th></th> <th>customerid</th> <th>customer_name</th> <th>country</th> <th>state</th> <th>gender</th> <th>dob</th> </thead> <tbody> </tbody> <tbody></tbody>
In [35]:
df_db_subscription.head()
Out[35]:
<style scoped> .dataframe tbody tr th:only-of-type { vertical-align: middle; } .dataframe tbody tr th { vertical-align: top; } .dataframe thead th { text-align: right; } </style> <thead> <th></th> <th>customerid</th> <th>subscription_start_date</th> <th>subscription_type</th> <th>renewal_date</th> <th>plan_type</th> <th>contract_type</th> <th>cancellation_date</th> <th>cancellation_reason</th> <th>monthly_charges</th> <th>cltv</th> <th>churn_score</th> </thead> <tbody> <th>0</th> <th>1</th> <th>2</th> <th>3</th> <th>4</th> </tbody> <tbody></tbody>
0002-ORFBO 2021-03-15 Refferal 2025-03-15 Standard Annual NaN NaN 13.99 627 12
0003-MKNFE 2020-08-01 Paid 2024-08-01 Premium Annual 2024-09-10 Switched to competitor 12.99 1150 91
0004-TLHLJ 2022-11-20 Organic 2025-11-20 Basic Monthly NaN NaN 6.99 210 34
0011-IGKFF 2019-05-10 Paid 2025-05-10 Premium Annual NaN NaN 22.99 1725 8
0013-EXCHZ 2023-01-05 Refferal 2024-01-05 Standard Monthly 2024-02-28 Too expensive 13.99 195 88
In [36]:
df_db_subscription.info()

<class 'pandas.DataFrame'>
RangeIndex: 21 entries, 0 to 20
Data columns (total 11 columns):
 #   Column                   Non-Null Count  Dtype  
---  ------                   --------------  -----  
 0   customerid               21 non-null     str    
 1   subscription_start_date  21 non-null     str    
 2   subscription_type        21 non-null     str    
 3   renewal_date             21 non-null     str    
 4   plan_type                21 non-null     str    
 5   contract_type            21 non-null     str    
 6   cancellation_date        6 non-null      str    
 7   cancellation_reason      6 non-null      str    
 8   monthly_charges          21 non-null     float64
 9   cltv                     21 non-null     int64  
 10  churn_score              21 non-null     int64  
dtypes: float64(1), int64(2), str(8)
memory usage: 1.9 KB
In [37]:
# Convert all string columns to object first
str_cols = df_db_subscription.select_dtypes(include='string').columns

df_db_subscription[str_cols] = df_db_subscription[str_cols].astype('object')

# Convert date columns to datetime
date_cols = [
    'subscription_start_date',
    'renewal_date',
    'cancellation_date'
]

for col in date_cols:
    df_db_subscription[col] = pd.to_datetime(
        df_db_subscription[col],
        errors='coerce'
    )
In [38]:
df_db_subscription.info()

<class 'pandas.DataFrame'>
RangeIndex: 21 entries, 0 to 20
Data columns (total 11 columns):
 #   Column                   Non-Null Count  Dtype         
---  ------                   --------------  -----         
 0   customerid               21 non-null     object        
 1   subscription_start_date  21 non-null     datetime64[us]
 2   subscription_type        21 non-null     object        
 3   renewal_date             21 non-null     datetime64[us]
 4   plan_type                21 non-null     object        
 5   contract_type            21 non-null     object        
 6   cancellation_date        6 non-null      datetime64[us]
 7   cancellation_reason      6 non-null      object        
 8   monthly_charges          21 non-null     float64       
 9   cltv                     21 non-null     int64         
 10  churn_score              21 non-null     int64         
dtypes: datetime64[us](3), float64(1), int64(2), object(5)
memory usage: 1.9+ KB
In [39]:
df_db_support.head()
Out[39]:
<style scoped> .dataframe tbody tr th:only-of-type { vertical-align: middle; } .dataframe tbody tr th { vertical-align: top; } .dataframe thead th { text-align: right; } </style> <thead> <th></th> <th>customerid</th> <th>complaint_date</th> <th>escalations</th> <th>csat_score</th> <th>col_1</th> <th>comment</th> </thead> <tbody> <th>0</th> <th>1</th> <th>2</th> <th>3</th> <th>4</th> </tbody> <tbody></tbody>
0003-MKNFE 2024-08-28 00:00:00 N 60 None service issue
0003-MKNFE 2024-08-28 00:00:00 Y 10 None demaned refund
0013-EXCHZ 2024-01-20 00:00:00 Y 20 None NaN
0013-MHZWF 2025-03-18 00:00:00 N 90 None guidance to renew
0013-SMEOE 2024-11-01 00:00:00 N 30 None NaN
In [40]:
df_db_support.info()

<class 'pandas.DataFrame'>
RangeIndex: 9 entries, 0 to 8
Data columns (total 6 columns):
 #   Column          Non-Null Count  Dtype 
---  ------          --------------  ----- 
 0   customerid      9 non-null      str   
 1   complaint_date  9 non-null      str   
 2   escalations     9 non-null      str   
 3   csat_score      9 non-null      int64 
 4   col_1           0 non-null      object
 5   comment         4 non-null      str   
dtypes: int64(1), object(1), str(4)
memory usage: 564.0+ bytes
In [41]:
df_db_support.drop(
    columns=['col_1', 'comment'],
    inplace=True,
    errors='ignore'
)
In [42]:
df_db_support.info()

<class 'pandas.DataFrame'>
RangeIndex: 9 entries, 0 to 8
Data columns (total 4 columns):
 #   Column          Non-Null Count  Dtype
---  ------          --------------  -----
 0   customerid      9 non-null      str  
 1   complaint_date  9 non-null      str  
 2   escalations     9 non-null      str  
 3   csat_score      9 non-null      int64
dtypes: int64(1), str(3)
memory usage: 420.0 bytes
In [43]:
# Convert all string columns to object
str_cols = df_db_support.select_dtypes(include='string').columns
df_db_support[str_cols] = df_db_support[str_cols].astype('object')

# Convert complaint_date to datetime
df_db_support['complaint_date'] = pd.to_datetime(
    df_db_support['complaint_date'],
    errors='coerce'
)
In [44]:
df_db_support.info()

<class 'pandas.DataFrame'>
RangeIndex: 9 entries, 0 to 8
Data columns (total 4 columns):
 #   Column          Non-Null Count  Dtype         
---  ------          --------------  -----         
 0   customerid      9 non-null      object        
 1   complaint_date  9 non-null      datetime64[us]
 2   escalations     9 non-null      object        
 3   csat_score      9 non-null      int64         
dtypes: datetime64[us](1), int64(1), object(2)
memory usage: 420.0+ bytes
In [45]:
# 3. Featrure Enginerring and data analysis
In [46]:
#create a new cloumn using existing col 
df_db_subscription['churn_flag'] = np.where(df_db_subscription['cancellation_date'].notna(),1,0)
In [47]:
df_db_subscription.head()
Out[47]:
<style scoped> .dataframe tbody tr th:only-of-type { vertical-align: middle; } .dataframe tbody tr th { vertical-align: top; } .dataframe thead th { text-align: right; } </style> <thead> <th></th> <th>customerid</th> <th>subscription_start_date</th> <th>subscription_type</th> <th>renewal_date</th> <th>plan_type</th> <th>contract_type</th> <th>cancellation_date</th> <th>cancellation_reason</th> <th>monthly_charges</th> <th>cltv</th> <th>churn_score</th> <th>churn_flag</th> </thead> <tbody> <th>0</th> <th>1</th> <th>2</th> <th>3</th> <th>4</th> </tbody> <tbody></tbody>
0002-ORFBO 2021-03-15 Refferal 2025-03-15 Standard Annual NaT NaN 13.99 627 12 0
0003-MKNFE 2020-08-01 Paid 2024-08-01 Premium Annual 2024-09-10 Switched to competitor 12.99 1150 91 1
0004-TLHLJ 2022-11-20 Organic 2025-11-20 Basic Monthly NaT NaN 6.99 210 34 0
0011-IGKFF 2019-05-10 Paid 2025-05-10 Premium Annual NaT NaN 22.99 1725 8 0
0013-EXCHZ 2023-01-05 Refferal 2024-01-05 Standard Monthly 2024-02-28 Too expensive 13.99 195 88 1
In [48]:
df = (df_db_subscription
    .merge(df_db_customer , on ='customerid' , how = 'left')
    .merge(df_db_support, on ='customerid' , how = 'left'))
In [49]:
df.shape
Out[49]:
(23, 20)
In [50]:
df_db_subscription['customerid'].nunique()
Out[50]:
21
In [51]:
df_db_customer['customerid'].nunique()
Out[51]:
21
In [52]:
df_db_support['customerid'].nunique()
Out[52]:
7
In [53]:
df_db_support
Out[53]:
<style scoped> .dataframe tbody tr th:only-of-type { vertical-align: middle; } .dataframe tbody tr th { vertical-align: top; } .dataframe thead th { text-align: right; } </style> <thead> <th></th> <th>customerid</th> <th>complaint_date</th> <th>escalations</th> <th>csat_score</th> </thead> <tbody> <th>0</th> <th>1</th> <th>2</th> <th>3</th> <th>4</th> <th>5</th> <th>6</th> <th>7</th> <th>8</th> </tbody> <tbody></tbody>
0003-MKNFE 2024-08-28 N 60
0003-MKNFE 2024-08-28 Y 10
0013-EXCHZ 2024-01-20 Y 20
0013-MHZWF 2025-03-18 N 90
0013-SMEOE 2024-11-01 N 30
0017-IUDMW 2024-04-10 Y 25
0019-EFAEP 2024-09-27 Y 30
0022-TCJCI 2024-09-13 Y 10
0022-TCJCI 2024-09-14 N 90
In [54]:
#as it has repeated entries we will on consider those which has last occurance 
df_db_support['complaint_count'] = df_db_support.groupby('customerid')['customerid'].transform('count')
In [55]:
df_db_support = df_db_support.sort_values('complaint_date').drop_duplicates('customerid',keep ='last')
In [56]:
df_db_support['customerid'].size
Out[56]:
7
In [57]:
#merge df
df = (df_db_subscription 
      .merge(df_db_customer, on = 'customerid' , how ='left')
      .merge(df_db_support, on = 'customerid' , how = 'left'))
In [58]:
df.shape
Out[58]:
(21, 21)
In [59]:
df.to_csv('exported_churn_data.csv',index=False)
In [ ]:
In [60]:
import os

print(os.getcwd())

C:\Users\Hp
In [61]:
print(os.path.abspath('exported_churn_data.csv'))

C:\Users\Hp\exported_churn_data.csv
In [ ]:
In [62]:
# DATA ANALYSIS
#1. charn rate
In [63]:
df.columns
Out[63]:
Index(['customerid', 'subscription_start_date', 'subscription_type',
       'renewal_date', 'plan_type', 'contract_type', 'cancellation_date',
       'cancellation_reason', 'monthly_charges', 'cltv', 'churn_score',
       'churn_flag', 'customer_name', 'country', 'state', 'gender', 'dob',
       'complaint_date', 'escalations', 'csat_score', 'complaint_count'],
      dtype='str')
In [64]:
churn_rate = df['churn_flag'].mean()*100
print("churn Rate is : ",round(churn_rate,2),'%')

churn Rate is :  28.57 %
In [65]:
#2. Retantion rate
retention_rate = 100-churn_rate
print("Retention Rate is : ",round(retention_rate,2),'%')

Retention Rate is :  71.43 %
In [66]:
df.head(2)
Out[66]:
<style scoped> .dataframe tbody tr th:only-of-type { vertical-align: middle; } .dataframe tbody tr th { vertical-align: top; } .dataframe thead th { text-align: right; } </style> <thead> <th></th> <th>customerid</th> <th>subscription_start_date</th> <th>subscription_type</th> <th>renewal_date</th> <th>plan_type</th> <th>contract_type</th> <th>cancellation_date</th> <th>cancellation_reason</th> <th>monthly_charges</th> <th>cltv</th> <th>...</th> <th>churn_flag</th> <th>customer_name</th> <th>country</th> <th>state</th> <th>gender</th> <th>dob</th> <th>complaint_date</th> <th>escalations</th> <th>csat_score</th> <th>complaint_count</th> </thead> <tbody> <th>0</th> <th>1</th> </tbody> <tbody></tbody>
0002-ORFBO 2021-03-15 Refferal 2025-03-15 Standard Annual NaT NaN 13.99 627 ... 0 keshav India Maharashtra Male 1982-04-12 NaT NaN NaN NaN
0003-MKNFE 2020-08-01 Paid 2024-08-01 Premium Annual 2024-09-10 Switched to competitor 12.99 1150 ... 1 raghav India Karnataka Male 1995-11-23 2024-08-28 Y 10.0 2.0
<p>2 rows × 21 columns</p>
In [67]:
# 3. churn by plan type 
churn_by_plan = df.groupby('plan_type')['churn_flag'].mean().mul(100).round(2).reset_index(name ='churn_rate_percentage')
print(churn_by_plan)

  plan_type  churn_rate_percentage
0     Basic                  60.00
1   Premium                  14.29
2  Standard                  22.22
In [68]:
# 4 a. churn by state + sum(revenue) & count of users
churn_by_state = (
    df.groupby('state')
      .agg(
          total_revenue=('monthly_charges', 'sum'),
          user_count=('customerid', 'nunique'),
          churned_users=('churn_flag', 'sum')
      )
      .reset_index()
)

churn_by_state
Out[68]:
<style scoped> .dataframe tbody tr th:only-of-type { vertical-align: middle; } .dataframe tbody tr th { vertical-align: top; } .dataframe thead th { text-align: right; } </style> <thead> <th></th> <th>state</th> <th>total_revenue</th> <th>user_count</th> <th>churned_users</th> </thead> <tbody> <th>0</th> <th>1</th> <th>2</th> <th>3</th> <th>4</th> <th>5</th> <th>6</th> <th>7</th> <th>8</th> </tbody> <tbody></tbody>
Delhi 52.96 4 1
Karnataka 20.98 2 2
Kathmandu 20.98 2 0
Maharashtra 50.97 3 0
Meghalaya 42.97 3 2
Nagaland 22.99 1 0
Rajasthan 36.98 2 0
Telangana 30.98 2 1
Uttar Pradesh 115.98 2 0
In [69]:
churn_by_state['churn_rate'] = (
    churn_by_state['churned_users']
    / churn_by_state['user_count']
    * 100
)

churn_by_state
Out[69]:
<style scoped> .dataframe tbody tr th:only-of-type { vertical-align: middle; } .dataframe tbody tr th { vertical-align: top; } .dataframe thead th { text-align: right; } </style> <thead> <th></th> <th>state</th> <th>total_revenue</th> <th>user_count</th> <th>churned_users</th> <th>churn_rate</th> </thead> <tbody> <th>0</th> <th>1</th> <th>2</th> <th>3</th> <th>4</th> <th>5</th> <th>6</th> <th>7</th> <th>8</th> </tbody> <tbody></tbody>
Delhi 52.96 4 1 25.000000
Karnataka 20.98 2 2 100.000000
Kathmandu 20.98 2 0 0.000000
Maharashtra 50.97 3 0 0.000000
Meghalaya 42.97 3 2 66.666667
Nagaland 22.99 1 0 0.000000
Rajasthan 36.98 2 0 0.000000
Telangana 30.98 2 1 50.000000
Uttar Pradesh 115.98 2 0 0.000000
In [70]:
# 4b .churn by subscription type + sum(revenue) & count of users
churn_by_subscription = (
    df.groupby('subscription_type')
      .agg(
          total_revenue=('monthly_charges', 'sum'),
          user_count=('customerid', 'nunique'),
          churned_users=('churn_flag', 'sum')
      )
      .reset_index()
)

churn_by_subscription
Out[70]:
<style scoped> .dataframe tbody tr th:only-of-type { vertical-align: middle; } .dataframe tbody tr th { vertical-align: top; } .dataframe thead th { text-align: right; } </style> <thead> <th></th> <th>subscription_type</th> <th>total_revenue</th> <th>user_count</th> <th>churned_users</th> </thead> <tbody> <th>0</th> <th>1</th> <th>2</th> </tbody> <tbody></tbody>
Organic 145.91 9 0
Paid 174.94 6 1
Refferal 74.94 6 5
In [71]:
churn_by_subscription['churn_rate'] = (
    churn_by_subscription['churned_users']
    / churn_by_subscription['user_count']
    * 100
)

churn_by_subscription
Out[71]:
<style scoped> .dataframe tbody tr th:only-of-type { vertical-align: middle; } .dataframe tbody tr th { vertical-align: top; } .dataframe thead th { text-align: right; } </style> <thead> <th></th> <th>subscription_type</th> <th>total_revenue</th> <th>user_count</th> <th>churned_users</th> <th>churn_rate</th> </thead> <tbody> <th>0</th> <th>1</th> <th>2</th> </tbody> <tbody></tbody>
Organic 145.91 9 0 0.000000
Paid 174.94 6 1 16.666667
Refferal 74.94 6 5 83.333333
In [72]:
# 5.ARPU - avg revenue per user
df.columns
Out[72]:
Index(['customerid', 'subscription_start_date', 'subscription_type',
       'renewal_date', 'plan_type', 'contract_type', 'cancellation_date',
       'cancellation_reason', 'monthly_charges', 'cltv', 'churn_score',
       'churn_flag', 'customer_name', 'country', 'state', 'gender', 'dob',
       'complaint_date', 'escalations', 'csat_score', 'complaint_count'],
      dtype='str')
In [73]:
arpu = df['monthly_charges'].mean()
print("ARPU =" , round(arpu,2))

ARPU = 18.85
In [74]:
#calculte customer age 
today = pd.Timestamp.today()

df['age'] = (
    today.year
    - df['dob'].dt.year
    - (
        (today.month < df['dob'].dt.month)
        | (
            (today.month == df['dob'].dt.month)
            & (today.day < df['dob'].dt.day)
        )
    )
)
df[['dob', 'age']]
Out[74]:
<style scoped> .dataframe tbody tr th:only-of-type { vertical-align: middle; } .dataframe tbody tr th { vertical-align: top; } .dataframe thead th { text-align: right; } </style> <thead> <th></th> <th>dob</th> <th>age</th> </thead> <tbody> <th>0</th> <th>1</th> <th>2</th> <th>3</th> <th>4</th> <th>5</th> <th>6</th> <th>7</th> <th>8</th> <th>9</th> <th>10</th> <th>11</th> <th>12</th> <th>13</th> <th>14</th> <th>15</th> <th>16</th> <th>17</th> <th>18</th> <th>19</th> <th>20</th> </tbody> <tbody></tbody>
1982-04-12 44
1995-11-23 30
1978-02-15 48
2001-08-30 24
1990-05-05 36
1988-12-10 37
1976-09-21 49
1999-03-14 27
1985-07-07 41
1993-10-29 32
1997-01-22 29
1981-06-18 45
2004-12-01 21
1992-04-25 34
1979-11-11 46
1986-02-28 40
1994-08-19 31
2000-09-02 25
1983-12-30 42
1991-05-14 35
1977-10-06 48
In [75]:
# 6. no. of days between the tenure (avg customer tenture)
# count of days user has used our service: cancellation date else current date
In [76]:
today = pd.Timestamp.today()
#print(today)
df['tenure_days'] = np.where(
    df['cancellation_date'].notna(), 

    (df['cancellation_date'] - df['subscription_start_date']).dt.days,

    (today - df['subscription_start_date']).dt.days
)
df
avg_tenture = df['tenure_days'].mean()
print("AVG TENTURE (Days) =",round(avg_tenture),0)

AVG TENTURE (Days) = 1518 0
In [77]:
# 7. revenue at risk - revenue lost from churned users
revenue_at_risk = df.loc[df['churn_flag'] == 1 , 'monthly_charges'].sum()
print("Revenue at Risk(Rs K) = " , revenue_at_risk)

Revenue at Risk(Rs K) =  73.94
In [78]:
# 8. escalation rate
escalation_rate = (df['escalations'] == 'Y').mean()*100
print("escalation rate is :",escalation_rate)

escalation rate is : 19.047619047619047
In [79]:
# 9. avg complaint rate per user
avg_complaint_rate = df['complaint_count'].mean()

print("Avg complaint rate is :",round(avg_complaint_rate,2))

Avg complaint rate is : 1.29
In [80]:
# 10 . correlation escalation vs churn


#df['escalations'] = np.where(df['escalations'] == 'Y',1,0) #encoding string to n type 

corr_df = df[['escalations' , 'churn_flag' ]].dropna()
#correlation
correlation = corr_df['escalations'].corr(df['churn_flag'])
print("correlation is :",correlation)

---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
Cell In[80], line 8
      4 #df['escalations'] = np.where(df['escalations'] == 'Y',1,0) #encoding string to n type
      5 
      6 corr_df = df[['escalations' , 'churn_flag' ]].dropna()
      7 #correlation
----> 8 correlation = corr_df['escalations'].corr(df['churn_flag'])
      9 print("correlation is :",correlation)

File ~\AppData\Roaming\Python\Python314\site-packages\pandas\core\series.py:2770, in Series.corr(self, other, method, min_periods)
   2767 if len(this) == 0:
   2768     return np.nan
-> 2770 this_values = this.to_numpy(dtype=float, na_value=np.nan, copy=False)
   2771 other_values = other.to_numpy(dtype=float, na_value=np.nan, copy=False)
   2773 if method in ["pearson", "spearman", "kendall"] or callable(method):

File ~\AppData\Roaming\Python\Python314\site-packages\pandas\core\base.py:689, in IndexOpsMixin.to_numpy(self, dtype, copy, na_value, **kwargs)
    685         values = values.copy()
    687     values[np.asanyarray(isna(self))] = na_value
--> 689 result = np.asarray(values, dtype=dtype)
    691 if (copy and not fillna) or not copy:
    692     if np.shares_memory(self._values[:2], result[:2]):
    693         # Take slices to improve performance of check

ValueError: could not convert string to float: 'Y'
In [ ]:
df.head(3)
In [81]:
# 11. churn risk create a column using existing columns
#df['churn_score'].head()

conditions = [
    (df['churn_score'] < 50),
    (df['churn_score'] >= 50) & (df['churn_score'] < 70),
    (df['churn_score'] >= 70)
]

choices = ['low','mid','high']

df['churn_risk'] = np.select(conditions,choices,default='unknown')
In [82]:
df[['churn_risk','churn_score']].head()
Out[82]:
<style scoped> .dataframe tbody tr th:only-of-type { vertical-align: middle; } .dataframe tbody tr th { vertical-align: top; } .dataframe thead th { text-align: right; } </style> <thead> <th></th> <th>churn_risk</th> <th>churn_score</th> </thead> <tbody> <th>0</th> <th>1</th> <th>2</th> <th>3</th> <th>4</th> </tbody> <tbody></tbody>
low 12
high 91
low 34
low 8
high 88
In [ ]:
In [ ]:
# 4. data visualization using matplotlib
In [88]:
df.columns
Out[88]:
Index(['customerid', 'subscription_start_date', 'subscription_type',
       'renewal_date', 'plan_type', 'contract_type', 'cancellation_date',
       'cancellation_reason', 'monthly_charges', 'cltv', 'churn_score',
       'churn_flag', 'customer_name', 'country', 'state', 'gender', 'dob',
       'complaint_date', 'escalations', 'csat_score', 'complaint_count', 'age',
       'tenure_days', 'churn_risk'],
      dtype='str')
In [83]:
df.head()
Out[83]:
<style scoped> .dataframe tbody tr th:only-of-type { vertical-align: middle; } .dataframe tbody tr th { vertical-align: top; } .dataframe thead th { text-align: right; } </style> <thead> <th></th> <th>customerid</th> <th>subscription_start_date</th> <th>subscription_type</th> <th>renewal_date</th> <th>plan_type</th> <th>contract_type</th> <th>cancellation_date</th> <th>cancellation_reason</th> <th>monthly_charges</th> <th>cltv</th> <th>...</th> <th>state</th> <th>gender</th> <th>dob</th> <th>complaint_date</th> <th>escalations</th> <th>csat_score</th> <th>complaint_count</th> <th>age</th> <th>tenure_days</th> <th>churn_risk</th> </thead> <tbody> <th>0</th> <th>1</th> <th>2</th> <th>3</th> <th>4</th> </tbody> <tbody></tbody>
0002-ORFBO 2021-03-15 Refferal 2025-03-15 Standard Annual NaT NaN 13.99 627 ... Maharashtra Male 1982-04-12 NaT NaN NaN NaN 44 1980.0 low
0003-MKNFE 2020-08-01 Paid 2024-08-01 Premium Annual 2024-09-10 Switched to competitor 12.99 1150 ... Karnataka Male 1995-11-23 2024-08-28 Y 10.0 2.0 30 1501.0 high
0004-TLHLJ 2022-11-20 Organic 2025-11-20 Basic Monthly NaT NaN 6.99 210 ... Delhi Female 1978-02-15 NaT NaN NaN NaN 48 1365.0 low
0011-IGKFF 2019-05-10 Paid 2025-05-10 Premium Annual NaT NaN 22.99 1725 ... Nagaland Male 2001-08-30 NaT NaN NaN NaN 24 2655.0 low
0013-EXCHZ 2023-01-05 Refferal 2024-01-05 Standard Monthly 2024-02-28 Too expensive 13.99 195 ... Delhi Female 1990-05-05 2024-01-20 Y 20.0 1.0 36 419.0 high
<p>5 rows × 24 columns</p>
In [84]:
df_visual = df.copy()
In [85]:
df_visual.shape
Out[85]:
(21, 24)
In [100]:
# 4.1 monthly churn trend (this is time series kpi)
df_visual['cancellation_month'] = df_visual['cancellation_date'].dt.to_period('M')

churn_trend = df_visual[df_visual['churn_flag'] == 1 ].groupby('cancellation_month').size()
plt.figure(figsize=(8,3))
plt.plot(
    churn_trend.index.astype(str),
    churn_trend.values,
    color='green',
    marker='o',
    linestyle='dashed',
    linewidth=2,
    markersize=12
)
plt.title('Monthly churn trend')
plt.xlabel('Month')
plt.ylabel('churned customer')
plt.show()
No description has been provided for this image
In [104]:
# 4.2 churn by plan type 

# Churned users by plan type
churn_by_plan = (
    df.groupby('plan_type')['churn_flag']
      .sum()
      .sort_values(ascending=False)
)
churn_by_plan

plt.figure(figsize=(8, 5))

plt.bar(
    churn_by_plan.index,
    churn_by_plan.values
)

plt.title('Churn by Plan Type')
plt.xlabel('Plan Type')
plt.ylabel('Number of Churned Customers')

plt.xticks(rotation=45)
plt.tight_layout()

plt.show()
No description has been provided for this image
In [105]:
#4.3 churn by state

import matplotlib.pyplot as plt

# Calculate churned customers by state
churn_by_state = (
    df.groupby('state')['churn_flag']
      .sum()
      .sort_values(ascending=True)
)

# Plot
plt.figure(figsize=(10, 7))

plt.barh(
    churn_by_state.index,
    churn_by_state.values
)

plt.title('Churn by State')
plt.xlabel('Number of Churned Customers')
plt.ylabel('State')

plt.tight_layout()
plt.show()
No description has been provided for this image
In [ ]:
# 5. data visualization using seaborn
In [107]:
#encodig - convert str to numeric so that we can find corr between features
df_visual.columns
Out[107]:
Index(['customerid', 'subscription_start_date', 'subscription_type',
       'renewal_date', 'plan_type', 'contract_type', 'cancellation_date',
       'cancellation_reason', 'monthly_charges', 'cltv', 'churn_score',
       'churn_flag', 'customer_name', 'country', 'state', 'gender', 'dob',
       'complaint_date', 'escalations', 'csat_score', 'complaint_count', 'age',
       'tenure_days', 'churn_risk', 'cancellation_month'],
      dtype='str')
In [108]:
#df_encoded =
df_visual[['plan_type','contract_type','churn_score','churn_flag','churn_flag','escalations']].head()
Out[108]:
<style scoped> .dataframe tbody tr th:only-of-type { vertical-align: middle; } .dataframe tbody tr th { vertical-align: top; } .dataframe thead th { text-align: right; } </style> <thead> <th></th> <th>plan_type</th> <th>contract_type</th> <th>churn_score</th> <th>churn_flag</th> <th>churn_flag</th> <th>escalations</th> </thead> <tbody> <th>0</th> <th>1</th> <th>2</th> <th>3</th> <th>4</th> </tbody> <tbody></tbody>
Standard Annual 12 0 0 NaN
Premium Annual 91 1 1 Y
Basic Monthly 34 0 0 NaN
Premium Annual 8 0 0 NaN
Standard Monthly 88 1 1 Y
In [114]:
df_encoded.head()
Out[114]:
<style scoped> .dataframe tbody tr th:only-of-type { vertical-align: middle; } .dataframe tbody tr th { vertical-align: top; } .dataframe thead th { text-align: right; } </style> <thead> <th></th> <th>plan_type</th> <th>contract_type</th> <th>churn_score</th> <th>churn_flag</th> <th>escalations</th> <th>churn_risk</th> </thead> <tbody> <th>0</th> <th>1</th> <th>2</th> <th>3</th> <th>4</th> </tbody> <tbody></tbody>
2 0 12 0 -1 1
1 0 91 1 1 0
0 1 34 0 -1 1
1 0 8 0 -1 1
2 1 88 1 1 0
In [118]:
# Select required columns
# incorrect method of encoding as numbers are not assigned based on priority 
df_encoded = df_visual[
    ['plan_type',
     'contract_type',
     'churn_score',
     'churn_flag',
     'escalations',
     'churn_risk']
].copy()

# Categorical columns to encode
categorical_cols = [
    'plan_type',
    'contract_type',
    'churn_risk',
    'escalations'
]

# Convert categorical columns into numeric codes
for col in categorical_cols:
    df_encoded[col] = df_encoded[col].astype('category').cat.codes

df_encoded.head()
Out[118]:
<style scoped> .dataframe tbody tr th:only-of-type { vertical-align: middle; } .dataframe tbody tr th { vertical-align: top; } .dataframe thead th { text-align: right; } </style> <thead> <th></th> <th>plan_type</th> <th>contract_type</th> <th>churn_score</th> <th>churn_flag</th> <th>escalations</th> <th>churn_risk</th> </thead> <tbody> <th>0</th> <th>1</th> <th>2</th> <th>3</th> <th>4</th> </tbody> <tbody></tbody>
2 0 12 0 -1 1
1 0 91 1 1 0
0 1 34 0 -1 1
1 0 8 0 -1 1
2 1 88 1 1 0
In [117]:
# Heatmap(correlation matrix)

import seaborn as sns
import matplotlib.pyplot as plt

sns.heatmap(
    df_encoded.corr(),
    annot=True
)

plt.show()
No description has been provided for this image
In [119]:
# Select required columns
# correct method of encoding based on priority
df_encoded = df_visual[
    [
        'plan_type',
        'contract_type',
        'churn_risk',
        'churn_score',
        'churn_flag',
        'escalations'
    ]
].copy()


# Define priority/order manually

plan_mapping = {
    'basic': 0,
    'standard': 1,
    'premium': 2
}

contract_mapping = {
    'monthly': 0,
    'annual': 1
}

risk_mapping = {
    'low': 0,
    'med': 1,
    'high': 2
}


# Apply encoding
df_encoded['plan_type'] = (
    df_encoded['plan_type']
    .str.lower()
    .str.strip()
    .map(plan_mapping)
)

df_encoded['contract_type'] = (
    df_encoded['contract_type']
    .str.lower()
    .str.strip()
    .map(contract_mapping)
)

df_encoded['churn_risk'] = (
    df_encoded['churn_risk']
    .str.lower()
    .str.strip()
    .map(risk_mapping)
)

# Escalations Y/N
df_encoded['escalations'] = (
    df_encoded['escalations']
    .map({'N': 0, 'Y': 1})
)

df_encoded.head()
Out[119]:
<style scoped> .dataframe tbody tr th:only-of-type { vertical-align: middle; } .dataframe tbody tr th { vertical-align: top; } .dataframe thead th { text-align: right; } </style> <thead> <th></th> <th>plan_type</th> <th>contract_type</th> <th>churn_risk</th> <th>churn_score</th> <th>churn_flag</th> <th>escalations</th> </thead> <tbody> <th>0</th> <th>1</th> <th>2</th> <th>3</th> <th>4</th> </tbody> <tbody></tbody>
1 1 0.0 12 0 NaN
2 1 2.0 91 1 1.0
0 0 0.0 34 0 NaN
2 1 0.0 8 0 NaN
1 0 2.0 88 1 1.0
In [120]:
# Heatmap(correlation matrix)

import seaborn as sns
import matplotlib.pyplot as plt

sns.heatmap(
    df_encoded.corr(),
    annot=True
)

plt.show()
No description has been provided for this image
In [121]:
import matplotlib.pyplot as plt
import numpy as np

# Correlation matrix
corr_matrix = df_encoded.corr()

# Create figure
plt.figure(figsize=(9, 7))

# Create heatmap
plt.imshow(corr_matrix, aspect='auto')

# Add colorbar
plt.colorbar(label='Correlation')

# Axis labels
plt.xticks(
    range(len(corr_matrix.columns)),
    corr_matrix.columns,
    rotation=45,
    ha='right'
)

plt.yticks(
    range(len(corr_matrix.columns)),
    corr_matrix.columns
)

# Add correlation values inside each cell
for i in range(len(corr_matrix.columns)):
    for j in range(len(corr_matrix.columns)):
        plt.text(
            j,
            i,
            f'{corr_matrix.iloc[i, j]:.2f}',
            ha='center',
            va='center'
        )

plt.title('Correlation Heatmap')

plt.tight_layout()
plt.show()
No description has been provided for this image
In [122]:
# pair plot for relationship between our dataset 
sns.pairplot(df_encoded)

plt.show()
No description has been provided for this image
In [126]:
# catplot/ facegrid plot - multi dimension comaprision

sns.catplot(data=df_visual,
          
          x='plan_type',
          y='monthly_charges',
          hue='gender',
          col='churn_risk')
Out[126]:
<seaborn.axisgrid.FacetGrid at 0x29f6401a120>
No description has been provided for this image
In [ ]:
In [ ]:
# pivot tabel using pandas
In [127]:
pd.pivot_table(
    df_visual,
    index = 'plan_type',
    values = 'churn_flag',
    aggfunc = 'mean',
)
Out[127]:
<style scoped> .dataframe tbody tr th:only-of-type { vertical-align: middle; } .dataframe tbody tr th { vertical-align: top; } .dataframe thead th { text-align: right; } </style> <thead> <th></th> <th>churn_flag</th> <th>plan_type</th> <th></th> </thead> <tbody> <th>Basic</th> <th>Premium</th> <th>Standard</th> </tbody> <tbody></tbody>
0.600000
0.142857
0.222222
In [ ]:
In [128]:
pd.pivot_table(
    df_visual,
    index = 'plan_type',
    values = ['monthly_charges','customerid','churn_flag'],
    aggfunc = {'monthly_charges': 'sum',
    'customerid' : 'nunique',
    'churn_flag':'mean',
              }
)
Out[128]:
<style scoped> .dataframe tbody tr th:only-of-type { vertical-align: middle; } .dataframe tbody tr th { vertical-align: top; } .dataframe thead th { text-align: right; } </style> <thead> <th></th> <th>churn_flag</th> <th>customerid</th> <th>monthly_charges</th> <th>plan_type</th> <th></th> <th></th> <th></th> </thead> <tbody> <th>Basic</th> <th>Premium</th> <th>Standard</th> </tbody> <tbody></tbody>
0.600000 5 52.95
0.142857 7 218.93
0.222222 9 123.91
In [ ]:
# working with sql in python (pandas)
In [129]:
#create db in sql
conn = sqlite3.connect('test_database.sqlite')
 #table details
conn.execute("CREATE TABLE users (first_name TEXT, country TEXT, budget INTEGER)")

#commit and save
conn.commit()
In [131]:
# insert data 

cursor = conn.cursor()

cursor.execute("""
    INSERT INTO users VALUES
    ('Aarav', 'India', 6200),
    ('Emma', 'USA', 7500),
    ('Lucas', 'Germany', 6800),
    ('Sophia', 'Canada', 7100)
    """
)

#commit and save
conn.commit()

print("data inserted successfully")

data inserted successfully
In [132]:
#check inserted data in table 
conn = sqlite3.connect('test_database.sqlite')
query = """SELECT * FROM users"""

df_results = pd.read_sql(query,conn)

df_results.head()
Out[132]:
<style scoped> .dataframe tbody tr th:only-of-type { vertical-align: middle; } .dataframe tbody tr th { vertical-align: top; } .dataframe thead th { text-align: right; } </style> <thead> <th></th> <th>first_name</th> <th>country</th> <th>budget</th> </thead> <tbody> <th>0</th> <th>1</th> <th>2</th> <th>3</th> <th>4</th> </tbody> <tbody></tbody>
Aarav India 6200
Emma USA 7500
Lucas Germany 6800
Sophia Canada 7100
Aarav India 6200
In [133]:
#aggregation 
query = """
    SELECT country, sum(budget) as total_budget
    FROM users
    GROUP BY country
"""

df_agg = pd.read_sql(query,conn)

df_agg
Out[133]:
<style scoped> .dataframe tbody tr th:only-of-type { vertical-align: middle; } .dataframe tbody tr th { vertical-align: top; } .dataframe thead th { text-align: right; } </style> <thead> <th></th> <th>country</th> <th>total_budget</th> </thead> <tbody> <th>0</th> <th>1</th> <th>2</th> <th>3</th> </tbody> <tbody></tbody>
Canada 14200
Germany 13600
India 12400
USA 15000
In [135]:
# ==========================================
# KEY INSIGHTS FROM CUSTOMER CHURN ANALYSIS
# ==========================================

# 1. Total customers
total_customers = df['customerid'].nunique()


# 2. Total churned customers
total_churned = df['churn_flag'].sum()


# 3. Overall churn rate
churn_rate = df['churn_flag'].mean() * 100


# 4. Average customer age
avg_age = df['age'].mean()


# 5. Average customer tenure
avg_tenure = df['tenure_days'].mean()


# 6. Average monthly charges
avg_monthly_charges = df['monthly_charges'].mean()


# 7. Total Revenue
total_revenue = df['monthly_charges'].sum()


# 8. Revenue loss due to churn
revenue_loss_churn = df.loc[
    df['churn_flag'] == 1,
    'monthly_charges'
].sum()


# 9. Revenue loss percentage
revenue_loss_percentage = (
    revenue_loss_churn / total_revenue
) * 100


# 10. Average CLTV
avg_cltv = df['cltv'].mean()


# 11. Average complaints per customer
avg_complaints = df['complaint_count'].mean()


# 12. Escalation rate
escalation_rate = (
    df['escalations'] == 'Y'
).mean() * 100


# 13. Plan type churn rate
plan_churn = (
    df.groupby('plan_type')['churn_flag']
      .mean()
      .mul(100)
      .sort_values(ascending=False)
)

highest_churn_plan = plan_churn.index[0]
highest_churn_plan_rate = plan_churn.iloc[0]


# 14. Subscription type churn rate
subscription_churn = (
    df.groupby('subscription_type')['churn_flag']
      .mean()
      .mul(100)
      .sort_values(ascending=False)
)

highest_churn_subscription = subscription_churn.index[0]
highest_churn_subscription_rate = subscription_churn.iloc[0]


# 15. State churn rate
state_churn = (
    df.groupby('state')['churn_flag']
      .mean()
      .mul(100)
      .sort_values(ascending=False)
)

highest_churn_state = state_churn.index[0]
highest_churn_state_rate = state_churn.iloc[0]


# 16. Monthly vs Annual contract churn percentage
contract_churn = (
    df.groupby('contract_type')['churn_flag']
      .mean()
      .mul(100)
)

monthly_churn = contract_churn.get('monthly', 0)
annual_churn = contract_churn.get('annual', 0)


# ==================
# PRINT THE INSIGHTS
# ==================

print("CUSTOMER CHURN - KEY INSIGHTS")
print("=" * 60)

print(f"1. Total Customers: {total_customers:,}")

print(f"2. Total Churned Customers: {total_churned:,}")

print(f"3. Overall Churn Rate: {churn_rate:.2f}%")

print(f"4. Average Customer Age: {avg_age:.1f} years")

print(f"5. Average Customer Tenure: {avg_tenure:.1f} days")

print(f"6. Average Monthly Charges: {avg_monthly_charges:,.2f}")

print(f"7. Total Revenue: {total_revenue:,.2f}")

print(
    f"8. Revenue Loss Due to Churn: "
    f"{revenue_loss_churn:,.2f}"
)

print(
    f"9. Revenue Lost Due to Churn (%): "
    f"{revenue_loss_percentage:.2f}%"
)

print(f"10. Average CLTV: {avg_cltv:,.2f}")

print(
    f"11. Average Complaints per Customer: "
    f"{avg_complaints:.2f}"
)

print(f"12. Escalation Rate: {escalation_rate:.2f}%")

print(
    f"13. Highest Churn Plan: "
    f"{highest_churn_plan} "
    f"({highest_churn_plan_rate:.2f}%)"
)

print(
    f"14. Highest Churn Subscription Type: "
    f"{highest_churn_subscription} "
    f"({highest_churn_subscription_rate:.2f}%)"
)

print(
    f"15. State with Highest Churn Rate: "
    f"{highest_churn_state} "
    f"({highest_churn_state_rate:.2f}%)"
)

print("\nCONTRACT TYPE CHURN")
print("-" * 60)

print(f"16. Monthly Contract Churn Rate: {monthly_churn:.2f}%")
print(f"17. Annual Contract Churn Rate: {annual_churn:.2f}%")

CUSTOMER CHURN - KEY INSIGHTS
============================================================
1. Total Customers: 21
2. Total Churned Customers: 6
3. Overall Churn Rate: 28.57%
4. Average Customer Age: 36.4 years
5. Average Customer Tenure: 1518.0 days
6. Average Monthly Charges: 18.85
7. Total Revenue: 395.79
8. Revenue Loss Due to Churn: 73.94
9. Revenue Lost Due to Churn (%): 18.68%
10. Average CLTV: 823.52
11. Average Complaints per Customer: 1.29
12. Escalation Rate: 19.05%
13. Highest Churn Plan: Basic (60.00%)
14. Highest Churn Subscription Type: Refferal (83.33%)
15. State with Highest Churn Rate: Karnataka (100.00%)

CONTRACT TYPE CHURN
------------------------------------------------------------
16. Monthly Contract Churn Rate: 0.00%
17. Annual Contract Churn Rate: 0.00%
In [ ]:
In [ ]:
In [ ]: