每個產品類別的特性比較

ggplot(ProductSell,aes(x=product_category_name_english,y=price))+
  geom_boxplot()+
  scale_y_log10()

因為Review是針對一筆訂單，而訂單可能包含多項商品，我們不知道評論究竟是針對訂單中的哪一個產品，因此篩選出只有 買一個商品的訂單，可以確保評論的星等是針對此商品。

篩選出只有購買一個商品的訂單

One_product = Item %>%
        group_by(order_id) %>% 
        summarise(product_num = n_distinct(order_item_id)) %>% 
        filter(product_num == 1)

以產品類別做分組 計算出平均單價、總收益、商品總賣出數、有多少賣家賣出此種產品類別

X = ProductSell %>%
    filter(order_id %in% One_product$order_id) %>%  #篩選出只有購買一個商品的訂單
    group_by(product_category_name_english) %>% 
    summarise(
      avg_price = mean(price), #平均單價
      total_rev = sum(price), #總收益
      total_sold_num = n(),　#商品總賣出數
      total_seller_num = n_distinct(seller_id) #有多少賣家賣出此種產品類別
    ) %>% mutate()

以產品類別做分組 計算出總評論數量、平均評論分數

ProductReview = ProductSell %>%
                filter(order_id %in% One_product$order_id) %>% 
                left_join(Reviews) #保留那些沒有評論的商品

Joining, by = "order_id"

vis = ProductReview %>% 
  group_by(product_category_name_english) %>%
  summarise(
    total_review_num = n(), #評論數量
    avg_review_score = mean(review_score)) %>% 
  merge(X) %>% 
  mutate(dummy = 2018)

google motion chart

op <- options(gvis.plot.tag='chart')
options(op)
vistmp = gvisMotionChart(
  vis, "product_category_name_english", "dummy",
  options=list(width=800, height=600, title="Product category"))

print(vistmp, file="M.html")

includeHTML("./M.html")

每個賣家所在州的特性比較

X = ProductSell %>%
    filter(order_id %in% One_product$order_id) %>%  #篩選出只有購買一個商品的訂單
    group_by(seller_state) %>% 
    summarise(
      avg_price = mean(price),
      total_rev = sum(price),
      total_sold_num = n(),
      total_seller_num = n_distinct(seller_id),
      total_cust_num = n_distinct(customer_unique_id)
    )

vis = ProductReview %>% 
  group_by(seller_state) %>%
  summarise(
    total_review_num = n(), #評論數量
    avg_review_score = mean(review_score)) %>% 
  merge(X) %>% 
  mutate(dummy = 2018)

op <- options(gvis.plot.tag='chart')
options(op)
vistmp = gvisMotionChart(
  vis, "seller_state", "dummy",
  options=list(width=800, height=600, title="Product category"))

print(vistmp, file="M.html")
includeHTML("./M.html")

地理繪圖

將州名縮寫對照到全名

abbrBZ = read.csv("state.csv")

vis = merge(vis,abbrBZ,by.x = "seller_state",by.y = "ABBR")
#vis$State_name = paste(vis$State_name,vis$seller_state,sep = "-")

GeoStates <- gvisGeoChart(vis, 
                          locationvar = "State_name",
                          
                          colorvar = "total_sold_num", 
                          sizevar = "total_cust_num",
                          
                          #hovervar = c("avg_review_score","total_seller_num"),
                          hovervar = "seller_state",
                          options=list(region="BR",
                                       displayMode="regions", 
                                       resolution="provinces",
                          width=800, height=600))

print(GeoStates, file="geo.html")

includeHTML("./geo.html")

分群：以賣家銷售資料對賣家做分群　推測其產業類別

在marketing funnel中的Deal資料集中 有business_segment欄位說明賣家屬於哪個產業類別 但只有提供377多個商家有business_segment

X = merge(Deal,ProductSell)
length(unique(X$seller_id))

[1] 377

原本的business_segment有幾種類別

levels(Deal$business_segment)

 [1] ""                                "air_conditioning"               
 [3] "audio_video_electronics"         "baby"                           
 [5] "bags_backpacks"                  "bed_bath_table"                 
 [7] "books"                           "car_accessories"                
 [9] "computers"                       "construction_tools_house_garden"
[11] "fashion_accessories"             "food_drink"                     
[13] "food_supplement"                 "games_consoles"                 
[15] "gifts"                           "handcrafted"                    
[17] "health_beauty"                   "home_appliances"                
[19] "home_decor"                      "home_office_furniture"          
[21] "household_utilities"             "jewerly"                        
[23] "music_instruments"               "other"                          
[25] "party"                           "perfume"                        
[27] "pet"                             "phone_mobile"                   
[29] "religious"                       "small_appliances"               
[31] "sports_leisure"                  "stationery"                     
[33] "toys"                            "watches"                        

所有我們嘗試對賣家做分群 並查看每群特性 推測其產業類別

分群的來源資料為 ProductSell:每一筆是一個賣家的商品銷售紀錄
整理出一個dataframe
每一列是一個賣家 欄位是每種產品種類的sold number

Segment = table(ProductSell$seller_id,ProductSell$product_category_name_english) %>% as.data.frame 
Segment = Segment %>% spread(Var2,Freq) #從長表格整理成寬表格
names(Segment)[1] = "seller_id"

Segment

只選取有銷售紀錄的賣家

Segment = Segment[rowSums(Segment[,2:72]) != 0,]

分群找出產業類別

根據賣家銷售紀錄來對賣家做分群，我們對賣家銷售紀錄的處理方法有兩種

mx = as.matrix(Segment[,2:72]) %>% prop.table(margin=1)

hc = dist(mx) %>% hclust(method="ward.D2")
# 用"binary"的方法來計算距離

plot(hc)

kg = cutree(hc, k=20)

cluster = as.data.frame(table(kg))


ggplot(cluster,aes(x=reorder(kg, -Freq), y = Freq)) + 
    geom_bar(stat="identity")+
    labs(x="第幾群",y="群內賣家數量")

mx = as.data.frame(mx)
mx$group = factor(cutree(hc, k=20))

D = mx %>% 
  group_by(group) %>% 
  summarise_each(funs(mean))

Warning: funs() is soft deprecated as of dplyr 0.8.0
please use list() instead

# Before:
funs(name = f(.)

# After: 
list(name = ~f(.))
This warning is displayed once per session.

D[,2:72] %>% 
  as.matrix %>%
d3heatmap(col=colorRampPalette(c('black','darkblue','blue','green','yellow','orange','red'))(999))

X = ProductSell %>%
  group_by(seller_id) %>%
  summarise(
    total_sold_num = n(),
    total_rev = sum(price),
    avg_freight = mean(freight_value),
    prod_num = n_distinct(product_id),
    avg_photo_qty = mean(product_photos_qty)
  )

Leader = cbind(X,mx[,"group"])
names(Leader)[7] = "group"

ggplot(Leader,aes(x=total_sold_num,y=total_rev))+
  geom_point()+
  facet_wrap(.~group)