# **【品牌忠誠度Brand loyalty/購物車品牌轉換率Shopping cart brand conversion rate_eCommerce Events History in Cosmetics Shop】ft. Kaggle - Python**

:::info
- 品牌忠誠度
- 購物車品牌轉換率
:::

[資料來源 : eCommerce Events History in Cosmetics Shop
](https://www.kaggle.com/datasets/mkechinov/ecommerce-events-history-in-cosmetics-shop)


import pandas as pd
import numpy as np
import seaborn as sb
import kagglehub

path = kagglehub.dataset_download("mkechinov/ecommerce-events-history-in-cosmetics-shop")

print("Path to dataset files:", path)

這裡只抽取200萬筆來做


### 品牌忠誠度

# 只保留購買行為
purchase_df = df_2[df_2['event_type'] == 'purchase'].copy()

# 每位用戶對品牌的購買次數
user_brand_counts = purchase_df.groupby(['user_id', 'brand']).size().reset_index(name='purchase_count')
user_brand_counts


# 計算是否為重複購買（同一品牌購買 >= 2 次）
user_brand_counts['repeated'] = (user_brand_counts['purchase_count'] >= 2).astype(int)

brand_loyalty = user_brand_counts.groupby('brand').agg(
    num_buyers=('user_id', 'nunique'),         # 購買過此品牌的用戶數
    num_repeat_buyers=('repeated', 'sum')      # 有重複購買的用戶數
).reset_index()

# 回購率（忠誠度）
brand_loyalty['repeat_rate'] = brand_loyalty['num_repeat_buyers'] / brand_loyalty['num_buyers']

# 篩選忠誠度高且用戶數夠的品牌
filtered_loyal_brands = brand_loyalty[
    (brand_loyalty['num_buyers'] >= 30) &       # 至少 30 人購買
    (brand_loyalty['repeat_rate'] >= 0.2)       # 至少 20% 有重複購買
].sort_values(by='repeat_rate', ascending=False)

# 忠誠品牌
filtered_loyal_brands.head(5)


# 空列表來存儲每個忠誠品牌對應的 other_brand
other_brands_for_loyal_brands = []
other_brands_for_loyal_brands_2 = []

#
for index, row in filtered_loyal_brands.iterrows():
    current_loyal_brand = row['brand']

    # 找出購買忠誠品牌的用戶ID
    loyal_brand_user_ids = user_brand_counts[
        user_brand_counts['brand'] == current_loyal_brand
    ]['user_id'].unique()

    # 篩選出這些用戶的購買記錄
    all_purchases_by_these_users = purchase_df[
        purchase_df['user_id'].isin(loyal_brand_user_ids)
    ].copy()

    # 購買過的、但不是忠誠品牌的其他品牌
    other_brands_bought = all_purchases_by_these_users[
        ~all_purchases_by_these_users['brand'].isin([current_loyal_brand])
    ]

    # 計算哪個品牌被購買次數最多
    if not other_brands_bought.empty:
        # 按 brand 分組並計算購買次數，然後取次數最多的那個品牌
        top_other_brand_df = other_brands_bought.groupby('brand').size().reset_index(name='total_purchases')
        top_other_brand = top_other_brand_df.sort_values(by='total_purchases', ascending=False).iloc[0]['brand']
        second_other_brand = top_other_brand_df.sort_values(by='total_purchases', ascending=False).iloc[1]['brand']
    else:
        top_other_brand = np.nan # 如果沒有購買其他品牌，則設為NaN
        second_other_brand = np.nan

    other_brands_for_loyal_brands.append(top_other_brand)
    other_brands_for_loyal_brands_2.append(second_other_brand)


filtered_loyal_brands['other_brand'] = other_brands_for_loyal_brands
filtered_loyal_brands['other_brand_2'] = other_brands_for_loyal_brands_2


filtered_loyal_brands


<br/>

### 購物車品牌轉換率

# 各品牌的 cart 和 purchase 數量
df_2 = df_2[df_2['brand'].notna()]
cart_by_brand = df_2[df_2['event_type'] == 'cart'].groupby('brand').size()
purchase_by_brand = df_2[df_2['event_type'] == 'purchase'].groupby('brand').size()

# 合併並計算轉換率
brand_df = pd.concat([cart_by_brand, purchase_by_brand], axis=1, keys=['cart_count', 'purchase_count'])
brand_df.fillna(0, inplace=True)
brand_df['conversion_rate'] = brand_df['purchase_count'] / (brand_df['cart_count'] + 1)  # +1 避免除以 0
brand_df


import matplotlib.pyplot as plt

top = brand_df.sort_values('purchase_count', ascending=False).head(10)

#
top[['cart_count', 'purchase_count']].plot(kind='bar', figsize=(8,4))
plt.title("Cart vs Purchase")
plt.ylabel("Event Count")
plt.show()

top['conversion_rate'].plot(kind='bar', figsize=(8,4), color='green')
plt.title("Conversion Rate")
plt.ylabel("Conversion Rate")
plt.show()