本文會以實例說明如何為 Ad Manager 資料移轉報表建構查詢。如要進一步瞭解資料移轉檔案,包括如何開始接收這類檔案,請參閱這篇文章。
請注意,本文先前稱為《資料移轉教戰手冊》。
廣告空缺的曝光
來自 NetworkImpressions
的廣告空缺的曝光
如要尋找某一天的廣告空缺曝光次數,請在 LineItemID
為 0
的項目中查詢 NetworkImpressions
。請注意,NetworkBackfillImpressions
中沒有廣告空缺的曝光資料。
程式碼
SELECT COUNT(1) AS UnfilledImpressions FROM NetworkImpressions WHERE LineItemID = 0 AND Time >= ‘2020-01-01’ AND Time < ‘2020-01–02’
結果
列 | UnfilledImpressions |
1 | 20000000 |
來自 NetworkRequests
的廣告空缺的曝光
您也可以查詢 NetworkRequests
查看廣告空缺的曝光次數。請找出 IsFilledRequest
為 false 的要求。請注意,NetworkBackfillRequests
中沒有廣告空缺的曝光資料。
程式碼
SELECT COUNT(1) AS UnfilledImpressions FROM NetworkRequests WHERE NOT IsFilledRequest AND Time >= '2020-01-01' AND Time < '2020-01-02'
結果
列 | UnfilledImpressions |
1 | 20000000 |
網址導致的廣告空缺的曝光
Ad Manager 報表功能可以顯示因廣告單元或請求大小導致的廣告空缺的曝光,但如為網址造成則無法顯示。加入 RefererURL
,協助您找出前十大會產生廣告空缺的曝光的網頁。
程式碼
SELECT RefererURL, COUNT(1) AS UnfilledImpressions FROM NetworkImpressions WHERE LineItemID = 0 AND Time >= '2020-01-01' AND Time < '2020-01-02' GROUP BY RefererURL ORDER BY UnfilledImpressions DESC LIMIT 10
結果
列 | RefererURL |
UnfilledImpressions |
1 | http://example.com/ | 4903691 |
2 | http://example.com/url/a | 748271 |
3 | http://example.com/url/b | 383293 |
4 | http://example.com/url/c | 364355 |
5 | http://example.com/url/d | 326495 |
6 | http://example.net/ | 295346 |
7 | http://example.net/url/a | 291043 |
8 | http://example.net/url/b | 276106 |
9 | http://example.net/url/c | 231169 |
10 | http://example.net/url/d | 194988 |
廣告單元導致的廣告空缺的曝光
找出單一網頁上導致最多廣告空缺的曝光的廣告單元。如果是使用 BigQuery 連接器,可以透過「廣告單元對照表」尋找廣告單元名稱。對照表涵蓋每天的廣告單元資料,因此請務必將對照表資料的範圍限定為特定日期。
程式碼
SELECT AdUnitID, Name AS AdUnitName, COUNT(1) AS UnfilledImpressions FROM NetworkImpressions AS NI INNER JOIN MatchTableAdUnit AS MTAU ON AdUnitID = ID AND LineItemID = 0 AND Time >= '2020-01-01' AND Time < '2020-01-02' AND RefererURL = 'https://example.com/' AND MTAU._DATA_DATE = '2020-01-01' GROUP BY AdUnitID, AdUnitName ORDER BY UnfilledImpressions DESC, AdUnitID LIMIT 10
結果
列 | AdUnitID |
AdUnitName |
UnfilledImpressions |
1 | 95730695 | 上一個層級 A 的名稱 | 1123439 |
2 | 95033015 | 上一個層級 B 的名稱 | 1116622 |
3 | 95033615 | 上一個層級 C 的名稱 | 1102641 |
4 | 95049575 | 上一個層級 D 的名稱 | 772235 |
5 | 95734535 | 上一個層級 E 的名稱 | 744777 |
6 | 95584895 | 上一個層級 F 的名稱 | 27593 |
7 | 95045255 | 上一個層級 G 的名稱 | 7482 |
8 | 95343215 | 上一個層級 H 的名稱 | 1925 |
9 | 94977215 | 上一個層級 I 的名稱 | 19 |
10 | 95033375 | 上一個層級 J 的名稱 | 12 |
曝光
根據 Product
和 DealType
顯示的曝光次數
使用資料移轉功能的 Product
和 DealType
欄位所產生的報表,相當於採用「需求管道」、「程式輔助管道」和「最佳化類型」維度的 Ad Manager 報表。從 NetworkImpressions
(其中 LineItemID
不是零) 和 NetworkBackfillImpressions
中選取曝光次數。
程式碼
SELECT Product, DealType, COUNT(1) AS Impressions FROM NetworkImpressions WHERE LineItemID != 0 AND Time >= '2020-01-01' AND Time < '2020-01-02' GROUP BY Product, DealType UNION ALL SELECT Product, DealType, COUNT(1) AS Impressions FROM NetworkBackfillImpressions WHERE Time >= '2020-01-01' AND Time < '2020-01-02' GROUP BY Product, DealType ORDER BY Product, DealType
結果
列 | 產品 |
DealType |
計數 |
1 | Ad Exchange | null | 60000000 |
2 | Ad Exchange | 私下競價 | 2000000 |
3 | 廣告伺服器 | null | 40000000 |
4 | 廣告伺服器 | 首選交易 | 1000000 |
5 | 廣告伺服器 | 程式輔助保證 | 1200000 |
6 | 廣告交易平台出價 | null | 15000000 |
7 | 廣告交易平台出價 | 首選交易 | 20000 |
8 | 廣告交易平台出價 | 私下競價 | 500000 |
9 | 優先認購 | null | 100000 |
報表參數
在 Ad Manager 報表中使用相同日期執行報表。選擇下列維度和指標:
- 尺寸:
- 需求管道
- 程式輔助管道
- 最佳化類型
- 指標:
- 曝光總數
- 曝光總數
結果
列 | 需求管道 | 程式輔助管道 | 最佳化類型 | 曝光總數 |
1 | 公開出價 | 公開競價 | 所有其他流量 | 9,000,000 |
2 | 公開出價 | 公開競價 | 最佳化競爭 | 7,000 |
3 | 公開出價 | 公開競價 | 目標千次曝光出價 | 5,993,000 |
4 | 公開出價 | 首選交易 | 所有其他流量 | 20,000 |
5 | 公開出價 | 私下競價 | 所有其他流量 | 496,000 |
6 | 公開出價 | 私下競價 | 最佳化競爭 | 4,000 |
7 | 廣告伺服器 | (不適用) | 所有其他流量 | 40,000,000 |
8 | 廣告伺服器 | 首選交易 | 所有其他流量 | 1,000,000 |
9 | 廣告伺服器 | 程式輔助保證 | 所有其他流量 | 1,200,000 |
10 | Ad Exchange | 公開競價 | 所有其他流量 | 48,000,000 |
11 | Ad Exchange | 公開競價 | 優先認購 | 100,000 |
12 | Ad Exchange | 公開競價 | 最佳化競爭 | 10,000 |
13 | Ad Exchange | 公開競價 | 目標千次曝光出價 | 11,990,000 |
14 | Ad Exchange | 私下競價 | 所有其他流量 | 1,995,000 |
15 | Ad Exchange | 私下競價 | 最佳化競爭 | 5,000 |
直接
- 資料移轉:
Product
=Ad Server
DealType
=null
- 第 3 列:40,000,000
- Ad Manager 報表:
- 需求管道 =「廣告伺服器」
- 程式輔助管道 =「(不適用)」
- 最佳化類型 =「所有其他流量」
- 第 7 列:40,000,000
首選交易
- 資料移轉:
Product
=Ad Server
DealType
為Preferred Deal
- 第 4 列:1,000,000
- Ad Manager 報表:
- 需求管道 =「廣告伺服器」
- 程式輔助管道 =「首選交易」
- 最佳化類型 =「所有其他流量」
- 第 8 列:1,000,000
- 資料移轉:
Product
=Exchange Bidding
DealType
為Preferred Deal
- 第 7 列:20,000
-
- Ad Manager 報表:
- 需求管道 =「公開出價」
- 程式輔助管道 =「首選交易」
- 最佳化類型 =「所有其他流量」
- 第 4 列:20,000
程式輔助保證
- 資料移轉:
Product
=Ad Server
DealType
為Programmatic Guaranteed
- 第 5 列:1,200,000
- Ad Manager 報表:
- 需求管道 =「廣告伺服器」
- 程式輔助管道 =「程式輔助保證」
- 最佳化類型 =「所有其他流量」
- 第 9 列:1,200,000
Ad Exchange 公開競價 (不包含優先認購)
- 資料移轉:
Product
=Ad Exchange
DealType
=null
- 第 1 列:60,000,000
- Ad Manager 報表:
- 需求管道 =「Ad Exchange」
- 程式輔助管道 =「公開競價」
- 最佳化類型 =「所有其他流量」、「目標千次曝光出價」、「最佳化競爭」
- 第 10 列、第 12 列和第 13 列總計:48,000,000 + 10,000 + 11,990,000 = 60,000,000
Ad Exchange 私下競價
- 資料移轉:
Product
=Ad Exchange
DealType
為Private Auction
- 第 2 列:2,000,000
- Ad Manager 報表:
- 需求管道 =「Ad Exchange」
- 程式輔助管道 =「私下競價」
- 最佳化類型 =「所有其他流量」、「最佳化競爭」
- 第 14 列和第 15 列總計:1,995,000 + 5,000 = 2,000,000
公開出價公開競價
- 資料移轉:
Product
=Exchange Bidding
DealType
=null
- 第 6 列:15,000,000
- Ad Manager 報表:
- 需求管道 =「公開出價」
- 程式輔助管道 =「公開競價」
- 最佳化類型 =「所有其他流量」、「目標千次曝光出價」、「最佳化競爭」
- 第 1 列、第 2 列和第 3 列總計:9,000,000 + 7,000 + 5,993,000 = 15,000,000
公開出價私下競價
- 資料移轉:
Product
=Exchange Bidding
DealType
為Private Auction
- 第 8 列:500,000
- Ad Manager 報表:
- 需求管道 =「公開出價」
- 程式輔助管道 =「私下競價」
- 最佳化類型 =「所有其他流量」、「最佳化競爭」
- 第 5 列和第 6 列總計:496,000 + 4,000 = 500,000
優先認購
- 資料移轉:
Product
=First Look
DealType
=null
- 第 9 列:100,000
- Ad Manager 報表:
- 需求管道 =「Ad Exchange」
- 程式輔助管道 =「公開競價」
- 最佳化類型 =「優先認購」
- 第 11 列:100,000
收益
千次曝光出價委刊項的收益
NetworkImpressions
檔案不含收益資料,但如果您使用 BigQuery 連接器,就可以使用委刊項對照表找出千次曝光出價費率。否則,請使用 Ad Manager API 尋找委刊項的費率。找出特定日期範圍內的指定千次曝光出價委刊項收益,計算方式依序是計算曝光次數,乘以費率,再除以 1,000。對照表涵蓋每天的廣告單元資料,因此請務必將對照表資料的範圍限定為特定日期。
程式碼
WITH Impression_Data AS ( SELECT LineItemID, COUNT(1) AS Impressions FROM NetworkImpressions WHERE LineItemID = 123456789 AND Time >= '2020-01-01' AND Time < '2020-01-11' GROUP BY LineItemID ) SELECT LineItemID, Impressions, CostPerUnitInNetworkCurrency AS Rate, CostType, ((Impressions * CostPerUnitInNetworkCurrency) / 1000) AS Revenue FROM Impression_Data JOIN MatchTableLineItem ON LineItemID = ID WHERE MatchTableLineItem._DATA_DATE = '2020-01-10'
結果
列 | LineItemID |
曝光 |
費率 |
CostType |
Revenue |
1 | 123456789 | 21324 | 3.5 | 千次曝光出價 | 74.634 |
每日費用委刊項的收益
與千次曝光出價委刊項一樣,您可以使用委刊項對照表或 Ad Manager API,找出委刊項的每日費用費率。對照表涵蓋每天的廣告單元資料,因此請務必將對照表資料的範圍限定為特定日期。如果要找出特定每日費用委刊項的收益,請計算放送的曝光天數,然後乘以費率。建議您納入放送的曝光次數,以找出平均有效千次曝光出價。
程式碼
WITH Impression_Data AS ( SELECT SUBSTR(Time, 0, 10) AS Date, LineItemID, CostPerUnitInNetworkCurrency AS Rate, CostType, COUNT(1) AS Impressions FROM NetworkImpressions JOIN MatchTableLineItem ON LineItemID = ID WHERE LineItemID = 123456789 AND MatchTableLineItem._DATA_DATE = '2020-01-01' GROUP BY Date, LineItemID, Rate, CostType ) SELECT LineItemID, COUNT(1) AS Days, CostType, Rate, (COUNT(1) * Rate) AS Revenue, SUM(Impressions) AS Impressions, ROUND((COUNT(1) * Rate) / SUM(Impressions) * 1000, 2) AS Average_eCPM FROM Impression_Data GROUP BY LineItemID, CostType, Rate
結果
列 | LineItemID |
天 |
CostType |
費率 |
Revenue |
曝光 |
Average_eCPM |
1 | 123456789 | 5 | 每日花費 | 4000.0 | 20000.0 | 7000000 | 2.86 |
單次點擊出價委刊項的收益
與千次曝光出價委刊項一樣,您可以使用委刊項對照表或 Ad Manager API,找出委刊項的單次點擊出價費率。對照表涵蓋每天的廣告單元資料,因此請務必將對照表資料的範圍限定為特定日期。如果要找出特定日期範圍內的指定單次點擊出價委刊項收益,請計算點擊次數,然後乘以費率。建議您納入放送的曝光次數,以找出平均有效千次曝光出價。
程式碼
WITH Impression_Data AS ( SELECT LineItemID, COUNT(1) AS Impressions FROM NetworkImpressions WHERE LineItemID = 123456789 GROUP BY LineItemID ), Click_Data AS ( SELECT LineItemID, CostPerUnitInNetworkCurrency AS Rate, CostType, COUNT(1) AS Clicks FROM NetworkClicks JOIN MatchTableLineItem ON LineItemID = ID WHERE LineItemID = 123456789 AND MatchTableLineItem._DATA_DATE = '2020-01-01' GROUP BY LineItemID, Rate, CostType ) SELECT LineItemID, CostType, Impressions, Clicks, ROUND(Clicks / Impressions * 100, 2) AS CTR, Rate, (Clicks * Rate) AS Revenue, ROUND((Clicks * Rate) / Impressions * 1000, 2) AS Average_eCPM FROM Impression_Data JOIN Click_Data USING (LineItemID)
結果
列 | LineItemID |
CostType |
曝光 |
點擊次數 |
點閱率 |
費率 |
Revenue |
Average_eCPM |
1 | 123456789 | CPC | 140000 | 23 | 0.02 | 15.5 | 356.5 | 2.55 |
千次可視曝光出價委刊項的收益
與千次曝光出價委刊項一樣,您可以使用委刊項對照表或 Ad Manager API,找出委刊項的千次可視曝光出價費率。對照表涵蓋每天的廣告單元資料,因此請務必將對照表資料的範圍限定為特定日期。若要查看特定千次可視曝光出價委刊項的收益,請從 NetworkActiveViews
計算可視曝光次數,再乘以費率。建議您納入放送的曝光次數,以找出平均有效千次曝光出價。
程式碼
WITH Active_View_Data AS ( SELECT LineItemID, COUNT(1) AS ViewableImpressions FROM NetworkActiveViews WHERE LineItemID = 123456789 GROUP BY LineItemID ), Impression_Data AS ( SELECT LineItemID, COUNT(1) AS Impressions FROM NetworkImpressions WHERE LineItemID = 123456789 GROUP BY LineItemID ) SELECT Active_View_Data.LineItemID, CostType, Impressions, ViewableImpressions, CostPerUnitInNetworkCurrency AS Rate, (CostPerUnitInNetworkCurrency * ViewableImpressions / 1000) AS Revenue, ROUND((CostPerUnitInNetworkCurrency * ViewableImpressions / 1000) / Impressions * 1000, 2) AS Average_eCPM FROM Impression_Data JOIN Active_View_Data USING (LineItemID) JOIN MatchTableLineItem ON Active_View_Data.LineItemID = ID WHERE MatchTableLineItem._DATA_DATE = '2020-08-01'
結果
列 | LineItemID |
CostType |
曝光 |
ViewableImpressions |
費率 |
Revenue |
Average_eCPM |
1 | 123456789 | CPMAV | 500000 | 150000 | 10 | 1500.0 | 3.0 |
廣告主的收益
如果要找出特定日期範圍內的指定廣告客戶收益,請計算每個委刊項的曝光次數,然後乘以費率。使用委刊項對照表找出費率,並使用公司對照表找出廣告客戶名稱。
程式碼
WITH Impression_Data AS ( SELECT AdvertiserID, LineItemID, COUNT(1) AS Impressions FROM NetworkImpressions WHERE AdvertiserID = 111222333 AND Time >= '2020-01-01' AND Time < '2020-01-02' GROUP BY AdvertiserID, LineItemID ) SELECT AdvertiserID, MTC.Name AS CompanyName, LineItemID, Impressions, CostPerUnitInNetworkCurrency AS Rate, CostType, ((Impressions * CostPerUnitInNetworkCurrency) / 1000) AS Revenue FROM Impression_Data JOIN MatchTableLineItem AS MTLI ON LineItemID = MTLI.ID JOIN MatchTableCompany AS MTC ON AdvertiserID = MTC.ID WHERE MTLI._DATA_DATE = '2020-01-01' AND MTC._DATA_DATE = '2020-01-01'
結果
列 | AdvertiserID |
公司名稱 |
LineItemID |
曝光 |
費率 |
CostType |
Revenue |
1 | 111222333 | 中國農業銀行 | 111111111 | 20212 | 5.0 | 千次曝光出價 | 101.06 |
2 | 111222333 | 中國農業銀行 | 222222222 | 58321 | 3.0 | 千次曝光出價 | 174.963 |
3 | 111222333 | 中國農業銀行 | 333333333 | 82772 | 8.5 | 千次曝光出價 | 703.562 |
4 | 111222333 | 中國農業銀行 | 444444444 | 19003 | 3.25 | 千次曝光出價 | 61.7597 |
程式碼調用次數
針對已啟用備用廣告的聯播網,資料移轉功能會針對備用廣告鏈中所選的每個委刊項計算程式碼調用次數,而 Ad Manager 報表功能只會針對備用廣告鏈中所選的第一個委刊項計算程式碼調用次數。資料移轉功能也會計算隨播廣告的程式碼調用次數,但 Ad Manager 報表則不會。如果您希望資料移轉報表盡量與 Ad Manager 報表一致,則只在 VideoFallbackPosition = 0
和 IsCompanion
為 false
時,計算程式碼調用次數。資料移轉中的中介服務程式碼調用次數可能與 Ad Manager 報表中的中介服務程式碼調用次數不符。視您的導入方式而定,資料移轉和 Ad Manager 報表中的程式碼調用次數可能會有所差異。
單一廣告客戶按委刊項顯示的的程式碼調用次數、曝光次數和顯示率
瞭解程式碼調用次數能帶來直接廣告客戶每個委刊項的曝光次數的頻率。由於我們查看的是直接廣告客戶,因此程式碼調用次數只會出現在 NetworkCodeServes
中,而曝光只會出現在 NetworkImpressions
。
程式碼
WITH Code_Serve_Data AS ( SELECT LineItemID, COUNT(1) AS CodeServes FROM NetworkCodeServes WHERE AdvertiserID = 12345678 AND VideoFallbackPosition = 0 AND IsCompanion IS FALSE AND Time >= '2020-01-01' AND Time < '2020-01-02' GROUP BY LineItemID ), Impression_Data AS ( SELECT LineItemID, COUNT(1) AS Impressions FROM NetworkImpressions WHERE AdvertiserID = 12345678 AND Time >= '2020-01-01' AND Time < '2020-01-02' GROUP BY LineItemID ) SELECT LineItemID, CodeServes, Impressions, ROUND((Impressions / CodeServes) * 100, 2) AS RenderRate FROM Code_Serve_Data JOIN Impression_Data USING (LineItemID) ORDER BY RenderRate DESC
結果
列 | LineItemID |
CodeServes |
曝光 |
RenderRate |
1 | 1111111111 | 6000 | 2600 | 43.33 |
2 | 2222222222 | 1000000 | 371200 | 37.12 |
3 | 3333333333 | 50000 | 17550 | 35.1 |
4 | 4444444444 | 800000 | 275000 | 34.38 |
5 | 5555555555 | 1500000 | 400000 | 26.66 |
按裝置類別和廣告素材大小 (已放送) 顯示的程式碼調用次數、曝光次數和顯示率
加入「裝置類別」和「廣告素材大小 (已放送)」,即可查看單一廣告客戶的單一訂單顯示率差異。
程式碼
WITH Code_Serve_Data AS ( SELECT LineItemID, CreativeSizeDelivered, DeviceCategory, COUNT(1) AS CodeServes FROM NetworkCodeServes WHERE AdvertiserID = 87654321 AND OrderID = 1111111111 AND VideoFallbackPosition = 0 AND IsCompanion IS FALSE GROUP BY LineItemID, CreativeSizeDelivered, DeviceCategory ), Impression_Data AS ( SELECT LineItemID, CreativeSizeDelivered, DeviceCategory, COUNT(1) AS Impressions FROM NetworkImpressions WHERE AdvertiserID = 87654321 AND OrderID = 1111111111 GROUP BY LineItemID, CreativeSizeDelivered, DeviceCategory ) SELECT LineItemID, DeviceCategory, CreativeSizeDelivered, CodeServes, Impressions, ROUND((Impressions / CodeServes) * 100, 2) AS RenderRate FROM Code_Serve_Data JOIN Impression_Data USING (LineItemID, CreativeSizeDelivered, DeviceCategory) ORDER BY LineItemID, CreativeSizeDelivered, DeviceCategory
結果
列 | LineItemID |
DeviceCategory |
CreativeSizeDelivered |
CodeServes |
曝光 |
RenderRate |
1 | 6666666666 | 連網電視 | 影片播放器大小 | 100 | 40 | 40.0 |
2 | 6666666666 | 電腦 | 影片播放器大小 | 20000 | 9000 | 45.0 |
3 | 6666666666 | 智慧型手機 | 影片播放器大小 | 32000 | 25000 | 78.13 |
4 | 6666666666 | 平板電腦 | 影片播放器大小 | 1000 | 800 | 80.0 |
5 | 7777777777 | 連網電視 | 300x250 | 200 | 190 | 95.0 |
6 | 7777777777 | 電腦 | 300x250 | 185000 | 184000 | 99.46 |
7 | 7777777777 | 智慧型手機 | 300x250 | 225000 | 220000 | 97.77 |
8 | 7777777777 | 平板電腦 | 300x250 | 10000 | 9800 | 98.0 |
9 | 7777777777 | 連網電視 | 300x50 | 50 | 50 | 100.0 |
10 | 7777777777 | 電腦 | 300x50 | 1000 | 900 | 90.0 |
11 | 7777777777 | 智慧型手機 | 300x50 | 90000 | 80000 | 88.89 |
12 | 7777777777 | 平板電腦 | 300x50 | 800 | 750 | 93.75 |
可視度
您可以使用 ActiveViewEligibleCount
、ActiveViewMeasurableCount
和 ActiveViewViewableCount
欄位,在曝光和 ActiveView 檔案中找到可視度資料。如要進一步瞭解這些欄位和欄位值的設定方式,請參閱這篇文章。
如要計算特定時間範圍的可視度,請將檔案中找到的值加總,或是彙整個別事件後加總這些值。這兩種策略可能會有些微差異,詳情如下所示。
符合資格的曝光、可評估的曝光和可視曝光 (總計)
找出特定日期的符合資格、可評估和可視曝光次數。如上所述,您必須使用曝光資料檔案和 ActiveView 檔案。
程式碼
DECLARE startdate STRING DEFAULT '2023-01-01 00:00:00';
DECLARE enddate STRING DEFAULT '2023-01-02 00:00:00';
WITH ActiveView_Data AS (
SELECT DeviceCategory, VideoPosition,
SUM(IFNULL(ActiveViewMeasurableCount, 0)) AS ActiveViewMeasurableCount,
SUM(IFNULL(ActiveViewViewableCount, 0)) AS ActiveViewViewableCount
FROM (
SELECT
DeviceCategory, VideoPosition,
SUM(ActiveViewMeasurableCount) AS ActiveViewMeasurableCount,
SUM(ActiveViewViewableCount) AS ActiveViewViewableCount
FROM
NetworkActiveViews
WHERE Time >= startdate AND Time < enddate
GROUP BY DeviceCategory, VideoPosition
UNION ALL
SELECT
DeviceCategory, VideoPosition,
SUM(ActiveViewMeasurableCount) AS ActiveViewMeasurableCount,
SUM(ActiveViewViewableCount) AS ActiveViewViewableCount
FROM
NetworkBackfillActiveViews
WHERE Time >= startdate AND Time < enddate
GROUP BY DeviceCategory, VideoPosition
)
GROUP BY DeviceCategory, VideoPosition
), Impression_Data AS (
SELECT DeviceCategory, VideoPosition,
SUM(IFNULL(ActiveViewEligibleCount, 0)) AS ActiveViewEligibleCount,
SUM(IFNULL(ActiveViewMeasurableCount, 0)) AS ActiveViewMeasurableCount
FROM (
SELECT
DeviceCategory, VideoPosition,
SUM(ActiveViewEligibleCount) AS ActiveViewEligibleCount,
SUM(ActiveViewMeasurableCount) AS ActiveViewMeasurableCount
FROM
NetworkImpressions
WHERE
Time >= startdate AND Time < enddate
AND LineItemID !=0
GROUP BY DeviceCategory, VideoPosition
UNION ALL
SELECT
DeviceCategory, VideoPosition,
SUM(ActiveViewEligibleCount) AS ActiveViewEligibleCount,
SUM(ActiveViewMeasurableCount) AS ActiveViewMeasurableCount
FROM
NetworkBackfillImpressions
WHERE
Time >= startdate AND Time < enddate
GROUP BY DeviceCategory, VideoPosition
)
GROUP BY DeviceCategory, VideoPosition
)
SELECT
DeviceCategory,
VideoPosition,
IFNULL(ActiveViewEligibleCount, 0) AS ActiveViewEligibleCount,
IFNULL(i.ActiveViewMeasurableCount, 0) + IFNULL(av.ActiveViewMeasurableCount, 0) AS ActiveViewMeasurableCount,
IFNULL(ActiveViewViewableCount, 0) AS ActiveViewViewableCount
FROM Impression_Data i
FULL JOIN ActiveView_Data av USING (DeviceCategory, VideoPosition)
ORDER BY DeviceCategory, VideoPosition
結果
列 | EligibleImpressions |
MeasurableImpressions |
ViewableImpressions |
1 | 97000000 | 95000000 | 60000000 |
符合資格的曝光、可評估的曝光和可視曝光 (依合併曝光次數)
彙整曝光資料檔案和 ActiveView 檔案中的個別事件,然後加總這些值,即可找出特定日期的符合資格、可評估和可視曝光次數。在極少數的情況下,我們會收到 Active View 可視率連線偵測 (ping),但不會收到曝光連線偵測 (ping)。將 ActiveView 資料與曝光資料彙整後,可能會發現相對於首次查詢,可評估的曝光和更少的可視曝光有所不同,如上所示。
程式碼
DECLARE startdate STRING DEFAULT '2023-01-01 00:00:00'; DECLARE enddate STRING DEFAULT '2023-01-02 00:00:00'; WITH ActiveView_Data AS ( SELECT CAST(substr(Time, 0, 10) as Date) AS Date, Product, KeyPart, TimeUsec2, SUM(ActiveViewMeasurableCount) AS avAVMC, SUM(ActiveViewViewableCount) AS ActiveViewViewableCount FROM NetworkActiveViews WHERE Time >= startdate AND Time < enddate GROUP BY Date, Product, KeyPart, TimeUsec2 UNION ALL SELECT CAST(substr(Time, 0, 10) as Date) AS Date, Product, KeyPart, TimeUsec2, SUM(ActiveViewMeasurableCount) AS avAVMC, SUM(ActiveViewViewableCount) AS ActiveViewViewableCount FROM NetworkBackfillActiveViews WHERE Time >= startdate AND Time < enddate GROUP BY Date, Product, KeyPart, TimeUsec2 ), Impression_Data AS ( SELECT CAST(substr(i.Time, 0, 10) as Date) AS Date, i.Product, i.TimeUsec2, i.KeyPart, i.ActiveViewEligibleCount AS ActiveViewEligibleCount, i.ActiveViewMeasurableCount AS iAVMC, av.avAVMC AS avAVMC, av.ActiveViewViewableCount FROM NetworkImpressions i LEFT JOIN ActiveView_Data AS av USING (Product, Keypart, TimeUsec2) WHERE i.Time >= startdate AND i.Time < enddate AND i.LineItemID !=0 UNION ALL SELECT CAST(substr(i.Time, 0, 10) as Date) AS Date, i.Product, i.TimeUsec2, i.KeyPart, i.ActiveViewEligibleCount AS ActiveViewEligibleCount, i.ActiveViewMeasurableCount AS iAVMC, av.avAVMC AS avAVMC, av.ActiveViewViewableCount FROM NetworkBackfillImpressions i LEFT JOIN ActiveView_Data AS av USING (Product, Keypart, TimeUsec2) WHERE i.Time >= startdate AND i.Time < enddate ), Full_Data AS ( SELECT Date, Product, TimeUsec2, KeyPart, ActiveViewEligibleCount, CASE WHEN ActiveViewViewableCount >=1 THEN 1 ELSE (IFNULL(iAVMC, 0) + IFNULL(avAVMC, 0)) END AS ActiveViewMeasurableCount, IFNULL(ActiveViewViewableCount, 0) AS ActiveViewViewableCount FROM Impression_Data ) SELECT SUM(ActiveViewEligibleCount) AS ActiveViewEligibleCount, SUM(ActiveViewMeasurableCount) AS ActiveViewMeasurableCount, SUM(ActiveViewViewableCount) AS ActiveViewViewableCount FROM Full_Data
結果
列 | EligibleImpressions |
MeasurableImpressions |
ViewableImpressions |
1 | 97000000 | 95000000 | 59900000 |
鍵/值
鍵使用方式
瞭解各鍵在廣告請求中的顯示頻率 (顯示在 CustomTargeting
中),以及各鍵用於放送委刊項的頻率 (顯示在 TargetedCustomCriteria
)。未出現在結果中或不常使用的有效鍵可能適合封存,以免超出鍵的上限。
程式碼
WITH Key_Value_Pairs AS ( SELECT KVPair FROM NetworkImpressions CROSS JOIN UNNEST(SPLIT(CustomTargeting, ';')) AS KVPair WHERE CustomTargeting IS NOT NULL UNION ALL SELECT KVPair FROM NetworkBackfillImpressions CROSS JOIN UNNEST(SPLIT(CustomTargeting, ';')) AS KVPair WHERE CustomTargeting IS NOT NULL ), Targeted_Key_Value_Pairs AS ( SELECT TargetedKVPair FROM NetworkImpressions CROSS JOIN UNNEST(SPLIT(TargetedCustomCriteria, ';')) AS TargetedKVPair WHERE TargetedCustomCriteria IS NOT NULL UNION ALL SELECT TargetedKVPair FROM NetworkBackfillImpressions CROSS JOIN UNNEST (SPLIT(TargetedCustomCriteria, ';')) AS TargetedKVPair WHERE TargetedCustomCriteria IS NOT NULL ), Key_Usage AS ( SELECT REGEXP_REPLACE(KVPair, '=.+', '') AS Key, COUNT(1) AS KeyUsageCount FROM Key_Value_Pairs GROUP BY Key ), Key_Targeted_Usage AS ( SELECT REGEXP_REPLACE(TargetedKVPair, '(!)*(=|~).+', '') AS Key, COUNT(1) AS KeyTargetedCount FROM Targeted_Key_Value_Pairs GROUP BY Key ) SELECT CASE WHEN Key_Usage.Key IS NULL THEN Key_Targeted_Usage.Key ELSE Key_Usage.Key END AS Key, KeyUsageCount, KeyTargetedCount FROM Key_Usage FULL JOIN Key_Targeted_Usage ON Key_Usage.Key = Key_Targeted_Usage.Key ORDER BY Key
結果
列 | 鑰匙 |
KeyUsageCount |
KeyTargetedCount |
1 | key_abc | 10000000 | 1000000 |
2 | key_def | 25000000 | 5000000 |
3 | key_ghi | 40000 | 2000 |
4 | key_jkl | 300000 | 12000 |
5 | key_mno | 100000 | 1000 |
出價合作夥伴的出價
從 CustomTargeting
中擷取出價,找出每個合作夥伴出價的頻率。在以下範例中,我們會預期每個合作夥伴的名稱開頭都是「bidder_prefix_
」,與「bidder_prefix_partnername
」中的相同,且預期該合作夥伴的出價會採用「bidder_prefix_partnername=1.23
」格式。
程式碼
SELECT Bidder, COUNT(1) AS BidCount FROM ( SELECT Bidder FROM NetworkImpressions CROSS JOIN UNNEST(REGEXP_EXTRACT_ALL(CustomTargeting, '(bidder_prefix_[A-z]+)=[0-9]+\\.[0-9]*')) AS Bidder WHERE CustomTargeting LIKE '%bidder_prefix_%' AND Time >= '2020-01-01' AND Time < '2020-01-02' UNION ALL SELECT Bidder FROM NetworkBackfillImpressions CROSS JOIN UNNEST(REGEXP_EXTRACT_ALL(CustomTargeting, '(bidder_prefix_[A-z]+)=[0-9]+\\.[0-9]*')) AS Bidder WHERE CustomTargeting LIKE '%bidder_prefix_%' AND Time >= '2020-01-01' AND Time < '2020-01-02' ) GROUP BY Bidder ORDER BY BidCount
結果
列 | 出價方 |
BidCount |
1 | bidder_prefix_partner_1 | 15000000 |
2 | bidder_prefix_partner_2 | 12000000 |
3 | bidder_prefix_partner_3 | 9000000 |
4 | bidder_prefix_partner_4 | 6000000 |
5 | bidder_prefix_partner_5 | 3000000 |
單一出價合作夥伴的出價金額和數量
在單一出價合作夥伴中找出最常見的出價值,以及各出價的出現頻率。在下方範例中,從名為「bidder_partner
」的合作夥伴的曝光次數表格中,選取 10 個最常見的出價;這裡的曝光指 CustomTargeting
含有為出價價格 (例如「1.23
」) 設定的「bidder_partner
」鍵。
程式碼
SELECT BidPrice, SUM(BidCount) AS BidCount FROM ( SELECT SAFE_CAST(REGEXP_EXTRACT(CustomTargeting, 'bidder_partner=([0-9]+\\.[0-9]*)') AS FLOAT64) AS BidPrice, COUNT(1) AS BidCount FROM NetworkImpressions WHERE CustomTargeting LIKE '%bidder_partner=%' AND Time >= '2020-01-01' AND Time < '2020-01-02' GROUP BY BidPrice UNION ALL SELECT SAFE_CAST(REGEXP_EXTRACT(CustomTargeting, 'bidder_partner=([0-9]+\\.[0-9]*)') AS FLOAT64) AS BidPrice, COUNT(1) AS BidCount FROM NetworkBackfillImpressions WHERE CustomTargeting LIKE '%bidder_partner=%' AND Time >= '2020-01-01' AND Time < '2020-01-02' GROUP BY BidPrice ) GROUP BY BidPrice ORDER BY BidCount DESC LIMIT 10
結果
列 | BidPrice |
BidCount |
1 | 0.01 | 600000 |
2 | 0.02 | 500000 |
3 | 0.05 | 400000 |
4 | 0.07 | 300000 |
5 | 0.09 | 200000 |
6 | 0.03 | 150000 |
7 | 0.08 | 100000 |
8 | 0.04 | 75000 |
9 | 0.10 | 50000 |
10 | 0.06 | 25000 |
出價數量和平均出價
在曝光表格中找出所有出價合作夥伴的出價總數和平均出價。在以下範例中,我們會預期每個合作夥伴的名稱開頭都是「bidder_prefix_
」,與「bidder_prefix_partnername
」中的相同,且預期該合作夥伴的出價會採用「bidder_prefix_partnername=1.23
」格式。
程式碼
WITH Bid_Data AS ( SELECT REGEXP_EXTRACT(Bid, '(bidder_prefix_[A-z]+)=[0-9]+\\.[0-9]*') AS Bidder, SAFE_CAST(REGEXP_EXTRACT(Bid, 'bidder_prefix_[A-z]+=([0-9]+\\.[0-9]*)') AS FLOAT64) AS BidPrice, COUNT(1) AS BidCount FROM ( SELECT Bid FROM NetworkImpressions CROSS JOIN UNNEST(REGEXP_EXTRACT_ALL(CustomTargeting, 'bidder_prefix_[A-z]+=[0-9]+\\.[0-9]*')) AS Bid WHERE CustomTargeting LIKE '%bidder_prefix_%' AND Time >= '2020-01-01' AND Time < '2020-01-02' UNION ALL SELECT Bid FROM NetworkBackfillImpressions CROSS JOIN UNNEST(REGEXP_EXTRACT_ALL(CustomTargeting, 'bidder_prefix_[A-z]+=[0-9]+\\.[0-9]*')) AS Bid WHERE CustomTargeting LIKE '%bidder_prefix_%' AND Time >= '2020-01-01' AND Time < '2020-01-02' ) GROUP BY Bidder, BidPrice ), BidPrice_Totals AS ( SELECT Bidder, SUM(BidValue) AS TotalBidValue FROM ( SELECT Bidder, BidPrice * BidCount AS BidValue FROM Bid_Data ) GROUP BY Bidder ), BidCount_Totals AS ( SELECT Bidder, SUM(BidCount) AS TotalBidCount FROM Bid_Data GROUP BY Bidder ) SELECT BidCount_Totals.Bidder, TotalBidCount, ROUND((TotalBidValue / TotalBidCount), 2) AS AverageBid FROM BidCount_Totals INNER JOIN BidPrice_Totals ON BidCount_Totals.Bidder = BidPrice_Totals.Bidder ORDER BY Bidder
結果
列 | 出價方 |
BidCount |
AverageBid |
1 | bidder_prefix_partner_1 | 15000000 | 0.21 |
2 | bidder_prefix_partner_2 | 12000000 | 1.43 |
3 | bidder_prefix_partner_3 | 9000000 | 2.67 |
4 | bidder_prefix_partner_4 | 6000000 | 6.80 |
5 | bidder_prefix_partner_5 | 3000000 | 0.92 |
DMP 區隔數量
資料管理平台通常會以鍵/值組合的形式傳遞使用者所屬的區隔。查看這些區隔在廣告請求中的顯示頻率,也就是可為每個區隔指定的曝光次數。擷取 CustomTargeting 中的區隔 ID。在下方範例中,預期的鍵名稱是「seg」,值則是由英文字母和數字組成。
程式碼
SELECT Segment, COUNT(1) AS Count FROM ( SELECT Segment FROM NetworkImpressions CROSS JOIN UNNEST(REGEXP_EXTRACT_ALL(CustomTargeting, 'seg=([A-z0-9]+)')) AS Segment WHERE CustomTargeting LIKE '%seg=%' AND Time >= '2020-01-01' AND Time < '2020-01-02' UNION ALL SELECT Segment FROM NetworkBackfillImpressions CROSS JOIN UNNEST(REGEXP_EXTRACT_ALL(CustomTargeting, 'seg=([A-z0-9]+)')) AS Segment WHERE CustomTargeting LIKE '%seg=%' AND Time >= '2020-01-01' AND Time < '2020-01-02' ) GROUP BY Segment ORDER BY Count DESC
結果
列 | 區隔 |
計數 |
1 | abcd1234 | 10000000 |
2 | efgh5678 | 9000000 |
3 | ijkl9012 | 8000000 |
4 | mnop3456 | 7000000 |
5 | qrst7890 | 6000000 |
6 | uvwx1234 | 5000000 |
7 | yzab5678 | 4000000 |
8 | cdef9012 | 3000000 |
9 | ghij3456 | 2000000 |
10 | klmn7890 | 1000000 |
影片
因網址、廣告單元 ID 和位置出現的影片錯誤
如要排解發生重大錯誤的影片委刊項問題,您可能需要找出造成錯誤的主要頁面和/或該頁面的廣告版位。在適用情況下,使用 NetworkVideoConversions
依據委刊項 (其中 ActionName
包含「error
」) 來找出錯誤。如果您在單一網頁上有多個影片播放器,這些播放器會使用相同的廣告單元,而您使用鍵 (例如「pos
」) 來區分網頁上的廣告單元,並從 CustomTargeting
擷取 pos
值。下方範例預期該鍵的名稱是「pos
」,並顯示導致單一影片委刊項發生錯誤的前五項 RefererURL
、AdUnitID
與 Position
組合。
程式碼
SELECT RefererURL, AdUnitID, REGEXP_EXTRACT(CustomTargeting, 'pos=([^;]+)') AS Position, COUNT(1) AS ErrorCount FROM NetworkVideoConversions WHERE LineItemID = 123456789 AND ActionName LIKE '%error%' AND Time >= '2020-01-01' AND Time < '2020-01-02' GROUP BY RefererURL, AdUnitID, Position ORDER BY ErrorCount DESC LIMIT 5
結果
列 | RefererURL |
AdUnitID |
位置 |
ErrorCount |
1 | https://example.com/ | 11111111 | top | 2000 |
2 | https://example.com/url/a | 22222222 | top | 1500 |
3 | https://example.com/url/b | 22222222 | top | 1400 |
4 | https://example.com/url/c | 11111111 | top | 1000 |
5 | https://example.com/url/c | 11111111 | bottom | 500 |