Is there a way to sort and rank audiences based on attributes in RTCDP?

[NickMannion]

Hi Adobe Community,

I have an interesting use case. So my team has this attribute for like propensity scores in our system. We want the ability that once we create an audience, we can group that audience by that propensity score attribute and then we can sort by the top 10% of customers with that attribute. So for example, if we have an audience that 100 people qualify for, I want to see what the propensity score range for the people with the highest 10 propensity scores (top 10%). I see in audience composition, we can use the rank function so we when we can descending, we can sort from top to bottom based on this attribute but I don't see a way to rank and then sort on the top 10%.

Thanks,

Nick

[nnakirikanti · Accepted solution]

Hello @NickMannion Data Distiller Audience is only yhe solution for this usecase without the intervention any external system ( like pre-prep of data with a flag or a percentage before ingesting to AEP).

Check this: https://experienceleague.adobe.com/en/docs/experience-platform/query/data-distiller-audiences/overvi...

Sample query to refine the rows based on your criteria and create a audience for your usecase.

SELECT * 
FROM (
CREATE AUDIENCE table_name
WITH (primary_identity='IdentitycolName', identity_namespace='Namespace for the identity used', [schema='target_schema_title'])
AS (
    SELECT *,

           ROW_NUMBER() OVER (ORDER BY your_column_to_sort_by) AS row_num

    FROM your_table

) AS ranked_data

WHERE row_num <= (SELECT COUNT(*) * 0.1 FROM your_table)  -- Select top 10% of records

ORDER BY row_num)

Let me know if you need more details on this.

~cheers,

NN.

[brekrut]

Agree with @nnakirikanti  on this approach.  Creating a rank of an attribute can be performed using data distiller or using audience compositions.