目录
一.项目背景
二.分析思路
三.数据
3.1 数据来源
3.2 数据理解
四.数据处理
4.1 导入数据
4.2 选择子集
4.3 新建表
五.搭建模型
5.1 多维度分析用户点击率
-- 5.1.1 年龄层次点击率
--5.2.2 消费档次点击率
-- 5.2.3 购物深度点击率
-- 5.2.4 职位（是否大学生）人群点击率分析
-- 5.2.5 城市层次点击率
5.2 多维度分析不同投放时间点击率
-- 5.2.1 每日各时间段点击率分析
-- 5.2.2 每周各天点击率分析
5.3 分析不同资源位点击率
六.结论
一. 项目背景
随着互联网的普及和电子商务的迅猛发展，网络广告成为一种新的经营方式。然而网络广告形式多样，现有广告投放系统率大多缺乏针对性，使得网络广告精确度不高，不能达到预期效果。因此，对推广数据的研究是十分必要的，本文将从用户特征，投放时间，投放位置三方面多维度进行数据分析，提高用户点击率，让推广费用投入高效。
二.分析思路
三. 数据
3.1 数据来源
阿里天池——https://tianchi.aliyun.com/dataset/dataDetail?dataId=56
3.2 数据理解
3.2.1 user_profile.csv
用户基本信息表格，包含1,062,768位用户信息
用户基本信息
3.2.2 raw_sample.csv
样本人群点击记录表格，包含26,558,961条点击记录
样本人群点击记录表格
3.2.3 ad_feature.csv
广告基本信息表，包含846,811个广告id的基本信息
广告基本信息表
四. 数据处理
4.1 导入数据
新建数据库advertisement，将数据csv表格通过navicat 导入数据库，导入过程备注在文章最后，导入结果如图
导入结果
4.2 选择子集
数据库advertisement包含84611个广告的基本信息、1062768个用户基本信息、26558961个广告点击记录。为构建高效化推广分析模型，保证分析更准确，选择样本用户点击数据最多的广告id进行分析。
4.2.1 样本人群点击记录表中点击数据最多的广告单元id
SELECT 样本人群广告点击记录表.广告单元id,
count(*) as 展示量
FROM advertisement.样本人群广告点击记录表
group by 样本人群广告点击记录表.广告单元id
ORDER BY 展示量 desc
运行可得
点击数量最多的广告单元id
4.2.2 点击数据最多的广告单元id——710164基本信息
SELECT `广告id`,商品类目id,`广告计划id`,`广告主id`,`品牌id`,商品价格
FROM advertisement.广告基本信息表
where `广告基本信息表`.`广告id`=710164;
运行
点击最多的广告信息
4.3 新建表
由于样本人群点击数据记录表有千万级别的数据量，为了简化搜索，我们筛选表格中广告id710164的点击记录，储存在新表[广告id710164人群点击数据记录表]中。
4.3.1 新建空白表
广告id710164人群点击记录表
4.3.2 从样本人群广告点击记录表中找出广告id为710164，将其存入广告id710164人群点击记录表
Insert into advertisement.广告id710164人群点击记录表
select * from advertisement.样本人群广告点击记录表 a
where a.`广告单元ID` LIKE '%710164%'
广告id710164人群点击记录表
4.3.3 两表联立
内联结关联【用户基本信息表】与【广告id710164人群点击记录表】，创建视图简化操作
CREATE VIEW 人群分析 AS
SELECT `广告id710164人群点击记录表`.`用户id`,
from_unixtime(`广告id710164人群点击记录表`.`时间戳`,'%Y-%m-%d %k：%i：%s') AS '点击时间',
`广告id710164人群点击记录表`.`资源位`,
`广告id710164人群点击记录表`.nonclk,
`广告id710164人群点击记录表`.clk,
`用户基本信息表`.`城市层级`,
`用户基本信息表`.`年龄层次`,
`用户基本信息表`.`性别`,
`用户基本信息表`.`消费档次`,
`用户基本信息表`.`是否大学生`,
`用户基本信息表`.`购物深度`
FROM `广告id710164人群点击记录表`,`用户基本信息表`
WHERE `用户基本信息表`.`用户id`=`广告id710164人群点击记录表`.`用户id`;
五.搭建模型
5.1 多维度分析用户点击率
5.1.1 年龄层次点击率
SELECT `人群分析`.`年龄层次`,
COUNT(*) AS 展现量,
SUM(`人群分析`.clk) AS 点击数,
CONCAT(ROUND(SUM(`人群分析`.clk) /COUNT(*)*100 ,2),'%')AS 点击率
from `人群分析`
GROUP BY `人群分析`.`年龄层次`;
由图可知，年龄层次1,2,5对于点击率偏高，展现量处于不错水平。
5.1.2 消费档次点击率
SELECT `人群分析`.`消费档次`,
CASE WHEN `人群分析`.`消费档次`=1 THEN "低档"
WHEN `人群分析`.`消费档次`=2 THEN "中档"
WHEN `人群分析`.`消费档次`=3 THEN "高档"
ELSE "未分类"
END AS `实际消费档次`,
COUNT(*) AS 展现量,
SUM(`人群分析`.clk) AS 点击数,
CONCAT(ROUND(SUM(`人群分析`.clk) /COUNT(*)*100 ,2),'%')AS 点击率
from `人群分析`
GROUP BY `人群分析`.`消费档次`
ORDER BY 点击率 DESC;
分析可知，高档人群点击率偏低，仅有7.13%，推测是因为产品的客单价是109，不符合高档人群购物需求，推广时候应该注意剔除此类人群。
5.1.3 购物深度点击率
SELECT `人群分析`.`购物深度`,
CASE WHEN `人群分析`.`购物深度`=1 THEN "浅层用户"
WHEN `人群分析`.`购物深度`=2 THEN "中度用户"
WHEN `人群分析`.`购物深度`=3 THEN "深度用户"
ELSE "未分类"
END AS `实际购物深度`,
COUNT(*) AS 展现量,
SUM(`人群分析`.clk) AS 点击数,
CONCAT(ROUND(SUM(`人群分析`.clk) /COUNT(*)*100 ,2),'%')AS 点击率
from `人群分析`
GROUP BY `人群分析`.`购物深度`
ORDER BY 点击率 DESC;
分析可知，用户购物深度对于点击率影响不大。但是深度用户在展现量占据90%以上，点击率仍有9.45%，该人群点击率比较稳定。
5.1.4 各职位（是否大学生）人群点击率分析
SELECT `人群分析`.`是否大学生`,
CASE WHEN `人群分析`.`是否大学生`=1 THEN "是"
WHEN `人群分析`.`是否大学生`=0 THEN "否"
ELSE "未分类"
END AS `是否大学生`,
COUNT(*) AS 展现量,
SUM(`人群分析`.clk) AS 点击数,
CONCAT(ROUND(SUM(`人群分析`.clk) /COUNT(*)*100 ,2),'%')AS 点击率
from `人群分析`
GROUP BY `人群分析`.`是否大学生`
ORDER BY 点击率 DESC;
分析可知，用户的职位对于是否是大学生影响不大，分析商品客单价109，属于平价款，接受程度较高。
5.1.5 城市层次点击率
SELECT `人群分析`.`城市层级`,
COUNT(*) AS 展现量,
SUM(`人群分析`.clk) AS 点击数,
CONCAT(ROUND(SUM(`人群分析`.clk) /COUNT(*)*100 ,2),'%')AS 点击率
from `人群分析`
GROUP BY `人群分析`.`城市层级`
ORDER BY 点击率 DESC;
分析可知，第1层级点击率较低，第4层级展现量也比较低，处于中间第2层级展现量以及点击率都很高。
5.2 多维度分析不同投放时间点击率
5.2.1 每日各时间段点击率分析
SELECT DATE_FORMAT(`人群分析`.`点击时间`,'%H') AS 小时,
COUNT(*) AS 展现量,
SUM(`人群分析`.clk) AS 点击数,
CONCAT(ROUND(SUM(`人群分析`.clk) /COUNT(*)*100 ,2),'%')AS 点击率
from `人群分析`
GROUP BY DATE_FORMAT(`人群分析`.`点击时间`,'%H')
ORDER BY 点击率 DESC;
分析可知得出以下结论
a、 在展现量达到一定体量的基础上，每日的上午10点、下午3点的点击率出现了小高峰。
b、 晚上由9点开始展现量提升比较大，但是点击率也在处于上升状态。
c、 深夜1-2点点击率超过12%,推测原因，一方面数据体量比较小，偶然性比较高。另一方面推测深夜依旧在逛淘宝，购买欲望比较强烈。
5.2.2 每周各天点击率分析
SELECT DATE_FORMAT(`人群分析`.`点击时间`,'%w') AS 周数,
COUNT(*) AS 展现量,
SUM(`人群分析`.clk) AS 点击数,
CONCAT(ROUND(SUM(`人群分析`.clk) /COUNT(*)*100 ,2),'%')AS 点击率
from `人群分析`
GROUP BY DATE_FORMAT(`人群分析`.`点击时间`,'%w')
ORDER BY 点击率 DESC;
分析总结：由图可知道周六，周日，周一的点击率偏高，展现量也处于不错的水平。运营人员可以根据推广目的结合转化率每日设置溢价。
5.3 分析不同资源位点击率
SELECT `人群分析`.`资源位`,
COUNT(*) AS 展现量,
SUM(`人群分析`.clk) AS 点击数,
CONCAT(ROUND(SUM(`人群分析`.clk) /COUNT(*)*100 ,2),'%')AS 点击率
from `人群分析`
GROUP BY `人群分析`.`资源位`
ORDER BY 点击率 DESC;
由分析结果可知，两个资源位点击率差异较少，虽然资源位430539点击率略高一点，但是展现量却不如另一资源位。运营人员应根据推广目的对曝光度以及点击率做合理的权衡。
六.总结与建议
6.1 产品分析
产品客单是109，在对应类目这个价格是普通价位，受众较多。对应广告的展现量以及点击量也处于较高的水平。
6.2 广告资源位分析
产品在两个资源位点击率并无较大差别。
6.3 广告投放时间分析
每周六，周日，周一产品的点击率较高。每天上午10点左右，下午3点左右点击率较高。可以根据推广需求及转化率对于这几个时间区间设置溢价，提高该时段的展现，提高整个计划的点击率。
6.4 投放人群分析
由分析可知，广告对于购物深度，以及职位的人群特性不敏感。对于城市层级，年龄层次，消费档次人群特性比较敏感，研究产品本身特点，推测产品主要消费人群是年龄处于1-2层级，消费能力一般，且处于社会中层的群体。
附：总结遇到的问题
一.在4.1导入数据主键问题
1.1 user_profile.csv
user_profile.csv
1.2 ad_feature.csv.tar.gz
image.png
1.3 raw_sample
raw_sample
由于 raw_sample中user不唯一，所以在导入时，建表不能选择主键
1.4.导入过程可参考
https://jingyan.baidu.com/article/4dc408484839c4c8d946f1ab.html
二.在4.3.2中，
样本人群广告点击记录表中找出广告id为710164，将其存入广告id710164人群点击记录表过程中发生错误errorThe total number of locks exceeds the lock table size。
解决途径https://blog.csdn.net/github_36326955/article/details/54970808
三.在6.1中
函数：FROM_UNIXTIME，将MYSQL中以INT(11)存储的时间以"YYYY-MM-DD"格式来显示
https://www.cnblogs.com/xieqian111/p/5735952.html
四.在6.2中，
1.ROUND 函数用于把数值字段舍入为指定的小数位数
SELECT ROUND(column_name,decimals) FROM table_name
2.concat()函数将两个或多个字符串连接在一起，本文是为了体现百分数
https://www.yiibai.com/mysql/sql-concat-in-mysql.html
网友评论