How to query historical attributes

[crispypop]

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

[birthariyamayank]

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

[crispypop]

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

[birthariyamayank · Accepted solution]

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.