Mssql+Tableau电商数据分析实战（二）

最近把sql复习了第二遍，也熟悉了tableau，纸上得来终觉浅，在天池找了一个专题进行实践，并参考了一个实践项目的分析思路，数据来源：UserBehavior

整个数据集解压之后有3.5G左右，包含了2017年11月25日至2017年12月3日之间，有行为的约一亿随机用户的隐式行为（行为包括点击、购买、加购、喜欢），用于隐式反馈推荐问题的研究。

导入mssql之后的基本的表结构如下：

列名	数据类型	说明
uid	int	序列化后的用户ID
pid	int	序列化后的商品ID
pcls	int	序列化后的商品所属类目ID
bhcls	nvarchar(50)	枚举类型，包括('pv’点击, 'buy’购买, ‘cart’,加购 'fav’收藏)
bht	nvarchar(50)	行为发生的时间戳

注意这里时间戳要设置为nvarchar()类型，如果直接设置成时间戳则显示出来是二进制数据，后期会无法转换成时间。

主要利用mssql+excel+tableau对天池比赛的淘宝用户行为进行分析。

一、数据清洗

1.1 检查重复值

按照用户、商品、时间聚集检查是否有对用户行为的重复记录

select uid
from UserBehavior
group by uid,pid,bht
having count(uid)>1

结果显示没有重复值

1.2 检查缺失值

检查空值

select *
from UserBehavior
where uid=NULL or pid=NULL or pcls=NULL or bhcls=NULL or bht=NULL

没有缺失值

1.3 时间戳转换成具体日期

创建一个新的列dates_time来填充转换日期

--增加列dates_time
ALTER TABLE userbehavior 
ADD COLUMN dates_time DATETIME;
--转换日期,将本来的日期加上'1970-01-01 00:00:00'得到10位时间戳对应时间
update UserBehavior set dates_time = DATEADD(s,cast(bht as bigint),'1970-01-01 00:00:00')  

这一步时间会很长，总共运行出来有一亿行以上的数据。

mssql的datetime类型会具体到秒后面三位小数，一般不需要这么精细，所以转换一个具体到天的列bh_day，便于后面按照日期计算

alter table UserBehavior add bh_day nvarchar(50);
update UserBehavior set bh_day = convert(char(10),dates_time,120);

1.4 剔除异常数据

鉴于数据量大，选择2017-11-25至2017-12-2 八天内数据来分析。

delete from UserBehavior where dates_time<'2017-11-25' or dates_time>'2017-12-03 '

最后看一下转换后的数据

最后得到大概850万左右行数据。

二、用户行为分析

2.1 AARRR模型

2.1.1 获客

定义用户第一次购买为获客，获取每日新增购买用户的数量

select t.f_date,count(*) as new_user
from (
select uid,min(bh_day) as f_date
from UserBehavior
where bhcls='buy'
group by uid
) as t
group by t.f_date

可视化：

可以看出新增用户从25日开始逐渐下降，但是在12月2日有一个小的提升，这种提升可能与促销活动等有关。

2.1.2 转化

此阶段衡量了用户转化和流失情况

①日流量
查询九日内每日的登陆用户数、浏览、收藏、加购、购买以及人均浏览量

SELECT
bh_day,
COUNT(DISTINCT uid) AS count_user,
SUM(CASE WHEN bhcls = 'pv'   THEN 1 ELSE 0 END ) AS count_pv,
SUM(CASE WHEN bhcls = 'fav'  THEN 1 ELSE 0 END ) AS count_fav,
SUM(CASE WHEN bhcls = 'cart' THEN 1 ELSE 0 END ) AS count_cart,
SUM(CASE WHEN bhcls = 'buy'  THEN 1 ELSE 0 END ) AS count_buy,
ROUND(SUM(CASE WHEN bhcls = 'pv' THEN 1 ELSE 0 END )/COUNT(DISTINCT uid),0) AS avg_pv
FROM UserBehavior
GROUP BY bh_day
ORDER BY bh_day

可视化后：

可以看出这几天内流量的变化不大，但是11/27和11/28这两天的pv波动比较大，推测可能是因为这两天为周一和周二，工作日的起始，同时也可以解释在11/30和12/1这两天周末流量飙升的原因。

②查询每日跳失率
跳失率：每日只点击一次用户数/总用户数

CREATE VIEW daily_pv AS
(
SELECT bh_day,uid,count(*) AS cn
FROM UserBehavior
WHERE bhcls='pv'
GROUP BY bh_day,uid
);
select t1.bh_day,count(distinct t1.uid) as left_user,count(distinct t2.uid) as all_user
from 
(select bh_day,uid from daily_pv where cn=1) as t1
join daily_pv as t2 on t1.bh_day = t2.bh_day
group by t1.bh_day;

跳出率在10%-15%波动，也就说明跳出率平稳，用户群体的留存较好。

③用户行为转化漏斗

SELECT
SUM(CASE WHEN bhcls = 'pv' THEN 1 ELSE 0 END ),
SUM(CASE WHEN (bhcls = 'fav' or bhcls = 'cart') THEN 1 ELSE 0 END ),
SUM(CASE WHEN bhcls = 'buy' THEN 1 ELSE 0 END ),
FROM UserBehavior

可视化：

虽然浏览量大，但是只有少部分用户选择了加车和收藏，最后只有极少部分用户选择购买。可以看出此转化漏斗的效率较低，用户变现能力较弱。

2.1.3 留存

由于数据只包含八天内数据，故根据前面的定义新用户计算七日留存率。

select u1.bh_day,count(distinct u1.uid)
from UserBehavior u1
where u1.uid in (
select distinct u2.uid
from UserBehavior u2
where u2.bhcls='buy' and u2.bh_day='2017-11-25')
and u1.bhcls='buy'
group by u1.bh_day

淘宝作为处于稳定期的app，次日到七日留存率都稳定在20%左右，处于行业内优秀水平，但是首日-次日的流失比较大，可以具体对产品用户转化地图来找到用户流失原因；且在用户首次使用后都在不断下降，在1日达到低谷，但是在2日又有一定的提升，这可能与周末的促销活动有关。

2.1.4 变现

SELECT t.cn,COUNT(*) as user_count
FROM (
SELECT uid,count(*) as cn
FROM UserBehavior
WHERE bhcls='buy'
GROUP BY uid
) AS t
GROUP BY t.cn
ORDER BY t.cn

复购率在10次以上的用户仅占18%，高复购的用户少，可以从商品、服务、物流三方面考虑寻找原因，提高复购率。

2.2 用户价值分析

RFM模型一般用来衡量用户价值和转化能力，从而帮助对用户细分，定义如下：最近一次购买(Recency)、消费频率(Frequency)、消费金额(Monetary)
数据缺少金额部分，所以从最近消费R和消费频率F来评估。

2.2.1 消费情况

创建一个视图buy_r_f来记录消费情况,计算每位用户的最近购买时间和和频率

CREATE VIEW buy_r_f AS
(
SELECT 
uid,DATEDIFF(DAY,MAX(bh_day),'2017-12-04') AS recency,COUNT(bhcls) AS f
FROM UserBehavior
WHERE bhcls='buy'
GROUP BY uid
)

计算排名来衡量用户的消费水平

SELECT uid,ROW_NUMBER() OVER(ORDER BY recency) AS rank_rencency,ROW_NUMBER() OVER(ORDER BY f) AS rank_f
FROM buy_r_f

根据不同用户种群的细分，可以进行差异化营销：
保持用户（第一象限）：这部分用户忠诚度高，进行不断营销保持即可。
挽留用户（第二象限）：这部分用户作为潜在发展对象，营销时可以发送邮件、短信等方式进行挽回；
发展用户（第三象限）：这部分用户在观望的居多，运营活动可以重点针对这部分用户，提高用户使用产品的频率，可以通过专属优惠投放、积分兑换等活动唤起用户注意力；
价值用户（第四象限）：这部分用户作为重点关注的VIP，适当增加个性化推荐的部分，精准投放保持其使用习惯；

三、商品销售分析

统计所有商品的销售频次、同时统计浏览、收藏、加车、购买最多的商品。

3.1商品销售频次

按照销售频次分类来分组，得到不同消费频次的商品数量。

SELECT t.buy_count,count(t.pid) as cn
FROM
(
pid,count(*) as buy_count
FROM UserBehavior
WHERE bhcls='buy'
GROUP BY pid
) as t
GROUP BY t.buy_count

80%商品的购买频率都在四次及以下，可以看出用户的购买基本靠长尾效应来积累，而不是靠爆款进行爆发式消费。

3.2 商品流量分布

创建商品流量视图

CREATE VIEW p_view AS(
SELECT TOP(1000000)
pid,
SUM(CASE WHEN bhcls='pv' THEN 1 ELSE 0 END) as view_sum,
SUM(CASE WHEN bhcls='fav' THEN 1 ELSE 0 END) as fav_sum,
SUM(CASE WHEN bhcls='cart' THEN 1 ELSE 0 END) as cart_sum,
SUM(CASE WHEN bhcls='buy' THEN 1 ELSE 0 END) as buy_sum,
COUNT(bhcls) AS p_sum
FROM UserBehavior
GROUP BY pid
)

找出累计流量前20的商品分析

WITH p1 AS(
SELECT pid ,RANK() OVER(PARTITION BY pid ORDER BY bh_sum DESC) AS bh_rk
FROM (
SELECT pid,SUM(bh_count) as bh_sum
FROM p_view
GROUP BY pid
) AS p2
)
SELECT *
FROM p_view AS p2
WHERE pid in (SELECT p1.pid FROM p1 WHERE bh_rk<21)

TOP20流量的商品基本都是大浏览量的商品：

• 高加车或者收藏的商品（比如pid为138964和363858这两个商品），对于这些商品可以在购物车或者收藏夹给予专属优惠券促进消费；
• 对于购买量高但是加车率低的商品（比如pid为2818402），考虑其可能质量较好，用户喜爱度高，可以提高其推荐权重，促进消费；
• 而只是浏览量高的商品可能在质量、详情等各个方向有一定缺陷，可以考虑跟商家沟通进一步考虑原因；

四、建议

4.1通过AARRR模型得出的建议

1. 获客：呈现短暂下降后回升的现象，与周末相关的营销活动对于吸引新用户起到促进作用。

2. 转化：过程中以浏览行为为主的用户下单率较少，此处为转化漏斗中可以改善的环节，结合后面TOP20商品流量分析可以给出下面建议：
   ①改进推荐系统中高购买量商品的权重，让用户更多的浏览高质量商品，减少挑选时间；
   ②对于用户反复浏览商品建立临时文件夹列出要素方便比较，省去用户返回页面浏览时间；
   ③优化收藏按钮，放在更醒目的地方提高使用率；

3. 留存：次日留存率较低，可以通过给新用户发放次日可用的优惠券等行为促进其次日使用率；且在12月2日流量又较大幅度上涨，应该留心该日所举办的活动，加以改进从而保证留存。

4. 收入：用户复购率可观，可以更多的投入在保持用户忠诚度上，以给予下次消费可用的优惠等方式促进用户消费频次提升。

4.2 通过RFM模型得出的建议

不同细分用户的使用习惯不同，细分后通过系统推荐精准化营销或者用户群营销是保持用户粘性的关键，应双管齐下，从而尝试更好的营销方式。

4.3 通过具体商品分析

用户对于同一商品的复购率并不高，主要通过对不同商品的单次购买积累销售量，这种模式可能与商品质量、商家服务等息息相关；这种模式在短期内可能打造高销售额，但是长期来看，用户对商品的低复购率会导致用户忠诚度低，如果有以低价为主的竞品出现则用户会很快流失，所以对于提高用户忠诚度提出以下建议：

1. 从商家角度：督促商家以诚信为主，对于高浏览量却低购买的商品调查后对相应商家群体提出改进建议；对于高浏览和购买的商品提高权重打造高质量爆款，形成良性循环；
2. 从用户角度：以红包、积分等奖励形式，促进用户评论商品，对于商品评论负面性强的商品降低其排名，促进用户购买高质量商品；
3. 从平台角度：改进商品详情页面，提供商品完整元数据便于比较，更多的提供历史比价、商品比较等功能；