Leveraging Customer Profile Array Data for export in Destination | Community
Skip to main content
M
MichaelBailey
June 27, 2023
Solved

Leveraging Customer Profile Array Data for export in Destination

  • June 27, 2023
  • 2 replies
  • 2042 views

Is there any way to export Customer Profile attributes contained within an Array?

 

Simply setting up a destination and selecting a field from the array doesn't make sense and even using index notation doesn't work (and wouldn't solve our use case anyway):

 

Is there some work around or process for accessing these values?

 

For example, we create a Segment for customers who have 'checking' accounts with a balance of less than 100.

This Segment includes 2 Customer Profiles that contain the following data:

{
    "id": "111",
       "account": [
       {
          "accountNumber": "AAA1",
          "accountBalance": 75,
          "accountType": "checking"
       },
       {
          "accountNumber": "AAA2",
          "accountBalance": 50,
          "accountType": "savings"
       },
       {
          "accountNumber": "AAA3",
          "accountBalance": 50,
          "accountType": "checking"
       }
    ]
},
{
    "id": "222",
    "account": [
       {
          "accountNumber": "BBB1",
          "accountBalance": 25,
          "accountType": "checking"
       },
       {
          "accountNumber": "BBB2",
          "accountBalance": 100,
          "accountType": "savings"
       }
    ]
}
 
If we want to create an extract that contains the customer id, accountNumber, and account Balance the data in the extract would look something like:
id | accountNumber | accountBalance
111 | AAA1 | 75
111 | AAA3 | 50
222 | BBB1 | 25
 
Is there any way to create an extract similar to this?
 
Thanks in advance for any help you can provide!
    This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
    Best answer by adobechat

    Hi @michaelbailey 

    Yes, you are correct but the extraction of specific array data directly from profile attributes to a destination is not supported by Adobe. What I have suggested is a workaround using the Query Service. Unfortunately, this is the closest you can get as what you are after is not yet supported by Adobe Experience Platform as far as I know.

    You could raise a support ticket with Adobe to confirm and let me know :).

    Thanks

    Madhan

    2 replies

    adobechat
    adobechat
    June 27, 2023

    HI @michaelbailey Yes, you can achieve this using AEP Query Service !

    Given your JSON data structure, you would use a SQL query with the CROSS JOIN UNNEST() function to flatten the nested array. Try something like this:-

    SELECT id, accounts.accountNumber, accounts.accountBalance FROM your_table_name, CROSS JOIN UNNEST(account) AS accounts WHERE accounts.accountType = 'checking' AND accounts.accountBalance < 100

    Hope this solves your problem.

    Thanks

    Madhan 

      M
      MichaelBaileyAuthor
      June 27, 2023

      Thanks for your response Madhan that is really good info.

       

      This is useful for querying a dataset to create a dataset that contains this information, but doesn't solve the problem of accessing Customer Profile attributes or leveraging Segments.

       

      For example, this will return all records in the dataset that meet the criteria (checking < 100) which could be inaccurate when compared to the data that exists in Customer Profiles.

        adobechat
        adobechatAccepted solution
        June 28, 2023

        Hi @michaelbailey 

        Yes, you are correct but the extraction of specific array data directly from profile attributes to a destination is not supported by Adobe. What I have suggested is a workaround using the Query Service. Unfortunately, this is the closest you can get as what you are after is not yet supported by Adobe Experience Platform as far as I know.

        You could raise a support ticket with Adobe to confirm and let me know :).

        Thanks

        Madhan

          S
          sharmilap
          November 13, 2023

          Hi @michaelbailey ,

           

          Not sure if this still needs to be resolved for you. We have a similar use case and this functionality seems to work now. Please try the following for your source field and check:

           

          _keybank.accounts[0].accountClosedDate

           

          I selected these attributes using the upward arrow in the 'SOURCE FIELD' from Destination Mapping screen.

           

          Thanks.

            Terms & ConditionsAccessibility statement

            Loading footer...