mysql通过价格划分档次_基于MySQL实现广告精准投放

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层级，消费能力一般，且处于社会中层的群体。