Obtaining a list of audience names and descriptions via Query Studio

[krystynasylwiaszurmanska]

I would like to run a query in Query Studio which produces a list of audiences with the following details: id, description, name, profile count.

I am wondering how to expand the following query to get not only ids and profile counts  but also names and descriptions:

SELECT
CONCAT_WS('-', key, source_namespace) AS audience_id,
COUNT(1) AS count_of_profiles
FROM
(
SELECT
UPPER(key) AS source_namespace,
EXPLODE(value)
FROM
(
SELECT
EXPLODE(segmentMembership)
FROM
<correct_dataset_name>
)
)
GROUP BY
audience_id;

[arpan-garg]

Hi @krystynasylwiaszurmanska  - As described earlier on separate thread , for this 2 datasets needs to be joined.

The first query can be used to get audience_id and the segmentName. Replace segmentdefinition_snapshot_export_xyz with the correct name

Select
identityMap.aepsegments.id as audience_id,
segmentName
from
segmentdefinition_snapshot_export_xyz
where
identityMap.aepsegments.id is not NULL
UNION ALL
Select
identityMap.ao.id,
segmentName
from
segmentdefinition_snapshot_export_xyz
where
identityMap.ao.id is not NULL

This will give you result like 

Later you can modify the query you wrote in your post to get result as shown below

Now from both these queries you get audience_id, profile count and audience name . You can union both of them to get the desired result.

Hope this helps.

[krystynasylwiaszurmanska]

Thanks for all your help!

I wrote the following query and it works great for getting segment names and counts. But it doesn't work for getting segment descriptions, the description column is all empty. Do you know why? Should I get descriptions in a different way?

Select
identityMap.aepsegments.id as audience_id,
segmentName,
description,
count_of_profiles
from
segmentdefinition_snapshot_export_XXX s1
LEFT JOIN (SELECT
key AS audience_id,
COUNT(1) AS count_of_profiles
FROM
(
SELECT
UPPER(key) AS source_namespace,
EXPLODE(value)
FROM
(
SELECT
EXPLODE(segmentMembership)
FROM
profile_snapshot_export_XXX
)
)
GROUP BY
audience_id) a on a.audience_id = array_join(s1.identityMap.aepsegments.id, '')
where
s1.identityMap.aepsegments.id is not NULL
UNION ALL
Select
identityMap.ao.id,
segmentName,
description,
count_of_profiles
from
segmentdefinition_snapshot_export_XXX s2
LEFT JOIN (SELECT
key AS audience_id,
COUNT(1) AS count_of_profiles
FROM
(
SELECT
UPPER(key) AS source_namespace,
EXPLODE(value)
FROM
(
SELECT
EXPLODE(segmentMembership)
FROM
profile_snapshot_export_XXX
)
)
GROUP BY
audience_id) b on b.audience_id = array_join(s2.identityMap.ao.id, '')
where
s2.identityMap.ao.id is not NULL
ORDER BY count_of_profiles DESC

[arpan-garg · Accepted solution]

Hi @krystynasylwiaszurmanska  - I don't see segmentDescription present in this schema and don't think so you will be able to fetch it via query services. If its important to you then you can think of using segment definition API using which you can fetch the description and name.

Thanks

[arun_97]

Great!!

But how to view attributes present in that particular audience profile???
Could you pls help me out.

[arpan-garg]

Hi @arun_97 - Can you please give me more info about your question? Maybe create a separate question for this. @krystynasylwiaszurmanska asked about fetching audience details and profile count.

[arun_97]

Hi @arpan-garg - Actually my question is let me explain with example :consider as some audience_id (e7er62-vbweu-icds-3vh) have 23 profiles ,Now i need to view the profiles attributes like (email_address,name,DOB,startDate) using query service , is it possible??

[NimashaJain]

Please raise a new question for your query?

@krystynasylwiaszurmanska Did you find the suggested solutions helpful? It would be great if you can  mark the answer as correct for posterity. If you have found out solution yourself, share it with wider audience in the community.