Notification

Only available in Google Ad Manager 360.

Review Data Transfer sample queries

Only available in Google Ad Manager 360.

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."

Expand all  Collapse all

In this article:

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.

Sample code and results

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.

Sample code and results

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.

Sample code and results

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.

Sample code and results

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

Comparing Data Transfer and Ad Manager Reporting

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.

Sample code and results (Data Transfer)

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
Sample code and results (Ad Manager reporting)

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
       

 

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
Summary and comparison

Direct

  • Data Transfer:
    • Product = Ad Server
    • DealType is null
    • 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 is Preferred 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 is Preferred 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 is Programmatic 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 is null
    • 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 is Private 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 is null
    • 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 is Private 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 is null
    • 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.

Sample code and results

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.

Sample code and results

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.

Sample code and results

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.

Sample code and results

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.

Sample code and results

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.

Sample code and results

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.

Sample code and results

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.

Sample code and results

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.

Sample code and results

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.

Sample code and results

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”.

Sample code and results

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”).

Sample code and results

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”.

Sample code and results

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.

Sample code and results

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.

Sample code and results

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

Was this helpful?

How can we improve it?

Need more help?

Try these next steps:

Post to the help community Get answers from community members
Contact us Tell us more and we’ll help you get there
true
Release notes

Read about the latest Ad Manager features and Help Center updates.

See what's new

Search
Clear search
Close search
Main menu
7623297279219258887
true
Search Help Center
true
true
true
true
true
148
false
false