基于MySQL实现广告精准投放

一、软件安装

MySQL安装：https://zhuanlan.zhihu.com/p/46905335
NAVICAT安装：https://www.zhihu.com/question/22187503/answer/424079363
数据集下载：https://tianchi.aliyun.com/dataset/dataDetail?dataId=56

二、数据简介：
1. 用户点击raw_sample
我们从淘宝网站中随机抽样了114万用户8天内的广告展示/点击日志（2600万条记录），构成原始的样本骨架。
字段说明如下：
   (1) user_id：脱敏过的用户ID；
   (2) adgroup_id：脱敏过的广告单元ID；
   (3) time_stamp：时间戳；
   (4) pid：资源位；
   (5) noclk：为1代表没有点击；为0代表点击；
   (6) clk：为0代表没有点击；为1代表点击；

2. 广告基本信息表ad_feature
本数据集涵盖了raw_sample中全部广告的基本信息。字段说明如下：
   (1) adgroup_id：脱敏过的广告ID；
   (2) cate_id：脱敏过的商品类目ID；
   (3) campaign_id：脱敏过的广告计划ID；
   (4) customer_id:脱敏过的广告主ID；
   (5) brand：脱敏过的品牌ID；
   (6) price: 宝贝的价格
其中一个广告ID对应一个商品（宝贝），一个宝贝属于一个类目，一个宝贝属于一个品牌。

3. 用户基本信息表user_profile
本数据集涵盖了raw_sample中全部用户的基本信息。字段说明如下：
   (1) userid：脱敏过的用户ID；
   (2) cms_segid：微群ID；
   (3) cms_group_id：cms_group_id；
   (4) final_gender_code：性别 1:男,2:女；
   (5) age_level：年龄层次；
   (6) pvalue_level：消费档次，1:低档，2:中档，3:高档；
   (7) shopping_level：购物深度，1:浅层用户,2:中度用户,3:深度用户
   (8) occupation：是否大学生，1:是,0:否
   (9) new_user_class_level：城市层级

三、目的：

为实现高效的广告推荐，通过对用户（用户id、性别、年龄、消费档次、购物深度、是否大学生、所在城市）；广告（广告id、商品类目、广告计划、广告主、品牌、价格）；用户行为（点击时间、是否点击等维度）等多维度进行数据分析，找出其中隐含的规律，提出最佳推广方案，实现广告的精准投放。

四、数据预处理：

1.数据导入

MySQL中创建用户基本信息表：navicat中导入用户基本信息
创建广告基本信息表：navicat中导入广告基本信息
创建用户点击信息表：navicat中导入用户点击信息

1.1 用户基本信息：

select count(*) from 用户基本信息;

共1061768个样本

1.2 广告基本信息：

select count(*) from 广告基本信息表;

共846811个样本

1.3 用户是否点击信息：

select count(*) from 用户点击信息;
select count (*) from 用户点击信息 where 点击=1;

共26557961个用户行为,其中用户点击1366056次。

2. 特征提取

选择广告点击最高的广告数据进行分析：

SELECT `用户点击信息表`.`广告id`,
count(*) AS '点击量'  
from `用户点击信息表`
GROUP BY 用户点击信息表.`广告id`
ORDER BY 点击量 DESC;

得到广告id 为 "710164" 的点击率最高有70929次

SELECT * from `广告基本信息表`WHERE 广告id=710164;

新建表格将 "710164" 广告id 取出单独分析

CREATE TABLE `新样本人群广告点击记录表`
       (用户id INT NOT NULL auto_increment, 
        时间戳 varchar(255)  NOT NULL, 
        广告id varchar(255) NULL, 
        资源位 varchar(255) NULL, 
        nonclk varchar(255) NULL, 
        clk varchar(255) NULL, 
        PRIMARY KEY(用户id,时间戳)
        )ENGINE=INNODB;

备注：Mysql解决The total number of locks exceeds the lock table size错误方法如下：扩大存储空间

 show variables like "%_buffer%";
 SET GLOBAL innodb_buffer_pool_size=67108864;  （3*1024*1024*1024，不要忘记;号）

将筛选结果储存到新表中

 Insert into `新样本人群广告点击记录表`
 select * from `样本人群广告点击记录表` 
 where `广告id` LIKE '%710164%';

五、数据分析与可视化图表制作

创建可视化图表:

内联结关联用户基本信息表和新样本人群点击记录表

通过探索各维度特征与当前维度下展现量和点击率之间的关系，分析用户心理与行为。

CREATE VIEW 视图（人群分析） AS
    SELECT `新样本人群广告点击记录表`.`用户id`,
    from_unixtime(`新样本人群广告点击记录表`.`时间戳    `,'%Y-%m-%d') AS '点击日期',
    from_unixtime(`新样本人群广告点击记录表`.`时间戳`,'%k：%i：%s') AS '点击时间',
    `新样本人群广告点击记录表`.`资源位`,
    `新样本人群广告点击记录表`.nonclk,
    `新样本人群广告点击记录表`.clk,
    `用户基本信息表`.`城市层级`,
    `用户基本信息表`.`年龄层次`,
    `用户基本信息表`.`性别`,
    `用户基本信息表`.`消费档次`,
    `用户基本信息表`.`是否大学生`,
    `用户基本信息表`.`购物深度`
     FROM `新样本人群广告点击记录表`,`用户基本信息表`
     WHERE `用户基本信息表`.`用户id`=`新样本人群广告点击记录表`.`用户id`;

5.1 分析不同资源位展现量与点击率：

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

由分析结果可知，两个资源位点击率差异较少，虽然资源位430539点击率略高一点，但是展现量却不如另一资源位。运营人员应根据推广目的对曝光度以及点击率做合理的权衡。

5.2 分析一天不同时间段展现量与点击率：

CREATE VIEW 视图（人群分析）1 AS
SELECT `新样本人群广告点击记录表`.`用户id`,
from_unixtime(`新样本人群广告点击记录表`.`时间戳`,'%d') AS '点击日期',
from_unixtime(`新样本人群广告点击记录表`.`时间戳`,'%k') AS '点击时间',
`新样本人群广告点击记录表`.`资源位`,
`新样本人群广告点击记录表`.nonclk,
`新样本人群广告点击记录表`.clk,
`用户基本信息表`.`城市层级`,
`用户基本信息表`.`年龄层次`,
`用户基本信息表`.`性别`,
`用户基本信息表`.`消费档次`,
`用户基本信息表`.`职位（是否大学生）`,
`用户基本信息表`.`购物深度`
FROM `新样本人群广告点击记录表`,`用户基本信息表`
WHERE `用户基本信息表`.`用户id`=`新样本人群广告点击记录表`.`用户id`;

SELECT `视图（人群分析）1`.`点击时间`,
COUNT(*) AS 展现量,
SUM(`视图（人群分析）1`.clk) AS 点击数,
ROUND(SUM(`视图（人群分析）1`.clk) /COUNT(*)*100 ,2) AS 点击率,
CONCAT(ROUND(SUM(`视图（人群分析）1`.clk) /COUNT(*)*100 ,2),'%')AS 点击率1
from `视图（人群分析）1`
GROUP BY `视图（人群分析）1`.`点击时间`
ORDER BY 点击率 DESC;

由以上数据图表分析，得出以下结论：

a、展现量从早上9点开始活跃至晚上十点达到高峰，与人们日常作息时间相对应。

b、在展现量达到一定体量的基础上，每日的上午10点、下午3点的点击率出现了小高峰。

c、深夜1-2点点击率升高接近13%,推测原因，一方面可能近期商家做活动抢购，另一方面夜深之时，一天忙碌后临睡前用户会有购物的欲望。

5.3 每周各天点击率分析：

CREATE VIEW 视图（人群分析）3 AS
SELECT `新样本人群广告点击记录表`.`用户id`,
from_unixtime(`新样本人群广告点击记录表`.`时间戳`,'%Y-%m-%d') AS '点击日期',
from_unixtime(`新样本人群广告点击记录表`.`时间戳`,'%w') AS '点击时间',
`新样本人群广告点击记录表`.`资源位`,
`新样本人群广告点击记录表`.nonclk,
`新样本人群广告点击记录表`.clk,
`用户基本信息表`.`城市层级`,
`用户基本信息表`.`年龄层次`,
`用户基本信息表`.`性别`,
`用户基本信息表`.`消费档次`,
`用户基本信息表`.`是否大学生`,
`用户基本信息表`.`购物深度`
FROM `新样本人群广告点击记录表`,`用户基本信息表`
WHERE `用户基本信息表`.`用户id`=`新样本人群广告点击记录表`.`用户id`;

SELECT DATE_FORMAT(`视图（人群分析）3`.`点击日期`,'%w') AS 周数,
COUNT(*) AS 展现量,
SUM(`视图（人群分析）3`.clk) AS 点击数,
ROUND(SUM(`视图（人群分析）3`.clk) /COUNT(*)*100 ,2) AS 点击率,
CONCAT(ROUND(SUM(`视图（人群分析）3`.clk) /COUNT(*)*100 ,2),'%')AS 点击率1
from `视图（人群分析）3`
GROUP BY DATE_FORMAT(`视图（人群分析）3`.`点击日期`,'%w')
ORDER BY 点击率 DESC;

分析总结：由图可知道5月6号周六到周一的点击率偏高，展现量也处于不错的水平，而到了下个周六展现量与点击率均有所下降，这可能与下个星期天即将到来的母亲节相关。运营人员可以根据对应节气上线一些具有节日特色的商品。

5.4 多维度分析各人群点击率：

5.4.1 各年龄层次人群点击率分析

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

由图可知，年龄层次1、2、5对于点击率偏高，年龄层次2和3展现量处于不错水平，可推断现今青少年仍是消费主体。

5.4.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;

由图表分析可知，高档人群展现量仅有883，且点击率也偏低，推测是由于产品109的单价，不符合高档人群购物需求，推广时候应该注意剔除此类人群。

5.4.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.4.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.4.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.4.6 不同性别人群点击率分析：

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

分析可得，女性远比男性展现量的点击率高，故运营人员可以多推荐一些女性相关的产品。

六、总结与建议

a、产品分析：商品价格是109，在对应类目这个价格是普通价位，受众较多。对应广告的展现量以及点击量也处于较高的水平。

b、广告资源位分析：产品在两个资源位点击率并无较大差别。

c、广告投放时间分析：每天上午10点左右，下午3点左右点击率较高，也与商家促销活动、节假日时间等因素相关。可以根据推广需求，及转化率对于这几个时间区间设置溢价，提高该时段的展现，提高整个计划的点击率。

d、投放人群分析：由分析可知，广告对于购物深度，以及是否是学生人群的特性不敏感。对于城市层级，年龄层次，消费档次人群特性比较敏感，研究产品本身特点，推测产品主要消费人群是年龄处于1-2层级，消费能力一般，且处于社会中层的群体。

发表于 2019-05-07 16:13 sort_man 阅读(331) 评论(0) 收藏举报

刷新页面返回顶部

基于MySQL实现广告精准投放

六、总结与建议

公告