how to get the count and few sample profiles with 50+ identities | Community
Skip to main content
itsMeTechy
itsMeTechy
Level 4
July 16, 2025
Solved

how to get the count and few sample profiles with 50+ identities

  • July 16, 2025
  • 2 replies
  • 443 views

Hi

 

is there a way to get the count of profiles with 50+ identities. as these profiles are not available for segmentation and in profile lookup. a count will be much helpful to understand how big is the count of such profiles which were not available for segmentation and activation.

 

also any way we can list the identities in such profiles for further analysis.

 

Thanks

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
Best answer by brekrut

You can use the following example who have more than 50 identity values.  The dataset name would be the profile snapshot table specific to the Adobe Experience Platform instance.

 

SELECT key, identityCountInGraph, count(identityCountInGraph) as graphCount
FROM (SELECT key, cardinality(value) as identityCountInGraph
FROM (SELECT explode(identityMap)
FROM dataset_name
WHERE cardinality(identityMap) > 1)) /* by definition, graphs have 2 or more identities */
WHERE key not in ('ecid', 'aaid', 'idfa', 'gaid') /* filter out common device/cookie namespaces */
GROUP BY 1, 2
ORDER BY 1, 2 asc

2 replies

AnkitJasani29
AnkitJasani29
Level 6
July 16, 2025

Hi @itsmetechy ,

I don't have query ready with me but using AEP’s Query Service you can do both. Count and list profiles with > 50 identities.

Reference: https://experienceleague.adobe.com/en/docs/experience-platform/query/sql/syntax

Thanks,

Ankit

brekrut
Adobe Employee
brekrutAdobe EmployeeAccepted solution
Adobe Employee
July 16, 2025

You can use the following example who have more than 50 identity values.  The dataset name would be the profile snapshot table specific to the Adobe Experience Platform instance.

 

SELECT key, identityCountInGraph, count(identityCountInGraph) as graphCount
FROM (SELECT key, cardinality(value) as identityCountInGraph
FROM (SELECT explode(identityMap)
FROM dataset_name
WHERE cardinality(identityMap) > 1)) /* by definition, graphs have 2 or more identities */
WHERE key not in ('ecid', 'aaid', 'idfa', 'gaid') /* filter out common device/cookie namespaces */
GROUP BY 1, 2
ORDER BY 1, 2 asc

itsMeTechy
itsMeTechyAuthor
Level 4
July 17, 2025

Thank you for the query @brekrut 

 

Does profile snapshot table will have profiles with more than 50+ identities. my understanding was that profile snapshot will reflect the profiles as in profile store and so it wont have the profiles with 50+ identities.

itsMeTechy
itsMeTechyAuthor
Level 4
July 17, 2025

queried the snapshot table and can see only profiles with less than 50 identities. profiles which were not available for segmentation (as they have 50+ identities) were either not available in snapshot table.

Terms & ConditionsAccessibility statement

Loading footer...