Understanding Identity Graphs in Adobe Experience Platform (AEP)

In the world of digital marketing and customer data platforms, identity resolution is a critical process. It helps brands stitch together multiple data points to form a unified view of their customers. Adobe Experience Platform (AEP) uses identity graphs to connect different identifiers, such as email addresses, cookies, and device IDs.

To analyze these identity graphs, we use SQL queries that help us understand how identities are linked and distributed. In this blog, we’ll break down an SQL query that extracts identity graph insights, step by step.

The Full Query

WITH identity_graphs AS (
  SELECT explode(identityMap) AS identity
  FROM dataset_name
  WHERE cardinality(identityMap) > 1
),
filtered_graphs AS (
  SELECT key, cardinality(value) AS identityCountInGraph
  FROM identity_graphs
  WHERE key NOT IN ('ecid', 'aaid', 'idfa', 'gaid')
)
SELECT key,
       identityCountInGraph,
       COUNT(*) AS graphCount,
       COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (PARTITION BY key) AS percentage
FROM filtered_graphs
GROUP BY key, identityCountInGraph
ORDER BY key, identityCountInGraph ASC;

What Does This Query Do?

The query aims to:

• ✅ Identify and analyze identity graphs in AEP.
• ✅ Exclude common device-based identifiers (like cookies and advertising IDs).
• ✅ Count how many identity graphs exist and their sizes.
• ✅ Show the percentage distribution of different graph sizes within each identity type.

This is crucial for understanding how customer identities are being stitched together and whether the identity resolution process is working as expected.

Step-by-Step Breakdown of the Query

1️⃣ Extract Identity Graphs

WITH identity_graphs AS (
  SELECT explode(identityMap) AS identity
  FROM dataset_name
  WHERE cardinality(identityMap) > 1
),

🔹 What is happening here?

• We are selecting records from dataset_name where the identityMap contains more than one identity.
• explode(identityMap) breaks down the identity map into individual identity records.

🔹 Why is this important?

By definition, an identity graph must have at least two linked identities. This step filters out single-identity records that don’t contribute to identity resolution.

2️⃣ Filter Out Common Device-Based Identifiers

filtered_graphs AS (
  SELECT key, cardinality(value) AS identityCountInGraph
  FROM identity_graphs
  WHERE key NOT IN ('ecid', 'aaid', 'idfa', 'gaid')
)

🔹 What is happening here?

• Each identity is assigned a key, which represents different identity namespaces (e.g., email, CRM ID, device ID).
• cardinality(value) calculates how many identities exist in the graph.
• We filter out common device-based identifiers:
  • ecid (Experience Cloud ID)
  • aaid (Android Advertising ID)
  • idfa (Apple Identifier for Advertisers)
  • gaid (Google Advertising ID)

🔹 Why is this important?

Device-based identifiers (like cookies and advertising IDs) are often temporary and not persistent across sessions or devices. By excluding them, we focus on more stable identity sources such as email, CRM IDs, or loyalty program numbers.

3️⃣ Count and Calculate Distribution of Identity Graphs

SELECT key,
       identityCountInGraph,
       COUNT(*) AS graphCount,
       COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (PARTITION BY key) AS percentage
FROM filtered_graphs
GROUP BY key, identityCountInGraph
ORDER BY key, identityCountInGraph ASC;

🔹 What is happening here?

• COUNT(*) AS graphCount → Counts how many times a particular identityCountInGraph appears.
• SUM(COUNT(*)) OVER (PARTITION BY key) → Calculates the total number of identity graphs for each identity namespace (key).
• COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (PARTITION BY key) AS percentage
  • Computes the percentage share of each graph size within its namespace.
  • Helps us understand how identities are distributed within each type.

🔹 Why is this important?

• Helps identify how customer identities are linked in AEP.
• Detects unusual identity clusters (e.g., if many graphs have an unusually high number of identities).
• Improves identity resolution strategies by showing where identities are fragmented or overly merged.

When & Where Should You Use This Query?

• ✔️ When analyzing identity resolution: Helps understand how customer identities are linked.
• ✔️ In debugging identity stitching issues: Detects anomalies in identity graphs.
• ✔️ For identity marketing strategies: Ensures customer profiles are correctly merged.
• ✔️ In Adobe Experience Platform (AEP) workflows: Useful for optimizing profile-building processes.

Possible Enhancements

✅ Filter out small identity graphs: If you only want to analyze larger graphs, add:

HAVING identityCountInGraph > 2

This ensures only identity graphs with more than two identities are considered.

✅ Track trends over time: If you store historical identity data, add a timestamp column to analyze how identity graphs evolve.

✅ Visualize the data: Use BI tools like Adobe Customer Journey Analytics, Power BI, or Tableau to create graphs that show identity distribution trends.

Final Thoughts

Identity resolution is at the core of delivering personalized experiences and effective marketing campaigns. This query helps analyze identity graphs to ensure they are correctly structured and provide meaningful insights into customer identities.

By filtering out temporary device-based identifiers and analyzing the distribution of identities within graphs, you can optimize your identity stitching strategy and improve customer data quality.

Reference: Identity graph linking rules — troubleshooting (Adobe Experience League)

💬 What Do You Think?

Have you encountered identity fragmentation issues in AEP? How do you handle identity resolution challenges? Drop a comment below and let’s discuss! 🚀