<jupyter_start><jupyter_text>load library<jupyter_code>### visualization
%matplotlib inline
import seaborn as sns
import matplotlib.pyplot as plt
import plotly.offline as py
import matplotlib as mat
py.init_notebook_mode(connected=True)
# data analysis and wrangling
import numpy as np
import pandas as pd
#import re
from functools import partial
# other library
import datetime
import calendar
# ignore sys warning
import warnings
import os
import glob
warnings.filterwarnings('ignore')<jupyter_output><empty_output><jupyter_text>Cohort analysis cohort analysis 幫助我們觀察消費者的回購狀況，透過它我們可以迅速發現回購異狀，並以此做更深入的分析，比如比較來源渠道、訂單內容等等。在這一篇 Jupyter 中，我要示範以 python 畫出cohort table。  load data from csvs 由於訂單資料很龐大，因此我先透過 API 將訂單資訊載入到本機端(這一步沒有呈現在這邊)，然後用 for loop 將每個月的訂單 csv 檔案載入並儲存成一個data frame。<jupyter_code>path =r'YOUR PATH' 
allFiles = glob.glob(path + "/*.csv")
df = pd.DataFrame()
list_ = []
for file_ in allFiles:
    df_temp = pd.read_csv(file_,index_col=None, header=0)
    list_.append(df_temp)
df = pd.concat(list_)<jupyter_output><empty_output><jupyter_text>spilt cart by different shop 由於訂單資訊並不會分商城儲存，因此我們必須先將不同商城的資訊拆開<jupyter_code>df = df.loc[df.order_status != 'Cancel']
df_10706 = df.loc[df.shop_id == 10706].reset_index().drop('index', axis=1)
del df
#df_10706.head(3)<jupyter_output><empty_output><jupyter_text>TS to TG 由於訂單資訊包含商品資訊，因此會出現單筆訂單多個商品(列)的情況，由於cohort table並不需要商品資訊，因此我們將去除商品資訊與去除重複訂單資訊。<jupyter_code>df_10706_tg = df_10706[['cart_code', 'member_code', 'order_time', 'order_label', 
                        'payment', 'promotion_discount', 'ecoupon_discount']]

#df_10706_tg.head(3)
df_10706_tg = df_10706_tg.groupby(['cart_code', 'member_code', 'order_time', 'order_label'], as_index=False).sum()
df_10706_tg = df_10706_tg.drop_duplicates()
#df_10706_tg.head(3)<jupyter_output><empty_output><jupyter_text>下面我們要處理時間資訊，由於原始資料的時間資訊細至毫秒，但是計算cohort table時，我們僅需到月的資訊，因此我們將訂購時間抓至月份。<jupyter_code>df_10706_tg['order_time'] = df_10706_tg['order_time'].str[:7]
df_10706_tg['order_time'] = pd.to_datetime(df_10706_tg['order_time'], format='%Y-%m-%d')
#df_10706_tg.head(3)<jupyter_output><empty_output><jupyter_text>Find first buy day 計算cohort的第一步在於先找出每位消費者第一次購買的月份，如此我們才可以根據消費者的首購月份計算每個 m + n 的回購<jupyter_code>df_10706_tg = pd.merge(df_10706_tg,
                       df_10706_tg.groupby('member_code', as_index=False)['order_time'].min(), 
                       'left',
                       'member_code')
df_10706_tg.columns = ['cart_code', 'member_code', 'order_time', 'order_label', 'payment', 
                       'promotion_discount', 'ecoupon_discount', 'first_buy']
#df_10706_tg.head(3)<jupyter_output><empty_output><jupyter_text>define cohort_group & order_period 將訂購日期與首購日期轉換成月份，如此方便我們計算cohort<jupyter_code>df_10706_tg['cohort_group'] = df_10706_tg.first_buy.astype(str).str[:7] 
df_10706_tg['order_period'] = df_10706_tg.order_time.astype(str).str[:7]
#df_10706_tg.head(3)<jupyter_output><empty_output><jupyter_text>calculate each cohort_group & order_period metrics 在這邊我們要分別計算每群不同首購月份的族群在不同月份的貢獻，這邊我們會特別計算人數與訂單數<jupyter_code>cohorts = df_10706_tg.groupby(['cohort_group', 'order_period']).agg({'member_code': pd.Series.nunique,
                                                                     'cart_code': pd.Series.nunique})

# make the column names more meaningful
cohorts.rename(columns={'UserId': 'TotalUsers',
                        'OrderId': 'TotalOrders'}, inplace=True)
cohorts.head()<jupyter_output><empty_output><jupyter_text>label cohort period 這邊我們要將每個月份依照不同的首購月份給予編號，以便我們方便比較。比如對比11月和12月的回購狀況，10月m+1和11月m+1的比較反而更直覺<jupyter_code>cohorts['period'] = cohorts.groupby(level=0).cumcount()
cohorts.head()<jupyter_output><empty_output><jupyter_text>reindex cohort 這邊我們將以剛重新計算的編號來取代原先的編號<jupyter_code>cohorts.reset_index(inplace=True)
cohorts.set_index(['cohort_group', 'period'], inplace=True)
cohorts.head()<jupyter_output><empty_output><jupyter_text>calculate user retention by cohort 這邊開始我們要根據回購人來計算cohort table，後續我們還會根據訂單數來做一次，詳細說明僅會在這邊呈現。首先我們先取得首購月份的總人數<jupyter_code>cohorts_usr_group = cohorts['member_code'].groupby(level=0).first()
cohorts_usr_group.head()<jupyter_output><empty_output><jupyter_text>再來我們先將 cohorts 以剛得到的總人數來計算各個月分的回購比例<jupyter_code>cohorts_usr = cohorts['member_code'].unstack(0)                  #將cohort_group作為欄位
cohorts_usr = cohorts_usr.divide(cohorts_usr_group, axis=1)      #計算每個月回購率
cohorts_usr.head()<jupyter_output><empty_output><jupyter_text>畫出不同首購月份的回購線圖<jupyter_code>cohorts_usr[['2017-01', '2017-02', '2017-03', '2017-04', '2017-05', '2017-06', '2017-07', '2017-08', '2017-09']].plot(figsize=(20,10))
plt.title('Cohorts: User Retention')
plt.xticks(np.arange(0, 12.1, 1))
plt.xlim(0, 12)
#plt.ylim(0, 0.2)
plt.ylabel('% of Cohort Purchasing')<jupyter_output><empty_output><jupyter_text>呈現 cohort table<jupyter_code>cohorts_usr.columns = ['1612', '1701', '1702', '1703', '1704', '1705',
                       '1706', '1707', '1708', '1709', '1710']
sns.set(style='white')
plt.figure(figsize=(20, 12))
plt.title('Cohorts: User Retention', size=24)
mat.rcParams.update({'font.size': 14})
plt.tick_params(labelsize=14)
sns.heatmap(cohorts_usr.T, mask=cohorts_usr.T.isnull(), annot=True, fmt='.2%', cmap='Paired')<jupyter_output><empty_output><jupyter_text>compute order retention by cohort 下列展示以訂單數為主要衡量回購基礎的算法，方法與上述相同，在此不贅述<jupyter_code>cohorts_crt_group = cohorts['cart_code'].groupby(level=0).first()
cohorts_crt_group.head()
cohorts_crt = np.round(cohorts['cart_code'].unstack(0).divide(cohorts_crt_group, axis=1), 4)
cohorts_crt.head()
cohorts_crt.reset_index()<jupyter_output><empty_output><jupyter_text>劃出每個月訂單回購變化<jupyter_code>cohorts_crt[['2017-01', '2017-02', '2017-03', '2017-04', '2017-05', '2017-06', '2017-07', '2017-08', '2017-09']].plot(figsize=(20,10))
plt.title('Cohorts: Cart Retention')
plt.xticks(np.arange(0, 12.1, 1))
plt.xlim(0, 12)
#plt.ylim(0, 0.2)
plt.ylabel('% of Cohort Purchasing')<jupyter_output><empty_output><jupyter_text>畫出以訂單為基準的cohort table<jupyter_code>cohorts_crt.columns = ['1612', '1701', '1702', '1703', '1704', '1705', '1706', '1707', '1708', '1709', '1710']
sns.set(style='white')
plt.figure(figsize=(20, 12))
plt.title('Cohorts: User Retention', size=24)
mat.rcParams.update({'font.size': 14})
plt.tick_params(labelsize=14)
sns.heatmap(cohorts_crt.T, mask=cohorts_crt.T.isnull(), annot=True, fmt='.2%', cmap='Paired')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1481597986788298, 'fraction_numerical': 0.06480025165146272, 'mean_word_length': 7.3232984293193715, 'pattern_counts': {'":': 0, '<': 69, '<?xml version=': 0, '>': 69, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 3, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '17', 'dir': 'jupyter-structured-clean-dedup', 'id': '54731', 'license': 'MIT', 'n_tokens_mistral': 3395, 'n_tokens_neox': 3193, 'n_words': 328, 'path': 'cohort_example.ipynb', 'repo_name': 'jwjhuang/ecommerce_analysis_tools', 'size': '6358'}