Skip to main content
crispypop
Level 2
June 27, 2023
Solved

How to query historical attributes

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

    This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
    birthariyaAccepted solution
    Level 3
    June 28, 2023

    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 in thread

    3 replies

    birthariya
    Level 3
    June 28, 2023

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

    Translate
    crispypopAuthor
    Level 2
    June 28, 2023

    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

    Translate
    birthariyaAccepted solution
    Level 3
    June 28, 2023

    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.

    Translate
      Terms & ConditionsAccessibility statement

      Loading footer...