Page Summary
-
User-Provided Data Matching (UPDM) allows you to join your first-party data with a user's signed-in activity across Google ad data.
-
UPDM is only available for ad events linked to signed-in users on Google owned & operated data and is not impacted by third-party cookie deprecation.
-
To use UPDM, you must upload first-party data collected about your users and ensure your privacy policy and consent acknowledgements comply with applicable laws and policies.
-
Data uploaded for UPDM must meet specific formatting requirements, including hashing for certain fields like email, phone, and name.
-
Match tables are created to join your first-party data with Google ad data, which can then be queried to gain insights and calculate metrics like match rate.
User-provided data matching (UPDM) joins first-party data that you've collected about a user—such as information from your websites, apps, or physical stores—with that same user's signed-in activity across all Google ad data, including Google owned & operated data. This includes data bought through Google Marketing Platform (GMP) products, for example, YouTube bought using Display & Video 360. Other GMP products which are not Google owned & operated are not supported.
To be eligible for user-provided data matching, the ad event must be linked to a signed-in user in Google ad data.
This document describes the user-provided data matching feature, and provides guidance on setup and use.
Private Cloud Match Overview
Gaining valuable advertising insights often requires stitching together data from multiple sources. Building your own solution to this data pipeline problem requires significant time investment and engineering investment. Private Cloud Match in Ads Data Hub streamlines this process by providing an Ads Data Hub query template for creating a match table in BigQuery that can then be used in your Ads Data Hub queries to match your ads data with your first-party data. Enriching your queries with first-party data can deliver richer customer experiences, and is more resistant to industry-wide ad-tracking changes.
Because user-provided data matching is only available on Google owned and operated inventory for signed-in users, it is not impacted by the upcoming deprecation of third-party cookies. Since it's more resistant to industry changes than third-party data, it can provide richer insights, which can lead to higher customer engagement.
Process summary
- Setup of data ingestion and matching
- You Make sure your first-party data is located in BigQuery and your service account has read access to it. See Set up data ingestion.
- First-party data ingestion and matching
- You format and upload your first-party data to your BigQuery dataset.
- You initiate a data-matching request by creating a Private Cloud Match analysis query and setting a schedule.
- Google joins data between your project and Google-owned data containing Google's user ID and hashed user-provided data to build and update match tables.
- See Ingest first-party data
- Ongoing queries in Ads Data Hub, based on matched data
- You run queries against the match tables in the same way you run regular queries in Ads Data Hub. See Query matched data.
Learn about privacy requirements
Collecting customer data
When using user-provided data matching, you must upload first-party data. This could be information you collected from your websites, apps, physical stores, or any information that a customer shared with you directly.
You must:
- Ensure that your privacy policy discloses that you share customer data with third parties to perform services on your behalf, and that you obtain consent for such sharing where legally required
- Only use Google's approved API or interface to upload customer data
- Comply with all applicable laws and regulations, including any self-regulatory or industry codes that may apply
First-party consent acknowledgement
To ensure you are able to use your first-party data in Ads Data Hub, you must confirm that you have obtained proper consent to share data from EEA end users with Google per the EU user consent policy and Ads Data Hub policy. This requirement applies to each Ads Data Hub account, and must be updated every time you upload new first-party data. Any one user can make this acknowledgement on behalf of the entire account.
Note that the same Google service query rules that apply to analysis queries also apply to UPDM queries. For example, you can't run cross-service queries on users in the EEA when you create a match table.
To learn how to acknowledge consent in Ads Data Hub, see Consent requirements for the European Economic Area.
Data size
To protect end-user privacy, user-provided data matching enforces these requirements regarding the size of your data:
- You must upload least 1,000 records in your user list.
Set up data ingestion
Before you start, make sure:
- Your first-party data must be in BigQuery. If you have a VPC-SC perimeter, this first party data must be located within your VPC-SC.
- Your Ads Data Hub service account must have read access to the first-party data.
- Your first-party data must be formatted and hashed correctly. See next section for more details.
Beyond that, Private Cloud Match has no additional onboarding. If you can run an analysis query, you can run a Private Cloud Match query.
Ingest and match first-party data
Source permissions and connection requirements
Ads Data Hub Connections uses the service account shown under Settings >
Details > Service account (usually
service-<number>@gcp-sa-adsdatahub.iam.gserviceaccount.com). Grant it these
roles:
- BigQuery source:
roles/bigquery.dataViewer(BigQuery Data Viewer) on the source dataset or table. - Cloud Storage source:
roles/storage.objectViewer(Storage Object Viewer) on the source bucket or object. - First-party data import destination:
roles/bigquery.dataEditor(BigQuery Data Editor) on the destination dataset, if the table isn't in your default dataset.
Field and data type requirements
These rules are for connections in the updated Connections experience. They depend on the connection type: User-provided data matching or First-party data.
User-provided data matching (UPDM)
Map user_id and at least one of these: email, phone, or a full address
(first_name, last_name, country, and zip_code). If you map any address
column, map all four. Otherwise, you can't save the connection. You can't
change the Type of these columns: it's HASH for email, phone,
first_name, and last_name, and STRING for user_id, country, and
zip_code.
The source column that you map to each destination column must contain the following. To format the values, see Format data for input.
| Destination column | Source column value | Example |
|---|---|---|
user_id |
Your ID for the user, in plain text | customer-1234 |
email, phone, first_name, last_name |
The formatted value, hashed with SHA-256 and encoded in Base64 (44 characters). Hex encoding (64 characters) also works. | h5JGBrQTGorO7q6IaFMfu5cSqqB6XTp1aybOD11spnQ=, the hash of test@gmail.com |
country |
A 2-letter country code (ISO 3166-1 alpha-2), in plain text | US |
zip_code |
A zip or postal code of up to 20 characters (letters A-Z, digits, spaces, and hyphens), in plain text | 94043 |
- Hash the values in your source. Connections doesn't hash
email,phone,first_name, orlast_name, even though their Type is HASH. One exception: if your connection was moved to the updated Connections experience, and a field used compute hash before the move (compute hash hashed the column for you), Connections still hashes that field. Keep that field's source values unhashed, but formatted; see Moved connections. - In a BigQuery source table, use
STRINGcolumns.email,phone,first_name, andlast_namemust beSTRING. With another type, such asINT64, you can't save the connection.user_idcan also beINT64orBYTES. ABYTESvalue that isn't UTF-8 text becomesNULL.zip_codecan also beINT64, but anINT64can't keep a leading zero (02134is stored as2134), so such a zip code can't match. Store zip codes asSTRING.
- Give every row a user ID. All rows with a
NULLuser ID count as one user, so at most one of them can match. The same goes for all rows with an empty user ID. - Your user ID becomes
BYTES. Connections converts it toBYTES, the user ID type that Format data for input lists, so your source column can staySTRING. For example, theINT64value42becomesb"42". In the_updmmatch tables, it's thecustomer_data_user_idcolumn.CAST(my_data.user_id AS BYTES)fails for anINT64ID, so to join on one, cast it toSTRINGfirst:customer_data_user_id = CAST(CAST(my_data.user_id AS STRING) AS BYTES). - An empty or wrongly formatted email, phone, or address value can't
match, but it doesn't fail the run. The row can still match on its
other values. For example, an email in plain text, the country
USA, or the zip code94043.1351can't match. To match on its address, a row needs valid values in all four address columns. - In a Private Cloud Match query, a value in the wrong format can fail
the whole query. This applies if you create the match table with the
CREATE OR REPLACE TABLE adh.updm_match_tablequery in Ads Data Hub instead of a connection. For example, an email hash that isn't 44 characters long, such as a hex-encoded one, fails the query with an error such asemail must be encoded using TO_BASE64(SHA256(email)). - Check your match rate. For example, use the
Calculate match rate query. Connections doesn't
report values that can't match; you get fewer matched users. If 1,000
users or fewer match, the run fails with
Minimum match count check failed. - Use only one UPDM connection per account. UPDM connections have no destination table. Every UPDM connection in an account, and every Private Cloud Match query, writes the same match table, and each successful run replaces it. To match data from several sources, combine it into one table or file first.
First-party data
For each column, choose a Type (Data Type on the connection details page). Connections converts each source value to that type, and the destination column gets the BigQuery type listed here:
| Type | Destination column | Values it converts | Values that become NULL |
|---|---|---|---|
| STRING (default) | STRING |
Any value, as text. From BigQuery, 123 becomes "123" and TRUE becomes "true". From Cloud Storage, the text is kept as is. |
A BYTES value that isn't UTF-8 text |
| HASH | STRING |
Same as STRING. Connections doesn't hash or check the value. | Same as STRING |
| INTEGER | INT64 |
Whole numbers, and number text such as "42". From BigQuery, a decimal is truncated: 1.9 becomes 1. |
Other values, such as "abc", TRUE, or a date. From Cloud Storage, also a decimal, such as 1.9. |
| DOUBLE | FLOAT64 |
Numbers, and number text such as "1.5" |
Other values, such as TRUE or a date |
| DATE | DATE |
DATE values, and year-month-day text such as 2026-01-15 or 2026-1-5 |
Other values, such as 01/15/2026, 20260115, or a TIMESTAMP or DATETIME value |
| TIMESTAMP | TIMESTAMP |
TIMESTAMP, DATETIME, and DATE values, and text such as 2026-01-15 10:00:00 or 2026-01-15T10:00:00-08:00. A value without a time zone is read as UTC; a date becomes midnight UTC. |
Other values, such as a Unix epoch time (1768471200) |
- A value that can't be converted doesn't fail the run. It's imported
as
NULL, and Connections doesn't report it, so check the destination table after the first run. - Choose the type that fits your data. For a BigQuery source, choose
the type that matches the source column: STRING for
STRING, INTEGER forINT64, DOUBLE forFLOAT64, DATE forDATE, and TIMESTAMP forTIMESTAMP. These values are imported unchanged. For a Cloud Storage source, every CSV value is text: choose the type of the values in the column, such as INTEGER for42or DATE for2026-01-15. If a column comes outNULLin every row, choose STRING, or convert the values in your source data first. - Some BigQuery types can't be mapped. If you map an
ARRAY,STRUCT,JSON, orGEOGRAPHYcolumn, you can't save the connection. - Each run replaces the whole destination table. It doesn't append. If the source has no rows, the destination becomes empty. Don't use the source table, or a table whose data you need to keep, as the destination.
Moved connections
Transformations aren't available yet in the updated Connections experience: you can't add one to a field, and the field mappings don't show them. A connection that was moved to the updated Connections experience still keeps the transformations of its fields, such as compute hash (SHA-256 hashing).
- In a first-party data connection, a field with a transformation gets the value that the transformation produces, instead of the Type conversion, and you can't change its Type. In a UPDM connection, the UPDM rules still apply to that value.
- A UPDM field that used compute hash is still hashed by Connections. Keep its source values unhashed, but formatted as described in Format data for input: an already hashed value is hashed again and can't match. To hash the values yourself instead, remove the field mapping and add it again.
Only these transformations are moved:
- Compute hash with SHA-256 (Base64 or hex), except on a UPDM
user_id,country, orzip_code, which must stay plain text. - Lowercase or uppercase formatting.
- Conversion to a string.
- Timestamp parsing without a default time zone, on up to 10 fields of a connection.
- Two of the first three in a row, such as lowercase formatting and then compute hash.
A transformation is moved only if what it produces fits its field. For two in a row, what the second one produces counts: lowercase formatting and then compute hash fits
email, but compute hash and then lowercase formatting doesn't.- In a UPDM connection, compute hash fits
email,phone,first_name, andlast_name. Lowercase or uppercase formatting and conversion to a string fituser_id,country, andzip_code. Timestamp parsing fits no field. - In a first-party data connection, compute hash fits a STRING or HASH column. Lowercase or uppercase formatting and conversion to a string fit a STRING column. Timestamp parsing fits a TIMESTAMP column.
A connection with any other transformation, such as merging or splitting fields, or with a transformation that doesn't fit its field, isn't moved.
- Compute hash with SHA-256 (Base64 or hex), except on a UPDM
Cloud Storage CSV column requirements
- Include a header row. The first line is skipped; its column names aren't used.
- The file must contain exactly the mapped source columns, no more and no fewer. Extra columns fail the run.
- First-party data: Columns must be in the same order as the field mappings.
- User-provided data matching: Columns must be in this order, keeping
only the fields you map:
user_id,email,phone,first_name,last_name,country,zip_code. The order of the mapping rows doesn't matter. - A different column order isn't detected: values load into the wrong fields (or match rates drop) without an error.
Supported data sources and connection limits
- Data sources: Only BigQuery and Cloud Storage sources are supported.
- BigQuery table names: Source and destination BigQuery tables need the
full
project.dataset.tablename. - Cloud Storage file paths: Provide a single file URI (such as
gs://bucket/path/file.csv). Wildcard paths with*or?aren't supported. - Connection names: Each connection in an account must have a unique name.
Format data for input
Your data must adhere to these formatting requirements to be correctly matched:
- Where indicated in the following input field descriptions, you must upload using SHA256 hashing.
- UPDM supports Base64 encoding. See TO_BASE64.
- Input fields must be formatted as strings. For example, if you're using the
BigQuery SHA256 hash function,
use the following transformation:
TO_BASE64(SHA256(user_data)).
User ID
- Data type:
BYTES - Hashing: None
- Remove leading and trailing whitespaces
- Lowercase all characters
- Include a domain name for all email addresses, such as gmail.com or hotmail.co.jp
- Remove accents—for example, change è, é, ê, or ë to e
- Remove all periods (.) that precede the domain name in
gmail.comandgooglemail.comemail addresses - Hashing: Base64 encoded SHA256
Valid: TO_BASE64(SHA256("jeffersonloveshiking@gmail.com"))
Invalid: TO_BASE64(SHA256("JéffersonLôvesHiking@gmail.com"))
Phone
- Strip whitespace
- Format in E.164 format—for instance, US example: +14155552671, UK example: +442071838750
- Remove all special characters except the "+" before the country code
- Hashing: Base64 encoded SHA256
Valid: TO_BASE64(SHA256("+18005550101"))
Invalid: TO_BASE64(SHA256("(800) 555-0101"))
First name
- Strip whitespace
- Lowercase all characters
- Remove all prefixes-for example Mrs., Mr., Ms., Dr.
- Don't remove accents—for example, è, é, ê, or ë
- Hashing: Base64 encoded SHA256
Valid: TO_BASE64(SHA256("daní"))
Invalid: TO_BASE64(SHA256("Daní"))
Last name
- Strip whitespace
- Lowercase all characters
- Remove all suffixes-for example Jr., Sr., 2nd, 3rd, II, III, PHD, MD
- Don't remove accents—for example, è, é, ê, or ë
- Hashing: Base64 encoded SHA256
Valid: TO_BASE64(SHA256("délacruz"))
Invalid: TO_BASE64(SHA256("dé la Cruz, Jr."))
Country
- Include the country code even if all of your customer data is from the same country
- Don't hash country data
- Use ISO 3166-1 alpha-2 country codes
- Hashing: None
Valid: US
Invalid: United States of America or USA
Zip code
- Don't hash zip code data
- Both US and international zip and postal codes are allowed
- Use only letters A-Z, digits, spaces, and hyphens, up to 20 characters
- For US:
- 5 digit codes are allowed—for example, 94043
- 5 digits followed by 4 digit extension are also allowed—for example, 94043-1351 or 940431351
- For all other countries:
- No need to convert to lowercase or to remove spaces and hyphens
- Leave out postal code extensions
- Hashing: None
Hash validation and data encoding
You can use the following hash validation scripts to ensure that your data is correctly formatted.
JavaScript
/**
* @fileoverview Provides the hashing algorithm, as well as some valid hashes of
* sample data for testing.
*/
async function hash(token) {
// Removes leading or trailing spaces and converts all characters to lowercase.
const formattedToken = token.trim().toLowerCase();
// Hashes the formatted string using the SHA-256 hashing algorithm.
const hashBuffer = await crypto.subtle.digest(
'SHA-256', (new TextEncoder()).encode(formattedToken));
// Converts the hash buffer to a base64-encoded string and returns it.
const base64Str = btoa(String.fromCharCode(...new Uint8Array(hashBuffer)));
return base64Str;
}
function main() {
// Expected hash for test@gmail.com:
// h5JGBrQTGorO7q6IaFMfu5cSqqB6XTp1aybOD11spnQ=
hash('test@gmail.com').then(result => console.log(result));
// Expected hash for +18005551212:
// YdkRG+0+bZz8G8O1yzWkAmh8TxVGvuBhor1ET73WTEQ=
hash('+18005551212').then(result => console.log(result));
// Expected hash for John: ltljLzY1ZMwwMlIUCc8iqFLyAy7sCZ7VlnwNAAzsYHo=
hash('John').then(result => console.log(result));
// Expected hash for Doe: eZ75KhGvkY4/t0HfQpNPO1aO0tk6wd908bjUGieTKm8=
hash('Doe').then(result => console.log(result));
}
main()
Python
"""Provides the hashing algorithm, as well as some valid hashes of sample data for testing.
Supports: Python 2, Python 3
Sample hashes:
- Email 'test@gmail.com': h5JGBrQTGorO7q6IaFMfu5cSqqB6XTp1aybOD11spnQ=
- Phone '+18005551212': YdkRG+0+bZz8G8O1yzWkAmh8TxVGvuBhor1ET73WTEQ=
- First name 'John': ltljLzY1ZMwwMlIUCc8iqFLyAy7sCZ7VlnwNAAzsYHo=
- Last name 'Doe': eZ75KhGvkY4/t0HfQpNPO1aO0tk6wd908bjUGieTKm8=
"""
import base64
import hashlib
def hash(token):
# Generates a base64-encoded SHA-256 hash of a normalized input string.
return base64.b64encode(
hashlib.sha256(
token.strip().lower().encode('utf-8')).digest()).decode('utf-8')
def print_hash(token, expected=None):
# Computes and displays the hash of a token, with optional validation.
hashed = hash(token)
if expected is not None and hashed != expected:
print(
'ERROR: Incorrect hash for token "{}". Expected "{}", got "{}"'.format(
token, expected, hashed))
return
print('Hash: "{}"\t(Token: {})'.format(hashed, token))
def main():
# Tests the hash function with sample tokens and expected results.
print_hash(
'test@gmail.com', expected='h5JGBrQTGorO7q6IaFMfu5cSqqB6XTp1aybOD11spnQ=')
print_hash(
'+18005551212', expected='YdkRG+0+bZz8G8O1yzWkAmh8TxVGvuBhor1ET73WTEQ=')
print_hash('John', expected='ltljLzY1ZMwwMlIUCc8iqFLyAy7sCZ7VlnwNAAzsYHo=')
print_hash('Doe', expected='eZ75KhGvkY4/t0HfQpNPO1aO0tk6wd908bjUGieTKm8=')
if __name__ == '__main__':
main()
Go
/*
Provides the hashing algorithm, as well as some valid hashes of sample data for testing.
Sample hashes:
- Email 'test@gmail.com': h5JGBrQTGorO7q6IaFMfu5cSqqB6XTp1aybOD11spnQ=
- Phone '+18005551212': YdkRG+0+bZz8G8O1yzWkAmh8TxVGvuBhor1ET73WTEQ=
- First name 'John': ltljLzY1ZMwwMlIUCc8iqFLyAy7sCZ7VlnwNAAzsYHo=
- Last name 'Doe': eZ75KhGvkY4/t0HfQpNPO1aO0tk6wd908bjUGieTKm8=
*/
package main
import (
"crypto/sha256"
"encoding/base64"
"fmt"
"strings"
)
// Hash hashes an email, phone, first name, or last name into the correct format.
func Hash(token string) string {
formatted := strings.TrimSpace(strings.ToLower(token))
hashed := sha256.Sum256([]byte(formatted))
encoded := base64.StdEncoding.EncodeToString(hashed[:])
return encoded
}
// PrintHash prints the hash for a token.
func PrintHash(token string) {
fmt.Printf("Hash: \"%s\"\t(Token: %s)\n", Hash(token), token)
}
func main() {
PrintHash("test@gmail.com")
PrintHash("+18005551212")
PrintHash("John")
PrintHash("Doe")
}
Java
package updm.hashing;
import static java.nio.charset.StandardCharsets.UTF_8;
import java.security.MessageDigest;
import java.security.NoSuchAlgorithmException;
import java.util.Base64;
/**
* Example of the hashing algorithm.
*
* <p>Sample hashes:
*
* <ul>
* <li>Email 'test@gmail.com': h5JGBrQTGorO7q6IaFMfu5cSqqB6XTp1aybOD11spnQ=
* <li>Phone '+18005551212': YdkRG+0+bZz8G8O1yzWkAmh8TxVGvuBhor1ET73WTEQ=
* <li>First name 'John': ltljLzY1ZMwwMlIUCc8iqFLyAy7sCZ7VlnwNAAzsYHo=
* <li>Last name 'Doe': eZ75KhGvkY4/t0HfQpNPO1aO0tk6wd908bjUGieTKm8=
* </ul>
*/
public final class HashExample {
private HashExample() {}
public static String hash(String token) {
// Normalizes and hashes the input token using SHA-256 and Base64 encoding.
String formattedToken = token.toLowerCase().strip();
byte[] hash;
try {
hash = MessageDigest.getInstance("SHA-256").digest(formattedToken.getBytes(UTF_8));
} catch (NoSuchAlgorithmException e) {
throw new IllegalStateException("SHA-256 not supported", e);
}
return Base64.getEncoder().encodeToString(hash);
}
public static void printHash(String token) {
// Calculates and prints the hash for the given token.
System.out.printf("Hash: \"%s\"\t(Token: %s)\n", hash(token), token);
}
public static void main(String[] args) {
// Executes hash calculations and prints results for sample tokens.
printHash("test@gmail.com");
printHash("+18005551212");
printHash("John");
printHash("Doe");
}
}
SQL
/*
Provides the hashing algorithm, as well as some valid hashes of sample data for testing.
The following code uses Google Standard SQL and can be run on BigQuery to generate match tables from unhashed data.
Sample hashes:
- Email 'test@gmail.com': h5JGBrQTGorO7q6IaFMfu5cSqqB6XTp1aybOD11spnQ=
- Phone '+18005551212': YdkRG+0+bZz8G8O1yzWkAmh8TxVGvuBhor1ET73WTEQ=
- First name 'John': ltljLzY1ZMwwMlIUCc8iqFLyAy7sCZ7VlnwNAAzsYHo=
- Last name 'Doe': eZ75KhGvkY4/t0HfQpNPO1aO0tk6wd908bjUGieTKm8=
The unhashed input table schema is assumed to be:
- Column name: UserID, Type: String
- Column name: Email, Type: String
- Column name: Phone, Type: String
- Column name: FirstName, Type: String
- Column name: LastName, Type: String
- Column name: PostalCode, Type: String
- Column name: CountryCode, Type: String
*/
-- Creates a new table with Base64-encoded SHA-256 hashes of specified columns.
CREATE TABLE `your_project_name.your_dataset_name.output_hashed_table_name`
AS
SELECT
UserID,
TO_BASE64(SHA256(LOWER(Email))) AS Email,
TO_BASE64(SHA256(Phone)) AS Phone,
TO_BASE64(SHA256(LOWER(FirstName))) AS FirstName,
TO_BASE64(SHA256(LOWER(LastName))) AS LastName,
PostalCode,
CountryCode,
FROM
`your_project_name.your_dataset_name.input_unhashed_table_name`;
Join keys
Some combinations of user-provided data are stronger than others. Following is a list of different user-provided data combinations, ranked by relative strength. If you use an address, you must include: First name, Last name, Country, and Zip code.
- Email, Phone, Address (strongest)
- Phone, Address
- Email, Address
- Email, Phone
- Address
- Phone
- Email (weakest)
Create a match table
- Click add Create > Query.
Copy the Private cloud match table generation query template and paste it into the query editor. Optional: You can use the Private cloud match table generation (with hashing) template if your data is not already hashed.
/* Private cloud match table generation Create a new match table using your first party data with this template. Parameters: Manually remove all the parameters tagged with @ prefix and replace them with column names from your first party table: * @user_id * @email * @phone * @first_name * @last_name * @country_code * @postal_code And your BigQuery table information: * @my_project: Your BigQuery project where the first party table is. * @my_dataset: Your dataset where the first party table is. * @my_first_party_table: Your first party table. */ CREATE OR REPLACE TABLE adh.updm_match_table AS ( SELECT CAST(@user_id AS BYTES) AS user_id, @email AS email, @phone AS phone, @first_name AS first_name, @last_name AS last_name, @country_code AS country, @postal_code AS zip_code FROM `@my_project.@my_dataset.@my_first_party_table` );/* Private cloud match table generation (with hashing) Create a new match table using your first party data with this template. Parameters: Manually remove all the parameters tagged with @ prefix and replace them with column names from your first party table: * @user_id * @email * @phone * @first_name * @last_name * @country_code * @postal_code And your BigQuery table information: * @my_project : Your BigQuery project where the first party table is. * @my_dataset : Your dataset where the first party table is. * @my_first_party_table : Your first party table. */ CREATE OR REPLACE TABLE adh.updm_match_table AS ( SELECT CAST(@user_id AS BYTES) AS user_id, TO_BASE64(SHA256(LOWER(@email))) AS email, TO_BASE64(SHA256(@phone)) AS phone, TO_BASE64(SHA256(LOWER(@first_name))) AS first_name, TO_BASE64(SHA256(LOWER(@last_name))) AS last_name, @country_code AS country, @postal_code AS zip_code FROM `@my_project.@my_dataset.@my_first_party_table` );Replace the parameter names with your column names to provide proper aliasing.
Click Save
Click Run. You need to manually run this query to refresh the match table. Each run will overwrite the current match table.
Migrate existing connections
Existing connections in the Connections tab are moving to an updated
Connections experience built directly into Ads Data Hub. Updated connections
read sources and write destinations using the service account shown under
Settings > Details > Service account (usually
service-<number>@gcp-sa-adsdatahub.iam.gserviceaccount.com) instead of the
legacy Data Fusion, Dataproc, and UPDM service accounts.
Complete these action items before your connections are moved:
- Grant service account permissions: Grant the service account shown under
Settings > Details > Service account (usually
service-<number>@gcp-sa-adsdatahub.iam.gserviceaccount.com) the required source and destination roles (roles/bigquery.dataViewer,roles/storage.objectViewer, androles/bigquery.dataEditoron non-default destination datasets). Connections with unreadable, deleted, or changed sources may still be moved (especially Cloud Storage sources, which aren't validated at move time), but runs fail until you grant access or fix the source. Convert unsupported connections: Connections with the following configurations won't be migrated. Convert them to supported BigQuery or Cloud Storage connections:
- Other data sources (Amazon S3, Snowflake, MySQL, PostgreSQL, Amazon Redshift, sFTP, or Salesforce): Stage the data in BigQuery or Cloud Storage first, then create a BigQuery or Cloud Storage connection.
- Unsupported field mappings or data types: Update or recreate the connection to meet the field and data type requirements.
- Unsupported transformations: Only the transformations listed in Moved connections are moved. Remove any other transformation, such as merging or splitting fields, and do that work in your source data instead.
- Cloud Storage wildcard paths (
*or?): Replace the wildcard URI with a single file URI or load the files into BigQuery first. - Names already used in Ads Data Hub: Rename the connection if a query, or a connection created in the updated Connections experience, already has the same name in your account.
Existing connections that share a name with each other are still migrated: the oldest one keeps the name, and the others get a suffix, such as
(2)or(3).Delete unused connections: Delete obsolete connections you no longer need.
Query matched data
Query the match tables
When your match tables contain enough data to satisfy privacy checks, you're ready to run queries against the tables.
The original table for first-party data (1PD) is represented by my_data.
This includes both Personally Identifiable Information (PII) and non-PII data.
Using the original table can improve your reports with more insights, as it
represents all the 1PD data in scope, when compared to a match table.
Each table in the Ads Data Hub schema containing a user_id field is
accompanied by a match table. For example, for the
adh.google_ads_impressions table, Ads Data Hub also generates a match table
called adh.google_ads_impressions_updm containing your user IDs.
Separate match tables are created for policy-isolated network tables. For
example, for the adh.google_ads_impressions_policy_isolated_network table, Ads
Data Hub also generates a match table called
adh.google_ads_impressions_policy_isolated_network_updm containing
your user IDs.
These tables contain a subset of the users available in the original tables,
where there is a match on the user_id. For example, if the original table
contains data for User A and User B, but only User A is matched, then User B
won't be in the match table.
The match tables contain an additional column called customer_data_user_id, which
stores the user identifier as BYTES.
It's important to consider the field's type when writing your queries. SQL
comparison operators expect that the literals you're comparing are of the same
type. Depending on how the user_id is stored in your table of first-party
data, you may need to encode the values in the table before matching the data.
You need to cast your join key into BYTES for successful matches:
JOIN ON
adh.google_ads_impressions_updm.customer_data_user_id = CAST(my_data.user_id AS BYTES)
Additionally, string comparisons in SQL are sensitive to capitalization, so you may need to encode strings on both sides of your comparison to ensure that they can be accurately compared.
Sample queries
Count matched users
This query counts the number of matched users in your Google Ads impressions table.
/* Count matched users in Google Ads impressions table */
SELECT COUNT(DISTINCT user_id)
FROM adh.google_ads_impressions_updm
Calculate match rate
Not all users are eligible for matching. For example, signed-out users,
children, and unconsented users are not matched through UPDM. You can use the
is_updm_eligible field to calculate more accurate UPDM match rates. Note that
the is_updm_eligible field was available beginning on October 1, 2024. You
cannot use this field to calculate match rates before that date.
/* Calculate the UPDM match rate */
CREATE TEMP TABLE total_events OPTIONS(privacy_checked_export=TRUE) AS
SELECT
customer_id,
COUNT(*) AS n
FROM adh.google_ads_impressions
WHERE is_updm_eligible
GROUP BY 1;
CREATE TEMP TABLE matched_events OPTIONS(privacy_checked_export=TRUE) AS
SELECT
customer_id,
COUNT(*) AS n
FROM adh.google_ads_impressions_updm
GROUP BY 1;
SELECT
customer_id,
SAFE_DIVIDE(matched_events.n, total_events.n) AS match_rate
FROM total_events
LEFT JOIN matched_events
USING (customer_id)
Join first-party and Google Ads data
This query shows how to join first-party data with Google Ads data:
/* Join first-party data with Google Ads data. The customer_data_user_id field
contains your ID as BYTES. You need to cast your join key into BYTES for
successful matches. */
SELECT
inventory_type,
COUNT(*) AS impressions
FROM
adh.yt_reserve_impressions_updm AS google_data_imp
LEFT JOIN
`my_data`
ON
google_data_imp.customer_data_user_id = CAST(my_data.user_id AS BYTES)
GROUP BY
inventory_type
UPDM FAQs
For a list of FAQs related to UPDM, see UPDM FAQs.