User-provided data matching

  • 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

  1. 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.
  2. 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
  3. 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, or last_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 STRING columns.
    • email, phone, first_name, and last_name must be STRING. With another type, such as INT64, you can't save the connection.
    • user_id can also be INT64 or BYTES. A BYTES value that isn't UTF-8 text becomes NULL.
    • zip_code can also be INT64, but an INT64 can't keep a leading zero (02134 is stored as 2134), so such a zip code can't match. Store zip codes as STRING.
  • Give every row a user ID. All rows with a NULL user 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 to BYTES, the user ID type that Format data for input lists, so your source column can stay STRING. For example, the INT64 value 42 becomes b"42". In the _updm match tables, it's the customer_data_user_id column. CAST(my_data.user_id AS BYTES) fails for an INT64 ID, so to join on one, cast it to STRING first: 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 code 94043.1351 can'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_table query 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 as email 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 for INT64, DOUBLE for FLOAT64, DATE for DATE, and TIMESTAMP for TIMESTAMP. 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 for 42 or DATE for 2026-01-15. If a column comes out NULL in 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, or GEOGRAPHY column, 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, or zip_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, and last_name. Lowercase or uppercase formatting and conversion to a string fit user_id, country, and zip_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.

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.table name.
  • 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

Email

  • 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.com and googlemail.com email 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.

  1. Email, Phone, Address (strongest)
  2. Phone, Address
  3. Email, Address
  4. Email, Phone
  5. Address
  6. Phone
  7. Email (weakest)

Create a match table

  1. Click add Create > Query.
  2. 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`
    );
    
  3. Replace the parameter names with your column names to provide proper aliasing.

  4. Click Save

  5. 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:

  1. 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, and roles/bigquery.dataEditor on 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.
  2. 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).

  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.