Background

• The company is a domestic manufacturer of bicycles and related accessories.
• By monitoring online and offline sales of commodities through dashboard, we can grasp the latest sales trends and regional distribution of commodities, for optimizing better business and achieving efficient operation.

Mind Map

E-com dashboard.jpeg

Dashboard Exhibition

image.png

【Dashboard Link】https://app.powerbi.com/view?r=eyJrIjoiNzQ4YzM5NDYtYTNhMC00YWY3LWI5ZDUtMmYzYmIwNWIyYWMzIiwidCI6ImNmY2E2NmVlLWM2MjYtNDQ3Ny1iZmE3LWIzMjk2MTA3N2U5MCJ9&pageName=ReportSection

Data preprocessing

Before generate the dashboard, do processing to the data.

1.Import modules and data

import sqlalchemy
import pandas as pd
#coding:gbk
engine=sqlalchemy.create_engine("mysql+pymysql://<user>:<password>@<IPaddress>:<port>/<database>?charset=gbk")
sql_cmd='select * from <table>'
dw_customer_order=pd.read_sql_query(sql=sql_cmd,con=engine)

#new column year-month
gather_customer_order['create_year_month']=gather_customer_order['create_date'].apply(lambda x:x[:7])

#new column year-season
gather_customer_order['季度']=gather_customer_order['create_date'].apply(lambda x:(datetime.datetime.strptime(x,'%Y-%m-%d').month-1)//3+1).astype('str')+'季度'
gather_customer_order['year']=gather_customer_order['create_date'].apply(lambda x:(datetime.datetime.strptime(x,'%Y-%m-%d').year)).astype('str')+'年'
gather_customer_order['year_season']=gather_customer_order['year']+gather_customer_order['季度']
del gather_customer_order['季度'],gather_customer_order['year']

image.png

2.Sales Amount Comparison

2.1 Product Sales Amount Comparision Between Quarters

# 對(duì)產(chǎn)品大類(lèi)和時(shí)間進(jìn)行聚合
cplb_zw=gather_customer_order.groupby(['cplb_zw','year_season']).agg({'sum_amount':"sum"})
cplb_zw=cplb_zw.reset_index()
a=pd.Series([])
for i in cplb_zw['cplb_zw'].unique():
    b=cplb_zw[cplb_zw['cplb_zw']==i]['sum_amount'].pct_change()
    a=a.append(b)
cplb_zw['diff']=a
cplb_zw['diff']=cplb_zw['diff'].apply(lambda x:str(round(x,4)*100))
cplb_zw['diff']=cplb_zw['diff'].replace(0,'0.00')
cplb_zw['diff']=cplb_zw['diff'].apply(lambda x:x.split('.')[0]+'.'+x.split('.')[1][:2])
cplb_zw['diff']=cplb_zw['diff']+'%'
cplb_zw['diff']=cplb_zw['diff'].replace('0.00%','-')

image.png

# export data to database
save_engine=sqlalchemy.create_engine("mysql+pymysql://root:123@127.0.0.1:3306/mozi?charset=gbk")
cplb_zw.to_sql('cplb_zw_season',con=save_engine,if_exists='replace')

2.2 Product Sales Amount Comparision Between Months

import sqlalchemy
import pandas as pd
import numpy as np
#coding:gbk
engine=sqlalchemy.create_engine("mysql+pymysql://<user>:<password>@<IPaddress>:<port>/<database>?charset=gbk")
sql_cmd='select * from <table>'
dw_customer_order=pd.read_sql_query(sql=sql_cmd,con=engine)
dw_customer_order['create_year_month']=dw_customer_order['create_date'].apply(lambda x:x[:7])
dw_customer_order2=dw_customer_order.groupby('create_year_month').agg({'order_num':sum,'sum_amount':sum})
dw_customer_order2['order_num_diff']=dw_customer_order2['order_num'].diff()/dw_customer_order2['order_num']
dw_customer_order2['sum_amount_diff']=dw_customer_order2['sum_amount'].diff()/dw_customer_order2['sum_amount']
dw_customer_order2=dw_customer_order2.reset_index()
dw_customer_order2

image.png

#export data to database
save_engine=sqlalchemy.create_engine("mysql+pymysql://root:123@127.0.0.1:3306/bike_sales?charset=gbk")
dw_customer_order2.to_sql('dw_customer_order2',con=save_engine,if_exists='replace')

2.3 Proportion of bicycle sales quantity

Proportion of bicycle sales quantity

gather_customer_order=dw_customer_order
#new columns year-month
gather_customer_order['create_year_month']=gather_customer_order['create_date'].apply(lambda x:x[:7])
#analyze bike only
gather_customer_order=gather_customer_order[gather_customer_order['cplb_zw']=='自行車(chē)']
#groupby month 
overall_sales_performance=gather_customer_order.groupby('create_year_month').agg({'order_num':sum,'sum_amount':sum})
#Cumulative sales of bicycles per month
gather_customer_order_group_month = overall_sales_performance.reset_index()
#Merge bicycle sales information tables
order_num_proportion = pd.merge(gather_customer_order,gather_customer_order_group_month,how='left',on='create_year_month')
order_num_proportion=order_num_proportion.drop(columns='sum_amount_y')
#Calculate bicycle sales/monthly bicycle sales ratio
order_num_proportion['order_proportion'] = order_num_proportion['order_num_x']/order_num_proportion['order_num_y']
#rename order_num_y as sum_month_order
order_num_proportion=order_num_proportion.rename(columns={'order_num_y':'sum_month_order'})

image.png

# export database 
order_num_proportion.to_sql('pt_bicycle_product_sales_month',con=save_engine)

Road/mountain/tourist bicycle segment performance

2.3.1 Road bicycle

gather_customer_order_road=gather_customer_order[gather_customer_order['cpzl_zw']=='公路自行車(chē)']
#Sales quantity of different types of road bicycles
gather_customer_order_road_month = gather_customer_order_road.groupby(['create_year_month','product_name']).agg({'order_num':sum})
gather_customer_order_road_month=gather_customer_order_road_month.reset_index()
gather_customer_order_road_month=gather_customer_order_road_month[['create_year_month','product_name','order_num']]
#Cumulative sales of road bicycles every month
gather_customer_order_road_month_sum = gather_customer_order_road_month.groupby('create_year_month').agg({'order_num':sum})
gather_customer_order_road_month_sum=gather_customer_order_road_month_sum.reset_index()
#merge gather_customer_order_road_month and cumulative monthly sales
#Used to calculate the proportion of different models of products
gather_customer_order_road_month1 = pd.merge(gather_customer_order_road_month,gather_customer_order_road_month_sum,how='left',on='create_year_month')
gather_customer_order_road_month1

image.png

2.3.2 Mountain bicycle

#screening
gather_customer_order_mountain=gather_customer_order[gather_customer_order['cpzl_zw']=='山地自行車(chē)']
#Sales quantity of different types of mountain bikes
gather_customer_order_Mountain_month = gather_customer_order_mountain.groupby(['create_year_month','product_name']).agg({'order_num':sum})
gather_customer_order_Mountain_month=gather_customer_order_Mountain_month.reset_index()

#Cumulative sales of bicycles on mountain road every month
gather_customer_order_Mountain_month_sum = gather_customer_order_Mountain_month.groupby('create_year_month').agg({'order_num':sum})

#merge hz_customer_order_Mountain_month adn Cumulative sales of bicycles on mountain road every month
#Used to calculate the proportion of different models of products
gather_customer_order_Mountain_month = pd.merge(gather_customer_order_Mountain_month,gather_customer_order_Mountain_month_sum,how='left',on='create_year_month')
gather_customer_order_Mountain_month.head()

image.png

2.3.3 Tourist bicycle

gather_customer_order_tour = gather_customer_order[gather_customer_order['cpzl_zw'] == '旅游自行車(chē)']
gather_customer_order_tour_month=gather_customer_order_tour.groupby(['create_year_month','product_name']).agg({'order_num':sum})
gather_customer_order_tour_month=gather_customer_order_tour_month.reset_index()
gather_customer_order_tour_month_sum=gather_customer_order_tour.groupby('create_year_month').agg({"order_num":sum})
gather_customer_order_tour_month_sum=gather_customer_order_tour_month_sum.reset_index()
gather_customer_order_tour_month=pd.merge(gather_customer_order_tour_month,gather_customer_order_tour_month_sum,how='left',on='create_year_month')
gather_customer_order_tour_month.head()

image.png

Merge three types of bicycle data

gather_customer_order_month=pd.concat([gather_customer_order_Mountain_month,gather_customer_order_road_month1,gather_customer_order_tour_month],axis=0)
gather_customer_order_month['order_num_proportion']=gather_customer_order_month['order_num_x']/gather_customer_order_month['order_num_y']
gather_customer_order_month.rename(columns={'order_num_x':'order_month_product','order_num_y':'sum_order_month'},inplace=True)
gather_customer_order_month.head()

image.png

2.4 Sales amount in February and March of 2021

gather_customer_order_month_2_3=gather_customer_order_month[gather_customer_order_month.create_year_month.isin(['2021-02','2021-03'])]
product_name=gather_customer_order_month_2_3['product_name'].unique().tolist()

order_top_x=pd.Series([])
for i in product_name:
    a=gather_customer_order_month_2_3[gather_customer_order_month_2_3['product_name']==i]['order_month_product'].diff()/gather_customer_order_month_2_3[gather_customer_order_month_2_3['product_name']==i]['order_month_product']
    order_top_x=order_top_x.append(a)
order_top_x=order_top_x.fillna(0)
gather_customer_order_month_2_3['order_num_diff']=order_top_x

3.Analysis of User Behaviors

#Read the database customer information table
engine=sqlalchemy.create_engine("mysql+pymysql://<user>:<password>@<IPaddress>:<port>/<database>?charset=gbk")
df_CUSTOMER = pd.read_sql("select customer_key,birth_date,gender,marital_status from ods_customer where create_date < '2021-02-28'",con = engine)
df_sales_orders_2 = pd.read_sql_query("select *  from ods_sales_orders",con = engine)
df_sales_orders_2['create_year_month']=df_sales_orders_2['create_date'].astype('str').apply(lambda x:x[:7])
df_sales_orders_2.groupby('create_year_month').agg({'create_year_month':'count',
                                                   'unit_price':sum})
df_sales_customer=pd.merge(df_sales_orders_2,df_CUSTOMER,on='customer_key',how='left')
df_sales_customer_drop=df_sales_customer.dropna(axis=0)
df_sales_customer_drop['birth_year']=df_sales_customer_drop['birth_date'].apply(lambda x:x.split('-')[0])
df_sales_customer_drop['birth_year']=2021-df_sales_customer_drop['birth_year'].astype('int')
df_sales_customer_drop.rename(columns={'birth_year':'age'},inplace=True)
age_labels=["30-34","35-39","40-44","45-49","50-54","55-59","60-64"]
age_bins=list(np.arange(30,66,5))
df_sales_customer_drop['age_group']=pd.cut(df_sales_customer_drop['age'],labels=age_labels,bins=age_bins)
age_labels_2=['<=29','30-39','>=40']
age_list_2=[0,29,40,100]
df_sales_customer_drop['age_level_2']=pd.cut(df_sales_customer_drop['age'],labels=age_labels_2,bins=age_list_2)

3.1 Proportion of User Gender

gender_count=df_sales_customer_drop.groupby('gender').agg({'cpzl_zw':'count'}).reset_index()
age_level_2_count=df_sales_customer_drop.groupby('age_level_2').agg({'cpzl_zw':'count'}).reset_index()
df_sales_customer_drop=pd.merge(df_sales_customer_drop,age_level_2_count,on='age_level_2')
df_sales_customer_drop.rename(columns = {'cpzl_zw_y':'age_level2_count'},inplace=True)
df_sales_customer_drop['gender_rate'] = 1/df_sales_customer_drop['gender_count']
df_sales_customer_drop['gender']=df_sales_customer_drop['gender'].replace('F','女性')
df_sales_customer_drop['gender']=df_sales_customer_drop['gender'].replace('M','男性')
df_sales_customer_drop['marital_status']=df_sales_customer_drop['marital_status'].replace('M','已婚')
df_sales_customer_drop['marital_status']=df_sales_customer_drop['marital_status'].replace('S','單身')

image.png

# export to database
df_sales_customer_drop.to_sql('df_sales_customer_dropna',con=save_engine,if_exists='replace')

3.2 User Behavior

3.2.1 Analysis of user consumption trends (monthly)

a.sales amount in every month
b.orders amount in every month
c.sales quantity in every month
d.the number of consumers in every month

df_sales_customer_drop_month=df_sales_customer_drop.groupby('create_year_month').agg({'create_year_month':'count',
                                                                         'unit_price':sum,
                                                                         'customer_key':'nunique'})
df_sales_customer_drop_month.rename(columns = {'unit_price':'消費(fèi)金額', 
                                               'customer_key': '客戶(hù)數(shù)量', 
                                               'create_year_month': '產(chǎn)品購(gòu)買(mǎi)量'}, inplace=True)
df_sales_customer_drop_month=df_sales_customer_drop_month.reset_index().rename(columns={'create_year_month':'年月'})
df_sales_customer_drop_month['銷(xiāo)售平均單價(jià)']=df_sales_customer_drop_month['消費(fèi)金額']/df_sales_customer_drop_month['產(chǎn)品購(gòu)買(mǎi)量']
df_sales_customer_drop_month['平均客單價(jià)']=df_sales_customer_drop_month['消費(fèi)金額']/df_sales_customer_drop_month['客戶(hù)數(shù)量']
#導(dǎo)出數(shù)據(jù)庫(kù)
df_sales_customer_drop_month.to_sql('df_sales_customer_drop_month',con=save_engine,if_exists='replace')

3.2.2 Analysis of individual consumption of users

a.Description statistics of consumption amount and consumption frequency
b.Distribution of users' consumption
c.Proportion of accumulated consumption amount of users

df_sales_customer_drop_client=df_sales_customer_drop.groupby('customer_key').agg({'customer_key':'count',
                                                                                 'unit_price':sum})
df_sales_customer_drop_client=df_sales_customer_drop_client.rename(columns={'customer_key':'購(gòu)買(mǎi)數(shù)量',
                                                                           'unit_price':'消費(fèi)金額'}).reset_index()
df_sales_customer_drop_client=df_sales_customer_drop_client.rename(columns={'customer_key':'客戶(hù)id'})
list=[i for i in range(0,int(df_sales_customer_drop_client['消費(fèi)金額'].max())+1000,500)]
df_sales_customer_drop_client['消費(fèi)金額分組']=pd.cut(df_sales_customer_drop_client['消費(fèi)金額'],bins=list,labels=list[1:])

image.png

#export to database
df_sales_customer_drop_client.to_sql('df_sales_customer_drop_client',con=save_engine,if_exists='replace')

Accumulated consumption

df_sales_customer_drop_cumsum=round(df_sales_customer_drop.groupby('customer_key').sum().sort_values('unit_price').cumsum()/df_sales_customer_drop.groupby('customer_key').sum().sort_values('unit_price').sum(),6)

df_sales_customer_drop_cumsum=df_sales_customer_drop_cumsum.reset_index().rename(columns={'unit_price':'消費(fèi)金額百分比',
                                                            'age':'訂單量百分比',
                                                            'customer_key':'客戶(hù)ID'}).iloc[:,:3]
df_sales_customer_drop_cumsum

image.png

#export to database
df_sales_customer_drop_cumsum.to_sql('df_sales_customer_drop_cumsum',con=save_engine,if_exists='replace')

3.2.3 User consumption behavior

a.First purchase
b.Last purchase
c.RFM model
d.Quantity of new, old, active, backflow and loss
e.User consumption cycle distribution
f.User lift cycle

a.First purchase

df_sales_customer_firstdate=df_sales_customer_drop.groupby('customer_key').min().create_date.value_counts().reset_index().rename(columns={'index':'first_date'})
#export to database
df_sales_customer_firstdate.to_sql('df_sales_customer_firstdate',con=save_engine,if_exists='replace')
df_sales_customer_firstdate

image.png

b.Last purchase

df_sales_customer_lastdate=df_sales_customer_drop.groupby('customer_key').max().create_date.value_counts().reset_index().rename(columns={'index':'last_date'})
#export to database
df_sales_customer_lastdate.to_sql('df_sales_customer_lastdate',con=save_engine,if_exists='replace')
df_sales_customer_lastdate

image.png

df_sales_customer_user_life=df_sales_customer_drop.groupby('customer_key').create_date.agg(['min','max'])
buy_onlyone=pd.DataFrame((df_sales_customer_user_life['min']==df_sales_customer_user_life['max']).value_counts()).rename(columns={0:'buy_onlyone'})

c.RFM model

df_sales_customer_new=df_sales_customer_drop.groupby(['create_year_month','customer_key']).create_date.agg(['min','max'])

df_sales_customer_new['new']=(df_sales_customer_new["min"] == df_sales_customer_new["max"] ) 

# divide user group
# pivot_table
rfm=df_sales_customer_drop.pivot_table(index='customer_key',
                  values=['product_key','unit_price','create_date'],
                  aggfunc={'create_date':'max',
                          'product_key':'count',
                          'unit_price':'sum'})


rfm['R']= -(rfm.create_date - rfm.create_date.max())/np.timedelta64(1,'D')
# R ：Recency  F：Frequency M：Monetary
rfm.rename(columns={'product_key':"F",'unit_price':'M'},inplace=True)
rfm

image.png

def rfm_func(x):
    level=x.apply(lambda x:'1' if x>=0 else '0')
#     print(type(level))
#     print(level.index)
    label=level.R + level.F + level.M
    d={
# R equels 1, indicating that it is larger than the mean and closer to the earliest time; 
# F equels 1, indicating that the consumption amount is relatively large; 
# M equels 1, indicating that the consumption frequency is relatively large, so it is an important value customer 

        '111':'重要價(jià)值客戶(hù)',
        '011':'重要保持客戶(hù)',
        '101':'重要發(fā)展客戶(hù)',
        '001':'重要挽留客戶(hù)',
        '110':'一般價(jià)值客戶(hù)',
        '010':'一般保持客戶(hù)',
        '100':'一般發(fā)展客戶(hù)',
        '000':'一般挽留客戶(hù)',
    }
    result=d[label]
    return result

rfm['label']=rfm[['R','F','M']].apply(lambda x:x-x.mean()).apply(rfm_func,axis=1)

rfm.loc[rfm.label=='重要價(jià)值客戶(hù)','color']='重要價(jià)值客戶(hù)'
rfm.loc[~(rfm.label=='重要價(jià)值客戶(hù)'),'color']='非重要價(jià)值客戶(hù)'
rfm=rfm.reset_index()
rfm

image.png

#export to database
rfm.to_sql('rfm',con=save_engine,if_exists='replace')

d.Quantity of new, old, active, backflow and loss

# pivot_table
pivoted_counts=df_sales_customer_drop.pivot_table(index='customer_key',
                             columns='create_year_month',
                             values='create_date',
                             aggfunc='count').fillna(0)
# if comsume,equals 1,otherwise 0
df_purchase=pivoted_counts.applymap(lambda x:1 if x>0 else 0)

# Here, due to pivottability, null values of 0 are filled in, and it is possible that the user did not register at all during the month，
#This will mislead the statistics of the first consumption data, so write a function to handle it
def active_status(data):
    status=[]
    # There are altogether 15 months of data, and one line of data is input each time for monthly judgment
    for i in range(15):
        # If there is no consumption this month, the result processed above
        if data[i]==0:
            if len(status)>0:
                if status[i-1]=='unreg':
                    status.append('unreg')
                else:
                    status.append('unactive')
            else:
                # If there is no previous data, it is considered unregistered
                status.append('unreg')
                
        #If consume this month
        else:
            if len(status)==0:
                status.append('new')
            else:
                if status[i-1]=='unactive':
                    status.append('return')
                elif status[i-1]=='unreg':
                    status.append('new')
                else:
                    status.append('active')
    return status
                    

If there is no consumption in this month, here is only the last month to judge whether the registration, there is a defect, you can judge whether there is

• If previously unregistered, it is still unregistered
• Loss/inactivity if there was previous consumption
• In other cases, unregistered

purchase_stats=df_purchase.apply(lambda x: pd.Series(active_status(x),index=df_purchase.columns),axis=1)

# This replaces unregistered values with null values so that 'count' is not counted
# The monthly user distribution
purchase_stats_ct=purchase_stats.replace('unreg',np.NaN).apply(lambda x:pd.value_counts(x))

returnratee=purchase_stats_ct.apply(lambda x:x/x.sum(),axis=0)
purchase_stats_ct_info = purchase_stats_ct.fillna(0).T
purchase_stats_ct_info=purchase_stats_ct_info.reset_index()
purchase_stats_ct_info

image.png

e.User consumption cycle distribution

# User purchase cycle (by order)
# Calculate the time interval between two adjacent orders, shift function is to shift the data, all the data will be shifted down a bit, so it is ok

order_diff=df_sales_customer_drop.groupby('customer_key').apply(lambda x:x.create_date-x.create_date.shift())
order_diff1=order_diff.fillna(10)

for i in range(0,len(order_diff1),1):
    if type(order_diff1[i])==int:
        order_diff1[i]=float(order_diff1[i])
for i in range(0,len(order_diff1),1):
    if type(order_diff1[i])!=float:
        order_diff1[i]=float(order_diff1[i].days)
order_diff_cut_lst = [i for i in range(0,int(order_diff1.max())+1,10)]
order_diff_info_hist = pd.cut(order_diff1,bins=order_diff_cut_lst,labels=order_diff_cut_lst[1:])
order_diff_info_hist=pd.DataFrame(order_diff_info_hist)
order_diff_info_hist

image.png

# export to database
order_diff_info_hist.to_sql('order_diff_info_hist',con=save_engine,if_exists='replace')

f.User lift cycle

• Order cycles are exponentially distributed
• The average purchase cycle is 68 days
• The majority of users have a purchase cycle of less than 100 days

user_life=df_sales_customer_user_life
user_life_info = ((user_life['max']-user_life['min'])/np.timedelta64(1,"D"))
user_life_lst = [i for i in range(0,int(user_life_info.max())+1,10)]
user_life_info_hist = pd.cut(user_life_info,bins=user_life_lst,labels=user_life_lst[1:])
user_life_info_hist_2 = user_life_info_hist.fillna(10)
user_life_info_hist_2=pd.DataFrame(user_life_info_hist_2)
user_life_info_hist_2=user_life_info_hist_2.reset_index()
user_life_info_hist_2

image.png

# export to database
user_life_info_hist_2.to_sql('user_life_info_hist_2',con=save_engine,if_exists='replace')

• The user's life cycle is heavily influenced by users who only buy once (can be excluded)
• The average user spends 134 days, and the median is only 0 days

user_life["差值"]=(user_life["max"] - user_life["min"])
user_life=user_life.reset_index()
user_life.head()

image.png

#export to database
user_life.to_sql('user_life',con=save_engine,if_exists='replace')

3.2.4 Repurchase in one month proportion and repurchase proportion analysis

a.Repurchase in one month proportion
-purchase more than one time in one month

b.Repurchase proportion
-purchase more than one time in whole time

# Distinguish one  from more than one  in order to calculate the repurchase rate, greater than 1 is 1, equal to 0 is 0, equal to 1 is 0
purchase_r=pivoted_counts.applymap(lambda x: 1 if x>1 else np.NaN if x==0 else 0)
purchase_r_reshop = (purchase_r.sum()/purchase_r.count()).reset_index(name = 'reshop')
purchase_r_reshop['create_year_month'] = purchase_r_reshop['create_year_month'].astype(str)
purchase_r_reshop

image.png

The repurchase in one month proportion remained stable at 20%. In the first five months, due to the influx of a large number of new users who only purchased once, the repurchase in one month proportion decreased dramatically

# You need to use a function to determine whether a repurchase is possible: users who spend money in one month also spend money in the next month are called repurchase, and this definition can be changed
def purchase_back(data):
    '''To determine whether each month is a repurchase, based on whether the purchase was made last month, the purchase made last month is not a repurchase if the purchase is not made next month'''
    status=[]
    for i in range(14):
        if data[i]==1:
            if data[i+1]==1:
                status.append(1)
            if data[i+1]==0:
                status.append(0)
        else:
            status.append(np.NaN)
    # The 15th month filled NaN
    status.append(np.NaN)
    return status
indexs = df_purchase.columns
indexs=df_sales_customer_drop['create_year_month'].sort_values().astype('str').unique()
purchase_b = df_purchase.apply(lambda x :pd.Series(purchase_back(x),index = indexs),axis =1)
purchase_b_backshop = purchase_b.sum()/purchase_b.count()
purchase_b_backshop.index = purchase_b_backshop.index.astype(str)
purchase_b_backshop=pd.DataFrame(purchase_b_backshop)
purchase_b_backshop

image.png

#export to database
purchase_r_reshop.to_sql('purchase_r_reshop',con=save_engine,if_exists='replace')

4.Sales analysis of hot products in March, 2021

gather_customer_order_3=gather_customer_order[gather_customer_order['create_year_month']=='2021-03']

TOP 10 product item and sales quantity comparision between Feb and Mar

customer_order_3_top10=gather_customer_order_3.groupby('product_name').agg({'order_num':'sum'}).reset_index()\
.sort_values('order_num',ascending=False)
customer_order_3_top10=customer_order_3_top10.head(10)
customer_order_3_top10

image.png

customer_order_month_2_3 = gather_customer_order_month_2_3[['create_year_month','product_name','order_month_product','cpzl_zw','order_num_diff']]
customer_order_month_2_3['cpzl_zw']=customer_order_month_2_3['cpzl_zw'].fillna('公路自行車(chē)')
customer_order_month_2_3=customer_order_month_2_3[customer_order_month_2_3['product_name'].isin\
                                                     (customer_order_3_top10['product_name']).tolist()]
customer_order_month_2_3['category']='本月TOP10銷(xiāo)量'
#Axis = 0 merges by row dimension, axis = 1 merges by column dimension
hot_products_3 = pd.concat([customer_order_month_2_3,customer_order_month_3_top10_seep],axis = 0)

image.png

image.png