广告投放数据分析

小小蔡

共 5194字，需浏览 11分钟

2022-02-09 17:32

1 提出问题：如何实现广告的精准投放

实现广告的精准投放就是最大化投入产出的过程，需要知道我们的用户在哪里，在哪些渠道能够更大化用户价值，以及各个渠道用户价值的变化规律。通过对渠道方式和渠道质量的追踪，以引流和转化率为关键事件，做好渠道的优化和维护。

本文通过研究阿里天池数据，仅从渠道，投放时间，投放人群，以点击率为数据指标进行数据分析，从而给出更好的方案和建议，实现高效率高产出。

2 数据来源

2.1 数据源

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

2.2 数据理解

（1）user_profile.csv.tar.gz

（2）ad_feature.csv.tar.gz

（3）raw_sample.csv.tar.gz

表user_profile反映的是用户信息，表ad_feature反映的是广告属性信息，表raw_sample反映的是用户行为信息。数据集不存在投入产出字段，命题转化为以浏览量、点击率作为数据指标，进行数据分析。

3 分析思路

4 数据存储

将数据源存储在MYSQL数据库中，且变更数据存储空间大小。

4.1 计算各个广告的点击次数

由于数据量巨大，宕机时间长，选择部分数据分析，以下计算各个广告的点击次数。

-- 1 各个广告总点击次数
SELECT raw_sample.adgroup_id, count(*) AS arise_count
FROM raw_sample
GROUP BY adgroup_id
ORDER BY arise_count DESC;

4.2 选择子集

筛选广告点击凑数最多的710164作为新的数据集建表

-- 2 新建表
CREATE TABLE IF NOT EXISTS 710164_sample
(
userid  INT NOT NULL, 
time_stamp VARCHAR(100) NOT NULL,
adgroup_id VARCHAR(20) DEFAULT NULL,
pid  VARCHAR(100) DEFAULT NULL,
nonclk  VARCHAR(20) DEFAULT NULL,
clk VARCHAR(20) DEFAULT NULL
)engine=innodb default charset=utf8;

将筛选数据插入数据表中

-- 3 将查询数据插入表中
INSERT INTO 710164_sample
SELECT * FROM raw_sample
WHERE adgroup_id = '710164';

创建视图作为中间桥梁

-- 4 创建视图
CREATE VIEW per_group_an AS 
SELECT s.userid, 
FROM_UNIXTIME(s.time_stamp,'%Y-%m-%d') AS 'clk_date',
FROM_UNIXTIME(s.time_stamp,'%k') AS 'clk_time',
s.pid,
s.nonclk,
s.clk,
u.new_user_class_level,
u.age_level,
u.final_gender_code,
u.pvalue_level,
u.occupation,
u.shopping_level
FROM 710164_sample AS s, user_profile AS u
WHERE u.userid = s.userid;

-- 5 查询视图数据
SELECT * FROM per_group_an;

-- 6 观察一下价格
SELECT price
FROM ad_feature
WHERE adgroup_id = '710164';

5 数据分析

5.1 分析不同渠道点击率

SELECT p.pid, COUNT(*) AS arise_count,
            SUM(p.clk) AS clk_count,
	   CONCAT(ROUND(SUM(p.clk)/COUNT(*) * 100,2),'%') AS clk_ratio
			 
FROM per_group_an AS p
GROUP BY p.pid
ORDER BY clk_ratio DESC;

由于渠道有限，仅从2个渠道源分析来看，430548_1007需要继续维护渠道，提高转化率，430548_1007需要加大渠道投入，做好引流。

5.2 分析不同投放时间点击率

5.2.1 每日各时间段点击率分析

SELECT p.clk_time, COUNT(*) AS arise_count,
       SUM(p.clk) AS clk_count,
			 ROUND(SUM(p.clk)/COUNT(*),4) AS clk_ratio,
			 CONCAT(ROUND(SUM(p.clk)/COUNT(*) * 100,2),'%') AS clk_ratio_copy
FROM per_group_an AS p
GROUP BY p.clk_time
ORDER BY clk_ratio DESC;

可知：

a. 在浏览量达到一定数据时，每日的上午10点、下午3点的点击率出现了小高峰。

b. 晚上由9点开始浏览量主键提升，但点击率相对于前段时间较低，说明多数用户是目的性流量走低。

c. 深夜0-2点点击率超过12%，推测原因，可能是浏览量较低，偶然性提升，也可能是这部分人群的目的性较强，如果能获知这些人群画像，能高效的做好营销转化。

说明，目标客户在深夜转化的可能性更高。

5.2.2 每周各天点击率分析

SELECT DATE_FORMAT(p.clk_date,'%w') AS week_day, COUNT(*) AS arise_count,
             SUM(p.clk) AS clk_count,
	     ROUND(SUM(p.clk)/COUNT(*),4) AS clk_ratio,
	     CONCAT(ROUND(SUM(p.clk)/COUNT(*) * 100,2),'%') AS clk_ratio_copy
FROM per_group_an AS p
GROUP BY DATE_FORMAT(p.clk_date,'%w') 
ORDER BY clk_ratio DESC;

可知，周六、日，周一点击率较高，浏览量也不低，说明这几天带来的收益是更大的。

5.2.3 各天各时间段分析

SELECT DATE_FORMAT(p.clk_date,'%w') AS week_day, clk_time,
       COUNT(*) AS arise_count,
       SUM(p.clk) AS clk_count,
			 ROUND(SUM(p.clk)/COUNT(*),4) AS clk_ratio,
			 CONCAT(ROUND(SUM(p.clk)/COUNT(*) * 100,2),'%') AS clk_ratio_copy
FROM per_group_an AS p
GROUP BY DATE_FORMAT(p.clk_date,'%w'),clk_time 
ORDER BY clk_ratio DESC;

5.3 分析人群特征点击率

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

SELECT age_level, COUNT(*) AS arise_count,
       SUM(p.clk) AS clk_count,
			 ROUND(SUM(p.clk)/COUNT(*),4) AS clk_ratio,
			 CONCAT(ROUND(SUM(p.clk)/COUNT(*) * 100,2),'%') AS clk_ratio_copy
FROM per_group_an AS p
GROUP BY age_level
ORDER BY clk_ratio DESC;

可知

a. 广告投放在1/2/5年龄层带来的点击率更高，需要从时间维度考虑，增大广告投入对这三个年龄层的效果。

b. 对与年龄层6，可以从时间维度考虑增大样本投入后会不会有更大的点击率，从而判定是否需要更多的投入。

c. 对比年龄层1/5和3，说明可以放弃对年龄层3的投入，最好增加时间维度和投入资源维度。

d. 年龄层0需要投入更多的资源，观察效果后判定舍去与否。

5.3.2 各个消费等级人群点击率分析

SELECT p.pvalue_level,
	    COUNT(*) AS arise_count,
	    SUM(p.clk) AS clk_count,
	    ROUND(SUM(p.clk)/COUNT(*),4) AS clk_ratio,
	    CONCAT(ROUND(SUM(p.clk)/COUNT(*) * 100,2),'%') AS clk_ratio_copy
FROM per_group_an AS p
GROUP BY pvalue_level
ORDER BY clk_ratio DESC;

可知，高档人群点击率偏低仅有7.13%，原因可能是产品的客单价是109，不符合这类人群的需求。

5.3.3 各购物深度人群点击率分析

SELECT p.shopping_level,
	    COUNT(*) AS arise_count,
	     SUM(p.clk) AS clk_count,
	     ROUND(SUM(p.clk)/COUNT(*),4) AS clk_ratio,
	     CONCAT(ROUND(SUM(p.clk)/COUNT(*) * 100,2),'%') AS clk_ratio_copy
FROM per_group_an AS p
GROUP BY shopping_level
ORDER BY clk_ratio DESC;

可知，中、深度用户点击率略高，如果逐步转化至付费阶段，相比于浅度用户，中、深度用户后续付费根能行会更大。

5.3.4 各职业（是否大学生）人群点击率分析

SELECT p.occupation,
	    COUNT(*) AS arise_count,
	     SUM(p.clk) AS clk_count,
	     ROUND(SUM(p.clk)/COUNT(*),4) AS clk_ratio,
	     CONCAT(ROUND(SUM(p.clk)/COUNT(*) * 100,2),'%') AS clk_ratio_copy
FROM per_group_an AS p
GROUP BY occupation
ORDER BY clk_ratio DESC;

可知，大学生的点击率略高，但总体区分度不大，说明样本用户群体对710164敏感度差异性不大，可能是价格109属于平价款的原因。

5.3.5 各城市等级人群点击率分析

SELECT p.new_user_class_level,
			 COUNT(*) AS arise_count,
	     SUM(p.clk) AS clk_count,
			 ROUND(SUM(p.clk)/COUNT(*),4) AS clk_ratio,
			 CONCAT(ROUND(SUM(p.clk)/COUNT(*) * 100,2),'%') AS clk_ratio_copy
FROM per_group_an AS p
GROUP BY new_user_class_level
ORDER BY clk_ratio DESC;