この記事では、アド マネージャーの Data Transfer レポート用のクエリを作成する方法を、例を使って紹介します。Data Transfer ファイルについて(受信方法など)
なお、この記事の旧タイトルは「Data Transfer のレシピ」です。
埋められなかったインプレッション数
埋められなかったインプレッション数を NetworkImpressions
から取得
1 日の埋められなかったインプレッション数を確認するには、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 |
URL 別の埋められなかったインプレッション数
アド マネージャー レポートでは、広告ユニットまたはリクエストされたサイズ別に埋められなかったインプレッション数を表示できますが、URL 単位で表示することはできません。埋められなかったインプレッションが発生している上位 10 ページを特定するには、RefererURL
を含めます。
コード
SELECT RefererURL, COUNT(1) AS UnfilledImpressions FROM NetworkImpressions WHERE LineItemID = 0 AND Time >= '2020-01-01' AND Time < '2020-01-02' GROUP BY RefererURL ORDER BY UnfilledImpressions DESC LIMIT 10
結果
行 | RefererURL |
UnfilledImpressions |
1 | http://example.com/ | 4903691 |
2 | http://example.com/url/a | 748271 |
3 | http://example.com/url/b | 383293 |
4 | http://example.com/url/c | 364355 |
5 | http://example.com/url/d | 326495 |
6 | http://example.net/ | 295346 |
7 | http://example.net/url/a | 291043 |
8 | http://example.net/url/b | 276106 |
9 | http://example.net/url/c | 231169 |
10 | http://example.net/url/d | 194988 |
埋められなかったインプレッション数(広告ユニット別)
1 つのページで埋められなかったインプレッション数の最も多い広告ユニットを確認します。BigQuery コネクタを使用する場合は、広告ユニットのマッチテーブルを使用して広告ユニットの名前を確認します。なお、マッチテーブルには毎日の広告ユニットデータが含まれるため、マッチテーブルのデータを 1 日に限定してください。
コード
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
別のインプレッション数
Data Transfer の Product
と DealType
フィールドを使用すると、「デマンド チャネル」、「プログラマティック チャネル」、「最適化タイプ」のディメンションを使用するアド マネージャー レポートと同様のレポートを生成できます。NetworkImpressions
(LineItemID
が 0 以外)と 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 |
カウント |
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 |
レポート パラメータ
同じ日付を使用して、アド マネージャー レポートでレポートを作成します。次のディメンションと指標を選択します。
- ディメンション:
- デマンド チャネル
- プログラマティック チャネル
- 最適化タイプ
- 指標:
- 合計インプレッション数
- 合計インプレッション数
結果
行 | デマンド チャネル | プログラマティック チャネル | 最適化タイプ | 合計インプレッション数 |
1 | Open Bidding | 公開オークション | その他すべてのトラフィック | 9,000,000 |
2 | Open Bidding | 公開オークション | 競合の最適化 | 7,000 |
3 | Open Bidding | 公開オークション | 目標インプレッション単価 | 5,993,000 |
4 | Open Bidding | 優先取引 | その他すべてのトラフィック | 20,000 |
5 | Open Bidding | プライベート オークション | その他すべてのトラフィック | 496,000 |
6 | Open Bidding | プライベート オークション | 競合の最適化 | 4,000 |
7 | 広告サーバー | (該当なし) | その他すべてのトラフィック | 40,000,000 |
8 | 広告サーバー | 優先取引 | その他すべてのトラフィック | 1,000,000 |
9 | 広告サーバー | プログラマティック保証型取引 | その他すべてのトラフィック | 1,200,000 |
10 | Ad Exchange | 公開オークション | その他すべてのトラフィック | 48,000,000 |
11 | Ad Exchange | 公開オークション | 優先交渉権 | 100,000 |
12 | Ad Exchange | 公開オークション | 競合の最適化 | 10,000 |
13 | Ad Exchange | 公開オークション | 目標インプレッション単価 | 11,990,000 |
14 | Ad Exchange | プライベート オークション | その他すべてのトラフィック | 1,995,000 |
15 | Ad Exchange | プライベート オークション | 競合の最適化 | 5,000 |
直接取引
- Data Transfer:
Product
=Ad Server
DealType
がnull
- 3 行目: 40,000,000
- アド マネージャー レポート:
- デマンド チャネル = 「広告サーバー」
- プログラマティック チャネル = 「(該当なし)」
- 最適化タイプ = 「その他すべてのトラフィック」
- 7 行目: 40,000,000
優先取引
- Data Transfer:
Product
=Ad Server
DealType
がPreferred Deal
- 4 行目: 1,000,000
- アド マネージャー レポート:
- デマンド チャネル = 「広告サーバー」
- プログラマティック チャネル = 「優先取引」
- 最適化タイプ = 「その他すべてのトラフィック」
- 8 行目: 1,000,000
- Data Transfer:
Product
=Exchange Bidding
DealType
がPreferred Deal
- 7 行目: 20,000
-
- アド マネージャー レポート:
- デマンド チャネル = 「Open Bidding」
- プログラマティック チャネル = 「優先取引」
- 最適化タイプ = 「その他すべてのトラフィック」
- 4 行目: 20,000
プログラマティック保証型取引
- Data Transfer:
Product
=Ad Server
DealType
がProgrammatic Guaranteed
- 5 行目: 1,200,000
- アド マネージャー レポート:
- デマンド チャネル = 「広告サーバー」
- プログラマティック チャネル = 「プログラマティック保証型取引」
- 最適化タイプ = 「その他すべてのトラフィック」
- 9 行目: 1,200,000
Ad Exchange の公開オークション(優先交渉権を除く)
- Data Transfer:
Product
=Ad Exchange
DealType
がnull
- 1 行目: 60,000,000
- アド マネージャー レポート:
- デマンド チャネル = 「Ad Exchange」
- プログラマティック チャネル = 「公開オークション」
- 最適化タイプ = 「その他すべてのトラフィック」、「目標インプレッション単価」、「競合の最適化」
- 10、12、13 行目の合計: 48,000,000 + 10,000 + 11,990,000 = 60,000,000
Ad Exchange のプライベート オークション
- Data Transfer:
Product
=Ad Exchange
DealType
がPrivate Auction
- 2 行目: 2,000,000
- アド マネージャー レポート:
- デマンド チャネル = 「Ad Exchange」
- プログラマティック チャネル = 「プライベート オークション」
- 最適化タイプ = 「その他すべてのトラフィック」、「競合の最適化」
- 14、15 行目の合計: 1,995,000 + 5,000 = 2,000,000
Open Bidding の公開オークション
- Data Transfer:
Product
=Exchange Bidding
DealType
がnull
- 6 行目: 15,000,000
- アド マネージャー レポート:
- デマンド チャネル = 「Open Bidding」
- プログラマティック チャネル = 「公開オークション」
- 最適化タイプ = 「その他すべてのトラフィック」、「目標インプレッション単価」、「競合の最適化」
- 1、2、3 行目の合計: 9,000,000 + 7,000 + 5,993,000 = 15,000,000
Open Bidding のプライベート オークション
- Data Transfer:
Product
=Exchange Bidding
DealType
がPrivate Auction
- 8 行目: 500,000
- アド マネージャー レポート:
- デマンド チャネル = 「Open Bidding」
- プログラマティック チャネル = 「プライベート オークション」
- 最適化タイプ = 「その他すべてのトラフィック」、「競合の最適化」
- 5、6 行目の合計: 496,000 + 4,000 = 500,000
優先交渉権
- Data Transfer:
Product
=First Look
DealType
がnull
- 9 行目: 100,000
- アド マネージャー レポート:
- デマンド チャネル = 「Ad Exchange」
- プログラマティック チャネル = 「公開オークション」
- 最適化タイプ = 「優先交渉権」
- 11 行目: 100,000
収益
CPM 広告申込情報の収益
NetworkImpressions
ファイルには収益データは含まれませんが、BigQuery コネクタを使用している場合は、広告申込情報のマッチテーブルを使用して CPM レートを確認できます。BigQuery コネクタを使用していない場合は、Ad Manager API を使用して広告申込情報のレートを確認します。特定の CPM 広告申込情報の特定の期間の収益を確認するには、インプレッションをカウントし、その数にレートを掛けて、1,000 で割ります。なお、マッチテーブルには毎日の広告ユニットデータが含まれるため、マッチテーブルのデータを 1 日に限定してください。
コード
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 | CPM | 74.634 |
CPD 広告申込情報の収益
CPM 広告申込情報と同様に、広告申込情報のマッチテーブルまたは Ad Manager API を使用して広告申込情報の CPD レートを確認できます。なお、マッチテーブルには毎日の広告ユニットデータが含まれるため、マッチテーブルのデータを 1 日に限定してください。特定の 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 | CPD | 4000.0 | 20000.0 | 7000000 | 2.86 |
CPC 広告申込情報の収益
CPM 広告申込情報と同様に、広告申込情報のマッチテーブルまたは Ad Manager API を使用して広告申込情報の CPC レートを確認できます。なお、マッチテーブルには毎日の広告ユニットデータが含まれるため、マッチテーブルのデータを 1 日に限定してください。特定の 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 | CPC | 140000 | 23 | 0.02 | 15.5 | 356.5 | 2.55 |
vCPM 広告申込情報の収益
CPM 広告申込情報と同様に、広告申込情報のマッチテーブルまたは Ad Manager API を使用して広告申込情報の vCPM レートを確認できます。なお、マッチテーブルには毎日の広告ユニットデータが含まれるため、マッチテーブルのデータを 1 日に限定してください。特定の 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 | CPMAV | 500000 | 150000 | 10 | 1500.0 | 3.0 |
広告主の収益
特定の広告主の特定の期間の収益を確認するには、各広告申込情報のインプレッションをカウントし、その数にレートを掛けます。レートを確認するには広告申込情報のマッチテーブルを、広告主名を確認するには会社のマッチテーブルを使用してください。
コード
WITH Impression_Data AS ( SELECT AdvertiserID, LineItemID, COUNT(1) AS Impressions FROM NetworkImpressions WHERE AdvertiserID = 111222333 AND Time >= '2020-01-01' AND Time < '2020-01-02' GROUP BY AdvertiserID, LineItemID ) SELECT AdvertiserID, MTC.Name AS CompanyName, LineItemID, Impressions, CostPerUnitInNetworkCurrency AS Rate, CostType, ((Impressions * CostPerUnitInNetworkCurrency) / 1000) AS Revenue FROM Impression_Data JOIN MatchTableLineItem AS MTLI ON LineItemID = MTLI.ID JOIN MatchTableCompany AS MTC ON AdvertiserID = MTC.ID WHERE MTLI._DATA_DATE = '2020-01-01' AND MTC._DATA_DATE = '2020-01-01'
結果
行 | AdvertiserID |
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 |
コード配信回数
ネットワークで代替機能が有効になっている場合、Data Transfer では代替チェーンで選択された広告申込情報ごとにコード配信が 1 回カウントされます。一方、アド マネージャー レポートでは、代替チェーンで選択された最初の広告申込情報に対してのみコード配信が 1 回カウントされます。また、Data Transfer ではコンパニオン広告についてもコード配信がカウントされますが、アド マネージャー レポートではカウントされません。Data Transfer レポートをアド マネージャー レポートとできるだけ一致させるには、VideoFallbackPosition = 0
かつ IsCompanion
が false
の場合にのみコード配信をカウントします。Data Transfer のメディエーション コード配信回数は、アド マネージャー レポートのメディエーション コード配信回数と一致しない場合があります。また、実装方法によっては、Data Transfer とアド マネージャー レポートのコード配信回数に別の差異が生じる可能性があります。
単一の広告主の広告申込情報別のコード配信回数、インプレッション数、レンダリング率
直接取引の広告主について、広告申込情報ごとにコード配信がインプレッションにつながった頻度を確認します。直接取引の広告主であるため、コード配信回数は 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 |
デバイス カテゴリとクリエイティブ サイズ(配信済み)別のコード配信回数、インプレッション数、レンダリング率
1 つの広告主のオーダーごとのレンダリング率の違いを確認するには、デバイス カテゴリとクリエイティブ サイズ(配信済み)を含めます。
コード
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 | 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 | 300×250 | 200 | 190 | 95.0 |
6 | 7777777777 | Desktop | 300×250 | 185000 | 184000 | 99.46 |
7 | 7777777777 | Smartphone | 300×250 | 225000 | 220000 | 97.77 |
8 | 7777777777 | Tablet | 300×250 | 10000 | 9800 | 98.0 |
9 | 7777777777 | Connected TV | 300×50 | 50 | 50 | 100.0 |
10 | 7777777777 | Desktop | 300×50 | 1000 | 900 | 90.0 |
11 | 7777777777 | Smartphone | 300×50 | 90000 | 80000 | 88.89 |
12 | 7777777777 | Tablet | 300×50 | 800 | 750 | 93.75 |
視認性
視認性データは、インプレッション ファイルとアクティブ ビュー ファイルの ActiveViewEligibleCount
、ActiveViewMeasurableCount
、ActiveViewViewableCount
フィールドで確認できます。各フィールドと値の設定方法について
期間の視認性は、ファイルの値の合計や、個々のイベントを結合してから値を合計することで算出できます。次の 2 つの戦略では、若干の差異が生じる可能性があります(下記に詳述)。
測定対象のインプレッション、測定可能なインプレッション、視認範囲のインプレッション(合計)
1 日の測定対象のインプレッション数、測定可能なインプレッション数、視認範囲のインプレッション数を確認します。前述のとおり、インプレッション ファイルとアクティブ ビュー ファイルを使用する必要があります。
コード
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 |
測定対象のインプレッション、測定可能なインプレッション、視認範囲のインプレッション(結合インプレッション別)
1 日の測定対象のインプレッション数、測定可能なインプレッション数、視認範囲のインプレッション数を確認するには、インプレッション ファイルとアクティブ ビュー ファイルの個々のイベントを結合してから、値を合計します。まれに、アクティブ ビュー視認可能の ping を受信していてもインプレッションの ping は受信していないことがあります。アクティブ ビュー データとインプレッション データを結合する際、測定可能なインプレッション数が異なり、最初のクエリ(前述)に比べて視認範囲のインプレッション数が少なくなることがあります。
コード
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 |
Key-Value
キーの使用状況
各キーが広告リクエストに表示される頻度(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_partnername
」のように「bidder_prefix_
」で始まり、入札単価は「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 個選択します(「1.23
」などの入札単価が設定された「bidder_partner
」というキーを含む CustomTargeting
のインプレッション)。
コード
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_partnername
」のように「bidder_prefix_
」で始まり、入札単価は「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 セグメント数
多くの場合、データ管理プラットフォームはユーザーが属するセグメントを Key-Value ペアとして渡します。広告リクエストでこれらのセグメントが表示された頻度、つまり各セグメントのターゲットに設定可能だったインプレッション数を確認できます。これには、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 |
動画
URL、広告ユニット ID、掲載位置別の動画エラー数
重大なエラーのある動画広告申込情報のトラブルシューティングを行うには、エラーの主な原因となっているページおよび / またはページ上の広告スロットを特定する必要があります。広告申込情報別にエラーを確認するには、NetworkVideoConversions
(ActionName
に「error
」が含まれるもの)を使用します。1 つのページに複数の動画プレーヤーがある場合、各プレーヤーは同じ広告ユニットを使用します。したがって、「pos
」などのキーを使ってページ上の広告ユニットを区別し、その pos
の値を CustomTargeting
から抽出します。次の例では、キーの名前が「pos
」で、単一の動画広告申込情報に対するエラーの原因として RefererURL
、AdUnitID
、Position
の組み合わせの上位 5 つを表示することを想定しています。
コード
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 |