<jupyter_start><jupyter_text>CAPSTONE PROJECT:3 - RETAIL    Importing Neccessary Python Packages and Libraries.<jupyter_code>import numpy as np
import pandas as pd
import seaborn as sns
import matplotlib.pyplot as plt
%matplotlib inline
import datetime as dt

# importing required libraries for clustering
import sklearn
from sklearn.preprocessing import StandardScaler
from sklearn.cluster import KMeans
from sklearn.metrics import silhouette_score<jupyter_output><empty_output><jupyter_text>Reading and Understanding Data<jupyter_code>x={1,2,3,4,4}
x.update([4,5,6,7])
print(x)
print(bool('False'))
print(bool)
i=0
while i<3:
    print(i)
    i+=1
else:
    print(0)
# Reading the data from excel to pandas dataframe on which analysis needs to be done.

retail = pd.read_excel('Online_Retail.xlsx')
retail.head()<jupyter_output><empty_output><jupyter_text>Performing a preliminary data inspection and data cleaning.<jupyter_code># shape of df

retail.shape #showing no. of rows and columns in the dataset.
# df info

retail.info() #showing no. of rows with non null entries and datatype of each variable with name.
# df description

retail.describe() #This gives statistical summary of numerical variable by default.<jupyter_output><empty_output><jupyter_text>Describe function has revealed strange insight with negative values in Quantity variable and UnitPrice variable, which needs further investigation.<jupyter_code>retail[retail.Quantity<0],retail[retail.Quantity>0]<jupyter_output><empty_output><jupyter_text>Filtering and comparing data for  both negative and non negative values of Quantity, and correlating with information provided we can infer that negative value  in Quantity is associated with cancelled order.<jupyter_code>retail[retail.UnitPrice<0]<jupyter_output><empty_output><jupyter_text>From above filtering and description it's not clear as to what is 'adjustment of bad debt' and needs consultation from client or domain expertise for it's treatment.    Checking for missing data and formulating an apt strategy to treat them.<jupyter_code># Calculating the Missing Values % contribution in DF

df_null = round(100*(retail.isnull().sum())/len(retail), 5)
df_null
# Droping rows having missing values

retail = retail.dropna()
retail.shape<jupyter_output><empty_output><jupyter_text>Dealing with duplicate records.<jupyter_code>retail[retail.duplicated()].shape
retail.loc[retail.duplicated(keep=False)]
retail[['StockCode','Description']][retail[['StockCode','Description']].duplicated(keep=False)]
# Dropping duplicate records.
retail.drop_duplicates(inplace= True)
retail.shape<jupyter_output><empty_output><jupyter_text>Descriptive analysis on dataset.<jupyter_code>retail.describe()  #This is giving the statistical summary such as count, mean, min, max and quartiles values.
retail[retail.Quantity<0].shape # This shows that 8872 no. of products has been returned or cancelled.
retail.CustomerID.nunique() #This shows the total no. of unique customers who are contributing to overall sales.
retail.Country.unique(),retail.Country.nunique() #This shows that there are 37 countries with their names contributing to sales.
retail.Country.value_counts()  # This shows the total no. of product bought for each country for that period, with UK being the highest contributor in sales.
retail.Country.value_counts().plot(kind= 'bar')
retail.InvoiceNo.nunique() # this shows the total no. of unique transactions for the given period.
len(retail.InvoiceNo.value_counts())# this shows the total no. of unique transactions for the given period.
len(retail.Description.unique()),retail.Description.nunique() # This shows the no. of distinct products bought by the customer during the given period.
sns.boxplot(retail.UnitPrice)
retail.UnitPrice.quantile([0,.25,.50,.75,.8,.9,.99,1])#This shows the relative prices of products where price of maximum products lie below 15 sterling
                                                      #per unit, with max price being 38970.
retail.UnitPrice[retail.UnitPrice<25].plot(kind='hist')
retail.Description.mode() #Highest selling product.
retail.Description.value_counts().head(25) # This is a list of top 25 selling products.<jupyter_output><empty_output><jupyter_text>Cohort analysis :  As per requirement we will build a cohort on the basis of the 1st invoice date of available data in a month and then check for retention metric. Cohort size will be 1 month and we will consider the whole data as range under analysis.<jupyter_code>retail.info()
#Converting Customer ID to object datatype.
retail1=retail.copy()
retail1['CustomerID']=retail1['CustomerID'].astype(str)
retail1.info()
# for this analysis we are going to drop negative Quantity orders if not done so it would be calculated in ordered more than once.
retail1.drop(retail1[retail1.Quantity<1].index, inplace = True)
retail1[retail1.Quantity<1]
n_orders = retail1.groupby(['CustomerID'])['InvoiceNo'].nunique()
mult_orders_perc = np.sum(n_orders > 1) / retail1['CustomerID'].nunique()
print(f'{100 * mult_orders_perc:.2f}% of customers ordered more than once.')
# This show that 65% out of all data provided to us in this given period ordered more than once.
# We can have a look at the distribution of the number of orders per customer.
ax = sns.distplot(n_orders, kde=False, hist=True)
ax.set(title='Distribution of number of orders per customer',
       xlabel='no. of orders', 
       ylabel='no. of customers');<jupyter_output><empty_output><jupyter_text>We keep only the relevant columns and drop duplicated values — one order (indicated by InvoiceNo) can contain multiple items (indicated by StockCode) for analysis<jupyter_code>retail1 = retail1[['CustomerID', 'InvoiceNo', 'InvoiceDate']].drop_duplicates()
retail1<jupyter_output><empty_output><jupyter_text>Creating the cohort and order_month variables. The first one indicates the monthly cohort based on the first purchase date (calculated per customer).The latter one is the truncated month of the purchase date.<jupyter_code>retail1['order_month'] = retail1['InvoiceDate'].dt.to_period('M')
retail1['cohort'] = retail1.groupby('CustomerID')['InvoiceDate'] \
                 .transform('min') \
                 .dt.to_period('M') 
retail1.head(25)<jupyter_output><empty_output><jupyter_text>Aggregating the data per cohort and order_month and count the number of unique customers in each group. Additionally, we add the period_number, which indicates the number of periods between the cohort month and the month of the purchase.<jupyter_code>df_cohort = retail1.groupby(['cohort', 'order_month']) \
              .agg(n_customers=('CustomerID', 'nunique')) \
              .reset_index(drop=False)
from operator import attrgetter
df_cohort['period_number'] = (df_cohort.order_month - df_cohort.cohort).apply(attrgetter('n'))
df_cohort.head(14)<jupyter_output><empty_output><jupyter_text>Now we will pivot the df_cohort table in a way that each row contains information about a given cohort and each column contains values for certain period.<jupyter_code>cohort_pivot = df_cohort.pivot_table(index = 'cohort',
                                     columns = 'period_number',
                                     values = 'n_customers')<jupyter_output><empty_output><jupyter_text>To obtain the retention matrix, we need to divide the values each row by the row's first value, which is actually the cohort size — all customers who made their first purchase in the given month.<jupyter_code>cohort_size = cohort_pivot.iloc[:,0]
retention_matrix = cohort_pivot.divide(cohort_size, axis = 0)<jupyter_output><empty_output><jupyter_text>We plot the retention matrix as a heatmap. Additionally, we wanted to include extra information regarding the cohort size. That is why we in fact created two heatmaps, where the one indicating the cohort size is using a white only colormap — no coloring at all.<jupyter_code>import matplotlib.colors as mcolors
with sns.axes_style("white"):
    fig, ax = plt.subplots(1, 2, figsize=(12, 8), sharey=True, gridspec_kw={'width_ratios': [1, 11]})
    
    # retention matrix
    sns.heatmap(retention_matrix, 
                mask=retention_matrix.isnull(), 
                annot=True, 
                fmt='.0%', 
                cmap='RdYlGn', 
                ax=ax[1])
    ax[1].set_title('Monthly Cohorts: User Retention', fontsize=16)
    ax[1].set(xlabel='no. of periods',
              ylabel='')

    # cohort size
    cohort_size_df = pd.DataFrame(cohort_size).rename(columns={0: 'cohort_size'})
    white_cmap = mcolors.ListedColormap(['white'])
    sns.heatmap(cohort_size_df, 
                annot=True, 
                cbar=False, 
                fmt='g', 
                cmap=white_cmap, 
                ax=ax[0])

    fig.tight_layout()<jupyter_output><empty_output><jupyter_text>After going through the heatmap it can be inferred that the Cohort in 2010-12 had maximum retention with 27% for the 12th period and an average of around 34% throughout the year. Whereas there is sharp decrease in retention rate for the 2nd group at the end period, average retention rate is around 26% throughout the period. Horizontally it shows gradual increase except for final sharp decline. All in all we can say- the cohort in proceeding months are observed to show a gradual decline in retention rate. But the average retention rate for the end period for all cohort is seen to be around 10%, except for the 1st cohort.   Data Preparation for RFM analysis.  We are going to analyse the Customers based on below 3 factors:- R (Recency): Number of days since last purchase- F (Frequency): Number of transactions- M (Monetary): Total amount of transactions (revenue contributed)<jupyter_code># New Attribute : Monetary

retail['Amount'] = retail['Quantity']*retail['UnitPrice']
rfm_m = retail.groupby('CustomerID')['Amount'].sum()
rfm_m = rfm_m.reset_index()
rfm_m.shape
rfm_m.head()
# New Attribute : Frequency

rfm_f = retail.groupby('CustomerID')['InvoiceNo'].count()
rfm_f = rfm_f.reset_index()
rfm_f.columns = ['CustomerID', 'Frequency']
rfm_f.head()
# Merging the two dfs

rfm = pd.merge(rfm_m, rfm_f, on='CustomerID', how='inner')
rfm.head()
# New Attribute : Recency

# Convert 'InvoiceDate' to  datetime  datatype.

retail['InvoiceDate'] = pd.to_datetime(retail['InvoiceDate'],format='%d-%m-%Y %H:%M')
retail.info()
# Compute the maximum date to know the last transaction date

max_date = max(retail['InvoiceDate'])
max_date
# Compute the difference between max date and transaction date

retail['Diff'] = max_date - retail['InvoiceDate']
retail
# Compute last transaction date to get the recency of customers

rfm_p = retail.groupby('CustomerID')['Diff'].min()
rfm_p = rfm_p.reset_index()
rfm_p.head()
# Extract number of days only

rfm_p['Diff'] = rfm_p['Diff'].dt.days
rfm_p.head()
# Merging tha dataframes to get the final RFM dataframe

rfm = pd.merge(rfm, rfm_p, on='CustomerID', how='inner')
rfm.columns = ['CustomerID', 'Amount', 'Frequency', 'Recency']
rfm.head()
x=[]
for i in rfm.Amount:
    if i<=rfm.Amount.quantile(.25):
        x.append(1)
    elif i<=rfm.Amount.quantile(0.50):
        x.append(2)
    elif i<=rfm.Amount.quantile(0.75):
        x.append(3)
    elif i<=rfm.Amount.quantile(1.0):
        x.append(4)
    
rfm["Amount_star"]=x
rfm
y=[]
for i in rfm.Frequency:
    if i<=rfm.Frequency.quantile(.25):
        y.append(1)
    elif i<=rfm.Frequency.quantile(0.50):
        y.append(2)
    elif i<=rfm.Frequency.quantile(0.75):
        y.append(3)
    elif i<=rfm.Frequency.quantile(1.0):
        y.append(4)
rfm["Frequency_star"]=y
rfm
z=[]
for i in rfm.Recency:
    if i<=rfm.Recency.quantile(.25):
        z.append(4)
    elif i<=rfm.Recency.quantile(0.50):
        z.append(3)
    elif i<=rfm.Recency.quantile(0.75):
        z.append(2)
    elif i<=rfm.Recency.quantile(1.0):
        z.append(1)
rfm['Recency_star']=z
rfm
rfm['rfm_score']=rfm.Amount_star+rfm.Frequency_star+rfm.Recency_star
rfm
rfm['rfm_score%']=round(((rfm.rfm_score/12)*100),2) #this I have created to have better clarity while analysing.
rfm
rfm['rfm_segment']=rfm.Amount_star.astype(str) + "-" + rfm.Frequency_star.astype(str) +  "-" + rfm.Recency_star.astype(str)
rfm
rfm['rfm_segment1']=rfm.Amount_star.astype(str) + rfm.Frequency_star.astype(str) + rfm.Recency_star.astype(str)
rfm
rfm.rfm_segment.value_counts().head(60) # this shows all the unique combination of star ratings.
#((rfm.Amount_star== 4).sum()/retail.CustomerID.nunique())*100<jupyter_output><empty_output><jupyter_text>Inferences from RFM segmentation:->From above rfm_segment we can easily filter and make inferences that any value starting with 4 or 3 (or say at hundreds place) will be a high revenue generating customer, thus they should be made to feel valued customer.->Any customer with rfm_score% of 75 or higher can be considered a high value customer even if they have not made more purchases because they can be approached to gain insight as to what offer lures them to buy more, and thus creating proper strategy to target those group to give offers or discounts.-> Best Customers – This group consists of those customers who are found in M-4, F-4, and R-4  meaning that they transacted recently, do so often and spend more than other customers. A shortened notation for this segment is 4-4-4;->High-spending New Customers – This group consists of those customers in 4-1-1 and 4-2-1. These are customers who transacted only once, but very recently and they spent a lot.->Lowest-Spending Active Loyal Customers – This group consists of those customers in segments 1-1-3 and 1-1-4 (they transacted recently and do so often, but spend the least).->Churned Best Customers – This segment consists of those customers in groups 4-1-1, 4-1-2, 4-2-1 and 4-2-2 (they transacted frequently and spent a lot, but it’s been a long time since they’ve transacted)->Best Customers – Communications with this group should make them feel valued and appreciated. These customers likely generate a disproportionately high percentage of overall revenues and thus focusing on keeping them happy should be a top priority. Further analyzing their individual preferences and affinities will provide additional information.->High-spending New Customers – It is always a good idea to carefully incubate all new customers, but because these new customers spent a lot on their first purchase, it’s even more important. Like with the Best Customers group, it’s important to make them feel valued and appreciated – and to give them terrific incentives to continue interacting with the client.->Lowest-Spending Active Loyal Customers – These repeat customers are active and loyal, but they are low spenders. Marketers should create campaigns for this group that make them feel valued, and incentivize them to increase their spend levels. As loyal customers, it often also pays to reward them with special offers if they spread the word about the brand to their friends, e.g., via social networks.->Churned Best Customers – These are valuable customers who stopped transacting a long time ago. While it’s often challenging to re-engage churned customers, the high value of these customers makes it worthwhile trying. Like with the Best Customers group, it’s important to communicate with them on the basis of their specific preferences, as known from earlier transaction data.   Data Preparation for Algorithm.  There are 2 types of outliers and we will treat outliers as it can skew our dataset.- Statistical- Domain specific<jupyter_code># Outlier Analysis of Amount Frequency and Recency

attributes = ['Amount','Frequency','Recency']
plt.rcParams['figure.figsize'] = [10,8]
sns.boxplot(data = rfm[attributes], orient="v", palette="Set2" ,whis=1.5,saturation=1, width=0.7)
plt.title("Outliers Variable Distribution", fontsize = 14, fontweight = 'bold')
plt.ylabel("Range", fontweight = 'bold')
plt.xlabel("Attributes", fontweight = 'bold')
# Removing (statistical) outliers for Amount
Q1 = rfm.Amount.quantile(0.05)
Q3 = rfm.Amount.quantile(0.95)
IQR = Q3 - Q1
rfm = rfm[(rfm.Amount >= Q1 - 1.5*IQR) & (rfm.Amount <= Q3 + 1.5*IQR)]

# Removing (statistical) outliers for Recency
Q1 = rfm.Recency.quantile(0.05)
Q3 = rfm.Recency.quantile(0.95)
IQR = Q3 - Q1
rfm = rfm[(rfm.Recency >= Q1 - 1.5*IQR) & (rfm.Recency <= Q3 + 1.5*IQR)]

# Removing (statistical) outliers for Frequency
Q1 = rfm.Frequency.quantile(0.05)
Q3 = rfm.Frequency.quantile(0.95)
IQR = Q3 - Q1
rfm = rfm[(rfm.Frequency >= Q1 - 1.5*IQR) & (rfm.Frequency <= Q3 + 1.5*IQR)]<jupyter_output><empty_output><jupyter_text>Standardizing the data.It is extremely important to rescale the variables so that they have a comparable scale.|There are two common ways of rescaling:1. Min-Max scaling 2. Standardisation (mean-0, sigma-1) Here, we will use Standardisation Scaling.<jupyter_code># Rescaling the attributes

rfm_df = rfm[['Amount', 'Frequency', 'Recency']]

# Instantiate
scaler = StandardScaler()

# fit_transform
rfm_df_scaled = scaler.fit_transform(rfm_df)
rfm_df_scaled.shape
rfm_df_scaled = pd.DataFrame(rfm_df_scaled)
rfm_df_scaled.columns = ['Amount', 'Frequency', 'Recency']
rfm_df_scaled.head()<jupyter_output><empty_output><jupyter_text>Building the Model  K-Means Clustering K-means clustering is one of the simplest and popular unsupervised machine learning algorithms.The algorithm works as follows:- First we initialize k points, called means, randomly.- We categorize each item to its closest mean and we update the mean’s coordinates, which are the averages of the items categorized in that mean so far.- We repeat the process for a given number of iterations and at the end, we have our clusters.<jupyter_code># k-means with some arbitrary k

kmeans = KMeans(n_clusters=3, max_iter=50)
kmeans.fit(rfm_df_scaled)
rfm_df_scaled
kmeans.labels_<jupyter_output><empty_output><jupyter_text>Deciding the optimum number of clusters to be formed.  Using Elbow Curve to get the right number of Clusters.A fundamental step for any unsupervised algorithm is to determine the optimal number of clusters into which the data may be clustered. The Elbow Method is one of the most popular methods to determine this optimal value of k.<jupyter_code># Elbow-curve/SSD

ssd = []
range_n_clusters = [2, 3, 4, 5, 6, 7, 8]
for num_clusters in range_n_clusters:
    kmeans = KMeans(n_clusters=num_clusters, max_iter=50)
    kmeans.fit(rfm_df_scaled)
    
    ssd.append(kmeans.inertia_)
    
# plot the SSDs for each n_clusters
plt.plot(range_n_clusters,ssd)<jupyter_output><empty_output><jupyter_text>Exporting dataframe to excel for data visualization in Tableau<jupyter_code>import openpyxl
import xlsxwriter
error_cluster= pd.DataFrame(list(zip(range_n_clusters,ssd)), columns=["no.of clusters","Error"])
error_cluster.to_excel("error_cluster.xlsx")<jupyter_output><empty_output><jupyter_text>Silhouette Analysis$$\text{silhouette score}=\frac{p-q}{max(p,q)}$$$p$ is the mean distance to the points in the nearest cluster that the data point is not a part of$q$ is the mean intra-cluster distance to all the points in its own cluster.* The value of the silhouette score range lies between -1 to 1. * A score closer to 1 indicates that the data point is very similar to other data points in the cluster, * A score closer to -1 indicates that the data point is not similar to the data points in its cluster.<jupyter_code># Silhouette analysis
range_n_clusters = [2, 3, 4, 5, 6, 7, 8]

for num_clusters in range_n_clusters:
    
    # intialise kmeans
    kmeans = KMeans(n_clusters=num_clusters, max_iter=50)
    kmeans.fit(rfm_df_scaled)
    
    cluster_labels = kmeans.labels_
    
    # silhouette score
    silhouette_avg = silhouette_score(rfm_df_scaled, cluster_labels)
    print("For n_clusters={0}, the silhouette score is {1}".format(num_clusters, silhouette_avg))
    
    
# Final model with k=3
kmeans = KMeans(n_clusters=3, max_iter=50)
kmeans.fit(rfm_df_scaled)
 kmeans.labels_
# assign the label
rfm['Cluster_Id'] = kmeans.labels_
rfm.head(60)
# Box plot to visualize Cluster Id vs Amount

sns.boxplot(x='Cluster_Id', y='Amount', data=rfm)
# Box plot to visualize Cluster Id vs Frequency

sns.boxplot(x='Cluster_Id', y='Frequency', data=rfm)
# Box plot to visualize Cluster Id vs Recency

sns.boxplot(x='Cluster_Id', y='Recency', data=rfm)<jupyter_output><empty_output><jupyter_text>Final Analysis  Inferences:K-Means Clustering with 3 Cluster Ids- Customers with Cluster Id 1 are frequent buyers, recent buyers and high spender as well, should be considered as the  best customer group.- Customers with Cluster Id 0 are the customers with comparatively good monetary value, frequency and receny for all transactions under observation. Thus these customer group can be a target for conversion into high spending cohort.- Customers with Cluster Id 2 are not recent buyers, frequency is also low and hence least of importance from business point of view.<jupyter_code>rfm<jupyter_output><empty_output><jupyter_text>Exporting files from dataframe to excel for visualization and dashboard building in Tableau.<jupyter_code>import openpyxl
import xlsxwriter
rfm.to_excel("rfm.xlsx")<jupyter_output><empty_output>
StarcoderdataJupyterStructuredCleanDedup