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

一个路过的小酱油 2020-12-01 01:25:56

208

文章标签：数据分析可视化 mysql

最后发布:2020-12-01 01:25:56首次发布:2020-12-01 01:25:56

本文链接：https://blog.csdn.net/Exaid_snow/article/details/110311439

版权

一、分析目的

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

二、数据来源

阿里天池——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层级，消费能力一般且处于社会中层的群体。而且分析中发现女性远比男性展现量的点击率高，故广告推广时可以考虑多推荐一些女性相关的产品。