关于广告精准投放的数据分析项目——用MYSQL实现

一、分析目的

在大数据时代的背景下,广告主可从购买媒介变成直接购买用户。广告的精准投放对广告主、服务平台与潜在用户而言,在提升效率与商业效益方面,有了更迫切的需求。然而网络广告形式多样,很多广告投放系统率相对缺乏针对性,使得网络广告精确度不够高。因此,对推广数据的研究是十分必要的。所以本次项目将从用户特征,投放时间,投放位置三方面多维度进行数据分析,以提高用户点击率,实现淘宝展示广告精准投放,提升广告投放效果。

二、数据来源

阿里天池——https://tianchi.aliyun.com/dataset/dataDetail?dataId=56

取其中三个数据集
raw_sample.csv.tar.gz
user_profile.csv.tar.gz
ad_feature.csv.tar.gz

数据集为淘宝网在2017年5月6日至5月13日中随机抽样114万用户8天内的广告展示/点击日志构成的原始样本以及广告和用户的基本信息。(信息均已脱敏)

三、数据理解

(1) raw_sample.csv.tar.gz:样本人群点击记录表格,包含条点击记录
在这里插入图片描述
(2)ad_feature.csv.tar.gz :广告的基本信息,包含广告id的基本信息
在这里插入图片描述
(3)user_profile.csv.tar.gz:用户基本信息表格,包含位用户信息
在这里插入图片描述

四、分析思路

通过上面的数据理解,我们可以得知现在手上的数据信息有:

用户信息用户id,性别,年龄,消费档次,购物深度,是否大学生,所在城市
广告信息广告id,商品类目,广告计划,广告主,品牌,价格
用户行为点击时间,是否点击

因为淘宝的广告和属于信息流广告,这类型广告一般按照CPC(即点击率为指标)衡量广告投放效果,故为达成广告精准有效投放,我们可从三方面来多维度分析广告的点击率:

1.投放位置
2.投放时间
3.投放人群

从而筛选出最佳的投放位置,最佳投放时间,优质投放人群,以实现精确投放,找出实现广告精准投放的方案,提高推广费用的使用效果。

五、数据处理

1.首先新建数据库test one(随便起的),把下载好的三个数据集.CSV格式文件,通过Navicat导入MYSQL里,导入过程在我前面的学习笔记有,这里不再复述。

三个数据集导入后先看看数据量:
在这里插入图片描述
在这里插入图片描述

在这里插入图片描述
在这里插入图片描述
84611个广告的基本信息、1061768个用户基本信息、26557961个广告点击记录。
由于广告点击记录足足2600多万条,MYSQL处理千万级别的数据的确很吃力(当然也有我电脑配置实在不好的原因…),处理广告点击信息的表格耗时接近1小时…
在这里插入图片描述
导入数据并了解基本情况后,对3个表格的各自的信息进行相关备注操作,更方便易懂
在这里插入图片描述
至此,准备工作完成

2.为构建高效推广分析模型,保证分析的准确性,也由于上面的查询点击记录表的广告点击量检索太过耗时,所以本次先进行特征提取,选择样本用户点击数最多的广告ID进行分析。

SELECT 样本人群广告点击记录表.广告id,
count(*) as 展示量
  FROM test one.样本人群广告点击记录表
group  by 样本人群广告点击记录表.广告id
ORDER BY 展示量 desc 

运行之,得到
在这里插入图片描述

因此筛选出表中点击数量最多的广告单元id为:710164
再看看这个广告单位的具体情况:

SELECT `广告id`,商品类目id,`广告计划id`,`广告主id`,`品牌id`,商品价格
 FROM test one.广告基本信息表
where `广告基本信息表`.`广告id`=710164;

得到:
在这里插入图片描述
对此,我们把这个相关信息储存在新表上方便后面操作。

3.新建表格,存入数据
先新建空白表一张
在这里插入图片描述

再从样本人群广告点击记录表中找出广告id710164,将其存入广告id710164人群点击记录表中

Insert into test one.广告id710164人群点击记录表
select * from test one.样本人群广告点击记录表 
where `广告id` LIKE '%710164%'

得到:
在这里插入图片描述
4.对《用户基本信息表》与《广告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`;

在这里插入图片描述
在这里插入图片描述
至此,所有准备工作均已完成,接下来可以开始分析

六、数据分析与可视化图表

1.投放位置
输入代码如下:

SELECT `人群分析`.`资源位`,
COUNT(*) AS 展现量,
SUM(`人群分析`.clk) AS 点击数,
CONCAT(ROUND(SUM(`人群分析`.clk) /COUNT(*)*100 ,2),'%')AS 点击率
from `人群分析`
GROUP BY `人群分析`.`资源位`
ORDER BY 点击率 DESC;

得到:
在这里插入图片描述
由分析结果可知,两个资源位点击率差距并不大,虽然资源位“430539”点击率高一点,但是其展现量却不如另一资源位。
所以运营可以根据推广目的对曝光度以及点击率做合理的调整。

2.投放时间
对时间进行每日各时间每周各天两个维度拆解分析
先看看日内各时间段的广告点击表现:

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;

得到:
在这里插入图片描述由于数据量有点多,这里用Tableau可视化一下,得到图表
在这里插入图片描述
看图分析:
(1)展现量从早上9点持续活跃到晚上10点的峰值,比较符合大家的作息规律
(2)在展现量达到一定体量的基础上,每日的上午10点和下午3点的点击率出现了小高峰
(3)深夜的1-2点点击率超过12%,推测原因,一方面数据体量相对比较小,偶然性概率高。另一方面可能在夜深人静之时,大家在一天忙碌后临睡前有比较强烈的购物欲望。再者或许商家在这个时间段搞活动。
————————————————
再看看周内各天的广告点击情况

SELECT DATE_FORMAT(`人群分析`.`点击时间`,'%w') AS 周数,
COUNT(*) AS 展现量,
SUM(`人群分析`.clk) AS 点击数,
CONCAT(ROUND(SUM(`人群分析`.clk) /COUNT(*)*100 ,2),'%')AS 点击率
from `人群分析`
GROUP BY DATE_FORMAT(`人群分析`.`点击时间`,'%w')

在这里插入图片描述
Tableau可视化一下数据得到
在这里插入图片描述

分析总结:由图可知道周六,周日,周一的点击率偏高,展现量也比较不错。运营人员可以根据推广目的结合转化率来进行合理配置。

3.投放人群
从用户基本信息表可知有六大属性:
性别,年龄,消费档次,购物深度,是否大学生,城市层级

一个个来,首先是
(1)不同性别人群点击率分析

SELECT `人群分析`.`性别`,
COUNT(*) AS 展现量,
SUM(`人群分析`.clk) AS 点击数,
CONCAT(ROUND(SUM(`人群分析`.clk) /COUNT(*)*100 ,2),'%')AS 点击率
from `人群分析`
GROUP BY `人群分析`.`性别`
ORDER BY 点击率 DESC;

在这里插入图片描述
在这里插入图片描述
显而易见,女性的展现量和点击率高均比男性要高,故运营人员可以多推荐一些女性相关的产品

(2)年龄层次点击率分析

SELECT `人群分析`.`年龄层次`,
COUNT(*) AS 展现量,
SUM(`人群分析`.clk) AS 点击数,
CONCAT(ROUND(SUM(`人群分析`.clk) /COUNT(*)*100 ,2),'%')AS 点击率
from `人群分析`
GROUP BY `人群分析`.`年龄层次`;

在这里插入图片描述
在这里插入图片描述
年龄层次1、2、5的群体点击率偏高,而且展现量处于较好的水平

(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;

在这里插入图片描述
由图可知,高档人群的展现量只有883,而且点击率也偏低,猜测是由于“广告id710164”的商品单价为109元,价格不符合高档人群购物需求,推广时候应该注意剔除此类人群。

(4)购物深度点击率分析

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)是否大学生群体点击率分析

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元,属于普通商品,接受程度较高

(6)不同城市层级点击率分析

SELECT `人群分析`.`城市层级`,
COUNT(*) AS 展现量,
SUM(`人群分析`.clk) AS 点击数,
CONCAT(ROUND(SUM(`人群分析`.clk) /COUNT(*)*100 ,2),'%')AS 点击率
from `人群分析`
GROUP BY `人群分析`.`城市层级`
ORDER BY 点击率 DESC;

在这里插入图片描述
由图可知,第1层级点击率较低,而第1、4层级展现量也较低,处于中间第2层级展现量以及点击率都很高,后续可以适度关注第2层级城市的投放。

六、总结和建议

**1.**商品价格是109元,在对应类目商品上,这个价格属于普通价位,受众较多。故对应广告的展现量以及点击量也处于较高的水平。
**2.**由上面分析可知产品在两个资源位点击率并无较大差别。
**3.**每天上午10点左右,下午3点左右的广告点击率较高,也与商家促销活动、节假日时间等因素相关。可以根据推广需求,及转化率对于这几个时间区间设置溢价,提高该时段的展现,提高整个计划的点击率。
**4.**广告对于否是大学生人群、购物深度这两个特性不敏感。对于城市层级,年龄层次,消费档次人群的特性比较敏感。结合广告商品本身特点,推测该商品主要消费人群是年龄处于1-2层级,消费能力一般且处于社会中层的群体。而且分析中发现女性远比男性展现量的点击率高,故广告推广时可以考虑多推荐一些女性相关的产品。

已标记关键词 清除标记
©️2020 CSDN 皮肤主题: 游动-白 设计师:上身试试 返回首页