Customer Data Platforms and Marketing Data Integration Questions

Monitoring & alerting layers

1) End-to-end latency

• Metric: ingestion_time -> visible_in_crm_time per record (p50/p95/p99). Alert if p95 > SLA threshold.

2) Data volume & drift

• Metric: daily/ hourly event counts by type vs baseline (rolling 7d). Alert on >=20% deviation or sustained drop.

3) Schema drift

• Metric: schema fingerprint/hash for incoming events; detect new/missing fields. Alert on field additions/removals or type changes.

4) Error rates

• Metric: failed deliveries per minute, retry queue length, poison message rate. Alert on >X errors/min or growing retry queue.

5) Data quality & completeness

• Metric: % of records with required keys (customer_id, timestamp). Alert on <99% completeness.

6) Business KPIs for stakeholders

• Marketing: leads synced per hour, time-to-first-sync for campaigns, percent of high-priority leads delivered within SLA.
• Sales: new accounts created, updates to contact ownership within 24h.

Alerting strategy

• Severity levels: P1 (end-to-end latency SLA breach, high error rates), P2 (data drift), P3 (minor schema changes).
• Escalation: automated paging for P1 with runbook links; P2/P3 to on-call and owners.

Dashboards & artifacts

• Real-time dashboard with latency histograms, delivery success trends, schema diffs, and business KPI tiles.
• Runbooks for remediation steps and playbooks for common failures (schema change, rate limit, downstream outage).

This mixes technical metrics for engineers and summarized business KPIs for marketing/sales to maintain trust and enable fast remediation.

Goal: star schema for marketing attribution, preserving account & contact hierarchies.

Core tables

1. fact_activity

• activity_id (PK), timestamp, contact_sk FK, account_sk FK, channel, campaign_id, cost, conversions

2. dim_contact (SCD Type 2)

• contact_sk (surrogate PK), contact_id (natural), name, email, account_id, start_ts, end_ts, is_current, attributes...

3. dim_account (hierarchy, SCD Type 2)

• account_sk, account_id, account_name, parent_account_id, industry, start_ts, end_ts, is_current
• materialize parent hierarchy fields (root_account_sk, level) for fast rollups

4. dim_campaign / channel

• campaign_sk, campaign_id, name, attribution_model

5. dim_date

• date_sk, date, year, month, day

Foreign keys & joins

• fact_activity.contact_sk -> dim_contact.contact_sk (FK)
• fact_activity.account_sk -> dim_account.account_sk
• Use surrogate keys assigned during ETL via lookups/joins; map events to the contact/account snapshot active at event timestamp (SCD time-travel join using event timestamp between start_ts and end_ts).

SCD choices

• Use Type 2 for contact and account to preserve historical ownership and attributes affecting attribution. Maintain is_current flag and validity windows.
• Store hashed attribute columns for quick change detection; store change_reason metadata.

Denormalization

• Precompute commonly used rollups in fact (e.g., root_account_sk, account_tier) to avoid expensive recursive joins.
• Maintain a lightweight contact_profile materialized view with latest attributes for operational lookups.

Performance & maintenance

• Partition facts by date_sk and cluster by contact_sk/account_sk.
• Implement incremental ETL that updates SCD tables and emits corrected fact rows when historical merges/contacts change.

This design balances analytically correct attribution (via SCD2 and time-travel joins) with query performance through denormalized fields and appropriate partitioning.

Smoke test plan after CRM<->CDP sync deployment

Core checks (quick automated & manual):

• Connectivity: confirm successful authenticated API/CDC connections and recent heartbeat logs.
• Record counts: compare counts for a sample timeframe (e.g., last 24h) between systems within acceptable delta.
• Field-level sync: verify a set of representative records have expected field values and timestamps (include ownership fields).
• Idempotency: replay a test event and ensure no duplicate records in CRM/CDP.
• Identity resolution: create an anonymous event then convert to an identified contact and confirm activity stitched.
• Error handling: confirm invalid records land in dead-letter and do not corrupt target.
• Audit trail: check sync logs show success/failure with processing times.

Document results and runbook for rollback/patch if critical failures are found.

Checklist: Technical, Organizational, Financial

Technical

• Ingestion connectors, real-time vs batch support
• Identity resolution & profile unification capabilities
• Scalability: throughput & storage limits, partitioning model
• Data residency, security, encryption, RBAC, audit logs
• Integration surface (APIs, webhooks, data export)
• Extensibility: custom transformations, SDKs

Organizational

• Required skills to operate, vendor support SLAs
• Time to onboard and migrate, change management
• Vendor roadmap alignment with business needs

Financial

• Total cost of ownership (license, infra, integrations, staffing)
• Predictable costs vs variable usage fees
• Migration costs and sunk costs

Proof-of-Concept (2–4 weeks)

1. Define success criteria (ingest X events/min, single view generation latency < Y, match rate > Z).
2. Ingest representative dataset into vendor CDP and into extended in-house pipeline.
3. Run parity tests: profile resolution accuracy, downstream sync to CRM, query latency under load.
4. Evaluate ops: day-2 operations, schema changes, monitoring, and support response.
5. Cost projection: measure resource usage and extrapolate TCO for 12 months.

Decision guidance: prefer buy if vendor meets >80% functional needs, reduces TCO or time-to-value, and offloads operational burden; otherwise build/extend with prioritized gaps and plan for staged migration.

Objective & constraints

• Deduplicate leads across CRM and CDP without losing sales history; preserve source context; support manual review.

Overall strategy

1. Normalize fields (emails lowercased, phone E.164, remove tracking params).
2. Compute deterministic keys: email_hash, phone_hash, deterministic name+company fingerprint.
3. Candidate grouping via blocking (email hash, phone hash, device id).
4. Scoring heuristic per candidate pair:
   • Exact email match: +80
   • Phone match: +60
   • Name+company fuzzy (Levenshtein/soundex): up to +30
   • Shared device/browser identifiers or IP history: +15
   • Temporal proximity of events: +10
   • Conflicting high-confidence fields (different emails): -50
5. Thresholds: score >= 80 => auto-merge; 50-79 => queue for manual review; <50 => keep separate.

Merge mechanics & preserving sales history

• Use merge strategy that preserves union of activity timelines and keeps source-attributed event lists.
• Keep primary record pointer and retain secondary record as alias with pointer to merged master; copy over CRM opportunities and preserve created_by/created_at fields.
• Store merge provenance: which fields chosen as canonical, scores, and source timestamps in audit table.

Validation & safeguards

• Pre-merge dry-run mode and automated tests to detect lost associations (e.g., orphaned opportunities).
• Post-merge checks: ensure every sales activity still references a contact; reconcile opportunity foreign keys.
• Soft-merge first: mark merged records and show in UI for 14 days where rollback is easy.

Operational tooling

• Use Spark for bulk dedupe, a rules engine for scoring, and a human review web UI. Keep merges idempotent and use transaction logs to roll back merges if required.

This ensures high precision, protects sales history, and provides human-in-the-loop checks for ambiguous cases.