淘宝广告用户行为研究

声明：源文章本人发布在知乎账号：可乐，地址：https://zhuanlan.zhihu.com/p/55208457

数据集地址：

https://tianchi.aliyun.com/dataset/dataDetail?dataId=56

数据集简介：

这是由阿里巴巴公司提供的一个关于淘宝广告的数据集，数据存放在4张表中，如下所示：

表名称	形状	所含字段
raw_sample(原始样本信息)	55k x 5	用户ID，广告ID，时间戳，资源位，是否点击
ad_feature(广告基本信息)	846k x 4	广告ID，商品类别ID，品牌ID，价格
user_profile(用户基本信息）	1048k x 7	用户ID，年龄层，性别，消费档次，购物能力，是否大学生，城市等级
behavior_log(用户行为日志)	249k x 5	用户ID，行为类型，时间，商品类目ID，品牌ID

raw_sample（原始样本信息）

从淘宝网站中随机抽样了114万用户8天内的广告点击日志，构成原始的样本信息。

字段说明如下：

（1）user_id：脱敏过的用户ID（int）；

（2）time_stamp：时间戳(Bigint, 1494032110 stands for 2017-05-06 08:55:10)；

（3）ad_id：脱敏过的广告ID(int)；

（4）pid：资源位；

（5）clk：为0代表没有点击，为1代表点击。

ad_feature(广告基本信息表)

本数据集涵盖了raw_sample中全部广告的基本信息。

字段说明如下：

(1) ad_id：脱敏过的广告ID(int)；

(2) cate_id：脱敏过的商品类别ID；

(3) brand_id：脱敏过的品牌ID；

(4) price: 商品价格。

其中一个广告ID对应一个商品，一个商品属于一个类别，一个商品属于一个品牌。

user_profile(用户基本信息表)

本数据集涵盖了raw_sample中全部用户的基本信息。

字段说明如下：

(1) user_id：脱敏过的用户ID；

(2) gender_code：性别（1:男,2:女）；

(3) age_level：年龄层次(0--6)；

(4) pvalue_level：消费档次（1:低档，2:中档，3:高档）；

(5) shopping_level：购物能力（1:浅层用户,2:中度用户,3:深度用户）；

(6) occupation：是否大学生（1:是,0:否）；

(7) user_city_level：城市层级（1 2 3 4 NULL）。

behavior_log(用户行为日志)

本数据集涵盖了raw_sample中全部用户22天内的购物行为。字段说明如下：

(1) user_id：脱敏过的用户userID（int）；

(2) time_stamp：时间戳(Bigint, 1494032110 表示2017-05-06 08:55:10)；

(3) btag：行为类型, 包括以下四种：

类型	说明
pv	浏览
cart	加入购物车
fav	喜欢
buy	购买

(4)cate_id：脱敏过的商品类别(int)；

(5) brand_id: 脱敏过的品牌类别(int)。

首先在Navicat中建立数据库ad_display，然后将Excel中4张表通过导入向导导入至ad_display。

CREATE DATABASE ad_display;

原始数据为CSV格式，导入后，查询导入表的行数，进行数据完整性检查。

数据分析：

1. 提出问题：

（1）广告资源位的有效性？

（2）分析最具吸引力商品类别和商品品牌、最畅销商品品牌？

（3）用户的留存率，转化率情况？

（4）建立用户画像基本信息。

2. 理解数据：

3.数据清洗：

首先删除名称中的空格对字段进行规范处理。

这里以user id+ time_stamp为首要观测值，会有很多重复的记录。这是由于不同的类型的行为数据是不同部门记录的，在打包到一起的时候，实际上会有小的偏差（即两个一样的time_stamp实际上是差异比较小的两个时间），因此删除重复时间与用户ID，设置唯一标识。

4.构建模型与可视化

4.1 广告资源位的有效性

在一段时间内广告获得的点击次数称之为“点击量”，点击率是指网站页面上某一内容被点击的次数与被显示次数之比，反映了网页上某一内容的受关注程度，经常用来衡量广告的吸引程度。点击量/展现量=点击率。

SELECT pid AS '资源位',COUNT(clk)AS '点击量',
       (COUNT(clk)/(SELECT COUNT(clk)
                      FROM raw_sample ) ) *100 AS '点击率%'
FROM raw_sample
WHERE clk ='1'
GROUP BY pid 
ORDER BY pid DESC;

结论：整体来看，广告资源位有效性并不高，可能需要考虑资源位的合理布局。其中，资源位id430548_1007曝光率较高，约为另一资源位曝光率的2倍。

4.2分析最具吸引力商品类别和商品品牌

SELECT cate_id AS '最具吸引力商品类别',COUNT(cate_id) AS '点击量'
FROM raw_sample AS a LEFT JOIN ad_feature AS b ON a.ad_id =b.ad_id
WHERE a.clk ='1'
GROUP BY cate_id
ORDER BY COUNT(cate_id) DESC
limit 10;

SELECT brand_id AS '最具吸引力品牌',
       COUNT(brand_id) AS '点击量'
FROM raw_sample AS a LEFT JOIN 
     ad_feature AS b ON a.ad_id =b.ad_id
WHERE  a.clk ='1' AND brand_id IS NOT null 
GROUP BY brand_id
ORDER BY COUNT(brand_id) DESC
limit 11;

结论：点击量前十商品品牌和商品类别显示了最受消费者欢迎的商品，其中，最受欢迎商品品牌（除NULL外）为353787，最受欢迎商品类别前三名分别为：6261、6421、5467，两项数据间前十名的变异量数不是很大。

4.3 畅销商品品牌分析

SELECT COUNT(btag)AS '总销量'
FROM ad_feature AS a  JOIN behavior_log AS b ON a.brand_id = b.brand_id
WHERE  btag = 'buy';

SELECT brand_id AS '商品品牌',COUNT(btag) AS '销量'
FROM behavior_log
WHERE btag= 'buy'
GROUP BY brand_id
ORDER BY COUNT(btag) DESC
limit 10;

SELECT SUM(price) AS '总销售额'
FROM ad_feature AS a JOIN behavior_log AS b ON a.brand_id = b.brand_id
WHERE btag = 'buy';

SELECT a.brand_id AS '最佳销售额品牌',
     (CONCAT(SUM(a.price)/10000,'万')) AS'销售额',
       b.time_stamp AS '时间戳'
FROM ad_feature AS a  JOIN behavior_log AS b ON a.brand_id = b.brand_id
WHERE a.brand_id is NOT NULL AND btag = 'buy'
GROUP BY a.brand_id
ORDER BY sum(a.price) DESC
limit 10;

结论：总销售额为11.04亿，销售量、销售额第1名均为品牌370203，370203为主打品牌，贡献了约76%的销售额，前10名商品品牌贡献了92%的销售额。品牌224985销量3名，销售额第7名。

品牌353787销售额为8360万，高居第二，接近第一名，但是销量却未进入前10，可适当降低商品单价；商品品牌388700销量为第二名，销售额未能进入前十，可适当采取提高商品单价策略。

4.3 消费者行为分析

SELECT MAX(price) AS '最高价格',MIN(price) AS '最低价格',AVG( price) AS '平均价格'
FROM ad_feature AS a JOIN behavior_log AS b ON a.brand_id= b.brand_id
WHERE btag = 'buy';

SELECT price AS '价格标签',COUNT(price) AS '销量'
FROM ad_feature AS a JOIN behavior_log AS b ON a.brand_id= b.brand_id
WHERE  btag = 'buy'
GROUP BY price
ORDER BY COUNT(price) DESC
LIMIT 10;

SELECT
SUM(CASE WHEN btag = 'pv' THEN 1 ELSE 0 END) AS '浏览人数',
SUM(CASE WHEN btag = 'cart' THEN 1 ELSE 0 END) as '加购物车人数',
SUM(CASE WHEN btag = 'fav' THEN 1 ELSE 0 END) AS '收藏人数',
SUM(CASE WHEN btag = 'buy' THEN 1 ELSE 0 END) AS '购买人数'
FROM behavior_log;

SELECT SUM(CASE WHEN btag = 'pv' THEN 1 ELSE 0 END) AS '浏览人数',
       SUM(CASE WHEN btag = 'cart' THEN 1 ELSE 0 END) AS '加入购物车人数',
       SUM(CASE WHEN btag = 'fav' THEN 1 ELSE 0 END) AS '收藏人数',
       SUM(CASE WHEN btag = 'buy' THEN 1 ELSE 0 END) AS '购买人数',
       ((SUM(CASE WHEN btag = 'cart'  THEN 1 ELSE 0 END) + SUM(CASE WHEN btag = 'fav' THEN 1 ELSE 0 END) )/ 
       SUM(CASE WHEN btag = 'pv' THEN 1 ELSE 0 END) )*100 AS '节点1转化率%',
       (SUM(CASE WHEN btag = 'buy' THEN 1 ELSE 0 END)  / 
       (SUM(CASE WHEN btag = 'cart'  THEN 1 ELSE 0 END) + SUM(CASE WHEN btag = 'fav' THEN 1 ELSE 0 END))  )*100 AS '节点2转化率%',
       (SUM(CASE WHEN btag = 'buy' THEN 1 ELSE 0 END) / SUM(CASE WHEN btag = 'pv' THEN 1 ELSE 0 END ) ) * 100 AS '渠道转化率%'
FROM behavior_log ;

SELECT btag AS '用户行为', COUNT(btag) AS '次数',time_stamp AS '时间戳'

FROM behavior_log

GROUP BY btag;

结论：价格在50元--200元间的商品更受欢迎，其中最畅销的价格标签在100-170元左右。商品平均价格为657.05元，价格间全距较大。

渠道曝光量为23.3万，有4913名消费者因曝光量转化为最终用户，渠道转化率为2.1%，其中第一环节流失率约95%，第二环节流失率约53%。

将时间戳进行转化，可以发现用户浏览广告行为大多发生在时间2017-05-03 22:00附近，加购物车和收藏时间在2017-05-03 15:00左右，购买行为大多发生在2017-05-03 20:50附近。

4.4 用户画像基本信息

SELECT SUM(CASE WHEN gender_code = '1' THEN 1 ELSE 0 END) AS '男性客户',
       SUM(CASE WHEN gender_code = '2' THEN 1 ELSE 0 END) AS '女性客户',
       SUM(CASE WHEN occupation = '1' THEN 1 ELSE 0 END) AS '大学生客户'
FROM user_profile AS a LEFT JOIN behavior_log AS b ON a.user_id = b.user_id
WHERE  btag = 'buy';

SELECT age_level'年龄层次',COUNT(age_level) AS '人数'
FROM user_profile AS a JOIN behavior_log AS b ON a.user_id = b.user_id
WHERE  btag = 'buy'
GROUP BY age_level
ORDER BY COUNT(age_level) DESC;

SELECT user_city_level'城市等级',COUNT(user_city_level) AS '该级城市用户数'
FROM user_profile AS a JOIN behavior_log AS b ON a.user_id = b.user_id
WHERE  btag = 'buy'
GROUP BY user_city_level
ORDER BY COUNT(user_city_level) DESC;

SELECT pvalue_level'消费等级',COUNT(pvalue_level) AS '人数'
FROM user_profile AS a JOIN behavior_log AS b ON a.user_id = b.user_id
WHERE btag = 'buy'
GROUP BY pvalue_level
ORDER BY COUNT(pvalue_level) DESC;

SELECT shopping_level'购物能力',COUNT(shopping_level) AS '人数'
FROM user_profile AS a JOIN behavior_log AS b ON a.user_id = b.user_id
WHERE  btag = 'buy'
GROUP BY shopping_level
ORDER BY COUNT(shopping_level) DESC;

结论：女性用户数量约为男性用户的2倍多，大学生约占总用户数量的6.3%。

年龄层次方面，用户多集中在3、4、2、5层次中，仅年龄第3、4层就占总用户数的55.8%，可见定位用户年龄阶段很明显。

消费档次中档人数：197人，低档人数：679人，高档人数：187人，主要为低档用户。

用户最多的在第2级城市，约为第3级城市的2倍。

用户进行购物能力划分后，主要人群为深度用户，中浅层用户共占10%，浅层用户最少。

思考：1、可以建立会员金字塔模型，针对不同级别制定不同的营销策略，通过营销活动唤醒流失以及休眠用户，对于老会员，思考如何提高其忠诚度。

2、思考AARRR模型，分阶段寻找广告投放痛点，制定营销战略。

posted @ 2019-07-26 10:13 可乐5266 阅读(518) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

可乐0526

淘宝广告用户行为研究

公告