Obtaining a list of audience names and descriptions via Query Studio | Adobe Higher Education
Skip to main content
K
krystynasylwiaszurmanska
Level 2
January 26, 2024
Resuelto

Obtaining a list of audience names and descriptions via Query Studio

  • January 26, 2024
  • 1 respuesta
  • 2734 visualizaciones

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;

    Mejor respuesta de arpan-garg

    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

    1 respuesta

    arpan-garg
    Community Advisor
    arpan-gargCommunity Advisor
    Community Advisor
    January 29, 2024

    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.

      K
      krystynasylwiaszurmanskaAutor
      Level 2
      January 29, 2024

      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
        Community Advisor
        arpan-gargCommunity AdvisorRespuesta
        Community Advisor
        January 29, 2024

        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

          Términos y condicionesAccessibility statement

          Loading footer...