Query DV360 Log-Level Data in Ads Data Hub: Setup Guide
Querying Display & Video 360 (DV360) log-level data in Ads Data Hub (ADH) requires linking your DV360 partner or advertiser ID to your ADH customer account, provisioning a Google Cloud Project for BigQuery, and executing privacy-safe SQL queries against Google's underlying event-level tables. Because ADH restricts access to raw user identifiers to protect consumer privacy, programmatic traders must write queries that aggregate data to a minimum threshold before exporting results. This setup allows you to safely join impression, click, and conversion logs with your own CRM data uploaded via BigQuery.
Key Takeaways
- Privacy Thresholds: ADH enforces a strict minimum aggregation threshold of 50 unique users per row to prevent user re-identification.
- Data Latency: DV360 event tables in ADH update roughly every 2 to 6 hours, providing near-real-time access compared to standard offline reporting.
- SQL Dialect: All queries in Ads Data Hub must be written in GoogleSQL (formerly Standard SQL) and execute inside a sandboxed BigQuery environment.
- Cost Management: BigQuery charges are based on the volume of data scanned (typically $5 per TB for on-demand query pricing), making partitioned queries and dry runs essential.
The Architecture of Ads Data Hub for DV360
To analyze DV360 log-level data, you must understand how Google structures its clean room environment. Unlike legacy UserID logs (which Google deprecated in Europe and heavily restricted globally due to GDPR and CCPA), Ads Data Hub does not let you download raw, event-level rows containing individual device IDs or cookie identifiers. Instead, Google hosts the raw event logs inside an infrastructure managed by Google. You write queries inside your ADH UI or via the ADH API, and the platform executes those queries against the raw tables. The output is then exported to your own Google Cloud Platform (GCP) BigQuery dataset, but only after passing Google's automated privacy checks.
This architecture relies on a clear separation of data. Google owns and maintains the adh.dv360_impressions, adh.dv360_clicks, and adh.dv360_conversions tables. You own the destination BigQuery dataset where the aggregated results are written. If you want to perform closed-loop attribution, you upload your first-party CRM data, offline purchase logs, or CDP segments into your GCP BigQuery dataset, and grant the ADH service account read access to those tables. Your queries can then join your first-party data with Google's media tables on common identifiers like hashed email addresses, phone numbers, or PAIR (Publisher Advertiser Identity Reconciliation) IDs.
Step 1: Provisioning and Linking Your Accounts
Before writing a single line of SQL, you must establish the linkages between your DSP, your Google Cloud infrastructure, and Ads Data Hub. This requires coordination between your programmatic trading team, cloud administrators, and Google representatives.
1. Prepare Your Google Cloud Project
You need a dedicated Google Cloud Platform (GCP) project with BigQuery enabled. This project will store your query results and any first-party data you plan to join. Ensure that your GCP billing is active, as queries run in ADH will write data to this project and incur standard BigQuery storage and analysis fees.
2. Link DV360 to Ads Data Hub
To pull your buying data into the clean room, you must link your DV360 Partner or Advertiser account to your ADH account. Follow these steps:
- In the Ads Data Hub UI, navigate to Settings > Link accounts.
- Click Link account and select Display & Video 360.
- Enter your DV360 Partner ID or Advertiser ID.
- In your DV360 console, an administrator must approve the link request under Partner Settings > Linked Accounts.
3. Configure Permissions
Your team members need specific Identity and Access Management (IAM) roles to perform their tasks. At a minimum, assign the following roles:
- Ads Data Hub Developer: Allows users to write, validate, and execute queries within the ADH interface.
- BigQuery Data Viewer: Assigned to the ADH service account on your GCP project so it can read your first-party datasets.
- BigQuery Data Editor: Assigned to the ADH service account on your GCP project so it can write query results to your destination dataset.
Step 2: Understanding the DV360 Event Schema
Once your accounts are linked, ADH exposes several read-only tables containing your programmatic campaign data. The primary tables you will interact with include:
adh.dv360_impressions: Contains record-level details for every impression served, including placement, creative, and inventory metadata.adh.dv360_clicks: Logs every click event, mapped back to the corresponding impression via animpression_id.adh.dv360_conversions: Captures Floodlight conversion events and attributed media touchpoints.
Key fields within these tables include standard campaign dimensions such as line_item_id, insertion_order_id, creative_id, campaign_id, and device_type. The schema also includes geographic fields, browser types, and exchange details. Crucially, the identifier field used for user-level joining is user_id. This is a virtualized, synthetic identifier unique to your ADH instance. You cannot export this ID, but you can use it within your queries to calculate reach, frequency, and path-to-conversion paths.
Step 3: Writing a Compliant SQL Query
Because of ADH's strict privacy framework, queries that attempt to select individual rows or group by highly granular dimensions that isolate fewer than 50 users will fail. The system will throw a privacy violation error and block the output from being written to your GCP dataset.
Below is a production-ready GoogleSQL query designed to analyze impression counts, unique reach, and average frequency by DV360 line item over the past 30 days. This query uses a HAVING clause to filter out any cohorts that do not meet the minimum privacy threshold, preventing execution failures.
SELECT
line_item_id,
device_type,
COUNT(DISTINCT user_id) as unique_reach,
COUNT(*) as total_impressions,
ROUND(COUNT(*) / COUNT(DISTINCT user_id), 2) as average_frequency
FROM
adh.dv360_impressions
WHERE
_PARTITIONTIME >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
AND line_item_id IS NOT NULL
GROUP BY
line_item_id,
device_type
HAVING
COUNT(DISTINCT user_id) >= 50;
When executing this query, ADH processes the raw event-level data in its secure environment, aggregates the counts by line item and device type, checks that each group contains at least 50 unique virtualized user IDs, and writes the clean, aggregated table directly to your designated BigQuery destination dataset.
Advanced Use Cases: CTV, SPO, and First-Party Data Joins
Simple reach and frequency metrics are easily accessible in standard DV360 reporting, but Ads Data Hub shines when solving complex programmatic challenges that require cross-referencing multiple data streams.
Solving CTV Frequency Fragmentation
In Connected TV (CTV) environments, device identifier duplication often leads to inaccurate frequency calculations. Programmatic buyers frequently face issues where the same household is served the same ad multiple times due to fragmented app environments and co-viewing. By querying raw impression logs in ADH, you can analyze frequency patterns across different publisher partners and virtual multichannel video programming distributors (vMVPDs). If you are experiencing delivery anomalies, this data helps pinpoint exactly where the caps are failing. For a deeper look at managing these setups, see our guide on how to Solve CTV Frequency Capping Duplication in DV360.
Supply-Path Optimization (SPO) and Custom Bidding
Evaluating the efficiency of different inventory paths is critical for maximizing working media spend. By joining adh.dv360_impressions with publisher-level metrics and exchange IDs, you can identify which Supply-Side Platforms (SSPs) deliver the lowest ad-serving fees and the highest win rates for identical inventory. This log-level analysis is highly valuable when designing custom bidding algorithms. You can write queries to isolate high-performing inventory paths and feed those insights directly back into your bidding logic. To learn how to execute this, read our detailed technical walk-through on DV360 Custom Bidding for CTV SPO.
Troubleshooting Media Quality and Block Rates
If you use third-party verification partners like Integral Ad Science (IAS) or DoubleVerify, you may notice high block rates that limit your scale. While standard reporting aggregates these blocks, querying log-level data in ADH allows you to correlate block events with specific creative sizes, exchange placements, and device types. This level of granularity is essential for identifying whether a high block rate is driven by a specific publisher misconfiguration or a broader targeting issue. For troubleshooting steps, review our article on Troubleshooting High IAS & DV Block Rates in DV360.
Best Practices for Query Optimization and Cost Control
Querying log-level programmatic data can quickly become expensive if your datasets span millions of daily impressions. To control costs and ensure fast query execution, apply these optimization techniques:
Always Use Partition Filters
Google partitions ADH tables by date. Never run a query without a _PARTITIONTIME or _PARTITIONDATE filter in the WHERE clause. Scanning a full year of log data to analyze a single week of campaign performance is an expensive mistake that can consume your entire GCP query budget.
Avoid Select * Statements
BigQuery is a columnar database. It charges based on the amount of data read from the specific columns included in your query. Only select the columns you absolutely need for your analysis. If you only need to evaluate line_item_id and impression_id, do not include fields like user_agent or geo_id in your SELECT statement.
Use Dry Runs
Before executing a query in the ADH UI, look at the query validator. It estimate the volume of data that will be scanned. Use this estimate to calculate the cost of the query before running it, particularly when working with large multi-advertiser datasets.
Frequently Asked Questions
Why does my query return empty rows or fail due to privacy checks?
This occurs when your query attempts to output groups that contain fewer than 50 unique users. ADH automatically drops these rows or blocks the query entirely to prevent re-identification. To fix this, group by broader dimensions, extend your date range to capture more users, or add a HAVING COUNT(DISTINCT user_id) >= 50 filter to your query.
How long is DV360 data retained inside Ads Data Hub?
By default, Google retains event-level data in Ads Data Hub for 13 months. This allows you to perform year-over-year comparisons and long-term attribution modeling. Once this retention window passes, the raw events are permanently deleted, though any aggregated data you exported to your own GCP project remains intact.
Can I export raw user IDs or device IDs from ADH to my CRM?
No. Ads Data Hub is designed specifically as a one-way privacy sandbox. You can import your own first-party data to join with Google's media logs, but any export must be aggregated and pass privacy checks. You cannot extract raw Google user IDs, cookies, or mobile advertising IDs (MAIDs) from the clean room.