Tutorial: GA4 + BigQuery Identity Resolution

Unify customer identities across Google Analytics 4, HubSpot CRM, Stripe billing, and Zendesk support data - all stored in BigQuery. Kanoniv connects directly to BigQuery, matches records using your spec, and writes the resolved entity map back.

Time: 20 minutes Prerequisites: GCP project with BigQuery, Python 3.9+, pip install kanoniv[bigquery]

What You Will Build

1. Load sample data into BigQuery (GA4 events, HubSpot, Stripe, Zendesk)
2. Connect Kanoniv to BigQuery via Source.from_warehouse()
3. Write a matching spec covering all four sources
4. Reconcile records into unified customer profiles
5. Inspect cross-source golden records
6. Write resolved entities back to BigQuery
7. Query the unified customer view with SQL

---

The Identity Challenge

Real-world customer data is fragmented:

Source	Identifier	Challenge
GA4	client_id (anonymous cookie)	Links to user_id only when authenticated
HubSpot	email + phone	Canonical anchor, but names vary
Stripe	email (work address)	Different email domain than CRM
Zendesk	email + phone	Phone format varies (555-100-0001 vs 5551000001)

The same person might appear as:

• GA4: client_id=a1b2c3 with [email protected]
• HubSpot: Alice Smith, [email protected], 555-100-0001
• Stripe: A. Smith, [email protected]
• Zendesk: Alice S., [email protected], (555) 100-0001

Kanoniv resolves all four into a single customer entity.

---

Step 1: Set Up BigQuery Dataset

Clone the tutorial repo and run the setup script:

cd kanoniv_tutorials/ga_bigquery
pip install google-cloud-bigquery

Set your GCP project (or let ADC handle it):

export GOOGLE_CLOUD_PROJECT=my-project
gcloud auth application-default login

Generate sample data and load it into BigQuery:

python setup_bigquery.py --dataset=kanoniv_demo

This creates 4 tables in the kanoniv_demo dataset:

Table	Records	Description
ga4_events	500	GA4 web analytics (60 authenticated, rest anonymous)
hubspot_contacts	150	CRM contacts (all 80 identity pool + 70 CRM-only)
stripe_customers	120	Billing records (55 of 80 with work emails + 65 Stripe-only)
zendesk_users	100	Support tickets (45 of 80 with phone variants + 55 Zendesk-only)

Total: ~870 records with 80 people overlapping across sources.

---

Step 2: Connect Kanoniv to BigQuery

Install the SDK with BigQuery support:

pip install kanoniv[bigquery]

Connect to your BigQuery tables:

from kanoniv import Source

project = "my-project"
dataset = "kanoniv_demo"
conn = f"bigquery://{project}/{dataset}?location=US"

sources = [
    Source.from_warehouse(
        "ga4_events", "ga4_events",
        connection_string=conn,
        primary_key="client_id",
    ),
    Source.from_warehouse(
        "hubspot_contacts", "hubspot_contacts",
        connection_string=conn,
        primary_key="contact_id",
    ),
    Source.from_warehouse(
        "stripe_customers", "stripe_customers",
        connection_string=conn,
        primary_key="customer_id",
    ),
    Source.from_warehouse(
        "zendesk_users", "zendesk_users",
        connection_string=conn,
        primary_key="user_id",
    ),
]

The bigquery:// URL format is bigquery://project_id/dataset?location=US. Kanoniv uses Application Default Credentials (ADC) automatically - the same credentials you use with gcloud and bq.

---

Step 3: Write the Identity Spec

Create a spec that tells Kanoniv how to match records across the four sources. The key insight: GA4's user_id field contains email addresses when users are authenticated, which bridges anonymous analytics to known CRM records.

from kanoniv import Spec, validate

spec = Spec.from_string("""
api_version: "kanoniv/v2"
identity_version: "ga_bigquery_demo_v1"
metadata:
  name: ga-bigquery-demo
  description: Unify GA4 + HubSpot + Stripe + Zendesk from BigQuery

entity:
  name: customer

sources:
  - name: ga4_events
    primary_key: client_id
    attributes:
      email: user_id       # GA4 user_id contains email when authenticated

  - name: hubspot_contacts
    primary_key: contact_id
    attributes:
      name: full_name
      email: email
      phone: phone

  - name: stripe_customers
    primary_key: customer_id
    attributes:
      name: name
      email: email

  - name: zendesk_users
    primary_key: user_id
    attributes:
      name: name
      email: email
      phone: phone

rules:
  - name: email_exact
    type: exact
    field: email
    weight: 1.0

  - name: phone_exact
    type: exact
    field: phone
    normalizer: phone      # Strips formatting: (555) 100-0001 -> 5551000001
    weight: 0.9

  - name: name_fuzzy
    type: similarity
    field: name
    algorithm: jaro_winkler
    threshold: 0.85
    weight: 0.6

survivorship:
  default: source_priority
  overrides:
    - field: name
      strategy: source_priority
      priority: [hubspot_contacts, zendesk_users, stripe_customers]
    - field: email
      strategy: source_priority
      priority: [hubspot_contacts, zendesk_users, stripe_customers, ga4_events]
    - field: phone
      strategy: source_priority
      priority: [hubspot_contacts, zendesk_users]

decision:
  thresholds:
    match: 0.85
    review: 0.60
""")

result = validate(spec)
result.raise_on_error()
print(f"Spec valid: {len(spec.sources)} sources, {len(spec.rules)} rules")

Key decisions in this spec:

• email: user_id on GA4 - maps the user_id column (which contains email addresses) to the canonical email field
• normalizer: phone - strips formatting so 555-100-0001, (555) 100-0001, and 5551000001 all match
• jaro_winkler at 0.85 - catches name variants like "Alice Smith" vs "A. Smith" vs "Alice S."
• source_priority survivorship - HubSpot CRM is the canonical source, then Zendesk, then Stripe

---

Step 4: Reconcile

Run the reconciliation engine:

from kanoniv import reconcile

result = reconcile(sources, spec)

total = sum(len(c) for c in result.clusters)
print(f"Total records:  {total}")
print(f"Clusters found: {len(result.clusters)}")
print(f"Golden records: {len(result.golden_records)}")
print(f"Merge rate:     {result.merge_rate:.1%}")

The engine processes all ~870 records and clusters them into unified customer profiles. Records from different sources that belong to the same person are merged into a single cluster.

---

Step 5: Inspect Cross-Source Matches

Golden records with source_count > 1 are identities that span multiple sources - these are the matches the engine resolved:

multi_source = [
    gr for gr in result.golden_records
    if int(gr.get("source_count", "1")) > 1
]
print(f"Cross-source golden records: {len(multi_source)}")

for gr in multi_source[:5]:
    kid = gr.get("kanoniv_id", "?")
    name = gr.get("name", "N/A")
    email = gr.get("email", "N/A")
    sc = gr.get("source_count", "1")
    print(f"  {kid}: {name} <{email}> ({sc} sources)")

Use the entity_lookup property to see how source records map to canonical IDs:

lookup = result.entity_lookup
print(lookup.head(20))

The lookup table has three columns: source_system, source_id, kanoniv_id. This is the join key for operational queries.

The engine bridged:

• GA4 authenticated user_id (email) -> HubSpot primary email
• HubSpot phone 555-100-0001 -> Zendesk phone (555) 100-0001 (via phone normalizer)
• HubSpot name "Alice Smith" -> Stripe name "A. Smith" (via Jaro-Winkler fuzzy match)

---

Step 6: Write Results Back to BigQuery

Export the resolved entities and membership map back to BigQuery:

import pyarrow as pa
from kanoniv.cloud_io import write_parquet_to_bigquery

golden_table = pa.Table.from_pylist(result.golden_records)
membership_table = pa.Table.from_pandas(result.entity_lookup)

counts = write_parquet_to_bigquery(
    {
        "resolved_entities": golden_table,
        "resolved_memberships": membership_table,
    },
    connection_string=conn,
    dataset=f"{dataset}_resolved",
)

for name, count in counts.items():
    print(f"  {name}: {count} rows")

This creates a new kanoniv_demo_resolved dataset with two tables:

• resolved_entities - one row per golden record (canonical customer)
• resolved_memberships - maps every source record to its golden record

---

Step 7: Query the Unified Customer View

Now query the results directly in BigQuery:

-- Golden records: one row per customer
SELECT
    kanoniv_id,
    name,
    email,
    phone,
    source_count
FROM `my-project.kanoniv_demo_resolved.resolved_entities`
WHERE CAST(source_count AS INT64) > 1
ORDER BY CAST(source_count AS INT64) DESC
LIMIT 20;

-- Membership map: which source records belong to each customer
SELECT
    m.kanoniv_id,
    m.source_system,
    m.source_id,
    e.full_name,
    e.email
FROM `my-project.kanoniv_demo_resolved.resolved_memberships` m
JOIN `my-project.kanoniv_demo.hubspot_contacts` e
    ON m.source_id = e.contact_id
    AND m.source_system = 'hubspot_contacts'
ORDER BY m.kanoniv_id
LIMIT 50;

-- Find GA sessions that resolved to known customers
SELECT
    m.kanoniv_id,
    g.client_id,
    g.user_id,
    g.event_name,
    g.page_path,
    g.session_start
FROM `my-project.kanoniv_demo_resolved.resolved_memberships` m
JOIN `my-project.kanoniv_demo.ga4_events` g
    ON m.source_id = g.client_id
    AND m.source_system = 'ga4_events'
WHERE m.kanoniv_id IN (
    SELECT kanoniv_id
    FROM `my-project.kanoniv_demo_resolved.resolved_entities`
    WHERE CAST(source_count AS INT64) >= 3
)
ORDER BY g.session_start DESC
LIMIT 100;

---

Run It All at Once

The resolve.py script runs the entire pipeline end-to-end:

pip install kanoniv[bigquery]
python resolve.py --project=my-project --dataset=kanoniv_demo

---

Summary

Step	What Happens
Setup	Sample data loaded into 4 BigQuery tables
Connect	Source.from_warehouse() with bigquery:// URL
Spec	Matching rules for email, phone (normalized), name (fuzzy)
Reconcile	Records clustered into unified customer profiles
Inspect	Golden records + entity lookup show cross-source matches
Export	Resolved entities written back to BigQuery
Query	Standard SQL on the unified view

Next Steps

• Customer 360 Tutorial - Same pattern with CSV files
• dbt + Snowflake - Snowflake pipeline with dbt normalization
• AutoTune - Fine-tune matching thresholds automatically
• Source Adapters - All connectors: CSV, JSON, pandas, Snowflake, Databricks, BigQuery
• Cloud API - Real-time resolution and bulk resolve