<jupyter_start><jupyter_text>Select Customers to Participate the Loyalty Program of a Retailer Using Clustering Analysis Author: <NAME> Part II - Data PreparationNow that the data is clean and have only the information under interest, I will perform a series of manipulations to extract features that might help to solve this business problem. The data was provided in a format not appropriate for this problem. Each row of the data set represents a product that was purchased in a given transaction by a specific customer. The goal of the project is to find the high-value customers to participate in the loyalty program the company wants to create. Thus, it is more important to know how many purchases the customer made, how much was spent in a certain period of time, what time of the year the purchases were made and in what frequency, rather than discriminate the items purchased in each transaction. This part of the project is composed by the following steps: - Feature engineering. What features are important to separate customers into distinct groups? - Outliers treatment. Outliers are extreme values that differentiate from the main patterns of the data. - Feature analysis and correlation - Standardizing Features. Clustering algorithms use distance as a mean to measure similarity. Different range of values in each feature will act as a weight determining how to cluster data, introducing bias into the model.<jupyter_code>from platform import python_version
print('Python Version:', python_version())<jupyter_output>Python Version: 3.8.8<jupyter_text>Libraries<jupyter_code># Data manipulation
import pandas as pd
import numpy as np
import re

# Visualizations
import matplotlib.pyplot as plt
import seaborn as sns
import plotly
import plotly.express as px
import plotly.graph_objects as go

import warnings
warnings.filterwarnings("ignore")

# import modules
import sys
sys.path.append("lib")
from customize_figure import figure_layout
print('Package version')
print('Re: ', re.__version__)
print('Pandas: ', pd.__version__)
print('Numpy: ', np.__version__)
print('Seaborn: ', sns.__version__)
print('Plotly: ', plotly.__version__)<jupyter_output>Package version
Re:  2.2.1
Pandas:  1.2.4
Numpy:  1.20.1
Seaborn:  0.11.1
Plotly:  5.1.0<jupyter_text>Import data<jupyter_code>df = pd.read_csv('data/ecommerce_clean.csv')
df.head()
df.shape
df.describe()<jupyter_output><empty_output><jupyter_text>Extracting year, month and day<jupyter_code>df['date'] = pd.to_datetime(df['InvoiceDate'])
df['year'] = df['date'].dt.year
df['month'] = df['date'].dt.month
df['day'] = df['date'].dt.day
df.head()
df['date'].max() - df['date'].min()<jupyter_output><empty_output><jupyter_text>I will use a time interval of 365 days (1 year) in this project.<jupyter_code>df.drop(index = df[df['date']>='2017-11-30 00:00:00'].index, inplace=True)

df['date'].max() - df['date'].min()<jupyter_output><empty_output><jupyter_text>total_purchase = Quantity*UnitPriceTotal amount of money spent by customers within 1 year period.<jupyter_code>df['total_purchase']=df['Quantity'] * df['UnitPrice']
df.head()<jupyter_output><empty_output><jupyter_text>Feature Engineeringdf_clusters dataframe will organize the data in the proper format for clustering analysis. New features will be created using the data available and domain knowledge.  df_clusters dataframe<jupyter_code># Total purchase per customer
df_clusters = df.groupby('CustomerID')['total_purchase'].sum().round(2).reset_index()
df_clusters.describe()<jupyter_output><empty_output><jupyter_text>As expected, total_purchase have negative values because some customers had just C_transaction in the time period the data was collected, as seen in the data cleaning step. However, negative values for total_purchase also indicate customers that made more returns than valid purchases. This is the case of CustomerID 17603 that placed the first valid order on 20-Sep-17, and returns were registered before this date clearly related to purchases placed before 29-Nov-2016.<jupyter_code>df[df['CustomerID']==17603.0].groupby('InvoiceNo').agg({'Quantity':sum,
                                                        'purchase':sum,
                                                        'InvoiceDate':lambda x:x.unique()})
df_clusters.shape<jupyter_output><empty_output><jupyter_text>Number of valid ordersI will remove all the C_transactions (Quantity<0) and the examples where UnitPrice = 0 to estimate the number of valid transactions.<jupyter_code>df_valid_orders = df[(df['UnitPrice']>0)&(df['Quantity']>0)]

number_orders = df_valid_orders.groupby('CustomerID')['InvoiceNo'].nunique()\
                               .reset_index()\
                               .rename(columns={'InvoiceNo':'number_orders'})


df_clusters = df_clusters.merge(right=number_orders, how='left', on='CustomerID')
df_clusters.fillna(0, inplace = True)
df_clusters.shape<jupyter_output><empty_output><jupyter_text>Number of C_transactions<jupyter_code>df[df['Quantity']<0]['InvoiceNo'].unique().shape
n_C_transactions = df[df['Quantity']<0].groupby('CustomerID')['InvoiceNo'].nunique()\
                                       .reset_index()\
                                       .rename(columns={'InvoiceNo':'number_returns'})

df_clusters = df_clusters.merge(right=n_C_transactions, 
                                on='CustomerID', 
                                how='left')

df_clusters.fillna(0, inplace=True)

df_clusters.head()
df_clusters.shape<jupyter_output><empty_output><jupyter_text>Number of orders with products of UnitPrice = 0<jupyter_code>orders_with_gifts = df[df['UnitPrice']==0].groupby('CustomerID')['InvoiceNo'].nunique()\
                                          .reset_index()\
                                          .rename(columns={'InvoiceNo':'orders_with_gifts'})
df_clusters = df_clusters.merge(right=orders_with_gifts, on='CustomerID', how='left')
df_clusters.fillna(0, inplace=True)
df_clusters.head()<jupyter_output><empty_output><jupyter_text>Number of orders per monthThe average number of valid orders a customer has placed per month.<jupyter_code>order_per_month = df_valid_orders.groupby(['CustomerID','month'])['InvoiceNo'].nunique()\
                                 .reset_index()\
                                 .groupby('CustomerID')['InvoiceNo'].mean()\
                                 .reset_index()\
                                 .rename(columns={'InvoiceNo':'order_per_month'})

df_clusters = df_clusters.merge(right=order_per_month, how='left', on='CustomerID')
df_clusters.head()<jupyter_output><empty_output><jupyter_text>Number of months with purchasesThis feature counts the number of months a customer placed new orders.<jupyter_code>months_with_order = df_valid_orders.groupby(['CustomerID'])['month'].nunique()\
                                   .reset_index()\
                                   .rename(columns={'month':'months_with_order'})

df_clusters = df_clusters.merge(right=months_with_order, how='left', on='CustomerID')
df_clusters.head()<jupyter_output><empty_output><jupyter_text>What time of the year customers have purchased?Months are divided into four groups representing quarters of the year. These features will inform in which time of the year customers have purchased.<jupyter_code># Quarter of the year
df['year_quarter'] = np.select(
    [
        df['month'].between(1, 3, inclusive=True), 
        df['month'].between(4, 6, inclusive=True),
        df['month'].between(7, 9, inclusive=True),
        df['month'].between(10, 12, inclusive=True)
    ], 
    [
        '1_3Month', 
        '4_6Month',
        '7_9Month',
        '10_12Month'
    ], 
    default='Unknown'
)
quarter_of_purchase = df.groupby(['CustomerID','InvoiceNo'])\
                                     .agg({'year_quarter': lambda x: x.unique()})\
                                     .reset_index()

quarter_number_puchases = pd.get_dummies(quarter_of_purchase, columns=['year_quarter'], prefix="", prefix_sep="")\
                            .drop(columns='InvoiceNo')\
                            .groupby('CustomerID')\
                            .sum()\
                            .reset_index()
quarter_number_puchases.head()
df_clusters = df_clusters.merge(right=quarter_number_puchases, how='left', on='CustomerID')
df_clusters.head()
df_clusters.shape
df_clusters.isnull().sum()
df_clusters.fillna(0, inplace=True)<jupyter_output><empty_output><jupyter_text>Customer average order valueThe average order value within 1 year period.<jupyter_code>df_clusters['average_order_value'] = df_clusters['total_purchase']/df_clusters['number_orders']
df_clusters.head()<jupyter_output><empty_output><jupyter_text>RecencyDetermines the difference between the current date in the database and the date of the customer's last purchase.<jupyter_code>current_date = df['date'].max() + pd.DateOffset(1)

recency = df_valid_orders.groupby('CustomerID')\
                         .agg({'date': lambda x: (current_date - x.max()).days})\
                         .reset_index()\
                         .rename(columns={'date':'recency'})

df_clusters = df_clusters.merge(right=recency, on='CustomerID', how='left')
df_clusters.isnull().sum()
df_clusters.fillna(365,inplace=True)<jupyter_output><empty_output><jupyter_text>Data dictionary:- total_purchase: total amount spent within 1 year period.- number_orders: number of orders within 1 year period.- number_returns: number of returns registered.- orders_with_gifts: number of orders with products of unit price equals 0.0.- order_per_month: average number of orders per month.- months_with_order: number of months with orders.- 1_3Months, 4_6Months, 7_9Months, 10_12Months: number of orders per quarter of the year.- average_order_value: average order value within 1 year period.- recency: difference between the current date in the database and the date of the customer's last purchase.  Exploratory Data Analysis<jupyter_code>data = df_clusters[df_clusters['number_orders']>0]
fig = px.scatter(x=np.log(data['number_orders']), y=data['total_purchase'].apply(lambda x: np.log(np.abs(1+x))))

annotations = []
annotations.append(dict(xref='paper', yref='paper',
                        x=-0.05, y=1.0,
                                  xanchor='left', yanchor='bottom',
                                  text='Total purchase value increases as the number of orders increase.',
                                  font=dict(family='Arial',
                                            size=16, 
                                            color = 'black'),
                                  showarrow=False))


fig.update_layout(figure_layout(title_text='Relation between total purchase value and number of orders.',
                                x_label='log(number of orders)',
                                y_label='log(Total purchase)',
                               annotations=annotations))

fig.show()
data = df_clusters[['10_12Month', '1_3Month', '4_6Month', '7_9Month']].sum()\
                                                                      .to_frame(name='count')\
                                                                      .reset_index()\
                                                                      .sort_values(by='count')

fig = px.bar(data, x='index', y='count')

annotations = []
annotations.append(dict(xref='paper', yref='paper',
                        x=-0.05, y=1.0,
                        xanchor='left', yanchor='bottom',
                        text='Last quarter of the year have the higher amount of sales.',
                        font=dict(family='Arial',
                                  size=16, 
                                  color = 'black'),
                        showarrow=False))

fig.update_layout(figure_layout(annotations=annotations,
                                title_text='Purchase distribution over 1 year.',
                                x_label='',
                                y_label='Frequency'))

fig.show()
data = df_clusters[df_clusters['number_returns']>0]
fig = px.scatter(x=data['number_returns'], y=data['total_purchase'])

annotations = []
annotations.append(dict(xref='paper', yref='paper',
                        x=-0.05, y=1.0,
                                  xanchor='left', yanchor='bottom',
                                  text='There is not an explicit relation between returns and purchase value.',
                                  font=dict(family='Arial',
                                            size=16, 
                                            color = 'black'),
                                  showarrow=False))


fig.update_layout(figure_layout(title_text='Relation between total purchase value and number of returns.',
                                x_label='Returns',
                                y_label='Purchase Value',
                               annotations=annotations))

fig.show()
fig = px.histogram(x=df_clusters['total_purchase'].apply(lambda x: np.sign(x)*np.log(np.abs(1+x))))

fig.update_layout(figure_layout(title_text='Distribution of Purchase Value.',
                                x_label='log(Total purchase) (£)',
                                y_label='Frequency'))
fig.show()<jupyter_output><empty_output><jupyter_text>Business Metrics Reapeat Purchase Rate, Purchase Frequency, Average Order Value metricsRepeat Purchase Rate (RPR), Purchase Frequency (PF), Time between purchases (TBP), Average Order Value (AOV), and Customer Lifetime Value (CLV) are relevant metrics used to evaluate the business as a whole. Track such metrics helps to find new ways to grow the profitability of the business. To achieve that, businesses can focus on getting people to buy more during each visit (average order value) or to shop more often (purchase frequency). A loyalty program can help with both average order value and purchase frequency. The enrollment in a loyalty program encourages a customer to shop again instead of choosing a competitor. Once a shopper is enrolled strategies can be used to motivate them to shop more frequently (increasing purchase frequency).https://blog.smile.io/how-to-calculate-purchase-frequency/  Reapeat Purchase Rate (RPR)Repeat purchase rate is the percentage of customers who purchase again after their first purchase. This basically measures the customer retention and is an indicator of the value provided for the customers.<jupyter_code>return_customers = df_clusters[df_clusters['number_orders']>1]['CustomerID'].shape[0]
total_customers = df_clusters['CustomerID'].nunique()

RPR = return_customers*100 / total_customers
print('RPR = {:.2f}%.'.format(RPR))<jupyter_output>RPR = 63.73%.<jupyter_text>Purchase Frequency (PF)The average number of times a customer makes a purchase within a year.<jupyter_code>number_of_orders = df_clusters['number_orders'].sum()
number_of_unique_customers = df_clusters.shape[0]

PF = number_of_orders / number_of_unique_customers
print('PF = {:.2f}.'.format(PF, 0))<jupyter_output>PF = 4.11.<jupyter_text>Time between purchases (TBP)Shows how often a typical customer goes before making a repeat purchase. This is a good stat to know because it allows to tailor email marketing campaigns.<jupyter_code>time_period = (df_valid_orders['date'].max() - df_valid_orders['date'].min()).days

TBP = time_period / PF
print('TBP = {:.2f} days.'.format(TBP))<jupyter_output>TBP = 88.82 days.<jupyter_text>64% of customers purchase at least a second time and, on average, they purchase 4 times within a year and take, on average, 88 days to make a purchase. These are some of the metrics the business can track to evaluate the performance of the loyalty program. It is expected the purchase frequency to increase and the time between purchases to decrease as the program evolves. Furthermore, it might be also expected that other customers engage with the program over time increasing the repeat customer rate.   Average order value (AOV)The average amount customers spend each time they make a purchase. This is a very important metric beacuse it has a strong correlation to an increase in profit. Based on this metric, businesses can determine their retention plan or marketing strategy. It is calculated dividing the yearly/monthly revenue by the number of orders your store has processed.<jupyter_code>total_revenue = df['total_purchase'].sum()

yearly_AOV = total_revenue / number_of_orders
print('Yearly AOV = £{:.2f}'.format(yearly_AOV))<jupyter_output>Yearly AOV = £445.38<jupyter_text>This metric is usually calculated monthly. So lets calculate AOV for each month in the data set:<jupyter_code>purchase_month = df.groupby('month')['total_purchase'].sum()
order_month = df_valid_orders.groupby('month')['InvoiceNo'].nunique()

monthly_AOV = purchase_month / order_month
fig = go.Figure()

labels = ['Monthly AOV', 'Yearly AOV']
colors = ['blue', 'gray']
line_size = [2,3]
dash = [None, 'dash']

x_data = monthly_AOV.index.to_list()
y_data = np.array([monthly_AOV, [yearly_AOV]*12])

for i in range(0, 2):
    fig.add_trace(go.Scatter(x=x_data, y=y_data[i], mode='lines',
        name=labels[i],
        line=dict(color=colors[i], width=line_size[i], dash=dash[i]),
        connectgaps=True,
    ))

    # endpoints
    fig.add_trace(go.Scatter(
        x=[x_data[-1], x_data[-1]],
        y=[y_data[i][-1], y_data[i][-1]],
        mode='markers',
        marker=dict(color=colors[i])
    ))


annotations = []

# Adding labels
for y_trace, label, color in zip(y_data, labels, colors):
    # labeling the right_side of the plot
    annotations.append(dict(xref='paper', x=1.15, y=y_trace[-1],
                                  xanchor='right', yanchor='middle',
                                  text=label,
                                  font=dict(family='Arial',
                                            size=16, 
                                            color = color),
                                  showarrow=False))


fig.update_layout(figure_layout(annotations=annotations,
                                title_text='Average Order Value (AOV) during one year period.',
                                x_label='Month',
                                y_label='AOV (£)'))
    
fig.show()<jupyter_output><empty_output><jupyter_text>Customer Lifetime ValueCustomer lifetime value (CLV) is a measure of the average customer’s revenue generated over their entire relationship with a company.CLV = Average Order Value x Purchase Frequency (yearly purchases) x Retention Period Reference: https://www.netsuite.com/portal/resource/articles/ecommerce/customer-lifetime-value-clv.shtmlhttps://clevertap.com/blog/customer-lifetime-value/https://clevertap.com/blog/customer-lifetime-value/<jupyter_code># Average spends per visit (yearly_AOV) * purchase frequency (4 times per year)
CLV = yearly_AOV * PF * 1
print('Customer Lifetime Value, CLV, = £{:.2f} in one year'.format(CLV))<jupyter_output>Customer Lifetime Value, CLV, = £1830.32 in one year<jupyter_text>Considering the metrics calculated, it is fair to consider that customers who spent more than £1929.47 and bought at least 4 times a year are eligible for the loyalty program.  Who are the customers that contribute more with the revenue<jupyter_code># Preparing data
plot_data = df_clusters.sort_values(by='total_purchase',ascending=False)['total_purchase'].to_frame().copy()
plot_data['contrib_revenue'] = plot_data['total_purchase'] / plot_data['total_purchase'].sum()
plot_data['cumsum'] = plot_data['contrib_revenue'].cumsum()
x_data = np.arange(0,1.1,0.1)
y_data = plot_data.quantile(q=x_data)['cumsum']
y_data.loc[0] = 0

# marker
x_marker = plot_data[plot_data['cumsum'] <= 0.80].shape[0] / df_clusters.shape[0]
y_marker = 0.8

fig = px.line(x=x_data,
              y=y_data)

fig.add_trace(go.Scatter(
        x=[x_marker],
        y=[y_marker],
        mode='markers',
        marker=dict(color='blue',size=10)))

annotations = []
annotations.append(dict(xref='paper', x=x_marker+0.01, y=y_marker-0.02,
                                  xanchor='left', yanchor='middle',
                                  text='{:.0f}% of the revenue comes from {:.0f}% of the customers.'\
                                             .format(y_marker*100,x_marker*100),
                                  font=dict(family='Arial',
                                            size=16, 
                                            color = 'blue'),
                                  showarrow=False))

fig.update_layout(figure_layout(annotations=annotations,
                                title_text='Customer Contribution to the UK Retailer Revenue',
                                x_label='Proportion of customers',
                                y_label='Proportion of Revenue'))

fig.show()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10658096699923254, 'fraction_numerical': 0.011751726784343821, 'mean_word_length': 3.004802151363811, 'pattern_counts': {'":': 0, '<': 95, '<?xml version=': 0, '>': 97, 'https://': 4, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 63, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '24', 'dir': 'jupyter-structured-clean-dedup', 'id': '387595', 'license': 'MIT', 'n_tokens_mistral': 6043, 'n_tokens_neox': 5744, 'n_words': 1588, 'path': 'Part-II-Data-Preparation.ipynb', 'repo_name': 'adrielidaltoe/Clustering-Analysis-Customer-Loyalty-Program', 'size': '20857'}