关于广告精准投放的数据分析项目——用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人群点击记录表`