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

[itsMeTechy]

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

[AnkitJasani29]

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 · Accepted solution]

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]

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]

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.