優衣庫-銷售服裝-分析案例實戰（BI-SQL-Python-Excel）

本篇文章和網上最主要的差別是分别(使用BI)（使用MYSQL）(使用Python)（使用Excel）分别進行資料分析。

文章目錄

• 一、BI(可視化展現（可PC端和移動端）)
• 二、(MYSQL版本)資料字段說明、建立、導入資料庫、多元度字段查詢分析。
• • 1.描述性統計
  • 2檢視線上，各城市的産品類别，總客戶數量，總銷售金額，總訂單數量，總購買的産品數量，總産品的成本，按銷售金額，降序排序。
  • 3.重新計算公式（再次生成新表，增加四個字段）
  • 4.SQL查詢-多元度-資料分析
  • • 1.不同城市的顧客通過線上或線下購買的人數，并按照顧客數降序
    • 2.不同性别的顧客通過線上或線下購買的人數,并按照顧客數降序
    • 3.不同年齡段的顧客通過線上或線下購買的人數，,并按照顧客數降序
    • 4.不同城市銷售管道的顧客的平均購買金額對比，并降序
    • 5.不同城市性别的顧客的平均購買金額對比，并降序
    • 6.不同年齡段的顧客的客戶數和平均購買力度對比，購買力度降序排序
    • 7.不同城市的年齡段的顧客的總客戶數，平均購買金額，總利潤，并按城市、總利潤，依次降序。
    • 8.探究銷售情況與購買時間的關系（周末與工作日的下單人數對比）
    • 9.不同産品的銷售情況及利潤高低
• 三、Python資料分析
• • 1.看了描述統計，發現銷售金額revenue有負數
  • 2.由于銷售收入和零收入存在較少，可以直接删除
  • 3.檢視資料是都有重複
  • 4.檢視是否有缺失值
  • 5對城市和銷售管道進行分組，對顧客數求和并重置索引
  • 6.不同城市的顧客通過線上或線下購買的人數對比（深紅色：city；橙色：customer）：
  • 7.銷售額随時間變化
  • 8.銷售收入占比
  • 9.顧客随着時間的變化
  • 10.客單價随着時間的變化
  • 11.各時間段利潤及單價的情況
  • 12.我們在檢視不同産品銷售情況描述統計
  • 13.不同産品的銷售情況可視化
  • 14.産品銷量占比
  • 15.産品利潤占比
  • 16.不同性别偏愛購買方式
  • 17.不同城市偏愛購買方式
  • 18.不同年齡階段偏愛的購買方式
  • 19.銷售額與産品成本之間的關系
  • 20.産品邊際利潤情況
  • 21.檢視産品成本與銷售額的相關關系
• 四、Excel
• 總結和建議

一、BI(可視化展現（可PC端和移動端）)

以下四張圖是上下拼一起的，資料可以關聯（在第一張小圖那裡篩選條件，有不同的元件）

這是導出Excel看闆的圖檔：

二、(MYSQL版本)資料字段說明、建立、導入資料庫、多元度字段查詢分析。

資料字段:

store_id 門店随機編号id,無實際意義

city 門店所在城市

channel 門店所産生的銷售管道，線上表示網上購買到門店自提，線下表示門店直接購買

gender_group 客戶性别 男女

age_group 客戶年齡段

wkd_ind 購買發生的時間（周末，周中）

product 産品類别

customer 客戶數量

revenue 銷售金額

order1 訂單數量

quant 購買的産品數量

unit_cost 産品的成本（包含制造和營銷層面）

建好表後，看看資料類型：

DESCRIBE users;
-- 或者 DESC users;
-- 或者 SHOW COLUMNS FROM users;
           

導資料：

因資料量，太大，先看看，前10行的資料：

1.描述性統計

客戶數量:

SELECT MIN(DISTINCT customer) AS 客戶數量最小值, MAX(DISTINCT customer) AS 客戶數量最大值
	, AVG(customer) AS 客戶數量平均值
FROM users
WHERE customer > 0;
           

銷售金額:

SELECT MIN(DISTINCT revenue) AS 銷售金額最小值, MAX(DISTINCT revenue) AS 銷售金額最大值
	, AVG(revenue) AS 銷售金額平均值
FROM users
WHERE revenue > 0;
           

購買的産品數量:

SELECT MIN(DISTINCT quant) AS 購買的産品數量最小值, MAX(DISTINCT quant) AS 購買的産品數量最大值
	, AVG(quant) AS 購買的産品數量平均值
FROM users
WHERE quant > 0;
           

産品的成本:

SELECT MIN(DISTINCT unit_cost) AS 産品的成本最小值, MAX(DISTINCT unit_cost) AS 産品的成本最大值
	, AVG(unit_cost) AS 産品的成本平均值
FROM users
WHERE unit_cost > 0;
           

訂單數量:

SELECT MIN(DISTINCT order1) AS 訂單數量最小值, MAX(DISTINCT order1) AS 訂單數量最大值
	, AVG(order1) AS 訂單數量平均值
FROM users;
           

2檢視線上，各城市的産品類别，總客戶數量，總銷售金額，總訂單數量，總購買的産品數量，總産品的成本，按銷售金額，降序排序。

SELECT city, product, SUM(customer)
	, SUM(revenue), SUM(order1)
	, SUM(quant), SUM(unit_cost)
FROM users
WHERE channel = '線上'
GROUP BY city, product
ORDER BY SUM(revenue) DESC;
           

3.重新計算公式（再次生成新表，增加四個字段）

#利用公式法，我們觀察每單顧客的總銷售額為revenue，根據數量quant計算出單價産品銷售金額

#（平均銷售額）unit_price = revenue/quant

#（客單價）revenue_pc = revenue/customer

#（毛利，單價利潤）margin_profit=unit_price-unit_cost

#（總利潤）profit=(unit_price-unit_cost)*quant

CREATE TABLE youyiku_Sales
AS
SELECT store_id AS 門店ID, city AS 城市, channel AS 銷售管道, gender_group AS 客戶性别分組, age_group AS 客戶年齡段
	, wkd_ind AS 購買時間周末或周中, product AS 産品類别, customer AS 客戶數量, revenue AS 銷售金額, order1 AS 訂單數量
	, quant AS 購買的産品數量, unit_cost AS 産品的成本, revenue / quant AS 平均銷售額
	, revenue / customer AS 客單價
	, revenue / quant - unit_cost AS 毛利
	, (revenue / quant - unit_cost) * quant AS 總利潤
FROM users;
           

生成新的子表後，再次檢視資料情況，因資料過多，先檢視前10條；

把無用記錄，删掉。

還記得怎麼删除嘛（drop、truncate和delete的差別）？

（三）SQL基礎進階 第2點

4.SQL查詢-多元度-資料分析

1.不同城市的顧客通過線上或線下購買的人數，并按照顧客數降序

2.不同性别的顧客通過線上或線下購買的人數,并按照顧客數降序

3.不同年齡段的顧客通過線上或線下購買的人數，,并按照顧客數降序

4.不同城市銷售管道的顧客的平均購買金額對比，并降序

5.不同城市性别的顧客的平均購買金額對比，并降序

按城市、平均購買力順序，優先排序

按平均購買力、城市順序，優先排序：

6.不同年齡段的顧客的客戶數和平均購買力度對比，購買力度降序排序

7.不同城市的年齡段的顧客的總客戶數，平均購買金額，總利潤，并按城市、總利潤，依次降序。

8.探究銷售情況與購買時間的關系（周末與工作日的下單人數對比）

不同城市購買時間，的客戶數量、平均銷售額、總利潤、銷量情況

9.不同産品的銷售情況及利潤高低

三、Python資料分析

#導入包
import pandas as pd
import numpy as np
import random
from matplotlib import pyplot as plt
from pyecharts import options as opts
from pyecharts.charts import Bar
from pyecharts.charts import Pie
from pyecharts.charts import Boxplot
from pyecharts.globals import ThemeType as t
import seaborn as sns
import matplotlib
%matplotlib inline
plt.rcParams['font.sans-serif'] = ['SimHei']  #設定中文
plt.rcParams['axes.unicode_minus'] = False
pd.set_option("display.max_column", None)
pd.set_option("display.max_row", None)
           

df = pd.read_csv(r'C:\Users\xxxxxxx\UNQ_DATA.csv')
df.head()
           

#檢視資料行列
df.shape
           

#描述性分析
df.describe()
           

1.看了描述統計，發現銷售金額revenue有負數

2.由于銷售收入和零收入存在較少，可以直接删除

df = df[df['revenue']>0]
df.describe()
           

3.檢視資料是都有重複

#inplace=True表示直接在源資料上進行操作
#重置索引reset_index
df.drop_duplicates(inplace=True)
df.reset_index(inplace=True, drop=True)
df.head()
           

4.檢視是否有缺失值

5對城市和銷售管道進行分組，對顧客數求和并重置索引

#對城市和銷售管道進行分組，對顧客數求和并重置索引
city_df1 = pd.DataFrame(df.groupby(['city', 'channel'
                                    ])['customer'].sum()).reset_index()
city_df1
           

6.不同城市的顧客通過線上或線下購買的人數對比（深紅色：city；橙色：customer）：

#對配色進行設定
sns.set(palette='RdBu')
#将已經聚合好的資料做可視化
plt.figure(figsize=(10, 6))
sns.barplot(x='city', y='customer', hue='channel', data=city_df1)
plt.title('不同城市的顧客通過線上或線下購買的人數對比', fontproperties=font)
plt.xlabel('城市', fontproperties=font)
plt.ylabel('顧客數量', fontproperties=font)
           

7.銷售額随時間變化

plt.figure(figsize=(10,8))
plt.style.use('ggplot')
df.groupby('wkd_ind')['revenue'].sum().plot.bar()
plt.title('銷售額随時間變化',fontproperties=font)
           

8.銷售收入占比

#銷售收入占比
data = df.groupby('wkd_ind')['revenue'].sum()
plt.pie(data,
        autopct='%.0f%%',
        shadow = True,
        textprops = {'fontsize':11},
        explode = [0.08,0])
plt.axis('equal')
plt.legend(['Weekday','Weekend'])
plt.title('各個時間段銷售額占比',fontproperties=font)
plt.show()
           

從圖表中可以看出銷售額revenue在weekday收入較高，占了59%。

9.顧客随着時間的變化

plt.figure(figsize=(10,8))
sns.barplot(x='wkd_ind',y='customer',data=df)
plt.title('各個時間客戶總數量情況',fontproperties=font)
           

大多顧客在周中weekday購買較多

10.客單價随着時間的變化

plt.figure(figsize=(10,8))
df['unit_revenue']=df['revenue']/df['quant']
sns.barplot(x='wkd_ind',y='unit_revenue',data=df)
plt.title('各個時間單價比',fontproperties=font)
           

unit_revenue單價在周末比較高，說明周末價格比較高，導緻周中比較多人前往購買

11.各時間段利潤及單價的情況

#各時間段利潤及單價的情況
plt.figure(figsize=(10,8))
#客單價
df['margin_profit']=(df['unit_revenue']-df['unit_cost'])
sns.barplot(x='wkd_ind',y='margin_profit',data=df)
plt.title('各個時間段利潤及單價情況',fontproperties=font)
           

利潤也是在周末占的比較高

#檢視不同産品銷售情況描述統計
uniqlo1.groupby('product')['revenue'].describe()
           

12.我們在檢視不同産品銷售情況描述統計

13.不同産品的銷售情況可視化

14.産品銷量占比

data1 = df.groupby('product')['revenue'].sum()
plt.pie(data1,
        labels=data1.index,
        autopct='%.f%%',
        shadow = True
        )
plt.title('産品銷量占比',fontsize=20)
plt.show()
           

從圖中可以看出，T桖占比最大43%

15.産品利潤占比

data1 = df.groupby('product')['margin_profit'].sum()
plt.pie(data1,
        labels=data1.index,
        autopct='%.f%%',
        shadow = True
        )
plt.title('産品利潤占比',fontsize=20)
plt.show()
           

從圖中可以看出，T桖占比最大43%

16.不同性别偏愛購買方式

#顧客更偏愛那種購買方式
#不同性别
def way(a,b):
    plt.figure(figsize=(12,8))
    data2=df[a].value_counts().index
    sns.countplot(y=a,hue='channel',data=df,order=data2)
    plt.title(b,fontsize=20)
way('gender_group','不同性别偏愛購買方式')
           

從圖中可以看出，女性購物多于男性,大多數都是偏愛線下的購買方式

17.不同城市偏愛購買方式

從圖中可以看出，大多數都是偏愛線下的購買方式，深圳、武漢、、杭州這三個城市銷量最大，深圳跟杭州全是線下，沒有線上消費

18.不同年齡階段偏愛的購買方式

從圖中可以看出，年齡集中在30-34歲

19.銷售額與産品成本之間的關系

20.産品邊際利潤情況

import matplotlib.pyplot as plt
plt.rc("font",family="SimHei",size="22") 
def way1(c,d):
    plt.figure(figsize=(12,8))
    sns.boxplot(x='margin_profit',y=c,data=df)
    plt.title(d,fontproperties=font)

way1('product','銷售産品利潤情況')
           

由圖可以，看出：

當季新品、牛仔褲、及運動為主要促銷産品類别，線下門店可優化産品布局，将利潤較高的新品及類别産品放置于促銷産品附近，以此來提高購買率，線上門店可以優化優惠券金額及産品定價來增加捆綁銷售；在高利潤産品中，配件的市場需求不小，可适當增加庫存來滿足使用者需求；而毛衣及裙子的銷量占比較低，可通過促銷及推廣活動來提升銷量，以此提升銷售利潤水準。

21.檢視産品成本與銷售額的相關關系

可以看出銷售收入與成本相關性不大

四、Excel

先整理再寫，思路是從MYSQL資料庫裡面，取數，生成Excel檔案。再根據這個資料源，做成資料透視表、各種圖表等。

總結和建議

相對于線上購買的方式，顧客更喜歡線下購買的方式；

南京的顧客、女性顧客、年齡段為25-29的顧客更具購買力；

工作日的銷售情況比周末更好；

所有産品中T恤的銷量最高，利潤也最高。

牛仔褲和運動是平均利潤低，成本高的産品，且銷量也不高的産品，應該減少庫存；毛衣、裙子是平均利潤高但銷售低的産品，要想利潤更大化，就要加大宣傳力度提高裙子、毛衣銷量使利潤更大。