This article contains examples of how to construct queries for Ad Manager Data Transfer reports. Learn more about Data Transfer files, including how to start receiving them.
Note that this article was previously called "Data Transfer Cookbook."
Unfilled impressions
Unfilled impressions from NetworkImpressions
To find the number of unfilled impressions for a day, query NetworkImpressions
for entries where LineItemID
is 0
. There are no unfilled impressions in NetworkBackfillImpressions
.
Code
SELECT COUNT(1) AS UnfilledImpressions FROM NetworkImpressions WHERE LineItemID = 0 AND Time >= ‘2020-01-01’ AND Time < ‘2020-01–02’
Results
Row | UnfilledImpressions |
1 | 20000000 |
Unfilled impressions from NetworkRequests
You can also find the number of unfilled impressions by querying NetworkRequests
. Look for requests where IsFilledRequest
is false. There are no unfilled impressions in NetworkBackfillRequests
.
Code
SELECT COUNT(1) AS UnfilledImpressions FROM NetworkRequests WHERE NOT IsFilledRequest AND Time >= '2020-01-01' AND Time < '2020-01-02'
Results
Row | UnfilledImpressions |
1 | 20000000 |
Unfilled impressions by URL
Ad Manager Reporting can show unfilled impressions by ad unit or requested size, but not by URL. Include RefererURL
to help you find the top ten pages that generate unfilled impressions.
Code
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
Results
Row | 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 |
Unfilled impressions by ad unit
Find the ad units that are responsible for the most unfilled impressions on a single page. If you use the BigQuery Connector, use the Ad Unit Match Table to find the name of the ad units. Because the match table contains ad unit data for every day, be sure to limit the match table data to one day.
Code
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
Results
Row | AdUnitID |
AdUnitName |
UnfilledImpressions |
1 | 95730695 | Name of last level A | 1123439 |
2 | 95033015 | Name of last level B | 1116622 |
3 | 95033615 | Name of last level C | 1102641 |
4 | 95049575 | Name of last level D | 772235 |
5 | 95734535 | Name of last level E | 744777 |
6 | 95584895 | Name of last level F | 27593 |
7 | 95045255 | Name of last level G | 7482 |
8 | 95343215 | Name of last level H | 1925 |
9 | 94977215 | Name of last level I | 19 |
10 | 95033375 | Name of last level J | 12 |
Impressions
Impressions by Product
and DealType
Use the Product
and DealType
fields in Data Transfer to generate reports comparable to Ad Manager reports that use the "Demand channel," "Programmatic channel," and "Optimization type" dimensions. Select impressions from NetworkImpressions
(where LineItemID
is not zero) and NetworkBackfillImpressions
.
Code
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
Results
Row | Product |
DealType |
Count |
1 | Ad Exchange | null | 60000000 |
2 | Ad Exchange | Private auction | 2000000 |
3 | Ad Server | null | 40000000 |
4 | Ad Server | Preferred deal | 1000000 |
5 | Ad Server | Programmatic guaranteed | 1200000 |
6 | Exchange Bidding | null | 15000000 |
7 | Exchange Bidding | Preferred deal | 20000 |
8 | Exchange Bidding | Private auction | 500000 |
9 | First Look | null | 100000 |
Report parameters
Run a report in Ad Manager Reporting using the same date. Choose the following dimensions and metrics:
- Dimensions:
- Demand channel
- Programmatic channel
- Optimization type
- Metrics:
- Total impressions
- Total impressions
Results
Row | Demand channel | Programmatic channel | Optimization type | Total impressions |
1 | Open Bidding | Open Auction | All Other Traffic | 9,000,000 |
2 | Open Bidding | Open Auction | Optimized Competition | 7,000 |
3 | Open Bidding | Open Auction | Target CPM | 5,993,000 |
4 | Open Bidding | Preferred Deals | All Other Traffic | 20,000 |
5 | Open Bidding | Private Auction | All Other Traffic | 496,000 |
6 | Open Bidding | Private Auction | Optimized Competition | 4,000 |
7 | Ad server | (not applicable) | All Other Traffic | 40,000,000 |
8 | Ad server | Preferred Deals | All Other Traffic | 1,000,000 |
9 | Ad server | Programmatic Guaranteed | All Other Traffic | 1,200,000 |
10 | Ad Exchange | Open Auction | All Other Traffic | 48,000,000 |
11 | Ad Exchange | Open Auction | First Look | 100,000 |
12 | Ad Exchange | Open Auction | Optimized Competition | 10,000 |
13 | Ad Exchange | Open Auction | Target CPM | 11,990,000 |
14 | Ad Exchange | Private Auction | All Other Traffic | 1,995,000 |
15 | Ad Exchange | Private Auction | Optimized Competition | 5,000 |
Direct
- Data Transfer:
Product
=Ad Server
DealType
isnull
- Row 3: 40,000,000
- Ad Manager Reporting:
- Demand channel = "Ad server"
- Programmatic channel = "(Not applicable)"
- Optimization type = "All Other Traffic"
- Row 7: 40,000,000
Preferred Deals
- Data Transfer:
Product
=Ad Server
DealType
isPreferred Deal
- Row 4: 1,000,000
- Ad Manager Reporting:
- Demand channel = "Ad server"
- Programmatic channel = "Preferred Deals"
- Optimization type = "All Other Traffic"
- Row 8: 1,000,000
- Data Transfer:
Product
=Exchange Bidding
DealType
isPreferred Deal
- Row 7: 20,000
-
- Ad Manager Reporting:
- Demand channel = "Open Bidding"
- Programmatic channel = "Preferred Deals"
- Optimization type = "All Other Traffic"
- Row 4: 20,000
Programmatic Guaranteed
- Data Transfer:
Product
=Ad Server
DealType
isProgrammatic Guaranteed
- Row 5: 1,200,000
- Ad Manager Reporting:
- Demand channel = "Ad server"
- Programmatic channel = "Programmatic Guaranteed"
- Optimization type = "All Other Traffic"
- Row 9: 1,200,000
Ad Exchange Open Auction (not including First Look)
- Data Transfer:
Product
=Ad Exchange
DealType
isnull
- Row 1: 60,000,000
- Ad Manager Reporting:
- Demand channel = "Ad Exchange"
- Programmatic channel = "Open Auction"
- Optimization type = "All Other Traffic," "Target CPM," "Optimized Competition"
- Row 10, Row 12, and Row 13 total: 48,000,000 + 10,000 + 11,990,000 = 60,000,000
Ad Exchange Private Auction
- Data Transfer:
Product
=Ad Exchange
DealType
isPrivate Auction
- Row 2: 2,000,000
- Ad Manager Reporting:
- Demand channel = "Ad Exchange"
- Programmatic channel = "Private Auction"
- Optimization type = "All Other Traffic," "Optimized Competition"
- Row 14 and Row 15 total: 1,995,000 + 5,000 = 2,000,000
Open Bidding Open Auction
- Data Transfer:
Product
=Exchange Bidding
DealType
isnull
- Row 6: 15,000,000
- Ad Manager Reporting:
- Demand channel = "Open Bidding"
- Programmatic channel = "Open Auction"
- Optimization type = "All Other Traffic," "Target CPM," "Optimized Competition"
- Row 1, Row 2, and Row 3 total: 9,000,000 + 7,000 + 5,993,000 = 15,000,000
Open Bidding Private Auction
- Data Transfer:
Product
=Exchange Bidding
DealType
isPrivate Auction
- Row 8: 500,000
- Ad Manager Reporting:
- Demand channel = "Open Bidding"
- Programmatic channel = "Private Auction"
- Optimization type = "All Other Traffic," "Optimized Competition"
- Row 5 and Row 6 total: 496,000 + 4,000 = 500,000
First Look
- Data Transfer:
Product
=First Look
DealType
isnull
- Row 9: 100,000
- Ad Manager Reporting:
- Demand channel = "Ad Exchange"
- Programmatic channel = "Open Auction"
- Optimization type = "First Look"
- Row 11: 100,000
Revenue
Revenue for a CPM line item
The NetworkImpressions
file does not contain revenue data, but if you use the BigQuery Connector, you can use the Line Item Match Table to find the CPM rate. Otherwise, use the Ad Manager API to find the rate of a line item. Find the revenue for a given CPM line item for a given date range by counting the impressions, multiplying by the rate, and dividing by 1,000. Because the match table contains ad unit data for every day, be sure to limit the match table data to one day.
Code
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'
Results
Row | LineItemID |
Impressions |
Rate |
CostType |
Revenue |
1 | 123456789 | 21324 | 3.5 | CPM | 74.634 |
Revenue for a CPD line item
As with CPM line items, you can use the Line Item Match Table or the Ad Manager API to find the CPD rate of a line item. Because the match table contains ad unit data for every day, be sure to limit the match table data to one day. To find the revenue for a given CPD line item, count the number of days in which impressions served and multiply by the rate. You may want to include the number of impressions served to find the average eCPM.
Code
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
Results
Row | LineItemID |
Days |
CostType |
Rate |
Revenue |
Impressions |
Average_eCPM |
1 | 123456789 | 5 | CPD | 4000.0 | 20000.0 | 7000000 | 2.86 |
Revenue for a CPC line item
As with CPM line items, you can use the Line Item Match Table or the Ad Manager API to find the CPC rate of a line item. Because the match table contains ad unit data for every day, be sure to limit the match table data to one day. To find the revenue for a given CPC line item for a given date range, count the clicks and multiply by the rate. You may want to include the number of impressions served to find the average eCPM.
Code
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)
Results
Row | LineItemID |
CostType |
Impressions |
Clicks |
CTR |
Rate |
Revenue |
Average_eCPM |
1 | 123456789 | CPC | 140000 | 23 | 0.02 | 15.5 | 356.5 | 2.55 |
Revenue for a vCPM line item
As with CPM line items, you can use the Line Item Match Table or the Ad Manager API to find the vCPM rate of a line item. Because the match table contains ad unit data for every day, be sure to limit the match table data to one day. To find the revenue for a given vCPM line item, count the viewable impressions from NetworkActiveViews
and multiply by the rate. You may want to include the number of impressions served to find the average eCPM.
Code
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'
Results
Row | LineItemID |
CostType |
Impressions |
ViewableImpressions |
Rate |
Revenue |
Average_eCPM |
1 | 123456789 | CPMAV | 500000 | 150000 | 10 | 1500.0 | 3.0 |
Revenue for an advertiser
To find the revenue for a given advertiser for a given date range, count the impressions for each line item and multiply by the rate. Use the Line Item Match Table to find the rate and the Company Match Table to find the advertiser name.
Code
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'
Results
Row | AdvertiserID |
CompanyName |
LineItemID |
Impressions |
Rate |
CostType |
Revenue |
1 | 111222333 | ABC | 111111111 | 20212 | 5.0 | CPM | 101.06 |
2 | 111222333 | ABC | 222222222 | 58321 | 3.0 | CPM | 174.963 |
3 | 111222333 | ABC | 333333333 | 82772 | 8.5 | CPM | 703.562 |
4 | 111222333 | ABC | 444444444 | 19003 | 3.25 | CPM | 61.7597 |
Code serves
For networks with fallback enabled, Data Transfer counts a code serve for every line item selected in the fallback chain while Ad Manager Reporting counts a code serve for only the first line item selected in the fallback chain. Data Transfer also counts a code serve for companion ads, while Ad Manager Reporting does not. If you are looking for your Data Transfer report to match your Ad Manager report as closely as possible, only count code serves where VideoFallbackPosition = 0
and where IsCompanion
is false
. Mediation code serves in Data Transfer may not match Mediation code serves in Ad Manager Reporting. Depending on your implementation, there may be other differences between code serve counts in Data Transfer and Ad Manager Reporting.
Code serves, impressions, and render rate by line item for a single advertiser
Find how often code serves turn into impressions for each line item of a direct advertiser. Because we are looking at a direct advertiser, these code serves will only be in NetworkCodeServes
and the impressions will only be in NetworkImpressions
.
Code
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
Results
Row | 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 |
Code serves, impressions, and render rate by device category and creative size delivered
Include the Device Category and the Creative Size Delivered to see how render rates vary for one order of one advertiser.
Code
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
Results
Row | LineItemID |
DeviceCategory |
CreativeSizeDelivered |
CodeServes |
Impressions |
RenderRate |
1 | 6666666666 | Connected TV | Video/Overlay | 100 | 40 | 40.0 |
2 | 6666666666 | Desktop | Video/Overlay | 20000 | 9000 | 45.0 |
3 | 6666666666 | Smartphone | Video/Overlay | 32000 | 25000 | 78.13 |
4 | 6666666666 | Tablet | Video/Overlay | 1000 | 800 | 80.0 |
5 | 7777777777 | Connected TV | 300x250 | 200 | 190 | 95.0 |
6 | 7777777777 | Desktop | 300x250 | 185000 | 184000 | 99.46 |
7 | 7777777777 | Smartphone | 300x250 | 225000 | 220000 | 97.77 |
8 | 7777777777 | Tablet | 300x250 | 10000 | 9800 | 98.0 |
9 | 7777777777 | Connected TV | 300x50 | 50 | 50 | 100.0 |
10 | 7777777777 | Desktop | 300x50 | 1000 | 900 | 90.0 |
11 | 7777777777 | Smartphone | 300x50 | 90000 | 80000 | 88.89 |
12 | 7777777777 | Tablet | 300x50 | 800 | 750 | 93.75 |
Viewability
Viewability data can be found in both the Impression and ActiveView files using the ActiveViewEligibleCount
, ActiveViewMeasurableCount
, and ActiveViewViewableCount
fields. Read more about these fields and how their values are set.
Viewability for a time period can be calculated by totaling the values found in the files or by joining the individual events and then totalling the values. There can be slight differences between these two strategies, detailed below.
Eligible impressions, measurable impressions, and viewable impressions (in total)
Find the number of eligible, measurable, and viewable impressions for a given day. As noted above, the Impression files and the ActiveView files must be used.
Code
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
Results
Row | EligibleImpressions |
MeasurableImpressions |
ViewableImpressions |
1 | 97000000 | 95000000 | 60000000 |
Eligible impressions, measurable impressions, and viewable impressions (by joined impression)
Find the number of eligible, measurable, and viewable impressions for a given day by joining the individual events in the Impression files and the ActiveView files and then totaling the values. There are rare instances in which we received an active view viewable ping but not receive an impression ping. When joining ActiveView data to Impression data, you may find differences in measurable impressions and fewer viewable impressions relative to the first query, shown above.
Code
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
Results
Row | EligibleImpressions |
MeasurableImpressions |
ViewableImpressions |
1 | 97000000 | 95000000 | 59900000 |
Key values
Key usage
Find how often each of your keys appears in an ad request (appears in CustomTargeting
) and how often each key was used to serve a line item (appears in TargetedCustomCriteria
). Active keys that don’t appear in the results or that are infrequently used might be good candidates for archiving in order to stay under your key limit.
Code
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
Results
Row | 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 |
Bids by bidding partners
Find how often each of your partners bids by extracting the bids from CustomTargeting
. The example below expects the name of each partner to begin “bidder_prefix_
” as in “bidder_prefix_partnername
”, and it expects a bid for that partner to be in the format “bidder_prefix_partnername=1.23
”.
Code
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
Results
Row | 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 |
Bid values and counts for a single bidding partner
For a single bidding partner, find the most common bid values and how often each bid was made. In the example below, select the 10 most common bids from the impressions tables for the partner named “bidder_partner
” (impressions where CustomTargeting
contains the key “bidder_partner
” that is set to a bid price, such as “1.23
”).
Code
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
Results
Row | 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 |
Bid counts and average bids
Find the total number of bids and the average bid from the impressions tables for all bidding partners. The example below expects the name of each partner to begin “bidder_prefix_
” as in “bidder_prefix_partnername
”, and it expects a bid for that partner to be in the format “bidder_prefix_partnername=1.23
”.
Code
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
Results
Row | 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 segment counts
Data Management Platforms often pass the segments to which a user belongs as key-value pairs. Find how often these segments appear in ad requests -- how many impressions were eligible to be targeted for each segment. Extract the segment IDs from CustomTargeting. The example below expects the name of the key to be “seg” and the values to be made up of letters and numbers.
Code
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
Results
Row | 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 |
Video
Video errors by URL, ad unit ID, and position
To troubleshoot video line items with significant errors, you may need to find the page and/or ad slot on the page that is most responsible for the errors. Use NetworkVideoConversions
to find errors by line item (where ActionName
contains “error
”). If you have more than one video player on a page, the players use the same ad unit, and you use a key like “pos
” to distinguish between ad units on a page, extract that pos
value from CustomTargeting
. The example below expects the name of that key to be “pos
” and shows the top five combinations of RefererURL
, AdUnitID
, and Position
responsible for errors to a single video line item.
Code
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
Results
Row | 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 |