Resolve split device identities into single user records

Merge fragmented anonymous device IDs and authenticated profiles into unified user journeys. Use it when multi-device activity skews active user counts.

You getIdentity Graph Mapping, deduplication Rules and edge Case Mitigations

Prompt

Prompt
Act as an identity resolution and product analyst. Analyze the sample user session rows below to design an identity stitching and deduplication strategy.
- Identify deterministic join keys (such as email hash or verified customer ID) and probabilistic signals (such as IP subnet plus browser fingerprint).
- Define the merge order to link anonymous device identifiers to authenticated user records.
- Detail edge-case handling for shared family devices or public terminals to avoid false merges.
Output format:
1. Identity Graph Mapping: Step-by-step merge logic
2. Deduplication Rules: Numbered criteria for merging device IDs into a canonical user ID
3. Edge Case Mitigations: Specific rules for shared hardware and rapid IP changes
4. Recommended Metric Adjustment: How to restate Daily Active Users (DAU)

The sample user event data is below:

Example output

Example for: mobile app and web traffic across guest checkouts and logged-in accounts

1. Identity Graph Mapping
  • Anchor Key: user_id generated upon account creation or verified login.
  • Bridge Keys: SHA256 hashed checkout email, phone number.
  • Secondary Keys: device_id (mobile IDFV/Android ID) and persistent web cookie_id.
  • Mapping Flow: When an anonymous session with device_id_B records a login or checkout with user_id_101, link device_id_B to user_id_101 retroactively for all prior events within a 30-day lookback window.
2. Deduplication Rules
  1. Deterministic Match: If any anonymous session shares a verified user_id or normalized email with a known profile, reassign all event records to the primary user_id.
  2. Timestamp Hierarchy: The earliest recorded account creation date acts as the parent profile if two authenticated profiles merge through a shared verified phone number.
  3. Session Windowing: Keep anonymous sessions independent until an explicit authentication event occurs.
3. Edge Case Mitigations
  • Shared Devices: If more than two distinct user_ids authenticate from the same device_id within 24 hours, do not merge historical anonymous events. Attribute past anonymous events only to the immediately preceding login.
  • Public Wi-Fi: Never merge profiles based solely on shared IP address and User-Agent strings.
4. Recommended Metric Adjustment

Restate Daily Active Users by querying COUNT(DISTINCT canonical_user_id) instead of COUNT(DISTINCT session_id). For unmerged anonymous users, count each unique device_id as one visitor only if active session duration exceeds 10 seconds.

Written by AI from this prompt. Your results will vary.

Paste sample identity tables, anonymous cookies, device IDs, and login logs into ChatGPT, Claude or Gemini. Ask the model to generate a SQL window function query to run the stitch logic on your warehouse tables.

Make it repeatable

Keep using this prompt without pasting it in every time.

Keep exploring

More in Data Analysis
Data Analysis

Interpret A/B test results plainly

Turn raw conversion figures into a clear summary that non-technical stakeholders can act on without statistical jargon.