![f93e70aea9e39aeed03b34d212748107.png](https://i-blog.csdnimg.cn/blog_migrate/9b556f86e4b4c2879dbb2513bdd4d8f5.jpeg)
前期,我们已经对MySQL的增删改查、以及多种查询方式和窗口、视图进行了解学习。本次我们利用前期所积累的知识进行数据分析实战。
本次实战数据源来自:
Supersetsuperset.workerindata.com明确问题
- 如何让用户喜欢我们的产品,进而提升产品销量?
分析思路
我们的产品是销售给用户的,销量好不好就在用户是不是喜欢我们的产品;用户喜欢还是不喜欢怎么判断呢?就要看用户的留存情况、复购情况。
那如何让用户喜欢我们的产品,进而增加销量呢?我们利用5W2H分析方法进行分析:
- why:那为什么用户喜欢我们的产品,那就需要看用户的需求是什么?比如说用户对我们的产品咖啡比较热爱,钟情某款产品;或者我们给优惠券,产品便宜了所以来购买;或者用户需要再我们的咖啡店进行聊天学习等;
- what:我们产品都有哪些?用户喜欢我们的哪些产品?
- where:用户喜欢店内使用我们的产品还是外带?
- when:用户什么时间段来购买产品?
- who:我们的目标用户群体主要有哪些?
那我们根据分析不同特征用户的需求,进而精细化运营,制定运营策略:
- how:我们如何去做才能提升用户喜爱,提高销量?
- how much:我们的产品是否需要进行促销优惠,优惠力度又该如何?
分析指标
![48874861497905509d9d7d0a80cd940d.png](https://i-blog.csdnimg.cn/blog_migrate/8573a99c0059eb16d464ef7dd9ee79c7.jpeg)
- 每天的总收入、总订单量
- 对用户分析:拉新、留存、老用户收入占比
- 了解用户消费习惯:用户消费频次、用户最近次消费日期、用户消费总金额、用户消费时长、用户到店时间分布
- 了解用户偏好:每个用户最喜欢的产品、近30天产品销量排名
- 了解用户的优惠券使用情况:每天赠送出去的不同优惠券数量、不同优惠券被使用消费的数量、过期优惠券未使用数量、优惠券按过期日期汇总
- 了解线下环境对用户影响:最受欢迎的桌子排名、消费金额和次数否在Top10中每个桌子坐过的次数、每个桌子每天空闲总时长
- 探查用户特征,发现用户价值(通过累计高频次、消费时长、连续消费等划分)
数据理解
本次数据集共有7个表:(脑图中的五角星项为Key)
![26177bbd0124dd64d515466f779bf0c2.png](https://i-blog.csdnimg.cn/blog_migrate/7af91ec1325934b8c8e7476058303a30.jpeg)
分别理解:
① 用户表 user_info
记录用户编号、性别、年龄以及注册日期;
![ef07ac2b1874c10d7b2ed904684178f3.png](https://i-blog.csdnimg.cn/blog_migrate/33d66019e0aa96eac186dbf222436add.png)
② 产品表 product
记录产品编号、产品名字、产品价格、每天可销售数量;
![828618c1b4cd6599a397905d934af91b.png](https://i-blog.csdnimg.cn/blog_migrate/f6b188a704af7008eb16f128ac4d6cae.png)
③ 桌号表 desk_info;
记录桌号、每桌人数;
![a157cbb79c44b84ca95e8b8c0d84b2b2.png](https://i-blog.csdnimg.cn/blog_migrate/1a66d843d2bf995c1b360f56576c84be.png)
④ 订单情况表 order_info
记录用户编号、订单编号、优惠券编号、下单时间、桌子编号;
![f09a205a129c7a7b37152126d0f13e16.png](https://i-blog.csdnimg.cn/blog_migrate/9ebf92b88aba32dbecac77f40f96aee5.png)
⑤ 订单数量表 order_detail
记录订单编号、产品偏好、销售数量;
![2e5cff9e7fc291ba189de561dd781881.png](https://i-blog.csdnimg.cn/blog_migrate/ce88108fc5e82d4eb0022baf9b13804c.png)
⑥ 优惠券表 coupon_info
记录优惠券种类、满减优惠、打折优惠、新用户无门榄优惠的数量
![41a2f3e0dad200faaaa4641804206b04.png](https://i-blog.csdnimg.cn/blog_migrate/8ac9d52e12c0ae32344cafbdd673d994.png)
⑦ 优惠券使用情况表 user_coupon_detail
记录优惠券编号、优惠券种类、用户编号、下单时间戳、优惠券到期日;
![35fbf593eacf0fd7c70115937a13504d.png](https://i-blog.csdnimg.cn/blog_migrate/4f98f6ab4e63f35c00b698976ac5bdbf.png)
数据清洗
本次项目不涉及数据清洗。
数据分析
我们根据上述的分析思路和分析指标,对数据进行分析:
计算每天的总收入、总订单量
1)计算每天的总收入、总订单量
- 每天的总收入
分析:每天收入 = 每天销售数量*售价;售价 = 价格*折扣;但产品不同,产品售价不同,产品销售数量也不同;所以需要对多表进行联结;
![b190bf7aeddacc671c2e1c49d506b7dc.png](https://i-blog.csdnimg.cn/blog_migrate/0f02599ffab5e0155feb174c8441bce7.jpeg)
select
![8c542c29ff17bf48aaf7578c2d95a485.png](https://i-blog.csdnimg.cn/blog_migrate/08507909629fdbbf49fbb22d70c52c42.png)
我们目前发现,6月1日(周六)的销售情况最佳;次之的为5月27日(周一);
- 每天总订单量
分析:对订单表order_info按照时间分组,对每天的订单数量统计单数;
select
![0e85736e582fbe14cc824163feda57f6.png](https://i-blog.csdnimg.cn/blog_migrate/9b376f8c558556d3f61382473c27923b.png)
我们目前发现,收入高的当天对应销量也较高;
对用户分析:拉新、留存、老用户收入占比
2)计算最近1周(假设今天2019-6-1)注册的用户,首次入店消费的用户数和消费金额
分析:条件是最近一周注册的用户,对符合条件的用户进行分析;统计新注册用户到店数(注意使用distinct)以及本周消费金额;
消费金额 = 每个用户本周购买产品数量*各个产品价格;价格 = 售价*折扣;但产品不同,产品售价不同,产品销售数量也不同;所以需要对多表进行联结;
![9dae38007c16f7f949fead0d62d193e6.png](https://i-blog.csdnimg.cn/blog_migrate/c36f64e6f22cf980b225c88e49239f24.jpeg)
select
注意,关于使用date_sub()函数执行日期减操作,比如 date_sub('d',interval7day),如果求当前日期的七天前,可以使用curdate()。
![d49630d6984db03804f65f2d876633c6.png](https://i-blog.csdnimg.cn/blog_migrate/9a7aab444ac8d5c158b3e1ccb2a0184e.png)
我们发现,最近一周新注册用户6人,进店消费共计141.68元;
3)新用户次日、次周、次月的复购用户数
-- 每个用户在某天天消费后,其后再次消费的日期
select
![b90756ef58247f73a68ff53603b82fe9.png](https://i-blog.csdnimg.cn/blog_migrate/816bbbae0549b4ab51d1d873def71153.jpeg)
-- 一般业务比较关系次日复购, 7日内复购, 30内复购用户数:
select
![e69c8b69385ea10b36ce08655c869028.png](https://i-blog.csdnimg.cn/blog_migrate/22c594fa3b0947773cdb982bce1d2a3e.png)
我们发现,5-25的用户次日复购人数1人,7日复购人数有7人;5-27用户新增6人,次日复购1人;
4)每天收入中,老用户收入占比
分析:首先明确每天收入金额(见第一问),其中用户为老用户(定义老用户,购买次数>1均为老用户)的购买金额占每天收入比;
-- 查找老用户
select
--那每日老用户的收入怎么算呢?
select
-- 那每天的老用户收入占比 = 老用户收入/当天收入【太粗暴了···期待精进】
select
![484736751ff26d1a3233b8a120334d30.png](https://i-blog.csdnimg.cn/blog_migrate/36b4418fe4b39f7cc01db90d1487cf8f.png)
我们发现,除了销量增高的5-27和6-1之外,其他时间均为老用户收入,占比100%;5-27和6-1增加大量新用户;
了解用户消费习惯:用户消费频次、用户最近次消费日期、用户消费总金额、用户消费时长、用户到店时间分布
5)每个用户消费频次
分析:对订单表order_info按照用户id分组,统计每个用户的订单数量;
select
![58943154aa045302a199fe57e19a06a2.png](https://i-blog.csdnimg.cn/blog_migrate/8f35dae94e0a2a31d0678413ddfbc070.png)
101用户属于咖啡狂热者,购买频次最大;次之的是102、103;
6)每个用户最近消费日期
分析:对订单表order_info按照用户id分组,统计每个用户消费订单日期最大项,显示用户id和消费日期;
select
![21e3766775129774d30e46eb4efb3974.png](https://i-blog.csdnimg.cn/blog_migrate/2c233a9e3a869b4e209ad09a0c8da0bd.png)
101用户102用户都是在19-06-02还有消费的用户,消费日期最近;
7)每个用户消费总金额
分析:每个用户消费金额= 每个用户购买产品数量*各个产品价格;价格 = 售价*折扣;但产品不同,产品售价不同,产品销售数量也不同;所以需要对多表进行联结;
![d2a5d6124dc1cc5862c25fcea6e8dfd3.png](https://i-blog.csdnimg.cn/blog_migrate/2308c1dfe3e1fc510e3314ebbd2e6d7f.jpeg)
select
![00293370b745a1d8c1cbb2b2eeaf744b.png](https://i-blog.csdnimg.cn/blog_migrate/dc0b93fe135e1838245b3a62cbb40761.png)
101用户消费金额最高,次之是102、103;
8)用户单次平均消费时长【未完】
分析:每个用户单次平均消费时长 = 每个用户总时长/消费次数
订单表order_info按照用户id、日期分组,按照分组结果计算每日消费时长,对消费时长求和为总时长;按照订单号统计消费次数;
【错误示例:因为timestampdiff(hour,createTime,payTime) 为时间戳,求和需要对】
select
(待后续补充,有大神会也可以提示一下,感谢)
9)用户到店按小时统计分布, 24小时, 每个小时到店人数【未完】
分析:每小时的用户分布数;对订单表order_info按照时间-时分组,统计每个小时的人数;
(待后续补充,有大神会也可以提示一下,感谢)
了解用户偏好:每个用户最喜欢的产品、近30天产品销量排名
10)每个用户最喜欢的产品
分析:按照用户编号分组,对分组结果查询最多购买产品数量,对每个用户购买的产品数量筛选,等于最大数量的便符合条件;
![c5620533af7b37cba7150395b7372cc2.png](https://i-blog.csdnimg.cn/blog_migrate/043a7668a32b2b8f8649b3f557078f0d.jpeg)
select
![6974e06d551dd82b405449303d9a2a17.png](https://i-blog.csdnimg.cn/blog_migrate/fe869a55a5999e9df2865e85911f8ced.png)
101用户,购买10 和12 两款数量最大,偏爱这两款;102偏爱10和13;
11)最近30天产品销量
分析:按照订单表order_info和订单情况表order_detail进行交叉联结,查询日期在30天内各产品编号销售数量:
![0c94f1707ded6063ef767f35ac6f4759.png](https://i-blog.csdnimg.cn/blog_migrate/2e8e03c138970dc29b6a62f70b794bb6.jpeg)
select
![771e7420ad7d1fd19614ab1a00e72c66.png](https://i-blog.csdnimg.cn/blog_migrate/e5445f66cc762fc9749f6df85948351d.png)
近30天内10款商品销量最好;
了解用户的优惠券使用情况:每天赠送出去的不同优惠券数量、不同优惠券被使用消费的数量、过期优惠券未使用数量、优惠券按过期日期汇总
12)每天赠送出去的不同优惠券数量
分析:按照优惠券情况表user_coupon_detail的createTime分组,对分组结果按照优惠券种类数量统计;
select
![10192eca65bdf4e083acdc57f24f9e70.png](https://i-blog.csdnimg.cn/blog_migrate/89da34290375650e5696c3f497088da3.png)
赠出的优惠券均为20001类别的优惠券;
13)不同优惠券被使用消费的数量和过期未消费的数量
- 不同优惠券被使用消费的数量
分析:根据订单中的优惠券编号,并统计使用的数量
select
![ce4d0f05cb5b50b24817a8d6b16a7095.png](https://i-blog.csdnimg.cn/blog_migrate/9cf1ff8bc5320c26e8bd59e766c8a824.jpeg)
我们发现,发放的优惠券均被使用;
- 不同优惠券过期未消费的数量
分析:截止到现在(假设是6月30日),优惠券到期日小于现在,且未被使用(不在订单中)的优惠券数量
select
![14bec5927eb89f5f7d399f34319751a5.png](https://i-blog.csdnimg.cn/blog_migrate/b160be56fdf969e894e2f21d2fc68b12.png)
我们发现,并无过期未使用的优惠券;看来用户还是很喜欢优惠券的;
这里为了验证代码是否因为写错导致没有数据,我们可以查看一个未到期的优惠券在2019-5-25消费情况:
select
![cf0de789b14855fb5169bda929d37c27.png](https://i-blog.csdnimg.cn/blog_migrate/ce80c6c4c28244a6f53e5bf8dbb264ff.png)
14)优惠券券按过期日期汇总
分析:按照优惠券情况表user_coupon_detail的过期日分组,对分组结果的数量统计,并按照过期日降序排列;
select
![4594931a758d7ae4a43eb0a0a5a759a6.png](https://i-blog.csdnimg.cn/blog_migrate/162b7b98d307a27a2cfb1137c53fa542.png)
根据以上了解,我们发现优惠券被广大用户所喜爱,那6-1和5-27销量较大是否是因为优惠券的原因呢?我们检查优惠券的发放日期:
select
![c9515399db3b01297cba311449b81a97.png](https://i-blog.csdnimg.cn/blog_migrate/4a5b5437f8a81227b31dcbeec360fd8b.png)
果然,用户购买量和优惠券的发放数量正相关;
了解线下环境对用户影响:最受欢迎的桌子排名、消费金额和次数否在Top10中每个桌子坐过的次数、每个桌子每天空闲总时长
15)做进30天内,最受欢迎的桌子top10
分析:对订单表order_info按照桌号deskid分组,统计近30天内桌号出现次数最多的top10
select
![74a99fea0ad3cb936ab9bf0f2101dbef.png](https://i-blog.csdnimg.cn/blog_migrate/f460ab9d5d80bd54d1146f049abc2d68.png)
我们发现,107#桌子最被大家喜欢,次之的是105和106#;再次之的是102和103#;
16)消费金额和次数都在Top10的用户,每个桌子坐过的次数
分析:对每个用户id分组,查询消费金额和消费次数都在top10的用户id;查询符合条件的用户每个桌子坐的次数;我们分以下几步慢慢来:
-- 消费金额top10的用户
select
![e4fde034b3db986f1769277d69285c94.png](https://i-blog.csdnimg.cn/blog_migrate/33c8b51c6256eb80ca3b4b248d7377b4.png)
-- 消费次数top10的用户
select
-- 消费次数和消费金额都在top10的用户
select
-- 消费金额和次数都在Top10的用户,每个桌子坐过的次数
select
![6f5f51b25cab21f0cec5ae16ec9b698d.png](https://i-blog.csdnimg.cn/blog_migrate/4cffadc77c3ebfe3ddea5ef2ad9837ae.png)
我们发现,top10 的用户,喜欢的桌子并不是我们刚才发现的top桌号,只有101号用户喜欢的是第三名的桌子;
我们检查桌子表:发现,最被喜欢的桌子107是单人桌;次之的105和106就属于大桌的;再次之的102和103属于中桌(2位);
![94afc1e0ec03efbffb7528aa41e1741b.png](https://i-blog.csdnimg.cn/blog_migrate/4883fdc936cb186a2d07ab1dd2a9fd15.png)
17)每个桌子每天空闲的总时长【未完】
(待后续补充,有大神会也可以提示一下,感谢)
探查用户特征,发现用户价值(通过累计高频次、消费时长、连续消费等划分)
18)在周末消费的用户
分析:可使用weekday(),返回值为7周六,1周日;
select
![f70f517b712715b4e33268665cb48e2f.png](https://i-blog.csdnimg.cn/blog_migrate/ae996eaf2ffa60a03eb94f5f05819b01.png)
我们发现,忠实用户101和102在周末仍然会消费呢;
19)工作日消费多余周末的用户
select
![d25d4ba6ffc42b317b5d4d29fd417505.png](https://i-blog.csdnimg.cn/blog_migrate/8586d4717ca70c37a38ffb45bfb142cf.png)
我们发现,工作日进行消费多余周末消费的用户top1是102号客户,显然101号客户更多的是在周末消费,属于周末控;而102号客户和103号客户是工作日控;
实施方案
根据以上分析结果,我们可以从以下几方面来提升用户的喜爱,提升销量:
- 通过发放新用户无门槛类的优惠券的方式,吸引新用户;
- 对于沉默用户,我们可以对其采用满减优惠的方式,激活用户;
- 对于忠实用户类似101类的用户,还可以以满一定金额返其最喜欢的产品来留存我们重要用户;
- 对于周末销量较小的情况,建议在周末适当增加优惠券吸引用户;
- 根据用户对桌子的偏爱,适当增加大桌,调整小桌布局;