<jupyter_start><jupyter_text>Task 4.a Data Cleansing Delete duplicated data.First exlude the invoice_item_id for duplication detection because this variable has unique value<jupyter_code>data['duplicated'] = data.duplicated(subset=data.columns.difference(['invoice_item_id']))
data
duplicatedData = data.query('duplicated==True')
duplicatedData
cleanData = data[data['duplicated'] == False]
cleanData<jupyter_output><empty_output><jupyter_text>Delete 3608 data we indicate as duplicate data and remove it from the dataframe  Task 4.b Total Revenue Calculate revenue for each product<jupyter_code>paidProduct = cleanData[cleanData['invoice_status'] == 'Paid']
paidProduct
paidProduct[paidProduct['client_id']==77223]
productCount = paidProduct[['product_id' , 'discounted_price']].groupby(['product_id']).agg(['sum', 'count'])
productCount
productCount.discounted_price.sort_values(by='sum', ascending=False)<jupyter_output><empty_output><jupyter_text>Task 4.c Loyal Customer The answer for 4.c included on section 4.e  Task 4.d Average Time Duration Calculate time duration for clients to complete their payments using invoice_created_at and invoice_paid_at<jupyter_code>d1 = paidProduct['invoice_created_at'].astype('datetime64[ns]') 
d2 = paidProduct['invoice_paid_at'].astype('datetime64[ns]') 
timeDuration = d2-d1
timeDuration.mean()
timeDuration.mean()<jupyter_output><empty_output><jupyter_text>Task 4.e Customer Segmentation Customer segmentation using RFMTo make the RFM table, we can create these columns, such as Recency, Frequency, and MonetaryValue column.- To get the number of days for recency column, we can subtract the snapshot date with the date where the transaction occurred.- To create the frequency column, we can count how much transactions by each customer.- Lastly, to create the monetary value (revenue) column, we can sum all transactions for each customer.<jupyter_code>newCleanData = cleanData.drop(['invoice_id','invoice_item_id','duplicated','invoice_created_at','service_cancelled_at','monthly_price_usd','discount','service_period'], axis=1)
from datetime import datetime
newCleanData['invoice_paid_at'] = pd.to_datetime(newCleanData['invoice_paid_at'])
newCleanData
newCleanData.groupby('invoice_status').sum()
newCleanData = newCleanData[newCleanData['invoice_status'] == 'Paid']
tx_user = pd.DataFrame(newCleanData['client_id'].unique())
tx_user.columns = ['client_id']
tx_user
tx_max_purchase = newCleanData.groupby('client_id').invoice_paid_at.max().reset_index()
tx_max_purchase.columns = ['client_id','MaxPurchaseDate']
tx_max_purchase['Recency'] = (tx_max_purchase['MaxPurchaseDate'].max() - tx_max_purchase['MaxPurchaseDate']).dt.days
tx_max_purchase
tx_user = pd.merge(tx_user, tx_max_purchase[['client_id','Recency']], on='client_id')
tx_user.head()
tx_user['Recency'] = tx_user['Recency'].fillna(0)
plt.hist(tx_user.Recency)
plt.show()
tx_user.Recency.describe()
tx_frequency = newCleanData.groupby('client_id').invoice_paid_at.count().reset_index()
tx_frequency.columns = ['client_id','Frequency']
tx_user = pd.merge(tx_user, tx_frequency, on='client_id')
tx_user.head()
tx_user.sort_values(by='Frequency', ascending=False)
plt.hist(tx_user.Frequency,range =(tx_user.Frequency.min(), tx_user.Frequency.max()))
plt.show()
tx_user.Frequency.describe()
revenue = newCleanData.groupby('client_id').discounted_price.sum().reset_index()
revenue.columns = ['client_id','Revenue']
tx_user = pd.merge(tx_user, revenue, on='client_id')
tx_user
plt.hist(tx_user.Revenue,range =(tx_user.Revenue.min(), tx_user.Revenue.max()))
plt.show()
tx_user['minRevenue'] = tx_user['Revenue'].apply(lambda x: 'True' if x >= 0 else 'False')
minRevenueUser = tx_user[tx_user['minRevenue'] == 'False']
minRevenueUser
userData = tx_user.drop(['client_id','minRevenue'], axis=1)
userData<jupyter_output><empty_output><jupyter_text>Data Normalization<jupyter_code>from sklearn.preprocessing import StandardScaler
scaler = StandardScaler()
new_df_data = scaler.fit_transform(userData)
new_df_data<jupyter_output><empty_output><jupyter_text>Find the k parameter of K-Means using Elbow Method<jupyter_code>distortions = []
sse = {}
K = range(1,10)
for k in K:
    kmeanModel = KMeans(n_clusters=k,random_state=42)
    kmeanModel.fit(new_df_data)
    sse[k] = kmeanModel.inertia_ 
import seaborn as sns
plt.title('The Elbow Method')
plt.xlabel('k')
plt.ylabel('SSE')
sns.pointplot(x=list(sse.keys()), y=list(sse.values()))
plt.show()<jupyter_output><empty_output><jupyter_text>The x-axis is the value of the k, and the y-axis is the SSE value of the data. We will take the best parameter by looking at where the k-value will have a linear trend on the next consecutive k.Based on our observation, the k-value of 4 is the best hyperparameter for our model because the next k-value tend to have a linear trend. Therefore, our best model for the data is K-Means with the number of clusters is 4.<jupyter_code>model = KMeans(n_clusters=4, random_state=42)
model.fit(new_df_data)
model.labels_.shape
tx_user["Cluster"] = model.labels_
tx_user.groupby('Cluster').agg({
    'Recency':'mean',
    'Frequency':'mean',
    'Revenue':['mean', 'count']}).round(2)<jupyter_output><empty_output><jupyter_text>Cluster 3 has one user only and have huge minus revenue, so we can ignore cluster 3 on this segmentation<jupyter_code>tx_user[tx_user['Cluster'] == 3]
df_normalized = pd.DataFrame(new_df_data, columns=['Recency', 'Frequency', 'Revenue'])
df_normalized['client_id'] = tx_user.client_id
df_normalized['Cluster'] = model.labels_
df_normalized = df_normalized[df_normalized['Cluster'] != 3]
df_normalized
# Melt The Data
df_nor_melt = pd.melt(df_normalized.reset_index(),
                      id_vars=['client_id', 'Cluster'],
                      value_vars=['Recency','Frequency','Revenue'],
                      var_name='Attribute',
                      value_name='Value')
df_nor_melt.head()<jupyter_output><empty_output><jupyter_text>Plot the RFM attribute to see the correlation in each cluster<jupyter_code>sns.lineplot('Attribute', 'Value', hue='Cluster', data=df_nor_melt)<jupyter_output><empty_output><jupyter_text>USER SEGMENTATION 1. Cluster 0 is less frequent, less to spend, but they buy the product recently. Therefore, it could be the cluster of new client.2. Cluster 1 is less frequent, less to spend, and they buy the product at the old time. Therefore, it could be the cluster of churned client.3. Cluster 2 is frequent, spend more, and they buy the product recently. Therefore, it could be the cluster of a loyal client.So, based on this finding we can also answer the question on session 4.c.<jupyter_code>loyalClient = tx_user[tx_user['Cluster'] == 2]
loyalClient = loyalClient.sort_values(by='Revenue', ascending=False)
freeVoucherClient = loyalClient.drop(['Recency','minRevenue','Cluster'],axis=1)
freeVoucherClient.iloc[0:100]<jupyter_output><empty_output><jupyter_text>Task 4.f Further Exploration<jupyter_code>pd.Series(cleanData.invoice_status).value_counts().plot(kind = 'bar')
unpaidTx = cleanData[cleanData['invoice_status']=='Unpaid']
unpaidTx
pd.Series(unpaidTx.service_status).value_counts().plot(kind = 'bar')<jupyter_output><empty_output><jupyter_text>From unpaid transaction, we can see that the biggest cause of unpaid transaction is cancellation and the second is pending setup.For pending setup, I can not clearly understand the meaning behind it, whether the user do not finish their registration or the service provider do not finist to set up the environment. Let say the meaning is user do not finish the registration.- If the case is user do not finish the registration, we send the reminder to the user like push notification via user's email to complete the registration<jupyter_code>unpaidTx.groupby(['product_id']).agg('sum')
pd.Series(unpaidTx.product_id).value_counts().plot(kind = 'bar')<jupyter_output><empty_output><jupyter_text>The most cancellation product is Product 3. We can do more analysis on Product 3 to see the cause why user tend to cancel the payment on Product 3<jupyter_code>product_3 = cleanData[cleanData['product_id']==3]
pd.Series(product_3.invoice_status).value_counts().plot(kind = 'bar')<jupyter_output><empty_output><jupyter_text>The cause why product 3 has the biggest unpaid transaction is because product 3 has the biggest demand, so the corelation is linear. We can conclude that the cause of user do not pay the service is nothing to do with the product/services.<jupyter_code>pd.Series(cleanData.product_id).value_counts().plot(kind = 'bar')<jupyter_output><empty_output><jupyter_text>From overall transaction, we can see Product 11, 10, 12, 5, 14, 7 , 6 , 13, and 8 has the least transaction. Maybe<jupyter_code>product_8 = paidProduct[paidProduct['product_id']==8]
pd.Series(product_8.service_status).value_counts().plot(kind = 'bar')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11617929321440669, 'fraction_numerical': 0.009258213842158745, 'mean_word_length': 7.1340679522497705, 'pattern_counts': {'":': 0, '<': 61, '<?xml version=': 0, '>': 62, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '15', 'dir': 'jupyter-structured-clean-dedup', 'id': '637283', 'license': 'MIT', 'n_tokens_mistral': 2929, 'n_tokens_neox': 2798, 'n_words': 734, 'path': 'Customer_Segmentation.ipynb', 'repo_name': 'amaliaka/Customer-Segmentation-using-K-Means', 'size': '8857'}