Product Analytics Instrumentation and Event Tracking Questions

High-level principle: split responsibilities by intent - Product owns the “what” (metrics & use-cases), Data owns the “definition/contract” and validation, Engineering owns the “implementation, delivery and runtime reliability.” Tie with one accountable owner per decision to avoid paralysis.

RACI (Activities → Product / Data / Engineering):

• Instrumentation design (events, metrics, tags)

  • Responsible: Product (defines metrics/use-cases)
  • Accountable: Data (schema/design approval)
  • Consulted: Engineering (feasibility, SDKs)
  • Informed: Stakeholders (PMs, Eng leads)

• Schema changes (new fields, renames)

  • Responsible: Data (maintains schema registry & contracts)
  • Accountable: Data
  • Consulted: Product (use-case impact), Engineering (implementation)
  • Informed: Consumers (downstream teams)

• Pipeline maintenance (ETL, streaming jobs)

  • Responsible: Engineering (platform/SRE team)
  • Accountable: Engineering
  • Consulted: Data (transform correctness), Product (priority)
  • Informed: Incident channels, Data consumers

• Data quality alerts (validation, anomaly detection)

  • Responsible: Data (write checks, maintain rules)
  • Accountable: Data
  • Consulted: Engineering (signal routing, remediation playbooks)
  • Informed: Product (impact on KPIs), On-call engineers

• SLA monitoring (latency, freshness, availability)

  • Responsible: Engineering (instrumentation + monitoring)
  • Accountable: Engineering
  • Consulted: Data (SLO definitions), Product (business SLAs)
  • Informed: Execs, Product ops

Operationalizing handoffs:

1. Schema Registry + Change Proposal: all schema changes require a Schema Change Proposal (simple PR) in a central registry with impact matrix. Data reviews and approves within SLA (48–72h).
2. Instrumentation Playbook: product authors an Instrumentation Spec template (event name, owner, business meaning, examples, retention), engineering attaches implementation PR referencing spec; Data signs off before merge.
3. CI/CD & Tests: instrumentation SDKs include unit tests that surface missing required fields; pipeline accepts only validated schema versions.
4. Change Windows & Deprecation Policy: versioned fields with clear deprecation timelines (e.g., announce 90d, deprecate 30d later); automated consumer breakage reports.
5. On-call & Runbooks: Data owns detector rules; Engineering owns alerting channels and runbooks. Alerts route to on-call pager with escalation and playbook links.
6. Governance Rhythm: weekly triage for urgent fixes, monthly data governance sync (Product + Data + Eng) and quarterly review of contracts, KPIs, and ownership shifts.
7. Dashboard & Audit Trail: public dashboard with pipeline SLAs, Q/A metrics, recent schema changes and consumer impact logs. All handoffs logged in ticket/PR for audit.

This model balances clear accountability while enabling collaborative consultation; it enforces guardrails (registry, tests, SLAs) so handoffs are explicit, automated where possible, and auditable.

High-level approach: systematically compare numerator and denominator definitions, data pipelines, sampling, deduplication, filters/timezones, and measurement rules. For each step I run SQL checks (and log-level audits) to isolate where counts diverge.

1. Compare raw event counts (same window, timezone)

SELECT
  COUNT(*) AS events,
  COUNT(DISTINCT user_id) AS unique_users,
  MIN(event_time) AS min_ts, MAX(event_time) AS max_ts
FROM events_table
WHERE event_name = 'click'
  AND event_time BETWEEN '2025-11-01' AND '2025-11-30';

Repeat for 'impression' and for the Amplitude-exported table.

2. Check timezone alignment / window boundaries

• Convert both to UTC and to business timezone, compare.

SELECT
  COUNT(*) FILTER (WHERE event_time AT TIME ZONE 'UTC' BETWEEN ... ) AS utc_count,
  COUNT(*) FILTER (WHERE (event_time AT TIME ZONE 'America/Los_Angeles') BETWEEN ... ) AS pst_count
FROM events_table;

3. Denominator definition: impressions vs opportunities vs pageviews

• Verify what Looker uses (impression event vs render) vs Amplitude.

SELECT event_name, COUNT(*) FROM events_table
WHERE event_time BETWEEN ... GROUP BY 1;

4. Numerator definition: what qualifies as a click

• Check event deduplication keys (event_id, session_id)

SELECT COUNT(*) AS raw_clicks,
       COUNT(DISTINCT event_id) AS deduped_clicks
FROM events_table
WHERE event_name='click' AND event_time BETWEEN ...;

5. Sampling or export limits

• Look for sampling flags or truncated exports (e.g., only first N events)

SELECT sample_rate, COUNT(*) FROM events_table_meta GROUP BY sample_rate;

Check Amplitude project settings and any piped exports for sampling.

6. Sessionization/Attribution differences (first-touch vs last-touch, attribution window)

• Recompute CTR under different attribution windows:

-- clicks within 30s of impression
SELECT COUNT(DISTINCT c.event_id) AS clicks_within_30s,
       COUNT(DISTINCT i.event_id) AS impressions
FROM impressions i
LEFT JOIN clicks c ON c.user_id = i.user_id
  AND c.event_time BETWEEN i.event_time AND i.event_time + INTERVAL '30 seconds';

7. Filters / LookML model logic

• Export the LookML measure SQL; run it directly in warehouse to compare.
• Check WHERE clauses, joins, user segments, bot filters.

8. Event dedupe across retried events / batch uploads

• Identify duplicated event_id across partitions

SELECT event_id, COUNT(*) FROM events_table
WHERE event_time BETWEEN ... GROUP BY event_id HAVING COUNT(*)>1 LIMIT 10;

9. Lag and late-arriving events

• Compare counts by ingestion_date vs event_date

SELECT event_date, ingestion_date, COUNT(*) FROM events_table
GROUP BY 1,2 ORDER BY event_date;

10. Measurement differences (user-level CTR vs event-level CTR)

• Compute both, casting to numeric before dividing (integer/integer division in Postgres truncates toward zero: SUM(click)=10 and SUM(impression)=250 would silently return 0 instead of 0.04 if left as integer division):

-- event-level CTR
SELECT SUM(click)::numeric / NULLIF(SUM(impression), 0) FROM metrics_table;
-- user-level CTR
SELECT AVG(clicks_per_user::numeric / NULLIF(impressions_per_user, 0)) FROM (
  SELECT user_id, SUM(click) AS clicks_per_user, SUM(impression) AS impressions_per_user
  FROM metrics_table GROUP BY user_id
) t;

11. Reproduce Amplitude logic

• Export Amplitude raw CSV or their SQL and run same aggregation. Compare row-level mismatches by joining on event_id to find which events one system includes/excludes.

12. Final validate: reconcile sample of mismatched event_ids

• Pull top 100 mismatches and inspect payloads/metadata (user_agent, sdk_version, source, retries).

Root-cause patterns to expect:

• Different denominator definitions (impression vs opportunity)
• Dedup differences (Amplitude dedupes client-side ids)
• Timezone/window misalignment
• Sampling in one pipeline
• Attribution rules (within X seconds)
• Late/duplicate ingestion

Action: after identifying mismatch, fix LookML or pipeline (filter, dedupe, timezone cast) and produce reconciled numbers with documented SQL.

Situation: A daily metrics pipeline began emitting timezone-normalized timestamps incorrectly, causing our daily active user and revenue aggregates to shift and under/over-count on certain days.

Task: Ensure accurate metrics, minimize business impact (reporting, billing, experiments), coordinate a technical fix, and communicate transparently to stakeholders.

Action:

• Detect: Ask the data team to run anomaly detection and diffs: compare raw event timestamps vs. normalized timestamps, and compare yesterday’s aggregates to a 7–14 day baseline. We surfaced a clear pattern tied to a recent deploy that changed timezone handling (UTC vs. local DST-adjusted).
• Contain / Rollback: I convened engineering and SRE to evaluate hot rollback vs. patch. Because business reporting depended on timely aggregates, we rolled back the change to the previous deploy within 1 hour, returning normalization to known-good behavior.
• Fix: Engineers implemented a tested fix (explicit use of timezone-aware parsing library, added unit/integration tests, and a schema check in the transformation step). We gated the change behind a feature flag and deployed to staging, validated with synthetic events.
• Remediate historical aggregates: Data engineers reprocessed the affected window (identified by deploy timestamp) in a backfill job with recalculated normalized timestamps and recomputed daily aggregates. We validated backfill results against raw events and a checksum of totals.
• Communication: I sent an incident brief to stakeholders (product, finance, analytics, executive) within 2 hours: impact summary, actions taken (rollback + backfill), expected timeline for corrected reports (24–48 hours), and mitigation for any client-facing billing or experiment data. After remediation, I circulated a postmortem with root cause, timeline, QA improvements, monitoring added (alert on timestamp drift and aggregate divergences), and preventive actions (pre-deploy synthetic data checks, required reviews for timezone logic).

Result: Aggregates restored within 36 hours, no customer billing impact, monitoring prevented recurrence. Lessons: make timezone handling explicit, add synthetic end-to-end checks, and include data owners in deployment sign-off.

Requirements & constraints:

• Functional: honor user deletion requests within 48 hours across online stores (user profiles, live event tables, caches) and archived event stores (data lakes, S3, cold backups).
• Non-functional: provable audit logs, minimal service disruption, low false positives, scalable to N deletions/day.
• Regulatory: soft vs hard delete policy clarification; whether downstream derivatives (analytics, ML models) must be purged.

High-level design:

1. Request intake & orchestration

• Self-serve portal + API that creates a deletion-job with user ID, scope, timestamp, SLA (48h).
• Authorization & verification workflow (consent checks, legal holds).

2. Deletion engine

• Online pipeline: Fan-out orchestrator issues targeted deletes to microservices/databases and caches. Use idempotent delete APIs and CDC to remove events referencing user_id.
• Archive pipeline: Cataloged retention index maps user_id -> data locations (tables, S3 prefixes, partitions). Batch jobs (Spark) run targeted deletes or rewrite partitions to exclude user data. For immutable object stores, use tombstone metadata + lifecycle policy to purge physical objects within retention window.
• Message bus (Kafka) for reliable commands and status updates; each subsystem emits completion events.

3. Auditing & verification

• Append-only audit log with job lifecycle, signed timestamps, operator IDs.
• Validation service: sampling + deterministic scans (hash-based) to assert absence in indexes and sample objects. Produce a signed compliance report per job.
• Reconciliation: periodic end-to-end job scanner that cross-checks catalog, DB indices, and object storage for residual PII.

Key considerations & trade-offs:

• Immediate consistency vs eventual: online deletes should be synchronous for profiles; analytics/archives can be eventual but must complete within 48h.
• Cost: rewrites of large partitions are expensive - prefer targeted object-level deletes when possible; maintain mapping index upfront to avoid full-scan deletions.
• Legal hold override path with strict logging.

Operational plan & metrics:

• KPIs: % deletions completed within 48h, audit verification pass rate, mean time to detect residuals.
• Roadmap phases: 1) intake + online deletes, 2) archival indexing + targeted archive deletes, 3) audit/validation automation, 4) scale & hardening.

This approach balances compliance, scalability, clear ownership, and auditable evidence for regulators.

Overview / Goal
Ensure experiment metrics reflect real user behavior by making events complete, uniquely attributed, and continuously validated so design conclusions are trustworthy.

Prioritized QA checklist (high → low)

1. Event schema & contract verification
   • Confirm required fields (event_name, timestamp, user_id, device_id, session_id, experiment_id).
   • Validate types/format and TTL (timestamps not future/past-bounds).
2. Data completeness tests
   • Compare client-side sent vs server-received counts per hour/day by platform.
   • Spike/drop detection relative to baseline.
3. Identity resolution & attribution
   • Verify presence and precedence rules (logged-in user_id > anonymous_id).
   • Check cross-device joins rate; flag high anonymous-to-user conversion gaps.
4. Deduplication checks
   • Detect duplicate event_ids within ingestion window; verify idempotency logic.
5. Experiment assignment fidelity
   • Ensure each user has consistent experiment_id across sessions; check randomization balance.
6. End-to-end UX validation
   • Instrument manual sessions and automated scripts to generate known events and assert pipeline arrival.

Remediation plan

• Fix schema violations at source; add client-side validation and guarded sends.
• Implement server-side idempotency using event_id + dedupe window.
• Implement identity graph: deterministic joins (user_id ↔ device_id), unify anonymous→login mapping, and store resolution confidence flags.
• Backfill/replay: replay missing events from client logs where available; label replayed events and recompute affected metrics; if logs missing, impute only with clear annotation and sensitivity analysis.
• Recompute experiment metrics after dedupe and identity resolution; publish comparison report with impact.

Monitoring & long-term controls

• Real-time alerts: ingestion drop, duplicate rate > threshold, identity mismatch rate.
• Dashboards: raw vs cleaned counts, experiment assignment stability, conversion by resolved identity type.
• Regression tests in CI for analytics schema and A/B randomization.
• Documentation & SLAs: event contracts, ownership, and runbook for incidents.

This ensures experiments used by research are reliable, traceable, and actionable for design decisions.