How to retrieve all profile ID's from a specific segment using query services?

[shubham10]

Hi Team,

Can someone please help me with the SQL query to retrieve all the profile ID's for a specific segment?

By using the query below, I can target my specific segment but not retrieve details from it.

SELECT * from TableName

where segmentName = "segment name";

[Anil_Umachigi]

@shubham10 are you looking to get all profiles part of a certain AEP segment created? if yes you have two ways to get this info 

1. In the UI, click on segment definition to reveal sample profiles
2. Use the API to preview/estimate the audience with profile id's 

AEP segments are not in a dataset and do not store profile id's, they do not behave like a dataset used to store xdm data. 

Hope this helps 

Anil 

[anuhya-tad]

@shubham10 Try this query

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

Note: replace profile_snapshot_export dataset name, segment id in the query as per the system you are using.profile_snapshot_export dataset is system generated one, you can find these by enabling show system datasets from UI. 

[Anil_Umachigi]

Hey @anuhya-tad Is this dataset created by default? i.e a snapshot of profile into a dataset or a manual creation? 

Does the data get updated every day on schedule? 

Thank you for sharing this. 

anil 

[anuhya-tad]

@Anil_Umachigi PFB answers

Q)Is this dataset created by default?

A)yes, system generated . snapshot of profile into a dataset .

Q)Does the data get updated every day on schedule? 

A)Yes, once per day

[Anil_Umachigi]

Perfect thank you! is there any documentation link which lists details of all the system generated dataset? I cannot find any  😞

Thank you again sharing here 🙂

[anuhya-tad]

@Anil_Umachigi 

Gained above knowledge from Adobe support team when interacted on an issue.

[Anil_Umachigi]

right ok! Thanks again 🙂

[AmitRaghuwanshi · Accepted solution]

Hi  @anuhya-tad ,  @Anil_Umachigi ,

There is a profile-snapshot-export dataset for each merge policies created within the environment (including the default one). To understand which merge policy a profile-snapshot-export dataset refers to, There is a mapping dataset that associates all the existing merge policies to the each profile-snapshot-export dataset called ”adwh_dim_merge_policies”.  

You can query "select * from adwh_dim_merge_policies" and get the details of snapshot data sets.

Thanks 

Amit

[Anil_Umachigi]

This is great info! Thank you for sharing @AmitRaghuwanshi