理解数据
对用户行为的分析可以简单地理解为用户画像，关于我自己对用户画像的理解：
我们每个人都有自己的微信成员，给每一位成员打上3到个不相同的标签，最后我们对这些标签进行汇总，统计，分析，其实就是简化的用户画像处理。以此，我们对以下用户行为数据进行几个不同维度的分析（维度的颗粒度适宜即可，无需过于细分，具体以各自业务为准）。
本文主要对以下数据进行分析，数据包含orderinfo表和userinfo表。其中orderinfo表含有539394条数据，5个字段；userinfo表含有101525条数据，3个字段。
涉及表的表结构如下：
orderinfo 订单详情表
| orderid 订单id
| userid 用户id
| isPaid 是否支付
| price 付款价格
| paidTime 付款时间
userinfo 用户信息表
| userid 用户id
| sex 用户性别
| birth 用户出生日期
数据来源于数据蛙。
本文主要从以下七个维度方面进行分析：
1、统计不同月份的下单人数。
2、统计用户三月份的回购率和复购率。
3、统计男女用户消费频次是否有差异。
4、统计多次消费的用户，第一次和最后一次消费间隔是多少天。
5、统计不同年龄段，用户的消费金额是否有差异。
6、统计消费的二八法则，消费的top20%用户，贡献了多少消费额。
1、统计不同月份的下单人数
select
year(paidTime),
month(paidTime),
count(distinct userid) as cons
from orderinfo where isPaid = '已支付'
group by year(paidTime),month(paidTime);
将数据可视化
从上图得知，平均每月都有5万左右的人购买产品，数据主要涉及3,4月份。总体得知该数据的大致模型。
2、统计用户三月份的回购率和复购率
本月复购率：当月购买了多次的用户占当月用户的比例
本月回购率：本月购买用户中有多少用户下个月又再次购买
-- 复购率
with p as
(select
userid,
count(1) as cons
from orderinfo where ispaid = '已支付' and month(paidTime) = '03'
group by userid)
select
count(1) as userid_cons,
sum(if(cons>1,1,0)) as fugou_cons,
concat(round(sum(if(cons>1,1,0))/count(1)*100,2),'%') as fugou_rate
from p;
-- 逐步考虑逻辑如下：
a. 先筛选出3月份的消费情况
select
*
from
orderinfo
where isPaid = '已支付'
and month(paidTime) = '03';
b. 统计一下每个用户在3月份消费了多少次
select
userid,
count(1) as cons
from
orderinfo
where isPaid = '已支付'
and month(paidTime) = '03'
group by userid;
c. 对购买次数做一个判断，统计出来那些消费了多次（大于1次）的用户数
with p as
(select
userid,
count(1) as cons
from
orderinfo
where isPaid = '已支付'
and month(paidTime) = '03'
group by userid
)
select
count(1) as userid_cons,
sum(if(cons>1,1,0)) as fugou_cons,
sum(if(cons>1,1,0))/count(1) as fugou_rate
from p;
-- 3月回购率=3月用户中4月又再次购买的人数/3月的用户总数
with p as
(select
userid,
date_format(paidTime,'%Y-%m-01') as month_dt,
count(1) as cons
from orderinfo
where isPaid = '已支付'
group by userid,date_format(paidTime,'%Y-%m-01'))
select
count(a.userid),
count(b.userid),
count(b.userid)/count(a.userid) as huigou_rate
from (select * from p) a
left join (select * from p) b
on a.userid = b.userid and date_sub(b.month_dt,interval 1 month) = a.month_dt
group by a.month_dt;
-- 分布逻辑：
a. 统计每年每月的一个用户消费情况
select
userid,
date_format(paidTime,'%Y-%m-01') as month_dt,
count(1) as cons
from orderinfo
where isPaid = '已支付'
group by userid,date_format(paidTime,'%Y-%m-01')
b. 相邻月份进行关联，能关联上的用户说明就是回购
with p as
(select
userid,
date_format(paidTime,'%Y-%m-01') as month_dt,
count(1) as cons
from orderinfo
where isPaid = '已支付'
group by userid,date_format(paidTime,'%Y-%m-01')
)
select
*
from (select * from p) as a
left join (select * from p) as b
on a.userid = b.userid
and date_sub(b.month_dt,interval 1 month) = a.month_dt;
c、统计每个月份的消费人数情况即可得到回购率
with p as
(select
userid,
date_format(paidTime,'%Y-%m-01') as month_dt,
count(1) as cons
from orderinfo
where isPaid = '已支付'
group by userid,date_format(paidTime,'%Y-%m-01')
)
select
a.month_dt,
count(a.userid),
count(b.userid),
count(b.userid)/count(a.userid) as huigou_rate
from (select * from p) as a
left join (select * from p) as b
on a.userid = b.userid
and date_sub(b.month_dt,interval 1 month) = a.month_dt
group by a.month_dt;
因为5月份数据统计量过于稀少，无法为分析提供足够的数据，因此单另分析三月份的回购率和复购率。对于30%的复购用户以及接近24%的回购用户，我们需要对他们进行特别关注且必须维护好，可将他们设置为一级用户。
3.统计男女用户消费频次是否有差异
with p as
(select
a.userid,
sex,
count(1) as cons
from orderinfo a
inner join (select * from userinfo where sex != '') b
on a.userid = b.userid
group by a.userid,sex)
select
sex,
avg(cons) as avg_cons
from p
group by sex;
-- 分步逻辑
a、统计每个用户的消费次数，注意要带性别
select
a.userid,
sex,
count(1) as cons
from orderinfo a
inner join (select * from userinfo where sex<>'') b
on a.userid=b.userid
group by a.userid,sex;
2、对性别做一个消费次数平均计算
with p as
(select
a.userid,
sex,
count(1) as cons
from orderinfo a
inner join (select * from userinfo where sex<>'') b
on a.userid=b.userid
group by a.userid,sex
)
select
sex,
avg(cons) as avg_cons
from p
group by sex;
从上表我们得出：男女用户之间的消费频次没有差异，因此性别因素是不需要另外考虑的。
4、统计多次消费的用户，第一次和最后一次消费间隔是多少天
a.取出多次消费的用户
select
userid
from orderinfo
where isPaid = '已支付'
group by userid
having count(1)>1;
b.取出第一次和最后一次的时间
with p as
(select
userid,
min(paidTime),
max(paidTime),
datediff(max(paidTime),min(paidTime)) as time_interval
from orderinfo
where isPaid = '已支付'
group by userid
having count(1)>1
order by userid
)
select
*,
avg(time_interval) over() as avg_time_interval
from p;
如上表，可以看出多次消费的用户最近消费时间和最早一次消费时间之间的差值，我们以消费时间差的平均值为统计标准，间隔大于16天的用户我们可以采用激励的措施（签到给金币，金币抵消部分RMB、购买优惠券等）吸引用户回归，间隔小于16天的用户我们可以采取更高的消费手段（满500减30，付费的落地页面更流畅等），以此吸收更大的流量用户。
5. 统计不同年龄段，用户的消费金额是否有差异
a. 计算每个用户的年龄，并对年龄进行分层(0-10:1,11-20:2,21-30:3)可以用case when。
select
userid,
birth,
now(),
timestampdiff(year,birth,now()) as real_age,
ceil(timestampdiff(year,birth,now())/10) as age
from userinfo
where birth>'1901-00-00';
b. 关联订单信息，获取每个用户所在年龄段的消费频次和消费金额
with p as
(select
userid,
birth,
now(),
ceil(timestampdiff(year,birth,now())/10) as age
from userinfo
where birth>'1901-00-00'
)
select
o.userid,
age,
count(1) as cons,
sum(price) as prices
from orderinfo o
inner join p
on o.userid = p.userid
group by o.userid,age;
c、再对年龄分层进行聚合，得到不同年龄层的消费情况
select
age,
avg(cons) as avg_cons,
avg(prices) as avg_prices
from
(with p as
(select
userid,
birth,
now(),
ceil(timestampdiff(year,birth,now())/10) as age
from userinfo
where birth>'1901-00-00'
)
select
o.userid,
age,
count(1) as cons,
sum(price) as prices
from orderinfo o
inner join p
on o.userid = p.userid
group by o.userid,age) a
group by age
order by age;
从上表可得出，不同年龄段的消费者消费金额和消费频次之间是不同的，重点维护好3,4,5,6年龄阶段的人群，即20-50之间的人群，对这部分人的喜好，兴趣，购买时间等特征进行用户画像处理。
6、统计消费的二八法则，消费的top20%用户，贡献了多少消费额
a、统计每个用户的消费金额，并进行一个降序排序
select
userid,
sum(price) as total_price
from orderinfo a
where isPaid = '已支付'
group by userid
order by total_price desc;
b、统计一下一共有多少用户，以及总消费金额是多少
select
count(1) as cons,
sum(total_price) as all_price
from (select
userid,
sum(price) as total_price
from orderinfo a
where isPaid="已支付"
group by userid) a;
c. 取出前20%的用户进行金额统计
select
count(1) as cons,
sum(total_price) as all_price
from (
select
userid,
sum(price) as total_price
from orderinfo a
where isPaid="已支付"
group by userid
order by total_price desc
limit 17000) b ;
select 2.7/3.18 -- 0.84906
20%的用户提供了85%的消费贡献量，要特别维护好前20%的用户，与上面5的做法一致，要做好用户维护。
结论
1. 总体数据显示3,4月份的购买用户量每月都有5万左右。
2. 从用户的复购率，回购率，性别影响，年龄分布影响，消费周期等几个不同的维度指标进行了用户行为分析和探讨，针对满足不同条件的用户进行不同的销售策略，力图达到精细化运营，以下分析结果可以帮助运营人员提供具体分析，提升他们的运营效率和业务增长：
2.1 对于30%的复购用户以及接近24%的回购用户，我们需要对他们进行重点关注，可以为这些用户进行push推送，提示减价信息等各种外部触发手段吸引用户；或者在页面内设置青铜、白银、黄金等各个等级的称号（可通过每日签到、消费金额进行升级）来鼓励用户前来消费。总之，使用各种手段，达到增加用户日活跃量(DAU)、品牌影响力和用户粘性(留存)这三个指标的效果。
2.2 男女用户之间的消费频次是没有差异，因此运营人员在考虑推送商品时不需要考虑男女性别问题，对用户都可以进行推荐促销。
2.3 进行多次购买的用户最近消费时间和最早一次消费时间之间的差值表现来看，以消费时间差的平均值为统计标准，间隔大于16天的用户我们可以采用激励的措施（签到给金币，金币抵消部分RMB、购买优惠券等）吸引用户回归，间隔小于16天的用户我们可以采取更高的消费手段（满500减30，付费的落地页面更流畅等），以此吸收更多的流量用户。
2.4 不同年龄段的消费者消费金额和消费频次之间是不同的，重点注重20-50之间的人群，对这部分人的喜好，兴趣，购买时间，购买时间间隔等细分特征进行更全面的用户画像处理。
2.5 进行了帕累托原则的应用，发现几乎符合前20%用户付出了80%的消费额的规则，因此要照顾好大R用户的需求。
