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

数据集地址：

数据集简介：

这是由阿里巴巴公司提供的一个关于淘宝广告的数据集，数据存放在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模型，分阶段寻找广告投放痛点，制定营销战略。

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

版权声明：本文为wangdan0526原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://www.cnblogs.com/wangdan0526/p/11248606.html