本报告共分为三部分：第一部分为背景介绍，第二部分为最终的分析报告（包含结论与建议、指标说明、指标体系、用户健康度分析、用户复购行为分析5个部分），第三部分为分析报告中涉及到的数据操作思路及SQL代码。

1 背景介绍

现有某电商网站的初创用户运营团队两张原始表，表格包含用户2016年2月1日到2016年4月15日的行为数据及用户信息介绍。现在需要根据这些信息做数据分析，为运营提供支撑。

1.1 表格介绍

1. User_info 用户信息表

2. user_action 用户行为表

两表关系如下图所示：

其中user_info是用户信息表，记录用户基础信息如性别、年龄、注册时间等；user_action是用户行为表，记录用户在平台上的一些行为。

1.2 思路

活跃用户群体是一个产品能够维持长期稳定变现的基础资源。用户运营需要获取用户，并在用户进入我们的产品之后引导他们进行激活、激励用户留存、促进用户转化、鼓励用户再传播。运营的核心在于维持用户的增长趋势、延长用户的生命周期、提高用户的价值。因此，对于现在的用户运营团队来说，数据分析师可以提供的价值有：

1. 搭建运营指标体系。指标体系可以让运营人员能够看到用户规模以及规模的变动状态。
2. 用户分类。按照用户阶段（新用户、激活用户、睡眠用户、流失用户）将用户区分出来，统计每天不同阶段的用户数量，让运营能够了解不同阶段用户规模及其变动情况，设计更加贴切的用户运营方案。
3. 用户行为分析。分析有消费行为的用户群与无消费行为的用户群区别，以便找出关键的特征来促进后续的高质量拉新与精细化运营。

2 用户运营前期分析报告

2.1 结论及建议

2.1.1 基本情况描述

1. 日活用户三月达到高峰后所有下降

周期内(2月1日-4月15日) 日均活跃用户数323人 ，日均增长1%。其中2月日均活跃222人，3月份日均活跃393人。在3月份15日，日活用户达到顶峰588人，之后呈现逐步下降的趋势，4月日均活跃人数为378人。

2. 日活用户消费转化率不错

日活用户消费转化率3.1%，考虑到是初创电商公司（转化率大多介于1%-3%之间），该转化率已基本达标。

3. 每日新增注册用户数较少、转化率较低

2月1日-4月15日共新增用户49人，增长呈现随机性；其中5人进行了下单行为，转化率为1%，转化率较低；后续运营需要首先进行拉新活动促进用户增长，在增长数量稳定之后考虑提高转化。

4. 本周（04.09-04.15）各项指标比上周有所增长

本周总的日活用户数达到1192人，比上周增加14%；另外，日活用户转化率为9%，比上周提高了50%；另外，本周浏览、点击、加购物车及下单这四个流程的数据均有显著增长，分别增长27.6%、26.69%、40%、71.43%，其中下单这一环节增长最大，达到了71%。

2.1.2 建议一：将更多资源投入到新用户的获取上，特别是女性用户的获取

已有数据主要表现出以下两个问题：

1.新增用户过少

1） 虽然流失用户和睡眠用户数量在不断下降，但是激活用户并没有增加；考虑到新增用户较少，总体用户数量呈现下降趋势；

2） 2月1日-4月5日复购率为 8.87%，表明公司处于用户获取阶段，应该将更多资源投入到新用户的获取和转化上；另外考虑到转化率已有3%，因此应优先考虑新用户的获取。

2.女性用户贡献高，但人数占比少

在复购用户中，女性将商品加入购物车的平均次数为21次，比男性（16次）高32%；女性下单次数平均4.6次，比男性(2.8次)高60%；但是在总人数占比上，女性只占8%，男性占比47%，女性用户数量不足男性的1/5。

因此，总的来说用户池较不健康，应该将更多资源投入到新用户的获取上，特别是女性用户的获取。

2.1.3 建议二：关注45岁以上人群的购物需求

在复购用户中，46岁以上人群比16-45岁人群的 详情页浏览次数低27%、点击率低28%，但是加入购物车和下单的行为分别高54%、13%。这可能表明46岁以上人群在进入网站/APP购物时有更明确的购物需求。

2.1.4 建议三：鼓励用户填写完整信息

复购用户中，性别为“未知”的群体下单概率高于其他群体79%；另外该群体浏览详情页、点击的次数也高于其他群体，只有加购物车环节的次数小于46岁以上群体。这可能是因为该类群体在购买前有明确的购物需求，但没有明显的品牌或者型号需求。后续需要结合该群体购买的商品品类进行进一步分析，并推广运营策略让他们补充信息完整度，以进行精细化运营。

2.2 指标及数据说明

统计周期：2016年02月01日至2016年04月15日

2.2.1 指标说明

1.DAU：指当日活跃用户的排重计数；

2.DNU：当日新注册用户的排重统计量；

3.每日消费转化率：每日有下单行为的用户数与每日活跃用户数的百分比；

4.每日新用户消费转化率：每日新注册用户中，有下单行为的用户数与每日新注册用户数的百分比；

5.复购用户数：一段时间内购买次数达两次及以上的用户数

6.复购率：复购用户数/活跃用户数

2.2.2 用户标签说明

1.新用户：注册当天的用户；

2.未激活用户：注册未满一周的用户;

3.激活用户：在注册后的第8到14天有活跃的用户；

4.睡眠用户：

1）用户成为激活用户后，有一周没有活跃；

2）用户从注册后在激活期（第8到第14天）未激活且第14天之后也未活跃

5.流失用户：成为睡眠用户后，有两周没有活跃的用户

2.3 建立用户指标体系

对于初创电商公司来说，首先需要验证的是到底有没有人来买东西，以及在这同时获取买家的数据信息，如订单内容、邮箱、人口统计学变量（年龄、性别、职业等）。因此，转化率可以作为评价初期电商健康程度的一个基本指标，其指访客中发生购买行为的比例。结合现有的表格信息，选取DAU（每日活跃用户数）、DNU（每日新注册用户数）以及转化率来构建指标体系。

2.3.1 DAU

每日活跃用户数：DAU，即每日活跃用户的排重统计量

由上图可知，2月1日-4月15日的日均活跃用户数为323人。其中， 2月日均活跃222人、3月份日均活跃393人，4月日均活跃378人。总体趋势上看，每日活跃用户数从2月开始逐步上升，在3月15日达到高峰588人，之后活跃用户量逐步下降稳定在370人左右，每日活跃用户数日环比增长均值为1%。

2.3.2 DNU

每日新注册用户数：DNU，即每日新注册用户的排重统计量

2月1日-4月15日共增加新注册用户数49人；DNU的变化呈现随机性。

2.3.3 转化率

1）活跃用户的日均消费转化率为3.1% ；

2）新注册用户的转化率为1%，49人中有5人进行了消费，转化率较低。

2.3.4 近两周用户行为分析

本周总的日活用户数达到1192人，比上周增加14%；转化率比上周提高了50%，达到9%；另外，浏览详情页、详情页内点击、加购物车及下单这四个流程的数据均由显著增长，分别增长27.6%、26.69%、40%、71.43%，其中下单这一环节增长最大，达到了71%。

2.4 用户健康程度分析

2.4.1 各类用户变化情况

DAU、DNU很难让我们观察用户池的用户构成。我们无法从这两个指标中知道用户是沉淀下来了逐渐变多，还是在逐渐减少？因此，根据用户注册时间和活跃时间，将用户分为5类，进一步观察用户情况：

1. 新用户: 注册当天的用户
2. 未激活用户: 将注册未满一周的用户作为未激活用户
3. 激活用户：注册后的第8天，用户进入激活期，激活期为注册后的第8到第14天，在激活期回访的用户，则成为激活用户
4. 睡眠用户：若用户成为激活用户后，有一周没有活跃；或从注册后在激活期（第8到第14天）未激活且第14天之后也未活跃的用户作为睡眠用户。（主要做唤醒）
5. 流失用户：将成为睡眠用户后，有两周没有活跃的用户作为流失用户。（虽然同为唤醒范围，但是流失用户的唤醒难度要大于睡眠用户，在预算较少的唤醒活动中不予考虑。）

不同类型用户分布情况如下：

流失用户(绿色）和睡眠用户（黄色）随着时间推进有逐步下降的趋势；但是日均新增为1人，日均非激活用户10人，激活用户为0人，三者没有呈现上升趋势。总的来说用户池较不健康。

2.4.2 本周新用户与激活用户行为对比

不同性别对比：

不同年龄段对比：

本周一位新用户在年龄方面与其他激活用户相吻合，处在16-25岁年龄段；新用户各项行为指标优于激活用户，在各个环节均表现地更为活跃。

2.5 用户复购分析

2.5.1用户复购基本情况

对于用户运营来说，由于获客成本居高不下，提升用户复购是将用户价值最大化的关键。那么我们现阶段在关注转化率的同时是否需要关注复购率？《精益数据分析》一书中对复购率有精确的划分：

90天内重复购买率达到1%~15%；说明你处于用户获取模式；把更多的精力和资源投入到新用户获取和转化；
90天内重复购买率达到15~30%；说明你处于混合模式；平衡用在新用户转化和老用户留存、复购上的精力和资源；
90天内重复购买率达到30%以上；说明你处于忠诚度模式；把更多的精力和资源投入到用户复购上；

2月1日-4月5日复购率为 8.87%，说明我们处于用户获取模式，应该将更多资源投入到新用户的获取和转化上。

另外，在性别及各阶段年龄构成上，复购用户和非复购用户没有显著差别。

2.5.2 不同性别复购用户与非复购用户行为特质

复购用户在各个行为阶段均比非复购用户更为积极：复购用户详情页的浏览次数和点击次数均为非复购用户的2倍，而下单数量超过非复购用户的10倍。另外，对于复购用户，在浏览次数和点击次数没有显著差别的前提下，女性比男性更容易将商品加入购物车以及下单购买。

2.5.3 不同年龄复购用户与非复购用户行为特质

总体上来看，无论哪个年龄段，复购用户在各项行为指标上均比非复购用户活跃。在复购用户中，相比于16-45岁人群，46岁以上人群的PV比16-45岁人群低27%、点击率低28%，但是加入购物车和下单的行为分别高54%、13%，这可能表明46岁以上人群在进入网站/APP购物时有更明确的购物需求。

在复购用户中，未知年龄段的群体在下单环节概率高于其他群体79%，此外，该群体浏览详情页、点击的次数也高于其他群体，只有加购物车环节的次数小于46岁以上群体。这可能是因为该类群体在购买前有明确的购物需求，但没有明显的品牌或者型号需求。后续需要结合该群体购买的商品品类进行进一步分析，并推广运营策略让他们补充信息完整度，以进行精细化运营。

3 数据操作及代码

3.1 基本情况代码（ Hive）

3.1.1 DAU

每日活跃用户数：DAU，即每日活跃用户的排重统计量

create table DAU as
select to_date(action_time) dates, count(distinct user_id) DAU
from user_action a
group by to_date(action_time)
order by dates;

3.1.2 DNU

每日新注册用户数：DNU，即每日新注册用户的排重统计量

create table DNU_1 as
select to_date(i.reg_time) dates, count(distinct i.user_id) DNU
from user_info i
where reg_time>='2016-01-31'
group by to_date(i.reg_time)
order by dates;

3.1.3 每日消费转化率

即每日所有用户的有下单行为的用户数与每日活跃用户数的百分比

create table consumer_convertion as
select t1.dates, t1.count_type4/t2.DAU consumer_convertion
from
(select to_date(action_time) dates, count(distinct user_id) count_type4
from user_action
where type = 4
group by to_date(action_time)) t1
left join
(select to_date(action_time) dates, count(distinct user_id) DAU
from user_action
group by to_date(action_time)) t2
on t1.dates = t2.dates
order by t1.dates;

3.1.4 每日新用户消费转化率

即每日新注册用户中，有下单行为的用户数与每日新注册用户数的百分比

create table newuser_convertion as
select date(reg_time) dates,count(distinct i.user_id) as dnu,
count(distinct if(type=4,i.user_id,Null)) as dnu_type4,
concat(round(count(distinct if(type=4,i.user_id,Null))/count(distinct i.user_id) *100,2),"%") as newuser_convertion
from user_action a
left join user_info i
on a.user_id = i.user_id
where date(reg_time) >='2016-01-31'
group by date(reg_time)
order by date(reg_time);

3.1.5 分析各指标变动情况

在用户行为表中，将每日的DAU、DNU、每日消费转化率、每日新用户消费转化率统计出来，并比较日环比。

create table index_change as
select dates, DAU, DNU, cv, nv,
lag(DAU) over(order by dates) DAU_1bf,
lag(DNU) over(order by dates) DNU_1bf,
lag(cv) over(order by dates) cv_1bf,
lag(nv) over(order by dates) nv_1bf,
DAU/lag(DAU) over(order by dates) DAU_rhb,
DNU/lag(DNU) over(order by dates) DNU_rhb,
cv/lag(cv) over(order by dates) cv_rhb,
nv/lag(nv) over(order by dates) nv_rhb
from(
--计算每日DAU、DNU、消费转化率和新用户消费转化率
select date(action_time) dates, count(distinct a.user_id) DAU, count(distinct i.user_id) DNU,
count(distinct if(type=4,a.user_id,null))/count(distinct a.user_id) cv,
count(distinct if(type=4,i.user_id,null))/count(distinct i.user_id) nv
from user_action a
left join user_info i
on a.user_id = i.user_id and date(reg_time) = date(action_time)
where date(action_time) >= '2016-02-01'
group by date(action_time)
) a
order by dates;

3.1.6 近两周用户行为分析

按周为单位分析最近两周的用户量变化、人均浏览数变化、人均点击数变化、人均加购物车数变化、人均下单数变化、购买转化率变化

create table week_change as
select if(a.action_time > '2016-04-08','this_week','last_week') week,
count(distinct a.user_id) DAU,
round(count(if(a.type=1,user_id,null))/count(distinct a.user_id),2) avg_pv,
round(count(if(a.type=6,user_id,null))/count(distinct a.user_id),2) avg_click,
round(count(if(a.type=2,user_id,null))/count(distinct a.user_id),2) avg_cart,
round(count(if(a.type=4,user_id,null))/count(distinct a.user_id),2) avg_order,
round(count(distinct if(a.type=4,user_id,null))/count(distinct a.user_id),2) order_conversion
from
(select *
from user_action a
where date(action_time)>'2016-04-01') a --最近两周行为数据
group by if(a.action_time > '2016-04-08','this_week','last_week');

将结果存到本地

insert overwrite local directory '/home/hadoop/datas/user_action_analysis'
row format delimited fields terminated by '\t'
select * from week_change

3.2 用户健康程度判断

3.2.1 判断用户池健康程度

按用户行为，将每日不同阶段用户规模统计出来,分析近期我们用户规模的变动趋势，判断我们的用户池的健康程度：

如果新用户、未激活用户、激活用户数增加，而睡眠用户和流失用户减少，说明我们的用户规模呈上升趋势；反之，则呈下降趋势

--建立一个宽表
create table user_info_action as
select distinct i.user_id, date(i.reg_time) reg_date, date(a.action_time) action_date
from user_info i
left join user_action a
on i.user_id = a.user_id;

-- 建立一个日期表（代表最终结果期望的一个输出格式）
--(如果不事先建立dates表格，在做题时直接用left join+where会存在一些问题：由于where执行顺序在select之前，如果where筛选掉一些数据，那么select中 date(a.action_time)所得出的日期就不完整)
create table dates as
select date(a.action_time) dates
from user_action a
group by date(a.action_time);
--1. 新增用户
create table dnu as
select d.dates,
(select count(distinct i.user_id)
from user_info i
left join user_action a
on a.user_id = i.user_id
where date(i.reg_time)=d.dates) dnu
from dates d;

--2. 未激活用户
create table unactive_user as
select d.dates, (
select count(distinct ia.user_id)
from user_info_action ia
where datediff(ia.reg_date,d.dates) < 7 and datediff(ia.reg_date,d.dates) >=0
) count_unactive
from dates d;
-- 3. 激活用户
create table active_user as
select d.dates, (
select count(distinct user_id)
from user_info_action ia
where datediff(ia.reg_date,ia.action_date) > 7 and datediff(ia.reg_date,d.dates) <=14 and datediff(ia.action_date,d.dates)>=0
) count_active
from dates d;

-------4. 睡眠用户及流失用户
create table sleep_loss_user as
select d.dates,
(select count(distinct user_id)
from user_info_action ia
where datediff(ia.action_date,d.dates)>7 or datediff(ia.reg_date,d.dates)>14
) sleep_users,
(select count(distinct user_id)
from user_info_action ia
where datediff(ia.action_date,d.dates)>14) loss_users
from dates d;

3.2.2 判断新用户群体是否健康

对比分析最近一周新用户群体与激活用户群体的人群画像、行为数据特点，探索近期产品引入的新用户群体是否健康。

--思路：1. 找出激活用户的人群画像和行为特点：首先两表连接，根据激活用户定义筛选出激活用户及其行为数据，然后汇总统计这些人群的行为数据特点，按照人群画像分组（年龄、性别 2. 统计新用户群体行为数据特点

create table newuser_action as
select age_between, sex gender, count(distinct i.user_id) user_count,
round(count(if(type=1,i.user_id,null))/count(distinct i.user_id),2) avg_pv,
round(count(if(type=6,i.user_id,null))/count(distinct i.user_id),2) avg_click,
round(count(if(type=2,i.user_id,null))/count(distinct i.user_id),2) avg_cart,
round(count(if(type=4,i.user_id,null))/count(distinct i.user_id),2) avg_order,
round(count(distinct if(type=4,i.user_id,null))/count(distinct i.user_id),2) order_conversion
from
(select *
from user_info
where date(reg_time) > '2016-04-08' and date(reg_time)< '2016-04-15') i
inner join
(select *
from user_action
where date(action_time) > '2016-04-08') a
on i.user_id = a.user_id
group by age_between,sex
grouping sets(age_between,sex);

-- 激活用户人群画像及行为特点
create table activeuser_action as
select age_between, sex gender, count(distinct i.user_id) user_count,
round(count(if(type=1,i.user_id,null))/count(distinct i.user_id),2) avg_pv,
round(count(if(type=6,i.user_id,null))/count(distinct i.user_id),2) avg_click,
round(count(if(type=2,i.user_id,null))/count(distinct i.user_id),2) avg_cart,
round(count(if(type=4,i.user_id,null))/count(distinct i.user_id),2) avg_order,
round(count(distinct if(type=4,i.user_id,null))/count(distinct i.user_id),2) order_conversion
from
(select *
from user_info
where date(reg_time) > '2016-04-01' and date(reg_time)< '2016-04-8') i
inner join
(select *
from user_action
where date(action_time) > '2016-04-08') a
on i.user_id = a.user_id
group by age_between,sex
grouping sets(age_between,sex);

3.3 用户复购分析

3.3.1 根据复购率判断产品模式

用户复购率 = 复购用户数 / 活跃用户数；

--复购:同一个id type=4次数大于2
create table fugou as
select concat(round(100*count(if(order_num>1,user_id,null))/count(distinct user_id),4),'%') fg_rate
from
(select user_id, count(if(type=4,user_id,null)) order_num
from user_action a
where date(action_time) > '2016-01-31'
group by user_id) fugou;

结果表明复购率为8.87%，处于用户获取模式，应该将更多精力和资源投入到新用户获取和转化。

复购用户数 ：一段时间内购买次数达两次及以上的用户数

3.3.2 复购用户特点

结合用户信息表和用户购买行为表，对比复购用户和非复购用户的用户画像、行为特点，探索复购用户的特点；

--复购用户用户画像及行为特点
create table fugou_user as
select age_between, sex gender, count(distinct i.user_id) user_count,
round(count(if(type=1,i.user_id,null))/count(distinct i.user_id),2) avg_pv,
round(count(if(type=6,i.user_id,null))/count(distinct i.user_id),2) avg_click,
round(count(if(type=2,i.user_id,null))/count(distinct i.user_id),2) avg_cart,
round(count(if(type=4,i.user_id,null))/count(distinct i.user_id),2) avg_order,
round(count(distinct if(type=4,i.user_id,null))/count(distinct i.user_id),2) order_conversion
from user_info i
inner join user_action a
on i.user_id = a.user_id
where i.user_id in
( --找出复购用户的user_id
select user_id
from
(select user_id, count(if(type=4,user_id,null)) order_num
from user_action a
where date(action_time) > '2016-01-31'
group by user_id) fugou
where order_num >=2
)
group by age_between,sex
grouping sets(age_between,sex);

--找出非复购用户
create table nonefugou_user as
select age_between, sex gender, count(distinct i.user_id) user_count,
round(count(if(type=1,i.user_id,null))/count(distinct i.user_id),2) avg_pv,
round(count(if(type=6,i.user_id,null))/count(distinct i.user_id),2) avg_click,
round(count(if(type=2,i.user_id,null))/count(distinct i.user_id),2) avg_cart,
round(count(if(type=4,i.user_id,null))/count(distinct i.user_id),2) avg_order,
round(count(distinct if(type=4,i.user_id,null))/count(distinct i.user_id),2) order_conversion
from user_info i
inner join user_action a
on i.user_id = a.user_id
where i.user_id not in
(
select user_id
from
(select user_id, count(if(type=4,user_id,null)) order_num
from user_action a
where date(action_time) > '2016-01-31'
group by user_id) fugou
where order_num >=2
)
group by age_between,sex
grouping sets(age_between,sex);

3.3.3 近一周复购用户激活、流失情况

结合最后一周的分阶段用户标签，分析近期复购用户的用户激活、用户流失的情况

create table recent_week_fugou as
select d.dates,
--计算用户激活量
( select count(distinct fugou_users.user_id)
from
(select a.user_id, date(action_time) action_time, date(reg_time) reg_time
from user_action a
inner join user_info i
on a.user_id = i.user_id
where date(action_time) > '2016-01-31' and type = 4) fugou_users
where datediff(date(action_time),d.dates) >= 7 and datediff(date(reg_time),d.dates) <=13 and datediff(date(action_time),d.dates)>=0
) count_active_fugou
from dates d
where d.dates > '2016-04-08';
--计算用户流失量
create table recentweek_fugou_ls as
select d.dates,
--计算每个日期分别有多少流失的复购人群
( select count(distinct fugou_users.user_id)
from
--找出带复购人群：一条购买记录为一行。
(select a.user_id, date(action_time) action_time, date(reg_time) reg_time
from user_action a
inner join user_info i
on a.user_id = i.user_id
where date(action_time) > '2016-01-31' and type = 4) fugou_users
where datediff(date(action_time),d.dates)>14) loss_user_fugou
from dates d
where d.dates > '2016-04-08';