關於廣告精準投放的資料分析專案——用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人群點選記錄表`.clk,
`使用者基本資訊表`.`城市層級`,
`使用者基本資訊表`.`年齡層次`,
`使用者基本資訊表`.`性別`,
`使用者基本資訊表`.`消費檔次`,
`使用者基本資訊表`.`是否大學生`,
`使用者基本資訊表`.`購物深度`
FROM `廣告id710164人群點選記錄表`,`使用者基本資訊表`
WHERE `使用者基本資訊表`.`使用者id`=`廣告id710164人群點選記錄表`.`使用者id`;

至此，所有準備工作均已完成，接下來可以開始分析

六、資料分析與視覺化圖表

1.投放位置
輸入程式碼如下：

SELECT `人群分析`.`資源位`,
COUNT(*) AS 展現量,
SUM(`人群分析`.clk) AS 點選數,
CONCAT(ROUND(SUM(`人群分析`.clk) /COUNT(*)*100 ,2),'%')AS 點選率
from `人群分析`
GROUP BY `人群分析`.`資源位`
ORDER BY 點選率 DESC;

得到：

由分析結果可知，兩個資源位點選率差距並不大，雖然資源位“430539”點選率高一點，但是其展現量卻不如另一資源位。
所以運營可以根據推廣目的對曝光度以及點選率做合理的調整。

2.投放時間
對時間進行每日各時間和每週各天兩個維度拆解分析
先看看日內各時間段的廣告點選表現：

SELECT DATE_FORMAT(`人群分析`.`點選時間`,'%H') AS 小時,
COUNT(*) AS 展現量,
SUM(`人群分析`.clk) AS 點選數,
CONCAT(ROUND(SUM(`人群分析`.clk) /COUNT(*)*100 ,2),'%')AS 點選率
from `人群分析`
GROUP BY DATE_FORMAT(`人群分析`.`點選時間`,'%H') 
ORDER BY 點選率 DESC;

得到：
由於資料量有點多，這裡用Tableau視覺化一下，得到圖表

看圖分析：
（1）展現量從早上9點持續活躍到晚上10點的峰值，比較符合大家的作息規律
（2）在展現量達到一定體量的基礎上，每日的上午10點和下午3點的點選率出現了小高峰
（3）深夜的1-2點點選率超過12%,推測原因，一方面資料體量相對比較小，偶然性概率高。另一方面可能在夜深人靜之時，大家在一天忙碌後臨睡前有比較強烈的購物慾望。再者或許商家在這個時間段搞活動。
————————————————
再看看周內各天的廣告點選情況

SELECT DATE_FORMAT(`人群分析`.`點選時間`,'%w') AS 週數,
COUNT(*) AS 展現量,
SUM(`人群分析`.clk) AS 點選數,
CONCAT(ROUND(SUM(`人群分析`.clk) /COUNT(*)*100 ,2),'%')AS 點選率
from `人群分析`
GROUP BY DATE_FORMAT(`人群分析`.`點選時間`,'%w')

Tableau視覺化一下資料得到

分析總結：由圖可知道週六，週日，週一的點選率偏高，展現量也比較不錯。運營人員可以根據推廣目的結合轉化率來進行合理配置。

3.投放人群
從使用者基本資訊表可知有六大屬性：
性別，年齡，消費檔次，購物深度，是否大學生，城市層級

一個個來，首先是
（1）不同性別人群點選率分析

SELECT `人群分析`.`性別`,
COUNT(*) AS 展現量,
SUM(`人群分析`.clk) AS 點選數,
CONCAT(ROUND(SUM(`人群分析`.clk) /COUNT(*)*100 ,2),'%')AS 點選率
from `人群分析`
GROUP BY `人群分析`.`性別`
ORDER BY 點選率 DESC;

顯而易見，女性的展現量和點選率高均比男性要高，故運營人員可以多推薦一些女性相關的產品

（2）年齡層次點選率分析

SELECT `人群分析`.`年齡層次`,
COUNT(*) AS 展現量,
SUM(`人群分析`.clk) AS 點選數,
CONCAT(ROUND(SUM(`人群分析`.clk) /COUNT(*)*100 ,2),'%')AS 點選率
from `人群分析`
GROUP BY `人群分析`.`年齡層次`;

年齡層次1、2、5的群體點選率偏高，而且展現量處於較好的水平

（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;

由圖可知，高檔人群的展現量只有883，而且點選率也偏低，猜測是由於“廣告id710164”的商品單價為109元，價格不符合高檔人群購物需求，推廣時候應該注意剔除此類人群。

（4）購物深度點選率分析

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）是否大學生群體點選率分析

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;
```![在這裡插入圖片描述](https://img-blog.csdnimg.cn/20201130222937507.jpg#pic_center)

對於使用者來說，是否是大學生對點選率影響不大，分析的廣告商品單價109元，屬於普通商品，接受程度較高

（6）不同城市層級點選率分析

```sql
SELECT `人群分析`.`城市層級`,
COUNT(*) AS 展現量,
SUM(`人群分析`.clk) AS 點選數,
CONCAT(ROUND(SUM(`人群分析`.clk) /COUNT(*)*100 ,2),'%')AS 點選率
from `人群分析`
GROUP BY `人群分析`.`城市層級`
ORDER BY 點選率 DESC;

由圖可知，第1層級點選率較低，而第1、4層級展現量也較低，處於中間第2層級展現量以及點選率都很高，後續可以適度關注第2層級城市的投放。

六、總結和建議

**1.**商品價格是109元，在對應類目商品上，這個價格屬於普通價位，受眾較多。故對應廣告的展現量以及點選量也處於較高的水平。
**2.**由上面分析可知產品在兩個資源位點選率並無較大差別。
**3.**每天上午10點左右，下午3點左右的廣告點選率較高，也與商家促銷活動、節假日時間等因素相關。可以根據推廣需求，及轉化率對於這幾個時間區間設定溢價，提高該時段的展現，提高整個計劃的點選率。
**4.**廣告對於否是大學生人群、購物深度這兩個特性不敏感。對於城市層級，年齡層次，消費檔次人群的特性比較敏感。結合廣告商品本身特點，推測該商品主要消費人群是年齡處於1-2層級，消費能力一般且處於社會中層的群體。而且分析中發現女性遠比男性展現量的點選率高，故廣告推廣時可以考慮多推薦一些女性相關的產品。