MySQL项目实操-电商销售活动数据分析

@Keep updating~~

已于 2023-10-07 14:02:49 修改

阅读量164

点赞数

文章标签： mysql 数据分析数据库 sql

于 2023-10-06 17:32:30 首次发布

本文链接：https://blog.csdn.net/Updatinh/article/details/133615104

版权

MySQL电商销售活动数据分析

一、项目背景和数据介绍

项目背景

随着网络电商的迅速发展，越来越多的商家开启创业，然而很多商家虽然成功入驻了电商平台，但店铺经营情况却并不理想。导致店铺出现经营不善的原因有很多，但很重要的一个因素就是忽视了店铺数据的分析。其实对于网店运营来说，学会进行数据分析是很关键的。一个合格的运营商家，不能够仅仅只是凭借经验做事，而是需要通过现象看本质，用专业的数据进行理性分析，在合适的时机做正确的事，让店铺运营少走弯路，减少运营的试错成本！
某平台在双12之前进行了预热活动，我们收集了某双12之前大概一周的用户行为数据，请基于以下数据分析平台此次活动的效果，并对以下的任务分析原因和给出解决方案。需要解决的问题以序号标识。

数据介绍

Shopping.csv是来自某电商的用户行为数据，选取了大约 4800名用户在 2019 年 11 月 25 日至 12 月 3 日期间的用户行为记录，包含了点击、购买、添加商品到购物车和收藏商品等行为。数据集的每一行表示一条用户行为，由用户ID、商品ID、商品类目ID、行为类型和时间戳组成，并以逗号分隔。数据字典详细描述如下：

字段	列名称	说明
uid	用户ID	整数类型，序列化后的用户ID
gid	商品ID	整数类型，序列化后的商品ID
cid	商品类目ID	整数类型，序列化后的商品所属类目ID
behavior	行为类型	字符串，包括4个指标：pv:详情点击, buy：购买, cart：加购物车, fav：收藏
times	时间	行为发生的时间戳

Users.csv是所有用户的信息表，包含将近2000用户的详情，数据字典详细描述如下：

字段	列名称	说明
uid	用户ID	整数类型，序列化后的用户ID
uname	姓名	用户的姓名
age	年龄	整数类型，用户的年龄
sex	性别	用户的性别（0：女， 1-男）
area_id	所属地区	用户注册的城市地址编码
create_time	注册时间	用户注册的时间

AreaCode.csv是中国各省市城市编码表，数据字典详细描述如下：

字段	列名称	说明
cityid	城市编码ID	整数类型，序列化后城市编码ID
province	省份	所属省份
city	城市	所属城市
area	地区	所属地区（市、县、区）
area1	地址	省份+城市+地区的完整地址

二、数据清洗

对于以上数据来说，数据质量相对较高，但还存在小部分数据问题可能会导致分析结果出错，需进行相应的处理。请找出数据中存在的几个问题，针对不同的数据问题通过写SQL解决：

u1、Users表年龄缺失，年龄存在不合理的值。并说明该怎样处理，原因是什么？

u2、Shopping表用户id缺失、behavior缺失、时间超出范围（只探究11/25-12/03时段内的数据）、数据重复（使用两种方法去重）。

其他数据处理：

u3、给Shopping表增加日期列dates、时间段列hours；给Users表也增加日期列dates。

u4、将Users表的0、1替换为男女。

三、数据统计分析

根据以上数据，完成以下数据的统计分析任务，并可以结合第三方可视化软件，简要分析统计的结果。

u1、基于用户行为转化分析

漏斗分析是一套流程式数据分析，它能够科学反映用户行为状态以及从起点到终点各阶段用户转化率情况的重要分析模型，可以帮助决策者找到用户流失的原因，以提升用户量、活跃度、留存率，并提升数据分析与决策的科学性等。
提示：uv是总访问人数，pv是总点击量， pv/uv是统计区间内平均每人的访问页面数。
(1)总体情况（商品总数、类目总数、总访问人数uv、总点击量pv、总收藏量fav、总加购量cart、总购买量buy、平均每人的访问页面数pv/uv）的数量统计，存为视图v_behavior。
(2)每天每个用户行为（uv、pv、buy、cart、fav、pv/uv）的数量统计，存为视图v_day。
(3) 一天内各时段用户行为（uv、pv、buy、cart、fav、pv/uv）的数量统计，存为视图v_hour。
(4)各个用户在每个用户行为上（pv、buy、cart、fav）的数量统计，存为视图v_users。
(5)每个地区每个用户行为（uv、pv、buy、cart、fav、pv/uv）的数量统计，存为视图v_area。
**(6)**电商行业常用漏斗模型：首页—商品详情页—添加购物车—结算—提交订单—支付。
但本数据只有点击商品详情页（pv）、加入购物车（cart）、收藏（fav）和支付（buy），漏斗简化为点击—加购—收藏—购买。请结合以上几道题的过程分析整个漏斗转化过程。可以参考下图：

（PS：以第一小题，搜索结果的数据来画漏斗图为主。）

u2、复购率和用户留存

复购率定义：在某时间窗口内重复消费用户（消费超过两次或购买超过两件物品上的用户）在总消费用户中占比。
跳失率定义：仅仅访问了单个页面的用户（pv=1 and）占全部访问用户（pv>1 ?）的百分比，或者指从首页离开网站的用户占所有访问用户的百分比，简单理解就是只有一次点击行为。
留存的定义为：前一天注册的人在后续时间内（如何判断？INTERVAL +1/2/3/4…）还有继续访问平台并进行任意操作（任意操作的意思是后面一天依旧有前面一天那个用户的），例如：11/25当天有100人注册，这100人在11/26-11-30每天的留存人数分别为：80–>50–>10–>0–>0。
每天每个用户行为（uv、pv、buy、cart、fav、pv/uv）的数量统计，存为视图v_day。
**(7)总体的复购人数、购物人数、复购率，存为视图v_rebuy。
(8)对复购的人进行下一步分析，统计分析有复购的人的其购买频率。
(9)所有数据总体的跳失率，存为视图v_runoff。
(10)**以2019/11/25为初始，统计每日的新增用户在往后7天每天的留存情况，存为视图v_keep。结果参考下图：

u3、商品销售情况分析

**(11) 商品销量排行榜TOP20，保存为视图：v_buytop20。
(12)商品浏览排行榜TOP20，保存为视图：v_viwetop20。
(13) 商品销量与浏览量间的相关性分析，将以上两个视图合并，分析合并后的结果。
(14)按商品购买次数分组，统计不同购买次数的商品总量，存为视图：v_count,并分析结果。
_(15) _**统计每天的gid购买量Top3，结果可以参考下图：（仅供参考，数据不准）

u4、基于RFM模型的用户价值分析

用户行为分析的重要一步就是对不同的用户进行价值划分，以此来区分不同的用户价值，以便后续进行活动推广和营销。根据美国数据库营销研究所Arthur Hughes的研究，客户数据库中有3个神奇的要素，这3个要素构成了数据分析最好的指标：
l 最近一次消费(Recency)；消费频率 (Frequency)；消费金额 (Monetary)；
本数据没有金额字段，此次分析从R、F两个维度来进行分析，首先需要对有购物行为（即behavior=’buy’）的用户分别从R、F两个维度进行统计，制定分级标准，然后进行打分，最终按照综合分数对用户进行价值分层。方法如下：
**(16) 统计每个有购物行为的用户最近一次购买记录距离”2019-12-3“当天的时间间隔R。
(17)对时间间隔R打分，0_{1区间为5分，2}3区间为3分，4~5区间为2分，6及以上为1分，并统计不同区间的人数，分析结果。
(18)统计每个有购物行为的用户的消费次数F。
(19)对消费次数F打分，0~5区间为1分，6_{15区间为2分，16}25区间为3分，26~ 39区间为4分，40分以上：5分，并统计不同区间的人数，分析结果。
(20)**根据R、F的打分结果，计算每个有购物行为的用户的得分总分，并根据总分对划分不同的用户，划分定义如下：