CJA - Multi values concatenated in a field, lookup challenge

[Hey_John]

Hi,

In our website, multiple product SKUs can be selected and we would like to track and analyze the popularity of the multi-select combinations. We are capturing the SKUs selected in datalayer like below,

"SKU001|SKU002|SKU003" when user selected 3 products

"SKU004|SKU005" when 2 products are selected

The SKU ID to Product name mapping CSV is ready and uploaded to AEP as Lookup dataset (there are ~10K SKU IDs). 

In the workspace report, we would like to have the field showing the product names like,

"Product Name 001|Product Name 002|Product Name 003"

"Product Name 004|Product Name 005"

We tried with Derived Field but seems it's not possible to do something like "Split the field, perform Lookup per items and concatenate them back".

Would like to see if there is any advice on this? 

Thanks,

John

[Asheesh_Pandey]

@Hey_John  I'm sure you have already tried available options for new derived field.  I'd use object array to solve this in CJA

https://experienceleague.adobe.com/en/docs/analytics-platform/using/cja-usecases/complex-data/object...

[Hey_John]

Hi @Asheesh_Pandey 

Thanks for your reply. I've review the documentation on array of objects. It's powerful to store the list structure of objects. However, our challenge is on reporting of the combinations of the object fields.

Below is a simplifed example based on the documentation,

{

  "ID": "1",

  "product": [

    {

      "SKU": "123",

      "units": 1

    },

    {

      "SKU": "456",

      "units": 1

    },

{

      "SKU": "789",

      "units": 1

    }

  ],

  "timestamp": 1534219229

}

{

  "ID": "2",

  "product": [

    {

      "SKU": "ABC",

      "units": 1

    },

    {

      "SKU": "DEF",

      "units": 1

    }

  ],

  "timestamp": 1534219230

}

What I am trying to achieve, is to have a report like below, so that we can understand the popularity of product combinations,

SKU selected in a Single Event	No. of Event
123+456+789	1
ABC+DEF	1

Is there anyway to somehow concat all the field values in an array?

Thanks,

John

[Asheesh_Pandey]

Hm, may create a new field dimension say "bundle" with in the array of object and set these product combinations using "product name" in place of SKU.  This new dimension "bundle"  would enable to report these combinations in desired reporting structure while maintaining unique SKU reporting in ID, Name dimensions.

Thanks,

Asheesh

[Hey_John]

Hi @Asheesh_Pandey 

Do you mean set the product names combinations to the "bundle" field in Adobe Launch client side code?

For some reasons, we can only capture the product SKU IDs. The IDs are now sent to Edge in object array. To show the product names combination, we tried on the Data Prep level but there is no lookup in Data Prep. On the CJA level, the Lookup on product names works fine for object array, but we cannot find a way to join the values in the array and set it to the "bundle" field.

Thanks.

[Asheesh_Pandey · Accepted solution]

Yes! if cant do on client for any reason. I'm not sure data prep can support combinations of functions as of now. Another option is using Query Service 

[Hey_John]

Hi @Asheesh_Pandey 

Thanks for the direction on Query Services. Studied the documentation and was thinking to use a scheduled SQL to retrieve the array values, do a concat and write it back to a XDM field. However, it seems SQL Update is not supported, would like to see any advice or if I'm missing something?

Thanks,

John