一、分析背景和目的

浏览、收集、购物车和订单是电子商务最基本的环节。通过对用户四种行为数据的分析，本项目描述了APP运营现状，探索用户行为规律，希望将这些规律与营销策略相结合，实现更准确的营销，达到增加销量的目的。

二、分析思路

用户(人)、商品(商品)APP解决以下问题：

如何为流量的数量和质量付费？用户的数量和比例如何？用户活跃？哪些用户是高价值用户？哪些用户可以引导消费者活动时间，规律用户偏好商品交易量的贡献？交易环节的转化率和跳转率，流程和页面是否合理？

分析用到RFM模型、AARRR漏斗模型

三、分析内容1. 数据清洗

导入数据

通过下载的数据集navicat导入 mysql，由于原始数据集有超过1亿条数据，因此只导入了2万条。建表已在导入过程中完成。

缺失值处理

SELECT COUNT(id),COUNT(user_id),COUNT(item_id),COUNT(category_id),COUNT(behavior_type),COUNT(timestamp_) FROM user_behavior;

用count函数统计每个字段的数量为2万，无缺失值

一致化处理

时间字段是时间戳格式，分析操作不方便，因此将其与格式化为格式datetime、date、time三种格式。

先加三个字段

ALTER TABLE user_behavior ADD COLUMN datetime_ VARCHAR(30) NOT NULL;ALTER TABLE user_behavior ADD COLUMN date_ VARCHAR(10) NOT NULL;ALTER TABLE user_behavior ADD COLUMN time_ VARCHAR(8) NOT NULL;

赋值三个字段

UPDATE user_behavior SET datetime_=FROM_UNIXTIME(timestamp_);UPDATE user_behavior SET date_ = LEFT(datetime_,10);UPDATE user_behavior SET time_ = SUBSTRING(datetime_,12,8);

添加方便id自增主键设置字段

ALTER TABLE user_behavior ADD COLUMN id INT(21) auto_increment PRIMARY KEY;

异常值处理

date_从2017年11月25日到2017年12月3日，处理数据集的时间范围被删除。

SELECT COUNT(id) FROM user_behavior WHERE date_ NOT BETWEEN '2017-11-25' AND '2017-12-03';DELETE FROM user_behavior WHERE date_ NOT BETWEEN '2017-11-25' AND '2017-12-03';

清理991条异常值

behavior_type处理有四个值：pv fav buy cart，删除范围外的数据。

SELECT COUNT(id) FROM user_behavior WHERE behavior_type NOT IN ('pv','fav','buy','cart');

behavior_type没有异常值

2. 用户分析

1)用户数量，交易用户数量

SELECT COUNT(DISTINCT user_id) as 用户数量 FROM user_behavior;SELECT COUNT(DISTINCT user_id) as 交易用户数量 FROM user_behavior WHERE behavior_type = 'buy';

用户19544，交易用户13330，交易用户68.21%的交易用户比例超过用户总数的一半。

2）用户PV、UV、日常生活，成交转化率

由于是购物场景，将活动定义为购买，日常活动是指每天有购买行为的用户数量；交易率是指到达商店并产生购买行为的人数与到达商店的人数之比，即UV / 有购买人数。

SELECT COUNT(id) as 总PV FROM user_behavior WHERE behavior_type = 'pv';SELECT date_ as 日期,COUNT(id) as PV FROM user_behavior WHERE behavior_type = 'pv' GROUP BY date_;

从图中可以看出，11-26略有下降，12-2、12-3大幅上升。为什么12-2、12-3大幅上升？首先猜测是周末的原因。11月25日 - 12月3日对应的周几如下:

可以看出，11月25日和26日都是周末，但变化很小。因此，12月2日和3日用户行为数量明显增加的原因不是周末。进一步猜测是否是因为假期？查看日历，我发现虽然2017年12月2日是下元节，但中国人没有这个节日的习惯，而不是因为节日。既然原因与时间无关，我继续猜测有两个原因：

12月2日和32天淘宝可能会有促销活动.12月12日双十二促销活动

由于没有更多的时间数据，无法验证。

SELECT COUNT(DISTINCT user_id) as 总UV FROM user_behavior WHERE behavior_type = 'pv';SELECT date_ as 日期,COUNT(DISTINCT user_id) as UV FROM user_behavior WHERE behavior_type = 'pv' GROUP BY date_;

UV总用户为19463，总用户为19544，平均每个用户有91个PV，即91次浏览商品细节。

从图中可以看出，12月1日至12月2日大幅增长，其余时间变化平缓PV趋势大致一致。

SELECT date_ as 日期,COUNT(DISTINCT user_id) as 日活 FROM user_behavior WHERE behavior_type = 'buy' GROUP BY date_;

从图中可以看出，12月2日和3日的生活明显高于其他生活。11月25日和26日是周末，但日常生活低于工作日PV和UV不同的规则暂时放下，因为没有更多的时间数据。

SELECT COUNT(DISTINCT user_id) 购买者数 FROM user_behavior WHERE behavior_type = 'buy';

总UV19463年，总购买人数为13330，交易转化率为13330/19433=68.49%表明商品对用户很有吸引力。

3) 用户行为时间规律

日维度分析

SELECT date_ 日期,SUM(CASE WHEN behavior_type='pv' THEN 1 ELSE 0 END) 浏览量, SUM(CASE WHEN behavior_type='fav' THEN 1 ELSE 0 END) 收藏量, SUM(CASE WHEN behavior_type='cart' THEN 1 ELSE 0 END) 购物车, SUM(CASE WHEN behavior_type='buy' THEN 1 ELSE 0 END) 购买单量FROM user_behaviorGROUP BY date_ ORDER BY date_

可以看出，数量上的浏览量 > 加入购物车 > 收藏量 > 购买单量；12月2日和3日，时间上的四种行为都有统一的规律，其余时间变化缓慢。

小时维度分析

为便于分析，添加小时数据，新增一列time_截取小时数据的字段。

ALTER TABLE user_behavior ADD COLUMN hour_ VARCHAR(2);UPDATE user_behavior SET hour_ = LEFT(time_,2);

现在有了小时数据，我们开始分析每小时不同用户行为的规律。

SELECT hour_ 小时,SUM(CASE WHEN behavior_type='pv' THEN 1 ELSE 0 END) 浏览量, SUM(CASE WHEN behavior_type='fav' THEN 1 ELSE 0 END) 收藏量, SUM(CASE WHEN behavior_type='cart' THEN 1 ELSE 0 END) 购物车, SUM(CASE WHEN behavior_type='buy' THEN 1 ELSE 0 END) 购买单量FROM user_behaviorGROUP BY hour_ ORDER BY hour_

可以看出，这四种操作有相同的规律。谷在4点和5点后开始显著上升，10点后趋于稳定，18点后开始显著上升，22点达到峰值，然后下降。我想知道工作日和周末的趋势是否不同？12月2日和3日的趋势是否相同？接下来，让我们分析这两个问题。

从以上三张图中可以看出，无论是工作日、周末还是数据表现良好的12.3和12.都有类似的规则。因此，从10点到次日0点选择运营活动时间更符合用户活动规则。

用户RFM模型分析

RFM的含义：

R（Recency）最后一次消费时间：表示用户最后一次消费距离当前时间。消费时间越近，客户的价值就越大。一年前消费的用户肯定没有一周前消费的用户那么有价值。F（Frequency）消费频率：消费频率是指用户在统计周期内购买商品的次数。经常购买的用户，即熟悉的客户，必须比偶尔购买的客户更有价值。M（Monetary）消费金额：消费金额是指用户在统计周期内消费的总金额，反映了消费者为企业创造的利润。当然，用户消费越多，价值就越大。

不幸的是，数据集没有用户消费数据，只能统计R和F，将用户分为四类：

高低维度通常以平均值和中位数区分。由于电子商务场景，大部分数据呈长尾分布，80%甚至90%以上集中在低频低范围内。少数用户提供大部分销售，平均数不能很好地反映数据集的特点。长尾用户很容易平均，因此选择中位数作为区分标准。

F值与用户价值成正比，大于中位数为高维度，小于低纬度；因为R值与用户价值成反比，因此中位数大于低纬度，中位数小于高纬度。

2017年11月25日至2017年12月3日，我们将2017年12月4日作为参考日计算R值。

第一步是统计最后一次消费的时间和频率

CREATE VIEW user_behavior_rfm AS SELECT user_id,TIMESTAMPDIFF(HOUR,MAX(datetime_),'2017-12-04') last_buy_time,COUNT(user_id) buy_count FROM user_behavior WHERE behavior_type = 'buy' GROUP BY user_id ORDER BY last_buy_time,buy_count;

第二步，计算中位数，最后消费时间中位数为54，消费频率中位数为2

-- 54SET @rowindex := -1;SELECT ** G(t.z) as last_buy_time_medianFROM (SELECT (@rowindex := @rowindex 1) rowindex,last_buy_time z FROM user_behavior_rfm ORDER BY z) tWHERE t.rowindex IN (FLOOR(@rowindex / 2), CEIL(@rowindex / 2));-- 2SET @rowindex := -1;SELECT ** G(t.x) as last_buy_time_medianFROM (SELECT (@rowindex := @rowindex + 1) rowindex, buy_count x FROM user_behavior_rfm ORDER BY x) tWHERE t.rowindex IN (FLOOR(@rowindex / 2), CEIL(@rowindex / 2));

第三步，用中位数界定高低维度并对用户分类

SELECT user_id, (CASE WHEN last_buy_time < 54 AND buy_count > 2 THEN '重要价值用户' WHEN last_buy_time >= 54 AND buy_count > 2 THEN '重要保持用户' WHEN last_buy_time < 54 AND buy_count <= 2 THEN '一般发展用户' WHEN last_buy_time >= 54 AND buy_count <= 2 THEN '一般挽留用户' ELSE NULL END) user_tagFROM user_behavior_rfm;

接着我们统计各类用户人数

SELECT t.user_tag, COUNT(t.user_id) usre_tag_count FROM (SELECT user_id, (CASE WHEN last_buy_time < 54 AND buy_count > 2 THEN '重要价值用户' WHEN last_buy_time >= 54 AND buy_count > 2 THEN '重要保持用户' WHEN last_buy_time < 54 AND buy_count <= 2 THEN '一般发展用户' WHEN last_buy_time >= 54 AND buy_count <= 2 THEN '一般挽留用户' ELSE NULL END) user_tag FROM user_behavior_rfm) tGROUP BY t.user_tagORDER BY usre_tag_count DESC;

可以看一般挽留用户占比最高，其次是重要价值用户，一般发展用户和中要保持用户次之。重要用户占43%，一般用户占57%，重要用户占比未过一半，还可以提高。

对于不同类型用户我们怎么做营销呢？这里要用到AARRR模型：

AARRR模型包括获取用户、激活用户、用户留存、获取收入、用户自推荐这五个环节。

对于重要价值用户，应该继续保持平台和商品对他们的吸引力，同时可以引导他们自推荐

对于重要保持用户，应该以用户激活为主要目的，通过精准推送、策划趣味性的活动、优化产品设计等手段再次吸引，以激活他们使用

对于一般发展用户，同样以用户激活为主要目的

对于一般挽留用户，应该以用户留存为主要目的，对这类用户做研究，对症下药，提高用户留存率

量化RFM模型

虽然用RFM模型细分了用户群体，获得各类用户数量和分布，但并不知道哪个用户最有价值，接下来我们量化RFM模型，找出最有价值的用户。

第一步，先对数据做归一化。因为R、F、M三个数据中M值往往数值大过R、F，如果只是简单相加，R、F会被忽略。数据集虽然没有M数据，但R值也比F值大不少，也需要归一化处理。

用最常用的min- ** x归一化。

先计算R、F最大值与最小值。R最大、最小值分别是215和0，F最大、最小值分别是72和1

SELECT MIN(u.last_buy_time) lbt_min, MAX(u.last_buy_time) lbt_ ** x, MIN(u.buy_count) bc_min, MAX(u.buy_count) bc_ ** xFROM user_behavior_rfm u;

然后用公式归一化。由于归一化后值在[0，1]之间，我把所有的数据同比放大了100倍，将源数据扩大到了0至100的区间，同时做了四舍五入，让数据可读性更强。经过这样的处理就将数据从原来不同量级无法直观对比，归一化到了0至100的区间，方便直观计算RFM总值。

SELECT user_id, ROUND(((u.last_buy_time - 0) / (215 - 0) * 100), 2) new_lbt, ROUND(((u.buy_count - 1) / (72 - 1) * 100), 2) new_bcFROM user_behavior_rfm uORDER BY new_lbt DESC, new_bc DESC;

最后我们计算总值，RFM总值 = R值*（-1）+F值+100。 因为R值与用户价值成反比，所以乘以-1取相反数，为了避免出现负值，最后加上100。

SELECT user_id, (t.new_lbt * -1 + t.new_bc + 100) total_scoreFROM (SELECT u.user_id, ((u.last_buy_time - 0) / (215 - 0) * 100) new_lbt, ((u.buy_count - 1) / (72 - 1) * 100) new_bc FROM user_behavior_rfm u) tORDER BY total_score DESC;

从结果里可以看到107932用户价值最大。这个用户平时的习惯是什么样呢？我们接着分析他的使用习惯

从图里可以看出来，这个用户习惯与总体规律不一致，11-25、26日有大量购买行为，11-27~11-29有大量浏览、购物车、购买行为，反而是12-2、3使用少；一天当中主要行为发生在0点和9-11点，这和总体规律类似。另外从行为类型上看，他没有收藏行为，购物车行为也较少，基本上现看现买。从日维度的分析和行为类型上分析基本可以看出是个土豪，可惜没有商品描述数据，无法知道他都买哪些商品。

3. 商品分析

1）购买次数分布

SELECT t.buy_count buy_times, COUNT(t.item_id) item_numFROM (SELECT item_id, COUNT(id) buy_count FROM user_behavior WHERE behavior_type = 'buy' GROUP BY item_id) tGROUP BY t.buy_countORDER BY item_num DESC;

可以看到，绝大部分商品只被购买了一次，说明长尾商品居多。

2） 各类行为商品数量排名

浏览量前几名的商品都有哪些呢？

SELECT item_id, COUNT(id) countFROM user_behaviorWHERE behavior_type = 'pv'GROUP BY item_idORDER BY count DESC;

这些商品购买购买情况怎么样呢？

可以看到，浏览量前三名的商品的购买单量在20名外，浏览多的其它商品是否也这样？

将商品分为浏览多购买多、浏览多购买少、浏览少购买多、浏览少购买少四类。电商存在“二八规律”，20%的商品贡献80%的销量，所以以20%为界限，区分浏览和购买的多与少，故四类商品的定义如下：

浏览多购买多：pv量前20%且成交单量也为前20%的品类

浏览多购买少：pv量前20%且成交单量后80%的品类

浏览少购买多：pv量后80%且成交单量前20%的品类

浏览少购买少：pv量后80%且成交单量后80%的品类

接下来统计每类商品数量。

SET @pv_sum = 0;SET @buy_sum = 0;SELECT t2.item_id item_id, t2.pv_count pv_count, t2.item_tab item_tabFROM (SELECT t.item_id item_id, t.pv_count pv_count, t.item_tab item_tab FROM ( SELECT t_pv_count_2.item_id item_id, t_pv_count_2.pv_count pv_count, (CASE WHEN t_pv_count_2.pv_sum/t_pv_total.pv_total < 0.2 THEN '20%' ELSE '80%' END) item_tab FROM (SELECT t_pv_count.item_id item_id, t_pv_count.pv_count pv_count, (@pv_sum := @pv_sum + t_pv_count.pv_count) pv_sum FROM (SELECT item_id, COUNT(id) pv_count FROM user_behavior WHERE behavior_type = 'pv' GROUP BY item_id ORDER BY pv_count DESC) t_pv_count) t_pv_count_2 JOIN (SELECT COUNT(id) pv_total FROM user_behavior WHERE behavior_type = 'pv') t_pv_total ) t WHERE t.item_tab = '20%') t2WHERE t2.item_id IN (SELECT t.item_id FROM ( SELECT t_buy_count_2.item_id item_id, t_buy_count_2.buy_count buy_count, (CASE WHEN t_buy_count_2.buy_sum/t_buy_total. buy_total < 0.2 THEN '20%' ELSE '80%' END) item_tab FROM (SELECT t_buy_count.item_id item_id, t_buy_count.buy_count buy_count, (@buy_sum := @buy_sum + t_buy_count.buy_count) buy_sum FROM (SELECT item_id, COUNT(id) buy_count FROM user_behavior WHERE behavior_type = 'buy' GROUP BY item_id ORDER BY buy_count DESC) t_buy_count) t_buy_count_2 JOIN (SELECT COUNT(id) buy_total FROM user_behavior WHERE behavior_type = 'buy') t_buy_total ) t WHERE t.item_tab = '20%')

浏览多购买多的商品共有994个

用同样的方式统计出，浏览多购买少有2539个，浏览少购买多有1515个，浏览少购买少有23619个

可以看出浏览少购买少的商品占绝大多数，其次是浏览多购买少，浏览少购买多，最后是浏览多购买多。浏览少购买少占绝大多数符合商品购买次数规律一致，再次验证了以长尾商品为主；浏览多购买少居第二，说明浏览量没有很好的转化为购买；浏览少购买多居第三，这类是生活中刚需商品，大概率是标品；浏览多购买多居最末，符合“二八”规律。

对于浏览多购买多的商品可当作促销主推商品，那么浏览多购买多的商品都有哪些？

浏览多购买多商品按浏览量排名如上，可以当作促销主推商品。

2279428商品收藏量最多。

3031354商品加入购物车次数最多。

4. APP分析 1. 流量路径分析

使用漏斗模型。营销漏斗模型常用于营销过程，是将非潜在客户逐步变为客户的转化量化模型。营销漏斗模型的价值在于量化了营销过程各个环节的效率，帮助找到薄弱环节。在电商场景里，我们要做的就是监控每个层级上的用户转化，寻找每个层级的可优化点。对于没有按照流程操作的用户，专门绘制他们的转化模型，缩短路径提升用户体验。

由于数据所限，本次分析通过浏览，加入购物车，购买这个三个行为的转化率，分析购物流程对用户的影响。用户使用收藏功能更多的是记录当下不会购买的商品，所以收藏功能不属于标准的购物环节，不加入分析。

SELECT t.pv_num, t.cart_num, t.buy_num, (ROUND(cart_num / pv_num, 4)) pv_cart, (ROUND(buy_num / cart_num, 4)) cart_buyFROM (SELECT SUM(IF(behavior_type = 'pv', 1, 0)) pv_num, SUM(IF(behavior_type = 'cart', 1, 0)) cart_num, SUM(IF(behavior_type = 'buy', 1, 0)) buy_num FROM user_behavior) t

各环节转化率如上，表格不直观，我们用图可视化

可以看到，浏览-购物车的转化率是6.2%，购物车-购买的转化率是36.25%，这样的数据是好是坏呢？我找了一份行业数据对标

数据来自艾瑞咨询《6·18电商全方位比拼，用户转化篇》，换算之后如下

主流电商6.18浏览-购物车平均转化率是23.65%，是本次分析对象的3.8倍，购物车-购买平均转化率是56.62%，是本次分析对象的1.6倍，而且同样是淘宝数据也比本次分析对象好不少。我认为有两个原因：

本次分析时间范围介于“11.11”和“12.12”之间，这两个大促活动影响了附近时间的销售。对标数据来源于“6.18”大促当日，数据表现比平时好2. 跳失率分析

跳失率，是指顾客通过相应入口进入，只访问了一个页面就离开的访问次数占该页面总访问次数的比例。

由于没有顾客离开这个行为的数据，无法计算只访问了一个页面就离开的访问次数，用只访问一个页面就离开的用户占总用户数比例替代，即在本次分析中：

跳失率 = 只有浏览行为的用户数 / 总用户数

SELECT SUM(IF(t.fav_num = 0 AND t.cart_num = 0 AND t.buy_num = 0, 1, 0)) / COUNT(t.user_id)FROM (SELECT user_id, SUM(IF(behavior_type = 'fav', 1, 0)) fav_num, SUM(IF(behavior_type = 'cart', 1, 0)) cart_num, SUM(IF(behavior_type = 'buy', 1, 0)) buy_num FROM user_behavior GROUP BY user_id) t

如图，跳失率只有5.83%，说明详情页对用户吸引力还不错。

3. APP销售分析

1) 人均成交量

人均成交量 = 订单量 / 用户总数

SELECT COUNT(id) / (SELECT COUNT(DISTINCT user_id) FROM user_behavior)FROM user_behaviorWHERE behavior_type = 'buy';

人均成交量为2.06

2) 复购率

复购率 = 复购用户数 / 总用户数

SELECT COUNT(t.user_id) / (SELECT COUNT(DISTINCT user_id) FROM user_behavior)FROM (SELECT user_id FROM user_behavior WHERE behavior_type = 'buy' GROUP BY user_id H ** ING COUNT(user_id) > 1) t

如图，用户整体复购率为45%，表现还不错

五、结论和建议

用户数量为19544，成交用户数量为13330，成交用户占比为68.21%，总UV是19463，说明商品对用户吸引力还不错。PV、UV趋势一致，周末小幅度增加，12-2、12-3大幅度增加，其余时间平缓。我认为这和“12.12”活动有关。建议营销活动放在周末，类似“12.12”的大活动，提前预热效果更好。日活11-25、11-26趋势与PV、UV不一致，可惜没有更多数据，无法查找原因。用户的浏览量 > 加入购物车 > 收藏量 > 购买单量。时间上四个行为都有统一的规律12月2、3日数量增加，其余时间变化平缓，这与PV、UV趋势一致在一天当中四类操作有相同的规律，波谷在4点，5点后开始明显上升，10点后趋于稳定，18点后开始更明显的上升，到22点达到波峰，之后下降。所以运营活动时间选在在10点到次日0点更符合用户活动规律。用RFM模型细分用户群体，一般挽留用户数量最多，占总体35%，重要价值用户数量第二，占总体28%，一般发展用户数量第三，占总体22%，重要保持用户数量最少，占总体15%。对于重要价值用户，应该继续保持平台和商品对他们的吸引力，同时可以引导他们自推荐。对于重要保持用户，应该以用户激活为主要目的，通过精准推送、策划趣味性的活动、优化产品设计等手段再次吸引，以激活他们使用。对于一般发展用户，同样以用户激活为主要目的。对于一般挽留用户，应该以用户留存为主要目的，对这类用户做研究，对症下药，提高用户留存率。购买一次的商品占整体85.00%，对商品进行分类，浏览多购买少的商品占整体82.39%。商品销售以长尾商品为主，而非爆款用漏斗模型分析流量路径转化率，浏览-购物车的转化率是6.2%，购物车-购买的转化率是36.25%，比6.18行业数据低不少。我认为有两个原因：1）本次分析时间范围介于“11.11”和“12.12”之间，这两个大促活动影响了附近时间的销售；2）对标数据来源于“6.18”大促当日，数据表现比平时好跳失率只有5.83%，说明详情页对用户吸引力还不错。人均成交量为2.06。用户整体复购率为45%