本文举例说明了如何构建对 Ad Manager 数据传输报告的查询。详细了解数据传输文件,包括如何开始接收此类文件。
请注意,本文以前称为“数据传输实战宝典”。
广告空缺的展示次数
在 NetworkImpressions
中查询广告空缺的展示次数
如需查看一天内广告空缺的展示次数,请在 NetworkImpressions
中查询 LineItemID
为 0
的条目。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
可帮助您找到会产生广告空缺的展示的前 10 个网页。
代码
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 报表。从 NetworkImpression
(其中 StackdriverID
不为零)和 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
结果
行 | Product |
DealType |
Count |
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 | 广告服务器 | 首选交易 | 所有其他流量 | 1000000 |
9 | 广告服务器 | 有保证的程序化交易 | 所有其他流量 | 1,200,000 |
10 | Ad Exchange | 公开竞价 | 所有其他流量 | 48,000,000 |
11 | Ad Exchange | 公开竞价 | 优先认购 | 100000 |
12 | Ad Exchange | 公开竞价 | 优化型竞争 | 10000 |
13 | Ad Exchange | 公开竞价 | 目标每千次展示费用 | 11,990,000 |
14 | Ad Exchange | 私下竞价 | 所有其他流量 | 1,995,000 |
15 | Ad Exchange | 私下竞价 | 优化型竞争 | 5000 |
直接
- 数据传输:
Product
=广告服务器
DealType
为null
- 第 3 行:40,000,000
- Ad Manager 报表:
- 需求渠道 =“广告服务器”
- 程序化渠道 =“(不适用)”
- 优化类型 =“所有其他流量”
- 第 7 行:40,000,000
首选交易
- 数据传输:
Product
=广告服务器
DealType
为首选交易
- 第 4 行:1,000,000
- Ad Manager 报表:
- 需求渠道 =“广告服务器”
- 程序化渠道 =“首选交易”
- 优化类型 =“所有其他流量”
- 第 8 行:1,000,000
- 数据传输:
Product
=广告交易平台出价功能
DealType
为首选交易
- 第 7 行:20,000
-
- Ad Manager 报表:
- 需求渠道 =“公开出价”
- 程序化渠道 =“首选交易”
- 优化类型 =“所有其他流量”
- 第 4 行:20,000
有保证的程序化交易
- 数据传输:
Product
=广告服务器
DealType
为有保证的程序化交易
- 第 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
交易类型
为私下竞价
- 第 2 行:2,000,000
- Ad Manager 报表:
- 需求渠道 =“Ad Exchange”
- 程序化渠道 =“私下竞价”
- 优化类型 =“所有其他流量”“优化型竞争”
- 第 14 行和第 15 行总计:1,995,000 + 5,000 = 2,000,000
公开出价公开竞价
- 数据传输:
Product
=广告交易平台出价功能
DealType
为null
- 第 6 行:15,000,000
- Ad Manager 报表:
- 需求渠道 =“公开出价”
- 程序化渠道 =“公开竞价”
- 优化类型 =“所有其他流量”“目标每千次展示费用”“优化型竞争”
- 第 1 行、第 2 行和第 3 行总计:9,000,000 + 7,000 + 5,993,000 = 15,000,000
公开出价私下竞价
- 数据传输:
Product
=广告交易平台出价功能
交易类型
为私下竞价
- 第 8 行:500,000
- Ad Manager 报表:
- 需求渠道 =“公开出价”
- 程序化渠道 =“私下竞价”
- 优化类型 =“所有其他流量”“优化型竞争”
- 第 5 行和第 6 行总计:496,000 + 4,000 = 500,000
优先认购
- 数据传输:
Product
=优先认购
DealType
为null
- 第 9 行:100,000
- Ad Manager 报表:
- 需求渠道 =“Ad Exchange”
- 程序化渠道 =“公开竞价”
- 优化类型 =“优先认购”
- 第 11 行:100,000
收入
CPM 订单项的收入
NetworkImpressions
文件不包含收入数据,但如果您使用 BigQuery 连接器,则可以使用订单项匹配表来找出 CPM 费率。否则,请使用 Ad Manager API 找出订单项的费率。统计展示次数,然后乘以费率,再除以 1,000,即可得出某个给定 CPM 订单项在给定日期范围内的收入。由于匹配表包含每天的广告单元数据,因此请务必将匹配表数据限制为特定的一天。
代码
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 |
Impressions |
Rate |
CostType |
Revenue |
1 | 123456789 | 21324 | 3.5 | 每千次展示费用 | 74.634 |
CPD 订单项的收入
与 CPM 订单项一样,您可以使用订单项匹配表或 Ad Manager API 找出订单项的 CPD 费率。由于匹配表包含每天的广告单元数据,因此请务必将匹配表数据限制为特定的一天。如需查看给定 CPD 订单项的收入,请统计投放天数,然后再乘以费率得出。您可能需要包含已投放的展示次数,以得出平均 eCPM。
代码
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 |
Days |
CostType |
Rate |
Revenue |
Impressions |
Average_eCPM |
1 | 123456789 | 5 | 每日费用 | 4000.0 | 20000.0 | 7000000 | 2.86 |
CPC 订单项的收入
与 CPM 订单项一样,您可以使用订单项匹配表或 Ad Manager API 找出订单项的 CPC 费率。由于匹配表包含每天的广告单元数据,因此请务必将匹配表数据限制为特定的一天。如需查看给定日期范围内给定 CPC 订单项的收入,请统计点击次数,然后再乘以费率计算得出。您可能需要包含已投放的展示次数,以得出平均 eCPM。
代码
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 |
Impressions |
点击次数 |
CTR |
Rate |
Revenue |
Average_eCPM |
1 | 123456789 | 每次点击费用 | 140000 | 23 | 0.02 | 15.5 | 356.5 | 2.55 |
vCPM 订单项的收入
与 CPM 订单项一样,您可以使用订单项匹配表或 Ad Manager API 找出订单项的 vCPM 费率。由于匹配表包含每天的广告单元数据,因此请务必将匹配表数据限制为特定的一天。如需查看给定 vCPM 订单项的收入,请从 NetworkActiveViews
统计可见展示的次数,然后再乘以费率得出。您可能需要包含已投放的展示次数,以得出平均 eCPM。
代码
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 |
Impressions |
ViewableImpressions |
Rate |
Revenue |
Average_eCPM |
1 | 123456789 | 每千次 ActiveView 可见展示费用 | 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 |
CompanyName |
LineItemID |
Impressions |
Rate |
CostType |
Revenue |
1 | 111222333 | ABC | 111111111 | 20212 | 5.0 | 每千次展示费用 | 101.06 |
2 | 111222333 | ABC | 222222222 | 58321 | 3.0 | 每千次展示费用 | 174.963 |
3 | 111222333 | ABC | 333333333 | 82772 | 8.5 | 每千次展示费用 | 703.562 |
4 | 111222333 | ABC | 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 |
Impressions |
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 |
Impressions |
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 文件中找到可见度数据。详细了解这些字段及其值的设置方式。
某个时间段的可见度按如下方法计算:将在文件中找到的值加总,或者在合并各个事件后再将这些值加总。这两种策略可能略有不同,详情如下。
符合条件的展示次数、可衡量的展示次数和可见的展示次数(总计)
找出给定日期的符合条件的展示次数、可衡量的展示次数和可见的展示次数。如上所述,必须使用 Impression 文件和 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
结果
行 | 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
结果
行 | Bidder |
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
结果
行 | 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
结果
行 | Segment |
Count |
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 |
Position |
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 |