SQL实现广告数据多维度的提取

---

一、数据介绍

1. 背景和来源

该数据是由阿里巴巴提供的跟广告点击率相关的数据，包含:

• raw_sample：点击日志
• user_profile：用户基本信息
• ad_feature：广告基本信息

数据下载链接

本数据用来练习分析思路和SQL取数能力。

2. 各字段介绍

raw_sample 有26557961条记录，6个字段

阿里天池上的介绍：

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

user_profile 有1061768条记录，9个字段

阿里天池上的介绍：

本数据集涵盖了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：城市层级

ad_feature 有846811条记录，6个字段
阿里天池上的介绍：

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

---

二、思路

思路脑图如下：
先关注重点指标，算整体指标，再看一维、二维等的各指标的变化。目的是为了对用户精准投放，提高点击率。

---

三、计算步骤

因数据量太大，运行太久，所以这里先选择广告展示Top10的十条广告分析，重点锻炼分析思路和代码提取方法。

1.选择数据

把广告展示量Top10的广告存到一个表中，方便后续计算

CREATE TABLE top_ad_data 
AS
SELECT * FROM raw_sample
WHERE adgroup_id IN(
	SELECT adgroup_id FROM (
		SELECT adgroup_id,count(*) 
		FROM raw_sample
		GROUP BY adgroup_id
		ORDER BY COUNT(*) DESC
		LIMIT 10)a);

SELECT COUNT(*) FROM top_ad_data;
--结果：263309

数据变成了263309条。

---

2.整体重要指标

select 
	count(1) as "PV",
    count(distinct user) as "UV",
    count(1)/count(distinct user) as "PV/UV",
    concat(round(sum(clk)/count(adgroup_id)*100,2),"%") as "clk_rate"
from top_ad_data;

这十条广告的点击率为8.09%，PV/UV达到2以上。

---

2.单个维度指标计算

2.1 各广告的指标

select 
	adgroup_id as "广告编号",
	count(1) as "PV",
    count(distinct user) as "UV",
    count(1)/count(distinct user) as "PV/UV",
    concat(round(sum(clk)/count(adgroup_id)*100,2),"%") as "clk_rate"
from 
	top_ad_data
group by
	adgroup_id
order by
	count(1) desc;
	

数据结果：

• 710164有很大的PV数，比第二PV数大很多，所以UV也会变大。
• 看后面的比率指标，710164的点击率并不是特别突出，有三条广告的点击率达到10%以上。
• 后续可结合广告基本信息查看高点击率的广告有什么特征。

---

2.2 不同时间的指标波动

2.2.1 一天24小时的指标波动