Customer Segmentation

Hello again and welcome to another one of my projects! This time, the boss has given us a dataset with previous transaction data and is asking for a few different things:

• Separate data into 3 different tables (Customers/Orders/Products) and write to a SQL database for storage.
• Create a Recency-Frequency-Monetary table for reporting.
• Segment the customers into groups using a basic KMeans clustering algorithm.

These clusters could represent anything from pushing different advertisements to those customers to inviting them back with a deal if they haven’t bought from our site in a while, or even general demographic information for later use. We will be evaluating our model using silhouette score, Calinski-Harabaz score, and distortion score. Lets get to it!

Step 1: Primary Analysis and Cleaning

After importing our libraries, we’ll read in the data from the single csv file that we’ve been given.

#import data:
df = pd.read_csv("customers.csv")

df.info()

As we can see, there are quite a few different kinds of data in this table. Since our first task is to separate our data into categories and write them to a SQL server, we’ll see if there’s an easy ID number we can use to split everything off.

#Since we're going to separate the tables, check to see if we can do so easily
#by separating on ID, no such luck
df[['Customers.id', 'Orders.id', 'Products.id']].head()

No such luck, let’s keep exploring the data and see what else we can find out about the different tables that we’ll be creating.

pd.DataFrame([{'customers': len(df['Customers.id'].value_counts()),
               'products': len(df['Products.id'].value_counts()),    
               'orders': len(df['Orders.id'].value_counts()),  
              }], columns = ['products', 'orders', 'customers'], index = ['quantity'])

So there are 3054 unique customers, who made 3568 unique orders of 1710 unique products. That will be useful information for later on. While exploring, I found there are a bunch of nonsense rows. Lets take those out.

df['Customers.id'].replace('\t<li>Commode liners can be used with most commode buckets.</li>', np.NaN, inplace = True)
df['Customers.id'].replace('\t<li>Liners include an absorbent pad which solidifies the waste and makes clean up easy and hygienic.</li>',
                                  np.NaN, inplace = True)


df.dropna(thresh= 8, inplace = True)
#we have about 70 columns, so if any observations are missing 10% or more of the data we should drop them

df.reset_index(inplace=True, drop=True)

As a final step in exploration, lets take a quick look at some of the transactions for each customer.

#Lets take a quick look at our customers and the order prices
Customers_groupdf = df.groupby(['Customers.id', 'Customers.fname', 'Customers.lname'])['Order_Items.price',
                                                                                       'Order_Items.cost', 'Orders.total'].sum()

Customers_groupdf.head(20)

Step 2: Separate into 3 DataFrames

In this step we’ll separate each chunk of our original table, clean them up and at the end, write it all into a SQL server.

#Customers DataFrame
customers = df.loc[: , :'Customers.last_modified']
customers.columns = [col.split('.')[1] for col in customers.columns]

#Orders DataFrame, we need to specify columns because they start with both:
#Orders.colname and Order_Items.colname which sometimes overlap
orders = df.loc[: , 'Orders.id':'Order_Items.cost']
orders_cols = ['id', 'fname', 'lname', 'order_number', 'currency', 'subtotal', 'shipping', 'total',  'shipping_carrier',
               'shipping_method', 'tracking', 'payment_status', 'payment_date', 'payment_type', 'payment_amount', 'payment_id',
               'payment_code', 'status', 'placed_date', 'updated_date', 'shipped_date', 'Items.id', 'Items.product_id',
               'Items.product_name', 'Items.qty', 'Items.price', 'Items.cost']
orders.columns = orders_cols

#Products DataFrame
products = df.loc[: , 'Products.id':]
products.columns = [col.split('.')[1] for col in products.columns]

Customers Table

Separating out each factor from the larger table creates a bunch of duplicates, so our primary step for each table will be dropping duplicates with the same ID.

#As you can see from this example, there are many duplicates
customers[customers['id'] == 3371.0]

customers.drop_duplicates(subset='id', inplace = True)

#After doing this, there is one more ID that is null, we will drop this as well
customers.drop(index = [801], inplace = True)

Our next step will be converting the columns to correct data types so that we can work with that data in python later if we need. This doesn’t matter so much for writing to the SQL database because we can specify formatting later on.

#Converting some columns to correct data type
customers['create_date'] = customers['create_date'].astype(int)
customers['mailing'] = customers['mailing'].astype(float)
customers['last_modified'] = customers['last_modified'].astype(int)
customers['create_date'] = customers['create_date'].transform(lambda x: time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(x)))
customers['last_modified'] = customers['last_modified'].transform(lambda x: time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(x)))

Lets just take one more look at the info to make sure we’re good to go.

customers.info()

<class 'pandas.core.frame.DataFrame'>
Int64Index: 3054 entries, 0 to 8571
Data columns (total 6 columns):
id               3054 non-null object
fname            3054 non-null object
lname            3054 non-null object
create_date      3054 non-null object
mailing          3054 non-null float64
last_modified    3054 non-null object
dtypes: float64(1), object(5)
memory usage: 167.0+ KB

Orders Table

As before, we will first drop the duplicates. Then we drop the remaining rows with null values, about 3 rows in this table.

orders.drop_duplicates(subset='id', inplace = True)
orders.dropna(inplace = True)

Then we do the feature transformations similar to the previous table.

orders['total'] = orders['total'].astype(float)
orders['updated_date'] = orders['updated_date'].transform(lambda x: time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(x)))
orders['shipped_date'] = orders['shipped_date'].transform(lambda x: time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(x)))

Next, we’ll create a column ‘profit’ that will be useful for segmentation later. This is also useful to get a quick snapshot of how much our company is making from each transaction.

orders['profit'] = (orders['Items.price'] - orders['Items.cost']) / orders['Items.qty']
orders['profit'].describe()

count    3565.000000
mean       15.067519
std        22.067572
min       -29.510000
25%         6.010000
50%        10.620000
75%        18.000000
max       549.000000
Name: profit, dtype: float64

Already we the benefit of creating this column is apparent, there are some orders we are actually LOSING money on! We can sort by those transactions that our profit is less than 0 for further analysis later on, although we won’t be covering it in this specific project, we have to stay focused on the task at hand!

#we can take a look at which orders we're losing money on and why
orders[orders['profit'] < 0]

We can drill down and look at things like what shipping methods we use most. Maybe we can cut a deal with USPS or whoever we’re doing the most business with. We can also see how much free shipping is happening, that may effect our bottom line.

orders['shipping_method'].value_counts()

11|Ground 2549 0|Standard Shipping 697 -1|Free Shipping 220 0|Free Shipping 86 NATIONAL DELIVERY 2 HOWARD’S EXPRESS, INC. 2 WILSON TRUCKING CORP 2 NATIONAL DELIVERY SYSTEMS 1 MEDTRANS 1 SOUTHWESTERN MOTOR TRNAS 1 ROADWAY EXPRESS 1 FEDERAL EXPRESS CORP. 1 INTERNATIONAL FEDEX 1 LAND AIR OF NEW ENGLAND 1 Name: shipping_method, dtype: int64

Now that this table is all cleaned up, we can move on to the final table.

Products Table

As before we’ll drop the duplicates, the null values (4 rows this time) and then do the feature transformations.

products.drop_duplicates(subset= 'id', inplace = True)
products.dropna(inplace = True)

#Feature Transformations
products['last_modified'] = products['last_modified'].transform(lambda x: time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(x)))
products['import_id'] = products['import_id'].astype(str)
products['vendor'] = products['vendor'].astype(str)

At this point, we can use NLP to analyze our products, see which ones are most popular, and group them into the types of products that give us the most profit. Again, this is beyond the scope of this specific project, which is to create a basic customer segmentation and a RFM table (eyes on the prize).

Writing to SQL Database

In this basic example, I will be using SQLite3, although I could use SQLalchemy or one of the other million packages that interface between python and SQL.

#Create Connection
conn = sqlite3.connect('SQL Server Filepath\\.db')

#Customers table
customers.to_sql('customers', conn, if_exists='replace', index=True)

#Orders table
orders.to_sql('orders', conn, if_exists='replace', index=True)

#Products table
products.to_sql('products', conn, if_exists='replace', index=True)

conn.close()

Groupby Analysis

With our first task behind us, we can start to see the finish line in the distance…. A bit far in the distance, but we can see it, and the fun bit is still ahead! Since we didn’t really do a deep dive into the products table, we’ll mostly be looking at our customer and order information.

#Make a FullName variable to merge on easily
#People can share the same first or last name, but rarely share both
customers['FullName'] = customers['fname'] + customers['lname']
orders['FullName'] = orders['fname'] + orders['lname']

#Create the new DataFrame
df2 = pd.merge(customers, orders, how= 'outer', on='FullName')

#Drop the redundant columns
df2.drop(['fname_x', 'lname_x', 'fname_y', 'lname_y'], axis = 1, inplace = True)

Lets take another snapshot of our customer buying habits, similar to before, but now we have the profit column as well.

#Lets look at a quick snapshot of customer buying habits
df2_items = df2.groupby('FullName')['Items.qty', 'Items.price', 'Items.cost', 'profit'].sum()
df2_items.sample(20)

And we can take a look at the shipping costs in case that’s something we want to analyze later on.

df2_order = df2.groupby('FullName')['subtotal', 'shipping', 'total'].sum()
df2_order.sample(20)

Now, we’ll create the ‘recency’ aspect of our Recency-Frequency-Monetary table for later.

df3 = df2.copy()
df3.dropna(inplace=True)
df3['placed_date'] = df3['placed_date'].transform(lambda x: time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(x)))

#Newest orders
df_order_new = df3.groupby('id_x')['placed_date'].max()

#Oldest orders
df_order_old = df3.groupby('id_x')['placed_date'].min()

df_order_old = pd.DataFrame(df_order_old)
df_order_new = pd.DataFrame(df_order_new)
df_order = pd.DataFrame()

df_order[['first_order', 'recent_order']] = pd.merge(df_order_old, df_order_new, left_on = 'id_x', right_on= 'id_x')

Now that we’ve done a good bit of feature engineering and analysis, it’s time to do the actual modeling and segmentation.

Modeling

The first thing that we’ll use is PCA, which helps to reduce the amount of features so that we can cluster. PCA can make it difficult to determine WHY the customers are segmented the way they are, so we will also be segmenting based on profit and recency, then comparing the 2 methods.

PCA

#PCA only works on numerical datatypes, so we'll select those out
numerical_features = df2.select_dtypes(exclude='object').columns

pca_df = df2[numerical_features]

pca_df.dropna(inplace=True)

#Create the Principal Components
pca_2 = PCA(n_components=2).fit(pca_df)

#Most of the variance in our features can be explained with 1 PC
#We will include 2 for ease of visualization
print(pca_2.explained_variance_ratio_)

Now, we’ll create a DataFrame of the principal components and add it to our df2 for later use.

#Create a PC DataFrame
principalcomponents = pca_2.fit_transform(pca_df)

principal_df = pd.DataFrame(data = principalcomponents, columns = ['principal component 1', 'principal component 2'])

df2.dropna(inplace = True)
#Make sure theyre the same shape so they merge properly
principal_df.shape, df2.shape

((3569, 2), (3569, 31))

df2 = pd.concat([df2, principal_df], axis = 1)

Clustering with KMeans

Since clustering requires low dimensionality data, we could use PCA to cluster. As stated before, PCA can make it difficult to know WHY the clusters exist how they do, so we will create a table to cluster based on profit and recency of the previous purchase. Other variables we could use to cluster include: shipping type, which vendor the customer used, and the types of products (if we used NLP to group product types).

Clustering based on Profit

df_cluster = df2.groupby('id_x')['profit', 'placed_date'].agg({'profit': 'sum','placed_date': 'max'})
df_cluster.head()

df_cluster.dropna(inplace = True)

X = pd.DataFrame(df_cluster)

#Start off with a large amount of clusters to analyze the optemization
K = KMeans(n_clusters = 9).fit(X)

With our cluster fit, it’s time to determine the optimal amount of groups for our customers. We will use yellowbrick’s clustering visualizers to help out with this. Silhouette scores closer to 1 are better (ranked from 0-1).

According to the graphs, 4 or 5 clusters would be best. We’ll choose 4 for now.

K = KMeans(n_clusters = 4).fit(X)
silhouette_score(X, K.labels_)

0.6309803241979693

#Now we'll add the cluster labels to our final DataFrame that we'll use for reporting
df_cluster['cluster'] = K.labels_
df_cluster.head()

Clustering based on PCA

We will follow the same process to determine cluster labels using PCA.

#Start off with a large amount of clusters to analyze the optemization
K = KMeans(n_clusters = 7).fit(principal_df)

According to the graphs, 3 clusters is best for the PCA model.

K = KMeans(n_clusters = 3).fit(X)
silhouette_score(X, K.labels_)

0.6402889003646225

We can see that the silhouette score for the PCA model is SLIGHTLY better than the previous model, but the PCA clusters may not be able to give us actionable information.

RFM Table

Our final task, create the RFM table to report to our boss. We will include the clusters as well so they know who to reach out to with certain promotions.

df_cluster = pd.merge(df_cluster, df_order, left_on= 'id_x', right_on= 'id_x')
df_cluster.head(30)

Here we have a table grouped by the customer ID, that shows us the profit we have earned from each customer, the time of their last order, and their cluster number. The clusters can be analyzed further to determine which type of product/offer will be pushed to them.

That’s all for now! As you can tell, there is a lot more that can be done with this data, but due to time and objective constraints I have limited this project to what is done here. If the boss wants us to dive a bit deeper using NLP on our product descriptions, or analyze the clusters more/differently that can be done in further iterations of the project.

Thank you for taking the time to read through this project. If you have any questions, please feel free to reach out to me via email!