电商mysql一般多少条数据_电商用户消费行为数据分析（mysql、excel ）

所需数据：ORDER_INFO_UTF.CSV、USER_INFO_UTF.CSV

提取码:yu63

目录

1.数据的导入

1.1创建表结构

1.1.1创建订单表

2.对数据进行分析

2.1  统计每个月的下单人数

2.2  统计复购率和回购率

2.3  统计消费者性别与消费频次的关系

2.4   3、4月份每日下单人数,每日下单数

2.5  统计消费者性别与平均消费金额的关系

2.6  统计不同年龄段的消费金额的占比

2.7  统计每个时间段的下单人数

2.8  统计消费的二八法则，消费的top20%用户消费额占总消费额的占比

1.导入csv数据

1.1创建表结构

1.1.1创建订单表CREATE TABLE ORDERINFO

(ORDERID VARCHAR(10) NULL,#订单ID，主键

USERID VARCHAR(10) NULL,#用户ID，可以和用户表进行关联

ISPAID VARCHAR(10) NULL,#是否支付

PRINCE VARCHAR(10) NULL,#订单价格

PAIDTIME DATETIME  NULL #订单支付时间)

1.1.2创建用户信息表CREATE TABLE USER_INFO_ULF

(USERID varchar(10) NULL,#用户ID，主键

SEX varchar(10) NULL,#性别

BIRTH DATE NULL #出生日期)

1.1.3为表创建索引，提高查询速度#为user_info_utf创建主键索引

ALTER TABLE `user_info_utf` MODIFY COLUMN userid VARCHAR(10) PRIMARY KEY；

#为表orderinfo创建主键索引

ALTER TABLE `orderinfo` MODIFY COLUMN `ORDERID` VARCHAR(10) PRIMARY KEY；

#为表orderinfo创建外键索引

ALTER TABLE orderinfo ADD FOREIGN KEY(userid) REFERENCES user_info_utf(userid)

1.2导入csv数据

电脑安装了sqlyog，导入使用本地加载的csv数据，速度很快，一两秒就导入成功了

2.数据处理#不对未支付订单进行分析，为提高查询效率，删除未支付订单

DELETE

FROM `orderinfo`

WHERE ispaid='未支付'

#5月份数据只有7条，难以进行分析，故删除5月份数据

DELETE FROM `orderinfo` WHERE paidtime>='2016-05-01'

2.1统计每个月的下单人数SELECT SUBSTRING(paidtime,6,2) '下单月份',COUNT(DISTINCT(`USERID`)) AS '月下单总人数'

FROM`orderinfo`

GROUP BY SUBSTRING(paidtime,1,7)

2.2统计复购率和回购率

2.2.1  3月份复购率SELECT COUNT(*) AS '下单人数', COUNT(IF(pt>1,1,NULL)) AS '重复下单人数', paidmonth AS '月份',(COUNT(IF(pt>1,1,NULL))/COUNT(*)) AS '复购率'

FROM (SELECT userid,COUNT(*) AS pt,MONTH(`PAIDTIME`) AS paidmonthFROM orderinfoGROUP BY userid,paidmonth) AS uc

GROUP BY paidmonth

四月份复购率下降：四月份的下单人数与复购率均下滑，从复购率来看，不是很高，可将提升的方向主要放在新用户的获取。

2.2.1  3月份回购率#创建3月份下单用户id与下单次数的视图

CREATE VIEW 3_userid

AS

SELECT COUNT(*) AS co1,userid

FROM `orderinfo`

WHERE SUBSTRING(paidtime,6,2)='03'

GROUP BY userid;

#创建4月份下单用户id与下单次数的视图

CREATE VIEW 4_userid AS

SELECT COUNT(*) AS co2,userid

FROM `orderinfo`

WHERE  SUBSTRING(paidtime,6,2)='04'

GROUP BY userid;#.四月份复购订单数

SELECT (SELECT COUNT(co2) AS co3 FROM 4_userid)AS '四月份的订单数',COUNT(*) AS '四月份的用户复购订单数',(COUNT(*)/(SELECT COUNT(co2) AS co3 FROM 4_userid)) AS '复购率'

FROM 3_userid

INNER JOIN 4_userid

ON 3_userid.userid=4_userid.userid

2.3  3、4月份每日下单人数,每日下单数#3.4月份每日下单人数,每日下单数

SELECT SUBSTRING(paidtime,6,5) AS '日期',COUNT(DISTINCT(userid)) AS '每日下单人数',COUNT(*) AS '每日下单人数'

FROM orderinfo

GROUP BY SUBSTRING(paidtime,6,5);

#导出sql数据，用excel透视表制作

周日至周四消费者购买数较活跃，周五周六较低迷：周五，周六订单数明显呈下降势，周六下单人数为一周中最少。2016年第15周的星期一为清明节，许多人外出，订单数明显减少。

2.4统计消费者性别与消费频次的关系

思路：建立临时表csu统计每个用户的购买次数及其性别，用avg和group by分组计算不同性别的消费频次SELECT AVG(csu.co2),sex

FROM (SELECT COUNT(o.userid) AS co2,sex,o.userid

FROM `orderinfo` AS o,`user_info_utf` AS u

WHERE o.userid=u.userid AND sex!=' '

GROUP BY o.userid,sex) AS csu

GROUP BY sex

小结：男性与女性平均购买次数差别不大，但三四月份下单总人数未8万多，而下单用户没有性别信息的用户有5万多，所以计算出来的不同性别的平均购买次数参考意义不大

2.5统计消费者性别与平均消费金额的关系

思路：建立临时表csc统计每个用户的购买次数及其性别，用avg和group by分组计算不同性别的平均消费金额

小结：从数据中可看出男性与女性平均购买金额差别不大，但三四月份下单总人数未8万多，而下单用户没有性别信息的用户有5万多，所以计算出来的不同性别的平均购买金额参考意义不大

2.6统计不同年龄段的消费金额的占比SELECT COUNT(*),ROUND(SUM(`PRINCE`),2) AS '总消费额',ROUND(AVG(`PRINCE`),2) AS '平均消费额',

CASE

WHEN aget.age BETWEEN 0 AND 9 THEN '0-9岁'

WHEN aget.age BETWEEN 10 AND 19 THEN '10-19岁'

WHEN aget.age BETWEEN 20 AND 29 THEN '20-29岁'

WHEN aget.age BETWEEN 30 AND 39 THEN '30-39岁'

WHEN aget.age BETWEEN 40 AND 49 THEN '40-49岁'

WHEN aget.age BETWEEN 50 AND 59 THEN '50-59岁'

WHEN aget.age BETWEEN 60 AND 69 THEN '60-69岁'

ELSE '70岁及以上'

END 'age_range'

FROM (SELECT o.`PRINCE`,IF(MONTH(NOW())

ROUND(YEAR(NOW())-YEAR(birth)),ROUND(YEAR(NOW())-YEAR(birth))-1) AS age,u.userid

FROM user_info_utf AS u

INNER JOIN orderinfo AS o

ON  u.`userid`=o.`USERID`

WHERE birth>1900-00-00) AS aget  #年龄表

GROUP BY age_range

不同年龄段消费总额占比

不同年龄段人数占比

30-39年龄段的消费者为消费主力:30-39岁年龄段的消费人数占比为44.4，消费总额占比为45.3，是主要消费人群，需重点关注。20-29岁与40-49岁的消费总额占比也比较可观，

2.7 统计每个时间段的下单人数SELECT COUNT(*),SUBSTRING(TIME(paidtime),1,2) AS '时间段'

FROM `orderinfo`

GROUP BY SUBSTRING(TIME(paidtime),1,2)

早上11点和晚上10点是下单高峰：下单时间主要为上午10点到23点，上午11-12点与21-23点之间消费者较为活跃，19点左右下单人数明显下降，这个时间段是下班高峰。

2.8 统计消费的二八法则，消费的top20%用户，贡献了多少额度SELECT SUM(sprince),SUM(sprince)/(SELECT SUM(prince) FROM `orderinfo`) AS 'top20%的用户消费额占总消费额的占比'

FROM (SELECT *,(@RowNum := @RowNum + 1) AS RowNum

FROM (SELECT userid,SUM(prince) AS sprince

FROM `orderinfo`

GROUP BY userid

ORDER BY sprince DESC) AS prince_r,(SELECT @RowNum := 0) AS myRows

WHERE @RowNum+1

FROM `orderinfo`

ORDER BY prince)) AS prince_range

消费的top20%用户贡献了85%的总消费额：从数据上可看出top20%用户对消费总额上的贡献很大，应重点关注，可为其提供更高品质，个性化的服务。