当前位置：首页 > 新闻资讯 > 英国礼品电商销售数据分析（原创）礼品数量统计表，礼品数量统计表怎么做出来的，宣传品礼品，礼品统计表格模板，
英国礼品电商销售数据分析（原创）礼品数量统计表，礼品数量统计表怎么做出来的，宣传品礼品，礼品统计表格模板，
作者：jizhicms2024-02-20 18:36
点赞
收藏
热度：738
一、分析背景与目的
该数据集由The UCI Machine Learning Repository提供的，英国在线零售商在2010年12月1日到2011年12月9日的在线零售数据，该电商公司主要以销售各类礼品为主，很多客户都是批发商。本文将从整体消费情况、客户、产品、区域四个维度对销售数据进行分析，以求挖掘销售增长点，从而实现进行精细化、数据化运营。
二、分析思路
三、数据理解
1.数据来源：The UCI Machine Learning Repository
2.时间节点：2010年12月1日到2011年12月9日
3.数据大小：541909条数据，8个字段
4.字段含义：
InvoiceNo：订单号，包含‘C’字母的为退单
StockCode：货品号
Description：货品描述
Quantity：数量
InvoiceDate：订单时间
UnitPrice：单价，以英镑为单位
CustomerID：用户账号
Country：国家
三、数据清洗
1.去除重复值
select count(distinct * )
from uk_onlineretail
查询后得到结果如下
去重后查询后数据只有536641条，表明原表中有5281条重复值，需进行去重处理
create table tmp --创建临时表
select distinct *
from uk_onlineretail
drop table uk_onlineretail; --删除旧表alter table tmp rename to uk_onlineretail; - - 把新表名重命名为旧表的名称
2.缺失值处理
select
sum(case when InvoiceNo is null then 1 else 0 end )as发票编号,
sum(case when StockCode is null then 1 else 0 end )as产品编号,
sum(case when Description is null then 1 else 0 end )as产品描述,
sum(case when Quantity is null then 1 else 0 end )as数量,
sum(case when InvoiceDate is null then 1 else 0 end )as日期,
sum(case when UnitPrice is null then 1 else 0 end )as单价,
sum(case when CustomerID is null then 1 else 0 end )as 客户编号,
sum(case when Country is null then 1 else 0 end )as国家
from uk_onlineretail
产品描述列缺失值1454条，但本次分析与产品描述列无关因此无需处理；客户编号列有135037条缺失值，数据量很大，且用户分析需用到客户编号列。在实际工作中，像客户编号这样有大量数据缺失的重要字段，首先找业务部门或者数据来源部门确认补上信息，在此处智能全部删除缺失值以保证不影响后续的用户分析。
delete from uk_onlineretail where CustomerID is null; -- 删除客户编号缺失的数据
3.异常值处理
1)判断时间是否有异常
--时间异常值判断
select min(InvoiceDate),max(InvoiceDate)
from uk_onlineretail
时间都在数据集规定的范围内，无异常
2)判断购买数量是否有异常
select *
from uk_onlineretail
where Quantity<=0
进一步判断购买数量小于等于0的订单是否全是退货订单。
select *
from uk_onlineretail
where Quantity>0 and InvoiceNo like C%
经判断发现，不存在购买量大于0且为退货的订单；因此可将原表分成两成两支笔，分布是成功下单表和取消下单表。
create table cancel_order
select *
from uk_onlineretail
where Quantity<=0 --生成cancel_order表
delete from uk_onlineretail where Quantity<=0
alter table uk_onlineretail rename to success_order --生成success_order表
4)判断单价是否有异常值
select *
from success_order
where UnitPrice<=0
有40条数据的单价小于等于0，需进行删除。
delete from success_order where UnitPrice<=0 --删除单价列小于0的数据
5）一致化处理
success_order表只有单价和数量，缺少总消费金额，所以需要添加一列；此外，由于分析过程需要用到月与天，因此需要对时间列进行格式化。
alter table success_order add column Amounts float(100,2) not null -- 插入新的一列
UPDATE success_order set Amounts =Quantity*UnitPrice -- 求交易金额
alter table success_order add column date varchar(255) not null -- 插入新列
update success_order set date=DATE_FORMAT(InvoiceDate,%Y-%m-%d) -- 在新列中中转换更新日期数据
alter table success_order add column month varchar(255) not null -- 插入新列
update success_order set month=DATE_FORMAT(InvoiceDate,%Y-%m) --时间转化为月
alter table success_order add column month1 varchar(255) not null -- 插入新列
update success_order set month1=DATE_FORMAT(InvoiceDate,%Y-%m-01) --时间转化为月
由于需要按月求退单率，因此cancel_order 表缺少月分列，需进行添加。
alter table cancel_order add column month varchar(255) not null
update cancel_order set month=DATE_FORMAT(InvoiceDate,%Y-%m)
四、数据分析
1.每月总体销售情况的分析
1)每月的总销售、商品销售数量、购买人数、总订单数、客单价、笔单价、连带率
create table sales_month
select month as 时间,month1 as 日期,sum(Amounts)as 总销售额,
count(StockCode) as 购买商品总数,
count(DISTINCT CustomerID)as 购买人数,
count(DISTINCT InvoiceNo) as 总订单数,
round(sum(Amounts)/count(DISTINCT CustomerID),2) as 客单价,
round(sum(Amounts)/count(DISTINCT InvoiceNo),2) as 笔单价,
round(count(StockCode)/count(DISTINCT InvoiceNo),2) as 连带率
from success_order
group by month
order by month
销售额短暂下降后又逐渐上升，在11月达到最高，12月的数据仅包含9天但也已经达到517190.43英镑，推测与即将的圣诞节有关。客单价、笔单价与连带率虽有所波动，但差距不大，较为稳定。
2)每月的新老客户数
由于数据集的时间是从2010年12月开始的，每有之前的数据，因此将在2010年12月进行购买的客户定义为新客户。
--求出每月新客户数create view t1
as
select month as 时间,count(distinct CustomerID) as 新客户数
from
(select CustomerID,min(month) as month
from success_order
group by CustomerID)a
group by month
--与sales_month表连接，求出每月的新老客户数
create table month_new_old_people
select sales_month.时间,sales_month.购买人数,
t1.新客户数,sales_month.购买人数-t1.新客户数 as 老客户数
from sales_month join t1
on sales_month.时间=t1.时间
3)每月新老客户的销售额
--求出每月新客销售额
create view t2
as
select c.month as 时间,sum(c.Amounts) as 新客户销售额
from
(select a.*
from
(success_order as a
join
(select CustomerID,min(month) as month
from success_order
group by CustomerID) b
on a.month=b.month and a.CustomerID=b.CustomerID)) c
group by c.month
--两表连接，求出每月新老客户的销售额create table month_new_old_amounts
select sales_month.时间,sales_month.总销售额,
t2.新客户销售额,sales_month.总销售额-t2.新客户销售额 as 老客户销售额
from sales_month join t2
on sales_month.时间=t2.时间
4)每月复购率
--求出每月复购人数
create view t3
AS
select month as 时间,count(distinct CustomerID) as 复购人数
from
(select month,CustomerID,count(distinct InvoiceNo)
from success_order
group by month,CustomerID
having count(distinct InvoiceNo)>1) a
group by month
--两表连接，求出每月复购率
create table mont_fg
select sales_month.时间,sales_month.购买人数,
t3.复购人数,round(t3.复购人数/sales_month.购买人数,2) as 复购率
from sales_month join t3
on sales_month.时间=t3.时间
每月的复够率均20%-30%的范围内，11月份由于销售额、订单数最高，因此复够率也最高。
4)每月退单率
--求出每月退单数
create view t4
AS
select month as 时间,count(distinct InvoiceNo) as 退单数
from cancel_order
group by month
--两表连接，求出每月退单率
select sales_month.时间,sales_month.总订单数,
t4.退单数,round(t4.退单数/sales_month.总订单数,2) as 退单率
from sales_month join t4
on sales_month.时间=t4.时间
每月的退单率均在10%-30%的范围内，前期退单率较高，而后可能因为售前环节的改进，使得退单率有所下降。
2、客户分析
1）客户的生命周期
--客户的生命周期
create table customer_life_cycle
select CustomerID as 客户编号,min(date) as 第一次消费,max(date) as 最后一次消费,
DATEDIFF(max(date),min(date)) as 生命周期
from success_order
group by CustomerID
用直方图绘制用户生命周期，即可看出不同时间段用户生命周期的分布频率，从而可判断出大多数客户的生命周期分布在哪个时间段。
2)客户的购买周期
--客户的购买周期
create view d
as
select CustomerID,date
from success_order
group by CustomerID,date
order by CustomerID,date
--在不同天进行消费的客户
create view d1
as
select CustomerID as 客户编号,round(avg(datediff),0) as 平均每两次购买间隔时间
from
(select d1.*,d2.date as date1,datediff(d2.date,d1.date) as datediff
from d as d1 join d as d2
on d1.CustomerID=d2.CustomerID and d1.date<d2.date
group by d1.CustomerID,d1.date) a
group by CustomerID
--仅在一天内消费的客户create view d2
as
select CustomerID as 客户编号,0 as 平均每两次购买间隔时间
from success_order
where CustomerID
not in (select d1.CustomerID
from d as d1 join d as d2
on d1.CustomerID=d2.CustomerID and d1.date<d2.date
group by d1.CustomerID)
group by CustomerID
order by CustomerID
--两表合并,求出所有用户的生命周期
create table customer_buy_cycle
select d1.* from d1
union
select d2.* from d2
用直方图绘制客户购买周期，即可看出不同时间段客户购买周期的分布频率，从而可判断出大多数客户的平均每两次购买间隔时间分布在哪个时间段。
3)RFM分析
RFM模型是衡量客户价值和客户创利能力的重要工具和手段。通过一个客户的近期购买行为、购买的总体频率以及花了多少钱三项指标来描述该客户的价值状况。
R：最近一次交易时间（最近一次消费到参考时间的间隔）
F：交易次数（消费了多少次）
M：交易金额（总消费金额）
按分析目的，对数据进行分类汇总：最近一次交易时间（最近一次交易到参考时间的间隔--天数差）、交易次数、交易金额。
create view rfm
as
select CustomerID as 客户编号,Country as 国家,MAX(date) as 最后一次交易时间,datediff(2011-12-09,MAX(date))as 天数差,
count(distinct InvoiceNo)as交易次数,
sum(Amounts)as交易金额
from success_order
group by CustomerID
order by 天数差 desc,交易金额 desc ,交易次数 desc
对rfm表中的数据进行评分
create view rfm1
as
select *,
(case when 天数差<=30 then 5
when 天数差 >30 and 天数差 <=90 then 4
when 天数差>90 and 天数差<=180 then 3
when 天数差>180 and 天数差<=365 then 2 else 1 END)as R评分,
(case when 交易次数<=10 then 1
when 交易次数 >10 and 交易次数 <=30 then 2
when 交易次数>30 and 天数差<=50 then 3
when 交易次数>50 and 交易次数<=80 then 4 else 5 END)as F评分,
(case when 交易金额<=1000 then 1
when 交易金额 >1000 and 交易金额 <=3000 then 2
when 交易金额>3000 and 交易金额<=5000 then 3
when 交易金额>5000 and 交易金额<=8000 then 4 else 5 END)as M评分
from rfm;
将R评分、F评分和M评分三个评分的平均值作为定义R值、F值、M值的阈值。
select round(avg(R评分),1)as R平均值,
round(avg(F评分),1)as F平均值,
round(avg(M评分),1)as M平均值
from rfm1;
create view rfm2
as
select *,R评分+F评分+M评分 as 总评分,case when R评分>3.8 then 1 else 0 end as R值,
case when F评分>1.1 then 1 else 0 end as F值,
case when M评分>1.6 then 1 else 0 end as M值
from rfm1
根据RFM值和客户分层表、对客户行为进行分层处理。
create view rfm3
as
select *,
case when R值=1 and F值=1 and M值=1 then 重要价值客户
when R值=0 and F值=1 and M值=1 then 重要唤回客户
when R值=1 and F值=0 and M值=1 then 重要深耕客户
when R值=0 and F值=0 and M值=1 then 重要挽留客户
when R值=1 and F值=1 and M值=0 then 潜力客户
when R值=1 and F值=0 and M值=0 then 新客户
when R值=0 and F值=1 and M值=0 then 一般维持客户
else 流失客户 end as 客户分层
from rfm2;
3、区域分析
分析各个国家的销售额、购买人数、总订单数、客单价来分析各个城市的销售业绩与消费水平、消费特征。
create table country_amounts
select Country as 国家,sum(Amounts) as 销售额,count(DISTINCT CustomerID)as 购买人数,
count(DISTINCT InvoiceNo) as 总订单数,
round(sum(Amounts)/count(DISTINCT CustomerID),2) as 客单价
from success_order
group by Country
4、产品分析
1)商品ABC分析
统计总共销售额
销售额
select sum(Amounts)
from success_order
--计算不同产品的销售额、累积销售额及占比set @csum :=0
create table p1
select b.*,round(b.累积销售额/8887208.88,2) as 累积销售额占比
from
(select a.*,round((@csum :=@csum+a.销售额),2) as 累积销售额
from
(select StockCode as 产品编号,sum(Amounts) as 销售额
from success_order
group by StockCode
order by sum(Amounts) desc) a ) b
--按照占比，进行产品的ABC划分
create table product_amounts
select *,
(case when 累积销售额占比<=0.7 then A
when 累积销售额占比>0.7and 累积销售额占比<=0.9 then B
ELSE C end) as 产品分类
from p1
统计A、B、C三类产品的销售额
select 产品分类,sum(销售额) as 销售额
from product_amounts
group by 产品分类
统计A、B、C三类产品的商品总数
select 产品分类,count(distinct 产品编号) as 产品总数
from product_amounts
group by 产品分类
2)各产品退货率分析
create table product_tuihuo
SELECT a.*,b.销售数量,round(a.退货数量/b.销售数量,2) as 退货率
from
(select StockCode as 产品编号,sum(abs(Quantity)) as 退货数量
from cancel_order
group by StockCode) a left join
(select StockCode as 产品编号,sum(Quantity) as 销售数量
from success_order
group by StockCode) b
on a.产品编号=b.产品编号
order by 退货率 desc
3)商品单价与销售额关联度分析
create table product_UnitPrice_Amounts
select UnitPrice as 产品单价,sum(Amounts) as 销售金额
from success_order
group by UnitPrice
order by sum(Amounts) desc
可用直方图绘制商品单价与销售额，即可看出不同单价段段客户销售额的总数，从而可判断出位于哪个单价段的商品的销售贡献度最大。
select sum(销售金额)
from product_unitprice_amounts
where 产品单价<=10
单价小于10的产品的销售额达到了8220562.97英镑，是销售额最重要的来源。
五、数据可视化
通过将powerbi连接数据库，导入上述分析创建的表格，建立可视化动态报表如下：
https://app.powerbi.com/reportEmbed?reportId=793d45ea-0c59-481c-ac6a-f8df3efded09&autoAuth=true&ctid=5a7a259b-6730-404b-bc25-5c6c773229ca&config=eyJjbHVzdGVyVXJsIjoiaHR0cHM6Ly93YWJpLXNvdXRoLWVhc3QtYXNpYS1yZWRpcmVjdC5hbmFseXNpcy53aW5kb3dzLm5ldC8ifQ%3D%3D
总体销售情况
用户分析
商品分析
趋势分析
六、结论
销售情况：
结论：11月销售额最高，推测12月销售数据将达到一个新的高峰，这可能与即将到来的圣诞节有关。
后续改进：1)打造圣诞节有关的活动，提供产品折扣，吸引新的潜在用户，活跃固有用户，从而提高销售量。
用户维度：
结论1：大部分用户的生命周期不到两个月，时间较短；大部分用户的平均两次购买间隔时间不到两个月，购买周期较短。
后续改进：1）根据每位用户的平均两次购买间隔时间，已发送信息的方式提醒客户进行下次消费，并可提供一定的优惠刺激客户进行购买，以此提高客户的忠诚度，延长生命周期。
结论3：通过rfm模型，对每位客户进行定义，从而进行更为精细化的运营
后续改进：1)重要价值客户：倾斜更多资源，VIP服务，个性化服务及附加销售；2)唤回客户：DM营销，提供有用的资源，可以通过续订/更新产品来挽回他们；3)重要深耕客户：提供会员，积分等计划，推荐其他相关产品；4)重要挽留客户：重点联系或者拜访，提高留存率；5)潜力客户：向上销售价值更高，更有用的产品；6)新客户：社群会员活动，提供免费的试用，提高客户的兴趣；7)一般维持客户：积分会员制，分享宝贵的资源，以折扣推荐热门产品，与他们重新联系；8)以促销折扣等方式恢复客户兴趣，否则暂时放弃无价值的客户。
区域维度：
结论：英国地区的销售额最高，为主要的消费地区，另有部分地区存在大批量订货的客户。
后续改进：绝大多数的客户是来自英国本土，需做到提前备货，节省调度；同时监控库存，防止断货。其他国家的客户较少，但是却多为优质客户，可以对这些客户进行深挖予以物流上面的支持
产品维度:
1.占到销售额70%的A类商品有525件，然而只占销售额10%的C类产品数量高到2377件，产品结构设置不合理。
后续改进：1)畅销商品过少，需加大畅销商品的开发力度，找到满足我们目标客户群体需求的商品；2)加大A类商品的安全库存量，降低缺货，提升销售额；3)对C类商品进行品类分析，淘汰不能满足顾客需求、重复性的商品，做最佳商品组合。
2.部分产品的退货率很高，甚至有些产品销售出去后全部退回，降低了销售额，需重点分析。
后续改进：1)了解退货率高的产品的退货原因，进行有目的的改进，或者是直接进行淘汰；2)确保产品质量，在销售网站页面上做到产品真实描述、使用高质量的图片和视频，发货前检查每件商品，提供优质的售前服务来减少产品的退货率。
3.客户群体偏向于购买低价产品，单价小于10的产品为销售的主力军。
后续改进：1)增加投入低价产品；2)进行低价产品的组合销售，进一步提高销售额。
