SQL数据分析实操:用户行为怎么分析才有价值

国内新闻 浏览(557)

2019-09-18 13: 15: 39独特的机器

上一篇

使用Excel,今天使用SQL进行分析!

什么,sql也可以做分析?

通用数据清理,预处理,数据分类,数据过滤,小计和数据透视操作可以与SQL相同的方式实现(可视化除外,这需要在Excel中显示)。 SQL不仅可以从数据库中读取数据,还可以通过不同的SQL函数语句直接返回所需的结果,从而大大提高了它们在客户端应用程序中的计算效率。

但是,此过程需要掌握SQL!

本文使用从MySQL提取的数据,并编写SQL的数据处理方法以对淘宝数据执行用户分析。

首先,数据来源和说明

本文选择2014年11月18日至2014年12月18日的8477个随机用户的个行为数据。数据集的每一行代表一个用户行为,共6列。

列字段包含以下内容:

User_id:用户ID item_id:产品ID行为类型:用户行为类型(包括点击,收藏,添加购物车,购买四种行为,分别由数字1、2、3、4表示)user_geohash:地理位置(值为空)item_category:类别ID(项目所属的类别)时间:发生用户行为的时间

第二,问问题

1,总体用户的购物情况

Pv(总流量),每日平均流量,uv(用户总数),具有购买行为的用户数量,用户的购物状态和回购率?

2.用户行为转换渠道

单击-添加购物车-收藏夹-每个链接的转换率是多少?购物车的废弃率是多少?如何改善?

3.高购买率和0购买率的人的特征是什么

4.根据时间维度了解用户的行为习惯

5.基于RFM模型的用户分析

第三,数据清理

1.导入数据

由于数据量超过一百万,因此通过数据库管理工具Navicat将数据集导入MySQL数据库将比较慢。我使用ETL工具水壶执行派生,这可以提高派生效率并促进随后的报表自动处理。对于用户。

2,欠值处理

item_category列指示地理位置信息。由于数据具有大量的空值并且位置信息已加密,因此很难研究,因此稍后不对item_category列进行分析。

3.数据一致性处理

由于时间字段的时间包含(年-月-日)和小时,为便于分析,该字段分为2个字段,即日期列(日期)和小时列(时间)。

{!-PGC_COLUMN-}

由于behavior_type列的四种行为类型分别使用1、2、3、4来单击,收集,添加购物车和购买四种行为,为了方便地查看数据,请将'1、2、3、4替换为' pv”,“收藏夹”,“购物车”,“购买”。

通过查询表结构,您可以看到date列date列不是日期类型:

将日期列更改为日期类型:

第四,建立模型并分析问题

1.用户总体购物情况

(1)pv(总流量)

(2)平均每日流量

(3)uv(用户总数)

(4)购买行为的用户数量

(5)用户的购物情况

(6)回购率:购买两次以上的用户占购买用户的比例

2.用户行为转换渠道

在购物过程中没有要收集和添加到购物车的连续点,因此可以将这两个链接放在一起作为购物过程中的一个步骤。最后,用户购物行为的每个链接的转换率如下:

不同的行业转化率会有所不同。根据2012年的一项研究,互联网上的平均转化率为2.13%(来自《精益数据分析》的数据),图中所示的转化率为1.04。 %,行业平均水平与淘宝移动用户行为转化率之间有很大差异,还有很大的增长空间。

3.高购买率和低购买率的人的特征是什么?

高购买率用户特征:

从以上结果可以看出,用户的点击率不是最高,这些用户的数量和购物车的数量也很小。通常,可以直接购买它们,而无需单击5次以上,因此可以推断出这些用户。对于理性的消费者而言,有明确的购物目标,即缺乏购买的形式,并且很少被商店的广告或促销所吸引。

购买率低是用户特征:

从以上结果可以看出,低购买率可分为两类,一类是点击次数少,另一类原因是此类用户可能是不太可能购物或不喜欢的用户。互联网,并且可以得到指导。另一方面,从商品的角度看,产品定价过高还是设计不合理?第二类用户是点击率高,收藏或购物车高的用户,并且此类用户可能正在为商家促销。准备,下订单的欲望更少,自控能力更强,更多的考虑或不付款,购物更加困难。

4.根据时间维度了解用户的行为习惯

(1))白天用户的活动时间分布

可以看出,用户活动每天从0迅速减少到5,并降至当天活动的最低值。用户活动从6迅速增加到10,用户活动从10稳定到18,从17点稳定到23点,用户活动迅速上升,达到一天的最高值。

(2)一周中的活跃用户分布

由于第一周和第五周的数据不完整,因此在此数据分析中不考虑两周的数据。

从以上结果可以看出,每周的用户活动相对稳定,每个星期五的活动将略有减少,而周末将逐渐增加。其中,由于双十二电子商务的推广,用户活动在星期五急剧增加。

5.根据RFM模型确定有价值的用户

RFM模型是衡量客户价值和客户获利能力的重要工具和手段。构成数据分析的最佳指标的三个要素是:

R-Recency(最后购买时间)

频率(消耗频率)

M-Money(消费金额)

对于RFM模型,我在《案例:手把手教你搭建RFM客户价值分析模型》

之前写了一篇文章。

由于数据源没有相关的金额数据,因此暂时通过R和F数据对客户价值进行评分。

(1)计算R-Recency

由于数据集包含从2014年11月18日到2014年12月18日的时间,因此此处以2014年12月19日为计算日期,而客户最近购买行为的日期以2014年12月19日为间隔。几天,对时间间隔进行排名,天数越少,客户价值越大,排名越高。

(2)计算F频率

首先计算每个用户的购买频率,然后对购买频率进行排名。频率越高,客户价值越大,排名越高。

(3)为用户评分

4,330名具有购买行为的用户根据排名进行分组,分为四组,第一季度的用户获得4分,第一季度到第二季度(即两分)排名1)用户获得3分,前两个季度得分最高,前三个季度得分2分,其余用户得分1分。根据该规则,分别对用户时间间隔等级评分和购买频率等级评分。最后,将两个分数合并在一起,作为用户的最终分数。

通过评分,您可以了解每个客户的特征并实现差异化营销。例如,user_value=44的用户需要注意关键用户;对于忠诚度高且购买力不足的用户,可以使用适当的折扣或捆绑销售来增加购买频率。

(4)水壶生产报告自动化

为了监视每月用户分数的变化,并推测客户消费的交易状态,结合数据库使用ETL工具水壶来实现定时输出分数结果:

这里,水壶的实现过程不再详细描述。

五,结论

1.整体转化率仅为1%。用户单击后,收集和购物车的转化率为5%。必须提高用户的购买意愿,并且可以通过活动和精准营销来促进。

2.高购买率和低点击量的用户属于理性购物者,目标明确,不受促销和广告影响;购买率低的用户可以视为等待或受限制的用户组,下订单的愿望更高。自我控制越来越少,购物就更加困难。

3.大多数用户的主要活动时间是从10:00到23:00,达到一天的高峰时间从19:00到23:00。该活动在每个星期五都有所减少,但周末开始活跃起来。可以根据用户的活跃时间段来准确地推动商人的折扣或促销,并且可以提高购买率。

4.通过R和F数据对用户的行为进行评分,并对每个用户进行精确的营销。它还可以监视R和F的数据,估计客户消费的交易状态,并恢复丢失的客户。

上一篇

使用Excel,今天使用SQL进行分析!

什么,sql也可以做分析?

通用数据清理,预处理,数据分类,数据过滤,小计和数据透视操作可以与SQL相同的方式实现(可视化除外,这需要在Excel中显示)。 SQL不仅可以从数据库中读取数据,还可以通过不同的SQL函数语句直接返回所需的结果,从而大大提高了它们在客户端应用程序中的计算效率。

但是,此过程需要掌握SQL!

本文使用从MySQL提取的数据,并编写SQL的数据处理方法以对淘宝数据执行用户分析。

首先,数据来源和说明

本文选择2014年11月18日至2014年12月18日的8477个随机用户的个行为数据。数据集的每一行代表一个用户行为,共6列。

列字段包含以下内容:

User_id:用户ID item_id:产品ID行为类型:用户行为类型(包括点击,收藏,添加购物车,购买四种行为,分别由数字1、2、3、4表示)user_geohash:地理位置(值为空)item_category:类别ID(项目所属的类别)时间:发生用户行为的时间

第二,问问题

1,总体用户的购物情况

Pv(总流量),每日平均流量,uv(用户总数),具有购买行为的用户数量,用户的购物状态和回购率?

2.用户行为转换渠道

单击-添加购物车-收藏夹-每个链接的转换率是多少?购物车的废弃率是多少?如何改善?

3.高购买率和0购买率的人的特征是什么

4.根据时间维度了解用户的行为习惯

5.基于RFM模型的用户分析

第三,数据清理

1.导入数据

由于数据量超过一百万,因此通过数据库管理工具Navicat将数据集导入MySQL数据库将比较慢。我使用ETL工具水壶执行派生,这可以提高派生效率并促进随后的报表自动处理。对于用户。

2,欠值处理

item_category列指示地理位置信息。由于数据具有大量的空值并且位置信息已加密,因此很难研究,因此稍后不对item_category列进行分析。

3.数据一致性处理

由于时间字段的时间包含(年-月-日)和小时,为便于分析,该字段分为2个字段,即日期列(日期)和小时列(时间)。

{!-PGC_COLUMN-}

由于behavior_type列的四种行为类型分别使用1、2、3、4来单击,收集,添加购物车和购买四种行为,为了方便地查看数据,请将'1、2、3、4替换为' pv”,“收藏夹”,“购物车”,“购买”。

通过查询表结构,您可以看到date列date列不是日期类型:

将日期列更改为日期类型:

第四,建立模型并分析问题

1.用户总体购物情况

(1)pv(总流量)

(2)平均每日流量

(3)uv(用户总数)

(4)购买行为的用户数量

(5)用户的购物情况

(6)回购率:购买两次以上的用户占购买用户的比例

2.用户行为转换渠道

在购物过程中没有要收集和添加到购物车的连续点,因此可以将这两个链接放在一起作为购物过程中的一个步骤。最后,用户购物行为的每个链接的转换率如下:

不同的行业转化率会有所不同。根据2012年的一项研究,互联网上的平均转化率为2.13%(来自《精益数据分析》的数据),图中所示的转化率为1.04。 %,行业平均水平与淘宝移动用户行为转化率之间有很大差异,还有很大的增长空间。

3.高购买率和低购买率的人的特征是什么?

高购买率用户特征:

从以上结果可以看出,用户的点击率不是最高,这些用户的数量和购物车的数量也很小。通常,可以直接购买它们,而无需单击5次以上,因此可以推断出这些用户。对于理性的消费者而言,有明确的购物目标,即缺乏购买的形式,并且很少被商店的广告或促销所吸引。

购买率低是用户特征:

从以上结果可以看出,低购买率可分为两类,一类是点击次数少,另一类原因是此类用户可能是不太可能购物或不喜欢的用户。互联网,并且可以得到指导。另一方面,从商品的角度看,产品定价过高还是设计不合理?第二类用户是点击率高,收藏或购物车高的用户,并且此类用户可能正在为商家促销。准备,下订单的欲望更少,自控能力更强,更多的考虑或不付款,购物更加困难。

4.基于时间维度了解用户的行为习惯

(1)一天中用户的活跃期分布

可以看到,用户的活动从一天的0到5点迅速减少,降至一天的最低活动水平,从6到10点迅速增加,从10到18点稳定。时钟,并从17点迅速增加到23点,达到一天中的最高水平。

(2)每周活跃用户时段的分布

由于第一周和第五周的数据不完整,因此在数据分析中未考虑这两周的数据。

从以上结果可以看出,用户的活动量每周相对稳定,每个周五的活动量略有减少,但在周末逐渐增加。其中,由于双12电子商务的促销活动,周五的用户活动急剧增加。

5.基于RFM模型寻找有价值的用户

RFM模型是衡量客户价值和客户盈利能力的重要工具和手段。构成数据分析最佳指标的三个因素是:

R-新近度

F-频率

M-钱

关于RFM模型,我写了一篇文章《案例:手把手教你搭建RFM客户价值分析模型》。

由于数据源没有相关的金额数据,因此暂时通过R和F数据对客户价值进行分级。

(1)计算R-Recency

由于数据集包含从2014年11月18日到2014年12月18日的时间,因此此处以2014年12月19日为计算日期,而客户最近购买行为的日期以2014年12月19日为间隔。几天,对时间间隔进行排名,天数越少,客户价值越大,排名越高。

(2)计算F频率

首先计算每个用户的购买频率,然后对购买频率进行排名。频率越高,客户价值越大,排名越高。

(3)为用户评分

4,330名具有购买行为的用户根据排名进行分组,分为四组,第一季度的用户获得4分,第一季度到第二季度(即两分)排名1)用户获得3分,前两个季度得分最高,前三个季度得分2分,其余用户得分1分。根据该规则,分别对用户时间间隔等级评分和购买频率等级评分。最后,将两个分数合并在一起,作为用户的最终分数。

通过评分,您可以了解每个客户的特征并实现差异化营销。例如,user_value=44的用户需要注意关键用户;对于忠诚度高且购买力不足的用户,可以使用适当的折扣或捆绑销售来增加购买频率。

(4)水壶生产报告自动化

为了监视每月用户分数的变化,并推测客户消费的交易状态,结合数据库使用ETL工具水壶来实现定时输出分数结果:

这里,水壶的实现过程不再详细描述。

五,结论

1.整体转化率仅为1%。用户单击后,收集和购物车的转化率为5%。必须提高用户的购买意愿,并且可以通过活动和精准营销来促进。

2.高购买率和低点击量的用户属于理性购物者,目标明确,不受促销和广告影响;购买率低的用户可以视为等待或受限制的用户组,下订单的愿望更高。自我控制越来越少,购物就更加困难。

3.大多数用户的主要活动时间是从10:00到23:00,达到一天的高峰时间从19:00到23:00。该活动在每个星期五都有所减少,但周末开始活跃起来。可以根据用户的活跃时间段来准确地推动商人的折扣或促销,并且可以提高购买率。

4.通过R和F数据对用户的行为进行评分,并对每个用户进行精确的营销。它还可以监视R和F的数据,估计客户消费的交易状态,并恢复丢失的客户。