Expand my Community achievements bar.

SOLVED

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

Avatar

Level 2

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";

1 Accepted Solution

Avatar

Correct answer by
Level 4

Hi  @Anuhya-Y ,  @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

 

View solution in original post

10 Replies

Avatar

Community Advisor

@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 

Avatar

Community Advisor

@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. 

anuhyatad_0-1667878512354.png

 

 

Avatar

Community Advisor

Hey @Anuhya-Y 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 

Avatar

Community Advisor

@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

Avatar

Community Advisor

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

Avatar

Community Advisor

@Anil_Umachigi 

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

Avatar

Correct answer by
Level 4

Hi  @Anuhya-Y ,  @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

 

Avatar

Level 2

Hi @Anuhya-Y @Anil_Umachigi  - Actually my question is 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??