使用数据集:京东消费者行为
使用软件与语言:Excel、Navicat15、MySQL8.0、Python3、Power BI
文章目录
一、分析目标
1、刻画用户画像,利用原数据集提取用户年龄整体的性别、年龄、城市、会员等级和注册时长。利用卡方检验这些变量与用户购买数之间的相关性和相关系数。并对用户在以上维度下考察整体转化率得出对应用户分级,最后按一定准则加权计算出大致的用户分级。
2、以提升指标转化率或者购买量为目标,通过分析时间、商品、商家的途径,寻找策略提高转化率或购买次数。
二、数据预处理
1.数据标签
Field | Type | Definition |
---|---|---|
customer_id | Bigint | 客户编码 |
product_id | Bigint | 产品编码 |
action_date | Date | 行为时间 |
action_id | Bigint | 行为编码 |
type | String | 行为类别 |
age_range | Int | 年龄分段 |
gender | String | 性别 |
customer_register_date | Date | 客户注册日期 |
customer_level | Int | 用户等级 |
city_level | Int | 城市级别 |
brand | String | 产品品牌 |
shop_id | Bigint | 店铺编码 |
category | String | 产品类别 |
product_market_date | Date | 产品上市日期 |
vender_id | Bigint | 商家编码 |
fans_number | Int | 粉丝数 |
vip_number | Int | 会员数 |
shop_register_date | Date | 开店时间 |
shop_category | String | 店铺主营 |
shop_score | Float | 店铺打分 |
注:时间范围为2018.2.1-2018.4.15。
2.查询缺失值
使用SQL:
SELECT
COUNT(customer_id),COUNT(product_id),COUNT(action_id),COUNT(type),COUNT(age_range),COUNT(gender),COUNT(customer_register_date),COUNT(customer_level),COUNT(city_level),COUNT(brand),COUNT(shop_id),COUNT(category),COUNT(product_market_date),COUNT(vender_id),COUNT(fans_number),COUNT(vip_number),COUNT(shop_register_date),COUNT(shop_category),COUNT(shop_score)
FROM jd_fnl
将得到结果导入Excel并转置得:
缺失值个数 | |
---|---|
COUNT(customer_id) | 183828 |
COUNT(product_id) | 183828 |
COUNT(action_id) | 183828 |
COUNT(type) | 183828 |
COUNT(age_range) | 183745 |
COUNT(gender) | 183828 |
COUNT(customer_register_date) | 183828 |
COUNT(customer_level) | 183828 |
COUNT(city_level) | 183703 |
COUNT(brand) | 183828 |
COUNT(shop_id) | 183828 |
COUNT(category) | 183828 |
COUNT(product_market_date) | 183828 |
COUNT(vender_id) | 183828 |
COUNT(fans_number) | 183828 |
COUNT(vip_number) | 183828 |
COUNT(shop_register_date) | 112474 |
COUNT(shop_category) | 183828 |
COUNT(shop_score) | 183828 |
由表可知,其中age_range、city_level存在少量的缺失值;而shop_register_date,即"店铺注册时间"有近1/3的数据为缺失值。
3.整体预分析
(1)用户数(UV)
SELECT COUNT(DISTINCT customer_id) as UV
FROM jd_fnl
得到结果为有140715个用户。
(2)访问频数(PV)
SELECT
SUM(IF(num=1,1,0)) as 1次,
SUM(IF(num=2,1,0)) as 2次,
SUM(IF(num=3,1,0)) as 3次,
SUM(IF(num=4,1,0)) as 4次,
SUM(IF(num>4,1,0)) as 大于4次
from(
SELECT
customer_id,
COUNT(*) as num
FROM jd_fnl
GROUP BY customer_id
) as freq
得到结果导入Excel并转置得:
frequency | num |
---|---|
1次 | 118140 |
2次 | 15475 |
3次 | 3691 |
4次 | 1363 |
大于4次 | 2046 |
(3)转化率
我们首先对总体进行行为分析:
SELECT
type,
COUNT(type) as num
FROM
jd_fnl
GROUP BY type
得到结果为:
type | num |
---|---|
Comment | 4010 |
Follow | 2179 |
Order | 10698 |
PageView | 163954 |
SavedCart | 2987 |
从表中我们可以得到PV数为163954,可以计算得到人均浏览量为 U V / P V ≊ 1.17 UV/PV \approxeq 1.17 UV/PV≊1.17次。
下面按日期对用户行为划分
SELECT
DATE_FORMAT(action_date,'%m-%d') as date,
SUM(IF(type in ('PageView'),1,0)) as 浏览数,
SUM(IF(type in ('Order'),1,0)) as 下单数,
SUM(IF(type in ('Savedcart'),1,0)) as 购物车数,
SUM(IF(type in ('comment'),1,0)) as 评论数,
SUM(IF(type in ('follow'),1,0)) as 点赞数
FROM
jd_fnl
GROUP BY date
直接将得到结果可视化(此处展示时间段为2018.2.1-2018.4.11):
通过日期上的行为数量的可视化,发现以下几点:
1.3.27和3.28的网页**“浏览数”存在异常**(均非常低),此处异常原因暂留待考究。
2.整体数据的趋势在2月中旬有一段低谷,其余时段数据较为平稳,考虑可能是因为2018年在2月中旬为春节,于是导致了物流工作人员减少等节假日相关问题。
3.评论数的变化趋势在4.7号到4.8中变化异常,“购物车数”在4.7号及前均为0,但4.8号剧增,此处异常留待考究。怀疑是统计中新增加了“购物车数”的指标。
考虑到第三点的影响,在整体时间维度上,“购物车数”有比较严重的异常情况,将时间调整到4.7号之后:
SELECT
type,
COUNT(type) as num
FROM
jd_fnl
WHERE MONTH(action_date)>3 and DAY(action_date)>7
GROUP BY type
ORDER BY num DESC
先可视化为漏斗图:
再可视化为条形——折线复合图:
可以发现,除了“浏览数”,其余用户行为次数的大概排名是**“购物车数”>“下单数”>“评论数”>“点赞数”**。
结合漏斗图,可以大概估计浏览用户在各环节的转化率,其中约1/5会选择先加入购物车,购物车中约1/3会下单。少部分客户会给出对购买商品的评论,极少数会对购买商品点赞。计算可得整体访问量到购买量的转化率约为7.94%。
3、用户数据提取
使用SQL:
SELECT
a.customer_id as 用户id,
age_range as 年龄等级,
gender as 性别,
register_time as 注册时间,
customer_level as 用户等级,
city_level as 城市等级,
act_freq as 行为数,
order_freq as 购买数,
ROUND(order_freq/act_freq,3) as 购买率
from
(SELECT DISTINCT
customer_id,
age_range,
gender,
TIMESTAMPDIFF(DAY,customer_register_date,"2018-4-15") as register_time,
customer_level,
city_level
FROM
jd_fnl) as a
left JOIN
(SELECT DISTINCT
customer_id,
COUNT(type) as act_freq,
sum(IF(type in ("order"),1,0)) as order_freq
FROM
jd_fnl
GROUP BY
customer_id
) as b
on a.customer_id=b.customer_id
结果导入Excel,预览前5行:
用户id | 年龄等级 | 性别 | 注册时间 | 用户等级 | 城市等级 | 行为数 | 购买数 | 购买率 |
---|---|---|---|---|---|---|---|---|
976695 | 5 | W | 302 | 6 | 3 | 1 | 0 | 0 |
395399 | 4 | M | 84 | 6 | 5 | 1 | 0 | 0 |
1068082 | 6 | W | 2071 | 1 | 4 | 2 | 0 | 0 |
735392 | 6 | M | 3134 | 7 | 5 | 6 | 0 | 0 |
1093882 | 5 | M | 1418 | 7 | 4 | 1 | 0 | 0 |
4.相关性分析
我们首先检查性别、年龄、注册时间、用户等级、城市等级和是否购买的相关性:
利用卡方检验,以下检验原假设均为:考察因素和是否购买无关。
利用python卡方检验得到结果如下表:
因素 | 显著性(P值) |
---|---|
年龄等级 | 0.0000 |
性别 | 0.0000 |
注册时间 | 0.0000 |
用户等级 | 0.0000 |
城市等级 | 0.0296 |
因此,性别、年龄、注册时间、用户等级这几个因素均与是否购买显著相关,而城市等级在置信度0.05下是与是否购买相关的,然而在0.01置信度下则与是否无关。
考察pearson和spearman相关矩阵:
考察最后一行可以发现性别、年龄、注册时间、用户等级均匀购买数存在着微弱的相关关系,城市等级的相关关系相对更低些。
同时可以注意到变量之间注册时间和年龄等级、用户等级存在正相关关系,即用户注册时间越长,年龄和用户等级平均越高。
分析中性别分别被编码为M-0,U-1,W-2,结合注册时间和性别的负相关关系,我们可以发现男性的注册时间平均比女性的平均注册时间更长。
三、用户画像
1.性别
(1)用户性别比例
SELECT
gender,
COUNT(gender) as num
FROM(
SELECT DISTINCT
customer_id,
gender
FROM
jd_fnl
)as gen
GROUP BY gender
得到结果并可视化:
由可视化结果,用户中61.2%为男性,38.5%为女性,小部分性别未知。
(2)转化率
分别计算男性和女性的行为分析数据组成
SELECT
type,
COUNT(type) as num
FROM
jd_fnl
WHERE MONTH(action_date)>3 and DAY(action_date)>7 AND gender in ('W')#男性将W改为M即可
GROUP BY type
ORDER BY num DESC
将结果可视化为漏斗图
分别可以计算得到其访问量到购买量的转化率为女性10.47%、男性6.45%、未知性别11.9%。
显然相比于整体的转化率7.94%,我们可以明显发现女性和未知性别的转化率是更高的,但同时考虑到未知性别的数据量问题,我们暂时只考虑男性和女性对转化率的贡献。最终,作出以下等级分级:
用户性别 | 等级分级 |
---|---|
男 | 低 |
女 | 高 |
未知 | 高 |
2.年龄等级
(1)年龄等级比例
SELECT
age_range ,
COUNT(age_range) as num
FROM(
SELECT DISTINCT
customer_id,
age_range
FROM
jd_fnl
)as age
GROUP BY age_range
得到结果:
age_range | num |
---|---|
1 | 10261 |
2 | 11147 |
3 | 7 |
4 | 11845 |
5 | 63531 |
6 | 43849 |
可视化结果:
由可视化结果,绝大部分用户年龄等级处在5级和6级,年龄等级1、2、3的用户数量则差不多,年龄等级4的用户则只有7位,数据量存在异常。
(2)转化率
SELECT
type,
COUNT(type) as num
FROM
jd_fnl
WHERE MONTH(action_date)>3 and DAY(action_date)>7 AND age_range in ('1','2')#3-4级和5-6级年龄修改此处条件即可
GROUP BY type
ORDER BY num DESC
将结果可视化为漏斗图:
分别可以计算得到其访问量到购买量的转化率为1-2级年龄7.03%、3-4级年龄12.44%和5-6级年龄7.68%。
结合整体转化率7.94%,我们同样作出以下作出以下等级分级:
用户年龄等级 | 等级分级 |
---|---|
1-2级年龄 | 中 |
3-4级年龄 | 高 |
5-6级年龄 | 中 |
3.注册时间
(1)注册时间分段
此处python计算注册时间频数的四分位点,将注册时间比例分为四段,得到分位等级如下:
分位数 | 注册时间 | 分位等级 |
---|---|---|
0.25 | 604 | 1 |
0.50 | 1220 | 2 |
0.75 | 2038 | 3 |
max | 5420 | 4 |
频数统计使用SQL验证如下:
SELECT
注册时间,
COUNT(注册时间) as num
FROM(
SELECT
用户id,
(CASE
WHEN `注册时间`<=604 THEN
1
WHEN 604<注册时间 and 注册时间<=1220 THEN
2
WHEN 1220<注册时间 and 注册时间<=2038 THEN
3
ELSE
4
END) as 注册时间
FROM
customer_level
)as zhuce
GROUP BY 注册时间
ORDER BY 注册时间
得到结果:
注册时间 | 频数 |
---|---|
1 | 35228 |
2 | 35118 |
3 | 35165 |
4 | 35134 |
(2)转化率
select
type,
count(type) as num
from
(SELECT DISTINCT
customer_id,
TIMESTAMPDIFF(DAY,customer_register_date,"2018-4-15") as register_time,
type,
action_date
FROM
jd_fnl) as a
WHERE MONTH(action_date)>3 and DAY(action_date)>7 AND register_time<=604 #其余时段更改此条件节即可
GROUP BY type
ORDER BY num DESC
将结果可视化为漏斗图:
分别计算得转化率有:10.47%,9.11%,7.30%,4.87%
我们可以发现注册时间越长的用户群体,其转化率越低,但是访问量并不会随之减少,主要减少的是消费欲望。我们得到如下等级分级:
注册时间等级 | 等级分级 |
---|---|
1 | 高 |
2 | 高 |
3 | 中 |
4 | 低 |
4.用户等级
(1)用户等级比例
SELECT
customer_level ,
COUNT(customer_level) as num
FROM(
SELECT DISTINCT
customer_id,
customer_level
FROM
jd_fnl
)as level
GROUP BY customer_level
ORDER BY customer_level
得到结果:
customer_level | num |
---|---|
1 | 35864 |
2 | 2 |
3 | 65 |
4 | 108 |
5 | 35160 |
6 | 21379 |
7 | 48137 |
可视化结果:
由可视化结果,我们可以按用户等级大致将用户分为四类群体:1-4级用户、5、6、7级用户。
(2)转化率
SELECT
type,
COUNT(type) as num
FROM
jd_fnl
WHERE MONTH(action_date)>3 and DAY(action_date)>7 AND customer_level in ('7')
GROUP BY type
ORDER BY num DESC
将结果可视化为漏斗图:
计算得转化率分别为:10.25%、7.1%、14.01%、4.69%。我们依然可以发现,较高等级的用户的转化率依然较低,因此可以考虑对于老用户的价值发掘。最后,我们给出以下分级。
用户等级 | 等级分级 |
---|---|
1-4 | 高 |
5 | 中 |
6 | 高 |
7 | 低 |
5.城市等级
(1)城市等级比例
SELECT
city_level ,
COUNT(city_level) as num
FROM(
SELECT DISTINCT
customer_id,
city_level
FROM
jd_fnl
)as city
GROUP BY city_level
得到结果:
city_level | num |
---|---|
1 | 29990 |
2 | 1816 |
3 | 36284 |
4 | 41931 |
5 | 30150 |
6 | 433 |
可视化结果:
由可视化结果,可以发些来自4级城市的用户最多,接下来是3级城市,其中1级城市和5级城市比例相当。也就是说,绝大部分用户都来自于我们传统理解上的二、三线城市。
(2)转化率
SELECT
type,
COUNT(type) as num
FROM
jd_fnl
WHERE MONTH(action_date)>3 and DAY(action_date)>7 AND city_level in ('1','2')#3-6级城市将此处条件改为3、4、5、6即可
GROUP BY type
ORDER BY num DESC
将结果可视化为漏斗图:
分别可以计算得到其访问量到购买量的转化率为1-2级城市7.91%和3-6级城市7.95%。
我们可以发现城市等级的转化率与整体转化率基本相当,因此我们不将城市纳入用户最终分级的考虑标准。
四、用户分级
经过用户画像分析,我们得到了用户群体的一个大致特征,和各个分组角度下用户群体的转化率,得到了性别、年龄、注册时长、用户等级四个分级维度,接下来进行用户分级。
1.分级准则:
首先结合用户画像中的分级标准,我们将高、中、低分别转化为数值得分:
等级分级 | 得分 |
---|---|
高 | 2 |
中 | 1 |
低 | 0 |
接下来给出权重计算准则,我们记录整体转化率为
E
0
=
7.94
%
E_0=7.94\%
E0=7.94%为平均转化率。我们分别记性别、年龄、注册时长、用户等级为
{
A
1
,
A
2
,
A
3
,
A
4
}
\{A_1,A_2,A_3,A_4\}
{A1,A2,A3,A4},假设
A
i
,
(
i
=
1
,
2
,
3
,
4
)
A_i,(i=1,2,3,4)
Ai,(i=1,2,3,4)中有
j
j
j个分类,每个分类的转化率记为
E
i
j
E_{ij}
Eij,我们定义
A
i
A_i
Ai的方差为:
V
a
r
(
A
i
)
=
∑
k
=
1
j
(
E
i
k
−
E
0
)
2
j
Var(A_i)=\frac{\sum_{k=1}^j(E_{ik}-E_0)^2}{j}
Var(Ai)=j∑k=1j(Eik−E0)2
我们依照方差占比得到各个分级维度的所占权重。
计算得每个维度的方差和权重为:
维度 | 方差 | 权重 |
---|---|---|
性别 | 8.10 | 24.6% |
年龄 | 7.05 | 21.4% |
注册时间 | 4.40 | 13.4% |
用户等级 | 13.36 | 40.6% |
利用sql作对应转化,再导入Excel,按权重计算得分,并结合购买率得到得分与购买率的相关矩阵:
可以发现最后的综合得分虽然与购买率的相关性仍然不到0.1,但是比综合前的离散因素有更好的相关性,更能帮助我们发现可能的价值用户。但由于其相关性依然不高,故此处可直接按照得分的四分位数将用户最终分为四级:
得分 | 用户评级 |
---|---|
1.38-2 | A |
1.102-1.38 | B |
0.62-1.38 | C |
0.214-0.62 | D |
到此,我们的目标一:刻画用户画像,和用户的大致分级两个内容已经基本完成。下面我们寻找策略去提高转化率。
其实,我们在分级过程中,也已经可以发现一个现象,那就是老用户的转化率相对较低,因此我们在此处已经可以提出一个策略建议,即设计方案提高老用户的购买率,例如给老用户提供更多的优惠,完善对老用户的回访服务和意见吸收,以提高老用户对平台的依赖和信赖。
接下来我们进一步寻找策略。
五、发掘策略
1.时段
(1)行为变化与转化率
我们查看单日24小时用户总体的行为情况:
SELECT
HOUR(action_date) as h,
SUM(IF(type in ('PageView'),1,0)) as 浏览数,
SUM(IF(type in ('Order'),1,0)) as 下单数,
SUM(IF(type in ('Savedcart'),1,0)) as 购物车数,
SUM(IF(type in ('comment'),1,0)) as 评论数,
SUM(IF(type in ('follow'),1,0)) as 点赞数
FROM
jd_fnl
WHERE MONTH(action_date)>3 and DAY(action_date)>7
GROUP BY h
得到结果并将其可视化:
我们可以发现用户的整体行为数据在一天的凌晨时段数值较低,而在19点开始到22点,用户整体行为数据数值呈明显的上升趋势。
接下来我们再考察1-6点、7-12点、13-18点、19-24点的转化率。
SELECT
type,
sum(IF(HOUR(action_date)>=19 AND HOUR(action_date)<=23 OR HOUR(action_date)=0,1,0)) as num#其余时间改变此处条件即可
FROM
jd_fnl
WHERE MONTH(action_date)>3 and DAY(action_date)>7
GROUP BY type
ORDER BY num DESC
将结果可视化为漏斗图
分别可以计算得到其访问量到购买量的转化率为1-6点4.67%、7-12点8.36%、13-18点8.998%和19点-24点7.22%。
(2)策略建议
首先结合上面的折线柱状图,我们可以发现,在一天的时间尺度下,浏览量和购买量是近似正相关的,因此为了提高购买量,可以设计方案在7-10点和18-22点这两个用户浏览量大幅提升的时间吸引更多用户,进而提高购买量。如规划广告投放时间段,或者设计类似晚间优惠券等方案在黄金时段吸引用户。
2.商品
(1)类别
此处利用商店分类来对商品类别的划分,以此考察商品类别对用户访问情况和购买情况的影响。
首先查看用户访问的商店类别分布情况:
SELECT DISTINCT
category,
COUNT(*) as num
FROM
jd_fnl
GROUP BY category
ORDER BY num DESC
得到结果:
shop_category | num |
---|---|
Electronics | 72706 |
Clothes | 29990 |
Beauty Makeup | 29617 |
Food | 25095 |
Household Eletric Appliance | 10283 |
Furniture | 5869 |
Jewellery Accessories | 5062 |
Outdoor Sports | 3853 |
Mother and Infant | 1353 |
可视化:
我可以发现访问量中电子产品类商店最多,高达39.6%,然后依次是服装类(16.3%)、化妆品类(16.1%)、食品类(13.7%)。
(2)行为分布
接下来我们查看4.7号之后,用户行为在商店类别上的差别。
SELECT
shop_category,
SUM(IF(type in ('PageView'),1,0)) as 浏览数,
SUM(IF(type in ('Order'),1,0)) as 下单数,
SUM(IF(type in ('Savedcart'),1,0)) as 购物车数,
SUM(IF(type in ('comment'),1,0)) as 评论数,
SUM(IF(type in ('follow'),1,0)) as 点赞数
FROM
jd_fnl
WHERE MONTH(action_date)>3 and DAY(action_date)>7
GROUP BY shop_category
得到结果,并利用Excel计算转化率得
shop_category | 浏览数 | 下单数 | 购物车数 | 评论数 | 点赞数 | 转化率 |
---|---|---|---|---|---|---|
Electronics | 6138 | 10 | 1210 | 125 | 69 | 0.16% |
Beauty Makeup | 2430 | 332 | 472 | 102 | 54 | 13.66% |
Food | 2361 | 285 | 475 | 117 | 68 | 12.07% |
Clothes | 2033 | 102 | 233 | 23 | 22 | 5.02% |
Household Eletric Appliance | 970 | 173 | 204 | 61 | 20 | 17.84% |
Jewellery Accessories | 525 | 141 | 132 | 44 | 10 | 26.86% |
Furniture | 522 | 68 | 81 | 22 | 5 | 13.03% |
Outdoor Sports | 375 | 67 | 144 | 15 | 8 | 17.87% |
Mother and Infant | 131 | 51 | 36 | 6 | 2 | 38.93% |
将其可视化得
我们可以发现以下几点:
1.对于电子类商品,人们虽然访问最多,加入购物车的数量也很多。但是电子类商品的下单量极低,可直接计算得到其转化率仅为0.16%。
2.对于化妆品类商品、家用电器类商品、珠宝配件类、家具类和母婴类商品用户在加入购物车后可能会有较高的购买量,甚至会直接购买而不加入购物车,因此这几类的转化率均很高。
针对电子产品,我们再考察电子产品相关的用户行为。
SELECT
category,
SUM(IF(type in ('PageView'),1,0)) as 浏览数,
SUM(IF(type in ('Order'),1,0)) as 下单数,
SUM(IF(type in ('Savedcart'),1,0)) as 购物车数,
SUM(IF(type in ('comment'),1,0)) as 评论数,
SUM(IF(type in ('follow'),1,0)) as 点赞数
FROM
jd_fnl
WHERE MONTH(action_date)>3 and DAY(action_date)>7 AND shop_category in ('Electronics')
GROUP BY category
ORDER BY 浏览数 DESC
得到结果
category | 浏览数 | 下单数 | 购物车数 | 评论数 | 点赞数 |
---|---|---|---|---|---|
Phone | 4063 | 2 | 718 | 74 | 44 |
Notebook | 749 | 1 | 163 | 12 | 13 |
Tablet | 713 | 5 | 206 | 26 | 6 |
Digital Camera | 387 | 0 | 70 | 6 | 3 |
Xbox | 214 | 1 | 49 | 7 | 2 |
Ipad | 12 | 1 | 4 | 0 | 1 |
可以发现以下几点:
1.大部分用户都会来浏览手机的销量情况,但极少数会购买。
2.转化率最高的两项为平板电脑和ipad(本质都是平板电脑)。虽然转化率依然偏低,但是下单量相比于其他产品较高。
而对于电子产品的品牌,
SELECT
brand,
SUM(IF(type in ('PageView'),1,0)) as 浏览数,
SUM(IF(type in ('Order'),1,0)) as 下单数,
SUM(IF(type in ('Savedcart'),1,0)) as 购物车数,
SUM(IF(type in ('comment'),1,0)) as 评论数,
SUM(IF(type in ('follow'),1,0)) as 点赞数
FROM
jd_fnl
WHERE MONTH(action_date)>3 and DAY(action_date)>7 AND shop_category in ('Electronics')
GROUP BY brand
ORDER BY 浏览数 DESC
得到结果
brand | 浏览数 | 下单数 | 购物车数 | 评论数 | 点赞数 |
---|---|---|---|---|---|
Apple | 1705 | 6 | 309 | 44 | 18 |
Huawei | 1661 | 0 | 306 | 27 | 17 |
Other | 460 | 0 | 94 | 9 | 6 |
Redmi | 330 | 1 | 117 | 10 | 3 |
Sumsung | 316 | 0 | 48 | 7 | 5 |
DELL | 303 | 1 | 56 | 5 | 7 |
Sony | 292 | 0 | 55 | 3 | 3 |
Microsoft | 214 | 1 | 49 | 7 | 2 |
Vivo | 199 | 1 | 33 | 3 | 2 |
Lenovo | 191 | 0 | 33 | 1 | 2 |
Acer | 173 | 0 | 41 | 3 | 4 |
OPPO | 117 | 0 | 21 | 0 | 0 |
Nikon | 95 | 0 | 15 | 3 | 0 |
HP | 82 | 0 | 33 | 3 | 0 |
我们可以发现用户倾向浏览苹果、华为等电子品牌;其中大部分购买的品牌为苹果。
(3)上架时长
接下来我们考虑上架时长,首先利用SQL取数。
SELECT
TIMESTAMPDIFF(DAY,date(product_market_date),"2018-4-15") as shop_time
from
jd_fnl
导入excel并计算得四分位数,按四分位数将上架时长分为四段:
上架时长 | 分段 |
---|---|
1-252 | 1 |
252-537 | 2 |
537-990 | 3 |
990-3621 | 4 |
下面我们结合商品类别和上架时长考察转化率情况:
SELECT
shop_category as 类别,
shop_time as 上架时长,
SUM(IF(type in ('PageView'),1,0)) as 浏览数,
SUM(IF(type in ('Savedcart'),1,0)) as 购物车数,
SUM(IF(type in ('Order'),1,0)) as 下单数,
SUM(IF(type in ('comment'),1,0)) as 评论数,
SUM(IF(type in ('follow'),1,0)) as 点赞数
FROM
(SELECT
customer_id,
shop_category,
(CASE
WHEN TIMESTAMPDIFF(DAY,date(product_market_date),"2018-4-15")<=252 THEN 1
WHEN TIMESTAMPDIFF(DAY,date(product_market_date),"2018-4-15")>252 AND
TIMESTAMPDIFF(DAY,date(product_market_date),"2018-4-15")<=537 THEN 2
WHEN TIMESTAMPDIFF(DAY,date(product_market_date),"2018-4-15")>537 AND
TIMESTAMPDIFF(DAY,date(product_market_date),"2018-4-15")<=990 THEN 3
ELSE 4
END) as shop_time,
action_date,
type
from
jd_fnl) as a
WHERE MONTH(action_date)>3 AND DAY(action_date)>7
GROUP BY 类别,上架时长
ORDER BY 类别
导入excel并使用数据透视表展示如下结果:
(4)策略建议
首先对于转化率高的商品,保证商品的数量、物流和质量安全,同时增加相关产品的宣传提高访问量以获得更多的购买。
而对于电子产品,我们可以发现大部分用户的意愿是以浏览为主,并且更偏向于浏览手机、笔记本、平板这些用品和苹果、华为这些品牌。所以从增加浏览量的角度讲,平台可以保证这些品类的电子产品以吸引用户。为了增加购买量,平台也可以和商家合作,推出合作活动,增加促销活动,提高用户购买意愿。同时,在电子产品的浏览界面,也可以适当增加其他商品的广告以吸引用户购买其他用品。
结合上架时间来看,我们可以发现:
对于“Clothes”、“Food”、"Jewellery"类的商品,用户活动显然更倾向于上架时间等级为1的商品。这类商品需要注意紧跟时尚,把握潮流;对于“Beauty Makeup”、“Furniture”、“Household Eletric Appliance”、"Mother and Infant"类的商品,人们便对上架时间等级为1-3呈现差不多的兴趣,甚至对2-3级的用品表现出更高的浏览购买意愿;对于“Outdoor Sports”类的商品则,用户则不再倾向于新上架商品,反而更加关注上架时间等级2-4的商品,因此平台对于后两类用品一方面可以增加新上线产品的宣传,另一方面,这些产品的用户也可能更加倾向于“评论数”,我们可以发现这些产品的评论比率相比于“Clothes”、“Food”都是比较高的,因此需要积极收集客户意见,提高用户评价。而电子产品,用户会倾向于浏览上架时间等级3-4的产品,但是购买上架时间等级1的产品,因此平台可以考虑促销出售上架时间等级3-4的电子产品以提高购买次数并及时引入最新电子产品。
3.商店
(1)数据提取和相关性分析
首先从原数据集中筛选商店信息:
SELECT
shop_id,brand,shop_category,fans_number,vip_number,
IFNULL(TIMESTAMPDIFF(DAY,shop_register_date,"2018-4-15"),0) as shop_register_time,
shop_score,
SUM(IF(type in ('PageView'),1,0)) as 浏览数,
SUM(IF(type in ('Savedcart'),1,0)) as 购物车数,
SUM(IF(type in ('Order'),1,0)) as 下单数,
SUM(IF(type in ('comment'),1,0)) as 评论数,
SUM(IF(type in ('follow'),1,0)) as 点赞数
FROM
jd_fnl
GROUP BY shop_id,brand,shop_category,fans_number,vip_number,shop_register_time,shop_score
ORDER BY 浏览数 desc
对提取数据进行相关分析:
可以发现,转化率和粉丝数量、会员数量、有正相关关系,与店铺得分的相关性有0.13左右。
(2)店铺类别
下面结合店铺类别考虑。考虑到粉丝数量、会员数量和店铺得分间的高相关性,这里主要考虑店铺类别、店铺得分和相关行为数据。
利用四分位数将店铺得分为四段等级:
店铺得分 | 分段 |
---|---|
-1-9.19 | 1 |
9.19-9.45 | 2 |
9.45-9.64 | 3 |
9.64-10 | 4 |
利用SQL语句分段并统计对应用户行为
SELECT
shop_id,shop_category,
(CASE
WHEN shop_score<=9.19 THEN 1
WHEN shop_score<=9.45 AND shop_score>9.19 THEN 2
WHEN shop_score<=9.64 AND shop_score>9.45 THEN 3
ELSE 4
END)as score_rank,
SUM(IF(type in ('PageView'),1,0)) as 浏览数,
SUM(IF(type in ('Savedcart'),1,0)) as 购物车数,
SUM(IF(type in ('Order'),1,0)) as 下单数,
SUM(IF(type in ('comment'),1,0)) as 评论数,
SUM(IF(type in ('follow'),1,0)) as 点赞数
FROM
jd_fnl
GROUP BY shop_id,shop_category,score_rank
ORDER BY shop_category
得到结果并导入EXCEL,利用数据透视表可视化得:
我们可以发现电子产品的购物车数此时对整体图表影响较大,我们暂时将电子产品类别筛去,得到图表:
(3)策略建议
首先作为店铺商家,需要关注自身的店铺评分,提高自身商品质量和服务,吸引更多客户消费。
下面结合到商店的具体产品类别。对于电子产品,我们可以发现电子产品的店铺,即使如苹果、华为,其评分基本均为0,且主要分布集中于评分等级1,评分等级上并无显著差距,因此对于电子产品类暂不讨论;对于其余各类别产品,用户均更倾向于在评分等级2-4的店铺消费,且在除了“Jwellery Accessories”中,评分等级1和评分等级2-4的店铺热度差距都很大。在"Outdoor Sports"中,用户最会对店铺有更高的追求,更偏向于评分等级3-4的店铺。因此,店铺一方面需要研究如何提高自身评分,平台也可以更多地去扶持推广高评分店铺,帮助管理低评分店铺。
六、附录
python绘制漏斗图代码:
#绘制漏斗图
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
dt= pd.read_excel('C:\\Users\\86151\\Desktop\\7-12点行为总数.xlsx')
cate=dt['type']
trans_data=dt['num']
from pyecharts.charts import *
from pyecharts import options as opts
funnel = Funnel(
init_opts=opts.InitOpts(
bg_color='white', # 设置背景颜色
theme='dark', # 设置主题
width='1000px', # 设置图的宽度
height='500px' # 设置图的高度
)
)
funnel.add(
" ",
[list(z) for z in zip(cate, trans_data)],
# is_label_show=True, # 确认显示标签
)
funnel.set_series_opts( # 自定义图表样式
label_opts=opts.LabelOpts(
is_show=True,
formatter="{a}\n{b} : {c}",
position = "outside",#图例位置
font_weight = 'bolder',
font_style = 'oblique',
font_size=15,
), # 是否显示数据标签
itemstyle_opts={
"normal": {
# 调整柱子颜色渐变
'shadowBlur': 8, # 光影大小
"barBorderRadius": [100, 100, 100, 100], # 调整柱子圆角弧度
"shadowColor": "#E9B7D3", # 调整阴影颜色
'shadowOffsetY': 6,
'shadowOffsetX': 6, # 偏移量
}
}
)
funnel.set_global_opts(
# 标题设置
title_opts=opts.TitleOpts(
title='7-12点行为漏斗图', # 主标题
#subtitle='各环节人数', # 副标题
pos_left='center', # 标题展示位置
title_textstyle_opts=dict(color='#5A3147'), # 设置标题字体颜色
subtitle_textstyle_opts=dict(color='#5A3147')
),
legend_opts=opts.LegendOpts(
is_show=True, # 是否显示图例
pos_left='left', # 图例显示位置
pos_top='3%', #图例距离顶部的距离
orient='vertical', # 图例水平布局
textstyle_opts=opts.TextStyleOpts(
color='#5A3147', # 颜色
font_size='13', # 字体大小
font_weight='bolder', # 加粗
),
),
)
funnel.render_notebook()
python卡方检验和相关分析代码:
import pandas as pd
import numpy as np
from scipy.stats import chi2_contingency
# In[5]:
df=pd.read_excel('C:/Users/86151/Desktop/年龄与购买.xlsx')
data=df.drop('年龄等级',axis=1)
kt=chi2_contingency(data)
print('卡方值=%.4f, p值=%.4f, 自由度=%i expected_frep=%s'%kt)
# In[6]:
df=pd.read_excel('C:/Users/86151/Desktop/性别与购买.xlsx')
data=df.drop('性别',axis=1)
kt=chi2_contingency(data)
print('卡方值=%.4f, p值=%.4f, 自由度=%i expected_frep=%s'%kt)
# In[7]:
df=pd.read_excel('C:/Users/86151/Desktop/注册时间与购买.xlsx')
data=df.drop('注册时间',axis=1)
kt=chi2_contingency(data)
print('卡方值=%.4f, p值=%.4f, 自由度=%i expected_frep=%s'%kt)
# In[8]:
df=pd.read_excel('C:/Users/86151/Desktop/用户等级与购买.xlsx')
data=df.drop('用户等级',axis=1)
kt=chi2_contingency(data)
print('卡方值=%.4f, p值=%.4f, 自由度=%i expected_frep=%s'%kt)
# In[9]:
df=pd.read_excel('C:/Users/86151/Desktop/城市等级与购买.xlsx')
data=df.drop('城市等级',axis=1)
kt=chi2_contingency(data)
print('卡方值=%.4f, p值=%.4f, 自由度=%i expected_frep=%s'%kt)
# In[10]:
#相关分析
import seaborn as sns
from matplotlib import pyplot as plt
df=pd.read_excel('C:/Users/86151/Desktop/jd_改/用户分级信息.xlsx')
# In[12]:
data=df.drop(['用户id','行为数','购买率'],axis=1)
#文本排序编码,M-0,U-1,W-2
from sklearn.preprocessing import LabelEncoder
cat_df = df.select_dtypes(include = ['object'])
object_cols = [col for col in cat_df.columns]
label_encoder = LabelEncoder()
for col in object_cols:
data[col] = label_encoder.fit_transform(data[col])
corr1 = data.corr(method='pearson') #pearson相关矩阵提取
corr2 = data.corr(method='spearman') #spearman相关矩阵提取
plt.rcParams['font.sans-serif']='SimHei' #设置字体为SimHei
plt.rcParams['axes.unicode_minus']=False #解决负号“-”显示异常
fig=plt.figure(figsize=(16,8),dpi=80)#创建画布
mask1 = np.triu(np.ones_like(corr1, dtype=bool))
mask2 = np.triu(np.ones_like(corr2, dtype=bool))
fig.add_subplot(1,2,1)
sns.heatmap(data=corr1, mask=mask1, annot=True, fmt='.2g', linewidth=1)
fig.add_subplot(1,2,2)
sns.heatmap(data=corr2, mask=mask2, annot=True, fmt='.2g', linewidth=1)
plt.show()
# In[6]:
df=pd.read_excel('C:/Users/86151/Desktop/jd_改/用户评分.xlsx')
data=df.drop('用户id',axis=1)
data.describe()
#相关分析
import seaborn as sns
from matplotlib import pyplot as plt
corr1 = data.corr(method='pearson') #pearson相关矩阵提取
corr2 = data.corr(method='spearman') #spearman相关矩阵提取
plt.rcParams['font.sans-serif']='SimHei' #设置字体为SimHei
plt.rcParams['axes.unicode_minus']=False #解决负号“-”显示异常
fig=plt.figure(figsize=(16,8),dpi=80)#创建画布
mask1 = np.triu(np.ones_like(corr1, dtype=bool))
mask2 = np.triu(np.ones_like(corr2, dtype=bool))
fig.add_subplot(1,2,1)
sns.heatmap(data=corr1, mask=mask1, annot=True, fmt='.2g', linewidth=1)
fig.add_subplot(1,2,2)
sns.heatmap(data=corr2, mask=mask2, annot=True, fmt='.2g', linewidth=1)
plt.show()
# In[7]:
df=pd.read_excel('C:/Users/86151/Desktop/店铺信息.xlsx')
data=df.drop('shop_id',axis=1)
data.describe()
# In[8]:
#相关分析
import seaborn as sns
from matplotlib import pyplot as plt
corr1 = data.corr(method='pearson') #pearson相关矩阵提取
corr2 = data.corr(method='spearman') #spearman相关矩阵提取
plt.rcParams['font.sans-serif']='SimHei' #设置字体为SimHei
plt.rcParams['axes.unicode_minus']=False #解决负号“-”显示异常
fig=plt.figure(figsize=(16,8),dpi=80)#创建画布
mask1 = np.triu(np.ones_like(corr1, dtype=bool))
mask2 = np.triu(np.ones_like(corr2, dtype=bool))
fig.add_subplot(1,2,1)
sns.heatmap(data=corr1, mask=mask1, annot=True, fmt='.2g', linewidth=1)
fig.add_subplot(1,2,2)
sns.heatmap(data=corr2, mask=mask2, annot=True, fmt='.2g', linewidth=1)
plt.show()