Expand my Community achievements bar.

Find your best audience, no third party cookies required – that’s where Adobe Real-Time CDP Collaboration (coming soon) comes in
SOLVED

How to query historical attributes

Avatar

Level 1

Hi Everyone

 

I'm trying to write a query (or segment builder) that can select for previous attributes - that is attributes in the XDM Model that have changed.  

 

Has anyone attempted this?

 

Thanks

1 Accepted Solution

Avatar

Correct answer by
Level 3

You can start by looking for the profile_snapshot_export_<mergepolicyid> dataset after enabling the Show system dataset in UI. (based on the merge-policy available, you can see multiple entries).

 

And below basic query should give you result for the segment membership

 

SELECT 
   to_json(identityMap), 
   to_json(segmentMembership) 
FROM 
   profile_snapshot_export_e7c86f34_f8ba_4493_820c_abcabcababab

 

Output should look like 

 

{"ecid":[{"id":"1234567896994453049406843593310784"}]} | {"ups":{"2k2k2k2k-4a4c-4a5a-abfb-4e35ff2461de":{"status":"realized","lastQualificationTime":"2023-05-24T03:12:39.000Z"}}}

 

where UPS ID is segment ID (Again based on segment qualification you may see multiple values)

 

This will tell you the segment membership status. Again, this dataset gets dropped and recreated on daily basis(as the name suggests - Snapshot), so you might have to make a copy everyday of so you can go back in time.

View solution in original post

3 Replies

Avatar

Level 3

If you can provide more details/context, Merge Policy can be evaluated as one of the options.

Generally speaking, Profile attribute history is not supported in segment builder unless the data is captured via timeseries model
You can also look for the profile snapshot table, where you can copy the whole table on a daily basis and flag a change in any attributes.

(both options will require additional data to be loaded back into datalake, so please consider this while making the decision.)

Avatar

Level 1

that is really helpful

 

so, maybe as better context, we're trying to keep a record of segment membership... where membership is defined by an attribute not an event...

 

do you know if there's a way to query the datalake to figure out historical membership

 

thanks in advance

Avatar

Correct answer by
Level 3

You can start by looking for the profile_snapshot_export_<mergepolicyid> dataset after enabling the Show system dataset in UI. (based on the merge-policy available, you can see multiple entries).

 

And below basic query should give you result for the segment membership

 

SELECT 
   to_json(identityMap), 
   to_json(segmentMembership) 
FROM 
   profile_snapshot_export_e7c86f34_f8ba_4493_820c_abcabcababab

 

Output should look like 

 

{"ecid":[{"id":"1234567896994453049406843593310784"}]} | {"ups":{"2k2k2k2k-4a4c-4a5a-abfb-4e35ff2461de":{"status":"realized","lastQualificationTime":"2023-05-24T03:12:39.000Z"}}}

 

where UPS ID is segment ID (Again based on segment qualification you may see multiple values)

 

This will tell you the segment membership status. Again, this dataset gets dropped and recreated on daily basis(as the name suggests - Snapshot), so you might have to make a copy everyday of so you can go back in time.