1- İş Problemi
Brezilyalı E-Ticaret firması olan Olist müşterilerini segmentlere ayırıp bu segmentlere göre pazarlama stratejileri belirlemek istiyor. Buna yönelik olarak müşterilerin davranışları tanımlanacak ve bu davranışlardaki öbeklenmelere göre gruplar oluşturulacak.
2- Veriyi Anlama ve Hazırlama
Veri seti diğer csv dosyaları gibi hazır olmayıp database içerisinde 9 farklı tablo şeklinde bulunuyor. Gerekli import işlemlerini yaptıktan sonra aşağıdaki gibi tablolar alınır.
# Step 1: Gathering Data from DataBase ( PostgreSQL )user = "*****"
password = "*****"
host = "localhost"
port = "5432"
database = "db_name"
connection_string = f"postgresql://{user}:{password}@{host}:{port}/{database}"
con = create_engine(connection_string)
df_customers = pd.read_sql('select * from customers', con)
df_geolocation = pd.read_sql('select * from geolocation', con)
df_orders = pd.read_sql('select * from orders', con)
df_order_items = pd.read_sql('select * from order_items', con)
df_order_payments = pd.read_sql('select * from order_payments', con)
df_order_reviews = pd.read_sql('select * from order_reviews', con)
df_products = pd.read_sql('select * from products', con)
df_sellers = pd.read_sql('select * from sellers', con)
df_translations = pd.read_sql('select * from product_translation', con)
Her bir df’i check_df_names fonksiyonu ile incelenir.
# Step 2: examination of missing valuesdf_name_list = ['df_customers', 'df_geolocation', 'df_orders', 'df_order_items', 'df_order_payments',
'df_order_reviews', 'df_products', 'df_sellers', 'df_translations']
for df in df_name_list:
check_df_names(df)
##################### NA #####################
order_id 0
customer_id 0
order_status 0
order_purchase 0
order_approved 160
order_delivered_carrier 1783
order_delivered_customer 2965
order_estimated_delivery 0
dtype: int64
##################### NA #####################
review_id 0
order_id 0
review_score 0
review_title 87656
review_comment 58247
review_create 0
review_answer 0
dtype: int64
##################### NA #####################
product_id 0
product_category 610
product_name_length 610
product_desc_length 610
product_photos_qty 610
product_weight_grams 2
product_length_cm 2
product_height_cm 2
product_width_cm 2
dtype: int64
Veri setine baktığımızda df_orders, df_order_reviews ve df_products için eksik değerlerin olduğunu görürüz. Bu eksik değerlerin atamalarını yapılır.
# for df_orders
df_orders['order_approved'].fillna(df_orders['order_purchase'], inplace=True)
df_orders['order_delivered_carrier'].fillna(df_orders['order_approved'], inplace=True)
df_orders['order_delivered_customer'].fillna(df_orders['order_estimated_delivery'], inplace=True)
# for df_order_reviews
df_order_reviews = df_order_reviews[~(df_order_reviews['review_title'].isnull())].reset_index(drop=True)
df_order_reviews['review_comment'] = df_order_reviews['review_comment'].replace(np.nan, 'None')
# for df_products
df_products.isnull().sum()
df_products_na_list = ['product_category', 'product_name_length', 'product_desc_length', 'product_photos_qty',
'product_weight_grams', 'product_length_cm', 'product_height_cm', 'product_width_cm']
for col in df_products_na_list:
df_products = df_products[~(df_products[col].isnull())]Müşterileri satın almalarına göre inceleyeceğimiz içi tüm dataframe’leri birleştirip tek bir dataframe üzerinden analiz gerçekleştirilir.
# Step 3: Merging All Dataframesdf_merge = pd.merge(df_orders, df_order_payments, on='order_id')
df_merge = pd.merge(df_merge, df_customers, on='customer_id')
df_merge = pd.merge(df_merge, df_order_items, on='order_id')
df_merge = pd.merge(df_merge, df_sellers, on='seller_id')
df_merge = pd.merge(df_merge, df_order_reviews, on='order_id')
df_merge = pd.merge(df_merge, df_products, on='product_id')
df_translations.columns = ['product_category', 'category_translation']
df_merge = pd.merge(df_merge, df_translations, on='product_category')df_merge.shape # (13801,40)
# tekrar eden satırların silinmesi
df_merge = df_merge.drop_duplicates(subset={'order_id',
'customer_id',
'order_purchase',
'order_delivered_customer'}, keep='first')
En çok satın alınan ürünler görselleştirilir.
# Step 4: Finding 10 most existing unique products and showing them in a bar-plotsplt.figure(figsize=(6, 10))
top10_sold_products = df_merge.groupby('product_category')['product_id'].count().sort_values(ascending=False).head(10)
sns.barplot(x=top10_sold_products.index, y=top10_sold_products.values)
plt.xticks(rotation=80)
plt.xlabel('Product category')
plt.title('Top 10 products')
plt.show()
3- RFM Metriklerinin Hesaplanması
Metrikleri ayrı ayrı hesapladıktan sonra rfm adında bir dataframe oluşturup hepsi burada birleştirilir.
# MONETARY
df_merge['TOTAL_SALES_QUANTITY'] = df_merge['payment_value'] * df_merge['payment_installments']
df_monetary = df_merge.groupby(['customer_unique_id'],
group_keys=False,
as_index=False).agg({'TOTAL_SALES_QUANTITY': 'sum'}).reset_index(drop=True)
df_monetary.rename(columns={'TOTAL_SALES_QUANTITY': 'monetary'}, inplace=True)
# FREQUENCY
df_frequency = df_merge.groupby(['customer_unique_id'],
group_keys=False,
as_index=False).agg({'order_id': 'count'}).reset_index(drop=True)
df_frequency.rename(columns={'order_id': 'frequency'}, inplace=True)# Merging df_monetary and df_freq tables
df = pd.merge(left=df_monetary,
right=df_frequency,
on='customer_unique_id',
how='inner')# RECENCY
df_merge['order_purchase'].max() # 2018-08-29
analysis_date = dt.datetime(2018, 8, 30)
df_merge['DAYS'] = (analysis_date - df_merge['order_purchase']).dt.days
df_recency = df_merge.groupby(['customer_unique_id'],
group_keys=False,
as_index=False).agg({'DAYS': 'min'}).reset_index(drop=True)
df_recency.rename(columns={'DAYS': 'recency'}, inplace=True)# create rfm df
rfm = pd.merge(left=df,
right=df_recency,
on='customer_unique_id',
how='inner')
rfm.head()
# Visualize metrics
plt.figure(figsize=(12, 10))
plt.subplot(3, 1, 1); sns.distplot(rfm['recency'])
plt.subplot(3, 1, 2); sns.distplot(rfm['frequency'])
plt.subplot(3, 1, 3); sns.distplot(rfm['monetary'])
plt.show()
4- Müşterilerin K-Means ile RFM Metriklerine göre Segmentasyonu
# Step 1: Data Standardizationrfm_scaler = rfm[['monetary', 'frequency', 'recency']]
sc = MinMaxScaler((0, 1))
model_scaling = sc.fit_transform(rfm_scaler)
model_df = pd.DataFrame(model_scaling, columns=rfm_scaler.columns)
model_df.head()
# Step 2: Finding optimal number of clusterskmeans = KMeans()
elbow = KElbowVisualizer(kmeans, k=(2, 20))
elbow.fit(model_df)
elbow.show()
# Step 3: Building K-Means Model
k_means = KMeans(n_clusters=5, random_state=42).fit(model_df)
segments = k_means.labels_# Step 4: Looking at frequency of segments
pd.DataFrame(segments).value_counts()
rfm['segment'] = segments
rfm.head()
# Step 5: Statistical examination of each segmentrfmStats = rfm[["segment", "recency", "frequency", "monetary"]].groupby("segment").agg(["mean", "median", "count", "std"])
rfmStats
# Step 6: Visualize SegmentsnewIndex=["segment_0","segment_1","segment_2","segment_3","segment_4"]
rfmStats.index=newIndex
plt.figure(figsize=(10, 6))
squarify.plot(sizes=rfmStats["recency"]["count"], label=rfmStats.index,
color=["cornsilk", "pink","royalblue", "red", "yellow"], alpha=.4)
plt.suptitle("Treemap: Number of Customers", fontsize=20)
