Expand my Community achievements bar.

Check out the 3rd Edition of the AEP Community Lens for all the latest releases, resources, and Community updates

Exporting Profile in Segments into CSV

Avatar

Level 2

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

Avatar

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

page footer