Expand my Community achievements bar.

Exporting Profile in Segments into CSV


Level 5

Hi there,


I came across this really useful query:

select * from profile_snapshot_export_XXXXX  where segmentmembership['ups']['195329af-1df-49f0-993a-4d3495132e6'].status IN ('realized', 'existing','exited')

from this community post: Post 


When I run the query, it satisfies my basic needs of knowing who is in the segment. Though I do have couple questions:

  1. What is "UPS" as mentioned in the query?
  2. Here is a sample response from running the query, I am not SQL expert, is it possible to:
    1.  Figure out what the fields are for the NULLs under the IdentityMap & SegmentMembership column, what would the query look like?
    2. Is it possible to only extract the UUID portion of the IdentityMap?
    3. akwankl_0-1679440791635.png
  3. How do I export this data as a CSV?

Thank you!


1 Reply


Community Advisor

Hello @akwankl 


1. You can extract the ecid or any identities like this.

SELECT identityMap['ecid']['id']  from profile_snapshot_export_XXXXXX ;

2. To understand the field's structure, look at the json of the profile attributes that will help you write/understand a query. I generally look at the templates to know how they work. You can find some sample queries here: Dataset query examples | Adobe Journey Optimizer

3. Once you have another query ready then you can store the output of the query in a dataset and then export the dataset to any cloud storage. More information is available here: Export datasets to cloud storage locations | Adobe Journey Optimizer

     Find me on LinkedIn