Expand my Community achievements bar.

SOLVED

Obtaining a list of audience names and descriptions via Query Studio

Avatar

Level 1

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;

1 Accepted Solution

Avatar

Correct answer by
Community Advisor

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

View solution in original post

7 Replies

Avatar

Community Advisor

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 

arpangarg_0-1706522711738.png

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

 

arpangarg_1-1706522830678.png

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.

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

Avatar

Correct answer by
Community Advisor

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

Avatar

Level 2

Great!!

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

Avatar

Community Advisor

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.

Avatar

Level 2

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??

Avatar

Employee

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.