Customer Data Platforms and Marketing Data Integration Questions

Start by clarifying goals and constraints: we need a canonical user ID for analytics (de-duplication, cross-device funnels, lifetime value) while PII (email, SSN, raw IP) is restricted or cannot be stored.

Strategies

1. Deterministic hashing

• Approach: Hash stable identifiers (email, phone, device_id) with a salted, keyed HMAC on ingestion server-side. Store only the HMAC outputs as user keys.
• Example attrs: normalized email, E.164 phone, hashed customer_id, merchant_account_id.
• Pros: High precision (exact matches), reproducible, simple joins.
• Cons: Misses non-exact matches (low recall) if data differs; key management critical.

2. Probabilistic matching

• Approach: Compute similarity scores between records using non-PII signals (aggregated device signals, behavioral fingerprints, user-agent + geo/time windows) or fuzzy matching on hashed tokens (Bloom filters, private set intersection variants).
• Example attrs: hashed name tokens, zip+first_letter_of_lastname, hashed device fingerprint, login timestamp distributions.
• Pros: Higher recall—finds likely matches when deterministic fails.
• Cons: Lower precision, risk of false merges; requires threshold tuning and auditing.

3. Identity graphs

• Approach: Build a graph of hashed identifiers and edges (deterministic links from login, transactional links, probabilistic edges with weights). Use graph algorithms to compute canonical cluster IDs (connected components, weighted clustering).
• Example: Node types: HMAC(email), HMAC(phone), device_id_hash, cookie_hash. Edges: login_event (strong), same_payment_method (medium), co-activity within 24h (weak).
• Pros: Rich lineage, explainability of merges; supports incremental updates.
• Cons: Complexity, compute cost, privacy risks from edge semantics.

4. Server-side stitching

• Approach: Perform all identity resolution in a secure backend environment (no raw PII persisted). Expose only opaque canonical IDs and aggregated metrics to BI layer.
• Implementation: Ingest events into secure enclave, run HMAC, matching, and graph algorithms; emit canonical_id + privacy-safe attributes (cohort flags, segments).
• Pros: Minimizes PII exposure, centralizes key mgmt.
• Cons: Requires secure infra, audit trails, and compute.

Trade-offs: precision vs recall vs privacy

• Deterministic = high precision, low recall; probabilistic increases recall at expense of precision and higher re-identification risk.
• Aggressive stitching (low thresholds) improves funnel completeness but risks incorrect merges that bias metrics (overstated retention, misattributed revenue).
• Conservative strategies protect metric accuracy but under-count users.

Concrete attribute list (privacy-safe):

• HMAC(email_norm), HMAC(phone_norm), customer_id_hash (first-party), device_id_hash, cookie_hash, first_seen_date, geo at coarse granularity (country, state), hashed IP truncated + salt, hashed billing_token, aggregated behavioral vectors (p90 session_length, top 3 event types).

Legal & privacy considerations

• Minimize collection; apply data minimization and purpose limitation.
• Use keyed HMAC with rotating keys; store keys in KMS with strict access controls and logs.
• Prefer irreversible hashing over reversible encryption; avoid storing raw PII.
• Assess re-identification risk and perform DPIAs where required (GDPR). If using probabilistic linking, document merging logic and allow for opt-out/erasure workflows.
• Maintain retention policies, anonymize/aggregate before exporting to BI; surface only cohort-level or pseudonymized canonical IDs.
• In jurisdictions like GDPR/CCPA, ensure lawful basis (consent/legitimate interest), provide transparency, and honor data subject requests.

Operational best practices for BI

• Track merge provenance so analysts can exclude uncertain merges.
• Expose confidence scores and a “merged_by” reason in metadata; allow dashboards to filter by high-confidence canonical_ids.
• Regularly validate matching algorithms against labeled samples; monitor metrics for sudden shifts that signal erroneous stitching.
• Document assumptions in dashboards (e.g., “user counts reflect canonical IDs with ≥0.9 confidence”) to avoid misinterpretation.

Requirements:

• Reconcile conversions from web, mobile app, and offline (call center) to marketing touchpoints.
• Respect privacy: user opt-outs, GDPR/CCPA, avoid PII leakage.
• Provide near-real-time and batch views for BI dashboards (funnel, LTV, channel ROAS).
• Explainability: attribution traces for audit.

High-level approach:

• Use a privacy-first, deterministic-then-probabilistic identity graph with clear separation of PII and analytics IDs.
• Deterministic links: hashed emails, hashed phone numbers (salted per-tenant) and device identifiers when user consent exists.
• Probabilistic matching only on aggregated level (no raw PII), with explicit opt-out exclusion.

Data flow:

1. Ingestion:
   • Web/mobile SDKs emit anonymized events containing: event_id, timestamp, event_type, marketing_touch_id, analytics_id (device_id or app installation id), and hashed PII only if consented.
   • Call center CRM sends conversion records with call_id, timestamp, phone_hash (if consent), agent_notes id.
2. Identity resolution service (separate secure service):
   • Maintains a mapping table: analytics_id <-> persistent_analytics_id (PAID) and, when consented, phone_hash/email_hash -> customer_id.
   • Resolution runs in a secure environment; writes only non-PII references (PAID, touch_id) to the analytics warehouse.
3. Attribution engine:
   • Consumes event stream and resolved PAIDs, reconstructs user journeys, applies configurable models (last-click, multi-touch, time-decay).
   • Offline conversions are backfilled: match phone_hash -> PAID; if match absent, mark as unmatched and show aggregated channel-level uplifts.
4. Warehouse & BI:
   • Store event_facts, attribution_facts, and aggregated metrics in curated schemas. Use materialized views for near-real-time dashboards.
   • Expose dashboards (Looker/Tableau/Power BI) showing channel performance, conversion paths, and attribution provenance with filters for model type and time windows.

Privacy safeguards:

• PII stored only in a hardened identity vault accessible to a small ops team; analytics systems hold only hashed/salted IDs or PAIDs.
• Honor opt-outs by flagging PAIDs as opt-out; ingest pipeline drops or masks events tied to opt-out IDs.
• Differential privacy / k-anonymity applied to any exported aggregated reports that could risk re-identification (thresholding, noise addition for small cohorts).
• Audit logging for all identity joins and opt-out actions; retention policies enforced.

Explainability & BI surface:

• Attribution_facts include: conversion_id, PAID (non-PII), chosen_model, credited_touch_id(s), attribution_weight, confidence_score, matched_source (deterministic|probabilistic|unmatched), and source_type (web|app|call).
• Dashboards:
  • Executive: channel ROAS, conversions, CPA with selectable attribution model.
  • Path analysis: funnels and Sankey showing touch sequences; ability to drill to touch-level provenance (no PII).
  • Data quality: unmatched offline conversions, % deterministic matches, opt-out impact.
  • Anomaly alerts: sudden drops in deterministic matching or spikes in unmatched calls.
• Provide downloadable reports with aggregated aggregates only; any row-level export respects opt-outs and PII rules.

Trade-offs & notes:

• Deterministic matching gives accuracy but depends on consented PII; probabilistic helps fill gaps but reduces auditability.
• Real-time deterministic attribution feasible; probabilistic/fuzzy joins are batch to reduce re-identification risk.
• Work with legal to define salt rotation, retention windows, and allowed analytics surfaces.

This design gives BI teams transparent, privacy-compliant attribution data with provenance and configurable models suitable for dashboards and executive reporting.