1
 2# This Python 3 environment comes with many helpful analytics libraries installed
 3# It is defined by the kaggle/python docker image: https://github.com/kaggle/docker-python
 4# For example, here's several helpful packages to load in
 5
 6import numpy as np # linear algebra
 7import pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)
 8
 9# Input data files are available in the "../input/" directory.
10
11import time, warnings
12import datetime as dt
13
14#visualizations
15import matplotlib.pyplot as plt
16from pandas.plotting import scatter_matrix
17%matplotlib inline
18import seaborn as sns
19
20warnings.filterwarnings("ignore")

1
2#load the dataset
3retail_df = pd.read_csv('../input/data.csv',encoding="ISO-8859-1",dtype={'CustomerID': str,'InvoiceID': str})
4retail_df.head(20)

1
2print(retail_df.describe())
3print(retail_df.info())

 1
 2#converting the type of Invoice Date Field from string to datetime.
 3retail_df['InvoiceDate'] = pd.to_datetime(retail_df['InvoiceDate'])
 4
 5#creating MonthKey field for reporting and visualization
 6retail_df['MonthKey'] = retail_df['InvoiceDate'].map(lambda date: 100*date.year + date.month)
 7
 8#calculate Revenue for each row and create a new dataframe with MonthKey - Revenue columns
 9retail_df['Revenue'] = retail_df['UnitPrice'] * retail_df['Quantity']
10revenue_by_month = retail_df.groupby(['MonthKey'])['Revenue'].sum().reset_index()
11print(revenue_by_month)
12
13# plot data
14revenue_by_month['MonthKey'] = revenue_by_month['MonthKey'].apply(str)
15%matplotlib inline
16plt.rcParams["figure.figsize"] = [20, 10]
17plt.plot('MonthKey', 'Revenue', data=revenue_by_month, linestyle='-', marker='o')
18plt.title("Revenue by Month")
19plt.show()

 1
 2
 3	MonthKey	Revenue
 40	201012	748957.020
 51	201101	560000.260
 62	201102	498062.650
 73	201103	683267.080
 84	201104	493207.121
 95	201105	723333.510
106	201106	691123.120
117	201107	681300.111
128	201108	682680.510
139	201109	1019687.622
1410	201110	1070704.670
1511	201111	1461756.250
1612	201112	433668.010

 1
 2revenue_by_month['MonthlyGrowth'] = revenue_by_month['Revenue'].pct_change()
 3
 4
 5# Plot data
 6
 7plt.rcParams["figure.figsize"] = [20, 10]
 8plt.plot('MonthKey', 'MonthlyGrowth', data=revenue_by_month, linestyle='-', marker='o')
 9plt.plot(range(1,len(revenue_by_month.index)+1),[0 for i in range(len(revenue_by_month.index))], color="k", lw=2.5)
10plt.title("Monthly Revenue Growth Rate")
11plt.show()

 1
 2customer_by_month = retail_df.groupby(['MonthKey'])['CustomerID'].nunique().reset_index()
 3customer_by_month
 4
 5
 6customer_by_month['MonthKey'] = customer_by_month['MonthKey'].apply(str)
 7%matplotlib inline
 8plt.rcParams["figure.figsize"] = [20, 10]
 9plt.plot('MonthKey', 'CustomerID', data=customer_by_month, linestyle='-', marker='o')
10plt.title("Customer by Month")
11plt.show()

 1
 2
 3	MonthKey	CustomerID
 40	201012	948
 51	201101	783
 62	201102	798
 73	201103	1020
 84	201104	899
 95	201105	1079
106	201106	1051
117	201107	993
128	201108	980
139	201109	1302
1410	201110	1425
1511	201111	1711
1612	201112	686

1
2order_by_month = retail_df.groupby(['MonthKey'])['InvoiceNo'].count().reset_index()
3order_by_month
4order_by_month['MonthKey'] = order_by_month['MonthKey'].apply(str)
5%matplotlib inline
6plt.rcParams["figure.figsize"] = [20, 10]
7plt.plot('MonthKey', 'InvoiceNo', data=order_by_month, linestyle='-', marker='o')
8plt.title("Count total order in Month")
9plt.show()

 1
 2avg_order_revenue = retail_df.groupby(['MonthKey'])['Revenue'].mean().reset_index()
 3
 4avg_order_revenue['MonthKey'] = avg_order_revenue['MonthKey'].apply(str)
 5
 6# Plot regression line
 7
 8plt.rcParams["figure.figsize"] = [20, 10]
 9plt.plot('MonthKey', 'Revenue', data=avg_order_revenue, linestyle='-', marker='o')
10plt.title("Average Revenue per Order")
11plt.show()

 1
 2create a dataframe contaning CustomerID and first purchase date
 3df_min_date_purchase =retail_df.groupby('CustomerID').InvoiceDate.min().reset_index()
 4df_min_date_purchase.columns = ['CustomerID','MinPurchaseDate']
 5df_min_date_purchase['MinMonthKey'] = df_min_date_purchase['MinPurchaseDate'].map(lambda date: 100*date.year + date.month)
 6
 7#merge first purchase date column to our main dataframe (tx_uk)
 8retail_new_df = pd.merge(retail_df, df_min_date_purchase, on='CustomerID')
 9
10retail_new_df.head()
11
12#create a column called User Type and assign Existing
13#if User's First Purchase Year Month before the selected Invoice Year Month
14retail_new_df['UserType'] = 'New'
15retail_new_df.loc[retail_new_df['MonthKey']>retail_new_df['MinMonthKey'],'UserType'] = 'Existing'
16
17#calculate the Revenue per month for each user type
18revenue_per_month = retail_new_df.groupby(['MonthKey','UserType'])['Revenue'].sum().reset_index()
19
20#filtering the dates and plot the result
21revenue_per_month = revenue_per_month.query("MonthKey != 201012 and MonthKey != 201112")
22
23revenue_per_month['MonthKey'] = revenue_per_month['MonthKey'].apply(str)
24revenue_per_month.set_index('MonthKey',inplace=True)
25# Plot regression line
26
27plt.rcParams["figure.figsize"] = [20, 10]
28fig, ax = plt.subplots()
29for label, grp in revenue_per_month.groupby('UserType'):
30    grp.plot(x = grp.index, y = 'Revenue',ax = ax, label = label,style='.-')
31plt.title("Old and New user")
32plt.show()

 1
 2new_user_ratio = retail_new_df.query("UserType == 'New'").groupby(['MonthKey'])['CustomerID'].nunique()/retail_new_df.query("UserType == 'Existing'").groupby(['MonthKey'])['CustomerID'].nunique()
 3new_user_ratio = new_user_ratio.reset_index()
 4new_user_ratio = new_user_ratio.dropna()
 5new_user_ratio.columns = ["MonthKey","NewCustomerRatio"]
 6
 7new_user_ratio = new_user_ratio.query("MonthKey != 201012 and MonthKey != 201112")
 8new_user_ratio['MonthKey'] = new_user_ratio['MonthKey'].apply(str)
 9
10# Plot regression line
11
12plt.rcParams["figure.figsize"] = [20, 10]
13plt.plot('MonthKey', 'NewCustomerRatio', data=new_user_ratio, linestyle='-', marker='o')
14plt.title("New Customer Ratio")
15plt.show()

 1#identify which users are active by looking at their revenue per month
 2df_user_purchase = retail_df.groupby(['CustomerID','MonthKey'])['Revenue'].sum().reset_index()
 3
 4#create retention matrix with crosstab
 5df_retention = pd.crosstab(df_user_purchase['CustomerID'], df_user_purchase['MonthKey']).reset_index()
 6
 7print(df_retention.head())
 8
 9#create an array of dictionary which keeps Retained & Total User count for each month
10months = df_retention.columns[2:]
11retention_array = []
12for i in range(len(months)-1):
13    retention_data = {}
14    selected_month = months[i+1]
15    prev_month = months[i]
16    retention_data['MonthKey'] = int(selected_month)
17    retention_data['TotalUserCount'] = df_retention[selected_month].sum()
18    retention_data['RetainedUserCount'] = df_retention[(df_retention[selected_month]>0) & (df_retention[prev_month]>0)][selected_month].sum()
19    retention_array.append(retention_data)
20
21#convert the array to dataframe and calculate Retention Rate
22df_retention = pd.DataFrame(retention_array)
23df_retention['RetentionRate'] = df_retention['RetainedUserCount']/df_retention['TotalUserCount']
24
25df_retention = df_retention.query("MonthKey != 201012 and MonthKey != 201112")
26df_retention['MonthKey'] = df_retention['MonthKey'].apply(str)
27
28# Plot regression line
29
30plt.rcParams["figure.figsize"] = [20, 10]
31plt.plot('MonthKey', 'RetentionRate', data=df_retention, linestyle='-', marker='o')
32plt.title("Monthly Retention Rate")
33plt.show()

 1# hàm crosstab trả ra ma trận tương quan giữa khách hàng và tháng, ví dụ như khách hàng 12346 tháng 201012 không có mua hàng, nhưng 201101 lại có
 2>> print(df_retention.head())
 3MonthKey CustomerID  201012  201101  201102  201103  201104  201105  201106  \
 40             12346       0       1       0       0       0       0       0
 51             12347       1       1       0       0       1       0       1
 62             12348       1       1       0       0       1       0       0
 73             12349       0       0       0       0       0       0       0
 84             12350       0       0       1       0       0       0       0
 9
10MonthKey  201107  201108  201109  201110  201111  201112
110              0       0       0       0       0       0
121              0       1       0       1       0       1
132              0       0       1       0       0       0
143              0       0       0       0       1       0
154              0       0       0       0       0       0

1retail_rfm_df = retail_df.copy()
2#remove canceled orders
3retail_rfm_df = retail_rfm_df[retail_rfm_df['Quantity']>0]
4#remove rows where customerID are NA
5retail_rfm_df.dropna(subset=['CustomerID'],how='all',inplace=True)
6retail_rfm_df = retail_rfm_df[retail_rfm_df['InvoiceDate']> "2010-12-31"]
7retail_rfm_df = retail_rfm_df[retail_rfm_df['InvoiceDate']< "2011-12-01"]

 1now = dt.date(2011,11,30)
 2#create a new column called date which contains the date of invoice only
 3retail_rfm_df['date'] = pd.DatetimeIndex(retail_rfm_df['InvoiceDate']).date
 4
 5#group by customers and check last date of purshace
 6recency_df = retail_rfm_df.groupby(by='CustomerID', as_index=False)['date'].max()
 7recency_df.columns = ['CustomerID','LastPurshaceDate']
 8
 9#calculate recency
10recency_df['Recency'] = recency_df['LastPurshaceDate'].apply(lambda x: (now - x).days)
11recency_df.drop('LastPurshaceDate',axis=1,inplace=True)
12print(recency_df.head())

 1>> recency_df.head()
 2  CustomerID  Recency
 30      12346      316
 41      12347       30
 52      12348       66
 63      12349        9
 74      12350      301
 8
 9>> recency_df.Recency.describe()
10count    4174.000000
11mean       82.557499
12std        88.535941
13min         0.000000
1425%        15.000000
1550%        45.000000
1675%       128.000000
17max       330.000000

1
2# drop duplicates
3retail_rfm_df_copy = retail_rfm_df
4retail_rfm_df_copy.drop_duplicates(subset=['InvoiceNo', 'CustomerID'], keep="first", inplace=True)
5#calculate frequency of purchases
6frequency_df = retail_rfm_df_copy.groupby(by=['CustomerID'], as_index=False)['InvoiceNo'].count()
7frequency_df.columns = ['CustomerID','Frequency']
8frequency_df.head()

1	CustomerID	Frequency
20	12346	1
31	12347	5
42	12348	3
53	12349	1
64	12350	1

1
2monetary_df = retail_rfm_df.groupby(['CustomerID'])['Revenue'].sum().reset_index()
3monetary_df.columns = ['CustomerID','Monetary']
4monetary_df.head()

1	CustomerID	Monetary
20	12346	77183.60
31	12347	120.56
42	12348	291.76
53	12349	15.00
64	12350	25.20

1
2#merge recency dataframe with frequency dataframe
3temp_df = recency_df.merge(frequency_df,on='CustomerID')
4#merge with monetary dataframe to get a table with the 3 columns
5rfm_df = temp_df.merge(monetary_df,on='CustomerID')
6#use CustomerID as index
7rfm_df.set_index('CustomerID',inplace=True)
8#check the head
9rfm_df.head()

1
2	Recency	Frequency	Monetary
3CustomerID
412346	316	1	77183.60
512347	30	5	120.56
612348	66	3	291.76
712349	9	1	15.00
812350	301	1	25.20

 1
 2#RFM Quartiles
 3rfm_df['Recency'] = -rfm_df['Recency']
 4quantiles = rfm_df.quantile(q=[0.25,0.5,0.75])
 5print(quantiles)
 6quantiles.to_dict()
 7
 8### Creation of RFM Segments
 9
10# Arguments (x = value, p = recency, monetary_value, frequency, k = quartiles dict)
11def FMScore(x,p,d):
12    if x <= d[p][0.25]:
13        return 0
14    elif x <= d[p][0.50]:
15        return 1
16    elif x <= d[p][0.75]:
17        return 2
18    else:
19        return 3
20
21#create rfm segmentation table
22rfm_segmentation = rfm_df
23rfm_segmentation['R_Quartile'] = rfm_segmentation['Recency'].apply(FMScore, args=('Recency',quantiles,))
24rfm_segmentation['F_Quartile'] = rfm_segmentation['Frequency'].apply(FMScore, args=('Frequency',quantiles,))
25rfm_segmentation['M_Quartile'] = rfm_segmentation['Monetary'].apply(FMScore, args=('Monetary',quantiles,))
26
27rfm_segmentation.head()
28
29
30rfm_segmentation['RFMScore'] = rfm_segmentation.R_Quartile.map(str) \
31                            + rfm_segmentation.F_Quartile.map(str) \
32                            + rfm_segmentation.M_Quartile.map(str)
33rfm_segmentation.head()
34
35
36#How many customers do we have in each segment?
37
38print("Best Customers: ",len(rfm_segmentation[rfm_segmentation['RFMScore']=='333']))
39print('Loyal Customers: ',len(rfm_segmentation[rfm_segmentation['F_Quartile']==3]))
40print("Big Spenders: ",len(rfm_segmentation[rfm_segmentation['M_Quartile']==3]))
41print('Almost Lost: ', len(rfm_segmentation[rfm_segmentation['RFMScore']=='133']))
42print('Lost Customers: ',len(rfm_segmentation[rfm_segmentation['RFMScore']=='033']))
43print('Lost Cheap Customers: ',len(rfm_segmentation[rfm_segmentation['RFMScore']=='000']))

 1>> print(quantiles)
 2>> quantiles.to_dict()
 3
 4     Recency  Frequency  Monetary
 50.25     15.0        1.0   17.4000
 60.50     45.0        2.0   43.5000
 70.75    128.0        4.0  119.6625
 8{'Frequency': {0.25: 1.0, 0.5: 2.0, 0.75: 4.0},
 9 'Monetary': {0.25: 17.399999999999999, 0.5: 43.5, 0.75: 119.66250000000001},
10 'Recency': {0.25: 15.0, 0.5: 45.0, 0.75: 128.0}}
11
12>>rfm_segmentation.head()
13
14	Recency	Frequency	Monetary	R_Quartile	F_Quartile	M_Quartile	RFMScore
15CustomerID
1612346	316	1	77183.60	3	0	3	303
1712347	30	5	120.56	1	3	3	133
1812348	66	3	291.76	2	2	3	223
1912349	9	1	15.00	0	0	0	000
2012350	301	1	25.20	3	0	1	301
21
22Best Customers:  10
23Loyal Customers:  980
24Big Spenders:  1044
25Almost Lost:  188
26Lost Customers:  374
27Lost Cheap Customers:  101